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
