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.14 -r1.15 --- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 30 Sep 2003 12:10:01 -0000 1.14 +++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 3 Oct 2003 22:21:45 -0000 1.15 @@ -1,18 +1,19 @@ -- TODO: -- --- which items in this data model need to use the content repository? -- need to add in workflow (for status among other things) --- need to take into account acs-rels -- add categories to projects -- --- packages/test-project/sql/postgresql/test-project-table-create.sql +-- packages/project-manager/sql/postgresql/project-manager-table-create.sql -- --- @author jader@bread.com, ncarroll@ee.usyd.edu.au and everyone else involved in this thread: http://openacs.org/forums/message-view?message_id=90742 +-- @author jader@bread.com +-- @author ncarroll@ee.usyd.edu.au was involved in creating the initial CR version +-- @author everyone else involved in this thread: http://openacs.org/forums/message-view?message_id=90742 -- @creation-date 2003-05-15 -- --- Project repository datamodel. +-- PROJECTS + create table pm_projects ( project_id integer constraint project_manager_id_fk @@ -53,65 +54,82 @@ -create table pm_project_roles ( +-- ROLES + +create sequence pm_role_seq start 3; + +create table pm_roles ( role_id integer - constraint pm_project_role_id_pk + constraint pm_role_id_pk primary key, one_line varchar(100) - constraint pm_project_role_one_line_uq + constraint pm_role_one_line_uq unique, description varchar(2000), - sort_order integer + sort_order integer, + is_observer_p char(1) default 'f' + constraint pm_role_is_observer_ck + check (is_observer_p in ('t','f')) ); -comment on table pm_project_roles is ' - Roles represent the way in which a party participates in a project. For example, - they could be a manager, or client, or participant.. The sort order determines - what order it is displayed in. +comment on table pm_roles is ' + Roles represent the way in which a party participates in a project + or task. For example, they could be a manager, or client, or + participant.. The sort order determines what order it is displayed + in. The is_observer_p specifies whether they are directly + responsible for the task. People not directly responsible will not + get email notifications reminding them that tasks are overdue, for + example. '; -insert into pm_project_roles (role_id, one_line, description, sort_order) values ('1','Participant','Team members who are responsible for the completion of the project','10'); -insert into pm_project_roles (role_id, one_line, description, sort_order) values ('2','Manager','Manages the team to complete the project on time and on budget.','20'); +insert into pm_roles (role_id, one_line, description, sort_order) values ('1','Participant','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','Manager','Manages the team to complete the project or task on time and on budget.','20'); -create sequence pm_project_role_seq start 3; +create table pm_default_roles ( + role_id integer + constraint pm_default_role_fk + references pm_roles + on delete cascade, + party_id integer + constraint pm_default_role_party_fk + references parties(party_id) + on delete cascade, + constraint pm_default_roles_uq + unique (role_id, party_id) +); +comment on table pm_default_roles is ' + Specifies what role a person is a part of by default +'; -create table pm_project_role_map ( +-- PROJECT ASSIGNMENT + +create table pm_project_assignment ( project_id integer constraint pm_project_role_map_project_fk references pm_projects on delete cascade, role_id integer constraint pm_project_role_map_role_fk - references pm_project_roles, + references pm_roles, party_id integer constraint pm_project_role_map_user_id_fk references parties(party_id) - on delete cascade + on delete cascade, + constraint pm_project_assignment_uq + unique (project_id, role_id, party_id) ); -comment on table pm_project_role_map is ' + +comment on table pm_project_assignment is ' Maps who is a part of what project, and in what capacity '; -create table pm_project_default_roles ( - role_id integer - constraint pm_proj_default_role_fk - references pm_project_roles - on delete cascade, - party_id integer - constraint pm_proj_default_role_party_fk - references parties(party_id) - on delete cascade -); +-- TASKS -comment on table pm_project_role_map is ' - Specifies what role a person is a part of by default, for projects -'; - -- we create two tables to store task information -- the information that we keep revisions on is in the -- pm_task_revisions table, the rest is in pm_task @@ -286,6 +304,8 @@ ); +-- DEPENDENCIES + -- dependency types -- such as: -- cannot start until Task X finishes @@ -322,78 +342,65 @@ on delete cascade, dependency_type varchar constraint pm_tasks_const_type - references pm_task_dependency_types + references pm_task_dependency_types, + constraint pm_task_dependency_uq + unique (task_id, parent_task_id) ); --- assignments +-- WORKGROUPS +create sequence pm_workgroup_seq; -create table pm_task_roles ( - role_id integer - constraint pm_task_role_id_pk +create table pm_workgroup ( + workgroup_id integer + constraint pm_workgroup_id_pk primary key, one_line varchar(100) - constraint pm_task_role_one_line_uq + constraint pm_workgroup_one_line_uq unique, description varchar(2000), - is_observer_p char(1) default 'f' - constraint pm_task_role_ck - check (is_observer_p in ('t','f')), sort_order integer ); -comment on table pm_task_roles is ' - Roles represent the way in which a role participates in a task. For example, - they could be an assignee, or a watcher. They can be divided up the same way - the organization is broken up, by sales and R and D for example. These terms - can be made up to fit the conditions of the local company. Defaults are set - up by another table, pm_task_default_role - The is_observer_p specifies - whether they are directly responsible for the task. People not directly - responsible will not get email notifications reminding them that tasks are - overdue, for example. -'; - -insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('1','Manager','This person oversees someone responsible for completion of the task','t','10'); -insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('2','Assignee','This person is directly responsible for completion of the task','f','20'); -insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('3','Watcher','This person is interested in developments of a ticket, but not directly responsible for it.','t','30'); - - -create sequence pm_task_role_seq start 4; - - -create table pm_task_role_map ( - task_id integer - constraint pm_task_role_map_task_fk - references pm_tasks +create table pm_workgroup_parties ( + workgroup_id integer + constraint pm_workgroup_parties_wg_id_fk + references pm_workgroup(workgroup_id) on delete cascade, - role_id integer - constraint pm_task_roles_fk - references pm_task_roles, party_id integer - constraint pm_task_role_map_user_id_fk + constraint pm_workgroup_party_fk references parties(party_id) - on delete cascade + on delete cascade, + role_id integer + constraint pm_workgroup_role_id + references pm_roles, + constraint pm_workgroup_parties_uq + unique (workgroup_id, party_id, role_id) ); -comment on table pm_task_role_map is ' - Maps who is a part of what task, and in what capacity -'; -create table pm_task_default_roles ( - role_id integer - constraint pm_task_default_role_fk - references pm_task_roles +-- TASK ASSIGNMENTS + +create table pm_task_assignment ( + task_id integer + constraint pm_task_assignment_task_fk + references pm_tasks(task_id) on delete cascade, + role_id integer + constraint pm_task_assignment_role_fk + references pm_roles, party_id integer - constraint pm_task_default_role_party_fk + constraint pm_task_assignment_party_fk references parties(party_id) - on delete cascade + on delete cascade, + constraint pm_task_assignment_uq + unique (task_id, role_id, party_id) ); -comment on table pm_task_role_map is ' - Specifies what role a person is a part of by default, for tasks + +comment on table pm_project_assignment is ' + Maps who is a part of what project, and in what capacity '; @@ -443,7 +450,6 @@ description varchar(4000), -- dates are optional, because it may be computed in reference -- to all other items, or simply not have a deadline - end_date timestamptz, -- percent complete is always 0 estimated_hours_work numeric, -- PERT charts require minimum and maximum estimates