Index: openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/acs-workflow-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/acs-workflow-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/acs-workflow-create.sql 9 Apr 2001 23:13:49 -0000 1.1 @@ -0,0 +1,20 @@ +-- +-- acs-workflow/sql/acs-workflow-create.sql +-- +-- Calls the other SQL files to create the data models and PL/SQL packages. +-- +-- @author Lars Pind (lars@pinds.com) +-- +-- @creation-date 2000-05-18 +-- +-- @cvs-id $Id: acs-workflow-create.sql,v 1.1 2001/04/09 23:13:49 danw Exp $ +-- + +\i wf-core-create +\i workflow-case-package +\i workflow-package +\i wf-callback-package +\i jobs-start + +\i sample-expenses-create +\i sample-article-create Index: openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/wf-core-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/wf-core-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/wf-core-create.sql 9 Apr 2001 23:13:49 -0000 1.1 @@ -0,0 +1,827 @@ +-- +-- acs-workflow/sql/wf-core-create.sql +-- +-- Creates the data model and views for the workflow package. +-- +-- @author Lars Pind (lars@pinds.com) +-- +-- @creation-date 2000-05-18 +-- +-- @cvs-id $Id: wf-core-create.sql,v 1.1 2001/04/09 23:13:49 danw Exp $ +-- + +---------------------------------- +-- KNOWLEDGE LEVEL OBJECTS +---------------------------------- + + +/* Create the workflow superclass */ + +create function inline_0 () +returns integer as ' +begin + select acs_object_type__create_type ( + ''workflow'', + ''Workflow'', + ''Workflow'', + ''acs_object'', + ''wf_cases'', + ''case_id'', + null, + ''f'', + null, + null + ); + + return 0; +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); + + +-- show errors + +create table wf_workflows ( + workflow_key varchar(100) + constraint wf_workflows_pk + primary key + constraint wf_workflows_workflow_key_fk + references acs_object_types(object_type) + on delete cascade, + description text default '' not null +); + +comment on table wf_workflows is ' + Parent table for the workflow definition. +'; + +create table wf_places ( + place_key varchar(100) default '' not null, + workflow_key varchar(100) + constraint wf_place_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + place_name varchar(100) + constraint wf_place_name_nn + not null, + -- purely for UI purposes + sort_order integer + constraint wf_place_order_ck + check (sort_order > 0), + constraint wf_place_pk + primary key (workflow_key, place_key), + constraint wf_places_wf_key_place_name_un + unique (workflow_key, place_name) +); + +comment on table wf_places is ' + The circles of the petri net. These hold the tokens representing the overall + state of the workflow. +'; + +create table wf_transitions ( + transition_key varchar(100) default '' not null, + transition_name varchar(100) + constraint wf_transition_name_nn + not null, + workflow_key varchar(100) + constraint wf_transition_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + -- purely for UI purposes + sort_order integer + constraint wf_transition_order_ck + check (sort_order > 0), + trigger_type varchar(40) default '' not null + constraint wf_transition_trigger_type_ck + check (trigger_type in + ('','automatic','user','message','time')), + constraint wf_transition_pk + primary key (workflow_key, transition_key), + constraint wf_trans_wf_key_trans_name_un + unique (workflow_key, transition_name) +); + +comment on table wf_transitions is ' + The squares in the petri net. The things that somebody (or something) actually does. +'; + +create table wf_arcs ( + workflow_key varchar(100) + constraint wf_ts_arc_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + transition_key varchar(100) default '' not null, + place_key varchar(100) default '' not null, + -- direction is relative to the transition + direction varchar(3) default '' not null + constraint wf_arc_direction_ck + check (direction in ('','in','out')), + /* Must be satisfied for the arc to be traveled by a token + * This is the name of a PL/SQL function to execute, which must return t or f + * Signature: (case_id in number, workflow_key in varchar, transition_key in varchar2, + * place_key in varchar, direction in varchar2, custom_arg in varchar2) + * return char(1) + */ + guard_callback varchar(100), + guard_custom_arg text default '' not null, + guard_description varchar(500) default '' not null, + constraint wf_arc_guard_on_in_arc_ck + check (guard_callback is null or direction = 'out'), + constraint wf_arc_transition_fk + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) + on delete cascade, + constraint wf_arc_place_fk + foreign key (workflow_key, place_key) references wf_places(workflow_key, place_key) + on delete cascade, + constraint wf_arc_pk + primary key (workflow_key, transition_key, place_key, direction) +); + +create index wf_arcs_wf_key_trans_key_idx on wf_arcs(workflow_key, transition_key); +create index wf_arcs_wf_key_place_key_idx on wf_arcs(workflow_key, place_key); + +comment on table wf_arcs is ' + The arcs of the workflow petri net. + Arcs always go between a transition and a place. + The direction is relative to the transition here, i.e. + in means it goes into the transition, out means it goes + away from the transition. +'; + +create table wf_attribute_info ( + attribute_id integer + constraint wf_attribute_info_attribute_pk + primary key + constraint wf_attribute_info_attribute_fk + references acs_attributes(attribute_id) + on delete cascade, + wf_datatype varchar(50) default '' not null + constraint wf_attr_info_wf_datatype_ck + check (wf_datatype in ('', 'none', 'party')) +); + + +create table wf_transition_attribute_map ( + workflow_key varchar(100) + constraint wf_trans_attr_map_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + transition_key varchar(100) default '' not null, + -- so the user can decide in what order the attributes should be presented + sort_order integer not null, + attribute_id integer + constraint wf_trans_attr_map_attribute_fk + references acs_attributes, + constraint wf_trans_attr_map_pk + primary key (workflow_key, transition_key, attribute_id), + constraint wf_trans_attr_map_trans_fk + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) + on delete cascade +); + +comment on table wf_transition_attribute_map is ' + The workflow attributes that should be set when + the given transition is fired. +'; + + +create table wf_transition_assignment_map ( + workflow_key varchar(100) + constraint wf_trans_asgn_map_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + transition_key varchar(100) default '' not null, + assign_transition_key varchar(100) default '' not null, + constraint wf_trans_asgn_map_pk + primary key (workflow_key, transition_key, assign_transition_key), + constraint wf_trans_asgn_map_trans_fk + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) + on delete cascade, + constraint wf_tr_asgn_map_asgn_trans_fk + foreign key (workflow_key, assign_transition_key) references wf_transitions(workflow_key, transition_key) + on delete cascade +); + +create index wf_trans_asgn_map_wf_trans_idx on wf_transition_assignment_map(workflow_key, transition_key); +create index wf_trans_asgn_map_wf_as_tr_idx on wf_transition_assignment_map(workflow_key, assign_transition_key); + +comment on table wf_transition_assignment_map is ' + When part of the output of one task is to assign users to another task, + specify that this is the case by inserting a row here. +'; + +comment on column wf_transition_assignment_map.transition_key is ' + transition_key is the assigning transition. +'; + +comment on column wf_transition_assignment_map.assign_transition_key is ' + transition_key is the transition being assigned a user to. +'; + + + + + +* + * Contexts + */ + +create table wf_contexts ( + context_key varchar(100) + constraint wf_context_pk + primary key, + context_name varchar(100) + constraint wf_contexts_context_name_nn + not null + constraint wf_contexts_context_name_un + unique +); + +comment on table wf_contexts is ' + The context of a workflow holds everything that''s not directly + part of the Petri Net structure, the stuff that''s likely to + be changed as the workflow is applied in a real business, and that + you will want to customize across different departments of the + same business. It includes assignments of transitions to parties, + the call-backs, etc. +'; + +* Insert a default context that all new cases will use if nothing else is defined */ +insert into wf_contexts (context_key, context_name) values ('default', 'Default Context'); + +commit; + + +create table wf_context_transition_info ( + context_key varchar(100) + constraint wf_context_trans_context_fk + references wf_contexts, + workflow_key varchar(100) + constraint wf_context_trans_workflow_fk + references wf_workflows, + transition_key varchar(100) default '' not null, + /* information for the transition in the context */ + /* The number of minutes this task is estimated to take */ + estimated_minutes integer, + /* + * Will be called when the transition is enabled/fired. + * signature: (case_id in number, transition_key in varchar, custom_arg in varchar2) + */ + enable_callback varchar(100) default '' not null, + enable_custom_arg text default '' not null, + fire_callback varchar(100) default '' not null, + fire_custom_arg text default '' not null, + /* + * Must insert rows into the wf_task_assignments table. + * Will be called when the transition becomes enabled + * signature: (task_id in number, custom_arg in varchar) + */ + assignment_callback varchar(100) default '' not null, + assignment_custom_arg text default '' not null, + /* + * Must return the date that the timed transition should fire + * Will be called when the transition is enabled + * signature: (case_id in number, transition_key in varchar, custom_arg in varchar2) return date + */ + time_callback varchar(100) default '' not null, + time_custom_arg text default '' not null, + /* + * Returns the deadline for this task. + * Will be called when the transition becomes enabled + * Signature: (case_id in number, transition_key in varchar, custom_arg in varchar2) return date + */ + deadline_callback varchar(100) default '' not null, + deadline_custom_arg text default '' not null, + /* The name of an attribute that holds the deadline */ + deadline_attribute_name varchar(100) default '' not null, + /* + * Must return the date that the user's hold on the task times out. + * called when the user starts the task. + * signature: (case_id in number, transition_key in varchar, custom_arg in varchar2) return date + */ + hold_timeout_callback varchar(100) default '' not null, + hold_timeout_custom_arg text default '' not null, + /* + * Notification callback + * Will be called when a notification is sent i.e., when a transition is enabled, + * or assignment changes. + * signature: (task_id in number, + * custom_arg in varchar, + * party_to in integer, + * party_from in out integer, + * subject in out varchar, + * body in out varchar) + */ + notification_callback varchar(100) default '' not null, + notification_custom_arg text default '' not null, + /* + * Unassigned callback + * Will be called whenever a task becomes unassigned + * Signature: (case_id in number, transition_key in varchar, custom_arg in varchar2) + */ + unassigned_callback varchar(100) default '' not null, + unassigned_custom_arg text default '' not null, + /* name of the privilege we should check before allowing access + * to task information. + */ + access_privilege text default '' not null, + /* table constraints */ + constraint wf_context_trans_trans_fk + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) + on delete cascade, + constraint wf_context_transition_pk + primary key (context_key, workflow_key, transition_key) +); + +create index wf_ctx_trans_wf_trans_idx on wf_context_transition_info(workflow_key, transition_key); + +comment on table wf_context_transition_info is ' + This table holds information that pertains to a transition in a specific context. + It will specifically hold +'; + + +create table wf_context_task_panels ( + context_key varchar(100) not null + constraint wf_context_panels_context_fk + references wf_contexts(context_key) + on delete cascade, + workflow_key varchar(100) not null + constraint wf_context_panels_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + transition_key varchar(100) not null, + sort_key integer not null, + header varchar(200) not null, + template_url varchar(500) not null, + /* table constraints */ + constraint wf_context_panels_trans_fk + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) + on delete cascade, + constraint wf_context_panels_pk + primary key (context_key, workflow_key, transition_key, sort_key) +); + +create index wf_ctx_panl_workflow_trans_idx on wf_context_task_panels(workflow_key, transition_key); + +comment on table wf_context_task_panels is ' + Holds information about the panels to be displayed on the task page. +'; + + + +create table wf_context_assignments ( + context_key varchar(100) + constraint wf_context_assign_context_fk + references wf_contexts(context_key) + on delete cascade, + workflow_key varchar(100) + constraint wf_context_assign_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + transition_key varchar(100) default '' not null, + party_id integer + constraint wf_context_assign_party_fk + references parties(party_id) + on delete cascade, + /* table constraints */ + constraint wf_context_assign_trans_fk + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) + on delete cascade, + constraint wf_context_assign_pk + primary key (context_key, workflow_key, transition_key, party_id) +); + +create index wf_ctx_assg_workflow_trans_idx on wf_context_assignments(workflow_key, transition_key); + +comment on table wf_context_assignments is ' + Static assignment at the context level. +'; + + + +------------------------------------ +-- OPERATIONAL LEVEL OBJECTS +------------------------------------ + +create table wf_cases ( + case_id integer + constraint wf_cases_pk + primary key + constraint wf_cases_acs_object_fk + references acs_objects(object_id) + on delete cascade, + workflow_key varchar(100) + constraint wf_cases_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + context_key varchar(100) + constraint wf_cases_context_fk + references wf_contexts(context_key) + on delete cascade, + object_id integer constraint wf_cases_object_fk + references acs_objects(object_id) + on delete cascade, + -- a toplevel state of the case + state varchar(40) + default 'created' + constraint wf_cases_state_ck + check (state in ('created', + 'active', + 'suspended', + 'canceled', + 'finished')) +); + +create index wf_cases_workflow_key_idx on wf_cases(workflow_key); +create index wf_cases_context_key_idx on wf_cases(context_key); +create index wf_cases_object_id_idx on wf_cases(object_id); + +comment on table wf_cases is ' + The instance of a workflow, the actual object we''re concerned with in this + workflow. +'; + +comment on column wf_cases.object_id is ' + A case is itself an acs_object, but moreover, a case will always be about some + other acs_object. E.g. for ticket-tracker, the case_id will refer to an instance of + the ticket-tracker-workflow, while the object_id will refer to the ticket itself. + It is possible to have multiple cases around the same object. +'; + + + + +create table wf_case_assignments ( + case_id integer + constraint wf_case_assign_fk + references wf_cases(case_id) + on delete cascade, + workflow_key varchar(100) default '' not null, + transition_key varchar(100) default '' not null, + party_id integer + constraint wf_case_assign_party_fk + references parties(party_id) + on delete cascade, + constraint wf_case_assign_trans_fk + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key), + constraint wf_case_assign_pk + primary key (case_id, transition_key, party_id) +); + +create index wf_case_assgn_party_idx on wf_case_assignments(party_id); + +comment on table wf_case_assignments is ' + Manual assignment at the per-case level. +'; + + +create table wf_case_deadlines ( + case_id integer + constraint wf_case_deadline_fk + references wf_cases(case_id) + on delete cascade, + workflow_key varchar(100) default '' not null, + transition_key varchar(100) default '' not null, + deadline timestamp + constraint wf_case_deadline_nn + not null, + constraint wf_case_deadline_trans_fk + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key), + constraint wf_case_deadline_pk + primary key (case_id, transition_key) +); + + +comment on table wf_case_deadlines is ' + Manual deadlines for the individual transitions (tasks) on a per-case basis. +'; + + + +create sequence t_wf_task_id_seq; +create view wf_task_id_seq as +select nextval('t_wf_task_id_seq') as nextval; + +create table wf_tasks ( + task_id integer + constraint wf_task_pk + primary key, + case_id integer + constraint wf_task_case_fk + references wf_cases + on delete cascade, + workflow_key varchar(100) + constraint wf_task_workflow_fk + references wf_workflows(workflow_key), + transition_key varchar(100) default '' not null, + /* Information about the task */ + state varchar(40) + default 'enabled' + constraint wf_task_state_ck + check (state in ('enabled','started','canceled', + 'finished','overridden')), + enabled_date timestamp default now(), + started_date timestamp, + canceled_date timestamp, + finished_date timestamp, + overridden_date timestamp, + /* -- TIME transition info */ + trigger_time timestamp, + /* -- USER transition info */ + deadline timestamp, + estimated_minutes integer, + holding_user integer + constraint wf_task_holding_user_fk + references users(user_id) + on delete cascade, + hold_timeout timestamp, + /* -- */ + constraint wf_task_transition_fk + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) +); + +create index wf_tasks_case_id_idx on wf_tasks(case_id); + +create index wf_tasks_holding_user_idx on wf_tasks(holding_user); + +comment on table wf_tasks is ' + The tasks that need to be done, who can do it, and what state it''s in. + A task is the instance of a transition. +'; + +create table wf_task_assignments ( + task_id integer + constraint wf_task_assign_task_fk + references wf_tasks(task_id) + on delete cascade, + party_id integer + constraint wf_task_party_fk + references parties(party_id) + on delete cascade, + constraint wf_task_assignments_pk + primary key (task_id, party_id) +); + +create index wf_task_asgn_party_id_idx on wf_task_assignments(party_id); + +create sequence t_wf_token_id_seq; +create view wf_token_id_seq as +select nextval('t_wf_token_id_seq') as nextval; + +create table wf_tokens ( + token_id integer + constraint wf_token_pk + primary key, + case_id integer + constraint wf_token_workflow_instance_fk + references wf_cases(case_id) + on delete cascade, + workflow_key varchar(100) + constraint wf_token_workflow_fk + references wf_workflows(workflow_key), + -- a token must always be in some place + place_key varchar(100) default '' not null, + state varchar(40) default 'free' + constraint wf_tokens_state_ck + check (state in ('free', 'locked', 'canceled', 'consumed')), + -- when the token is locked, by which task + locked_task_id integer + constraint wf_token_task_fk + references wf_tasks(task_id), + -- info on state changes + produced_date timestamp default now(), + locked_date timestamp, + canceled_date timestamp, + consumed_date timestamp, + produced_journal_id integer + constraint wf_token_produced_journal_fk + references journal_entries(journal_id), + locked_journal_id integer + constraint wf_token_locked_journal_fk + references journal_entries(journal_id), + canceled_journal_id integer + constraint wf_token_canceled_journal_fk + references journal_entries(journal_id), + consumed_journal_id integer + constraint wf_token_consumed_journal_fk + references journal_entries(journal_id), + constraint wf_token_place_fk + foreign key (workflow_key, place_key) references wf_places(workflow_key, place_key) +); + +create index wf_tokens_case_id_idx on wf_tokens(case_id); + +comment on table wf_tokens is ' + Where the tokens currently are, and what task is laying hands on it, if any. + A token is sort of the instance of a place, except there''ll be one row here per + token, and there can be more than one token per place. +'; + + +* Should evetually be done by acs_objects automatically */ + +create table wf_attribute_value_audit ( + case_id integer + constraint wf_attr_val_audit_case_fk + references wf_cases(case_id) + on delete cascade, + attribute_id integer + constraint wf_attr_val_audit_attr_fk + references acs_attributes(attribute_id) + on delete cascade, + journal_id integer + constraint wf_attr_val_audit_journal_fk + references journal_entries(journal_id), + attr_value text default '' not null, + constraint wf_attr_val_audit_pk + primary key (case_id, attribute_id, journal_id) +); + +create index wf_attr_val_aud_attr_id_idx on wf_attribute_value_audit(attribute_id); + +comment on table wf_attribute_value_audit is ' + This table holds all the attribute values that has been set, + so we can track changes over the lifetime of a case. +'; + + +* + * This is the cartesian product of transitions and contexts. + * We need this in order to compute the following wf_transition_info view, + * because Oracle won't let us outer join against more than one table. + */ +create view wf_transition_contexts as +select t.transition_key, + t.transition_name, + t.workflow_key, + t.sort_order, + t.trigger_type, + c.context_key, + c.context_name +from wf_transitions t, wf_contexts c; + + + +* + * Returns all the information stored about a certain transition + * in all contexts. You'll usually want to use this with a + * "where context = " clause. + */ +create view wf_transition_info as +select t.transition_key, + t.transition_name, + t.workflow_key, + t.sort_order, + t.trigger_type, + t.context_key, + ct.estimated_minutes, + ct.enable_callback, + ct.enable_custom_arg, + ct.fire_callback, + ct.fire_custom_arg, + ct.assignment_callback, + ct.assignment_custom_arg, + ct.time_callback, + ct.time_custom_arg, + ct.deadline_callback, + ct.deadline_custom_arg, + ct.deadline_attribute_name, + ct.hold_timeout_callback, + ct.hold_timeout_custom_arg, + ct.notification_callback, + ct.notification_custom_arg, + ct.unassigned_callback, + ct.unassigned_custom_arg, + ct.access_privilege +from wf_transition_contexts t LEFT OUTER JOIN wf_context_transition_info ct +on (ct.workflow_key = t.workflow_key and + ct.transition_key = t.transition_key and + ct.context_key = t.context_key); + + + +* + * This view makes it easy to get the input/output places of a transition + */ +create view wf_transition_places as +select a.workflow_key, + t.transition_key, + p.place_key, + p.place_name, + p.sort_order, + a.direction, + a.guard_callback, + a.guard_custom_arg, + a.guard_description +from wf_arcs a, wf_places p, wf_transitions t +where a.transition_key = t.transition_key +and a.workflow_key = t.workflow_key +and p.place_key = a.place_key +and p.workflow_key = a.workflow_key; + + +* + * This view returns information about all currently enabled transitions. + * It does not include transitions that are started. This information, along + * with additional, dynamic information, such as the user assignment or the + * time a timed transition triggers, is then stored in wf_tasks. + * + * Contrary to wf_tasks, this is authoritative, in that it queries + * the actual state of the workflow net. + * + * The logic behind this view is: All transitions in all cases, for which + * there does not exists a place for which there is not a free token. + */ +create view wf_enabled_transitions as +select c.case_id, + t.transition_key, + t.transition_name, + t.workflow_key, + t.sort_order, + t.trigger_type, + t.context_key, + t.enable_callback, + t.enable_custom_arg, + t.fire_callback, + t.fire_custom_arg, + t.assignment_callback, + t.assignment_custom_arg, + t.time_callback, + t.time_custom_arg, + t.deadline_callback, + t.deadline_custom_arg, + t.deadline_attribute_name, + t.hold_timeout_callback, + t.hold_timeout_custom_arg, + t.notification_callback, + t.notification_custom_arg, + t.unassigned_callback, + t.unassigned_custom_arg, + t.estimated_minutes, + t.access_privilege + from wf_transition_info t, + wf_cases c + where t.workflow_key = c.workflow_key + and t.context_key = c.context_key + and c.state = 'active' + and not exists + (select tp.place_key + from wf_transition_places tp + where tp.transition_key = t.transition_key + and tp.workflow_key = t.workflow_key + and tp.direction = 'in' + and not exists + (select tk.token_id + from wf_tokens tk + where tk.place_key = tp.place_key + and tk.case_id = c.case_id + and tk.state = 'free' + ) + ); + + + + + +* + * This view joins wf_tasks with the parties data model to figure out who can perform the tasks. + * It should contain one row per ( user x task ) + */ + +* Replaced 'unique' with 'distinct', because Stas had problems with Oracle behaving mysteriously */ + +create view wf_user_tasks as +select distinct ta.task_id, + ta.case_id, + ta.workflow_key, + ta.transition_key, + tr.transition_name, + ta.enabled_date, + ta.started_date, + u.user_id, + ta.state, + ta.holding_user, + ta.hold_timeout, + ta.deadline, + ta.estimated_minutes +from wf_tasks ta, + wf_task_assignments tasgn, + wf_cases c, + wf_transition_info tr, + party_approved_member_map m, + users u +where ta.state in ( 'enabled','started') +and c.case_id = ta.case_id +and c.state = 'active' +and tr.transition_key = ta.transition_key +and tr.trigger_type = 'user' +and tr.context_key = c.context_key +and tasgn.task_id = ta.task_id +and m.party_id = tasgn.party_id +and u.user_id = m.member_id; + + 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/workflow-case-package.sql 9 Apr 2001 23:13:49 -0000 1.1 @@ -0,0 +1,1781 @@ +-- +-- acs-workflow/sql/workflow-case-package.sql +-- +-- Creates the PL/SQL package that provides the API for interacting +-- with a workflow case. +-- +-- @author Lars Pind (lars@pinds.com) +-- +-- @creation-date 2000-05-18 +-- +-- @cvs-id $Id: workflow-case-package.sql,v 1.1 2001/04/09 23:13:49 danw Exp $ +-- + + +-- create or replace package workflow_case +-- is +-- +-- function new ( +-- case_id in number default null, +-- workflow_key in varchar2, +-- context_key in varchar2 default null, +-- object_id in integer, +-- creation_date in date default sysdate, +-- creation_user in integer default null, +-- creation_ip in varchar2 default null +-- ) return integer; +-- +-- procedure add_manual_assignment ( +-- case_id in number, +-- transition_key in varchar2, +-- party_id in number +-- ); +-- +-- procedure remove_manual_assignment ( +-- case_id in number, +-- transition_key in varchar2, +-- party_id in number +-- ); +-- +-- procedure clear_manual_assignments ( +-- case_id in number, +-- transition_key in varchar2 +-- ); +-- +-- procedure start_case ( +-- case_id in number, +-- creation_user in integer default null, +-- creation_ip in varchar2 default null, +-- msg in varchar2 default null +-- ); +-- +-- procedure delete( +-- case_id in number +-- ); +-- +-- procedure suspend( +-- case_id in number, +-- user_id in number default null, +-- ip_address in varchar2 default null, +-- msg in varchar2 default null +-- ); +-- +-- procedure resume( +-- case_id in number, +-- user_id in number default null, +-- ip_address in varchar2 default null, +-- msg in varchar2 default null +-- ); +-- +-- procedure cancel( +-- case_id in number, +-- user_id in number default null, +-- ip_address in varchar2 default null, +-- msg in varchar2 default null +-- ); +-- +-- procedure fire_message_transition ( +-- task_id in number +-- ); +-- +-- /* To perform an action on the workflow: +-- * (numbers in parenthesis is the number of times each function should get called) +-- * +-- * 1. begin_task_action (1) +-- * 2. set_attribute_value (0..*) +-- * 3. clear_manual_assignments (0..1) +-- * 4. add_manual_assignment (0..*) +-- * 5. end_task_action (1) +-- */ +-- function begin_task_action ( +-- task_id in number, +-- action in varchar2, +-- action_ip in varchar2, +-- user_id in number, +-- msg in varchar2 default null +-- ) return number; +-- +-- procedure set_attribute_value ( +-- journal_id in number, +-- attribute_name in varchar2, +-- value in varchar2 +-- ); +-- +-- procedure end_task_action ( +-- journal_id in number, +-- action in varchar2, +-- task_id in number +-- ); +-- +-- /* Shortcut, that does both begin and end, when you have no attributes to set or assignments to make */ +-- function task_action ( +-- task_id in number, +-- action in varchar2, +-- action_ip in varchar2, +-- user_id in number, +-- msg in varchar2 default null +-- ) return number; +-- +-- function get_attribute_value ( +-- case_id in number, +-- attribute_name in varchar2 +-- ) return varchar2; +-- +-- procedure add_task_assignment ( +-- task_id in number, +-- party_id in number +-- ); +-- +-- procedure remove_task_assignment ( +-- task_id in number, +-- party_id in number +-- ); +-- +-- procedure clear_task_assignments ( +-- task_id in number +-- ); +-- +-- +-- /* DBMS_JOBS */ +-- +-- procedure sweep_timed_transitions; +-- +-- procedure sweep_hold_timeout; +-- +-- end workflow_case; + +-- show errors + +-- create or replace package body workflow_case +-- function new +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; +begin + if context_key is null then + v_context_key_for_query := ''default''; + else + v_context_key_for_query := 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 + ); + + /* insert the case in to the general wf_cases table */ + insert into wf_cases + (case_id, workflow_key, context_key, object_id, state) + values + (v_case_id, new__workflow_key, v_context_key_for_query, new__object_id, ''created''); + + /* insert the case into the workflow-specific cases table */ + select table_name into v_workflow_case_table + 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; + + return v_case_id; + +end;' language 'plpgsql'; + + +-- procedure add_manual_assignment +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); +begin + 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; +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); +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; +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); +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; +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; +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 + ); + + 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 + ); + + /* Turn the wheels */ + sweep_automatic_transitions( + case_id => start_case__case_id, + journal_id => v_journal_id + ); + 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); +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 the journal */ + journal_entry.delete_for_object(workflow_case.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 + 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; + + /* delete from the generic cases table */ + delete from wf_cases where case_id = workflow_case.delete__case_id; + + /* delete from acs-objects */ + acs_object.delete(workflow_case.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; +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''); + 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 + ); + + update wf_cases + set state = ''suspended'' + where case_id = suspend__case_id; + 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; +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''); + 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 + ); + + update wf_cases + set state = ''active'' + where case_id = resume__case_id; + 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; +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''); + 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 + ); + + update wf_cases + set state = ''canceled'' + where case_id = cancel__case_id; + 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; +begin + select t.case_id, tr.transition_name, tr.trigger_type + into v_case_id, v_transition_name, v_trigger_type + from wf_tasks t, wf_transitions tr + where t.task_id = fire_message_transition__task_id + and tr.workflow_key = t.workflow_key + 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''); + 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'' + ); + + fire_transition_internal( + task_id => fire_message_transition__task_id, + journal_id => v_journal_id + ); + + sweep_automatic_transitions( + case_id => v_case_id, + journal_id => v_journal_id + ); + 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 + 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.''); + end if; + + select decode(count(*),0,0,1) 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.''); + end if; + elsif 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 + 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.''); + end if; + elsif 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||''''''''); + end if; + + /* Is this user assigned to this task? */ + select decode(count(*),0,0,1) 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.''); + end if; + + /* This task is finished without an explicit start. + * Store the user as the holding_user */ + update wf_tasks + 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; + + elsif 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; + + select t.case_id, tr.transition_name into v_case_id, v_transition_name + from wf_tasks t, + wf_transitions tr + where t.task_id = begin_task_action__task_id + and tr.workflow_key = t.workflow_key + and tr.transition_key = t.transition_key; + + /* 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 + ); + + return v_journal_id; + +end;' language 'plpgsql'; + + +-- procedure end_task_action +create function workflow_case__end_task_action (number,varchar,number) +returns integer as ' +declare + journal_id alias for $1; + action alias for $2; + task_id alias for $3; +begin + select creation_user into v_user_id + from acs_objects + where object_id = end_task_action__journal_id; + + /* 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; + + 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; +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 + ); + + end_task_action( + journal_id => v_journal_id, + action => task_action__action, + task_id => task_action__task_id + ); + + return v_journal_id; + +end;' language 'plpgsql'; + + +-- procedure set_attribute_value +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; +begin + select o.object_type, o.object_id into v_workflow_key, v_case_id + from journal_entries je, acs_objects o + where je.journal_id = set_attribute_value__journal_id + and o.object_id = je.object_id; + + select attribute_id into v_attribute_id + from acs_attributes + 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 + ); + + 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; +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; +begin + return acs_object.get_attribute( + object_id_in => get_attribute_value__case_id, + attribute_name_in => get_attribute_value__attribute_name + ); + +end;' language 'plpgsql'; + + +-- procedure add_task_assignment +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; + 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; + +begin + -- check that we don''t 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; + end if; + + -- get the notification callback info + + select case_id, workflow_key, transition_key + into v_case_id, v_workflow_key, v_transition_key + from wf_tasks + where task_id = add_task_assignment__task_id; + + select context_key + into v_context_key + from wf_cases + where case_id = v_case_id; + + open c_callback; + fetch c_callback into callback_rec; + + if c_callback%FOUND then + v_notification_callback := callback_rec.notification_callback; + v_notification_custom_arg := callback_rec.notification_custom_arg; + else + v_notification_callback := null; + v_notification_custom_arg := null; + end if; + + -- 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 + ); + end loop; + + -- do the insert + + insert into wf_task_assignments + (task_id, party_id) + values + (add_task_assignment__task_id, add_task_assignment__party_id); + + + 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; +begin + delete + from wf_task_assignments + where task_id = remove_task_assignment__task_id + and party_id = remove_task_assignment__party_id; + + select count(*) into v_num_assigned + from wf_task_assignments + where task_id = remove_task_assignment__task_id; + + if v_num_assigned > 0 then + return; + end if; + + select case_id, transition_key + into v_case_id, v_transition_key + from wf_tasks + where task_id = remove_task_assignment__task_id; + + select workflow_key, context_key + into v_workflow_key, v_context_key + 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 + ); + end if; + close c_callback; + + 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); +begin + delete + from wf_task_assignments + where task_id = clear_task_assignments__task_id; + + select case_id, transition_key + into v_case_id, v_transition_key + from wf_tasks + where task_id = clear_task_assignments__task_id; + + select workflow_key, context_key + into v_workflow_key, v_context_key + from wf_cases + where case_id = v_case_id; + + select unassigned_callback, unassigned_custom_arg + into v_callback, v_custom_arg + from wf_context_transition_info + where workflow_key = v_workflow_key + 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 + ); + + return 0; +end;' language 'plpgsql'; + + +-- function evaluate_guard +create function workflow_case__evaluate_guard (varchar,varchar,number,varchar,varchar,varchar,varchar) +returns integer 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; +begin + if callback is null then + -- null guard evaluates to true + return ''t''; + else + if 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; + end if; + end if; + +end;' language 'plpgsql'; + + +-- procedure execute_transition_callback +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; +begin + if callback is not null then + execute ''begin ''||callback + || ''(:1, :2, :3); end;'' + using in case_id, + in transition_key, + in custom_arg; + end if; + return 0; +end;' language 'plpgsql'; + + +-- function execute_time_callback +create function workflow_case__execute_time_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; +begin + if callback is null then + raise_application_error(-20000, ''There''''s no time_callback function for the timed transition '''''' || 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; + +end;' language 'plpgsql'; + + +-- function get_task_deadline +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; +begin + /* + * 1. or if there''s a row in wf_case_deadlines, we use that + * 2. if there is a callback, we execute that + * 3. otherwise, if there is an attribute, we use that + */ + + /* 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 + /* 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 + /* attribute */ + v_deadline := acs_object.get_attribute( + object_id_in => get_task_deadline__case_id, + attribute_name_in => get_task_deadline__attribute_name + ); + else + v_deadline := null; + end if; + end if; + + return v_deadline; + +end;' language 'plpgsql'; + + +-- function execute_hold_timeout_callback +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; +begin + if 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; + +end;' language 'plpgsql'; + + +-- procedure execute_unassigned_callback +create function workflow_case__execute_unassigned_callback (varchar,number,varchar) +returns integer as ' +declare + callback alias for $1; + task_id alias for $2; + 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; + end if; + 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; +begin + + /* Find out who to assign the given task to. + * + * 1. See if there are rows in wf_case_assignments. + * 2. If not, and a callback is defined, execute that. + * 3. Otherwise, grab the assignment from the workflow context. + * + * (We used to use the callback first, but that makes + * reassignment of tasks difficult.) + */ + + v_done_p := ''f''; + for case_assignment_rec in case_assignments loop + v_done_p := ''t''; + add_task_assignment ( + task_id => task_id, + party_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; + else + for context_assignment_rec in context_assignments loop + add_task_assignment ( + task_id => task_id, + party_id => context_assignment_rec.party_id + ); + end loop; + end if; + end if; + 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; +begin + select wf_token_id_seq.nextval into v_token_id from dual; + + select workflow_key into v_workflow_key + from wf_cases c + where c.case_id = add_token__case_id; + + 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; +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; +begin + update wf_tokens + set state = ''locked'', + locked_task_id = lock_token__task_id, + locked_date = sysdate, + 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; +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 +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 + ); + end loop; + + /* Mark the released ones canceled */ + update wf_tokens + set state = ''canceled'', + canceled_date = sysdate, + canceled_journal_id = release_token__journal_id + where state = ''locked'' + and locked_task_id = release_token__task_id; + 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; +begin + if task_id is null then + update wf_tokens + set state = ''consumed'', + consumed_date = sysdate, + consumed_journal_id = consume_token__journal_id + where case_id = consume_token__case_id + and place_key = consume_token__place_key + and state = ''free'' + and rownum = 1; + else + update wf_tokens + set state = ''consumed'', + consumed_date = sysdate, + 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; +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); +begin + + enable_transitions(case_id => sweep_automatic_transitions__case_id); + + loop + v_done_p := ''t''; + v_finished_p := finished_p( + case_id => sweep_automatic_transitions__case_id, + journal_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 + ); + v_done_p := ''f''; + end loop; + enable_transitions(case_id => sweep_automatic_transitions__case_id); + end if; + + exit when v_done_p = ''t''; + end loop; + return 0; +end;' language 'plpgsql'; + + +-- function finished_p +create function workflow_case__finished_p (number,number) +returns integer as ' +declare + case_id alias for $1; + journal_id alias for $2; +begin + select state into v_case_state + from wf_cases + where case_id = finished_p__case_id; + + if v_case_state = ''finished'' then + 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 + from wf_tokens + where case_id = finished_p__case_id + and place_key = ''end''; + + if v_num_rows = 0 then + return ''f''; + else + /* There''s a token in the end place. + * 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 + 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.''); + end if; + + /* Consume that token */ + select token_id into v_token_id + from wf_tokens + 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 + ); + + 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( + object_id => finished_p__case_id, + action => ''case finish'', + action_pretty => ''Case finished'' + ); + + return ''t''; + end if; + end if; + +end;' language 'plpgsql'; + + +-- procedure notify_assignee +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; +begin + select to_char(ta.deadline,''Mon fmDDfm, YYYY HH24:MI:SS''), + acs_object.name(c.object_id), + tr.transition_key, + tr.transition_name + into v_deadline_pretty, + v_object_name, + v_transition_key, + v_transition_name + from wf_tasks ta, wf_transitions tr, wf_cases c + where ta.task_id = notify_assignee__task_id + and c.case_id = ta.case_id + and tr.workflow_key = c.workflow_key + and tr.transition_key = ta.transition_key; + + /* 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_body := ''You have been assigned to a task. +''||'' +Task : ''||v_transition_name||'' +Object : ''||v_object_name||'' +''; + + if v_deadline_pretty != '''' then + v_body := v_body ||''Deadline: ''||v_deadline_pretty||'' +''; + end if; + + /* 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 */ + + 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; + 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 + ); + + return 0; +end;' language 'plpgsql'; + + +-- procedure enable_transitions +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 +begin + select workflow_key into v_workflow_key + from wf_cases + where case_id = enable_transitions__case_id; + + /* we mark tasks overridden if they were once enabled, but are no longer so */ + + update wf_tasks + set state = ''overridden'', + overridden_date = sysdate + where case_id = enable_transitions__case_id + and state = ''enabled'' + and transition_key not in + (select transition_key + from wf_enabled_transitions + where case_id = enable_transitions__case_id); + + + /* insert a task for the transitions that are enabled but have no task row */ + + for trans_rec in tasks_to_create 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 + ); + 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; + + /* we''re ready to insert the row */ + select wf_task_id_seq.nextval into v_task_id from dual; + + insert into wf_tasks ( + task_id, case_id, workflow_key, transition_key, + deadline, trigger_time, estimated_minutes + ) values ( + v_task_id, enable_transitions__case_id, v_workflow_key, + trans_rec.transition_key, + 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 + ); + + /* 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 + ); + + 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 + ); + end if; + + end loop; + 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; +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 + from wf_tasks t, wf_cases c, wf_transition_info ti + where t.task_id = fire_transition_internal__task_id + and c.case_id = t.case_id + and ti.context_key = c.context_key + and ti.workflow_key = c.workflow_key + and ti.transition_key = t.transition_key; + + /* Check that the state is either started or enabled */ + + if v_state = ''enabled'' then + v_locked_task_id := null; + elsif 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; + + + /* Mark the task finished */ + + update wf_tasks + set state = ''finished'', + finished_date = sysdate + 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 + ); + 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 + 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 + ); + + 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 + ); + end if; + end loop; + + + /* 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 ( + 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 = ''#'') + loop + add_token( + case_id => v_case_id, + place_key => place_rec.place_key, + journal_id => 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 + ); + 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; +begin + select decode(count(*),0,0,1) 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 || ''''''''); + end if; + return 0; +end;' language 'plpgsql'; + + +-- procedure start_task +create function workflow_case__start_task (number,number,number) +returns integer as ' +declare + task_id alias for $1; + user_id alias for $2; + journal_id alias for $3; + v_case_id number; + v_workflow_key wf_workflows.workflow_key%TYPE; + v_transition_key varchar(100); + v_hold_timeout_callback varchar(100); + v_hold_timeout_custom_arg varchar(4000); + v_hold_timeout timestamp; +begin + ensure_task_in_state(task_id => start_task__task_id, state => ''enabled''); + + select t.case_id, t.workflow_key, t.transition_key, ti.hold_timeout_callback, ti.hold_timeout_custom_arg + into v_case_id, v_workflow_key, v_transition_key, v_hold_timeout_callback, v_hold_timeout_custom_arg + from wf_tasks t, wf_cases c, wf_transition_info ti + where t.task_id = start_task__task_id + and c.case_id = t.case_id + and ti.context_key = c.context_key + and ti.workflow_key = t.workflow_key + and ti.transition_key = t.transition_key; + + v_hold_timeout := execute_hold_timeout_callback(v_hold_timeout_callback, + v_hold_timeout_custom_arg, v_case_id, v_transition_key); + + /* Mark it started */ + + update wf_tasks + set state = ''started'', + started_date = sysdate, + holding_user = start_task__user_id, + hold_timeout = v_hold_timeout + where task_id = start_task__task_id; + + + /* Reserve one token from each input place */ + + for place_rec in input_places(v_workflow_key,v_transition_key) loop + lock_token( + case_id => v_case_id, + place_key => place_rec.place_key, + journal_id => start_task__journal_id, + task_id => start_task__task_id + ); + end loop; + return 0; +end;' language 'plpgsql'; + + +-- procedure cancel_task +create function workflow_case__cancel_task (number,number) +returns integer as ' +declare + task_id alias for $1; + journal_id alias for $2; +begin + ensure_task_in_state(task_id => cancel_task__task_id, state => ''started''); + select case_id into v_case_id + from wf_tasks + where task_id = cancel_task__task_id; + + /* Mark the task canceled */ + + update wf_tasks + set state = ''canceled'', + canceled_date = sysdate + where task_id = cancel_task__task_id; + + + /* Release our reserved tokens */ + + release_token( + task_id => cancel_task__task_id, + journal_id => cancel_task__journal_id + ); + + /* The workflow state has now changed, so we must run this */ + + sweep_automatic_transitions( + case_id => v_case_id, + journal_id => cancel_task__journal_id + ); + return 0; +end;' language 'plpgsql'; + + +-- procedure finish_task +create function workflow_case__finish_task (number,number) +returns integer as ' +declare + task_id alias for $1; + journal_id alias for $2; +begin + select case_id into v_case_id + from wf_tasks + where task_id = finish_task__task_id; + + fire_transition_internal( + task_id => finish_task__task_id, + journal_id => finish_task__journal_id + ); + + sweep_automatic_transitions( + case_id => v_case_id, + journal_id => finish_task__journal_id + ); + return 0; +end;' language 'plpgsql'; + + + +-- show errors + + +-- +-- schedule processing of the notification queue +-- + +create function inline_0 () +returns integer as ' +begin + select nt__schedule_process (1,'localhost',25); + NONE, + NONE, + 25 + ); + return 0; +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); +