-- -- Adding hierarchy, parallelism and timed actions -- -- @cvs-id $Id: upgrade-1.2-2.0d1.sql,v 1.14.2.2 2006/11/22 10:03:27 nimam Exp $ -- ---------------------------------------------------------------------- -- Fixing various problems and omissions with the old data model ---------------------------------------------------------------------- -- Loading old upgrades from other scripts. alter table workflows add description text; alter table workflows add description_mime_type varchar(200); alter table workflows alter column description_mime_type set default 'text/plain'; -- -- Fixes case deletion, which can now be done completely through cascading delete -- Also adds missing upgrade scripts from that bug fix -- -- @author Lars Pind (lars@collaboraid.biz) -- -- @cvs-id $Id: upgrade-1.2-2.0d1.sql,v 1.14.2.2 2006/11/22 10:03:27 nimam Exp $ create or replace function workflow_case_pkg__delete (integer) returns integer as ' declare delete_case_id alias for $1; rec record; begin -- All workflow data cascades from the case id delete from workflow_cases where case_id = delete_case_id; return 0; end;' language 'plpgsql'; create or replace function workflow_case_log_entry__new ( integer, -- entry_id varchar, -- content_type integer, -- case_id integer, -- action_id varchar, -- comment varchar, -- comment_mime_type integer, -- creation_user varchar -- creation_ip ) returns integer as ' declare p_item_id alias for $1; p_content_type alias for $2; p_case_id alias for $3; p_action_id alias for $4; p_comment alias for $5; p_comment_mime_type alias for $6; p_creation_user alias for $7; p_creation_ip alias for $8; v_name varchar; v_action_short_name varchar; v_action_pretty_past_tense varchar; v_case_object_id integer; v_item_id integer; v_revision_id integer; begin select short_name, pretty_past_tense into v_action_short_name, v_action_pretty_past_tense from workflow_actions where action_id = p_action_id; -- use case object as context_id select object_id into v_case_object_id from workflow_cases where case_id = p_case_id; -- build the unique name if p_item_id is not null then v_item_id := p_item_id; else select nextval into v_item_id from acs_object_id_seq; end if; v_name := v_action_short_name || '' '' || v_item_id; v_item_id := content_item__new ( v_item_id, -- item_id v_name, -- name v_case_object_id, -- parent_id v_action_pretty_past_tense, -- title now(), -- creation_date p_creation_user, -- creation_user v_case_object_id, -- context_id p_creation_ip, -- creation_ip ''t'', -- is_live p_comment_mime_type, -- mime_type p_comment, -- text ''text'', -- storage_type ''t'', -- security_inherit_p ''CR_FILES'', -- storage_area_key ''content_item'', -- item_subtype p_content_type -- content_type ); -- insert the row into the single-column entry revision table select content_item__get_live_revision (v_item_id) into v_revision_id; insert into workflow_case_log_rev (entry_rev_id) values (v_revision_id); -- insert into workflow-case-log insert into workflow_case_log (entry_id, case_id, action_id) values (v_item_id, p_case_id, p_action_id); -- return id of newly created item return v_item_id; end;' language 'plpgsql'; -- Now change parent_id of existing cases create or replace function inline_0() returns integer as ' declare rec record; begin for rec in select c.object_id, l.entry_id from workflow_cases c, workflow_case_log l where c.case_id = l.case_id loop update cr_items set parent_id = rec.object_id where item_id = rec.entry_id; end loop; return 0; end;' language 'plpgsql'; select inline_0(); drop function inline_0(); -- Missing unique constraints on names -- TODO: Test these alter table workflow_roles add constraint wf_roles_short_name_un unique (workflow_id, short_name); alter table workflow_roles add constraint wf_roles_pretty_name_un unique (workflow_id, pretty_name); alter table workflow_actions add constraint wf_actions_short_name_un unique (workflow_id, short_name); alter table workflow_actions add constraint wf_actions_pretty_name_un unique (workflow_id, pretty_name); 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 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 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_action_en_in_st_pk primary key (action_id, state_id); -- Workflow deputies should use timestamp, not date alter table workflow_deputies rename column start_date to start_date_old; alter table workflow_deputies rename column end_date to end_date_old; alter table workflow_deputies add start_date timestamptz; alter table workflow_deputies add end_date timestamptz; update workflow_deputies set start_date = start_date_old, end_date = end_date_old; alter table workflow_deputies alter column start_date set not null; alter table workflow_deputies alter column end_date set not null; alter table workflow_deputies drop column start_date_old cascade; alter table workflow_deputies drop column end_date_old cascade; -- adding foreign key/cascade and start/end date indices 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); -- 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 alter object_id drop not null; -- 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) constraint wf_acns_trigger_type_ck check (trigger_type in ('user','auto','init','time','message','parallel','workflow','dynamic')); alter table workflow_actions alter column trigger_type set default 'user'; 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 interval; -- 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_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', 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); -- Adding enabled action assignees table for dynamic actions 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_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 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. 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);