Index: openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/workflow-case-package.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/workflow-case-package.sql,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/workflow-case-package.sql 9 Apr 2001 23:13:49 -0000 1.1 +++ openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/workflow-case-package.sql 10 Apr 2001 23:53:26 -0000 1.2 @@ -151,27 +151,28 @@ create function workflow_case__new (number,varchar,varchar,integer,timestamp,integer,varchar) returns integer as ' declare - case_id alias for $1; - workflow_key alias for $2; - context_key alias for $3; - object_id alias for $4; - creation_date alias for $5; - creation_user alias for $6; - creation_ip alias for $7; + new__case_id alias for $1; + new__workflow_key alias for $2; + new__context_key alias for $3; + new__object_id alias for $4; + new__creation_date alias for $5; + new__creation_user alias for $6; + new__creation_ip alias for $7; begin - if context_key is null then + if new__context_key is null then v_context_key_for_query := ''default''; else - v_context_key_for_query := context_key; + v_context_key_for_query := new__context_key; end if; /* insert a row into acs_objects */ - v_case_id := acs_object.new( - object_id => new__case_id, - object_type => new__workflow_key, - creation_date => new__creation_date, - creation_user => new__creation_user, - creation_ip => new__creation_ip + v_case_id := acs_object__new( + new__case_id, + new__workflow_key, + new__creation_date, + new__creation_user, + new__creation_ip, + null ); /* insert the case in to the general wf_cases table */ @@ -185,8 +186,7 @@ from acs_object_types where object_type = new__workflow_key; - execute ''insert into ''||v_workflow_case_table||'' (case_id) values (:1)'' - using v_case_id; + execute ''insert into '' || v_workflow_case_table || '' (case_id) values ('' || v_case_id || '')''; return v_case_id; @@ -197,250 +197,283 @@ create function workflow_case__add_manual_assignment (number,varchar,number) returns integer as ' declare - case_id alias for $1; - transition_key alias for $2; - party_id alias for $3; - v_workflow_key varchar(100); + add_manual_assignment__case_id alias for $1; + add_manual_assignment__transition_key alias for $2; + add_manual_assignment__party_id alias for $3; + v_workflow_key varchar; begin - select workflow_key into v_workflow_key from wf_cases where case_id = add_manual_assignment__case_id; + select workflow_key into v_workflow_key + from wf_cases + where case_id = add_manual_assignment__case_id; insert into wf_case_assignments (case_id, workflow_key, transition_key, party_id) values - (add_manual_assignment__case_id, v_workflow_key, add_manual_assignment__transition_key, add_manual_assignment__party_id); - return 0; + (add_manual_assignment__case_id, v_workflow_key, + add_manual_assignment__transition_key, + add_manual_assignment__party_id); + + return 0; end;' language 'plpgsql'; -- procedure remove_manual_assignment create function workflow_case__remove_manual_assignment (number,varchar,number) returns integer as ' declare - case_id alias for $1; - transition_key alias for $2; - party_id alias for $3; - v_workflow_key varchar(100); + remove_manual_assignment__case_id alias for $1; + remove_manual_assignment__transition_key alias for $2; + remove_manual_assignment__party_id alias for $3; begin - select workflow_key into v_workflow_key from wf_cases where case_id = remove_manual_assignment__case_id; - - delete from wf_case_assignments - where workflow_key = v_workflow_key - and case_id = remove_manual_assignment__case_id - and transition_key = remove_manual_assignment__transition_key - and party_id = remove_manual_assignment__party_id; - return 0; + delete + from wf_case_assignments + where workflow_key = (select workflow_key + from wf_cases + where case_id = remove_manual_assignment__case_id) + and case_id = remove_manual_assignment__case_id + and transition_key = remove_manual_assignment__transition_key + and party_id = remove_manual_assignment__party_id; + + return 0; end;' language 'plpgsql'; -- procedure clear_manual_assignments create function workflow_case__clear_manual_assignments (number,varchar) returns integer as ' declare - case_id alias for $1; - transition_key alias for $2; - v_workflow_key varchar(100); + clear_manual_assignments__case_id alias for $1; + clear_manual_assignments__transition_key alias for $2; begin - select workflow_key into v_workflow_key from wf_cases where case_id = clear_manual_assignments__case_id; - delete - from wf_case_assignments - where workflow_key = v_workflow_key - and case_id = clear_manual_assignments__case_id - and transition_key = clear_manual_assignments__transition_key; - return 0; + delete + from wf_case_assignments + where workflow_key = (select workflow_key + from wf_cases + where case_id = clear_manual_assignments__case_id) + and case_id = clear_manual_assignments__case_id + and transition_key = clear_manual_assignments__transition_key; + + return 0; end;' language 'plpgsql'; -- procedure start_case create function workflow_case__start_case (number,integer,varchar,varchar) returns integer as ' declare - case_id alias for $1; - creation_user alias for $2; - creation_ip alias for $3; - msg alias for $4; - v_journal_id number; + start_case__case_id alias for $1; + start_case__creation_user alias for $2; + start_case__creation_ip alias for $3; + start_case__msg alias for $4; + v_journal_id number; begin /* Add an entry to the journal */ - v_journal_id := journal_entry.new( - object_id => start_case__case_id, - action => ''case start'', - action_pretty => ''Case started'', - creation_user => start_case__creation_user, - creation_ip => start_case__creation_ip, - msg => start_case__msg + v_journal_id := journal_entry__new( + null, + start_case__case_id, + ''case start'', + ''Case started'', + start_case__creation_user, + start_case__creation_ip, + start_case__msg ); - update wf_cases set state = ''active'' where case_id = start_case__case_id; + update wf_cases + set state = ''active'' + where case_id = start_case__case_id; - add_token( - case_id => start_case__case_id, - place_key => ''start'', - journal_id => v_journal_id + PERFORM workflow_case__add_token( + start_case__case_id, + ''start'', + v_journal_id ); /* Turn the wheels */ - sweep_automatic_transitions( - case_id => start_case__case_id, - journal_id => v_journal_id + PERFORM workflow_case__sweep_automatic_transitions( + start_case__case_id, + v_journal_id ); - return 0; + + return 0; end;' language 'plpgsql'; -- procedure delete create function workflow_case__delete (number) returns integer as ' declare - case_id alias for $1; - v_workflow_case_table varchar(30); + delete__case_id alias for $1; + v_workflow_case_table varchar; begin /* delete attribute_value_audit, tokens, tasks */ - delete from wf_attribute_value_audit where case_id = workflow_case.delete__case_id; - delete from wf_case_assignments where case_id = workflow_case.delete__case_id; - delete from wf_case_deadlines where case_id = workflow_case.delete__case_id; - delete from wf_tokens where case_id = workflow_case.delete__case_id; - delete from wf_task_assignments where task_id in (select task_id from wf_tasks where case_id = workflow_case.delete__case_id); - delete from wf_tasks where case_id = workflow_case.delete__case_id; + delete from wf_attribute_value_audit + where case_id = delete__case_id; + delete from wf_case_assignments + where case_id = delete__case_id; + + delete from wf_case_deadlines + where case_id = delete__case_id; + + delete from wf_tokens + where case_id = delete__case_id; + + delete from wf_task_assignments + where task_id in (select task_id + from wf_tasks + where case_id = delete__case_id); + + delete from wf_tasks + where case_id = delete__case_id; + /* delete the journal */ - journal_entry.delete_for_object(workflow_case.delete__case_id); + PERFORM journal_entry__delete_for_object(delete__case_id); /* delete from the workflow-specific cases table */ select table_name into v_workflow_case_table from acs_object_types ot, wf_cases c - where c.case_id = workflow_case.delete__case_id + where c.case_id = delete__case_id and object_type = c.workflow_key; - execute ''delete from ''||v_workflow_case_table||'' where case_id = :case_id'' - using in workflow_case.delete__case_id; + execute ''delete from '' || v_workflow_case_table || '' where case_id = '' || delete__case_id; /* delete from the generic cases table */ - delete from wf_cases where case_id = workflow_case.delete__case_id; + delete from wf_cases where case_id = delete__case_id; /* delete from acs-objects */ - acs_object.delete(workflow_case.delete__case_id); - return 0; + PERFORM acs_object__delete(delete__case_id); + + return 0; end;' language 'plpgsql'; -- procedure suspend create function workflow_case__suspend (number,number,varchar,varchar) returns integer as ' declare - case_id alias for $1; - user_id alias for $2; - ip_address alias for $3; - msg alias for $4; - v_state varchar(40); - v_journal_id number; + suspend__case_id alias for $1; + suspend__user_id alias for $2; + suspend__ip_address alias for $3; + suspend__msg alias for $4; + v_state varchar; + v_journal_id number; begin select state into v_state from wf_cases where case_id = suspend__case_id; if v_state != ''active'' then - raise_application_error(-20000, ''Only active cases can be suspended''); + raise EXCEPTION ''-20000: Only active cases can be suspended''; end if; /* Add an entry to the journal */ - v_journal_id := journal_entry.new( - object_id => suspend__case_id, - action => ''case suspend'', - action_pretty => ''case suspended'', - creation_user => suspend__user_id, - creation_ip => suspend__ip_address, - msg => suspend__msg + v_journal_id := journal_entry__new( + null, + suspend__case_id, + ''case suspend'', + ''case suspended'', + now(), + suspend__user_id, + suspend__ip_address, + suspend__msg ); update wf_cases set state = ''suspended'' where case_id = suspend__case_id; - return 0; + + return 0; end;' language 'plpgsql'; -- procedure resume create function workflow_case__resume (number,number,varchar,varchar) returns integer as ' declare - case_id alias for $1; - user_id alias for $2; - ip_address alias for $3; - msg alias for $4; - v_state varchar(40); - v_journal_id number; + resume__case_id alias for $1; + resume__user_id alias for $2; + resume__ip_address alias for $3; + resume__msg alias for $4; + v_state varchar; + v_journal_id number; begin select state into v_state from wf_cases where case_id = resume__case_id; if v_state != ''suspended'' and v_state != ''canceled'' then - raise_application_error(-20000, ''Only suspended or canceled cases can be resumed''); + raise EXCEPTION ''-20000: Only suspended or canceled cases can be resumed''; end if; /* Add an entry to the journal */ v_journal_id := journal_entry.new( - object_id => resume__case_id, - action => ''case resume'', - action_pretty => ''case resumed'', - creation_user => resume__user_id, - creation_ip => resume__ip_address, - msg => resume__msg + null, + resume__case_id, + ''case resume'', + ''case resumed'', + now(), + resume__user_id, + resume__ip_address, + resume__msg ); update wf_cases set state = ''active'' where case_id = resume__case_id; - return 0; + + return 0; end;' language 'plpgsql'; -- procedure cancel create function workflow_case__cancel (number,number,varchar,varchar) returns integer as ' declare - case_id alias for $1; - user_id alias for $2; - ip_address alias for $3; - msg alias for $4; - v_state varchar(40); - v_journal_id number; + cancel__case_id alias for $1; + cancel__user_id alias for $2; + cancel__ip_address alias for $3; + cancel__msg alias for $4; + v_state varchar; + v_journal_id number; begin select state into v_state from wf_cases where case_id = cancel__case_id; if v_state != ''active'' and v_state != ''suspended'' then - raise_application_error(-20000, ''Only active or suspended cases can be canceled''); + raise EXCEPTION ''-20000: Only active or suspended cases can be canceled''; end if; /* Add an entry to the journal */ - v_journal_id := journal_entry.new( - object_id => cancel__case_id, - action => ''case cancel'', - action_pretty => ''Case canceled'', - creation_user => cancel__user_id, - creation_ip => cancel__ip_address, - msg => cancel__msg + v_journal_id := journal_entry__new( + null, + cancel__case_id, + ''case cancel'', + ''Case canceled'', + now(), + cancel__user_id, + cancel__ip_address, + cancel__msg ); update wf_cases set state = ''canceled'' where case_id = cancel__case_id; - return 0; + + return 0; end;' language 'plpgsql'; -- procedure fire_message_transition create function workflow_case__fire_message_transition (number) returns integer as ' declare - task_id alias for $1; - v_case_id number; - v_transition_name varchar(100); - v_trigger_type varchar(40); - v_journal_id number; + fire_message_transition__task_id alias for $1; + v_case_id number; + v_transition_name varchar; + v_trigger_type varchar; + v_journal_id number; begin select t.case_id, tr.transition_name, tr.trigger_type into v_case_id, v_transition_name, v_trigger_type @@ -450,81 +483,90 @@ and tr.transition_key = t.transition_key; if v_trigger_type != ''message'' then - raise_application_error(-20000, ''Transition ''''''||v_transition_name||'''''' is not message triggered''); + raise EXCEPTION ''-20000: Transition \\\''' || v_transition_name || ''\\\' is not message triggered''; end if; /* Add an entry to the journal */ - v_journal_id := journal_entry.new( - object_id => v_case_id, - action => ''task ''||fire_message_transition__task_id||'' fire'', - action_pretty => v_transition_name || '' fired'' + v_journal_id := journal_entry__new( + null, + v_case_id, + ''task '' || fire_message_transition__task_id || '' fire'', + v_transition_name || '' fired'', + now(), + null, + null, + null ); - fire_transition_internal( - task_id => fire_message_transition__task_id, - journal_id => v_journal_id + PERFORM workflow_case__fire_transition_internal( + fire_message_transition__task_id, + v_journal_id ); - sweep_automatic_transitions( - case_id => v_case_id, - journal_id => v_journal_id + PERFORM workflow_case__sweep_automatic_transitions( + v_case_id, + v_journal_id ); - return 0; + + return 0; end;' language 'plpgsql'; -- function begin_task_action create function workflow_case__begin_task_action (number,varchar,varchar,number,varchar) returns integer as ' declare - task_id alias for $1; - action alias for $2; - action_ip alias for $3; - user_id alias for $4; - msg alias for $5; + begin_task_action__task_id alias for $1; + begin_task_action__action alias for $2; + begin_task_action__action_ip alias for $3; + begin_task_action__user_id alias for $4; + begin_task_action__msg alias for $5; + v_state varchar; + v_journal_id number; + v_case_id number; + v_transition_name varchar; + v_num_rows number; begin select state into v_state from wf_tasks where task_id = begin_task_action__task_id; if begin_task_action__action = ''start'' then if v_state != ''enabled'' then - raise_application_error(-20000, ''Task is in state ''''''||v_state||'''''', ''|| - ''but it must be in state ''''enabled'''' to be started.''); + raise EXCEPTION ''-20000: Task is in state \\\'%\\\', but it must be in state \\\'enabled\\\' to be started.'', v_state_name; end if; - select decode(count(*),0,0,1) into v_num_rows + select case when count(*) = 0 then 0 else 1 end into v_num_rows from wf_user_tasks where task_id = begin_task_action__task_id and user_id = begin_task_action__user_id; if v_num_rows = 0 then - raise_application_error(-20000, ''You are not assigned to this task.''); + raise EXCEPTION ''-20000: You are not assigned to this task.''; end if; - elsif begin_task_action__action = ''finish'' or begin_task_action__action = ''cancel'' then + else if begin_task_action__action = ''finish'' or begin_task_action__action = ''cancel'' then if v_state = ''started'' then /* Is this user the holding user? */ - select decode(count(*),0,0,1) into v_num_rows + select case when count(*) = 0 then 0 else 1 end into v_num_rows from wf_tasks where task_id = begin_task_action__task_id and holding_user = begin_task_action__user_id; if v_num_rows = 0 then - raise_application_error(-20000, ''You are the user currently working on this task.''); + raise EXCEPTION ''-20000: You are the user currently working on this task.''; end if; - elsif v_state = ''enabled'' then + else if v_state = ''enabled'' then if begin_task_action__action = ''cancel'' then - raise_application_error(-20000, ''You can only cancel a task in state ''''started'''', ''|| - ''but this task is in state ''''''||v_state||''''''''); + raise EXCEPTION ''-20000: You can only cancel a task in state \\\'started\\\', but this task is in state \\\'%\\\''', v_state; end if; /* Is this user assigned to this task? */ - select decode(count(*),0,0,1) into v_num_rows + select case when count(*) = 0 then 0 else 1 end into v_num_rows from wf_user_tasks where task_id = begin_task_action__task_id and user_id = begin_task_action__user_id; if v_num_rows = 0 then - raise_application_error(-20000, ''You are not assigned to this task.''); + raise EXCEPTION ''-20000: You are not assigned to this task.''; end if; /* This task is finished without an explicit start. @@ -533,15 +575,14 @@ set holding_user = begin_task_action__user_id where task_id = begin_task_action__task_id; else - raise_application_error(-20000, ''Task is in state ''''''||v_state||'''''', ''|| - ''but it must be in state ''''enabled'''' or ''''started'''' to be finished''); - end if; + raise EXCEPTION ''-20000: Task is in state \\\'%\\\', but it must be in state \\\'enabled\\\' or \\\'started\\\' to be finished'', v_state_name; + end if; end if; - elsif begin_task_action__action = ''comment'' then + else if begin_task_action__action = ''comment'' then -- We currently allow anyone to comment on a task -- (need this line because PL/SQL doens''t like empty if blocks) v_num_rows := 0; - end if; + end if; end if; end if; select t.case_id, tr.transition_name into v_case_id, v_transition_name from wf_tasks t, @@ -552,13 +593,15 @@ /* Insert a journal entry */ - v_journal_id := journal_entry.new( - object_id => v_case_id, - action => ''task ''||begin_task_action__task_id||'' ''||begin_task_action__action, - action_pretty => v_transition_name || '' '' || begin_task_action__action, - creation_user => begin_task_action__user_id, - creation_ip => begin_task_action__action_ip, - msg => begin_task_action__msg + v_journal_id := journal_entry__new( + null, + v_case_id, + ''task '' || begin_task_action__task_id || '' '' || begin_task_action__action, + v_transition_name || '' '' || begin_task_action__action, + now(), + begin_task_action__user_id, + begin_task_action__action_ip, + begin_task_action__msg ); return v_journal_id; @@ -573,6 +616,7 @@ journal_id alias for $1; action alias for $2; task_id alias for $3; + v_user_id number; begin select creation_user into v_user_id from acs_objects @@ -581,42 +625,49 @@ /* Update the workflow state */ if end_task_action__action = ''start'' then - start_task(end_task_action__task_id, v_user_id, end_task_action__journal_id); - elsif end_task_action__action = ''finish'' then - finish_task(end_task_action__task_id, end_task_action__journal_id); - elsif end_task_action__action = ''cancel'' then - cancel_task(end_task_action__task_id, end_task_action__journal_id); - elsif end_task_action__action != ''comment'' then - raise_application_error(-20000, ''Unknown action '''''' || end_task_action__action || ''''''''); - end if; + PERFORM workflow_case__start_task(end_task_action__task_id, + v_user_id, + end_task_action__journal_id + ); + else if end_task_action__action = ''finish'' then + PERFORM workflow_case__finish_task(end_task_action__task_id, + end_task_action__journal_id + ); + else if end_task_action__action = ''cancel'' then + PERFORM workflow_case__cancel_task(end_task_action__task_id, + end_task_action__journal_id + ); + else if end_task_action__action != ''comment'' then + raise EXCEPTION ''-20000: Unknown action \\\'%\\\''', end_task_action__action; + end if; end if; end if; end if; - return 0; + return 0; end;' language 'plpgsql'; -- function task_action create function workflow_case__task_action (number,varchar,varchar,number,varchar) returns number as ' declare - task_id alias for $1; - action alias for $2; - action_ip alias for $3; - user_id alias for $4; - msg alias for $5; - v_journal_id integer; + task_action__task_id alias for $1; + task_action__action alias for $2; + task_action__action_ip alias for $3; + task_action__user_id alias for $4; + task_action__msg alias for $5; + v_journal_id integer; begin - v_journal_id := begin_task_action( - task_id => task_action__task_id, - action => task_action__action, - action_ip => task_action__action_ip, - user_id => task_action__user_id, - msg => task_action__msg + v_journal_id := workflow_case__begin_task_action( + task_action__task_id, + task_action__action, + task_action__action_ip, + task_action__user_id, + task_action__msg ); - end_task_action( - journal_id => v_journal_id, - action => task_action__action, - task_id => task_action__task_id + PERFORM workflow_case__end_task_action( + v_journal_id, + task_action__action, + task_action__task_id ); return v_journal_id; @@ -628,9 +679,12 @@ create function workflow_case__set_attribute_value (number,varchar,varchar) returns integer as ' declare - journal_id alias for $1; - attribute_name alias for $2; - value alias for $3; + set_attribute_value__journal_id alias for $1; + set_attribute_value__attribute_name alias for $2; + set_attribute_value__value alias for $3; + v_workflow_key varchar; + v_case_id number; + v_attribute_id number; begin select o.object_type, o.object_id into v_workflow_key, v_case_id from journal_entries je, acs_objects o @@ -642,30 +696,32 @@ where object_type = v_workflow_key and attribute_name = set_attribute_value__attribute_name; - acs_object.set_attribute( - object_id_in => v_case_id, - attribute_name_in => set_attribute_value__attribute_name, - value_in => set_attribute_value__value + PERFORM acs_object__set_attribute( + v_case_id, + set_attribute_value__attribute_name, + set_attribute_value__value ); insert into wf_attribute_value_audit (case_id, attribute_id, journal_id, attr_value) values - (v_case_id, v_attribute_id, set_attribute_value__journal_id, set_attribute_value__value); - return 0; + (v_case_id, v_attribute_id, set_attribute_value__journal_id, + set_attribute_value__value); + + return 0; end;' language 'plpgsql'; -- function get_attribute_value create function workflow_case__get_attribute_value (number,varchar) returns integer as ' declare - case_id alias for $1; - attribute_name alias for $2; + get_attribute_value__case_id alias for $1; + get_attribute_value__attribute_name alias for $2; begin - return acs_object.get_attribute( - object_id_in => get_attribute_value__case_id, - attribute_name_in => get_attribute_value__attribute_name + return acs_object__get_attribute( + get_attribute_value__case_id, + get_attribute_value__attribute_name ); end;' language 'plpgsql'; @@ -675,53 +731,27 @@ create function workflow_case__add_task_assignment (number,number) returns integer as ' declare - task_id alias for $1; - party_id alias for $2; - v_count integer; + add_task_assignment_task_id alias for $1; + add_task_assignment_party_id alias for $2; + v_count integer; v_workflow_key wf_workflows.workflow_key%TYPE; v_context_key wf_contexts.context_key%TYPE; v_case_id wf_cases.case_id%TYPE; v_transition_key wf_transitions.transition_key%TYPE; v_notification_callback wf_context_transition_info.notification_callback%TYPE; v_notification_custom_arg wf_context_transition_info.notification_custom_arg%TYPE; - - -- might - cursor c_new_assigned_users - select distinct - from users - where u.user_id - select distinct - from wf_task_assignments - party_approved_member_map m2, - users u2 - where tasgn2.task_id - and m2.party_id - and u2.user_id - and exists - select 1 - from party_approved_member_map - where m.member_id - and m.party_id - ); - cursor c_callback - select notification_callback, - notification_custom_arg - from wf_context_transition_info - where context_key - and workflow_key - and transition_key callback_rec c_callback%ROWTYPE; - + v_assigned_user record; begin - -- check that we don''t hit the unique constraint + -- check that we don not hit the unique constraint select count(*) into v_count from wf_task_assignments where task_id = add_task_assignment__task_id and party_id = add_task_assignment__party_id; if v_count > 0 then - return; + return null; end if; -- get the notification callback info @@ -736,10 +766,15 @@ from wf_cases where case_id = v_case_id; - open c_callback; - fetch c_callback into callback_rec; + select notification_callback, + notification_custom_arg into callback_rec + from wf_context_transition_info + where context_key = v_context_key + and workflow_key = v_workflow_key + and transition_key = v_transition_key; - if c_callback%FOUND then + + if FOUND then v_notification_callback := callback_rec.notification_callback; v_notification_custom_arg := callback_rec.notification_custom_arg; else @@ -749,12 +784,29 @@ -- notify any new assignees - for v_assigned_user in c_new_assigned_users loop - notify_assignee( - task_id => add_task_assignment__task_id, - user_id => v_assigned_user.user_id, - callback => v_notification_callback, - custom_arg => v_notification_custom_arg + for v_assigned_user in + select distinct u.user_id + from users u + where u.user_id not in ( + select distinct u2.user_id + from wf_task_assignments tasgn2, + party_approved_member_map m2, + users u2 + where tasgn2.task_id = add_task_assignment__task_id + and m2.party_id = tasgn2.party_id + and u2.user_id = m2.member_id) + and exists ( + select 1 + from party_approved_member_map m + where m.member_id = u.user_id + and m.party_id = add_task_assignment__party_id + ) + LOOP + PERFORM workflow_case__notify_assignee( + add_task_assignment__task_id, + v_assigned_user.user_id, + v_notification_callback, + v_notification_custom_arg ); end loop; @@ -766,29 +818,22 @@ (add_task_assignment__task_id, add_task_assignment__party_id); - return 0; + return 0; end;' language 'plpgsql'; -- procedure remove_task_assignment create function workflow_case__remove_task_assignment (number,number) returns integer as ' declare - task_id alias for $1; - party_id alias for $2; - v_num_assigned number; - v_case_id number; - v_workflow_key varchar(100); - v_transition_key varchar(100); - v_context_key varchar(100); - - cursor c_callback - select unassigned_callback, - from wf_context_transition_info - where workflow_key - and context_key - and transition_key - callback_rec c_callback%ROWTYPE; + remove_task_assignment__task_id alias for $1; + remove_task_assignment__party_id alias for $2; + v_num_assigned number; + v_case_id number; + v_workflow_key varchar; + v_transition_key varchar; + v_context_key varchar; + callback_rec c_callback%ROWTYPE; begin delete from wf_task_assignments @@ -800,7 +845,7 @@ where task_id = remove_task_assignment__task_id; if v_num_assigned > 0 then - return; + return null; end if; select case_id, transition_key @@ -813,33 +858,35 @@ from wf_cases where case_id = v_case_id; - open c_callback; - fetch c_callback into callback_rec; - - if c_callback%FOUND then - execute_unassigned_callback ( - callback => callback_rec.unassigned_callback, - task_id => task_id, - custom_arg => callback_rec.unassigned_custom_arg + select unassigned_callback, unassigned_custom_arg + into callback_rec + from wf_context_transition_info + where workflow_key = v_workflow_key + and context_key = v_context_key + and transition_key = v_transition_key; + if FOUND then + PERFORM workflow_case__execute_unassigned_callback ( + callback_rec.unassigned_callback, + task_id, + callback_rec.unassigned_custom_arg ); end if; - close c_callback; - return 0; + return 0; end;' language 'plpgsql'; -- procedure clear_task_assignments create function workflow_case__clear_task_assignments (number) returns integer as ' declare - task_id alias for $1; - v_case_id number; - v_transition_key varchar(100); - v_workflow_key varchar(100); - v_context_key varchar(100); - v_callback varchar(100); - v_custom_arg varchar(4000); + clear_task_assignments__task_id alias for $1; + v_case_id number; + v_transition_key varchar; + v_workflow_key varchar; + v_context_key varchar; + v_callback varchar; + v_custom_arg varchar; begin delete from wf_task_assignments @@ -862,47 +909,53 @@ and context_key = v_context_key and transition_key = v_transition_key; - execute_unassigned_callback ( - callback => v_callback, - task_id => task_id, - custom_arg => v_custom_arg + PERFORM workflow_case__execute_unassigned_callback ( + v_callback, + task_id, + v_custom_arg ); - return 0; + return 0; end;' language 'plpgsql'; -- function evaluate_guard create function workflow_case__evaluate_guard (varchar,varchar,number,varchar,varchar,varchar,varchar) -returns integer as ' +returns boolean as ' declare - callback alias for $1; - custom_arg alias for $2; - case_id alias for $3; - workflow_key alias for $4; - transition_key alias for $5; - place_key alias for $6; - direction alias for $7; + evaluate_guard__callback alias for $1; + evaluate_guard__custom_arg alias for $2; + evaluate_guard__case_id alias for $3; + evaluate_guard__workflow_key alias for $4; + evaluate_guard__transition_key alias for $5; + evaluate_guard__place_key alias for $6; + evaluate_guard__direction alias for $7; + v_guard_happy_p boolean; + v_rec record; begin - if callback is null then + if evaluate_guard__callback is null then -- null guard evaluates to true return ''t''; else - if callback = ''#'' then + if evaluate_guard__callback = ''#'' then return ''f''; else - execute ''begin :1 := '' || callback - || ''(:2, :3, :4, :5, :6, :7); end;'' - using out v_guard_happy_p, - in case_id, - in workflow_key, - in transition_key, - in place_key, - in direction, - in custom_arg; - return v_guard_happy_p; + for v_rec in + execute ''select '' || evaluate_guard__callback + || ''('' || + evaluate_guard__case_id || '','' || + evaluate_guard__workflow_key || '','' || + evaluate_guard__transition_key || '','' || + evaluate_guard__place_key || '','' || + evaluate_guard__direction || '','' || + evaluate_guard__custom_arg || '')'' + LOOP + return v_rec.guard_happy_p; + end LOOP; end if; end if; + + return null; end;' language 'plpgsql'; @@ -911,43 +964,45 @@ create function workflow_case__execute_transition_callback (varchar,varchar,number,varchar) returns integer as ' declare - callback alias for $1; - custom_arg alias for $2; - case_id alias for $3; - transition_key alias for $4; + execute_transition_callback__callback alias for $1; + execute_transition_callback__custom_arg alias for $2; + execute_transition_callback__case_id alias for $3; + execute_transition_callback__transition_key alias for $4; begin - if callback is not null then - execute ''begin ''||callback - || ''(:1, :2, :3); end;'' - using in case_id, - in transition_key, - in custom_arg; + if execute_transition_callback__callback is not null then + execute ''select '' || execute_transition_callback__callback + || ''('' || execute_transition_callback__case_id || '','' || + execute_transition_callback__transition_key || '','' || + execute_transition_callback__custom_arg '')'' end if; - return 0; + + return 0; end;' language 'plpgsql'; -- function execute_time_callback create function workflow_case__execute_time_callback (varchar,varchar,number,varchar) -returns integer as ' +returns timestamp as ' declare - callback alias for $1; - custom_arg alias for $2; - case_id alias for $3; - transition_key alias for $4; + execute_time_callback__callback alias for $1; + execute_time_callback__custom_arg alias for $2; + execute_time_callback__case_id alias for $3; + execute_time_callback__transition_key alias for $4; + v_rec record; begin - if callback is null then - raise_application_error(-20000, ''There''''s no time_callback function for the timed transition '''''' || transition_key || ''''''''); + if execute_time_callback__callback is null then + raise EXCEPTION ''-20000: There\\\'s no time_callback function for the timed transition \\\'%\\\''', execute_time_callback__transition_key; end if; - execute ''begin :1 := '' || callback - || ''(:2, :3, :4); end;'' - using out v_trigger_time, - in case_id, - in transition_key, - in custom_arg; - - return v_trigger_time; + for v_rec in execute ''select '' || execute_time_callback__callback || ''('' || + execute_time_callback__case_id || '','' || + execute_time_callback__transition_key || '','' || + execute_time_callback__custom_arg || '') as trigger_time'' + LOOP + return v_rec.trigger_time; + end LOOP; + + return null; end;' language 'plpgsql'; @@ -956,11 +1011,13 @@ create function workflow_case__get_task_deadline (varchar,varchar,varchar,number,varchar) returns integer as ' declare - callback alias for $1; - custom_arg alias for $2; - attribute_name alias for $3; - case_id alias for $4; - transition_key alias for $5; + get_task_deadline__callback alias for $1; + get_task_deadline__custom_arg alias for $2; + get_task_deadline__attribute_name alias for $3; + get_task_deadline__case_id alias for $4; + get_task_deadline__transition_key alias for $5; + v_deadline timestamp; + v_rec record; begin /* * 1. or if there''s a row in wf_case_deadlines, we use that @@ -969,26 +1026,32 @@ */ /* wf_case_deadlines */ - open case_deadline_cur; - fetch case_deadline_cur into v_deadline; - if case_deadline_cur%NOTFOUND then - if callback is not null then + select deadline into v_deadline + from wf_case_deadlines + where case_id = get_task_deadline__case_id + and transition_key = get_task_deadline__transition_key; + + if NOT FOUND then + if get_task_deadline__callback is not null then /* callback */ - execute ''begin :1 := '' || callback - || ''(:2, :3, :4); end;'' - using out v_deadline, - in case_id, - in transition_key, - in custom_arg; - elsif attribute_name is not null then + for v_rec in + execute ''select '' || get_task_deadline__callback || ''('' || + get_task_deadline__case_id || '','' || + get_task_deadline__transition_key || '','' || + get_task_deadline__custom_arg || '') as deadline'' + LOOP + v_deadline := v_rec.deadline; + exit; + end LOOP; + else if get_task_deadline__attribute_name is not null then /* attribute */ - v_deadline := acs_object.get_attribute( - object_id_in => get_task_deadline__case_id, - attribute_name_in => get_task_deadline__attribute_name + v_deadline := acs_object__get_attribute( + get_task_deadline__case_id, + get_task_deadline__attribute_name ); else v_deadline := null; - end if; + end if; end if; end if; return v_deadline; @@ -1000,23 +1063,28 @@ create function workflow_case__execute_hold_timeout_callback (varchar,varchar,number,varchar) returns integer as ' declare - callback alias for $1; - custom_arg alias for $2; - case_id alias for $3; - transition_key alias for $4; + execute_hold_timeout_callback__callback alias for $1; + execute_hold_timeout_callback__custom_arg alias for $2; + execute_hold_timeout_callback__case_id alias for $3; + execute_hold_timeout_callback__transition_key alias for $4; + v_hold_timeout timestamp; + v_rec record; begin - if callback is null then + if execute_hold_timeout_callback__callback is null then return null; end if; - execute ''begin :1 := '' || callback - || ''(:2, :3, :4); end;'' - using out v_hold_timeout, - in case_id, - in transition_key, - in custom_arg; - - return v_hold_timeout; + for v_rec in + execute ''select '' || execute_hold_timeout_callback__callback + || ''('' || + execute_hold_timeout_callback__case_id || '','' || + execute_hold_timeout_callback__transition_key || '','' || + execute_hold_timeout_callback__custom_arg || '') into hold_timeout'' + LOOP + return v_rec.hold_timeout; + end LOOP; + + return null; end;' language 'plpgsql'; @@ -1030,22 +1098,23 @@ custom_arg alias for $3; begin if callback is not null then - execute ''begin '' || callback - || ''(:1, :2); end;'' - using in task_id, - in custom_arg; + execute ''select '' || callback + || ''('' || task_id || '','' || custom_arg || '')''; end if; - return 0; + + return 0; end;' language 'plpgsql'; -- procedure set_task_assignments create function workflow_case__set_task_assignments (number,varchar,varchar) returns integer as ' declare - task_id alias for $1; - callback alias for $2; - custom_arg alias for $3; + set_task_assignments__task_id alias for $1; + set_task_assignments__callback alias for $2; + set_task_assignments__custom_arg alias for $3; + v_done_p boolean; + case_assignment_rec record; begin /* Find out who to assign the given task to. @@ -1059,41 +1128,56 @@ */ v_done_p := ''f''; - for case_assignment_rec in case_assignments loop + for case_assignment_rec in select party_id + from wf_case_assignments ca, wf_tasks t + where t.task_id = set_task_assignments__task_id + and ca.case_id = t.case_id + and ca.transition_key = t.transition_key + LOOP v_done_p := ''t''; - add_task_assignment ( - task_id => task_id, - party_id => case_assignment_rec.party_id + PERFORM workflow_case__add_task_assignment ( + task_id, + case_assignment_rec.party_id ); end loop; if v_done_p != ''t'' then - if callback is not null then - execute ''begin ''|| set_task_assignments__callback - || ''(:1, :2); end;'' - using in set_task_assignments__task_id, - in set_task_assignments__custom_arg; + if set_task_assignments__callback is not null then + execute ''select ''|| set_task_assignments__callback || ''('' || + set_task_assignments__task_id || '','' || + set_task_assignments__custom_arg || '')''; else - for context_assignment_rec in context_assignments loop - add_task_assignment ( - task_id => task_id, - party_id => context_assignment_rec.party_id + for context_assignment_rec in + select party_id + from wf_context_assignments ca, wf_cases c, wf_tasks t + where t.task_id = set_task_assignments__task_id + and c.case_id = t.case_id + and ca.context_key = c.context_key + and ca.workflow_key = t.workflow_key + and ca.transition_key = t.transition_key + LOOP + PERFORM workflow_case__add_task_assignment ( + task_id, + context_assignment_rec.party_id ); - end loop; + end LOOP; end if; end if; - return 0; + + return 0; end;' language 'plpgsql'; -- procedure add_token create function workflow_case__add_token (number,varchar,number) returns integer as ' declare - case_id alias for $1; - place_key alias for $2; - journal_id alias for $3; + add_token__case_id alias for $1; + add_token__place_key alias for $2; + add_token__journal_id alias for $3; + v_token_id number; + v_workflow_key varchar; begin select wf_token_id_seq.nextval into v_token_id from dual; @@ -1104,80 +1188,95 @@ insert into wf_tokens (token_id, case_id, workflow_key, place_key, state, produced_journal_id) values - (v_token_id, add_token__case_id, v_workflow_key, add_token__place_key, ''free'', add_token__journal_id); - return 0; + (v_token_id, add_token__case_id, v_workflow_key, add_token__place_key, + ''free'', add_token__journal_id); + + return 0; end;' language 'plpgsql'; -- procedure lock_token create function workflow_case__lock_token (number,varchar,number,number) returns integer as ' declare - case_id alias for $1; - place_key alias for $2; - journal_id alias for $3; - task_id alias for $4; + lock_token__case_id alias for $1; + lock_token__place_key alias for $2; + lock_token__journal_id alias for $3; + lock_token__task_id alias for $4; begin + -- FIXME: rownum +-- update wf_tokens +-- set state = ''locked'', +-- locked_task_id = lock_token__task_id, +-- locked_date = now(), +-- locked_journal_id = lock_token__journal_id +-- where case_id = lock_token__case_id +-- and place_key = lock_token__place_key +-- and state = ''free'' +-- and rownum = 1; + update wf_tokens set state = ''locked'', locked_task_id = lock_token__task_id, - locked_date = sysdate, + locked_date = now(), locked_journal_id = lock_token__journal_id where case_id = lock_token__case_id and place_key = lock_token__place_key - and state = ''free'' - and rownum = 1; - return 0; + and state = ''free''; + + return 0; end;' language 'plpgsql'; -- procedure release_token create function workflow_case__release_token (number,number) returns integer as ' declare - task_id alias for $1; - journal_id alias for $2; - cursor token_cur - select token_id, - case_id, - place_key - from wf_tokens - where state - and locked_task_id + release_token__task_id alias for $1; + release_token__journal_id alias for $2; + token_rec record; begin /* Add a new token for each released one */ - for token_rec in token_cur loop - add_token( - case_id => token_rec.case_id, - place_key => token_rec.place_key, - journal_id => release_token__journal_id + for token_rec in + select token_id, + case_id, + place_key + from wf_tokens + where state = ''locked'' + and locked_task_id = release_token__task_id + LOOP + PERFROM workflow_case__add_token( + token_rec.case_id, + token_rec.place_key, + release_token__journal_id ); end loop; /* Mark the released ones canceled */ update wf_tokens set state = ''canceled'', - canceled_date = sysdate, + canceled_date = now(), canceled_journal_id = release_token__journal_id where state = ''locked'' and locked_task_id = release_token__task_id; - return 0; + + return 0; end;' language 'plpgsql'; -- procedure consume_token create function workflow_case__consume_token (number,varchar,number,number) returns integer as ' declare - case_id alias for $1; - place_key alias for $2; - journal_id alias for $3; - task_id alias for $4; + consume_token__case_id alias for $1; + consume_token__place_key alias for $2; + consume_token__journal_id alias for $3; + consume_token__task_id alias for $4; begin if task_id is null then update wf_tokens set state = ''consumed'', - consumed_date = sysdate, + consumed_date = now(), consumed_journal_id = consume_token__journal_id where case_id = consume_token__case_id and place_key = consume_token__place_key @@ -1186,66 +1285,65 @@ else update wf_tokens set state = ''consumed'', - consumed_date = sysdate, + consumed_date = now(), consumed_journal_id = consume_token__journal_id where case_id = consume_token__case_id and place_key = consume_token__place_key and state = ''locked'' and locked_task_id = consume_token__task_id; end if; - return 0; + + return 0; end;' language 'plpgsql'; -- procedure sweep_automatic_transitions create function workflow_case__sweep_automatic_transitions (number,number) returns integer as ' declare - case_id alias for $1; - journal_id alias for $2; - cursor enabled_automatic_transitions - select task_id - from wf_tasks - where tr.workflow_key - and tr.transition_key - and tr.trigger_type - and ta.state - and ta.case_id - v_done_p char(1); - v_finished_p char(1); + sweep_automatic_transitions__case_id alias for $1; + sweep_automatic_transitions__journal_id alias for $2; + v_done_p boolean; + v_finished_p boolean; + task_rec record; begin - enable_transitions(case_id => sweep_automatic_transitions__case_id); + PERFORM workflow_case__enable_transitions(sweep_automatic_transitions__case_id); - loop + LOOP v_done_p := ''t''; - v_finished_p := finished_p( - case_id => sweep_automatic_transitions__case_id, - journal_id => sweep_automatic_transitions__journal_id); + v_finished_p := workflow_case__finished_p( + sweep_automatic_transitions__case_id, + sweep_automatic_transitions__journal_id); if v_finished_p = ''f'' then for task_rec in enabled_automatic_transitions loop - fire_transition_internal( - task_id => task_rec.task_id, - journal_id => sweep_automatic_transitions__journal_id + PERFORM workflow_case__fire_transition_internal( + task_rec.task_id, + sweep_automatic_transitions__journal_id ); v_done_p := ''f''; end loop; - enable_transitions(case_id => sweep_automatic_transitions__case_id); + PERFORM workflow_case__enable_transitions(sweep_automatic_transitions__case_id); end if; exit when v_done_p = ''t''; end loop; - return 0; + + return 0; end;' language 'plpgsql'; -- function finished_p create function workflow_case__finished_p (number,number) -returns integer as ' +returns booleanr as ' declare - case_id alias for $1; - journal_id alias for $2; + finished_p__case_id alias for $1; + finished_p__journal_id alias for $2; + v_case_state varchar; + v_token_id number; + v_num_rows number; + v_journal_id number; begin select state into v_case_state from wf_cases @@ -1255,7 +1353,7 @@ return ''t''; else /* Let''s see if the case is actually finished, but just not marked so */ - select decode(count(*),0,0,1) into v_num_rows + select case when count(*) = 0 then 0 else 1 end into v_num_rows from wf_tokens where case_id = finished_p__case_id and place_key = ''end''; @@ -1267,13 +1365,16 @@ * Count the total number of tokens to make sure the wf is well-constructed. */ - select decode(count(*),0,0,1,1,2) into v_num_rows + select case when count(*) = 0 then 0 + when count(*) = 1 then 1 + else 2 + end into v_num_rows from wf_tokens where case_id = finished_p__case_id and state in (''free'', ''locked''); if v_num_rows > 1 then - raise_application_error(-20000, ''The workflow net is misconstructed: Some parallel executions have not finished.''); + raise EXCEPTION ''-20000: The workflow net is misconstructed: Some parallel executions have not finished.''; end if; /* Consume that token */ @@ -1282,21 +1383,27 @@ where case_id = finished_p__case_id and state in (''free'', ''locked''); - consume_token( - case_id => finished_p__case_id, - place_key => ''end'', - journal_id => finished_p__journal_id + PERFORM workflow_case__consume_token( + finished_p__case_id, + ''end'', + finished_p__journal_id, + null ); update wf_cases set state = ''finished'' where case_id = finished_p__case_id; /* Add an extra entry to the journal */ - v_journal_id := journal_entry.new( + v_journal_id := journal_entry__new( + null, object_id => finished_p__case_id, action => ''case finish'', - action_pretty => ''Case finished'' + action_pretty => ''Case finished'', + now(), + null, + null, + null ); return ''t''; @@ -1310,19 +1417,19 @@ create function workflow_case__notify_assignee (integer,integer,<=>,varchar) returns integer as ' declare - task_id alias for $1; - user_id alias for $2; - callback alias for $3; - custom_arg alias for $4; - v_deadline_pretty varchar(400); - v_object_name varchar(4000); - v_transition_key wf_transitions.transition_key%TYPE; - v_transition_name wf_transitions.transition_name%TYPE; - v_party_from parties.party_id%TYPE; - v_party_to parties.party_id%TYPE; - v_subject varchar(4000); - v_body varchar(4000); - v_request_id integer; + notify_assignee__task_id alias for $1; + notify_assignee__user_id alias for $2; + notify_assignee__callback alias for $3; + notify_assignee__custom_arg alias for $4; + v_deadline_pretty varchar; + v_object_name text; + v_transition_key wf_transitions.transition_key%TYPE; + v_transition_name wf_transitions.transition_name%TYPE; + v_party_from parties.party_id%TYPE; + v_party_to parties.party_id%TYPE; + v_subject text; + v_body text; + v_request_id integer; begin select to_char(ta.deadline,''Mon fmDDfm, YYYY HH24:MI:SS''), acs_object.name(c.object_id), @@ -1340,7 +1447,7 @@ /* Default values */ v_party_from := -1; /* This default value should probably be pulled from somewhere */ - v_subject := ''Assignment: ''||v_transition_name||'' ''||v_object_name; + v_subject := ''Assignment: ''|| v_transition_name || '' '' || v_object_name; v_body := ''You have been assigned to a task. ''||'' Task : ''||v_transition_name||'' @@ -1355,23 +1462,27 @@ /* We''d like to add a URL to go visit, but how do we get that URL? */ /* The notifications should really be sent from the application server layer, not from the database */ + -- FIXME: last three args are also out varibles. + if notify_assignee__callback is not null then - execute ''begin ''||notify_assignee__callback||''(:1, :2, :3, :4, :5, :6); end;'' - using in notify_assignee__task_id, - in notify_assignee__custom_arg, - in notify_assignee__user_id, - in out v_party_from, - in out v_subject, - in out v_body; + execute ''select '' || notify_assignee__callback || ''('' || + notify_assignee__task_id || '','' || + notify_assignee__custom_arg || '','' || + notify_assignee__user_id || '','' || + v_party_from || '','' || + v_subject || '','' || + v_body || '')''; end if; - v_request_id := nt.post_request ( - party_from => v_party_from, - party_to => notify_assignee__user_id, - expand_group => ''f'' , - subject => v_subject, - message => v_body, - max_retries => 3 + -- FIXME: notifications package not ported yet. + + v_request_id := nt__post_request ( + v_party_from, + notify_assignee__user_id, + ''f'' , + v_subject, + v_body, + 3 ); return 0; @@ -1382,48 +1493,17 @@ create function workflow_case__enable_transitions (number) returns integer as ' declare - case_id alias for $1; - cursor tasks_to_create - select transition_key, - transition_name, - trigger_type, - enable_callback, - enable_custom_arg, - assignment_callback, - assignment_custom_arg, - time_callback, - time_custom_arg, - deadline_callback, - deadline_custom_arg, - deadline_attribute_name, - notification_callback, - notification_custom_arg, - unassigned_callback, - unassigned_custom_arg, - estimated_minutes - from wf_enabled_transitions - where et.case_id - and not - where case_id - and transition_key - and state - v_task_id number; - v_workflow_key varchar(100); - v_trigger_time timestamp; - v_deadline_date timestamp; - v_party_from integer; - v_subject varchar(500); - v_body varchar(4000); - v_num_assigned number; - request_id nt_requests.request_id%TYPE; - cursor assignees_cur - select distinct - from wf_task_assignments - party_approved_member_map m, - users u - where tasgn.task_id - and m.party_id - and u.user_id + enable_transitions__case_id alias for $1; + v_task_id number; + v_workflow_key varchar; + v_trigger_time timestamp; + v_deadline_date timestamp; + v_party_from integer; + v_subject varchar; + v_body text; + v_num_assigned number; + request_id nt_requests.request_id%TYPE; + trans_rec record; begin select workflow_key into v_workflow_key from wf_cases @@ -1433,7 +1513,7 @@ update wf_tasks set state = ''overridden'', - overridden_date = sysdate + overridden_date = now() where case_id = enable_transitions__case_id and state = ''enabled'' and transition_key not in @@ -1444,24 +1524,49 @@ /* insert a task for the transitions that are enabled but have no task row */ - for trans_rec in tasks_to_create loop + for trans_rec in select transition_key, + transition_name, + trigger_type, + enable_callback, + enable_custom_arg, + assignment_callback, + assignment_custom_arg, + time_callback, + time_custom_arg, + deadline_callback, + deadline_custom_arg, + deadline_attribute_name, + notification_callback, + notification_custom_arg, + unassigned_callback, + unassigned_custom_arg, + estimated_minutes + from wf_enabled_transitions et + where et.case_id = enable_transitions__case_id + and not exists (select 1 from wf_tasks + where case_id = enable_transitions__case_id + and transition_key = et.transition_key + and state in (''enabled'', ''started'')) + LOOP v_trigger_time := null; v_deadline_date := null; if trans_rec.trigger_type = ''user'' then - v_deadline_date := get_task_deadline( - callback => trans_rec.deadline_callback, - custom_arg => trans_rec.deadline_custom_arg, - attribute_name => trans_rec.deadline_attribute_name, - case_id => enable_transitions__case_id, - transition_key => trans_rec.transition_key + v_deadline_date := workflow_case__get_task_deadline( + trans_rec.deadline_callback, + trans_rec.deadline_custom_arg, + trans_rec.deadline_attribute_name, + enable_transitions__case_id, + trans_rec.transition_key ); - elsif trans_rec.trigger_type = ''time'' then - v_trigger_time := execute_time_callback(trans_rec.time_callback, - trans_rec.time_custom_arg, - enable_transitions__case_id, trans_rec.transition_key); - end if; + else if trans_rec.trigger_type = ''time'' then + v_trigger_time := workflow_case__execute_time_callback( + trans_rec.time_callback, + trans_rec.time_custom_arg, + enable_transitions__case_id, + trans_rec.transition_key); + end if; end if; /* we''re ready to insert the row */ select wf_task_id_seq.nextval into v_task_id from dual; @@ -1475,54 +1580,56 @@ v_deadline_date, v_trigger_time, trans_rec.estimated_minutes ); - set_task_assignments( - task_id => v_task_id, - callback => trans_rec.assignment_callback, - custom_arg => trans_rec.assignment_custom_arg + PERFORM workflow_case__set_task_assignments( + v_task_id, + trans_rec.assignment_callback, + trans_rec.assignment_custom_arg ); /* Execute the transition enabled callback */ - execute_transition_callback( - callback => trans_rec.enable_callback, - custom_arg => trans_rec.enable_custom_arg, - case_id => enable_transitions__case_id, - transition_key => trans_rec.transition_key + PERFORM workflow_case__execute_transition_callback( + trans_rec.enable_callback, + trans_rec.enable_custom_arg, + enable_transitions__case_id, + trans_rec.transition_key ); select count(*) into v_num_assigned from wf_task_assignments where task_id = v_task_id; if v_num_assigned = 0 then - execute_unassigned_callback ( - callback => trans_rec.unassigned_callback, - task_id => v_task_id, - custom_arg => trans_rec.unassigned_custom_arg + PERFORM workflow_case__execute_unassigned_callback ( + trans_rec.unassigned_callback, + v_task_id, + trans_rec.unassigned_custom_arg ); end if; end loop; - return 0; + + return 0; end;' language 'plpgsql'; -- procedure fire_transition_internal create function workflow_case__fire_transition_internal (number,number) returns integer as ' declare - task_id alias for $1; - journal_id alias for $2; - v_case_id number; - v_state varchar(40); - v_transition_key varchar(100); - v_workflow_key varchar(100); - v_place_key varchar(100); - v_direction varchar(3); - v_guard_happy_p char(1); - v_fire_callback varchar(100); - v_fire_custom_arg varchar(4000); - v_found_happy_guard char(1); - v_locked_task_id number; + fire_transition_internal__task_id alias for $1; + fire_transition_internal__journal_id alias for $2; + v_case_id number; + v_state varchar; + v_transition_key varchar; + v_workflow_key varchar; + v_place_key varchar; + v_direction varchar; + v_guard_happy_p boolean; + v_fire_callback varchar; + v_fire_custom_arg text; + v_found_happy_guard boolean; + v_locked_task_id number; + place_rec record; begin select t.case_id, t.state, t.workflow_key, t.transition_key, ti.fire_callback, ti.fire_custom_arg into v_case_id, v_state, v_workflow_key, v_transition_key, v_fire_callback, v_fire_custom_arg @@ -1537,55 +1644,65 @@ if v_state = ''enabled'' then v_locked_task_id := null; - elsif v_state = ''started'' then + else if v_state = ''started'' then v_locked_task_id := fire_transition_internal__task_id; else - raise_application_error(-20000, ''Can''''t fire the transition if it''''s not in state enabled or started''); - end if; + raise EXCEPTION ''-20000: Can\\\'t fire the transition if it\\\'s not in state enabled or started''; + end if; end if; /* Mark the task finished */ update wf_tasks set state = ''finished'', - finished_date = sysdate + finished_date = now() where task_id = fire_transition_internal__task_id; /* Consume the tokens */ - for place_rec in input_places(v_workflow_key, v_transition_key) loop - consume_token( - case_id => v_case_id, - place_key => place_rec.place_key, - journal_id => fire_transition_internal__journal_id, - task_id => v_locked_task_id + for place_rec in select * + from wf_transition_places tp + where tp.workflow_key = v_workflow_key + and tp.transition_key = v_transition_key + LOOP + PERFORM workflow_case__consume_token( + v_case_id, + place_rec.place_key, + fire_transition_internal__journal_id, + v_locked_task_id ); end loop; /* Spit out new tokens in the output places */ v_found_happy_guard := ''f''; - for place_rec in output_places(v_workflow_key, v_transition_key) loop + for place_rec in + select * + from wf_transition_places tp + where tp.workflow_key = v_workflow_key + and tp.transition_key = v_transition_key + and direction = ''out'' + LOOP v_place_key := place_rec.place_key; v_direction := place_rec.direction; - v_guard_happy_p := evaluate_guard( - callback => place_rec.guard_callback, - custom_arg => place_rec.guard_custom_arg, - case_id => v_case_id, - workflow_key => v_workflow_key, - transition_key => v_transition_key, - place_key => v_place_key, - direction => v_direction + v_guard_happy_p := workflow_case__evaluate_guard( + place_rec.guard_callback, + place_rec.guard_custom_arg, + v_case_id, + v_workflow_key, + v_transition_key, + v_place_key, + v_direction ); if v_guard_happy_p = ''t'' then v_found_happy_guard := ''t''; - add_token( - case_id => v_case_id, - place_key => place_rec.place_key, - journal_id => fire_transition_internal__journal_id + PERFORM workflow_case__add_token( + v_case_id, + place_rec.place_key, + fire_transition_internal__journal_id ); end if; end loop; @@ -1594,52 +1711,54 @@ /* If we didn''t find any happy guards, look for arcs with the special hash (#) guard */ if v_found_happy_guard = ''f'' then - for place_rec in ( + for place_rec in select place_key from wf_transition_places tp where tp.workflow_key = v_workflow_key and tp.transition_key = v_transition_key and tp.direction = ''out'' - and tp.guard_callback = ''#'') + and tp.guard_callback = ''#'' loop - add_token( - case_id => v_case_id, - place_key => place_rec.place_key, - journal_id => fire_transition_internal__journal_id + PERFORM workflow_case__add_token( + v_case_id, + place_rec.place_key, + fire_transition_internal__journal_id ); end loop; end if; /* Execute the transition fire callback */ - execute_transition_callback( - callback => v_fire_callback, - custom_arg => v_fire_custom_arg, - case_id => v_case_id, - transition_key => v_transition_key + PERFORM workflow_case__execute_transition_callback( + v_fire_callback, + v_fire_custom_arg, + v_case_id, + v_transition_key ); - return 0; + + return 0; end;' language 'plpgsql'; -- procedure ensure_task_in_state create function workflow_case__ensure_task_in_state (number,varchar) returns integer as ' declare - task_id alias for $1; - state alias for $2; - v_count number; + ensure_task_in_state__task_id alias for $1; + ensure_task_in_state__state alias for $2; + v_count number; begin - select decode(count(*),0,0,1) into v_count + select case when count(*) 0 then 0 else 1 end into v_count from wf_tasks where task_id = ensure_task_in_state__task_id and state = ensure_task_in_state__state; if v_count != 1 then - raise_application_error(-20000, ''The task ''|| ensure_task_in_state__task_id || '' is not in state '''''' || ensure_task_in_state__state || ''''''''); + raise EXCEPTION ''-20000: The task % is not in state \\\'%\\\''', ensure_task_in_state__task_id, ensure_task_in_state__state end if; - return 0; + + return 0; end;' language 'plpgsql'; Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 17 Mar 2001 16:00:40 -0000 1.2 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 10 Apr 2001 23:53:26 -0000 1.3 @@ -93,7 +93,7 @@ ''membership_rel'', acs__magic_object_id(''registered_users''), v_user_id, - member_state + member_state, null, null);