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.24 -r1.25
--- openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql	12 Jan 2004 10:56:59 -0000	1.24
+++ openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql	23 Jan 2004 11:02:27 -0000	1.25
@@ -147,41 +147,45 @@
 );
 
 create table workflow_actions (
-  action_id               integer
-                          constraint wf_acns_pk
-                          primary key,
-  workflow_id             integer
-                          constraint wf_acns_workflow_id_nn
-                          not null
-                          constraint wf_acns_workflow_id_fk
-                          references workflows(workflow_id)
-                          on delete cascade,
-  sort_order              integer
-                          constraint wf_acns_sort_order_nn
-                          not null,
-  short_name              varchar(100)
-                          constraint wf_acns_short_name_nn
-                          not null,
-  pretty_name             varchar(200)
-                          constraint wf_acns_pretty_name_nn
-                          not null,
-  pretty_past_tense       varchar(200),
-  description             text,
-  description_mime_type   varchar(200),
-  edit_fields             varchar(4000),
-  assigned_role           integer
-                          constraint wf_acns_assigned_role_fk
-                          references workflow_roles(role_id)
-                          on delete set null,
-  always_enabled_p        bool default 'f',
+  action_id                 integer
+                            constraint wf_acns_pk
+                            primary key,
+  workflow_id               integer
+                            constraint wf_acns_workflow_id_nn
+                            not null
+                            constraint wf_acns_workflow_id_fk
+                            references workflows(workflow_id)
+                            on delete cascade,
+  sort_order                integer
+                            constraint wf_acns_sort_order_nn
+                            not null,
+  short_name                varchar(100)
+                            constraint wf_acns_short_name_nn
+                            not null,
+  pretty_name               varchar(200)
+                            constraint wf_acns_pretty_name_nn
+                            not null,
+  pretty_past_tense         varchar(200),
+  description               text,
+  description_mime_type     varchar(200),
+  edit_fields               varchar(4000),
+  assigned_role             integer
+                            constraint wf_acns_assigned_role_fk
+                            references workflow_roles(role_id)
+                            on delete set null,
+  always_enabled_p          bool default 'f',
   -- When the action to automatically fire.
   -- A value of 0 means immediately, null means never.
   -- Other values mean x amount of time after having become enabled
-  timeout                 interval,
-  child_workflow_id       integer
-                          constraint wf_acns_child_workflow_fk
-                          references workflows(workflow_id)
-                          on delete cascade,
+  timeout                   interval,
+  parent_action_id          integer
+                            constraint wf_acns_parent_action_fk
+                            references workflow_actions(action_id)
+                            on delete cascade,
+  trigger_type              varchar(50)
+                            constraint wf_acns_trigger_type_ck
+                            check (trigger_type in ('user','auto','init','time','message','parallel','workflow','dynamic'))
+                            default 'user',
   constraint wf_actions_short_name_un
   unique (workflow_id, short_name),
   constraint wf_actions_pretty_name_un
@@ -248,80 +252,47 @@
   primary key (action_id, acs_sc_impl_id)
 );
 
--- For the initial action, which fires when a new case is started
-create table workflow_initial_action (
-  workflow_id             integer
-                          constraint wf_roles_workflow_id_nn
-                          not null
-                          constraint wf_initial_acn_pk
-                          primary key
-                          constraint wf_initial_acn_wf_fk
-                          references workflows(workflow_id)
-                          on delete cascade,
-  action_id               integer
-                          constraint wf_initial_acn_act_fk
-                          references workflow_actions(action_id)
-                          on delete cascade
-);
-
-create table workflow_action_child_role_map(
-  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)
-);
-
-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
 ---------------------------------
 
