Index: openacs-4/contrib/obsolete-packages/acs-workflow/sql/upgrade/upgrade-4.0-4.0.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/acs-workflow/sql/upgrade/Attic/upgrade-4.0-4.0.1.sql,v diff -u -N --- openacs-4/contrib/obsolete-packages/acs-workflow/sql/upgrade/upgrade-4.0-4.0.1.sql 13 Mar 2001 22:59:27 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,346 +0,0 @@ --- Recreating almost all foreign key constraints in order to add "on delete cascade" - --- wf_workflows - -alter table wf_workflows drop constraint wf_workflows_workflow_key_fk; -alter table wf_workflows add ( - constraint wf_workflows_workflow_key_fk - foreign key(workflow_key) references acs_object_types(object_type) on delete cascade -); - --- wf_places - -alter table wf_places drop constraint wf_place_workflow_fk; -alter table wf_places add ( - constraint wf_place_workflow_fk - foreign key(workflow_key) references wf_workflows(workflow_key) on delete cascade -); - --- wf_transitions - -alter table wf_transitions drop constraint wf_transition_workflow_fk; -alter table wf_transitions add ( - constraint wf_transition_workflow_fk - foreign key(workflow_key) references wf_workflows(workflow_key) on delete cascade -); - --- wf_arcs - -alter table wf_arcs drop constraint wf_ts_arc_workflow_fk; -alter table wf_arcs add ( - constraint wf_ts_arc_workflow_fk - foreign key(workflow_key) references wf_workflows(workflow_key) on delete cascade -); - -alter table wf_arcs drop constraint wf_arc_transition_fk; -alter table wf_arcs add ( - constraint wf_arc_transition_fk - foreign key(workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) on delete cascade -); - -alter table wf_arcs drop constraint wf_arc_place_fk; -alter table wf_arcs add ( - constraint wf_arc_place_fk - 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); -create index wf_arcs_wf_key_place_key_idx on wf_arcs(workflow_key, place_key); - --- wf_attribute_info - -alter table wf_attribute_info add ( - constraint wf_attribute_info_attribute_pk primary key(attribute_id) -); - -alter table wf_attribute_info drop constraint wf_attribute_info_attribute_fk; -alter table wf_attribute_info add ( - constraint wf_attribute_info_attribute_fk - foreign key(attribute_id) references acs_attributes(attribute_id) on delete cascade -); - --- wf_transition_attribute_map - -alter table wf_transition_attribute_map drop constraint wf_trans_attr_map_workflow_fk; -alter table wf_transition_attribute_map add ( - constraint wf_trans_attr_map_workflow_fk - foreign key(workflow_key) references wf_workflows(workflow_key) on delete cascade -); - -alter table wf_transition_attribute_map drop constraint wf_trans_attr_map_trans_fk; -alter table wf_transition_attribute_map add ( - constraint wf_trans_attr_map_trans_fk - foreign key(workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) on delete cascade -); - --- wf_transition_assignment_map - -alter table wf_transition_assignment_map drop constraint wf_trans_asgn_map_workflow_fk; -alter table wf_transition_assignment_map add ( - constraint wf_trans_asgn_map_workflow_fk - foreign key(workflow_key) references wf_workflows(workflow_key) on delete cascade -); - -alter table wf_transition_assignment_map drop constraint wf_trans_asgn_map_trans_fk; -alter table wf_transition_assignment_map add ( - constraint wf_trans_asgn_map_trans_fk - foreign key(workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) on delete cascade -); - -alter table wf_transition_assignment_map drop constraint wf_tr_asgn_map_asgn_trans_fk; -alter table wf_transition_assignment_map add ( - constraint wf_tr_asgn_map_asgn_trans_fk - foreign key(workflow_key, assign_transition_key) references wf_transitions(workflow_key, transition_key) on delete cascade -); - -create index wf_trans_asgn_map_wf_trans_idx on wf_transition_assignment_map(workflow_key, transition_key); -create index wf_trans_asgn_map_wf_as_tr_idx on wf_transition_assignment_map(workflow_key, assign_transition_key); - --- wf_context_transition_info - -alter table wf_context_transition_info drop constraint wf_context_trans_trans_fk; -alter table wf_context_transition_info add ( - constraint wf_context_trans_trans_fk - foreign key(workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) on delete cascade -); - -create index wf_ctx_trans_wf_trans_idx on wf_context_transition_info(workflow_key, transition_key); - --- wf_context_assignments - -alter table wf_context_assignments drop constraint wf_context_assign_context_fk; -alter table wf_context_assignments add ( - constraint wf_context_assign_context_fk - foreign key(context_key) references wf_contexts(context_key) on delete cascade -); - -alter table wf_context_assignments drop constraint wf_context_assign_workflow_fk; -alter table wf_context_assignments add ( - constraint wf_context_assign_workflow_fk - foreign key(workflow_key) references wf_workflows(workflow_key) on delete cascade -); - -alter table wf_context_assignments drop constraint wf_context_assign_party_fk; -alter table wf_context_assignments add ( - constraint wf_context_assign_party_fk - foreign key(party_id) references parties(party_id) on delete cascade -); - -alter table wf_context_assignments drop constraint wf_context_assign_trans_fk; -alter table wf_context_assignments add ( - constraint wf_context_assign_trans_fk - foreign key(workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) on delete cascade -); - -create index wf_ctx_assg_workflow_trans_idx on wf_context_assignments(workflow_key, transition_key); - --- wf_cases - -alter table wf_cases drop constraint wf_cases_acs_object_fk; -alter table wf_cases add ( - constraint wf_cases_acs_object_fk - foreign key(case_id) references acs_objects(object_id) on delete cascade -); - -alter table wf_cases drop constraint wf_cases_workflow_fk; -alter table wf_cases add ( - constraint wf_cases_workflow_fk - foreign key(workflow_key) references wf_workflows(workflow_key) on delete cascade -); - -alter table wf_cases drop constraint wf_cases_context_fk; -alter table wf_cases add ( - constraint wf_cases_context_fk - foreign key(context_key) references wf_contexts(context_key) on delete cascade -); - -alter table wf_cases drop constraint wf_cases_object_fk; -alter table wf_cases add ( - constraint wf_cases_object_fk - foreign key(object_id) references acs_objects(object_id) on delete cascade -); - -create index wf_cases_workflow_key_idx on wf_cases(workflow_key); -create index wf_cases_context_key_idx on wf_cases(context_key); -create index wf_cases_object_id_idx on wf_cases(object_id); - --- wf_case_assignments - -alter table wf_case_assignments drop constraint wf_case_assign_fk; -alter table wf_case_assignments add ( - constraint wf_case_assign_fk - foreign key(case_id) references wf_cases(case_id) on delete cascade -); - -alter table wf_case_assignments drop constraint wf_case_assign_party_fk; -alter table wf_case_assignments add ( - constraint wf_case_assign_party_fk - foreign key(party_id) references parties(party_id) on delete cascade -); - -alter table wf_case_assignments drop constraint wf_case_assign_trans_fk; -alter table wf_case_assignments add ( - constraint wf_case_assign_trans_fk - foreign key(workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) on delete cascade -); - -create index wf_case_assgn_party_idx on wf_case_assignments(party_id); - --- wf_case_deadlines - -alter table wf_case_deadlines drop constraint wf_case_deadline_fk; -alter table wf_case_deadlines add ( - constraint wf_case_deadline_fk - foreign key(case_id) references wf_cases(case_id) on delete cascade -); - -alter table wf_case_deadlines drop constraint wf_case_deadline_trans_fk; -alter table wf_case_deadlines add ( - constraint wf_case_deadline_trans_fk - foreign key(workflow_key, transition_key) references wf_transitions(workflow_key, transition_key) on delete cascade -); - --- wf_tasks - -alter table wf_tasks drop constraint wf_task_case_fk; -alter table wf_tasks add ( - constraint wf_task_case_fk - foreign key(case_id) references wf_cases(case_id) on delete cascade -); - -alter table wf_tasks drop constraint wf_task_holding_user_fk; -alter table wf_tasks add ( - constraint wf_task_holding_user_fk - foreign key(holding_user) references users(user_id) on delete cascade -); - -create index wf_tasks_case_id_idx on wf_tasks(case_id); -create index wf_tasks_holding_user_idx on wf_tasks(holding_user); - --- wf_task_assignments - -alter table wf_task_assignments drop constraint wf_task_assign_task_fk; -alter table wf_task_assignments add ( - constraint wf_task_assign_task_fk - foreign key(task_id) references wf_tasks(task_id) on delete cascade -); - -alter table wf_task_assignments drop constraint wf_task_party_fk; -alter table wf_task_assignments add ( - constraint wf_task_party_fk - foreign key(party_id) references parties(party_id) on delete cascade -); - -create index wf_task_asgn_party_id_idx on wf_task_assignments(party_id); - --- wf_tokens - -alter table wf_tokens drop constraint wf_token_workflow_instance_fk; -alter table wf_tokens add ( - constraint wf_token_workflow_instance_fk - foreign key(case_id) references wf_cases(case_id) on delete cascade -); - -create index wf_tokens_case_id_idx on wf_tokens(case_id); - --- wf_attribute_value_audit - -alter table wf_attribute_value_audit drop constraint wf_attr_val_audit_case_fk; -alter table wf_attribute_value_audit add ( - constraint wf_attr_val_audit_case_fk - foreign key(case_id) references wf_cases(case_id) on delete cascade -); - -alter table wf_attribute_value_audit drop constraint wf_attr_val_audit_attr_fk; -alter table wf_attribute_value_audit add ( - constraint wf_attr_val_audit_attr_fk - foreign key(attribute_id) references acs_attributes(attribute_id) on delete cascade -); - -create index wf_attr_val_aud_attr_id_idx on wf_attribute_value_audit(attribute_id); - - --- Added a column to wf_tasks - -alter table wf_tasks add (estimated_minutes integer); - - --- We've added the column estimated_minutes to this view. - -create or replace view wf_enabled_transitions as -select c.case_id, - t.transition_key, - t.transition_name, - t.workflow_key, - t.sort_order, - t.trigger_type, - t.context_key, - t.enable_callback, - t.enable_custom_arg, - t.fire_callback, - t.fire_custom_arg, - t.assignment_callback, - t.assignment_custom_arg, - t.time_callback, - t.time_custom_arg, - t.deadline_callback, - t.deadline_custom_arg, - t.deadline_attribute_name, - t.hold_timeout_callback, - t.hold_timeout_custom_arg, - t.estimated_minutes - from wf_transition_info t, - wf_cases c - where t.workflow_key = c.workflow_key - and t.context_key = c.context_key - and c.state = 'active' - and not exists - (select tp.place_key - from wf_transition_places tp - where tp.transition_key = t.transition_key - and tp.workflow_key = t.workflow_key - and tp.direction = 'in' - and not exists - (select tk.token_id - from wf_tokens tk - where tk.place_key = tp.place_key - and tk.case_id = c.case_id - and tk.state = 'free' - ) - ); - - --- We've added the column workflow_key to this view - -create or replace view wf_user_tasks as -select distinct ta.task_id, - ta.case_id, - ta.workflow_key, - ta.transition_key, - tr.transition_name, - ta.enabled_date, - ta.started_date, - u.user_id, - ta.state, - ta.holding_user, - ta.hold_timeout, - ta.deadline, - ta.estimated_minutes -from wf_tasks ta, - wf_task_assignments tasgn, - wf_cases c, - wf_transition_info tr, - party_approved_member_map m, - users u -where ta.state in ( 'enabled','started') -and c.case_id = ta.case_id -and c.state = 'active' -and tr.transition_key = ta.transition_key -and tr.trigger_type = 'user' -and tr.context_key = c.context_key -and tasgn.task_id = ta.task_id -and m.party_id = tasgn.party_id -and u.user_id = m.member_id; - - -