Index: openacs-4/packages/workflow/sql/oracle/workflow-tables-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/workflow/sql/oracle/workflow-tables-create.sql,v diff -u -r1.10 -r1.11 --- openacs-4/packages/workflow/sql/oracle/workflow-tables-create.sql 11 Dec 2003 21:40:15 -0000 1.10 +++ openacs-4/packages/workflow/sql/oracle/workflow-tables-create.sql 8 Jan 2004 13:23:08 -0000 1.11 @@ -242,7 +242,47 @@ on delete cascade ); +-- TODO: Test these +create table workflow_action_children( + child_id integer + constraint wf_action_children_pk + primary key, + action_id integer + constraint wf_action_children_nn + not null + constraint wf_action_children_action_fk + references workflow_actions(action_id) + on delete cascade, + child_workflow integer + constraint wf_action_children_workflow_fk + references workflows(workflow_id) + on delete cascade +); +create table workflow_action_child_role_map( + child_id integer + constraint wf_act_child_rl_map_child_fk + references workflow_action_children(child_id), + parent_role integer + constraint wf_act_child_rl_map_prnt_rl_fk + references workflow_roles(role_id), + child_role integer + constraint wf_act_child_rl_map_chld_rl_fk + references workflow_roles(role_id), + mapping_type char(40) + constraint wf_act_child_rl_map_type_ck + check (mapping_type in + ('per_role','per_user')), + constraint wf_act_chld_rl_map_pk + primary key (child_id, parent_role) +); + +comment on column workflow_action_child_role_map.mapping_type is ' + If per user, we create a child workflow per user who is a member of any of the parties assigned to the parent_role. + If per role, we create just one child workflow, with the exact same parties that are in the parent_role. + If more than one child_role has a mapping_type other than per_role, the cartesian product of these roles will be created. +'; + --------------------------------- -- Workflow level, Finite State Machine Model --------------------------------- Index: openacs-4/packages/workflow/sql/oracle/upgrade/upgrade-1.2-2.0d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/workflow/sql/oracle/upgrade/upgrade-1.2-2.0d1.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/workflow/sql/oracle/upgrade/upgrade-1.2-2.0d1.sql 1 Dec 2003 09:53:19 -0000 1.2 +++ openacs-4/packages/workflow/sql/oracle/upgrade/upgrade-1.2-2.0d1.sql 8 Jan 2004 13:23:08 -0000 1.3 @@ -57,3 +57,59 @@ alter table workflow_fsm_states add constraint wf_fsm_states_short_name_un unique (workflow_id, short_name); alter table workflow_fsm_states add constraint wf_fsm_states_pretty_name_un unique (workflow_id, pretty_name); + + +-- New not null constraints +-- TODO: Test these +alter table workflow_initial_action alter column workflow_id set not null; +alter table workflow_roles alter column workflow_id set not null; + + +-- Changing from 'on delete cascade' to 'on delete set null' +-- TODO: Test these +alter table workflow_fsm_actions drop constraint wf_fsm_acns_new_st_fk; +alter table workflow_fsm_actions add + constraint wf_fsm_acns_new_st_fk foreign key (new_state) + references workflow_fsm_states(state_id) on delete set null; + +-- Adding recursive actions +-- TODO: Test these +create table workflow_action_children( + child_id integer + constraint wf_action_children_pk + primary key, + action_id integer + constraint wf_action_children_nn + not null + constraint wf_action_children_action_fk + references workflow_actions(action_id) + on delete cascade, + child_workflow integer + constraint wf_action_children_workflow_fk + references workflows(workflow_id) + on delete cascade +); + +create table workflow_action_child_role_map( + child_id integer + constraint wf_act_child_rl_map_child_fk + references workflow_action_children(child_id), + parent_role integer + constraint wf_act_child_rl_map_prnt_rl_fk + references workflow_roles(role_id), + child_role integer + constraint wf_act_child_rl_map_chld_rl_fk + references workflow_roles(role_id), + mapping_type char(40) + constraint wf_act_child_rl_map_type_ck + check (mapping_type in + ('per_role','per_user')), + constraint wf_act_chld_rl_map_pk + primary key (child_id, parent_role) +); + +comment on column workflow_action_child_role_map.mapping_type is ' + If per user, we create a child workflow per user who is a member of any of the parties assigned to the parent_role. + If per role, we create just one child workflow, with the exact same parties that are in the parent_role. + If more than one child_role has a mapping_type other than per_role, the cartesian product of these roles will be created. +'; Index: openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql,v diff -u -r1.20 -r1.21 --- openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql 5 Jan 2004 15:42:51 -0000 1.20 +++ openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql 8 Jan 2004 13:23:08 -0000 1.21 @@ -260,7 +260,46 @@ on delete cascade ); +create table workflow_action_children( + child_id integer + constraint wf_action_children_pk + primary key, + action_id integer + constraint wf_action_children_nn + not null + constraint wf_action_children_action_fk + references workflow_actions(action_id) + on delete cascade, + child_workflow integer + constraint wf_action_children_workflow_fk + references workflows(workflow_id) + on delete cascade +); +create table workflow_action_child_role_map( + child_id integer + constraint wf_act_child_rl_map_child_fk + references workflow_action_children(child_id), + parent_role integer + constraint wf_act_child_rl_map_prnt_rl_fk + references workflow_roles(role_id), + child_role integer + constraint wf_act_child_rl_map_chld_rl_fk + references workflow_roles(role_id), + mapping_type char(40) + constraint wf_act_child_rl_map_type_ck + check (mapping_type in + ('per_role','per_user')), + constraint wf_act_chld_rl_map_pk + primary key (child_id, parent_role) +); + +comment on column workflow_action_child_role_map.mapping_type is ' + If per user, we create a child workflow per user who is a member of any of the parties assigned to the parent_role. + If per role, we create just one child workflow, with the exact same parties that are in the parent_role. + If more than one child_role has a mapping_type other than per_role, the cartesian product of these roles will be created. +'; + --------------------------------- -- Workflow level, Finite State Machine Model --------------------------------- @@ -304,7 +343,7 @@ new_state integer constraint wf_fsm_acns_new_st_fk references workflow_fsm_states(state_id) - on delete cascade + on delete set null -- can be null ); Index: openacs-4/packages/workflow/sql/postgresql/upgrade/upgrade-1.2-2.0d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/workflow/sql/postgresql/upgrade/upgrade-1.2-2.0d1.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/workflow/sql/postgresql/upgrade/upgrade-1.2-2.0d1.sql 16 Dec 2003 18:18:49 -0000 1.3 +++ openacs-4/packages/workflow/sql/postgresql/upgrade/upgrade-1.2-2.0d1.sql 8 Jan 2004 13:23:08 -0000 1.4 @@ -56,3 +56,52 @@ -- New not null constraints alter table workflow_initial_action alter column workflow_id set not null; alter table workflow_roles alter column workflow_id set not null; + + +-- Changing from 'on delete cascade' to 'on delete set null' +alter table workflow_fsm_actions drop constraint wf_fsm_acns_new_st_fk; +alter table workflow_fsm_actions add + constraint wf_fsm_acns_new_st_fk foreign key (new_state) + references workflow_fsm_states(state_id) on delete set null; + + +-- Adding recursive actions +create table workflow_action_children( + child_id integer + constraint wf_action_children_pk + primary key, + action_id integer + constraint wf_action_children_nn + not null + constraint wf_action_children_action_fk + references workflow_actions(action_id) + on delete cascade, + child_workflow integer + constraint wf_action_children_workflow_fk + references workflows(workflow_id) + on delete cascade +); + +create table workflow_action_child_role_map( + child_id integer + constraint wf_act_child_rl_map_child_fk + references workflow_action_children(child_id), + parent_role integer + constraint wf_act_child_rl_map_prnt_rl_fk + references workflow_roles(role_id), + child_role integer + constraint wf_act_child_rl_map_chld_rl_fk + references workflow_roles(role_id), + mapping_type char(40) + constraint wf_act_child_rl_map_type_ck + check (mapping_type in + ('per_role','per_user')), + constraint wf_act_chld_rl_map_pk + primary key (child_id, parent_role) +); + +comment on column workflow_action_child_role_map.mapping_type is ' + If per user, we create a child workflow per user who is a member of any of the parties assigned to the parent_role. + If per role, we create just one child workflow, with the exact same parties that are in the parent_role. + If more than one child_role has a mapping_type other than per_role, the cartesian product of these roles will be created. +';