#first drop all constraints on the existing tables
alter table ACTION_ITEM drop constraint FKE077AB7C5D3A9B8E;
alter table AI_COMMENT drop constraint FK3E2A8128F020303B;
alter table AI_COMMENT drop constraint FK3E2A8128999D94C6;
alter table AI_FIELD drop constraint FK1BEC5603F020303B;
alter table AI_FIELD drop constraint FK1BEC5603A18837F5;
alter table AI_ITR_COMMENT drop constraint FK42D630F0F020303B;
alter table AI_ITR_COMMENT drop constraint FK42D630F0999D94C6;
alter table AI_REVISION_COMMENT drop constraint FK4F56D12F020303B;
alter table AI_REVISION_COMMENT drop constraint FK4F56D1298F3AE86;
alter table COMMENT drop constraint FK63717A3F1811CB9D;
alter table COMMENT_FIELD drop constraint FKADAF9ADA999D94C6;
alter table COMMENT_FIELD drop constraint FKADAF9ADAA18837F5;
alter table FR_EXTRA drop constraint FKFF633EBD5D3A9B8E;
alter table FR_EXTRA drop constraint FKFF633EBD44B2EB10;
alter table FR_EXTRA drop constraint FKFF633EBD89A8D6E2;
alter table ITERATION drop constraint FK2F1CFEBDA9598293;
alter table ITERATION drop constraint FK2F1CFEBDE71841CE;
alter table ITERATION drop constraint FK2F1CFEBD5E255DBA;
alter table ITERATION drop constraint FK2F1CFEBDCD03FF19;
alter table REPLY drop constraint FK4A41ACA999D94C6;
alter table REPLY drop constraint FK4A41ACA4A6A205B;
alter table REVIEW drop constraint FK8FE1EB589D9CAB55;
alter table REVIEW_COMMENT drop constraint FKD84E3458999D94C6;
alter table REVIEW_COMMENT drop constraint FKD84E3458E71841CE;
alter table REVIEW_FIELD drop constraint FK90C31533E71841CE;
alter table REVIEW_FIELD drop constraint FK90C31533A18837F5;
alter table REVISION_COMMENT drop constraint FKD00FBDDBD9181D66;
alter table REVISION_COMMENT drop constraint FKD00FBDDB9CAB902A;
alter table REVISION_COMMENT drop constraint FKD00FBDDBD91F70A1;
alter table RI_ACTIVE_REVIEWER drop constraint FKFCD55165D3A9B8E;
alter table RI_ACTIVE_REVIEWER drop constraint FKFCD55161811CB9D;
alter table RI_COMMENT drop constraint FK770B04375D3A9B8E;
alter table RI_COMMENT drop constraint FK770B0437999D94C6;
alter table RI_COMPLETED_REVIEWER drop constraint FKB37C74A15D3A9B8E;
alter table RI_COMPLETED_REVIEWER drop constraint FKB37C74A11811CB9D;
alter table RI_FIELD drop constraint FK1E050D25D3A9B8E;
alter table RI_FIELD drop constraint FK1E050D2A18837F5;
#
# drop index is not sql92, however it is supported by most dbs but the dialect may change
#
drop index field_name_idx if exists;
drop index fr_revs_idx if exists;
drop index fr_result_idx if exists;
drop index fr_source_idx if exists;
drop index fr_path_idx if exists;
drop index scl_review_idx if exists;
#
# ActionItem has not been used as yet so we can just alter the table
#
alter table ACTION_ITEM alter column ITERATION_ID rename to REVIEW_ID;
#
# FR_EXTRA need to change the references from iteration to review
#
create table NEW_FR_EXTRA (FRX_ID integer not null, WHEN_ADDED timestamp, reviewed integer, furtherAction integer, reviewScope integer, fileRevision integer, REVIEW_ID integer not null, fromRevision integer, FRX_ORDER integer, primary key (FRX_ID), unique (fileRevision, REVIEW_ID));
insert into NEW_FR_EXTRA (FRX_ID, WHEN_ADDED, reviewed, furtherAction, reviewScope, fileRevision, REVIEW_ID, fromRevision, FRX_ORDER) select frx.FRX_ID, frx.WHEN_ADDED, frx.reviewed, frx.furtherAction, frx.reviewScope, frx.fileRevision, it.REVIEW_ID, frx.fromRevision, frx.FRX_ORDER from FR_EXTRA frx, ITERATION it where it.ITERATION_ID = frx.ITERATION_ID;
drop table FR_EXTRA;
alter table NEW_FR_EXTRA rename to FR_EXTRA;
#
# REVIEW has taken on many of the fields of ITERATION so we need to grab those and the iteration fields
#
create table NEW_REVIEW (REVIEW_ID integer not null, name varchar(255), repoName varchar(255), state varchar(255), description varchar(255), summary varchar(255), CREATE_DATE timestamp, metricsVersion integer, author integer, moderator integer, creator integer, primary key (REVIEW_ID));
insert into NEW_REVIEW (REVIEW_ID, name, repoName, state, description, summary, CREATE_DATE, metricsVersion, author, moderator, creator) select rev.REVIEW_ID, rev.name, rev.repoName, rev.state, rev.description, it.description, rev.CREATE_DATE, rev.metricsVersion, it.author, it.moderator, it.creator from REVIEW rev, ITERATION it where rev.currentIteration = it.ITERATION_ID;
drop table REVIEW;
alter table NEW_REVIEW rename to REVIEW;
#
# We need to transfer all iteration comments to review comments then drop the RI_COMMENT table
#
insert into REVIEW_COMMENT (REVIEW_ID, COMMENT_ID) select it.REVIEW_ID, comment.COMMENT_ID from ITERATION it, RI_COMMENT comment where comment.ITERATION_ID = it.ITERATION_ID;
drop table RI_COMMENT;
#
# RI_ACTIVE_REVIEWER is changed to just ACTIVE_REVIEWER and the iteration id is changed to review id
#
create table ACTIVE_REVIEWER (REVIEW_ID integer not null, USER_ID integer not null, primary key (REVIEW_ID, USER_ID));
insert into ACTIVE_REVIEWER (REVIEW_ID, USER_ID) select it.REVIEW_ID, ar.USER_ID from RI_ACTIVE_REVIEWER ar, ITERATION it where ar.ITERATION_ID = it.ITERATION_ID;
drop table RI_ACTIVE_REVIEWER;
#
# RI_COMPLETED_REVIEWER changes to COMPLETED_REVIEWER and iteration id is changed to review id
#
create table COMPLETED_REVIEWER (REVIEW_ID integer not null, USER_ID integer not null, primary key (REVIEW_ID, USER_ID));
insert into COMPLETED_REVIEWER (REVIEW_ID, USER_ID) select it.REVIEW_ID, cr.USER_ID from RI_COMPLETED_REVIEWER cr, ITERATION it where cr.ITERATION_ID = it.ITERATION_ID;
drop table RI_COMPLETED_REVIEWER;
#
# RI_FIELD may have been filled in but not yet supported, just drop it
#
drop table RI_FIELD;
#
# and finally drop ITERATION
#
drop table ITERATION;
#
# Now just add new constraints and indexes
#
alter table ACTION_ITEM add constraint FKE077AB7CE71841CE foreign key (REVIEW_ID) references REVIEW;
alter table AI_COMMENT add constraint FK3E2A8128F020303B foreign key (ACTION_ITEM_ID) references ACTION_ITEM;
alter table AI_COMMENT add constraint FK3E2A8128999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table AI_FIELD add constraint FK1BEC5603F020303B foreign key (ACTION_ITEM_ID) references ACTION_ITEM;
alter table AI_FIELD add constraint FK1BEC5603A18837F5 foreign key (FIELD_ID) references FIELD;
alter table AI_ITR_COMMENT add constraint FK42D630F0F020303B foreign key (ACTION_ITEM_ID) references ACTION_ITEM;
alter table AI_ITR_COMMENT add constraint FK42D630F0999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table AI_REVISION_COMMENT add constraint FK4F56D12F020303B foreign key (ACTION_ITEM_ID) references ACTION_ITEM;
alter table AI_REVISION_COMMENT add constraint FK4F56D1298F3AE86 foreign key (REVISIONCOMMENT_ID) references REVISION_COMMENT;
alter table COMMENT add constraint FK63717A3F1811CB9D foreign key (USER_ID) references USER;
alter table COMMENT_FIELD add constraint FKADAF9ADA999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table COMMENT_FIELD add constraint FKADAF9ADAA18837F5 foreign key (FIELD_ID) references FIELD;
create index field_name_idx on FIELD (name);
alter table FR_EXTRA add constraint FKFF633EBD44B2EB10 foreign key (fromRevision) references REVISION;
alter table FR_EXTRA add constraint FKFF633EBDE71841CE foreign key (REVIEW_ID) references REVIEW;
alter table FR_EXTRA add constraint FKFF633EBD89A8D6E2 foreign key (fileRevision) references REVISION;
alter table REPLY add constraint FK4A41ACA999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table REPLY add constraint FK4A41ACA4A6A205B foreign key (REPLY_ID) references COMMENT;
alter table REVIEW add constraint FK8FE1EB58A9598293 foreign key (moderator) references USER;
alter table REVIEW add constraint FK8FE1EB585E255DBA foreign key (creator) references USER;
alter table REVIEW add constraint FK8FE1EB58CD03FF19 foreign key (author) references USER;
alter table REVIEW_COMMENT add constraint FKD84E3458999D94C6 foreign key (COMMENT_ID) references COMMENT;
alter table REVIEW_COMMENT add constraint FKD84E3458E71841CE foreign key (REVIEW_ID) references REVIEW;
alter table REVIEW_FIELD add constraint FK90C31533E71841CE foreign key (REVIEW_ID) references REVIEW;
alter table REVIEW_FIELD add constraint FK90C31533A18837F5 foreign key (FIELD_ID) references FIELD;
create index fr_revs_idx on REVISION (revision);
create index fr_result_idx on REVISION (reviewResult);
create index fr_source_idx on REVISION (source);
create index fr_path_idx on REVISION (path);
alter table REVISION_COMMENT add constraint FKD00FBDDBD9181D66 foreign key (revision) references REVISION;
alter table REVISION_COMMENT add constraint FKD00FBDDB9CAB902A foreign key (comment) references COMMENT;
alter table REVISION_COMMENT add constraint FKD00FBDDBD91F70A1 foreign key (FRX_ID) references FR_EXTRA;
alter table ACTIVE_REVIEWER add constraint FKFCD5516E71841CE foreign key (REVIEW_ID) references REVIEW;
alter table ACTIVE_REVIEWER add constraint FKFCD55161811CB9D foreign key (USER_ID) references USER;
alter table COMPLETED_REVIEWER add constraint FKB37C74A1E71841CE foreign key (REVIEW_ID) references REVIEW;
alter table COMPLETED_REVIEWER add constraint FKB37C74A11811CB9D foreign key (USER_ID) references USER;
create index scl_review_idx on STATE_CHANGE (reviewId);
UPDATE CRUVERSION SET version = 3 WHERE version_id = 1;