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.5 -r1.6 --- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 7 Aug 2003 23:08:45 -0000 1.5 +++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 13 Aug 2003 18:56:29 -0000 1.6 @@ -19,9 +19,6 @@ references cr_revisions on delete cascade constraint pm_projects_id_pk primary key, - project_name varchar(255) - constraint pm_projects_name_nn - not null, -- a user-specified project_code varchar(255), goal varchar(4000), @@ -53,10 +50,71 @@ ); + +create table pm_project_roles ( + role_id integer + constraint pm_project_role_id_pk + primary key, + one_line varchar(100) + constraint pm_project_role_one_line_uq + unique, + description varchar(2000), + sort_order integer +); + + +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_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 sequence pm_project_role_seq start 3; + + +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_project_roles, + party_id integer + constraint pm_project_role_map_user_id_fk + references parties(party_id) + on delete cascade +); + +comment on table pm_project_role_map 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 +); + +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 +create sequence pm_tasks_number_seq; create table pm_tasks ( task_id integer @@ -170,3 +228,74 @@ constraint pm_tasks_const_type references pm_task_constraint_types ); + + +-- assignments + + +create table pm_task_roles ( + role_id integer + constraint pm_task_role_id_pk + primary key, + one_line varchar(100) + 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 +); + +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 + 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 + references parties(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 +'; + +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 +';