-- Upgrade script that converts the Bug Tracker to using the workflow package and the Content Repository
-- for its bugs.
--
-- @author Lars Pind
-- @author Peter Marklund
-- @creation-date 2003-02-13
---- *******
---- ******* Workflow Upgrade START
-- Prior to this sql script being sourced it is assumed that the before-upgrade Tcl callback
-- has setup worklfow instances for the Bug Tracker package type and for all package instances
-- First move all workflow data for each bug into the workflow data model
-- temporary table to map bug tracker comment format to CR mime types
create table temp_format_mime_map (
format text,
mime_type text);
insert into temp_format_mime_map (format, mime_type) values ('html', 'text/html');
insert into temp_format_mime_map (format, mime_type) values ('plain', 'text/plain');
insert into temp_format_mime_map (format, mime_type) values ('pre', 'text/fixed-width');
create or replace function inline_0 ()
returns integer as '
declare
-- Package_id loop
project_rec record;
v_workflow_id integer;
v_open_action_id integer;
-- Bug loop vars
bug_rec record;
v_case_id integer;
v_assignee_role_id integer;
v_submitter_role_id integer;
v_current_state_id integer;
-- Action loop vars
action_rec record;
v_entry_id integer;
v_action_id integer;
v_mime_type text;
begin
for project_rec in select project_id from bt_projects
loop
-- Get the bug workflow id
select workflow_id into v_workflow_id
from workflows
where short_name = ''bug''
and object_id = project_rec.project_id;
if v_workflow_id is null then
raise EXCEPTION ''You must define the workflow before running this upgade script. The workflow is created by the APM Tcl callbacks.'';
end if;
select action_id into v_open_action_id
from workflow_actions
where workflow_id = v_workflow_id
and short_name = ''open'';
for bug_rec in select b.bug_id,
b.status,
b.resolution,
b.assignee,
o.creation_user,
o.creation_date
from bt_bugs b, acs_objects o
where b.bug_id = o.object_id
and b.project_id = project_rec.project_id
loop
-- Create the case
select nextval(''workflow_cases_seq'') into v_case_id;
insert into workflow_cases (case_id, workflow_id, object_id)
values (v_case_id, v_workflow_id, bug_rec.bug_id);
-- Insert the submitter
select role_id into v_submitter_role_id
from workflow_roles
where short_name = ''submitter''
and workflow_id = v_workflow_id;
insert into workflow_case_role_party_map (case_id, role_id, party_id)
values (v_case_id, v_submitter_role_id, bug_rec.creation_user);
-- Insert the assignee
if bug_rec.assignee is not null then
select role_id into v_assignee_role_id
from workflow_roles
where short_name = ''assignee''
and workflow_id = v_workflow_id;
insert into workflow_case_role_party_map (case_id, role_id, party_id)
values (v_case_id, v_assignee_role_id, bug_rec.assignee);
end if;
-- Set the current state
select state_id into v_current_state_id
from workflow_fsm_states
where short_name = bug_rec.status
and workflow_id = v_workflow_id;
insert into workflow_case_fsm (case_id, current_state)
values (v_case_id, v_current_state_id);
for action_rec in select action,
resolution,
actor,
action_date,
comment,
comment_format
from bt_bug_actions
where bug_id = bug_rec.bug_id
loop
select action_id into v_action_id
from workflow_actions
where workflow_id = v_workflow_id
and short_name = action_rec.action;
select mime_type into v_mime_type
from temp_format_mime_map
where format = action_rec.comment_format;
-- Create the case log entry
v_entry_id := workflow_case_log_entry__new (
null,
''workflow_case_log_entry'',
v_case_id,
v_action_id,
action_rec.comment,
v_mime_type,
action_rec.actor,
null);
-- Update the creation date of the case log entry
update acs_objects set creation_date = action_rec.action_date
where object_id = v_entry_id;
-- If this is a resolve action - add the resolution code
if action_rec.action = ''resolve'' then
insert into workflow_case_log_data (entry_id, key, value)
values (v_entry_id, ''resolution'', bug_rec.resolution);
end if;
end loop;
end loop;
end loop;
return 0;
end;' language 'plpgsql';
select inline_0();
drop function inline_0();
drop table temp_format_mime_map;
-- remove the bug-tracker notifications stuff completely
create function inline_0 ()
returns integer as '
declare
v_old_notification_type_id integer;
v_new_notification_type_id integer;
row record;
begin
-- change bug_tracker_project_notif to workflow
select type_id
into v_old_notification_type_id
from notification_types
where short_name = ''bug_tracker_project_notif'';
select type_id
into v_new_notification_type_id
from notification_types
where short_name = ''workflow'';
update notification_requests set type_id = v_new_notification_type_id where type_id = v_old_notification_type_id;
-- change bug_tracker_bug_notif to workflow_case
select type_id
into v_old_notification_type_id
from notification_types
where short_name = ''bug_tracker_bug_notif'';
select type_id
into v_new_notification_type_id
from notification_types
where short_name = ''workflow_case'';
update notification_requests set type_id = v_new_notification_type_id where type_id = v_old_notification_type_id;
for row in select nt.type_id
from notification_types nt
where nt.short_name in (''bug_tracker_project_notif'', ''bug_tracker_bug_notif'')
loop
perform notification_type__delete(row.type_id);
delete from notifications where type_id = row.type_id;
delete from notification_types where type_id = row.type_id;
delete from notification_types_intervals where type_id = row.type_id;
delete from notification_types_del_methods where type_id = row.type_id;
end loop;
return null;
end;' language 'plpgsql';
select inline_0();
drop function inline_0 ();
-- Delete the service contract data
create function bt_service_contract_delete(varchar,varchar)
returns integer as '
declare
p_impl_name alias for $1;
p_impl_short_name alias for $2;
impl_id integer;
v_foo integer;
begin
-- the notification type impl
impl_id := acs_sc_impl__get_id (
''NotificationType'', -- impl_contract_name
p_impl_name -- impl_name
);
PERFORM acs_sc_binding__delete (
''NotificationType'',
p_impl_name
);
v_foo := acs_sc_impl_alias__delete (
''NotificationType'', -- impl_contract_name
p_impl_name, -- impl_name
''GetURL'' -- impl_operation_name
);
v_foo := acs_sc_impl_alias__delete (
''NotificationType'', -- impl_contract_name
p_impl_name, -- impl_name
''ProcessReply'' -- impl_operation_name
);
return 0;
end;
' language 'plpgsql';
-- Drop bug tracker notifications. They are now taken care of by the workflow package
select bt_service_contract_delete('bug_tracker_project_notif_type','bug_tracker_project_notif');
select bt_service_contract_delete('bug_tracker_bug_notif_type','bug_tracker_bug_notif');
drop function bt_service_contract_delete(varchar,varchar);
-- Changed column names
-- comment is a reserved word in Oracle
alter table bt_patch_actions rename column comment to comment_text;
-- Drop sequences not used
drop view bt_bug_number_seq;
drop sequence t_bt_bug_number_seq;
drop view bt_patch_number_seq;
drop sequence t_bt_patch_number_seq;
-- Drop tables no longer used
drop table bt_bug_actions;
-- Drop functions not needed anymore
drop function bt_component__default_assignee(
integer -- component_id
);
drop function bt_bug__status_sort_order(
varchar -- status
);
-- *******
-- ******* CR Upgrade START
-- ******* First move away data from changed tables into temporary tables
create table project_temp as select * from bt_projects;
drop table bt_projects;
create table bt_bugs_temp as select * from bt_bugs;
drop table bt_bugs;
-- ******* START create new tables, indices, and functions
create table bt_projects (
project_id integer not null
constraint bt_projects_apm_packages_fk
references apm_packages(package_id)
on delete cascade
constraint bt_projects_pk
primary key,
description text,
-- short string will be included in the subject line of emails
email_subject_name text,
maintainer integer
constraint bt_projects_maintainer_fk
references users(user_id),
folder_id integer
constraint bt_projects_folder_fk
references cr_folders(folder_id),
root_keyword_id integer
constraint bt_projects_keyword_fk
references cr_keywords(keyword_id)
);
create or replace function bt_project__new(
integer -- package_id
) returns integer
as '
declare
p_package_id alias for $1;
v_count integer;
v_instance_name varchar;
v_creation_user integer;
v_creation_ip varchar;
v_folder_id integer;
v_keyword_id integer;
begin
select count(*)
into v_count
from bt_projects
where project_id = p_package_id;
if v_count > 0 then
return 0;
end if;
-- get instance name for the content folder
select p.instance_name, o.creation_user, o.creation_ip
into v_instance_name, v_creation_user, v_creation_ip
from apm_packages p join acs_objects o on (p.package_id = o.object_id)
where p.package_id = p_package_id;
-- create a root CR folder
v_folder_id := content_folder__new(
''bug_tracker_''||p_package_id, -- name
v_instance_name, -- label
null, -- description
content_item_globals.c_root_folder_id -- parent_bi
);
-- register our content type
PERFORM content_folder__register_content_type (
v_folder_id, -- folder_id
''bt_bug_revision'', -- content_type
''t'' -- include_subtypes
);
-- create the instance root keyword
v_keyword_id := content_keyword__new(
v_instance_name, -- heading
null, -- description
null, -- parent_id
null, -- keyword_id
current_timestamp, -- creation_date
v_creation_user, -- creation_user
v_creation_ip, -- creation_ip
''content_keyword'' -- object_type
);
-- insert the row into bt_projects
insert into bt_projects
(project_id, folder_id, root_keyword_id)
values
(p_package_id, v_folder_id, v_keyword_id);
-- Create a General component to start with
insert into bt_components (component_id, project_id, component_name)
select acs_object_id_seq.nextval, p_package_id, ''General'';
return 0;
end;
' language 'plpgsql';
create or replace function bt_project__delete(
integer -- project_id
) returns integer
as '
declare
p_project_id alias for $1;
v_folder_id integer;
v_root_keyword_id integer;
rec record;
begin
-- get the content folder for this instance
select folder_id, root_keyword_id
into v_folder_id, v_root_keyword_id
from bt_projects
where project_id = p_project_id;
-- This gets done in tcl before we are called ... for now
-- Delete the bugs
-- for rec in select item_id from cr_items where parent_id = v_folder_id
-- loop
-- perform bt_bug__delete(rec.item_id);
-- end loop;
-- Delete the patches
for rec in select patch_id from bt_patches where project_id = p_project_id
loop
perform bt_patch__delete(rec.patch_id);
end loop;
-- delete the content folder
raise notice ''about to delete content_folder.'';
perform content_folder__delete(v_folder_id);
-- delete the projects keywords
perform bt_project__keywords_delete(p_project_id, ''t'');
-- These tables should really be set up to cascade
delete from bt_versions where project_id = p_project_id;
delete from bt_components where project_id = p_project_id;
delete from bt_user_prefs where project_id = p_project_id;
delete from bt_projects where project_id = p_project_id;
return 0;
end;
' language 'plpgsql';
create or replace function bt_project__keywords_delete(
integer, -- project_id
bool -- delete_root_p
) returns integer
as '
declare
p_project_id alias for $1;
p_delete_root_p alias for $1;
v_root_keyword_id integer;
rec record;
begin
-- get the content folder for this instance
select root_keyword_id
into v_root_keyword_id
from bt_projects
where project_id = p_project_id;
-- if we are deleting the root, remove it from the project as well
if p_delete_root_p = 1 then
update bt_projects
set root_keyword_id = null
where project_id = p_project_id;
end if;
-- delete the projects keywords
for rec in
select k2.keyword_id
from cr_keywords k1, cr_keywords k2
where k1.keyword_id = v_root_keyword_id
and k2.tree_sortkey between k1.tree_sortkey and tree_right(k1.tree_sortkey)
order by length(k2.tree_sortkey) desc
loop
if (p_delete_root_p = 1) or (rec.keyword_id != v_root_keyword_id) then
perform content_keyword__delete(rec.keyword_id);
end if;
end loop;
return 0;
end;
' language 'plpgsql';
-- versions and components haven't changed...
create table bt_default_keywords (
project_id integer not null
constraint bt_default_keywords_project_fk
references bt_projects(project_id)
on delete cascade,
parent_id integer not null
constraint bt_default_keyw_parent_keyw_fk
references cr_keywords(keyword_id)
on delete cascade,
keyword_id integer not null
constraint bt_default_keyw_keyword_fk
references cr_keywords(keyword_id)
on delete cascade,
constraint bt_default_keywords_prj_par_un
unique (project_id, parent_id)
);
create index bt_default_keyw_parent_id_idx on bt_default_keywords(parent_id);
create index bt_default_keyw_keyword_id_idx on bt_default_keywords(keyword_id);
-- content_item subtype
create table bt_bugs(
bug_id integer
constraint bt_bug_pk
primary key
constraint bt_bug_bt_bug_fk
references cr_items(item_id)
on delete cascade,
-- this is the only column we really add here
bug_number integer,
-- the comment from the initial action
-- denormalized from a far-fetched workflow join
comment_content text,
comment_format varchar(200),
-- denormalized from cr_items
parent_id integer,
live_revision_id integer,
-- denormalized from cr_revisions.title
summary varchar(1000),
-- denormalized from bt_projects
project_id integer,
-- denormalized from bt_bug_revisions
component_id integer,
resolution varchar(50),
user_agent varchar(500),
found_in_version integer,
fix_for_version integer,
fixed_in_version integer,
-- denormalized from acs_objects
creation_date timestamp,
creation_user integer,
-- constraint
constraint bt_bug_parent_id_bug_number_un
unique (parent_id, bug_number)
);
-- LARS:
-- we need to figure out which ones of these will be used by the query optimizer
create index bt_bugs_proj_id_bug_number_idx on bt_bugs(project_id, bug_number);
create index bt_bugs_bug_number_idx on bt_bugs(bug_number);
create index bt_bugs_proj_id_fix_for_idx on bt_bugs(project_id, fix_for_version);
create index bt_bugs_fix_for_version_idx on bt_bugs(fix_for_version);
create index bt_bugs_proj_id_crea_date_idx on bt_bugs(project_id, creation_date);
create index bt_bugs_creation_date_idx on bt_bugs(creation_date);
-- Update the bug content item object type
update acs_object_types set name_method = null where object_type = 'bt_bug';
-- content_revision specialization
create table bt_bug_revisions (
bug_revision_id integer
constraint bt_bug_rev_pk
primary key
constraint bt_bug_rev_bug_id_fk
references cr_revisions(revision_id)
on delete cascade,
component_id integer
constraint bt_bug_rev_components_fk
references bt_components(component_id),
resolution varchar(50)
constraint bt_bug_rev_resolution_ck
check (resolution is null or
resolution in ('fixed','bydesign','wontfix','postponed','duplicate','norepro','needinfo')),
user_agent varchar(500),
found_in_version integer
constraint bt_bug_rev_found_in_version_fk
references bt_versions(version_id),
fix_for_version integer
constraint bt_bug_rev_fix_for_version_fk
references bt_versions(version_id),
fixed_in_version integer
constraint bt_bug_rev_fixed_in_version_fk
references bt_versions(version_id)
);
-- Create the bug revision content type
select content_type__create_type (
'bt_bug_revision',
'content_revision',
'Bug Revision',
'Bug Revisions',
'btbug_revisions',
'bug_revision_id',
'content_revision.revision_name'
);
select define_function_args ('bt_bug__new','bug_id,bug_number,package_id,component_id,found_in_version,summary,user_agent,comment_content,comment_formt,creation_date,creation_user,creation_ip,item_subtype;bt_bug,content_type;bt_bug_revision');
create or replace function bt_bug__new(
integer, -- bug_id
integer, -- bug_number
integer, -- package_id
integer, -- component_id
integer, -- found_in_version
varchar, -- summary
varchar, -- user_agent
text, -- comment_content
varchar, -- comment_format
timestamp, -- creation_date
integer, -- creation_user
varchar, -- creation_ip
varchar, -- item_subtype
varchar -- content_type
) returns int
as '
declare
p_bug_id alias for $1;
p_bug_number alias for $2;
p_package_id alias for $3;
p_component_id alias for $4;
p_found_in_version alias for $5;
p_summary alias for $6;
p_user_agent alias for $7;
p_comment_content alias for $8;
p_comment_format alias for $9;
p_creation_date alias for $10;
p_creation_user alias for $11;
p_creation_ip alias for $12;
p_item_subtype alias for $13;
p_content_type alias for $14;
v_bug_id integer;
v_revision_id integer;
v_bug_number integer;
v_folder_id integer;
begin
-- get the content folder for this instance
select folder_id
into v_folder_id
from bt_projects
where project_id = p_package_id;
-- get bug_number
if p_bug_number is null then
select coalesce(max(bug_number),0) + 1
into v_bug_number
from bt_bugs
where parent_id = v_folder_id;
else
v_bug_number := p_bug_number;
end if;
-- create the content item
v_bug_id := content_item__new_temp(
v_bug_number, -- name
v_folder_id, -- parent_id
p_bug_id, -- item_id
null, -- locale
p_creation_date, -- creation_date
p_creation_user, -- creation_user
v_folder_id, -- context_id
p_creation_ip, -- creation_ip
p_item_subtype, -- item_subtype
p_content_type, -- content_type
null, -- title
null, -- description
null, -- mime_type
null, -- nls_language
null -- data
);
-- create the item type row
insert into bt_bugs
(bug_id, bug_number, comment_content, comment_format, parent_id, project_id, creation_date, creation_user)
values
(v_bug_id, v_bug_number, p_comment_content, p_comment_format, v_folder_id, p_package_id, p_creation_date, p_creation_user);
-- create the initial revision
v_revision_id := bt_bug_revision__new(
null, -- bug_revision_id
v_bug_id, -- bug_id
p_component_id, -- component_id
p_found_in_version, -- found_in_version
null, -- fix_for_version
null, -- fixed_in_version
null, -- resolution
p_user_agent, -- user_agent
p_summary, -- summary
p_creation_date, -- creation_date
p_creation_user, -- creation_user
p_creation_ip -- creation_ip
);
return v_bug_id;
end;
' language 'plpgsql';
-- A temporary modified version that doesn't create an acs_object
create or replace function content_item__new_temp (varchar,integer,integer,varchar,timestamp with time zone,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar)
returns integer as '
declare
new__name alias for $1;
new__parent_id alias for $2; -- default null
new__item_id alias for $3; -- default null
new__locale alias for $4; -- default null
new__creation_date alias for $5; -- default now()
new__creation_user alias for $6; -- default null
new__context_id alias for $7; -- default null
new__creation_ip alias for $8; -- default null
new__item_subtype alias for $9; -- default ''content_item''
new__content_type alias for $10; -- default ''content_revision''
new__title alias for $11; -- default null
new__description alias for $12; -- default null
new__mime_type alias for $13; -- default ''text/plain''
new__nls_language alias for $14; -- default null
new__text alias for $15; -- default null
new__storage_type alias for $16; -- check in (''text'',''file'')
-- relation_tag alias for $17;
-- is_live alias for $18;
new__relation_tag varchar default null;
new__is_live boolean default ''f'';
v_parent_id cr_items.parent_id%TYPE;
v_parent_type acs_objects.object_type%TYPE;
v_item_id cr_items.item_id%TYPE;
v_revision_id cr_revisions.revision_id%TYPE;
v_title cr_revisions.title%TYPE;
v_rel_id acs_objects.object_id%TYPE;
v_rel_tag cr_child_rels.relation_tag%TYPE;
v_context_id acs_objects.context_id%TYPE;
begin
-- place the item in the context of the pages folder if no
-- context specified
if new__parent_id is null then
v_parent_id := content_item_globals.c_root_folder_id;
else
v_parent_id := new__parent_id;
end if;
-- Determine context_id
if new__context_id is null then
v_context_id := v_parent_id;
else
v_context_id := new__context_id;
end if;
if v_parent_id = 0 or
content_folder__is_folder(v_parent_id) = ''t'' then
if v_parent_id != 0 and
content_folder__is_registered(
v_parent_id, new__content_type, ''f'') = ''f'' then
raise EXCEPTION ''-20000: This items content type % is not registered to this folder %'', new__content_type, v_parent_id;
end if;
else if v_parent_id != 0 then
select object_type into v_parent_type from acs_objects
where object_id = v_parent_id;
if NOT FOUND then
raise EXCEPTION ''-20000: Invalid parent ID % specified in content_item.new'', v_parent_id;
end if;
if content_item__is_subclass(v_parent_type, ''content_item'') = ''t'' and
content_item__is_valid_child(v_parent_id, new__content_type) = ''f'' then
raise EXCEPTION ''-20000: This items content type % is not allowed in this container %'', new__content_type, v_parent_id;
end if;
end if; end if;
-- Create the object
-- No, during upgrade the acs object is already created so skip this step
v_item_id := new__item_id;
-- v_item_id := acs_object__new(
-- new__item_id,
-- new__item_subtype,
-- new__creation_date,
-- new__creation_user,
-- new__creation_ip,
-- v_context_id
-- );
insert into cr_items (
item_id, name, content_type, parent_id, storage_type
) values (
v_item_id, new__name, new__content_type, v_parent_id, new__storage_type
);
-- if the parent is not a folder, insert into cr_child_rels
if v_parent_id != 0 and
content_folder__is_folder(v_parent_id) = ''f'' and
content_item__is_valid_child(v_parent_id, new__content_type) = ''t'' then
v_rel_id := acs_object__new(
null,
''cr_item_child_rel'',
now(),
null,
null,
v_parent_id
);
if new__relation_tag is null then
v_rel_tag := content_item__get_content_type(v_parent_id)
|| ''-'' || new__content_type;
else
v_rel_tag := new__relation_tag;
end if;
insert into cr_child_rels (
rel_id, parent_id, child_id, relation_tag, order_n
) values (
v_rel_id, v_parent_id, v_item_id, v_rel_tag, v_item_id
);
end if;
-- use the name of the item if no title is supplied
if new__title is null then
v_title := new__name;
else
v_title := new__title;
end if;
if new__title is not null or
new__text is not null then
v_revision_id := content_revision__new(
v_title,
new__description,
now(),
new__mime_type,
null,
new__text,
v_item_id,
null,
new__creation_date,
new__creation_user,
new__creation_ip
);
end if;
-- make the revision live if is_live is true
if new__is_live = ''t'' then
PERFORM content_item__set_live_revision(v_revision_id);
end if;
return v_item_id;
end;' language 'plpgsql';
-- A temporary modified version that doesn't create an acs_object
create function content_item__new_temp (varchar,integer,integer,varchar,timestamp with time zone,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer)
returns integer as '
declare
new__name alias for $1;
new__parent_id alias for $2; -- default null
new__item_id alias for $3; -- default null
new__locale alias for $4; -- default null
new__creation_date alias for $5; -- default now()
new__creation_user alias for $6; -- default null
new__context_id alias for $7; -- default null
new__creation_ip alias for $8; -- default null
new__item_subtype alias for $9; -- default ''content_item''
new__content_type alias for $10; -- default ''content_revision''
new__title alias for $11; -- default null
new__description alias for $12; -- default null
new__mime_type alias for $13; -- default ''text/plain''
new__nls_language alias for $14; -- default null
-- changed to integer for blob_id
new__data alias for $15; -- default null
-- relation_tag alias for $17;
-- is_live alias for $18;
new__relation_tag varchar default null;
new__is_live boolean default ''f'';
v_parent_id cr_items.parent_id%TYPE;
v_parent_type acs_objects.object_type%TYPE;
v_item_id cr_items.item_id%TYPE;
v_revision_id cr_revisions.revision_id%TYPE;
v_title cr_revisions.title%TYPE;
v_rel_id acs_objects.object_id%TYPE;
v_rel_tag cr_child_rels.relation_tag%TYPE;
v_context_id acs_objects.context_id%TYPE;
begin
-- place the item in the context of the pages folder if no
-- context specified
if new__parent_id is null then
v_parent_id := content_item_globals.c_root_folder_id;
else
v_parent_id := new__parent_id;
end if;
-- Determine context_id
if new__context_id is null then
v_context_id := v_parent_id;
else
v_context_id := new__context_id;
end if;
if v_parent_id = 0 or
content_folder__is_folder(v_parent_id) = ''t'' then
if v_parent_id != 0 and
content_folder__is_registered(
v_parent_id, new__content_type, ''f'') = ''f'' then
raise EXCEPTION ''-20000: This items content type % is not registered to this folder %'', new__content_type, v_parent_id;
end if;
else if v_parent_id != 0 then
select object_type into v_parent_type from acs_objects
where object_id = v_parent_id;
if NOT FOUND then
raise EXCEPTION ''-20000: Invalid parent ID % specified in content_item.new'', v_parent_id;
end if;
if content_item__is_subclass(v_parent_type, ''content_item'') = ''t'' and
content_item__is_valid_child(v_parent_id, new__content_type) = ''f'' then
raise EXCEPTION ''-20000: This items content type % is not allowed in this container %'', new__content_type, v_parent_id;
end if;
end if; end if;
-- Create the object
v_item_id := new__item_id;
-- v_item_id := acs_object__new(
-- new__item_id,
-- new__item_subtype,
-- new__creation_date,
-- new__creation_user,
-- new__creation_ip,
-- v_context_id
-- );
insert into cr_items (
item_id, name, content_type, parent_id, storage_type
) values (
v_item_id, new__name, new__content_type, v_parent_id, ''lob''
);
-- if the parent is not a folder, insert into cr_child_rels
if v_parent_id != 0 and
content_folder__is_folder(v_parent_id) = ''f'' and
content_item__is_valid_child(v_parent_id, new__content_type) = ''t'' then
v_rel_id := acs_object__new(
null,
''cr_item_child_rel'',
now(),
null,
null,
v_parent_id
);
if new__relation_tag is null or new__relation_tag = '''' then
v_rel_tag := content_item__get_content_type(v_parent_id)
|| ''-'' || new__content_type;
else
v_rel_tag := new__relation_tag;
end if;
insert into cr_child_rels (
rel_id, parent_id, child_id, relation_tag, order_n
) values (
v_rel_id, v_parent_id, v_item_id, v_rel_tag, v_item_id
);
end if;
-- use the name of the item if no title is supplied
if new__title is null or new__title = '''' then
v_title := new__name;
else
v_title := new__title;
end if;
-- create the revision if data or title or text is not null
-- note that the caller could theoretically specify both text
-- and data, in which case the text is ignored.
if new__data is not null then
v_revision_id := content_revision__new(
v_title,
new__description,
now(),
new__mime_type,
new__nls_language,
new__data,
v_item_id,
null,
new__creation_date,
new__creation_user,
new__creation_ip
);
end if;
-- make the revision live if is_live is true
if new__is_live = ''t'' then
PERFORM content_item__set_live_revision(v_revision_id);
end if;
return v_item_id;
end;' language 'plpgsql';
create or replace function bt_bug__delete(
integer -- bug_id
) returns integer
as '
declare
p_bug_id alias for $1;
v_case_id integer;
rec record;
begin
-- Every bug is associated with a workflow case
select case_id
into v_case_id
from workflow_cases
where object_id = p_bug_id;
perform workflow_case__delete(v_case_id);
-- Every bug may have notifications attached to it
-- and there is one column in the notificaitons datamodel that doesn''t
-- cascade
for rec in select notification_id from notifications
where response_id = p_bug_id loop
perform notification__delete (rec.notification_id);
end loop;
-- unset live & latest revision
-- update cr_items
-- set live_revision = null,
-- latest_revision = null
-- where item_id = p_bug_id;
perform content_item__delete(p_bug_id);
return 0;
end;
' language 'plpgsql';
create or replace function bt_bug_revision__new(
integer, -- bug_revision_id
integer, -- bug_id
integer, -- component_id
integer, -- found_in_version
integer, -- fix_for_version
integer, -- fixed_in_version
varchar, -- resolution
varchar, -- user_agent
varchar, -- summary
timestamp, -- creation_date
integer, -- creation_user
varchar -- creation_ip
) returns int
as '
declare
p_bug_revision_id alias for $1;
p_bug_id alias for $2;
p_component_id alias for $3;
p_found_in_version alias for $4;
p_fix_for_version alias for $5;
p_fixed_in_version alias for $6;
p_resolution alias for $7;
p_user_agent alias for $8;
p_summary alias for $9;
p_creation_date alias for $10;
p_creation_user alias for $11;
p_creation_ip alias for $12;
v_revision_id integer;
begin
-- create the initial revision
v_revision_id := content_revision__new(
p_summary, -- title
null, -- description
now(), -- publish_date
null, -- mime_type
null, -- nls_language
null, -- new_data
p_bug_id, -- item_id
p_bug_revision_id, -- revision_id
p_creation_date, -- creation_date
p_creation_user, -- creation_user
p_creation_ip -- creation_ip
);
-- insert into the bug-specific revision table
insert into bt_bug_revisions
(bug_revision_id, component_id, resolution, user_agent, found_in_version, fix_for_version, fixed_in_version)
values
(v_revision_id, p_component_id, p_resolution, p_user_agent, p_found_in_version, p_fix_for_version, p_fixed_in_version);
-- make this revision live
PERFORM content_item__set_live_revision(v_revision_id);
-- update the cache
update bt_bugs
set live_revision_id = v_revision_id,
summary = p_summary,
component_id = p_component_id,
resolution = p_resolution,
user_agent = p_user_agent,
found_in_version = p_found_in_version,
fix_for_version = p_fix_for_version,
fixed_in_version = p_fixed_in_version
where bug_id = p_bug_id;
return v_revision_id;
end;
' language 'plpgsql';
-- ******* END create new tables, indices, and functions
-- ******* Recreate the project data
create or replace function inline_0 ()
returns integer as '
declare
project_rec record;
begin
for project_rec in select project_id,
description,
email_subject_name,
maintainer
from project_temp
loop
perform bt_project__new (
project_rec.project_id
);
update bt_projects set description = project_rec.description,
email_subject_name = project_rec.email_subject_name,
maintainer = project_rec.maintainer
where project_id = project_rec.project_id;
end loop;
return 0;
end;' language 'plpgsql';
select inline_0();
drop function inline_0();
-- ******* Migrate the severiy and priority codes to CR keywords
create table code_keyword_map_temp (
code_id integer,
-- for bt_priority_codes or bt_severity_codes
keyword_id integer
references cr_keywords
);
-- ******* Migrate bug type to CR keywords
create table bug_type_keyword_map_temp (
project_id integer,
bug_type varchar,
keyword_id integer
references cr_keywords
);
create or replace function inline_0 ()
returns integer as '
declare
project_rec record;
v_keyword_id integer;
v_severity_root integer;
severity_rec record;
v_priority_root integer;
priority_rec record;
v_bug_type_root integer;
begin
for project_rec in
select project_id, root_keyword_id
from bt_projects
loop
-- Create the severity root keyword
v_severity_root := content_keyword__new (
''Severity'',
null,
project_rec.root_keyword_id,
null,
null,
null,
null,
''content_keyword'' -- object_type
);
for severity_rec in
select severity_id,
severity_name,
sort_order
from bt_severity_codes
where project_id = project_rec.project_id
order by sort_order
loop
v_keyword_id := content_keyword__new (
severity_rec.sort_order || '' - '' || severity_rec.severity_name,
null,
v_severity_root,
null,
null,
null,
null,
''content_keyword'' -- object_type
);
insert into code_keyword_map_temp (code_id, keyword_id)
values (severity_rec.severity_id, v_keyword_id);
end loop;
-- Create the priority code root
v_priority_root := content_keyword__new (
''Priority'',
null,
project_rec.root_keyword_id,
null,
null,
null,
null,
''content_keyword'' -- object_type
);
for priority_rec in
select priority_id,
priority_name,
sort_order
from bt_priority_codes
where project_id = project_rec.project_id
order by sort_order
loop
v_keyword_id := content_keyword__new (
priority_rec.sort_order || '' - '' || priority_rec.priority_name,
null,
v_priority_root,
null,
null,
null,
null,
''content_keyword'' -- object_type
);
insert into code_keyword_map_temp (code_id, keyword_id)
values (priority_rec.priority_id, v_keyword_id);
end loop;
-- Create the bug type root
v_bug_type_root := content_keyword__new (
''Bug Type'',
null,
project_rec.root_keyword_id,
null,
null,
null,
null,
''content_keyword'' -- object_type
);
-- Bug Type: Bug
v_keyword_id := content_keyword__new (
''Bug'',
null,
v_bug_type_root,
null,
null,
null,
null,
''content_keyword'' -- object_type
);
insert into bug_type_keyword_map_temp (project_id, bug_type, keyword_id)
values (project_rec.project_id, ''bug'', v_keyword_id);
-- Bug Type: Suggestion
v_keyword_id := content_keyword__new (
''Suggestion'',
null,
v_bug_type_root,
null,
null,
null,
null,
''content_keyword'' -- object_type
);
insert into bug_type_keyword_map_temp (project_id, bug_type, keyword_id)
values (project_rec.project_id, ''suggestion'', v_keyword_id);
-- Bug Type: Todo
v_keyword_id := content_keyword__new (
''Todo'',
null,
v_bug_type_root,
null,
null,
null,
null,
''content_keyword'' -- object_type
);
insert into bug_type_keyword_map_temp (project_id, bug_type, keyword_id)
values (project_rec.project_id, ''todo'', v_keyword_id);
end loop;
return 0;
end;' language 'plpgsql';
select inline_0();
drop function inline_0();
-- ******* START Bug upgrade
-- Create each of the bugs with the new API that creates a content
-- item, an initial revision, and populates the bt_bugs denormalization table
create or replace function inline_0 ()
returns integer as '
declare
-- Project loop
project_rec record;
v_workflow_id integer;
v_open_action_id integer;
-- Bug loop
bug_rec record;
notifications_rec record;
v_state_id integer;
v_item_id integer;
v_new_bug_id integer;
v_severity_id integer;
v_priority_id integer;
v_bug_type_id integer;
v_bug_revision_id integer;
begin
for project_rec in select project_id from bt_projects
loop
-- Get the bug workflow id
select workflow_id into v_workflow_id
from workflows
where short_name = ''bug''
and object_id = project_rec.project_id;
select action_id into v_open_action_id
from workflow_actions
where workflow_id = v_workflow_id
and short_name = ''open'';
for bug_rec in select b.bug_id,
b.bug_number,
b.project_id,
b.component_id,
b.found_in_version,
b.fix_for_version,
b.fixed_in_version,
b.resolution,
b.summary,
b.user_agent,
b.severity,
b.priority,
b.bug_type,
cr.content,
cr.mime_type,
o.creation_user,
o.creation_date,
o.creation_ip
from bt_bugs_temp b,
acs_objects o,
workflow_cases c,
workflow_case_log wcl,
cr_items ci,
cr_revisions cr
where b.bug_id = o.object_id
and b.project_id = project_rec.project_id
and b.bug_id = c.object_id
and c.workflow_id = v_workflow_id
and wcl.case_id = c.case_id
and wcl.action_id = v_open_action_id
and ci.item_id = wcl.entry_id
and cr.revision_id = ci.live_revision
loop
-- Use a modified version of bt_bug__new that doesn''t
-- create a new acs_object
perform bt_bug__new (
bug_rec.bug_id, -- bug_id
bug_rec.bug_number, -- bug_number
bug_rec.project_id, -- package_id
bug_rec.component_id, -- component_id
bug_rec.found_in_version, -- found_in_version
bug_rec.summary, -- summary
bug_rec.user_agent, -- user_agent
bug_rec.content, -- comment_content
bug_rec.mime_type, -- comment_format
bug_rec.creation_date, -- creation_date
bug_rec.creation_user, -- creation_user
bug_rec.creation_ip, -- creation_ip
''bt_bug'', -- item_subtype
''bt_bug_revision'' -- content_type
);
-- Get the revision id
select live_revision
into v_bug_revision_id
from cr_items
where item_id = bug_rec.bug_id;
-- Update with fix_for_version, fixed_in_version, resolution
update bt_bug_revisions
set fix_for_version = bug_rec.fix_for_version,
fixed_in_version = bug_rec.fixed_in_version,
resolution = bug_rec.resolution
where bug_revision_id = v_bug_revision_id;
-- update the cache in the item
update bt_bugs
set fix_for_version = bug_rec.fix_for_version,
fixed_in_version = bug_rec.fixed_in_version,
resolution = bug_rec.resolution
where bug_id = bug_rec.bug_id;
-- Map severity
select keyword_id
into v_severity_id
from code_keyword_map_temp
where code_id = bug_rec.severity;
perform content_keyword__item_assign (
bug_rec.bug_id,
v_severity_id,
null,
bug_rec.creation_user,
bug_rec.creation_ip
);
-- Map priority
select keyword_id
into v_priority_id
from code_keyword_map_temp
where code_id = bug_rec.priority;
perform content_keyword__item_assign (
bug_rec.bug_id,
v_priority_id,
null,
bug_rec.creation_user,
bug_rec.creation_ip
);
-- Map bug type
select keyword_id
into v_bug_type_id
from bug_type_keyword_map_temp
where project_id = bug_rec.project_id
and bug_type = bug_rec.bug_type;
perform content_keyword__item_assign (
bug_rec.bug_id,
v_bug_type_id,
null,
bug_rec.creation_user,
bug_rec.creation_ip
);
end loop;
end loop;
return 0;
end;' language 'plpgsql';
select inline_0();
drop function inline_0();
-- ******* Drop tables no longer used
drop table bt_severity_codes;
drop table bt_priority_codes;
-- ******* Drop temporary upgrade tables
drop table code_keyword_map_temp;
drop table bug_type_keyword_map_temp;
drop table bt_bugs_temp;
drop table project_temp;
-- ******* Drop temporary upgrade functions
--drop function bt_bug__new_temp(
-- integer, -- bug_id
-- integer, -- bug_number
-- integer, -- package_id
-- integer, -- component_id
-- integer, -- found_in_version
-- varchar, -- summary
-- varchar, -- user_agent
-- text, -- comment_content
-- varchar, -- comment_format
-- timestamp, -- creation_date
-- integer, -- creation_user
-- varchar, -- creation_ip
-- varchar, -- item_subtype
-- varchar -- content_type
--);
drop function content_item__new_temp (varchar,integer,integer,varchar,timestamp with time zone,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,integer);
drop function content_item__new_temp (varchar,integer,integer,varchar,timestamp with time zone,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar);
OpenACS – The Toolkit for Online Communities : ACS API Browser : Bug Tracker 1.6d6 : upgrade-0.9d1-1.2d2.sql
Begin main content
