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.9 -r1.10 --- openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 22 Aug 2003 21:06:10 -0000 1.9 +++ openacs-4/contrib/packages/project-manager/sql/postgresql/project-manager-table-create.sql 29 Aug 2003 00:35:35 -0000 1.10 @@ -19,15 +19,9 @@ references cr_revisions on delete cascade constraint pm_projects_id_pk primary key, - -- a user-specified + -- a user-specified project code 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, @@ -38,9 +32,9 @@ -- denormalized, computed values -- these are computed but stored in projects table for efficient -- access. - estimated_completion_date timestamptz, - earliest_completion_date timestamptz, - latest_completion_date timestamptz, + estimated_finish_date timestamptz, + earliest_finish_date timestamptz, + latest_finish_date timestamptz, actual_hours_completed numeric, estimated_hours_total numeric ); @@ -146,17 +140,26 @@ -- to all other items, or simply not have a deadline end_date timestamptz, -- keep track of completion status - percent_complete numeric, + 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 - -- if we are not using these, then we set the min and max - -- estimates to the same value. + -- 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 + actual_hours_worked numeric, + -- network diagram stuff, computed + earliest_start timestamptz, + earliest_finish timestamptz, + latest_start timestamptz, + latest_finish timestamptz ); -- create the content type @@ -196,6 +199,17 @@ 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 @@ -227,45 +241,88 @@ '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 +); --- constraint types +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 +); + + +-- 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_constraint_types ( +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_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'); +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_constraint_seq; +create sequence pm_task_dependency_seq; -create table pm_task_constraints ( - constraint_id integer +create table pm_task_dependency ( + dependency_id integer constraint pm_task_const_id_pk primary key, task_id integer - constraint pm_tasks_const_task_id_fk + 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, - const_type varchar + dependency_type varchar constraint pm_tasks_const_type - references pm_task_constraint_types + references pm_task_dependency_types );