Index: openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql,v
diff -u -N -r1.5 -r1.6
--- openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql	21 Jan 2003 18:05:48 -0000	1.5
+++ openacs-4/packages/workflow/sql/postgresql/workflow-tables-create.sql	3 Feb 2003 12:22:47 -0000	1.6
@@ -44,160 +44,130 @@
 -- this workflows table.
 create table workflows (
   workflow_id             integer
-                          constraint workflows_pk
+                          constraint wfs_pk
                           primary key
-                          constraint workflows_workflow_id_fk
+                          constraint wfs_workflow_id_fk
                           references acs_objects(object_id)
                           on delete cascade,
   short_name              varchar(100)
-                          constraint workflows_short_name_nn
+                          constraint wfs_short_name_nn
                           not null,
   pretty_name             varchar(200)
-                          constraint workflows_pretty_name_nn
+                          constraint wfs_pretty_name_nn
                           not null,
   object_id               integer
-                          constraint workflows_object_id_fk
+                          constraint wfs_object_id_fk
                           references acs_objects(object_id)
                           on delete cascade,
   package_key             varchar(100)
-                          constraint workflows_apm_package_types_fk
+                          constraint wfs_package_key_nn
+                          not null
+                          constraint wfs_apm_package_types_fk
                           references apm_package_types(package_key),
   -- object_id points to either a package type, package instance, or single workflow case
   -- For Bug Tracker, every package instance will get its own workflow instance that is a copy
   -- of the workflow instance for the Bug Tracker package type
   object_type             varchar(1000)
-                          constraint workflows_object_type_nn
+                          constraint wfs_object_type_nn
                           not null
-                          constraint workflows_object_type_fk
+                          constraint wfs_object_type_fk
                           references acs_object_types(object_type)
                           on delete cascade,
-  constraint workflows_oid_sn_un
-  unique (object_id, short_name)
+  constraint wfs_oid_sn_un
+  unique (package_key, object_id, short_name)
 );
 
 -- For callbacks on workflow
 create table workflow_callbacks (
   workflow_id             integer
-                          constraint workflow_callbacks_wid_nn
+                          constraint wf_cbks_wid_nn
                           not null
-                          constraint workflow_callbacks_wid_fk
+                          constraint wf_cbks_wid_fk
                           references workflows(workflow_id)
                           on delete cascade,
   acs_sc_impl_id          integer
-                          constraint workflow_callbacks_sci_nn
+                          constraint wf_cbks_sci_nn
                           not null
-                          constraint workflow_callbacks_sci_fk
+                          constraint wf_cbks_sci_fk
                           references acs_sc_impls(impl_id)
                           on delete cascade,
   sort_order              integer
-                          constraint workflow_callbacks_so_nn
+                          constraint wf_cbks_so_nn
                           not null,
-  constraint workflow_callbacks_pk
+  constraint wf_cbks_pk
   primary key (workflow_id, acs_sc_impl_id)
 );
 
 create table workflow_roles (
   role_id                 integer
-                          constraint workflow_roles_pk
+                          constraint wf_roles_pk
                           primary key,
   workflow_id             integer
-                          constraint workflow_roles_workflow_id_fk
+                          constraint wf_roles_workflow_id_fk
                           references workflows(workflow_id)
                           on delete cascade,
   short_name              varchar(100)
-                          constraint workflow_roles_short_name_nn
+                          constraint wf_roles_short_name_nn
                           not null,
   pretty_name             varchar(200)
-                          constraint workflow_roles_pretty_name_nn
+                          constraint wf_roles_pretty_name_nn
+                          not null,
+  sort_order              integer
+                          constraint wf_roles_so_nn
                           not null
 );
 
 create sequence workflow_roles_seq;
 
