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.4 -r1.5
--- openacs-4/packages/workflow/sql/oracle/upgrade/upgrade-1.2-2.0d1.sql	8 Jan 2004 16:00:40 -0000	1.4
+++ openacs-4/packages/workflow/sql/oracle/upgrade/upgrade-1.2-2.0d1.sql	30 Jan 2004 09:46:04 -0000	1.5
@@ -1,52 +1,13 @@
 --
--- Adds timed actions, plus some missing delete cascade indices
+-- Adding hierarchy, parallelism and timed actions
 -- 
 -- @cvs-id $Id$
 --
 
+----------------------------------------------------------------------
+-- Fixing various problems and omissions with the old data model
+----------------------------------------------------------------------
 
-alter table workflow_actions add (timeout_seconds integer);
-
-
-create sequence workflow_case_enbl_act_seq;
-
-create table workflow_case_enabled_actions(
-  enabled_action_id       integer
-                          constraint wf_case_enbl_act_case_id_pk
-                          primary key,
-  case_id                 integer
-                          constraint wf_case_enbl_act_case_id_nn
-                          not null
-                          constraint wf_case_enbl_act_case_id_fk
-                          references workflow_cases(case_id)
-                          on delete cascade,
-  action_id               integer
-                          constraint wf_case_enbl_act_action_id_nn
-                          not null
-                          constraint wf_case_enbl_act_action_id_fk
-                          references workflow_actions(action_id)
-                          on delete cascade,
-  enabled_date            date
-                          default sysdate,
-  executed_date           date,
-  enabled_state           char(40)
-                          constraint wf_case_enbl_act_state_ck
-                          check (enabled_state in ('enabled','completed','canceled','refused')),
-  -- the timestamp when this action will fire
-  execution_time          date
-);
-
-create index wf_case_enbl_act_case_idx on workflow_case_enabled_actions(case_id);
-create index wf_case_enbl_act_action_idx on workflow_case_enabled_actions(action_id);
-create index wf_case_enbl_act_state_idx on workflow_case_enabled_actions(enabled_state);
-
-
--- Missing delete cascade index in Oracle
-
-create index workflow_case_log_action_id on workflow_case_log (action_id);
-create index workflow_case_log_case_id on workflow_case_log (case_id);
-
-
 -- Missing unique constraints on names
 -- TODO: Test these
 alter table workflow_roles add constraint wf_roles_short_name_un unique (workflow_id, short_name);
@@ -58,49 +19,239 @@
 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);
 
+-- Not bothering with the not null constraints for workflow_initial_action as we're dropping that table anyway
 
--- 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
-alter table workflow_actions add (
-  child_workflow_id       integer
-                          constraint wf_acns_child_workflow_fk
-                          references workflows(workflow_id)
-                          on delete cascade);
+-- Adding unique constraint on workflow fsm enabled in actions
+-- This could cause upgrades to fail, if there are in fact duplicates, so let's pray that there aren't
+alter table workflow_fsm_action_en_in_st add constraint workflow_fsm_acn_en_in_st_pk primary key (action_id, state_id);
 
