Index: openacs-4/packages/tasks/sql/postgresql/upgrade/upgrade-0.1d8-0.1d9.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/tasks/sql/postgresql/upgrade/upgrade-0.1d8-0.1d9.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/tasks/sql/postgresql/upgrade/upgrade-0.1d8-0.1d9.sql 1 Mar 2006 12:23:19 -0000 1.1 @@ -0,0 +1,314 @@ +-- tasks/sql/postgresql/upgrade/upgrade-0.1d8-0.1d9.sql +-- +-- @author Matthew Geddert (openacs@geddert.com) +-- @creation-date 2006-02-22 +-- @cvs-id $Id: upgrade-0.1d8-0.1d9.sql,v 1.1 2006/03/01 12:23:19 matthewg Exp $ + + +-- We first validate that we can in fact delete +create or replace function inline_0() returns integer as ' +declare + v_valid_p boolean; + v_tpt_row record; + v_tt_row record; + v_tpi_row record; + +begin + + if ( select count(*) from t_process_instances where object_id is not null ) > 0 then + raise EXCEPTION '' -20000: tasks/sql/postgresql/upgrade/upgrade-0.1d8-0.1d9.sql cannot upgrade because t_process_instances object_id column is not null.''; + end if; + + if ( select count(*) from apm_packages where package_key = ''tasks'' ) > 1 then + raise EXCEPTION '' -20000: tasks/sql/postgresql/upgrade/upgrade-0.1d8-0.1d9.sql cannot upgrade because there is more than one tasks instance.''; + end if; + + if ( select count(*) from t_tasks where party_id not in ( select member_id from group_distinct_member_map where group_id = ''-2'' )) > 0 then + raise EXCEPTION '' -20000: tasks/sql/postgresql/upgrade/upgrade-0.1d8-0.1d9.sql cannot upgrade because assigned to parties that are not in -2.''; + end if; + + + -- t_processes changes + alter table t_processes add column assignee_id integer; + + + -- + -- t_procss_instances changes + + update t_process_instances set object_id = party_id ; +-- alter table t_process_instances drop constraint t_process_instances_party_fk; + alter table t_process_instances drop column party_id; + + + -- + -- t_process_tasks party_id, object_id + + for v_tpt_row in select * from t_process_tasks where object_id is not null + loop + + -- create forward link + insert into acs_data_links (rel_id, object_id_one, object_id_two) + values (nextval(''acs_data_links_seq''), v_tpt_row.task_id, v_tpt_row.object_id ); + + -- create backward link + insert into acs_data_links (rel_id, object_id_one, object_id_two) + values (nextval(''acs_data_links_seq''), v_tpt_row.object_id, v_tpt_row.task_id ); + + end loop; + return 0; + + update t_process_tasks set object_id = party_id; + alter table t_process_tasks drop constraint t_process_tasks_party_fk; + alter table t_process_tasks drop column party_id; + + -- + -- t_tasks party_id, object_id + + for v_tt_row in select * from t_tasks where object_id is not null + loop + + -- create forward link + insert into acs_data_links (rel_id, object_id_one, object_id_two) + values (nextval(''acs_data_links_seq''), v_tt_row.task_id, v_tt_row.object_id ); + + -- create backward link + insert into acs_data_links (rel_id, object_id_one, object_id_two) + values (nextval(''acs_data_links_seq''), v_tt_row.object_id, v_tt_row.task_id ); + + end loop; + return 0; + + update t_tasks set object_id = party_id; + alter table t_tasks drop constraint t_tasks_party_fk; + alter table t_tasks drop column party_id; + + + + return 0; + +end;' language 'plpgsql'; +-- alter table t_process_tasks add column tester boolean; +-- Calling and droping the function +select inline_0(); +drop function inline_0(); + + +drop function tasks_task__new (integer,integer,integer,integer,integer,varchar,text,varchar,text,integer,integer,timestamptz,timestamptz,integer,integer,varchar,integer,integer); + +drop function tasks_process_task__new (integer,integer,integer,integer,integer,integer,varchar,text,varchar,text,integer,integer,numeric,numeric,integer,integer,varchar,integer,integer); + +drop function tasks_process_instance__new (integer,integer,integer,integer,integer,integer,integer,varchar,integer); + +drop function tasks_process__new (integer,varchar,text,varchar,integer,integer,integer,varchar,integer) + + +select define_function_args('tasks_task__new','task_id,process_instance_id,process_task_id,object_id,title,description,mime_type,comment,status_id,priority,start_date,due_date,package_id,creation_user,creation_ip,context_id,assignee_id'); + +create or replace function tasks_task__new (integer,integer,integer,integer,varchar,text,varchar,text,integer,integer,timestamptz,timestamptz,integer,integer,varchar,integer,integer) +returns integer as ' +declare + p_task_id alias for $1; + p_process_instance_id alias for $2; + p_process_task_id alias for $3; + p_object_id alias for $4; + p_title alias for $5; + p_description alias for $6; + p_mime_type alias for $7; + p_comment alias for $8; + p_status_id alias for $9; + p_priority alias for $10; + p_start_date alias for $11; + p_due_date alias for $12; + p_package_id alias for $13; + p_creation_user alias for $14; + p_creation_ip alias for $15; + p_context_id alias for $16; + p_assignee_id alias for $17; + v_task_id integer; + v_start_date timestamptz; +begin + v_task_id:= acs_object__new( + p_task_id, + ''tasks_task'', + now(), + p_creation_user, + p_creation_ip, + coalesce(p_context_id, p_package_id), + ''t'', + p_title, + p_package_id + ); + + if p_start_date is null then + v_start_date := now(); + else + v_start_date := p_start_date; + end if; + + insert into t_tasks + (task_id, process_instance_id, process_task_id, object_id, + title, description, mime_type, comment, status_id, priority, + start_date, due_date, assignee_id) + values + (v_task_id, p_process_instance_id, p_process_task_id, + p_object_id, p_title, p_description, p_mime_type, p_comment, + p_status_id, p_priority, v_start_date, p_due_date, p_assignee_id); + + return v_task_id; +end; +' language 'plpgsql'; + + +select define_function_args('tasks_process_task__new','task_id,process_id,open_action_id,closing_action_id,object_id,title,description,mime_type,comment,status_id,priority,start,due,package_id,creation_user,creation_ip,context_id,assignee_id'); + +create or replace function tasks_process_task__new (integer,integer,integer,integer,integer,varchar,text,varchar,text,integer,integer,numeric,numeric,integer,integer,varchar,integer,integer) +returns integer as ' +declare + p_task_id alias for $1; + p_process_id alias for $2; + p_open_action_id alias for $3; + p_closing_action_id alias for $4; + p_object_id alias for $5; + p_title alias for $6; + p_description alias for $7; + p_mime_type alias for $8; + p_comment alias for $9; + p_status_id alias for $10; + p_priority alias for $11; + p_start alias for $12; + p_due alias for $13; + p_package_id alias for $14; + p_creation_user alias for $15; + p_creation_ip alias for $16; + p_context_id alias for $17; + p_assignee_id alias for $18; + v_task_id integer; +begin + v_task_id:= acs_object__new( + p_task_id, + ''tasks_process_task'', + now(), + p_creation_user, + p_creation_ip, + coalesce(p_context_id, p_package_id), + ''t'', + p_title, + p_package_id + ); + + insert into t_process_tasks + (task_id, process_id, open_action_id, closing_action_id, + object_id, title, description, mime_type, comment, status_id, + priority, start, due, assignee_id) + values + (v_task_id, p_process_id, p_open_action_id, p_closing_action_id, + p_object_id, p_title, p_description, p_mime_type, + p_comment, p_status_id, p_priority, p_start, p_due, p_assignee_id); + + return v_task_id; +end; +' language 'plpgsql'; + + +select define_function_args('tasks_process_instance__new','process_instance_id,process_id,case_id,object_id,package_id,creation_user,creation_ip,context_id'); + +create or replace function tasks_process_instance__new (integer,integer,integer,integer,integer,integer,varchar,integer) +returns integer as ' +declare + p_process_instance_id alias for $1; + p_process_id alias for $2; + p_case_id alias for $3; + p_object_id alias for $4; + p_package_id alias for $5; + p_creation_user alias for $6; + p_creation_ip alias for $7; + p_context_id alias for $8; + v_process_instance_id integer; +begin + v_process_instance_id:= acs_object__new( + p_process_instance_id, + ''tasks_process_instance'', + now(), + p_creation_user, + p_creation_ip, + coalesce(p_context_id, p_package_id), + ''t'', + ''process instance of process '' || p_process_id || '' for object '' || p_object_id, + p_package_id + ); + + insert into t_process_instances + (process_instance_id, process_id, case_id, object_id) + values + (v_process_instance_id, p_process_id, p_case_id, p_object_id); + + return v_process_instance_id; +end; +' language 'plpgsql'; + +select define_function_args('tasks_process__new','process_id,title,description,mime_type,workflow_id,assignee_id,package_id,creation_user,creation_ip,context_id'); + +create or replace function tasks_process__new (integer,varchar,text,varchar,integer,integer,integer,integer,varchar,integer) +returns integer as ' +declare + p_process_id alias for $1; + p_title alias for $2; + p_description alias for $3; + p_mime_type alias for $4; + p_workflow_id alias for $5; + p_assignee_id alias for $6; + p_package_id alias for $7; + p_creation_user alias for $8; + p_creation_ip alias for $9; + p_context_id alias for $10; + v_process_id integer; +begin + v_process_id:= acs_object__new( + p_process_id, + ''tasks_process'', + now(), + p_creation_user, + p_creation_ip, + coalesce(p_context_id, p_package_id), + ''t'', + p_title, + p_package_id + ); + + insert into t_processes + (process_id, title, description, mime_type, workflow_id, assignee_id) + values + (v_process_id, p_title, p_description, p_mime_type, p_workflow_id, p_assignee_id); + + return v_process_id; +end; +' language 'plpgsql'; + + + + + + + + + + +-- drop function tasks__completion_date (integer); +-- drop function takss__completion_user (integer); + + +-- alter table t_process_tasks drop constraint t_process_tasks_object_id_fk; + +-- alter table t_process_tasks drop column party_id + + +-- t_tasks, t_process_tasks, t_process_instances + +-- create application_data links for object_id +-- delete object_id values +-- move party_id to object_id + + + +-- alter table t_process_tasks drop column party_id; +-- alter table t_tasks drop column party_id;