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 -N -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.
+';