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 -r1.4 -r1.5 --- openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/wf-core-create.sql 28 Apr 2001 19:58:39 -0000 1.4 +++ openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/wf-core-create.sql 19 Nov 2001 18:20:33 -0000 1.5 @@ -66,10 +66,11 @@ place_name varchar(100) constraint wf_place_name_nn not null, - -- purely for UI purposes + -- so we can display places in some logical order -- sort_order integer constraint wf_place_order_ck check (sort_order > 0), + -- table constraints -- constraint wf_place_pk primary key (workflow_key, place_key), constraint wf_places_wf_key_place_name_un @@ -81,6 +82,35 @@ state of the workflow. '; +create table wf_roles ( + role_key varchar(100), + workflow_key varchar(100) + constraint wf_roles_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + role_name varchar(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 varchar(100), transition_name varchar(100) @@ -90,18 +120,26 @@ constraint wf_transition_workflow_fk references wf_workflows(workflow_key) on delete cascade, - -- purely for UI purposes + -- what role does this transition belong to + -- (only for user-triggered transitions) + role_key varchar(100), + -- so we can display transitions in some logical order -- sort_order integer constraint wf_transition_order_ck check (sort_order > 0), trigger_type varchar(40) 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), 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 ' @@ -128,16 +166,17 @@ guard_callback varchar(100), guard_custom_arg text, guard_description varchar(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 = '' or direction = 'out'), + check (guard_callback = '' 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); @@ -151,19 +190,6 @@ 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) - 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 @@ -175,11 +201,12 @@ attribute_id integer 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 ' @@ -188,43 +215,42 @@ '; -create table wf_transition_assignment_map ( +create table wf_transition_role_assign_map ( workflow_key varchar(100) - constraint wf_trans_asgn_map_workflow_fk + constraint wf_role_asgn_map_workflow_fk references wf_workflows(workflow_key) on delete cascade, transition_key varchar(100), - assign_transition_key varchar(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 varchar(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 */ @@ -256,9 +282,35 @@ insert into wf_contexts (context_key, context_name) values ('default', 'Default Context'); -commit; +create table wf_context_workflow_info ( + context_key varchar(100) + constraint wf_context_wf_context_fk + references wf_contexts + on delete cascade, + workflow_key varchar(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 varchar(100) constraint wf_context_trans_context_fk @@ -270,6 +322,8 @@ /* information for the transition in the context */ /* The integer of minutes this task is estimated to take */ estimated_minutes integer, + /* Instructions for how to complete the task. Will be displayed on the task page. */ + instructions text, /* * Will be called when the transition is enabled/fired. * signature: (case_id in integer, transition_key in varchar, custom_arg in varchar2) @@ -279,13 +333,6 @@ fire_callback varchar(100), fire_custom_arg text, /* - * 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), - assignment_custom_arg text, - /* * 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 @@ -322,22 +369,18 @@ notification_callback varchar(100), notification_custom_arg text, /* - * Unassigned callback - * Will be called whenever a task becomes unassigned - * Signature: (case_id in integer, transition_key in varchar, 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 varchar(100), unassigned_custom_arg text, - /* name of the privilege we should check before allowing access - * to task information. - */ - access_privilege text, - /* 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); @@ -348,6 +391,37 @@ '; +create table wf_context_role_info ( + context_key varchar(100) + constraint wf_context_role_context_fk + references wf_contexts(context_key) + on delete cascade, + workflow_key varchar(100) + constraint wf_context_role_workflow_fk + references wf_workflows(workflow_key) + on delete cascade, + role_key varchar(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 varchar(100), + assignment_custom_arg varchar(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 varchar(100) not null constraint wf_context_panels_context_fk @@ -358,15 +432,23 @@ references wf_workflows(workflow_key) on delete cascade, transition_key varchar(100) not null, - sort_key integer not null, + sort_order integer not null, header varchar(200) not null, template_url varchar(500) not null, - /* table constraints */ + /* 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); @@ -386,23 +468,23 @@ constraint wf_context_assign_workflow_fk references wf_workflows(workflow_key) on delete cascade, - transition_key varchar(100), + role_key varchar(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, + -- 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. '; @@ -426,7 +508,8 @@ constraint wf_cases_context_fk references wf_contexts(context_key) on delete cascade, - object_id integer constraint wf_cases_object_fk + object_id integer + constraint wf_cases_object_fk references acs_objects(object_id) on delete cascade, -- a toplevel state of the case @@ -445,41 +528,45 @@ 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, workflow_key varchar(100), - transition_key varchar(100), + role_key varchar(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), + -- 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. '; @@ -493,10 +580,12 @@ 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), + -- 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 ); @@ -543,9 +632,9 @@ references users(user_id) on delete cascade, hold_timeout timestamp, - /* -- */ + -- 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); @@ -566,8 +655,9 @@ constraint wf_task_party_fk references parties(party_id) 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); @@ -613,8 +703,9 @@ consumed_journal_id integer 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); @@ -641,8 +732,9 @@ constraint wf_attr_val_audit_journal_fk references journal_entries(journal_id), attr_value text, + -- 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); @@ -664,6 +756,7 @@ t.workflow_key, t.sort_order, t.trigger_type, + t.role_key, c.context_key, c.context_name from wf_transitions t, wf_contexts c; @@ -682,13 +775,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, @@ -699,8 +792,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 LEFT OUTER JOIN wf_context_transition_info ct on (ct.workflow_key = t.workflow_key and ct.transition_key = t.transition_key and @@ -709,6 +801,25 @@ /* + * 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 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_contexts c, wf_roles r LEFT OUTER JOIN wf_context_role_info cr +on (cr.workflow_key = r.workflow_key and + cr.role_key = r.role_key) +where cr.context_key = c.context_key; + + + +/* * This view makes it easy to get the input/output places of a transition */ create view wf_transition_places as @@ -748,12 +859,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, @@ -763,10 +873,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 @@ -822,6 +932,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