Index: openacs-4/contrib/obsolete-packages/acs-workflow/sql/oracle/wf-core-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/acs-workflow/sql/oracle/wf-core-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/obsolete-packages/acs-workflow/sql/oracle/wf-core-create.sql 5 Apr 2001 18:23:38 -0000 1.1 +++ openacs-4/contrib/obsolete-packages/acs-workflow/sql/oracle/wf-core-create.sql 19 Nov 2001 18:17:46 -0000 1.2 @@ -19,11 +19,11 @@ begin acs_object_type.create_type( - object_type => 'workflow', - pretty_name => 'Workflow', - pretty_plural => 'Workflow', - table_name => 'wf_cases', - id_column => 'case_id' + object_type => 'workflow', + pretty_name => 'Workflow', + pretty_plural => 'Workflow', + table_name => 'wf_cases', + id_column => 'case_id' ); end; / @@ -33,9 +33,9 @@ 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, + constraint wf_wf_acs_object_types_fk + references acs_object_types(object_type) + on delete cascade, description varchar2(4000) ); @@ -46,27 +46,59 @@ 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, + 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 + constraint wf_place_name_nn + not null, + -- so we can display places in some logical order -- sort_order integer - constraint wf_place_order_ck - check (sort_order > 0), + constraint wf_place_order_ck + check (sort_order > 0), + -- table constraints -- constraint wf_place_pk - primary key (workflow_key, place_key), + primary key (workflow_key, place_key), constraint wf_places_wf_key_place_name_un - unique (workflow_key, place_name) + 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_roles ( + role_key varchar2(100), + workflow_key varchar2(100) + constraint wf_roles_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + role_name varchar2(100) + constraint wf_role_role_name_nn + not null, + -- so we can display roles in some logical order -- + sort_order integer + constraint wf_roles_order_ck + check (sort_order > 0), + -- table constraints -- + constraint wf_role_pk + primary key (workflow_key, role_key), + constraint wf_roles_wf_key_role_name_un + unique (workflow_key, role_name) +); + +comment on table wf_roles is ' + A process has certain roles associated with it, such as "submitter", + "reviewer", "editor", "claimant", etc. For each transition, then, you + specify what role is to perform that task. Thus, two or more tasks can be + performed by one and the same role, so that when the role is reassigned, + it reflects assignments of both tasks. Users and parties are then assigned + to roles instead of directly to tasks. +'; + + create table wf_transitions ( transition_key varchar2(100), transition_name varchar2(100) @@ -75,19 +107,27 @@ workflow_key varchar2(100) constraint wf_transition_workflow_fk references wf_workflows(workflow_key) - on delete cascade, - -- purely for UI purposes + on delete cascade, + -- what role does this transition belong to + -- (only for user-triggered transitions) + role_key varchar2(100), + -- so we can display transitions in some logical order -- 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 + constraint wf_transition_trigger_type_ck + check (trigger_type in ('automatic','user','message','time')), + -- table constraints -- constraint wf_transition_pk - primary key (workflow_key, transition_key), + primary key (workflow_key, transition_key), constraint wf_trans_wf_key_trans_name_un - unique (workflow_key, transition_name) + unique (workflow_key, transition_name), + constraint wf_transition_role_fk + foreign key (workflow_key,role_key) references wf_roles(workflow_key,role_key) + /* We don't do on delete cascade here, because that would mean that + * when a role is deleted, the transitions associated with that role would be deleted, too */ ); comment on table wf_transitions is ' @@ -96,15 +136,15 @@ create table wf_arcs ( workflow_key varchar2(100) - constraint wf_ts_arc_workflow_fk - references wf_workflows(workflow_key) - on delete cascade, + 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')), + 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, @@ -114,16 +154,17 @@ guard_callback varchar2(100), guard_custom_arg varchar2(4000), guard_description varchar2(500), + -- table constraints -- + constraint wf_arc_pk + primary key (workflow_key, transition_key, place_key, direction), constraint wf_arc_guard_on_in_arc_ck - check (guard_callback is null or direction = 'out'), + 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, + 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) + foreign key (workflow_key, place_key) references wf_places(workflow_key, place_key) + on delete cascade ); create index wf_arcs_wf_key_trans_key_idx on wf_arcs(workflow_key, transition_key); @@ -137,35 +178,23 @@ 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, + 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_attribute_fk + references acs_attributes, + -- table constraints -- constraint wf_trans_attr_map_pk - primary key (workflow_key, transition_key, attribute_id), + 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 + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) + on delete cascade ); comment on table wf_transition_attribute_map is ' @@ -174,56 +203,55 @@ '; -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, +create table wf_transition_role_assign_map ( + workflow_key varchar2(100) + constraint wf_role_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 + assign_role_key varchar2(100), + -- table constraints -- + constraint wf_role_asgn_map_pk + primary key (workflow_key, transition_key, assign_role_key), + constraint wf_role_asgn_map_trans_fk + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) + on delete cascade, + constraint wf_tr_role_asgn_map_asgn_fk + foreign key (workflow_key, assign_role_key) references wf_roles(workflow_key, role_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); +create index wf_role_asgn_map_wf_trans_idx on wf_transition_role_assign_map(workflow_key, transition_key); +create index wf_role_asgn_map_wf_as_tr_idx on wf_transition_role_assign_map(workflow_key, assign_role_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 table wf_transition_role_assign_map is ' + When part of the output of one task is to assign users to a role, + specify that this is the case by inserting a row here. '; -comment on column wf_transition_assignment_map.transition_key is ' +comment on column wf_transition_role_assign_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. +comment on column wf_transition_role_assign_map.assign_role_key is ' + assign_role_key is the role 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 + 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 ' @@ -240,86 +268,105 @@ commit; +create table wf_context_workflow_info ( + context_key varchar2(100) + constraint wf_context_wf_context_fk + references wf_contexts + on delete cascade, + workflow_key varchar2(100) + constraint wf_context_wf_workflow_fk + references wf_workflows + on delete cascade, + /* The principal is the user/party that sends out email assignment notifications + * And receives email when a task becomes unassigned (for more than x minutes?) + */ + principal_party integer + constraint wf_context_wf_principal_fk + references parties + on delete set null, + -- table constraints -- + constraint wf_context_workflow_pk + primary key (context_key, workflow_key) +); +comment on table wf_context_workflow_info is ' + Holds context-dependent information about the workflow, specifically the + principal user. +'; + + 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), + context_key varchar2(100) + constraint wf_context_trans_context_fk + references wf_contexts + on delete cascade, + workflow_key varchar2(100) + constraint wf_context_trans_workflow_fk + references wf_workflows + on delete cascade, + transition_key varchar2(100), /* information for the transition in the context */ /* The number of minutes this task is estimated to take */ - estimated_minutes integer, + estimated_minutes integer, + /* Instructions for how to complete the task. Will be displayed on the task page. */ + instructions varchar2(4000), /* * 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), + 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), + 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), + deadline_callback varchar2(100), + deadline_custom_arg varchar2(4000), /* The name of an attribute that holds the deadline */ - deadline_attribute_name varchar2(100), + 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), + 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) + * 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), + 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) + * Callback to handle unassigned tasks. + * Will be called when an enabled task becomes unassigned. + * Signature: (task_id in number, 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 */ + -- table constraints -- constraint wf_context_trans_trans_fk - foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) - on delete cascade, + 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) + 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); @@ -330,25 +377,64 @@ '; +create table wf_context_role_info ( + context_key varchar2(100) + constraint wf_context_role_context_fk + references wf_contexts(context_key) + on delete cascade, + workflow_key varchar2(100) + constraint wf_context_role_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + role_key varchar2(100), + /* + * Callback to programatically assign a role. + * Must call wordflow_case.*_role_assignment to make the assignments. + * Will be called when a transition for that role becomes enabled + * signature: (role_key in varchar2, custom_arg in varchar2) + */ + assignment_callback varchar2(100), + assignment_custom_arg varchar2(4000), + -- table constraints -- + constraint wf_context_role_role_fk + foreign key (workflow_key, role_key) references wf_roles(workflow_key, role_key) + on delete cascade, + constraint wf_context_role_info_pk + primary key (context_key, workflow_key, role_key) +); + +comment on table wf_context_role_info is ' + This table holds context-dependent info for roles, currently only the assignment callback +'; + + 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 */ + 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_order integer not null, + header varchar2(200) not null, + template_url varchar2(500) not null, + /* Display this panel in place of the action panel */ + overrides_action_p char(1) default 'f' + constraint wf_context_panels_ovrd_p_ck + check (overrides_action_p in ('t','f')), + /* Display this panel only when the task has been started (and not finished) */ + only_display_when_started_p char(1) default 'f' + constraint wf_context_panels_display_p_ck + check (only_display_when_started_p in ('t','f')), + -- table constraints -- constraint wf_context_panels_trans_fk - foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) - on delete cascade, + 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) + primary key (context_key, workflow_key, transition_key, sort_order) ); create index wf_ctx_panl_workflow_trans_idx on wf_context_task_panels(workflow_key, transition_key); @@ -358,33 +444,32 @@ '; - 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, + 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, + role_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_pk - primary key (context_key, workflow_key, transition_key, party_id) + primary key (context_key, workflow_key, role_key, party_id), + constraint wf_context_assign_role_fk + foreign key (workflow_key, role_key) references wf_roles(workflow_key, role_key) + on delete cascade ); -create index wf_ctx_assg_workflow_trans_idx on wf_context_assignments(workflow_key, transition_key); +create index wf_ctx_assg_workflow_trans_idx on wf_context_assignments(workflow_key, role_key); comment on table wf_context_assignments is ' - Static assignment at the context level. + Static (default) per-context assignments of roles to parties. '; @@ -397,26 +482,26 @@ case_id integer constraint wf_cases_pk primary key - constraint wf_cases_acs_object_fk - references acs_objects(object_id) - on delete cascade, + 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, + 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', + state varchar2(40) + default 'created' + constraint wf_cases_state_ck + check (state in ('created', + 'active', 'suspended', 'canceled', 'finished')) @@ -427,58 +512,64 @@ 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. + The instance of a process, e.g. the case of publishing one article, + the case of handling one insurance claim, the case of handling + one ecommerce order, of fixing one ticket-tracker ticket. '; 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. + A case is generally about some other object, e.g., an insurance claim, an article, + a ticket, an order, etc. This is the place to store the reference to that object. + It is not uncommong to have more than one case for the same object, e.g., we might + have one process for evaluating and honoring an insurance claim, and another for archiving + legal information about a claim. '; create table wf_case_assignments ( - case_id integer - constraint wf_case_assign_fk - references wf_cases(case_id) - on delete cascade, + 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), + role_key varchar2(100), + party_id integer + constraint wf_case_assign_party_fk + references parties(party_id) + on delete cascade, + -- table constraints -- constraint wf_case_assign_pk - primary key (case_id, transition_key, party_id) + primary key (case_id, role_key, party_id), + constraint wf_case_assign_role_fk + foreign key (workflow_key, role_key) references wf_roles(workflow_key, role_key) + on delete cascade ); 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. + Manual per-case assignments of roles to parties. '; create table wf_case_deadlines ( - case_id integer - constraint wf_case_deadline_fk - references wf_cases(case_id) - on delete cascade, + 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), + deadline date + constraint wf_case_deadline_nn + not null, + -- table constraints -- constraint wf_case_deadline_pk - primary key (case_id, transition_key) + primary key (case_id, transition_key), + constraint wf_case_deadline_trans_fk + foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) + on delete cascade ); @@ -491,22 +582,22 @@ 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, + 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), + 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', + 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, @@ -517,15 +608,15 @@ trigger_time date, /* -- USER transition info */ deadline date, - estimated_minutes integer, + estimated_minutes integer, holding_user integer - constraint wf_task_holding_user_fk - references users(user_id) - on delete cascade, + constraint wf_task_holding_user_fk + references users(user_id) + on delete cascade, hold_timeout date, - /* -- */ + -- table constraints -- constraint wf_task_transition_fk - foreign key (workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) + 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); @@ -539,15 +630,16 @@ create table wf_task_assignments ( task_id integer - constraint wf_task_assign_task_fk - references wf_tasks(task_id) - on delete cascade, + constraint wf_task_assign_task_fk + references wf_tasks(task_id) + on delete cascade, party_id integer - constraint wf_task_party_fk + constraint wf_task_party_fk references parties(party_id) - on delete cascade, + on delete cascade, + -- table constraints -- constraint wf_task_assignments_pk - primary key (task_id, party_id) + primary key (task_id, party_id) ); create index wf_task_asgn_party_id_idx on wf_task_assignments(party_id); @@ -556,43 +648,44 @@ 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), + 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')), + 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), + 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, + canceled_date date, consumed_date date, produced_journal_id integer - constraint wf_token_produced_journal_fk - references journal_entries(journal_id), + 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), + 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), + 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_consumed_journal_fk + references journal_entries(journal_id), + -- table constraints -- constraint wf_token_place_fk - foreign key (workflow_key, place_key) references wf_places(workflow_key, place_key) + 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); @@ -607,20 +700,21 @@ /* 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), + 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), + -- table constraints -- constraint wf_attr_val_audit_pk - primary key (case_id, attribute_id, journal_id) + primary key (case_id, attribute_id, journal_id) ); create index wf_attr_val_aud_attr_id_idx on wf_attribute_value_audit(attribute_id); @@ -641,7 +735,8 @@ t.transition_name, t.workflow_key, t.sort_order, - t.trigger_type, + t.trigger_type, + t.role_key, c.context_key, c.context_name from wf_transitions t, wf_contexts c; @@ -660,13 +755,13 @@ t.sort_order, t.trigger_type, t.context_key, + t.role_key, ct.estimated_minutes, + ct.instructions, 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, @@ -677,8 +772,7 @@ ct.notification_callback, ct.notification_custom_arg, ct.unassigned_callback, - ct.unassigned_custom_arg, - ct.access_privilege + ct.unassigned_custom_arg from wf_transition_contexts t, wf_context_transition_info ct where ct.workflow_key (+) = t.workflow_key and ct.transition_key (+) = t.transition_key @@ -687,6 +781,24 @@ /* + * Returns all the information stored about a certain role + * in all contexts. You'll usually want to use this with a + * "where context = " clause. + */ +create or replace view wf_role_info as +select r.role_key, + r.role_name, + r.workflow_key, + c.context_key, + cr.assignment_callback, + cr.assignment_custom_arg +from wf_roles r, wf_contexts c, wf_context_role_info cr +where cr.workflow_key (+) = r.workflow_key + and cr.role_key (+) = r.role_key + and cr.context_key = c.context_key; + + +/* * This view makes it easy to get the input/output places of a transition */ create or replace view wf_transition_places as @@ -726,12 +838,11 @@ t.sort_order, t.trigger_type, t.context_key, + t.role_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, @@ -741,10 +852,10 @@ 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 + t.instructions, + t.unassigned_callback, + t.unassigned_custom_arg from wf_transition_info t, wf_cases c where t.workflow_key = c.workflow_key @@ -758,10 +869,10 @@ 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' + from wf_tokens tk + where tk.place_key = tp.place_key + and tk.case_id = c.case_id + and tk.state = 'free' ) ); @@ -782,6 +893,7 @@ ta.workflow_key, ta.transition_key, tr.transition_name, + tr.instructions, ta.enabled_date, ta.started_date, u.user_id, @@ -800,6 +912,7 @@ and c.case_id = ta.case_id and c.state = 'active' and tr.transition_key = ta.transition_key +and tr.workflow_key = ta.workflow_key and tr.trigger_type = 'user' and tr.context_key = c.context_key and tasgn.task_id = ta.task_id