+create sequence workflow_fsm_states_seq;
+
 create table workflow_fsm_states (
-  state_id                integer
-                          constraint wf_fsm_states_pk
-                          primary key,
-  workflow_id             integer
-                          constraint wf_fsm_states_workflow_id_nn
-                          not null
-                          constraint wf_fsm_states_workflow_id_fk
-                          references workflows(workflow_id)
-                          on delete cascade,
-  sort_order              integer
-                          constraint wf_fsm_states_sort_order_nn
-                          not null,
+  state_id                  integer
+                            constraint wf_fsm_states_pk
+                            primary key,
+  workflow_id               integer
+                            constraint wf_fsm_states_workflow_id_nn
+                            not null
+                            constraint wf_fsm_states_workflow_id_fk
+                            references workflows(workflow_id)
+                            on delete cascade,
+  parent_action_id          integer
+                            constraint wf_fsm_states_parent_action_fk
+                            references workflow_actions(action_id)
+                            on delete cascade,
+  sort_order                integer
+                            constraint wf_fsm_states_sort_order_nn
+                            not null,
   -- The state with the lowest sort order is the initial state
-  short_name              varchar(100)
-                          constraint wf_fsm_states_short_name_nn
-                          not null,
-  pretty_name             varchar(200)
-                          constraint wf_fsm_states_pretty_name_nn
-                          not null,
-  hide_fields             varchar(4000),
+  short_name                varchar(100)
+                            constraint wf_fsm_states_short_name_nn
+                            not null,
+  pretty_name               varchar(200)
+                            constraint wf_fsm_states_pretty_name_nn
+                            not null,
+  hide_fields               varchar(4000),
   constraint wf_fsm_states_short_name_un
   unique (workflow_id, short_name),
   constraint wf_fsm_states_pretty_name_un
   unique (workflow_id, pretty_name)
 );
 
-create sequence workflow_fsm_states_seq;
+create index wf_fsm_states_workflow_idx on workflow_fsm_states(workflow_id);
+create index wf_fsm_states_prnt_action_idx on workflow_fsm_states(parent_action_id);
 
+
 create table workflow_fsm_actions (
   action_id               integer
                           constraint wf_fsm_acns_aid_fk
@@ -342,7 +313,7 @@
                           constraint wf_fsm_acn_enb_in_st_acn_id_nn
                           not null
                           constraint wf_fsm_acn_enb_in_st_acn_id_fk
-                          references workflow_fsm_actions(action_id)
+                          references workflow_actions(action_id)
                           on delete cascade,
   state_id                integer
                           constraint wf_fsm_acn_enb_in_st_st_id_nn
@@ -360,8 +331,9 @@
   primary key (action_id, state_id)
 );
 
+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);
 
-
 --------------------------------------------------------
 -- Workflow level, context-dependent (assignments, etc.)
 --------------------------------------------------------
@@ -425,16 +397,10 @@
   object_id                 integer
                             constraint wf_cases_object_id_fk
                             references acs_objects(object_id)
-                            on delete cascade,
-  -- the object which this case is about, e.g. the acs-object for a bug-tracker bug
-  top_case_id               integer
-                            constraint wf_cases_top_case_id_fk
-                            references workflow_cases(case_id)
                             on delete cascade
 );
 
 create index workflow_cases_workflow_id on workflow_cases (workflow_id);
-create index workflow_cases_top_case_id on workflow_cases (top_case_id);
 
 create table workflow_case_role_party_map (
   case_id                 integer
@@ -462,50 +428,50 @@
 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            timestamptz 
-                          default current_timestamp,
-  executed_date           timestamptz,
-  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          timestamptz
+  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,
+  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                boolean default 'f',
+  completed_p               boolean default 'f',
+  -- TOOD: trigger_type, assigned_role, use_action_assignees_p ...
+  execution_time            timestamptz
 );
 
 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);
+create index wf_case_enbl_act_parent_idx on workflow_case_enabled_actions(parent_enabled_action_id);
 
