Index: openacs-4/packages/project-manager/sql/oracle/project-manager-table-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/project-manager/sql/oracle/project-manager-table-create.sql,v diff -u -r1.1 -r1.1.2.1 --- openacs-4/packages/project-manager/sql/oracle/project-manager-table-create.sql 29 Apr 2005 17:43:36 -0000 1.1 +++ openacs-4/packages/project-manager/sql/oracle/project-manager-table-create.sql 30 Nov 2005 06:47:36 -0000 1.1.2.1 @@ -98,7 +98,10 @@ sort_order integer, is_observer_p char(1) default 'f' constraint pm_role_is_observer_ck - check (is_observer_p in ('t','f')) + check (is_observer_p in ('t','f')), + is_lead_p char(1) default 'f' + constraint pm_role_is_lead_ck + check (is_lead_p in ('t','f')) ); @@ -156,6 +159,166 @@ '; +-- PROCESSES + +create sequence pm_process_seq; + +create table pm_process ( + process_id integer + constraint pm_process_id_pk + primary key, + one_line varchar(200) + constraint pm_process_one_line_nn + not null, + description varchar(1000), + party_id integer + constraint pm_process_party_fk + references parties + constraint pm_process_party_nn + not null, + creation_date date, + deleted_p char(1) default 'f' + constraint pm_process_deleted_p_ck + check (deleted_p in ('t','f')) +); + +comment on table pm_process is ' + Processes are a set of templates for tasks, so that people can + create sets of tasks quickly. Their structure needs to match that of + tasks. The process holds the meta information, and is also an identifier + that is used by the user to select which process they''d like to copy or + use +'; + +create or replace view +pm_process_active as + SELECT * FROM pm_process where deleted_p = 'f'; + +-- each time a process is used, it creates an instance of that process +-- we use this to allow a user to see overviews of process status, etc.. + +create sequence pm_process_instance_seq start with 1; + +create table pm_process_instance ( + instance_id integer + constraint pm_process_instance_id_pk + primary key, + name varchar(200), + process_id integer + constraint pm_process_instance_process_fk + references pm_process on delete cascade, + project_item_id integer + constraint pm_process_project_fk + references cr_items +); + + +create sequence pm_process_task_seq; + +create table pm_process_task ( + process_task_id integer + constraint pm_process_task_id_pk + primary key, + process_id integer + constraint pm_process_process_id_fk + references + pm_process + constraint pm_process_process_id_nn + not null, + one_line varchar(200) + constraint pm_process_task_one_line_nn + not null, + description varchar(4000), + mime_type varchar(200) default 'text/plain' + constraint pm_process_task_mime_type_fk + references cr_mime_types(mime_type), + -- dates are optional, because it may be computed in reference + -- to all other items, or simply not have a deadline + -- percent complete is always 0 + 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, + ordering integer +); + +comment on table pm_process_task is ' + A template for the tasks that will be created by the process +'; + + +-- 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','Starts before this starts'); +insert into pm_task_dependency_types (short_name, description) values ('start_before_finish','Starts before this finishes'); +insert into pm_task_dependency_types (short_name, description) values ('finish_before_start','Finishes before this starts'); +insert into pm_task_dependency_types (short_name, description) values ('finish_before_finish','Finishes before this finishes'); + +create sequence pm_process_task_dependency_seq; + +create table pm_process_task_dependency ( + dependency_id integer + constraint pm_proc_task_dependcy_pk + primary key, + process_task_id integer + constraint pm_proc_task_proc_task_fk + references pm_process_task + on delete cascade, + parent_task_id integer + constraint pm_proc_task_parent_id_fk + references pm_process_task + on delete cascade, + dependency_type varchar(100) + constraint pm_process_task_dep_type + references pm_task_dependency_types, + constraint pm_proc_task_depend_uq + unique (process_task_id, parent_task_id) +); + +comment on table pm_process_task_dependency is ' + Keeps track of dependencies. Used to create the dependencies in the + new tasks. +'; + +create table pm_process_task_assignment ( + process_task_id integer + constraint pm_proc_task_assign_task_fk + references pm_process_task(process_task_id) + on delete cascade, + role_id integer + constraint pm_proc_task_assign_role_fk + references pm_roles, + party_id integer + constraint pm_proc_task_assign_party_fk + references parties(party_id) + on delete cascade, + constraint pm_proc_task_assgn_uq + unique (process_task_id, role_id, party_id) +); + + +comment on table pm_process_task_assignment is ' + Maps who is assigned to process tasks. These will be the default people + assigned to the new tasks +'; + + + -- TASKS -- we create two tables to store task information @@ -196,12 +359,16 @@ references pm_task_status, deleted_p char(1) default 'f' constraint pm_tasks_deleted_p_ck - check (deleted_p in ('t','f')) + check (deleted_p in ('t','f')), + process_instance integer + constraint pm_tasks_process_instance_fk + references + pm_process_instance ); CREATE OR REPLACE view pm_tasks_active as - SELECT task_id, task_number, status FROM pm_tasks where deleted_p = 'f'; + SELECT task_id, task_number, status, process_instance FROM pm_tasks where deleted_p = 'f'; create table pm_tasks_revisions ( @@ -234,7 +401,9 @@ earliest_start date, earliest_finish date, latest_start date, - latest_finish date + latest_finish date, + -- How important is this task + priority integer default 0 ); -- create the content type @@ -389,28 +558,6 @@ ); --- 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','Starts before this starts'); -insert into pm_task_dependency_types (short_name, description) values ('start_before_finish','Starts before this finishes'); -insert into pm_task_dependency_types (short_name, description) values ('finish_before_start','Finishes before this starts'); -insert into pm_task_dependency_types (short_name, description) values ('finish_before_finish','Finishes before this finishes'); - - create sequence pm_task_dependency_seq; create table pm_task_dependency ( @@ -438,8 +585,9 @@ ); --- WORKGROUPS +-- WORKGROUPS: currently not used + create sequence pm_workgroup_seq; create table pm_workgroup ( @@ -493,6 +641,8 @@ Maps who is a part of what task, and in what capacity '; +-- TASK CROSS REFERENCES + create table pm_task_xref ( task_id_1 integer constraint pm_task_xref_task1_nn @@ -514,113 +664,6 @@ '; --- PROCESSES - -create sequence pm_process_seq; - -create table pm_process ( - process_id integer - constraint pm_process_id_pk - primary key, - one_line varchar(200) - constraint pm_process_one_line_nn - not null, - description varchar(1000), - party_id integer - constraint pm_process_party_fk - references parties - constraint pm_process_party_nn - not null, - creation_date date -); - -comment on table pm_process is ' - Processes are a set of templates for tasks, so that people can - create sets of tasks quickly. Their structure needs to match that of - tasks. The process holds the meta information, and is also an identifier - that is used by the user to select which process they''d like to copy or - use -'; - -create sequence pm_process_task_seq; - -create table pm_process_task ( - process_task_id integer - constraint pm_process_task_id_pk - primary key, - process_id integer - constraint pm_process_process_id_fk - references - pm_process - constraint pm_process_process_id_nn - not null, - one_line varchar(200) - constraint pm_process_task_one_line_nn - not null, - description varchar(4000), - -- dates are optional, because it may be computed in reference - -- to all other items, or simply not have a deadline - -- percent complete is always 0 - 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, - ordering integer -); - -comment on table pm_process_task is ' - A template for the tasks that will be created by the process -'; - -create sequence pm_process_task_dependency_seq; - -create table pm_process_task_dependency ( - dependency_id integer - constraint pm_proc_task_dependcy_pk - primary key, - process_task_id integer - constraint pm_proc_task_proc_task_fk - references pm_process_task - on delete cascade, - parent_task_id integer - constraint pm_proc_task_parent_id_fk - references pm_process_task - on delete cascade, - dependency_type varchar(100) - constraint pm_process_task_dep_type - references pm_task_dependency_types, - constraint pm_proc_task_depend_uq - unique (process_task_id, parent_task_id) -); - -comment on table pm_process_task_dependency is ' - Keeps track of dependencies. Used to create the dependencies in the - new tasks. -'; - -create table pm_process_task_assignment ( - process_task_id integer - constraint pm_proc_task_assign_task_fk - references pm_process_task(process_task_id) - on delete cascade, - role_id integer - constraint pm_proc_task_assign_role_fk - references pm_roles, - party_id integer - constraint pm_proc_task_assign_party_fk - references parties(party_id) - on delete cascade, - constraint pm_proc_task_assgn_uq - unique (process_task_id, role_id, party_id) -); - - -comment on table pm_process_task_assignment is ' - Maps who is assigned to process tasks. These will be the default people - assigned to the new tasks -'; - create table pm_users_viewed ( viewing_user integer constraint pm_usrs_viewed_viewing_user_fk