#create a new user table with unique constraint on username
create table NEWUSER (USER_ID integer not null, userName varchar(255), dontSendMeMyNotifications bit, stateChangeEmailNotification integer, commentEmailNotification integer, replyEmailNotification integer, generalEmailNotification integer, completeEmailNotification integer, primary key (USER_ID), unique (userName));
insert into NEWUSER (USER_ID, userName, dontSendMeMyNotifications, stateChangeEmailNotification, commentEmailNotification, replyEmailNotification, generalEmailNotification, completeEmailNotification) select * from USER;

#drop all FK constraints on user
alter table ACTIVE_REVIEWER drop constraint FK6381431E1811CB9D;
alter table COMMENT drop constraint FK63717A3F1811CB9D;
alter table COMPLETED_REVIEWER drop constraint FK4B1D69991811CB9D;
alter table NOTIFICATION drop constraint FKAD9970EBBE1FB2F1;
alter table RECIPIENT drop constraint FKD6F981F9210DC959;
alter table REVIEW drop constraint FK8FE1EB58CD03FF19;
alter table REVIEW drop constraint FK8FE1EB585E255DBA;
alter table REVIEW drop constraint FK8FE1EB58A9598293;

#drop old user and subsitute new
drop table USER;
alter table NEWUSER rename to USER;

#reinstate constraints and add index
alter table ACTIVE_REVIEWER add constraint FK6381431E1811CB9D foreign key (USER_ID) references USER;
alter table COMMENT add constraint FK63717A3F1811CB9D foreign key (USER_ID) references USER;
alter table COMPLETED_REVIEWER add constraint FK4B1D69991811CB9D foreign key (USER_ID) references USER;
alter table NOTIFICATION add constraint FKAD9970EBBE1FB2F1 foreign key (actioner) references USER;
alter table RECIPIENT add constraint FKD6F981F9210DC959 foreign key (user) references USER;
alter table REVIEW add constraint FK8FE1EB58CD03FF19 foreign key (author) references USER;
alter table REVIEW add constraint FK8FE1EB585E255DBA foreign key (creator) references USER;
alter table REVIEW add constraint FK8FE1EB58A9598293 foreign key (moderator) references USER;
create index idx_user_name on USER (userName);

# add new metrics
INSERT INTO METRIC_DEFINITION VALUES(2,'<metrics version="2" xmlns="http://www.cenqua.com/crucible/metrics-1">\u000a    <review>\u000a        <field type="STRING">\u000a            <name>section</name>\u000a            <label>Department</label>\u000a            <restriction type="NON_EXCLUSIVE_SET"/>\u000a            <namedValue name="None">none</namedValue>\u000a            <namedValue name="Crucible">crucible</namedValue>\u000a            <namedValue name="Fisheye">fisheye</namedValue>\u000a            <namedValue name="Clover">clover</namedValue>\u000a        </field>\u000a        <field type="STRING">\u000a            <name>spec</name>\u000a            <label>Specification Document (URL)</label>\u000a        </field>\u000a        <field type="STRING">\u000a            <name>uml</name>\u000a            <label>UML diagram (URL)</label>\u000a        </field>\u000a        <field type="DATE">\u000a            <name>due</name>\u000a            <label>Due date</label>\u000a        </field>\u000a    </review>\u000a    <comment>\u000a        <field type="INTEGER">\u000a            <name>classification</name>\u000a            <label>Classification</label>\u000a            <restriction type="EXCLUSIVE_SET"/>\u000a            <namedValue name="Missing">1</namedValue>\u000a            <namedValue name="Extra (superfluous)">2</namedValue>\u000a            <namedValue name="Ambiguous">3</namedValue>\u000a            <namedValue name="Inconsistent">4</namedValue>\u000a            <namedValue name="Improvement desirable">5</namedValue>\u000a            <namedValue name="Not conforming to standards">6</namedValue>\u000a            <namedValue name="Risk-prone">7</namedValue>\u000a            <namedValue name="Factually incorrect">8</namedValue>\u000a            <namedValue name="Not implementable">9</namedValue>\u000a            <namedValue name="Editorial">10</namedValue>\u000a            <defaultValue>Improvement desirable</defaultValue>\u000a        </field>\u000a        <field type="BOOLEAN">\u000a            <name>rank</name>\u000a            <label>Ranking</label>\u000a            <restriction type="EXCLUSIVE_SET"/>\u000a            <namedValue name="Major">true</namedValue>\u000a            <namedValue name="Minor">false</namedValue>\u000a            <defaultValue>Minor</defaultValue>\u000a        </field>\u000a    </comment>\u000a    <defect>\u000a        <field type="INTEGER">\u000a            <name>classification</name>\u000a            <label>Classification</label>\u000a            <restriction type="EXCLUSIVE_SET"/>\u000a            <namedValue name="Missing">1</namedValue>\u000a            <namedValue name="Extra (superfluous)">2</namedValue>\u000a            <namedValue name="Ambiguous">3</namedValue>\u000a            <namedValue name="Inconsistent">4</namedValue>\u000a            <namedValue name="Improvement desirable">5</namedValue>\u000a            <namedValue name="Not conforming to standards">6</namedValue>\u000a            <namedValue name="Risk-prone">7</namedValue>\u000a            <namedValue name="Factually incorrect">8</namedValue>\u000a            <namedValue name="Not implementable">9</namedValue>\u000a            <namedValue name="Editorial">10</namedValue>\u000a            <defaultValue>Improvement desirable</defaultValue>\u000a        </field>\u000a        <field type="BOOLEAN">\u000a            <name>rank</name>\u000a            <label>Ranking</label>\u000a            <restriction type="EXCLUSIVE_SET"/>\u000a            <namedValue name="Major">true</namedValue>\u000a            <namedValue name="Minor">false</namedValue>\u000a            <defaultValue>Minor</defaultValue>\u000a        </field>\u000a    </defect>\u000a\u000a</metrics>')

# and finally update the db version
UPDATE CRUVERSION SET version = 9 WHERE version_id = 1;
