-- -- 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.2 2001/04/13 00:29:01 danw Exp $ -- ---------------------------------- -- KNOWLEDGE LEVEL OBJECTS ---------------------------------- /* Create the workflow superclass */ create function inline_0 () returns integer as ' begin PERFORM 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 integer, 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 integer of minutes this task is estimated to take */ estimated_minutes integer, /* * Will be called when the transition is enabled/fired. * signature: (case_id in integer, 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 integer, 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 integer, 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 integer, 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 integer, 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 integer, * 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 integer, 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;