--- Static role-party map
-create table workflow_role_default_parties (
-  role_id                 integer
-                          constraint workflow_role_default_parties_rid_nn
-                          not null
-                          constraint workflow_role_default_parties_rid_fk
-                          references workflow_roles(role_id)
-                          on delete cascade,
-  party_id                integer
-                          constraint workflow_role_default_parties_pid_nn
-                          not null
-                          constraint workflow_role_default_parties_pid_fk
-                          references parties(party_id)
-                          on delete cascade,
-  constraint workflow_role_default_parties_pk
-  primary key (role_id, party_id)
-);
-
--- Static map between roles and parties allowed to be in those roles
-create table workflow_role_allowed_parties (
-  role_id                 integer
-                          constraint workflow_role_allowed_parties_rid_nn
-                          not null
-                          constraint workflow_role_allowed_parties_rid_fk
-                          references workflow_roles(role_id)
-                          on delete cascade,
-  party_id                integer
-                          constraint workflow_role_allowed_parties_pid_nn
-                          not null
-                          constraint workflow_role_allowed_parties_pid_fk
-                          references parties(party_id)
-                          on delete cascade,
-  constraint workflow_role_allowed_parties_pk
-  primary key (role_id, party_id)
-);
-
 -- Callbacks for roles
 create table workflow_role_callbacks (
   role_id                 integer
-                          constraint workflow_role_callbacks_role_id_nn
+                          constraint wf_role_cbks_role_id_nn
                           not null
-                          constraint workflow_role_callbacks_role_id_fk
+                          constraint wf_role_cbks_role_id_fk
                           references workflow_roles(role_id)
                           on delete cascade,
   acs_sc_impl_id          integer
-                          constraint workflow_role_callbacks_contract_id_nn
+                          constraint wf_role_cbks_contract_id_nn
                           not null
-                          constraint workflow_role_callbacks_contract_id_fk
+                          constraint wf_role_cbks_contract_id_fk
                           references acs_sc_impls(impl_id)
                           on delete cascade,
   -- this should be an implementation of any of the three assignment
   -- service contracts: DefaultAssignee, AssigneePickList, or 
   -- AssigneeSubQuery
   sort_order              integer
-                          constraint workflow_role_callbacks_sort_order_nn
+                          constraint wf_role_cbks_sort_order_nn
                           not null,
-  constraint workflow_role_callbacks_pk
+  constraint wf_role_cbks_pk
   primary key (role_id, acs_sc_impl_id),
-  constraint workflow_role_asgn_rol_sort_un
+  constraint wf_role_asgn_rol_sort_un
   unique (role_id, sort_order)
 );
 
 create table workflow_actions (
   action_id               integer
-                          constraint workflow_actions_pk
+                          constraint wf_acns_pk
                           primary key,
   workflow_id             integer
-                          constraint workflow_actions_workflow_id_nn
+                          constraint wf_acns_workflow_id_nn
                           not null
-                          constraint workflow_actions_workflow_id_fk
+                          constraint wf_acns_workflow_id_fk
                           references workflows(workflow_id)
                           on delete cascade,
   sort_order              integer
-                          constraint workflow_actions_sort_order_nn
+                          constraint wf_acns_sort_order_nn
                           not null,
   short_name              varchar(100)
-                          constraint workflow_actions_short_name_nn
+                          constraint wf_acns_short_name_nn
                           not null,
   pretty_name             varchar(200)
-                          constraint workflow_actions_pretty_name_nn
+                          constraint wf_acns_pretty_name_nn
                           not null,
   pretty_past_tense       varchar(200),
+  edit_fields             varchar(4000),
   assigned_role           integer
-                          constraint workflow_actions_assigned_role_fk
+                          constraint wf_acns_assigned_role_fk
                           references workflow_roles(role_id)
                           on delete set null,
   always_enabled_p        bool default 'f'
@@ -208,70 +178,71 @@
 -- Determines which roles are allowed to take certain actions
 create table workflow_action_allowed_roles (
   action_id               integer
-                          constraint workflow_action_allowed_roles_action_id_nn
+                          constraint wf_acn_alwd_roles_acn_id_nn
                           not null
-                          constraint workflow_action_allowed_roles_action_id_fk
+                          constraint wf_acn_alwd_roles_acn_id_fk
                           references workflow_actions(action_id)
                           on delete cascade,
   role_id                 integer
-                          constraint workflow_action_allowed_roles_role_id_nn
+                          constraint wf_acn_alwd_roles_role_id_nn
                           not null
-                          constraint workflow_action_allowed_roles_role_id_fk
+                          constraint wf_acn_alwd_roles_role_id_fk
                           references workflow_roles(role_id)
                           on delete cascade,
-  constraint workflow_action_allowed_roles_pk
+  constraint wf_acn_alwd_roles_pk
   primary key (action_id, role_id)
 );
 
 -- Determines which privileges (on the object treated by a workflow case) will allow
 -- users to take certain actions
 create table workflow_action_privileges (
   action_id               integer
-                          constraint workflow_action_privileges_action_id_nn
+                          constraint wf_acn_priv_acn_id_nn
                           not null
-                          constraint workflow_action_privileges_action_id_fk
+                          constraint wf_acn_priv_acn_id_fk
                           references workflow_actions(action_id)
                           on delete cascade,
   privilege               varchar(100)
-                          constraint workflow_action_privileges_privilege_nn
+                          constraint wf_acn_priv_privilege_nn
                           not null
-                          constraint workflow_action_privileges_privilege_fk
+                          constraint wf_acn_priv_privilege_fk
                           references acs_privileges(privilege)
                           on delete cascade,
-  constraint workflow_action_privileges_pk
+  constraint wf_acn_priv_pk
   primary key (action_id, privilege)
 );
 
 -- For callbacks on actions
 create table workflow_action_callbacks (
   action_id               integer
-                          constraint workflow_action_callbacks_action_id_nn
+                          constraint wf_acn_cbks_acn_id_nn
                           not null
-                          constraint workflow_action_callbacks_action_id_fk
+                          constraint wf_acn_cbks_acn_id_fk
                           references workflow_actions(action_id)
                           on delete cascade,
   acs_sc_impl_id          integer
-                          constraint workflow_action_callbacks_sci_nn
+                          constraint wf_acn_cbks_sci_nn
                           not null
-                          constraint workflow_action_callbacks_sci_fk
+                          constraint wf_acn_cbks_sci_fk
                           references acs_sc_impls(impl_id)
                           on delete cascade,
   sort_order              integer
-                          constraint workflow_action_callbacks_sort_order_nn
+                          constraint wf_acn_cbks_sort_order_nn
                           not null,
-  constraint workflow_action_callbacks_pk
+  constraint wf_acn_cbks_pk
   primary key (action_id, acs_sc_impl_id)
 );
 
+-- For the initial action, which fires when a new case is started
 create table workflow_initial_action (
   workflow_id             integer
-                          constraint workflow_initial_action_pk
+                          constraint wf_initial_acn_pk
                           primary key
-                          constraint workflow_initial_action_wf_fk
+                          constraint wf_initial_acn_wf_fk
                           references workflows(workflow_id)
                           on delete cascade,
   action_id               integer
-                          constraint workflow_initial_action_act_fk
+                          constraint wf_initial_acn_act_fk
                           references workflow_actions(action_id)
                           on delete cascade
 );
@@ -283,59 +254,106 @@
 
 create table workflow_fsm_states (
   state_id                integer
-                          constraint workflow_fsm_states_pk
+                          constraint wf_fsm_states_pk
                           primary key,
   workflow_id             integer
-                          constraint workflow_fsm_states_workflow_id_nn
+                          constraint wf_fsm_states_workflow_id_nn
                           not null
-                          constraint workflow_fsm_states_workflow_id_fk
+                          constraint wf_fsm_states_workflow_id_fk
                           references workflows(workflow_id)
                           on delete cascade,
   sort_order              integer
-                          constraint workflow_fsm_states_sort_order_nn
+                          constraint wf_fsm_states_sort_order_nn
                           not null,
   -- The state with the lowest sort order is the initial state
   short_name              varchar(100)
-                          constraint workflow_fsm_states_short_name_nn
+                          constraint wf_fsm_states_short_name_nn
                           not null,
   pretty_name             varchar(200)
-                          constraint workflow_fsm_states_pretty_name_nn
-                          not null
+                          constraint wf_fsm_states_pretty_name_nn
+                          not null,
+  hide_fields             varchar(4000)
 );
 
 create sequence workflow_fsm_states_seq;
 
 create table workflow_fsm_actions (
   action_id               integer
-                          constraint workflow_fsm_actions_aid_fk
+                          constraint wf_fsm_acns_aid_fk
                           references workflow_actions(action_id)
                           on delete cascade
-                          constraint workflow_fsm_actions_pk
+                          constraint wf_fsm_acns_pk
                           primary key,
   new_state               integer
-                          constraint workflow_fsm_actions_new_state_fk
+                          constraint wf_fsm_acns_new_st_fk
                           references workflow_fsm_states(state_id)
                           on delete cascade
   -- can be null
 );
 
 -- If an action is enabled in all states it won't have any entries in this table
 -- it is enabled in all states
-create table workflow_fsm_action_enabled_in_states (
+create table workflow_fsm_action_en_in_st (
   action_id               integer
-                          constraint workflow_fsm_action_enabled_in_states_action_id_nn
+                          constraint wf_fsm_acn_enb_in_st_acn_id_nn
                           not null
-                          constraint workflow_fsm_action_enabled_in_states_action_id_fk
+                          constraint wf_fsm_acn_enb_in_st_acn_id_fk
                           references workflow_fsm_actions(action_id)
                           on delete cascade,
   state_id                integer
-                          constraint workflow_fsm_action_enabled_in_states_state_id_nn
+                          constraint wf_fsm_acn_enb_in_st_st_id_nn
                           not null
-                          constraint workflow_fsm_action_enabled_in_states_state_id_fk
+                          constraint wf_fsm_acn_enb_in_st_st_id_fk
                           references workflow_fsm_states
                           on delete cascade
 );
 
+
+
+--------------------------------------------------------
+-- Workflow level, context-dependent (assignments, etc.)
+--------------------------------------------------------
+
+
+-- Static role-party map
+create table workflow_role_default_parties (
+  role_id                 integer
+                          constraint wf_role_default_parties_rid_nn
+                          not null
+                          constraint wf_role_default_parties_rid_fk
+                          references workflow_roles(role_id)
+                          on delete cascade,
+  party_id                integer
+                          constraint wf_role_default_parties_pid_nn
+                          not null
+                          constraint wf_role_default_parties_pid_fk
+                          references parties(party_id)
+                          on delete cascade,
+  constraint wf_role_default_parties_pk
+  primary key (role_id, party_id)
+);
+
+-- Static map between roles and parties allowed to be in those roles
+create table workflow_role_allowed_parties (
+  role_id                 integer
+                          constraint wf_role_alwd_parties_rid_nn
+                          not null
+                          constraint wf_role_alwd_parties_rid_fk
+                          references workflow_roles(role_id)
+                          on delete cascade,
+  party_id                integer
+                          constraint wf_role_alwd_parties_pid_nn
+                          not null
+                          constraint wf_role_alwd_parties_pid_fk
+                          references parties(party_id)
+                          on delete cascade,
+  constraint wf_role_alwd_parties_pk
+  primary key (role_id, party_id)
+);
+
+
+
+
 ---------------------------------
 -- Case level, Generic Model
 ---------------------------------
@@ -344,86 +362,45 @@
 
 create table workflow_cases (
   case_id                 integer
-                          constraint workflow_cases_pk
+                          constraint wf_cases_pk
                           primary key,
   workflow_id             integer
-                          constraint workflow_cases_workflow_id_nn
+                          constraint wf_cases_workflow_id_nn
                           not null
-                          constraint workflow_cases_workflow_id_fk
+                          constraint wf_cases_workflow_id_fk
                           references workflows(workflow_id)
                           on delete cascade,
   object_id               integer
-                          constraint workflow_cases_object_id_nn
+                          constraint wf_cases_object_id_nn
                           not null
-                          constraint workflow_cases_object_id_fk
+                          constraint wf_cases_object_id_fk
                           references acs_objects(object_id)
                           on delete cascade
-                          constraint workflow_cases_object_id_un
+                          constraint wf_cases_object_id_un
                           unique
   -- the object which this case is about, e.g. the acs-object for a bug-tracker bug
 );
 
-create sequence workflow_case_log_seq;
-
-create table workflow_case_log (
-  entry_id                integer
-                          constraint workflow_case_log_pk
-                          primary key,
-  case_id                 integer
-                          constraint workflow_case_log_case_id_fk 
-                          references workflow_cases(case_id)
-                          on delete cascade,
-  action_id               integer
-                          constraint workflow_case_log_action_id_fk
-                          references workflow_actions(action_id)
-                          on delete cascade,
-  user_id                 integer
-                          constraint workflow_case_log_user_id_fk
-                          references users(user_id)
-                          on delete cascade,
-  action_date             timestamp
-                          constraint workflow_case_log_action_date_nn
-                          not null 
-                          default now(),
-  comment                 text,
-  comment_format          varchar(30) default 'plain' not null
-                          constraint  bt_bug_actions_comment_format_ck
-                          check (comment_format in ('html', 'plain', 'pre'))
-);
-
-create table workflow_case_log_data (
-  entry_id                integer
-                          constraint workflow_case_log_data_eid_nn
-                          not null
-                          constraint workflow_case_log_data_eid_fk
-                          references workflow_case_log(entry_id)
-                          on delete cascade,
-  key                     varchar(50),
-  value                   varchar(4000),
-  constraint workflow_case_log_data_pk
-  primary key (entry_id, key)
-);
-
 create table workflow_case_role_party_map (
   case_id                 integer
-                          constraint workflow_case_role_party_map_case_id_nn
+                          constraint wf_case_role_pty_map_case_id_nn
                           not null
-                          constraint workflow_case_role_party_map_case_id_fk
+                          constraint wf_case_role_pty_map_case_id_fk
                           references workflow_cases(case_id)
                           on delete cascade,
   role_id                 integer
-                          constraint workflow_case_role_party_map_case_id_nn
+                          constraint wf_case_role_pty_map_case_id_nn
                           not null
-                          constraint workflow_case_role_party_map_case_id_fk
+                          constraint wf_case_role_pty_map_case_id_fk
                           references workflow_roles(role_id)
                           on delete cascade,
   party_id                integer
-                          constraint workflow_case_role_party_map_party_id_nn
+                          constraint wf_case_role_pty_map_pty_id_nn
                           not null
-                          constraint workflow_case_role_party_map_party_id_fk
+                          constraint wf_case_role_pty_map_pty_id_fk
                           references parties(party_id)
                           on delete cascade,
-  constraint workflow_case_role_party_map_pk
+  constraint wf_case_role_pty_map_pk
   primary key (case_id, role_id, party_id)
 );
 
@@ -433,13 +410,66 @@
 
 create table workflow_case_fsm (
   case_id                 integer
-                          constraint workflow_case_fsm_case_id_nn
+                          constraint wf_case_fsm_case_id_nn
                           not null
-                          constraint workflow_case_fsm_case_id_fk
+                          constraint wf_case_fsm_case_id_fk
                           references workflow_cases(case_id)
                           on delete cascade,
   current_state           integer
-                          constraint workflow_case_fsm_state_id_fk
+                          constraint wf_case_fsm_st_id_fk
                           references workflow_fsm_states(state_id)
                           on delete cascade
 );
+
+
+---------------------------------
+-- Case level, Activity Log
+---------------------------------
+
+begin;
+    select content_type__create_type (
+        'workflow_activity_log', -- content_type
+	'content_revision',      -- supertype
+	'Workflow Activity Log', -- pretty_name
+	'Workflow Activity Log', -- pretty_plural
+	'workflow_case_log',     -- table_name
+	'entry_id',              -- id_column
+    );
+end;
+
+
+create sequence workflow_case_log_seq;
+
+create table workflow_case_log (
+  entry_id                integer
+                          constraint wf_case_log_eid_fk
+                          references cr_revisions on delete cascade
+                          constraint wf_case_log_pk
+                          primary key,
+  case_id                 integer
+                          constraint wf_case_log_case_id_fk 
+                          references workflow_cases(case_id)
+                          on delete cascade,
+  action_id               integer
+                          constraint wf_case_log_acn_id_fk
+                          references workflow_actions(action_id)
+                          on delete cascade,
+  comment_format          varchar(50) 
+                          default 'text/plain'
+                          constraint wf_clog_comment_format_nn
+                          not null
+);
+
+create table workflow_case_log_data (
+  entry_id                integer
+                          constraint wf_case_log_data_eid_nn
+                          not null
+                          constraint wf_case_log_data_eid_fk
+                          references workflow_case_log(entry_id)
+                          on delete cascade,
+  key                     varchar(50),
+  value                   varchar(4000),
+  constraint wf_case_log_data_pk
+  primary key (entry_id, key)
+);
+
