-- 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 -- -- @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 -- -- PROJECTS 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, 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. estimated_finish_date timestamptz, earliest_finish_date timestamptz, latest_finish_date timestamptz, actual_hours_completed numeric, estimated_hours_total numeric ); -- 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 ); -- ROLES create sequence pm_role_seq start 3; create table pm_roles ( role_id integer constraint pm_role_id_pk primary key, one_line varchar(100) constraint pm_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')) ); 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_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 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 ); 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 ( 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, party_id integer constraint pm_project_role_map_user_id_fk references parties(party_id) on delete cascade ); comment on table pm_project_assignment is ' Maps who is a part of what project, and in what capacity '; -- TASKS -- 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 constraint pm_tasks_task_id_fk references cr_items on delete cascade constraint pm_task_task_id_pk primary key, task_number integer ); create table pm_tasks_revisions ( task_revision_id integer constraint pm_task_revs_id_fk references cr_revisions on delete cascade constraint pm_task_revs_id_pk primary key, -- dates are optional, because it may be computed in reference -- to all other items, or simply not have a deadline end_date timestamptz, -- keep track of completion status percent_complete numeric constraint pm_task_per_complete_gt_ck check(percent_complete >= 0) constraint pm_task_per_complete_lt_ck check(percent_complete <= 100), estimated_hours_work numeric, -- PERT charts require minimum and maximum estimates -- these are optionally used estimated_hours_work_min numeric, estimated_hours_work_max numeric, -- this should be computed by checking with logger? The actual -- data should be in logger, logged by who did it, when etc.. -- or we can create a separate table to keep track of task hours -- and make sure its data model is similar to logger? actual_hours_worked numeric, -- network diagram stuff, computed earliest_start timestamptz, earliest_finish timestamptz, latest_start timestamptz, latest_finish timestamptz ); -- create the content type select content_type__create_type ( 'pm_task', -- content_type 'content_revision', -- supertype 'Task', -- pretty_name 'Tasks', -- pretty_plural 'pm_tasks_revisions', -- table_name (should this be pm_task?) 'task_revision_id', -- id_column 'pm_task__name' -- name_method ); -- add in attributes select content_type__create_attribute ( 'pm_task', -- content_type 'end_date', -- attribute_name 'date', -- datatype 'End date', -- pretty_name 'End dates', -- pretty_plural null, -- sort_order null, -- default value 'timestamptz' -- column_spec ); select content_type__create_attribute ( 'pm_task', -- content_type 'percent_complete', -- attribute_name 'number', -- datatype 'Percent complete', -- pretty_name 'Percents complete', -- pretty_plural null, -- sort_order null, -- default value 'numeric' -- column_spec ); select content_type__create_attribute ( 'pm_task', -- content_type 'estimated_hours_work', -- attribute_name 'number', -- datatype 'Estimated hours work', -- pretty_name 'Estimated hours work', -- pretty_plural null, -- sort_order null, -- default value 'numeric' -- column_spec ); select content_type__create_attribute ( 'pm_task', -- content_type 'estimated_hours_work_min', -- attribute_name 'number', -- datatype 'Estimated minimum hours', -- pretty_name 'Estimated minimum hours', -- pretty_plural null, -- sort_order null, -- default value 'numeric' -- column_spec ); select content_type__create_attribute ( 'pm_task', -- content_type 'estimated_hours_work_max', -- attribute_name 'number', -- datatype 'Estimated maximum hours', -- pretty_name 'Estimated maximum hours', -- pretty_plural null, -- sort_order null, -- default value 'numeric' -- column_spec ); select content_type__create_attribute ( 'pm_task', -- content_type 'actual_hours_worked', -- attribute_name 'number', -- datatype 'Actual hours worked', -- pretty_name 'Actual hours worked', -- pretty_plural null, -- sort_order null, -- default value 'numeric' -- column_spec ); select content_type__create_attribute ( 'pm_task', -- content_type 'earliest_start', -- attribute_name 'date', -- datatype 'Earliest start date', -- pretty_name 'Earliest start dates', -- pretty_plural null, -- sort_order null, -- default value 'timestamptz' -- column_spec ); select content_type__create_attribute ( 'pm_task', -- content_type 'earliest_finish', -- attribute_name 'date', -- datatype 'Earliest finish date', -- pretty_name 'Earliest finish dates', -- pretty_plural null, -- sort_order null, -- default value 'timestamptz' -- column_spec ); select content_type__create_attribute ( 'pm_task', -- content_type 'latest_start', -- attribute_name 'date', -- datatype 'Latest start date', -- pretty_name 'Latest start dates', -- pretty_plural null, -- sort_order null, -- default value 'timestamptz' -- column_spec ); select content_type__create_attribute ( 'pm_task', -- content_type 'latest_finish', -- attribute_name 'date', -- datatype 'Latest finish date', -- pretty_name 'Latest finish dates', -- pretty_plural null, -- sort_order null, -- default value 'timestamptz' -- column_spec ); -- DEPENDENCIES -- dependency types -- such as: -- cannot start until Task X finishes -- cannot start until Task X begins -- cannot finish until Task X finishes -- cannot finish until Task X begins create table pm_task_dependency_types ( short_name varchar(100) constraint pm_task_const_sn_pk primary key, description varchar(1000) ); insert into pm_task_dependency_types (short_name, description) values ('start_before_start','Start before this starts'); insert into pm_task_dependency_types (short_name, description) values ('start_before_finish','Start before this finishes'); insert into pm_task_dependency_types (short_name, description) values ('finish_before_start','Finish before this starts'); insert into pm_task_dependency_types (short_name, description) values ('finish_before_finish','Finish before this finishes'); create sequence pm_task_dependency_seq; create table pm_task_dependency ( dependency_id integer constraint pm_task_const_id_pk primary key, task_id integer constraint pm_task_const_task_id_fk references pm_tasks on delete cascade, parent_task_id integer constraint pm_tasks_const_parent_id_fk references pm_tasks on delete cascade, dependency_type varchar constraint pm_tasks_const_type references pm_task_dependency_types ); -- WORKGROUPS create sequence pm_workgroup_seq; create table pm_workgroup ( workgroup_id integer constraint pm_workgroup_id_pk primary key, one_line varchar(100) constraint pm_workgroup_one_line_uq unique, description varchar(2000), 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 ); -- 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_assignment_party_fk references parties(party_id) on delete cascade ); comment on table pm_project_assignment is ' Maps who is a part of what project, and in what capacity ';