-create table workflow_action_child_role_map(
+-- adding user_id and start/end date indices
+create index workflow_deputies_deputy_idx on workflow_deputies(deputy_user_id);
+create index workflow_deputies_sd_idx on workflow_deputies(start_date);
+create index workflow_deputies_ed_idx on workflow_deputies(end_date);
+
+-- TODO: This isn't strictly required, but might be useful, anyhow
+-- object_id can now be null, and doesn't have to be unique 
+-- (since we're going to have plenty of rows with null object_id)
+alter table workflow_cases drop constraint wf_cases_object_id_un;
+alter table workflow_cases drop constraint wf_cases_object_id_nn;
+
+-- Adding foreign key index on workflow_fsm_states
+create index wf_fsm_states_workflow_idx on workflow_fsm_states(workflow_id);
+
+-- Changing referential integrity constraint on workflow_fsm_action_en_in_st
+alter table workflow_fsm_action_en_in_st drop constraint wf_fsm_acn_enb_in_st_acn_id_fk;
+alter table workflow_fsm_action_en_in_st add foreign key (action_id) references workflow_actions(action_id) on delete cascade;
+
+-- Missing cascading delete indices
+create index wf_fsm_act_en_in_st_action_idx on workflow_fsm_action_en_in_st(action_id);
+create index wf_fsm_act_en_in_st_state_idx on workflow_fsm_action_en_in_st(state_id);
+
+----------------------------------------------------------------------
+-- Adding hierarchy and parallelism
+----------------------------------------------------------------------
+
+-- Adding hierarchical actions
+alter table workflow_actions add
+  parent_action_id          integer
+                            constraint wf_acns_parent_action_fk
+                            references workflow_actions(action_id)
+                            on delete cascade;
+
+-- Adding explicit trigger_type, for use with hierarchy and parallelism; replacing workflow_initial_action table
+alter table workflow_actions add
+  trigger_type              varchar(50) default 'user'
+                            constraint wf_acns_trigger_type_ck
+                            check (trigger_type in ('user','auto','init','time','message','parallel','workflow','dynamic'));
+update workflow_actions set trigger_type = 'user';
+update workflow_actions
+set    trigger_type = 'init'
+where  action_id in (select action_id 
+                     from   workflow_initial_action);
+drop table workflow_initial_action;
+
+-- Adding timeout for timed actions
+alter table workflow_actions add timeout_seconds integer;
+
+-- Add parent_action_id to states table
+alter table workflow_fsm_states add 
+  parent_action_id          integer
+                            constraint wf_fsm_states_parent_action_fk
+                            references workflow_actions(action_id)
+                            on delete cascade;
+create index wf_fsm_states_prnt_action_idx on workflow_fsm_states(parent_action_id);
+
+-- Adding enabled actions table to hold dynamic/parallel actions
+create sequence workflow_case_enbl_act_seq;
+create table workflow_case_enabled_actions(
+  enabled_action_id         integer
+                            constraint wf_case_enbl_act_case_id_pk
+                            primary key,
+  case_id                   integer
+                            constraint wf_case_enbl_act_case_id_nn
+                            not null
+                            constraint wf_case_enbl_act_case_id_fk
+                            references workflow_cases(case_id)
+                            on delete cascade,
   action_id                 integer
-                            constraint wf_act_child_rl_map_child_fk
-                            references workflow_actions(action_id),
-  child_role_id             integer
-                            constraint wf_act_child_rl_map_chld_rl_fk
-                            references workflow_roles(role_id),
-  parent_role_id            integer
-                            constraint wf_act_child_rl_map_prnt_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'))
-                            default 'per_role',
-  constraint wf_act_chld_rl_map_pk
-  primary key (action_id, child_role_id)
+                            constraint wf_case_enbl_act_action_id_nn
+                            not null
+                            constraint wf_case_enbl_act_action_id_fk
+                            references workflow_actions(action_id)
+                            on delete cascade,
+  parent_enabled_action_id  integer
+                            constraint wf_case_enbl_act_parent_id_fk
+                            references workflow_case_enabled_actions(enabled_action_id)
+                            on delete cascade,
+  assigned_p                char(1) default 'f'
+                            constraint wf_case_enbl_act_ap_ck
+                            check (assigned_p in ('t', 'f')),
+  completed_p               char(1) default 'f'
+                            constraint wf_case_enbl_act_cp_ck
+                            check (completed_p in ('t', 'f')),
+  execution_time            date
 );
 
-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.
-';
+create index wf_case_enbl_act_case_idx on workflow_case_enabled_actions(case_id);
+create index wf_case_enbl_act_action_idx on workflow_case_enabled_actions(action_id);
+
+-- Adding enabled action assignees table for dynamic actions
+create table workflow_case_action_assignees(
+  enabled_action_id         integer
+                            constraint wf_case_actn_asgn_eaid_fk
+                            references workflow_case_enabled_actions
+                            on delete cascade,
+  party_id                  integer
+                            constraint wf_case_actn_asgn_pid_fk
+                            references parties(party_id)
+                            on delete cascade,
+  constraint wf_case_action_assignees_pk
+  primary key (enabled_action_id, party_id)
+);
+
+create index wf_case_actn_asgn_en_act_idx on workflow_case_action_assignees(enabled_action_id);
+create index wf_case_actn_asgn_party_idx on workflow_case_action_assignees(party_id);
+
+-- A case now has multiple states, but only one per parent_action_id
+alter table workflow_case_fsm add
+  parent_enabled_action_id  integer
+                            constraint wf_case_fsm_action_id_fk
+                            references workflow_case_enabled_actions(enabled_action_id)
+                            on delete cascade;
+
+alter table workflow_case_fsm add
+  constraint wf_case_fsm_case_parent_un
+  unique (case_id, parent_enabled_action_id);
+
+create index wf_case_fsm_prnt_enbl_actn_idx on workflow_case_fsm(parent_enabled_action_id);
+create index workflow_case_fsm_state_idx on workflow_case_fsm(current_state);
+
+-- New and changed views
+
+-- Answers the question: Who is this user acting on behalf of? Which user is allowed to act on behalf of me?
+-- A mapping between users and their deputies
+create or replace view workflow_user_deputy_map as
+    select nvl(dep.deputy_user_id, u.user_id) as user_id,
+           u.user_id as on_behalf_of_user_id
+    from   users u,
+           workflow_deputies dep
+    where  u.user_id = dep.user_id (+)
+      and  (sysdate between dep.start_date and  dep.end_date);
+
+-- Answers the question: What are the enabled and assigned actions and which role are they assigned to?
+-- Useful for showing the task list for a particular user or role.
+-- Note that dynamic actions can very well be assigned even though they don't have an assigned_role;
+-- the assignees will be in workflow_case_action_assignees.
+drop view workflow_case_assigned_actions;
+create or replace view workflow_case_assigned_actions as
+    select c.workflow_id,
+           wcea.case_id,
+           c.object_id,
+           wcea.action_id,
+           wa.assigned_role as role_id,
+           wcea.enabled_action_id
+      from workflow_case_enabled_actions wcea,
+           workflow_actions wa,
+           workflow_cases c
+     where wcea.completed_p = 'f'
+       and wcea.assigned_p = 't'
+       and wa.action_id = wcea.action_id
+       and c.case_id = wcea.case_id;
+
+-- This view specifically answers the question: What are the actions assigned to this user?
+
+-- Answers the question: Which parties are currently assigned to which actions?
+-- Does not take deputies into account.
+-- Pimarily needed for building the wf_case_assigned_user_actions view.
+-- TODO: See if we can find a way to improve this without the union?
+create or replace view wf_case_assigned_party_actions as
+    select wcaa.enabled_action_id,
+           wcaa.action_id,
+           wcaa.case_id,
+           wcaasgn.party_id
+    from   workflow_case_assigned_actions wcaa,
+           workflow_case_action_assignees wcaasgn
+    where  wcaasgn.enabled_action_id = wcaa.enabled_action_id
+    union
+    select wcaa.enabled_action_id,
+           wcaa.action_id,
+           wcaa.case_id,
+           wcrpm.party_id
+    from   workflow_case_assigned_actions wcaa,
+           workflow_case_role_party_map wcrpm
+    where  wcrpm.role_id = wcaa.role_id
+    and    wcrpm.case_id = wcaa.case_id
+    and    not exists (select 1 
+                       from   workflow_case_action_assignees 
+                       where  enabled_action_id = wcaa.enabled_action_id);
+-- TODO: Above 'not exists' can be removed, if we store the assigned_role_id with the 
+-- workflow_case_enabled_actions table,
+-- and set it to null when assignment is dynamic like here
+
+
+
+-- Answers the question: which actions is this user assigned to?
+-- Does take deputies into account
+create or replace view wf_case_assigned_user_actions as
+    select wcapa.enabled_action_id,
+           wcapa.action_id,
+           wcapa.case_id,
+           wudm.user_id,
+           wudm.on_behalf_of_user_id
+    from   wf_case_assigned_party_actions wcapa,
+           party_approved_member_map pamm,
+           workflow_user_deputy_map wudm
+    where  pamm.party_id = wcapa.party_id
+    and    wudm.on_behalf_of_user_id = pamm.member_id;
+
+-- Answers the question: which roles is this user playing?
+-- Does take deputies into account
+create or replace view workflow_case_role_user_map as
+    select wcrpm.case_id,
+           wcrpm.role_id,
+           wudm.user_id,
+           wudm.on_behalf_of_user_id
+    from   workflow_case_role_party_map wcrpm,
+           party_approved_member_map pamm,
+           workflow_user_deputy_map wudm
+    where  pamm.party_id = wcrpm.party_id
+    and    wudm.on_behalf_of_user_id = pamm.member_id;
+
+-- pretty-name unique per parent, not per workflow
+alter table workflow_actions 
+  drop constraint wf_actions_pretty_name_un;
+alter table workflow_actions 
+  add constraint wf_actions_pretty_name_un
+  unique (workflow_id, parent_action_id, pretty_name);
+
+alter table workflow_fsm_states
+  drop constraint wf_fsm_states_pretty_name_un;
+alter table workflow_fsm_states
+  add constraint wf_fsm_states_pretty_name_un
+  unique (workflow_id, parent_action_id, pretty_name);
