-- -- 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.1.1 2001/03/13 22:59:27 ben Exp $ -- ---------------------------------- -- KNOWLEDGE LEVEL OBJECTS ---------------------------------- /* Create the workflow superclass */ begin acs_object_type.create_type( object_type => 'workflow', pretty_name => 'Workflow', pretty_plural => 'Workflow', table_name => 'wf_cases', id_column => 'case_id' ); end; / show errors create table wf_workflows ( workflow_key varchar2(100) constraint wf_workflows_pk primary key constraint wf_workflows_workflow_key_fk references acs_object_types(object_type) on delete cascade, description varchar2(4000) ); comment on table wf_workflows is ' Parent table for the workflow definition. '; create table wf_places ( place_key varchar2(100), workflow_key varchar2(100) constraint wf_place_workflow_fk references wf_workflows(workflow_key) on delete cascade, place_name varchar2(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 varchar2(100), transition_name varchar2(100) constraint wf_transition_name_nn not null, workflow_key varchar2(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 varchar2(40) 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 varchar2(100) constraint wf_ts_arc_workflow_fk references wf_workflows(workflow_key) on delete cascade, transition_key varchar2(100), place_key varchar2(100), -- direction is relative to the transition direction varchar2(3) 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 varchar2, transition_key in varchar2, * place_key in varchar2, direction in varchar2, custom_arg in varchar2) * return char(1) */ guard_callback varchar2(100), guard_custom_arg varchar2(4000), guard_description varchar2(500), 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 varchar2(50) constraint wf_attr_info_wf_datatype_ck check (wf_datatype in ('none', 'party')) ); create table wf_transition_attribute_map ( workflow_key varchar2(100) constraint wf_trans_attr_map_workflow_fk references wf_workflows(workflow_key) on delete cascade, transition_key varchar2(100), -- 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 varchar2(100) constraint wf_trans_asgn_map_workflow_fk references wf_workflows(workflow_key) on delete cascade, transition_key varchar2(100), assign_transition_key varchar2(100), 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 varchar2(100) constraint wf_context_pk primary key, context_name varchar2(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 varchar2(100) constraint wf_context_trans_context_fk references wf_contexts, workflow_key varchar2(100) constraint wf_context_trans_workflow_fk references wf_workflows, transition_key varchar2(100), /* 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 varchar2, custom_arg in varchar2) */ enable_callback varchar2(100), enable_custom_arg varchar2(4000), fire_callback varchar2(100), fire_custom_arg varchar2(4000), /* * 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 varchar2) */ assignment_callback varchar2(100), assignment_custom_arg varchar2(4000), /* * 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 varchar2, custom_arg in varchar2) return date */ time_callback varchar2(100), time_custom_arg varchar2(4000), /* * Returns the deadline for this task. * Will be called when the transition becomes enabled * Signature: (case_id in number, transition_key in varchar2, custom_arg in varchar2) return date */ deadline_callback varchar2(100), deadline_custom_arg varchar2(4000), /* The name of an attribute that holds the deadline */ deadline_attribute_name varchar2(100), /* * 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 varchar2, custom_arg in varchar2) return date */ hold_timeout_callback varchar2(100), hold_timeout_custom_arg varchar2(4000), /* * 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 varchar2, * party_to in integer, * party_from in out integer, * subject in out varchar2, * body in out varchar2) */ notification_callback varchar2(100), notification_custom_arg varchar2(4000), /* * Unassigned callback * Will be called whenever a task becomes unassigned * Signature: (case_id in number, transition_key in varchar2, custom_arg in varchar2) */ unassigned_callback varchar2(100), unassigned_custom_arg varchar2(4000), /* name of the privilege we should check before allowing access * to task information. */ access_privilege varchar2(100), /* 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 varchar2(100) not null constraint wf_context_panels_context_fk references wf_contexts(context_key) on delete cascade, workflow_key varchar2(100) not null constraint wf_context_panels_workflow_fk references wf_workflows(workflow_key) on delete cascade, transition_key varchar2(100) not null, sort_key integer not null, header varchar2(200) not null, template_url varchar2(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 varchar2(100) constraint wf_context_assign_context_fk references wf_contexts(context_key) on delete cascade, workflow_key varchar2(100) constraint wf_context_assign_workflow_fk references wf_workflows(workflow_key) on delete cascade, transition_key varchar2(100), 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 varchar2(100) constraint wf_cases_workflow_fk references wf_workflows(workflow_key) on delete cascade, context_key varchar2(100) constraint wf_cases_context_fk references wf_contexts(context_key) on delete cascade, object_id constraint wf_cases_object_fk references acs_objects(object_id) on delete cascade, -- a toplevel state of the case state varchar2(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 varchar2(100), transition_key varchar2(100), 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 varchar2(100), transition_key varchar2(100), deadline date 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 wf_task_id_seq start with 1000 increment by 1; 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 varchar2(100) constraint wf_task_workflow_fk references wf_workflows(workflow_key), transition_key varchar2(100), /* Information about the task */ state varchar2(40) default 'enabled' constraint wf_task_state_ck check (state in ('enabled','started','canceled', 'finished','overridden')), enabled_date date default sysdate, started_date date, canceled_date date, finished_date date, overridden_date date, /* -- TIME transition info */ trigger_time date, /* -- USER transition info */ deadline date, estimated_minutes integer, holding_user integer constraint wf_task_holding_user_fk references users(user_id) on delete cascade, hold_timeout date, /* -- */ 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 wf_token_id_seq start with 1000 increment by 1; 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 varchar2(100) constraint wf_token_workflow_fk references wf_workflows(workflow_key), -- a token must always be in some place place_key varchar2(100), state varchar2(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 date default sysdate, locked_date date, canceled_date date, consumed_date date, 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 varchar2(4000), 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 or replace 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 or replace 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, wf_context_transition_info ct where 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 or replace 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 or replace 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 or replace 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;