-- 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 -- -- @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 -- -- Project repository datamodel. 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 varchar(255), goal varchar(4000), -- is the deadline computed from the end date, or from -- today? -- e = end, t = today deadline_scheduling char(1) default 't' constraint pm_projects_dline_scheduling_ck check (deadline_scheduling in ('t','e')), 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_completion_date timestamptz, earlier_completion_date timestamptz, latest_completion_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 ); 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 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, -- PERT charts require minimum and maximum estimates -- if we are not using these, then we set the min and max -- estimates to the same value. 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 ); -- 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_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 ); -- constraint 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_constraint_types ( short_name varchar(100) constraint pm_task_const_sn_pk primary key, description varchar(1000) ); insert into pm_task_constraint_types (short_name, description) values ('start_before_start','Must start before this task can start'); insert into pm_task_constraint_types (short_name, description) values ('start_before_finish','Must start before this task can finish'); insert into pm_task_constraint_types (short_name, description) values ('finish_before_start','Must finish before this task can start'); insert into pm_task_constraint_types (short_name, description) values ('finish_before_finish','Must finish before this task can finish'); create sequence pm_task_constraint_seq; create table pm_task_constraints ( constraint_id integer constraint pm_task_const_id_pk primary key, task_id integer constraint pm_tasks_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, const_type varchar 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 ';