Buna TGH ,
e mai jos o solutie folosita
de mine intr-un project
cu PostgresSQL.
Daca e prea complicat poti
intreba pe <pers>.
E destul de <offtopic>
fie tree
a - b - c - d
|
e _ g
|
f
parents(id, parent)
a NULL
b a
c b
d c
e b
g e
f e
hierarchy(id, hit)
// Adica
Daca Parents(b,a) si Parents(c,b) atunci hierarchy(c,a)
Un fel de inchidere trazitiva rezolvata cu un triger.
---
b a
c b, c a
d a, d b, d c
Uite si scriptul cu
triger cu tot.
--
-- The Postgres specific
hacks
--
--create function plpgsql_call_handler() RETURNS opaque
--as '/usr/lib/pgsql/plpgsql.so' language
'c';
create function plpgsql_call_handler() RETURNS opaque
as '/usr/local/pgsql/lib/plpgsql.so' language 'c';
create trusted procedural language 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
-- Data model
CREATE TABLE projects (
id integer PRIMARY KEY,
name varchar(100) NOT NULL,
info varchar(255) DEFAULT '',
url_desc varchar(255) DEFAULT '',
url_info varchar(255) DEFAULT '',
parent integer DEFAULT 0,
begin_date integer NOT NULL,
end_date integer NOT NULL
);
CREATE SEQUENCE seq_projects_id;
CREATE INDEX idx_projects_parent ON projects(parent);
CREATE TABLE work_item (
email varchar(100) NOT NULL,
proj_id integer NOT NULL,
begin_date integer NOT NULL,
end_date integer NOT NULL,
status varchar(12) NOT NULL,
comment text DEFAULT '',
PRIMARY KEY (email, proj_id),
CHECK ( status in ('active','close','wait') )
);
CREATE INDEX idx_work_item_email ON work_item(email);
CREATE INDEX idx_work_item_proj_id ON work_item(proj_id);
CREATE TABLE pontaj (
id integer PRIMARY KEY,
email varchar(100) NOT NULL,
proj_id integer NOT NULL,
begin_date integer NOT NULL,
end_date integer NOT NULL,
comment text DEFAULT ''
);
CREATE SEQUENCE seq_pontaj_id;
CREATE INDEX idx_pontaj_email ON pontaj(email);
CREATE INDEX idx_pontaj_proj_id ON pontaj(proj_id);
CREATE INDEX idx_pontaj_begin_date ON pontaj(begin_date);
CREATE INDEX idx_pontaj_end_date ON pontaj(end_date);
CREATE INDEX idx_pontaj_e_date ON pontaj(email,begin_date);
CREATE VIEW view_work_item AS
SELECT work_item.email AS email,
work_item.proj_id AS proj_id,
work_item.begin_date AS begin_date,
work_item.end_date AS end_date,
work_item.status AS status,
work_item.comment AS comment,
projects.name AS name,
projects.info AS info,
projects.url_desc AS url_desc
FROM work_item, projects
WHERE proj_id = id;
CREATE TABLE hierarchy (
id integer PRIMARY KEY,
proj_id integer NOT NULL,
child_id integer NOT NULL,
level integer DEFAULT 1,
child_hier integer DEFAULT 1
);
CREATE SEQUENCE seq_hierarchy;
CREATE INDEX idx_hierarchy_proj_id ON hierarchy(proj_id);
CREATE INDEX idx_hierarchy_child_id ON hierarchy(child_id);
CREATE FUNCTION get_parent_project_id(integer)
RETURNS integer
AS '
DECLARE
p_proj_id alias for $1;
BEGIN
RETURN parent from projects WHERE id = p_proj_id;
END;
'
LANGUAGE 'plpgsql';
CREATE FUNCTION hierarchy_new_project(integer)
RETURNS integer
AS '
DECLARE
p_proj_id alias for $1;
v_parent_id integer;
v_hier_new_id integer;
v_count integer;
BEGIN
v_count := 0;
IF p_proj_id = NULL THEN
RETURN 0;
ELSE
v_parent_id := p_proj_id;
LOOP
IF v_parent_id = NULL THEN
RETURN v_count;
ELSE
IF (SELECT count(*) FROM hierarchy
WHERE proj_id = v_parent_id AND child_id =
p_proj_id) = 0 THEN
SELECT nextval(''seq_hierarchy'') INTO v_hier_new_id;
INSERT INTO hierarchy (id, proj_id, child_id)
VALUES (v_hier_new_id, v_parent_id, p_proj_id);
v_count := v_count + 1;
END IF;
END IF;
v_parent_id := get_parent_project_id(v_parent_id);
END LOOP;
END IF;
END;
'
LANGUAGE 'plpgsql';
CREATE FUNCTION on_new_project()
RETURNS opaque
AS '
DECLARE
v_parent_id integer;
v_hier_new_id integer;
BEGIN
IF NEW.id = NULL THEN
RETURN NEW;
ELSE
v_parent_id := NEW.id;
LOOP
IF v_parent_id = NULL THEN
RETURN NEW;
ELSE
IF (SELECT count(*) FROM hierarchy
WHERE proj_id = v_parent_id AND child_id =
NEW.id) = 0 THEN
SELECT nextval(''seq_hierarchy'') INTO v_hier_new_id;
INSERT INTO hierarchy (id, proj_id, child_id)
VALUES (v_hier_new_id, v_parent_id, NEW.id);
END IF;
END IF;
v_parent_id := get_parent_project_id(v_parent_id);
END LOOP;
END IF;
END;
'
LANGUAGE 'plpgsql';
CREATE TRIGGER trig_new_project
AFTER INSERT
ON projects
FOR EACH ROW
EXECUTE PROCEDURE on_new_project();
CREATE VIEW view_total_pontaj AS
SELECT projects.id AS proj_id,
projects.name AS name,
projects.parent AS parent,
hierarchy.child_id AS child_id,
pontaj.email AS email,
pontaj.begin_date AS begin_date,
pontaj.end_date AS end_date
FROM projects, hierarchy, pontaj
WHERE projects.id = hierarchy.proj_id AND
hierarchy.child_id = pontaj.proj_id;
CREATE VIEW view_nod_pontaj AS
SELECT projects.id AS proj_id,
projects.name AS name,
projects.parent AS parent,
pontaj.email AS email,
pontaj.begin_date AS begin_date,
pontaj.end_date AS end_date
FROM projects, pontaj
WHERE projects.id = pontaj.proj_id;
Petrica.
---
Pentru dezabonare, trimiteti mail la
[EMAIL PROTECTED] cu subiectul 'unsubscribe rlug'.
REGULI, arhive si alte informatii: http://www.lug.ro/mlist/