-create table workflow_case_parent_action(
-  case_id                 integer 
-                          constraint wf_case_child_cases_case_fk
-                          references workflow_cases
-                          constraint wf_case_child_cases_case_pk
-                          primary key,
-  parent_enabled_action_id
-                          integer
-                          constraint wf_case_child_cases_en_act_fk
-                          references workflow_case_enabled_actions
-                          constraint wf_case_child_cases_en_act_nn
-                          not null
+create table workflow_case_action_assignees(
+  enabled_action_id         integer
+                            constraint wf_case_actn_asgn_enbld_actn_fk
+                            references workflow_case_enabled_actions
+                            on delete cascade,
+  party_id                  integer
+                            constraint wf_case_actn_asgn_party_id_fk
+                            references parties(party_id)
+                            on delete cascade,
+  constraint wf_case_action_assignees_pk
+  primary key (enabled_action_id, party_id)
 );
 
-create index wf_cs_child_cs_en_act_idx on workflow_case_parent_action(parent_enabled_action_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);
 
 ---------------------------------
 -- Deputies
@@ -514,76 +480,55 @@
 -- When a user is away, for example on vacation, he
 -- can hand over his workflow roles to some other user - a deputy
 create table workflow_deputies (
+  -- user_id is the user that has a deputy, on whose behalf the deputy will operate
   user_id             integer
 		      constraint workflow_deputies_pk
 		      primary key
 		      constraint workflow_deputies_uid_fk
 		      references users(user_id),
+  -- deputy_user_id is the user taking over the other user's tasks
   deputy_user_id      integer
 		      constraint workflow_deputies_duid_fk
 		      references users(user_id),
-  start_date	      date
+  start_date	      timestamptz
 		      constraint workflow_deputies_sdate_nn
 		      not null,
-  end_date	      date
+  end_date	      timestamptz
 		      constraint workflow_deputies_edate_nn
 		      not null,
   message	      varchar(4000)
 );
 
--- role-to-user-map with deputies. Does not select users who
--- have deputies, should we do that?
-create view workflow_case_role_user_map as
-select distinct q.case_id,
-       q.role_id,
-       q.user_id,
-       q.on_behalf_of_user_id
-from (
-    select rpm.case_id,
-           rpm.role_id,
-           pmm.member_id as user_id,
-           pmm.member_id as on_behalf_of_user_id
-    from   workflow_case_role_party_map rpm, 
-           party_approved_member_map pmm,
-	   users u
-    where  rpm.party_id = pmm.party_id
-    and    pmm.member_id = u.user_id
-    and    not exists (select 1 
-                       from workflow_deputies 
-                       where user_id = pmm.member_id
-                       and now() between start_date and end_date)
-    union
-    select rpm.case_id,
-           rpm.role_id,
-           dep.deputy_user_id as user_id,
-           pmm.member_id as on_behalf_of_user_id
-    from   workflow_case_role_party_map rpm, 
-           party_approved_member_map pmm,
-	   users u,
-           workflow_deputies dep
-    where  rpm.party_id = pmm.party_id
-    and    pmm.member_id = u.user_id
-    and    dep.user_id = pmm.member_id
-    and    now() between dep.start_date and dep.end_date
-) q;
+create index workflow_deputies_deputy_idx on workflow_deputies(deputy_user_id);
+create index workflow_deputies_start_date_idx on workflow_deputies(start_date);
+create index workflow_deputies_end_date_idx on workflow_deputies(end_date);
 
+
 ---------------------------------
 -- Case level, Finite State Machine Model
 ---------------------------------
 
 create table workflow_case_fsm (
-  case_id                 integer
-                          constraint wf_case_fsm_case_id_nn
-                          not null
-                          constraint wf_case_fsm_case_id_fk
-                          references workflow_cases(case_id)
-                          on delete cascade,
-  current_state           integer
-                          constraint wf_case_fsm_st_id_fk
-                          references workflow_fsm_states(state_id)
-                          on delete cascade
+  case_id                   integer
+                            constraint wf_case_fsm_case_id_nn
+                            not null
+                            constraint wf_case_fsm_case_id_fk
+                            references workflow_cases(case_id)
+                            on delete cascade,
+  parent_enabled_action_id  integer
+                            constraint wf_case_fsm_action_id_fk
+                            references workflow_case_enabled_actions(enabled_action_id)
+                            on delete cascade,
+  current_state             integer
+                            constraint wf_case_fsm_st_id_fk
+                            references workflow_fsm_states(state_id)
+                            on delete cascade,
+  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 wf_case_fsm_state_idx on workflow_case_fsm(current_state);
 
 ---------------------------------
 -- Case level, Activity Log
@@ -636,19 +581,87 @@
 -- Useful views
 -----------------
 
-create view workflow_case_assigned_actions as 
+-- 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 coalesce(dep.deputy_user_id, u.user_id) as user_id,
+           u.user_id as on_behalf_of_user_id
+    from   users u left outer join
+           workflow_deputies dep on (dep.user_id = u.user_id and current_timestamp between start_date and 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.
+create or replace view workflow_case_assigned_actions as
     select c.workflow_id,
-           c.case_id, 
+           wcea.case_id,
            c.object_id,
-           a.action_id, 
-           a.assigned_role as role_id
-    from   workflow_cases c,
-           workflow_case_fsm cfsm,
-           workflow_actions a,
-           workflow_fsm_action_en_in_st aeis
-    where  cfsm.case_id = c.case_id
-    and    a.always_enabled_p = 'f'
-    and    aeis.state_id = cfsm.current_state
-    and    aeis.assigned_p = 't'
-    and    a.action_id = aeis.action_id
-    and    a.assigned_role is not null;
+           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 workflow_case_assigned_user_actions view.
+-- TODO: See if we can find a way to improve this without the union?
+create or replace view workflow_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 workflow_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   workflow_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;
