Index: openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/postgresql/Attic/project-manager-table-create.sql,v diff -u -r1.17 -r1.18 --- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 26 Jan 2004 15:39:40 -0000 1.17 +++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 26 Feb 2004 15:15:40 -0000 1.18 @@ -1,7 +1,6 @@ -- TODO: -- -- need to add in workflow (for status among other things) --- add categories to projects -- -- packages/project-manager/sql/postgresql/project-manager-table-create.sql @@ -23,8 +22,8 @@ description varchar(100), -- closed or open status_type char(1) default 'c' - constraint pm_projects_status_type_ck - check (status_type in ('c','o')) + constraint pm_projects_status_type_ck + check (status_type in ('c','o')) ); insert into pm_project_status (status_id, description, status_type) values @@ -33,27 +32,29 @@ (2, 'Closed', 'c'); +-- project revisions, items are kept in cr_items + create table pm_projects ( - project_id integer - constraint project_manager_id_fk - references cr_revisions on delete cascade - constraint pm_projects_id_pk - primary key, - -- a user-specified project code - project_code varchar(255), - goal varchar(4000), - planned_start_date timestamptz, - planned_end_date timestamptz, - actual_start_date timestamptz, - actual_end_date timestamptz, + project_id integer + constraint pm_proj_rev_fk + references cr_revisions on delete cascade + constraint pm_proj_rev_pk + primary key, + -- a user-specified project code + project_code varchar(255), + goal varchar(4000), + planned_start_date timestamptz, + planned_end_date timestamptz, + actual_start_date timestamptz, + actual_end_date timestamptz, status_id integer constraint pm_projects_status_id_nn not null constraint pm_projects_status_id_fk references pm_project_status, - ongoing_p char(1) default 'f' - constraint pm_projects_ongoing_p_ck - check (ongoing_p in ('t','f')), + ongoing_p char(1) default 'f' + constraint pm_projects_ongoing_p_ck + check (ongoing_p in ('t','f')), -- denormalized, computed values -- these are computed but stored in projects table for efficient -- access. @@ -67,16 +68,19 @@ -- create the content type select content_type__create_type ( - 'pm_project', -- content_type - 'content_revision', -- supertype - 'Project', -- pretty_name - 'Projects', -- pretty_plural - 'pm_projects', -- table_name - 'project_id', -- id_column - 'pm_project__name' -- name_method + 'pm_project', -- content_type + 'content_revision', -- supertype + 'Project', -- pretty_name + 'Projects', -- pretty_plural + 'pm_projects', -- table_name + 'project_id', -- id_column + 'pm_project__name' -- name_method ); +-- other fields are added in too. See the -custom script. + + -- ROLES create sequence pm_role_seq start 4; @@ -106,7 +110,6 @@ insert into pm_roles (role_id, one_line, description, sort_order) values ('1','Lead','Team members who are responsible for the completion of the project','10'); insert into pm_roles (role_id, one_line, description, sort_order) values ('2','Player','A person on the team responsible for completion of the project','20'); - insert into pm_roles (role_id, one_line, description, sort_order, is_observer_p) values ('3','Watcher','A person interested in developments, possibly helping out on it.','30','t'); @@ -131,7 +134,7 @@ create table pm_project_assignment ( project_id integer - constraint pm_project_role_map_project_fk + constraint pm_proj_role_map_project_fk references pm_projects on delete cascade, role_id integer @@ -157,6 +160,25 @@ -- the information that we keep revisions on is in the -- pm_task_revisions table, the rest is in pm_task +create sequence pm_task_status_seq start 3; + +create table pm_task_status ( + status_id integer + constraint pm_task_status_pk + primary key, + description varchar(100), + -- closed or open + status_type char(1) default 'c' + constraint pm_task_status_type_ck + check (status_type in ('c','o')) +); + +insert into pm_task_status (status_id, description, status_type) values +(1, 'Open', 'o'); +insert into pm_task_status (status_id, description, status_type) values +(2, 'Closed', 'c'); + + create sequence pm_tasks_number_seq; create table pm_tasks ( @@ -166,7 +188,10 @@ on delete cascade constraint pm_task_task_id_pk primary key, - task_number integer + task_number integer, + status integer + constraint pm_tasks_task_status_fk + references pm_task_status ); @@ -206,7 +231,7 @@ -- create the content type select content_type__create_type ( 'pm_task', -- content_type - 'content_revision', -- supertype + 'content_revision', -- supertype 'Task', -- pretty_name 'Tasks', -- pretty_plural 'pm_tasks_revisions', -- table_name (should this be pm_task?) @@ -428,11 +453,31 @@ ); -comment on table pm_project_assignment is ' - Maps who is a part of what project, and in what capacity +comment on table pm_task_assignment is ' + Maps who is a part of what task, and in what capacity '; +create table pm_task_xref ( + task_id_1 integer + constraint pm_task_xref_task1_nn + not null + constraint pm_task_xref_task1_fk + references pm_tasks(task_id) + on delete cascade, + task_id_2 integer + constraint pm_task_xref_task2_nn + not null + constraint pm_task_xref_task2_fk + references pm_tasks(task_id) + on delete cascade, + constraint pm_task_xref_lt check (task_id_1 < task_id_2) +); +comment on table pm_task_xref is ' + Maps related tasks. +'; + + -- PROCESSES create sequence pm_process_seq;