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.13 -r1.14 --- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 15 Sep 2003 23:09:56 -0000 1.13 +++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 30 Sep 2003 12:10:01 -0000 1.14 @@ -1,19 +1,18 @@ -- 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/project-manager/sql/postgresql/project-manager-table-create.sql +-- packages/test-project/sql/postgresql/test-project-table-create.sql -- --- @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 +-- @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 -- @creation-date 2003-05-15 -- --- PROJECTS - +-- Project repository datamodel. create table pm_projects ( project_id integer constraint project_manager_id_fk @@ -54,82 +53,65 @@ --- ROLES - -create sequence pm_role_seq start 3; - -create table pm_roles ( +create table pm_project_roles ( role_id integer - constraint pm_role_id_pk + constraint pm_project_role_id_pk primary key, one_line varchar(100) - constraint pm_role_one_line_uq + constraint pm_project_role_one_line_uq unique, description varchar(2000), - sort_order integer, - is_observer_p char(1) default 'f' - constraint pm_role_is_observer_ck - check (is_observer_p in ('t','f')) + sort_order integer ); -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. +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. '; -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'); +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'); -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) -); +create sequence pm_project_role_seq start 3; -comment on table pm_default_roles is ' - Specifies what role a person is a part of by default -'; --- PROJECT ASSIGNMENT - -create table pm_project_assignment ( +create table pm_project_role_map ( 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_roles, + references pm_project_roles, party_id integer constraint pm_project_role_map_user_id_fk references parties(party_id) - on delete cascade, - constraint pm_project_assignment_uq - unique (project_id, role_id, party_id) + on delete cascade ); - -comment on table pm_project_assignment is ' +comment on table pm_project_role_map is ' Maps who is a part of what project, and in what capacity '; --- TASKS +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 +); +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 @@ -304,8 +286,6 @@ ); --- DEPENDENCIES - -- dependency types -- such as: -- cannot start until Task X finishes @@ -342,65 +322,78 @@ on delete cascade, dependency_type varchar constraint pm_tasks_const_type - references pm_task_dependency_types, - constraint pm_task_dependency_uq - unique (task_id, parent_task_id) + references pm_task_dependency_types ); --- WORKGROUPS +-- assignments -create sequence pm_workgroup_seq; -create table pm_workgroup ( - workgroup_id integer - constraint pm_workgroup_id_pk +create table pm_task_roles ( + role_id integer + constraint pm_task_role_id_pk primary key, one_line varchar(100) - constraint pm_workgroup_one_line_uq + constraint pm_task_role_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 ); -create table pm_workgroup_parties ( - workgroup_id integer - constraint pm_workgroup_parties_wg_id_fk - references pm_workgroup(workgroup_id) - on delete cascade, - party_id integer - constraint pm_workgroup_party_fk - references parties(party_id) - 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_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'); --- TASK ASSIGNMENTS -create table pm_task_assignment ( +create sequence pm_task_role_seq start 4; + + +create table pm_task_role_map ( task_id integer - constraint pm_task_assignment_task_fk - references pm_tasks(task_id) + constraint pm_task_role_map_task_fk + references pm_tasks on delete cascade, role_id integer - constraint pm_task_assignment_role_fk - references pm_roles, + constraint pm_task_roles_fk + references pm_task_roles, party_id integer - constraint pm_task_assignment_party_fk + constraint pm_task_role_map_user_id_fk references parties(party_id) - on delete cascade, - constraint pm_task_assignment_uq - unique (task_id, role_id, party_id) + on delete cascade ); +comment on table pm_task_role_map is ' + Maps who is a part of what task, and in what capacity +'; -comment on table pm_project_assignment is ' - Maps who is a part of what project, and in what capacity +create table pm_task_default_roles ( + role_id integer + constraint pm_task_default_role_fk + references pm_task_roles + on delete cascade, + party_id integer + constraint pm_task_default_role_party_fk + references parties(party_id) + on delete cascade +); + +comment on table pm_task_role_map is ' + Specifies what role a person is a part of by default, for tasks ';