------------------------------------------------------------------------------
--  File: migration-003-004.sql
--
-- 
--  This script enables the migration of the database schema from 003 to 004.
-- 
--  Update History
--
--
--	Who			When		What
--	---			----		----
--	Randy (CRW)		17-07-2007	Initial Version
------------------------------------------------------------------------------


---------
-- Tables
---------

CREATE TABLE ORGANIZATIONS  (ID TECH_ID NOT NULL,CODE CODE NOT NULL) ;

CREATE TABLE PROJECTS  (ID TECH_ID NOT NULL,CODE CODE NOT NULL,ORGA_ID TECH_ID NOT NULL) ;


----------------
-- Table Columns
----------------

ALTER TABLE EXPERIMENTS ADD COLUMN PROJ_ID TECH_ID NOT NULL;

ALTER TABLE EXPERIMENT_PROPERTIES ADD COLUMN VERSION INTEGER NOT NULL;
ALTER TABLE EXPERIMENT_PROPERTIES ADD COLUMN PERS_ID_REGISTERER TECH_ID NOT NULL;
ALTER TABLE EXPERIMENT_PROPERTIES ADD COLUMN REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL;


--------------------------
-- Primary Key Constraints
--------------------------

ALTER TABLE ORGANIZATIONS ADD CONSTRAINT ORGA_PK PRIMARY KEY(ID);

ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_PK PRIMARY KEY(ID);


---------------------------------------
-- Business (Candidate) Key Constraints
---------------------------------------

ALTER TABLE ORGANIZATIONS ADD CONSTRAINT ORGA_BK_UK UNIQUE(CODE);

ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_BK_UK UNIQUE(CODE,ORGA_ID);

ALTER TABLE EXPERIMENTS DROP CONSTRAINT EXPE_BK_UK;
ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_BK_UK UNIQUE(CODE,PROJ_ID);

ALTER TABLE EXPERIMENT_PROPERTIES DROP CONSTRAINT EXPR_BK_UK ;
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_BK_UK UNIQUE(EXPE_ID,FILE_NAME,VERSION);


--------------------------
-- Foreign Key Constraints
--------------------------

ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_ORGA_FK FOREIGN KEY (ORGA_ID)REFERENCES ORGANIZATIONS(ID);

ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_PROJ_FK FOREIGN KEY (PROJ_ID)REFERENCES PROJECTS(ID);

ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER)REFERENCES PERSONS(ID);


-------------
--  Sequences
-------------

CREATE SEQUENCE ORGANIZATION_ID_SEQ;

CREATE SEQUENCE PROJECT_ID_SEQ;
