2 -- rename old "date" fields to be SQL compliant
4 alter table project_history rename column date to mod_date;
7 -- Change project_task to delete on removal of project
10 ALTER TABLE project_task
11 ADD CONSTRAINT projecttask_groupprojectid_fk FOREIGN KEY (group_project_id)
12 REFERENCES project_group_list(group_project_id) ON DELETE CASCADE;
15 -- Add email address to send all task updates to
17 ALTER TABLE project_group_list ADD COLUMN send_all_posts_to text;
21 -- Each task can be assigned a category
23 CREATE SEQUENCE "project_categor_category_id_seq" ;
24 CREATE TABLE project_category (
25 category_id integer DEFAULT nextval('project_categor_category_id_seq'::text) NOT NULL,
27 CONSTRAINT projcat_projgroupid_fk REFERENCES project_group_list(group_project_id) ON DELETE CASCADE,
29 CONSTRAINT "project_category_pkey" Primary Key ("category_id")
31 CREATE INDEX projectcategory_groupprojectid ON project_category(group_project_id);
32 INSERT INTO project_category VALUES ('100','1','None');
33 SELECT SETVAL('project_categor_category_id_seq',100);
38 ALTER TABLE project_task ADD COLUMN category_id int REFERENCES project_category(category_id);
39 UPDATE project_task SET category_id=100;
42 -- Convenience view required for ProjectTask object
44 CREATE VIEW project_task_vw AS
45 SELECT project_task.*,project_category.category_name,project_status.status_name
47 FULL JOIN project_category ON (project_category.category_id=project_task.category_id)
48 NATURAL JOIN project_status;
51 -- Each task can have multiple artifacts associated with it
53 CREATE TABLE project_task_artifact (
55 CONSTRAINT projtaskartifact_projtaskid_fk REFERENCES project_task(project_task_id) ON DELETE CASCADE,
57 CONSTRAINT projtaskartifact_artifactid_fk REFERENCES artifact(artifact_id) ON DELETE CASCADE);
58 CREATE INDEX projecttaskartifact_projecttaskid ON project_task_artifact (project_task_id);
59 CREATE INDEX projecttaskartifact_artifactid ON project_task_artifact (artifact_id);
62 -- Relation to forums dedicated to this project
64 CREATE TABLE project_group_forum (
66 CONSTRAINT projgroupforum_projgroupid_fk REFERENCES project_group_list(group_project_id) ON DELETE CASCADE,
68 CONSTRAINT projgroupforum_groupforumid_fk REFERENCES forum_group_list(group_forum_id) ON DELETE CASCADE);
69 CREATE INDEX projectgroupforum_groupprojectid ON project_group_forum(group_project_id);
70 CREATE INDEX projectgroupforum_groupforumid ON project_group_forum(group_forum_id);
73 -- Relation to a category of docs for this project
75 CREATE TABLE project_group_doccat (
77 CONSTRAINT projgroupdoccat_projgroupid_fk REFERENCES project_group_list(group_project_id) ON DELETE CASCADE,
79 CONSTRAINT projgroupdoccat_docgroupid_fk REFERENCES doc_groups(doc_group) ON DELETE CASCADE);
80 CREATE INDEX projectgroupdoccat_groupprojectid ON project_group_forum(group_project_id);
81 CREATE INDEX projectgroupdoccat_groupgroupid ON project_group_doccat(doc_group_id);
86 CREATE VIEW project_depend_vw AS
87 SELECT pt.project_task_id,pd.is_dependent_on_task_id,pt.end_date,pt.start_date
88 FROM project_task pt NATURAL JOIN project_dependencies pd;
90 CREATE VIEW project_dependon_vw AS
91 SELECT pd.project_task_id,pd.is_dependent_on_task_id,pt.end_date,pt.start_date
92 FROM project_task pt FULL JOIN project_dependencies pd ON (pd.is_dependent_on_task_id=pt.project_task_id);
94 CREATE VIEW project_history_user_vw AS
95 SELECT users.realname,users.email,users.user_name,project_history.*
96 FROM users,project_history
97 WHERE project_history.mod_by=users.user_id;
100 -- Move project messages into separate table from project_history
102 CREATE TABLE project_messages (
103 project_message_id SERIAL,
104 project_task_id INT NOT NULL REFERENCES project_task(project_task_id) ON DELETE CASCADE,
106 posted_by INT NOT NULL REFERENCES users(user_id),
107 postdate int NOT NULL);
110 INSERT INTO project_messages (project_task_id,body,posted_by,postdate)
111 SELECT project_task_id,old_value,mod_by,mod_date
113 WHERE field_name='details';
115 DELETE FROM project_history WHERE field_name='details';
119 CREATE VIEW project_message_user_vw AS
120 SELECT users.realname,users.email,users.user_name,project_messages.*
121 FROM users,project_messages
122 WHERE project_messages.posted_by=users.user_id;
124 -- Remove all existing dependencies, as they may be problematic.
126 DELETE FROM project_dependencies;
129 -- Function to enforce dependencies in the table structure
132 CREATE OR REPLACE FUNCTION projtask_update_depend () RETURNS OPAQUE AS '
139 -- See if tasks that are dependent on us are OK
140 -- See if the end date has changed
142 IF NEW.end_date > OLD.end_date THEN
144 -- If the end date pushed back, push back dependent tasks
146 FOR dependent IN SELECT * FROM project_depend_vw WHERE is_dependent_on_task_id=NEW.project_task_id LOOP
148 -- Some dependent tasks may not start immediately
150 IF dependent.start_date > OLD.end_date THEN
151 IF dependent.start_date < NEW.end_date THEN
152 delta := NEW.end_date-dependent.start_date;
154 SET start_date=start_date+delta,
155 end_date=end_date+delta
156 WHERE project_task_id=dependent.project_task_id;
159 IF dependent.start_date = OLD.end_date THEN
160 delta := NEW.end_date-OLD.end_date;
162 SET start_date=start_date+delta,
163 end_date=end_date+delta
164 WHERE project_task_id=dependent.project_task_id;
168 ELSIF NEW.end_date < OLD.end_date THEN
170 -- If the end date moved up, move up dependent tasks
172 FOR dependent IN SELECT * FROM project_depend_vw WHERE is_dependent_on_task_id=NEW.project_task_id LOOP
173 IF dependent.start_date = OLD.end_date THEN
175 -- dependent task was constrained by us - bring it forward
177 delta := OLD.end_date-NEW.end_date;
179 SET start_date=start_date-delta,
180 end_date=end_date-delta
181 WHERE project_task_id=dependent.project_task_id;
186 -- MAY WISH TO INSERT AUDIT TRAIL HERE FOR CHANGED begin/end DATES
190 ' LANGUAGE 'plpgsql';
193 CREATE TRIGGER projtask_update_depend_trig AFTER UPDATE ON project_task
194 FOR EACH ROW EXECUTE PROCEDURE projtask_update_depend();
198 -- Function to enforce dependencies in the table structure
200 CREATE OR REPLACE FUNCTION projtask_insert_depend () RETURNS OPAQUE AS '
206 -- ENFORCE START/END DATE logic
208 IF NEW.start_date >= NEW.end_date THEN
209 RAISE EXCEPTION ''START DATE CANNOT BE AFTER END DATE'';
212 -- First make sure we start on or after end_date of tasks
215 FOR dependon IN SELECT * FROM project_dependon_vw
216 WHERE project_task_id=NEW.project_task_id LOOP
218 -- See if the task we are dependon on
219 -- ends after we are supposed to start
221 IF dependon.end_date > NEW.start_date THEN
222 delta := dependon.end_date-NEW.start_date;
223 RAISE NOTICE ''Bumping Back: % Delta: % '',NEW.project_task_id,delta;
224 NEW.start_date := NEW.start_date+delta;
225 NEW.end_date := NEW.end_date+delta;
231 ' LANGUAGE 'plpgsql';
233 CREATE TRIGGER projtask_insert_depend_trig BEFORE INSERT OR UPDATE ON project_task
234 FOR EACH ROW EXECUTE PROCEDURE projtask_insert_depend();