-- -- bt_versions -- drop index bt_versions_pk; drop index bt_versions_version_name_un; alter table bt_versions rename to bt_versions_old; create table bt_versions ( version_id integer not null constraint bt_versions_pk primary key, project_id integer not null constraint bt_versions_projects_fk references bt_projects(project_id), -- Like apm_package_versions.version_name -- But can also be a human-readable name like "Future", "Milestone 3", etc. version_name varchar(500) not null, description text, anticipated_freeze_date date, actual_freeze_date date, anticipated_release_date date, actual_release_date date, maintainer integer constraint bt_versions_maintainer_fk references users(user_id), supported_platforms varchar(1000), active_version_p char(1) not null constraint bt_versions_active_version_p_ck check (active_version_p in ('t','f')) default 'f', -- Can we assign bugs to be fixed for this version? assignable_p char(1) constraint bt_versions_assignable_p_ck check (assignable_p in ('t','f')) ); insert into bt_versions select * from bt_versions_old; -- -- bt_components -- drop index bt_components_pk; drop index bt_components_name_un; alter table bt_components rename to bt_components_old; create table bt_components ( component_id integer not null constraint bt_components_pk primary key, project_id integer not null constraint bt_components_projects_fk references bt_projects(project_id), component_name varchar(500) not null, description text, -- a component can be without maintainer, in which case we just default to the project maintainer maintainer integer constraint bt_components_maintainer_fk references users(user_id) ); insert into bt_components select * from bt_components_old; -- -- bt_bugs -- drop index bt_bugs_pk; drop index bt_bugs_bug_number_un; alter table bt_bugs rename to bt_bugs_old; create table bt_bugs ( bug_id integer constraint bt_bugs_pk primary key constraint bt_bugs_bug_id_fk references acs_objects(object_id), project_id integer constraint bt_bugs_projects_fk references bt_projects(project_id), component_id integer constraint bt_bugs_components_fk references bt_components(component_id), bug_number integer not null, status varchar(50) not null constraint bt_bugs_status_ck check (status in ('open', 'resolved', 'closed')) default 'open', resolution varchar(50) constraint bt_bugs_resolution_ck check (resolution is null or resolution in ('fixed','bydesign','wontfix','postponed','duplicate','norepro')), bug_type varchar(50) not null constraint bt_bugs_bug_type_ck check (bug_type in ('bug', 'suggestion','todo')), severity integer not null constraint bt_bugs_severity_fk references bt_severity_codes(severity_id), priority integer not null constraint bt_bugs_priority_fk references bt_priority_codes(priority_id), user_agent varchar(500), original_estimate_minutes integer, latest_estimate_minutes integer, elapsed_time_minutes integer, found_in_version integer constraint bt_bugs_found_in_version_fk references bt_versions(version_id), fix_for_version integer constraint bt_bugs_fix_for_version_fk references bt_versions(version_id), fixed_in_version integer constraint bt_bugs_fixed_in_version_fk references bt_versions(version_id), summary varchar(500) not null, assignee integer constraint bt_bug_assignee_fk references users(user_id), constraint bt_bugs_bug_number_un unique (project_id, bug_number) ); insert into bt_bugs select * from bt_bugs_old; -- -- bt_bugs__new -- create function bt_component__default_assignee( integer -- component_id ) returns integer as ' declare p_component_id alias for $1; v_assignee integer; begin select maintainer into v_assignee from bt_components where component_id = p_component_id; if v_assignee is null then select p.maintainer into v_assignee from bt_projects p, bt_components c where p.project_id = c.project_id and c.component_id = p_component_id; end if; return v_assignee; end; ' language 'plpgsql'; drop function bt_bug__new (integer, integer, integer, varchar, integer, integer, integer, varchar, text, varchar, varchar, integer, varchar); create function bt_bug__new( integer, -- bug_id integer, -- project_id integer, -- component_id varchar, -- bug_type integer, -- severity integer, -- priority integer, -- found_in_version varchar, -- summary text, -- description varchar, -- desc_format varchar, -- user_agent integer, -- creation_user varchar -- creation_ip ) returns int as ' declare p_bug_id alias for $1; p_project_id alias for $2; p_component_id alias for $3; p_bug_type alias for $4; p_severity alias for $5; p_priority alias for $6; p_found_in_version alias for $7; p_summary alias for $8; p_description alias for $9; p_desc_format alias for $10; p_user_agent alias for $11; p_creation_user alias for $12; p_creation_ip alias for $13; v_bug_id integer; v_bug_number integer; v_assignee integer; v_action_id integer; begin v_assignee := bt_component__default_assignee(p_component_id); v_bug_id := acs_object__new( p_bug_id, -- object_id ''bt_bug'', -- object_type now(), -- creation_date p_creation_user, -- creation_user p_creation_ip, -- creation_ip p_project_id, -- context_id ''t'' -- security_inherit_p ); select coalesce(max(bug_number),0) + 1 into v_bug_number from bt_bugs where project_id = p_project_id; insert into bt_bugs (bug_id, project_id, component_id, bug_number, bug_type, severity, assignee, priority, found_in_version, summary, user_agent) values (v_bug_id, p_project_id, p_component_id, v_bug_number, p_bug_type, p_severity, v_assignee, p_priority, p_found_in_version, p_summary, p_user_agent); select nextval(''t_acs_object_id_seq'') into v_action_id; insert into bt_bug_actions (action_id, bug_id, action, actor, comment, comment_format) values (v_action_id, v_bug_id, ''open'', p_creation_user, p_description, p_desc_format); return 0; end; ' language 'plpgsql'; drop table bt_versions_old; drop table bt_components_old; drop table bt_bugs_old;