#first drop changed constraints on the existing tables
#
alter table ACTIVE_REVIEWER drop constraint FKFCD5516E71841CE;
alter table ACTIVE_REVIEWER drop constraint FKFCD55161811CB9D;
alter table COMPLETED_REVIEWER drop constraint FKB37C74A1E71841CE;
alter table COMPLETED_REVIEWER drop constraint FKB37C74A11811CB9D;
#
# Add the two new tables
#
create table NOTIFICATION (NOTIFICATION_ID integer not null, CREATE_DATE timestamp, actioner integer, type integer, note varchar(255), review integer, ref1 integer, ref2 integer, primary key (NOTIFICATION_ID));
create table RECIPIENT (RECIPIENT_ID integer not null, email integer, uiACK bit, NOTIFICATION_ID integer not null, user integer, primary key (RECIPIENT_ID));
#
# Now just add new constraints and indexes
#
alter table ACTIVE_REVIEWER add constraint FK6381431EE71841CE foreign key (REVIEW_ID) references REVIEW;
alter table ACTIVE_REVIEWER add constraint FK6381431E1811CB9D foreign key (USER_ID) references USER;
alter table COMPLETED_REVIEWER add constraint FK4B1D6999E71841CE foreign key (REVIEW_ID) references REVIEW;
alter table COMPLETED_REVIEWER add constraint FK4B1D69991811CB9D foreign key (USER_ID) references USER;
create index notification_ref1_idx on NOTIFICATION (ref1);
create index notification_type_idx on NOTIFICATION (type);
create index notification_ref2_idx on NOTIFICATION (ref2);
create index notifiaction_review_idx on NOTIFICATION (review);
alter table NOTIFICATION add constraint FKAD9970EB92041DC4 foreign key (review) references REVIEW;
alter table NOTIFICATION add constraint FKAD9970EBBE1FB2F1 foreign key (actioner) references USER;
create index recipient_email_idx on RECIPIENT (email);
create index recipient_uiack_idx on RECIPIENT (uiACK);
alter table RECIPIENT add constraint FKD6F981F9210DC959 foreign key (user) references USER;
alter table RECIPIENT add constraint FKD6F981F931D229EE foreign key (NOTIFICATION_ID) references NOTIFICATION;
#
# and finally update the db version
#
UPDATE CRUVERSION SET version = 4 WHERE version_id = 1;