----------------------------------------------------------------------------------------------\012--  File: migration-014-015.sql\012--\012-- \012--  This script enables the migration of the database schema from 014 to 015.\012-- \012--  Update History\012--\012--\012--\011Who\011\011\011When\011\011What\012--\011---\011\011\011----\011\011----\012--\011Charles Ramin-Wright\0112008-02-19\011Initial Version \012--  \012----------------------------------------------------------------------------------------------\012\012--=================================\012-- New Tables and related objects\012--=================================\012\012------------------------------------------------------------------------------------\012--\012--      created table 'SAMPLE_TYPE_PROPERTY_TYPES' to implement entity 'SAMPLE TYPE PROPERTY TYPE'\012--      created table 'SAMPLE_PROPERTIES' to implement entity 'SAMPLE PROPERTY'\012--      created table 'EXPERIMENT_TYPE_PROPERTY_TYPES' to implement entity 'EXPERIMENT TYPE PROPERTY TYPE'\012--      created table 'EXPERIMENT_PROPERTIES' to implement entity 'EXPERIMENT PROPERTY'\012--\012--      created primary key 'EXPERIMENT_PROPERTIES.EXPR_PK' for table 'EXPERIMENT_PROPERTIES'\012--      created unique key 'EXPERIMENT_PROPERTIES.EXPR_BK_UK' for table 'EXPERIMENT_PROPERTIES'\012--\012--      created primary key 'EXPERIMENT_TYPE_PROPERTY_TYPES.ETPT_PK' for table 'EXPERIMENT_TYPE_PROPERTY_TYPES'\012--      created unique key 'EXPERIMENT_TYPE_PROPERTY_TYPES.ETPT_BK_UK' for table 'EXPERIMENT_TYPE_PROPERTY_TYPES'\012--\012--      created primary key 'SAMPLE_PROPERTIES.SAPR_PK' for table 'SAMPLE_PROPERTIES'\012--      created unique key 'SAMPLE_PROPERTIES.SAPR_BK_UK' for table 'SAMPLE_PROPERTIES'\012--\012--      created primary key 'SAMPLE_TYPE_PROPERTY_TYPES.STPT_PK' for table 'SAMPLE_TYPE_PROPERTY_TYPES'\012--      created unique key 'SAMPLE_TYPE_PROPERTY_TYPES.STPT_BK_UK' for table 'SAMPLE_TYPE_PROPERTY_TYPES'\012------------------------------------------------------------------------------------\012\012-- Creating tables\012\012CREATE TABLE EXPERIMENT_PROPERTIES (ID TECH_ID NOT NULL,VALUE GENERIC_VALUE,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,EXPE_ID TECH_ID NOT NULL,ETPT_ID TECH_ID NOT NULL,CVTE_ID TECH_ID,PERS_ID_REGISTERER TECH_ID NOT NULL);\012CREATE TABLE EXPERIMENT_TYPE_PROPERTY_TYPES (ID TECH_ID NOT NULL,EXTY_ID TECH_ID NOT NULL,PRTY_ID TECH_ID NOT NULL,IS_MANDATORY BOOLEAN_CHAR NOT NULL DEFAULT 'F',IS_MANAGED_INTERNALLY BOOLEAN_CHAR NOT NULL DEFAULT 'F',PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);\012CREATE TABLE SAMPLE_PROPERTIES (ID TECH_ID NOT NULL,SAMP_ID TECH_ID NOT NULL,STPT_ID TECH_ID NOT NULL,VALUE GENERIC_VALUE,CVTE_ID TECH_ID,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);\012CREATE TABLE SAMPLE_TYPE_PROPERTY_TYPES (ID TECH_ID NOT NULL,SATY_ID TECH_ID NOT NULL,PRTY_ID TECH_ID NOT NULL,IS_MANDATORY BOOLEAN_CHAR NOT NULL DEFAULT 'F',IS_MANAGED_INTERNALLY BOOLEAN_CHAR NOT NULL DEFAULT 'F',PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);\012\012-- Creating primary key constraints\012\012ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_PK PRIMARY KEY(ID);\012ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_PK PRIMARY KEY(ID);\012ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_PK PRIMARY KEY(ID);\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_PK PRIMARY KEY(ID);\012\012-- Creating unique constraints\012\012ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_BK_UK UNIQUE(EXPE_ID,ETPT_ID);\012ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_BK_UK UNIQUE(EXTY_ID,PRTY_ID);\012ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_BK_UK UNIQUE(SAMP_ID,STPT_ID);\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_BK_UK UNIQUE(SATY_ID,PRTY_ID);\012\012-- Creating foreign key constraints\012\012ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_CVTE_FK FOREIGN KEY (CVTE_ID) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);\012ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_ETPT_FK FOREIGN KEY (ETPT_ID) REFERENCES EXPERIMENT_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE;\012ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_EXPE_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS(ID);\012ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);\012ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_EXTY_FK FOREIGN KEY (EXTY_ID) REFERENCES EXPERIMENT_TYPES(ID);\012ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);\012ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_PRTY_FK FOREIGN KEY (PRTY_ID) REFERENCES PROPERTY_TYPES(ID);\012ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_CVTE_FK FOREIGN KEY (CVTE_ID) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);\012ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);\012ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_SAMP_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES(ID);\012ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_STPT_FK FOREIGN KEY (STPT_ID) REFERENCES SAMPLE_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE;\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_PRTY_FK FOREIGN KEY (PRTY_ID) REFERENCES PROPERTY_TYPES(ID);\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_SATY_FK FOREIGN KEY (SATY_ID) REFERENCES SAMPLE_TYPES(ID);\012\012-- Creating check constraints\012\012ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_CK CHECK ((VALUE IS NOT NULL AND CVTE_ID IS NULL) OR (VALUE IS NULL AND CVTE_ID IS NOT NULL));\012ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_CK CHECK ((VALUE IS NOT NULL AND CVTE_ID IS NULL) OR (VALUE IS NULL AND CVTE_ID IS NOT NULL));\012\012\012-- Creating sequences\012\012CREATE SEQUENCE ETPT_ID_SEQ;\012CREATE SEQUENCE EXPERIMENT_PROPERTY_ID_SEQ;\012CREATE SEQUENCE SAMPLE_PROPERTY_ID_SEQ;\012CREATE SEQUENCE STPT_ID_SEQ;\012\012\012-- Creating indexes\012\012CREATE INDEX ETPT_EXTY_FK_I ON EXPERIMENT_TYPE_PROPERTY_TYPES (EXTY_ID);\012CREATE INDEX ETPT_PERS_FK_I ON EXPERIMENT_TYPE_PROPERTY_TYPES (PERS_ID_REGISTERER);\012CREATE INDEX ETPT_PRTY_FK_I ON EXPERIMENT_TYPE_PROPERTY_TYPES (PRTY_ID);\012CREATE INDEX EXPR_CVTE_FK_I ON EXPERIMENT_PROPERTIES (CVTE_ID);\012CREATE INDEX EXPR_ETPT_FK_I ON EXPERIMENT_PROPERTIES (ETPT_ID);\012CREATE INDEX EXPR_EXPE_FK_I ON EXPERIMENT_PROPERTIES (EXPE_ID);\012CREATE INDEX EXPR_PERS_FK_I ON EXPERIMENT_PROPERTIES (PERS_ID_REGISTERER);\012CREATE INDEX SAPR_CVTE_FK_I ON SAMPLE_PROPERTIES (CVTE_ID);\012CREATE INDEX SAPR_PERS_FK_I ON SAMPLE_PROPERTIES (PERS_ID_REGISTERER);\012CREATE INDEX SAPR_SAMP_FK_I ON SAMPLE_PROPERTIES (SAMP_ID);\012CREATE INDEX SAPR_STPT_FK_I ON SAMPLE_PROPERTIES (STPT_ID);\012CREATE INDEX STPT_PERS_FK_I ON SAMPLE_TYPE_PROPERTY_TYPES (PERS_ID_REGISTERER);\012CREATE INDEX STPT_PRTY_FK_I ON SAMPLE_TYPE_PROPERTY_TYPES (PRTY_ID);\012CREATE INDEX STPT_SATY_FK_I ON SAMPLE_TYPE_PROPERTY_TYPES (SATY_ID);\012\012\012\012--====================================\012-- Modifications of existing objects\012--====================================\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Add column IS_MANAGED_INTERNALLY to table MATERIAL_TYPE_PROPERTY_TYPES in order \012--- to allow internally managed material properties.\012---------------------------------------------------------------------------------------------------\012\012ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD IS_MANAGED_INTERNALLY BOOLEAN_CHAR NOT NULL DEFAULT 'F';\012\012------------------------------------------------------------------------------------\012--  Purpose:  Drop PERS_ID for PROCEDURES\012------------------------------------------------------------------------------------\012\012alter table procedures drop column pers_id;\012\012------------------------------------------------------------------------------------\012--  Purpose:  Rename PERS_ID into PERS_ID_REGISTERER for CONTROLLED_VOCABULARIES \012--  and CONTROLLED_VOCABULARY_TERMS\012------------------------------------------------------------------------------------\012\012alter table controlled_vocabularies rename column pers_id to pers_id_registerer;\012alter table controlled_vocabulary_terms rename column pers_id to pers_id_registerer; \012\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Rename the check constraint from VALUE_OR_CVTE_ID to MAPR_CK of table MATERIAL_PROPERTIES\012---------------------------------------------------------------------------------------------------\012\012ALTER TABLE MATERIAL_PROPERTIES DROP CONSTRAINT VALUE_OR_CVTE_ID;\012ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_CK CHECK ((VALUE IS NOT NULL AND CVTE_ID IS NULL) OR (VALUE IS NULL AND CVTE_ID IS NOT NULL));\012\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Add the column IS_MANAGED_INTERNALLY to the CONTROLLED_VOCABULARIES table \012---------------------------------------------------------------------------------------------------\012alter table controlled_vocabularies add is_managed_internally boolean_char NOT NULL DEFAULT 'F';\012\012--====================================\012-- Add Master Data\012--====================================\012\012-----------------------------------------------------------------------------------\012--  Purpose:  Create Controlled Vocabulary PLATE_GEOMETRY\012-----------------------------------------------------------------------------------\012insert into controlled_vocabularies \012       ( id\012       , code\012       , description\012       , pers_id_registerer\012       , is_managed_internally )\012values  (nextval('CONTROLLED_VOCABULARY_ID_SEQ')\012       , 'PLATE_GEOMETRY'\012       , 'The geometry or dimensions of a plate'\012       , (select id from persons where user_id ='system')\012       , true);\012\012\012-----------------------------------------------------------------------------------\012--  Purpose:  Create Controlled Vocabulary Terms for PLATE_GEOMETRY\012-----------------------------------------------------------------------------------\012insert into controlled_vocabulary_terms \012       ( id\012       , code\012       , covo_id \012       , pers_id_registerer)\012values  (nextval('CVTE_ID_SEQ')\012       , '384_WELLS_16X24'\012       , (select id from controlled_vocabularies where code ='PLATE_GEOMETRY')\012       , (select id from persons where user_id ='system'));\012\012\012-----------------------------------------------------------------------------------\012--  Purpose:  Create property type PLATE_GEOMETRY\012-----------------------------------------------------------------------------------\012insert into property_types\012(id\012,code\012,description\012,label\012,daty_id\012,covo_id\012,pers_id_registerer)\012values \012(nextval('PROPERTY_TYPE_ID_SEQ')\012,'PLATE_GEOMETRY'\012,'Plate Geometry'\012,'Plate Geometry'\012,(select id from data_types where code ='CONTROLLEDVOCABULARY')\012,(select id from controlled_vocabularies where code ='PLATE_GEOMETRY')\012,(select id from persons where user_id ='system')\012);\012\012\012------------------------------------------------------------------------------------\012--  Purpose:  Insert an initial data set into the table SAMPLE_TYPE_PROPERTY_TYPES\012------------------------------------------------------------------------------------\012\012   ---------------------------------\012   --  Sample Type   MASTER_PLATE\012   --  Property Type PLATE_GEOMETRY   \012   ---------------------------------\012\012insert into sample_type_property_types\012(   id\012   ,saty_id\012   ,prty_id\012   ,is_mandatory\012   ,is_managed_internally\012   ,pers_id_registerer\012   )\012values \012   (nextval('STPT_ID_SEQ')\012   ,(select id from sample_types where code = 'MASTER_PLATE')\012   ,(select id from property_types where code = 'PLATE_GEOMETRY')\012   ,true\012   ,true\012   ,(select id from persons where user_id ='system')\012);\012\012   ---------------------------------\012   --  Sample Type   CONTROL_LAYOUT\012   --  Property Type PLATE_GEOMETRY   \012   ---------------------------------\012\012insert into sample_type_property_types\012(   id\012   ,saty_id\012   ,prty_id\012   ,is_mandatory\012   ,is_managed_internally\012   ,pers_id_registerer\012   )\012values \012   (nextval('STPT_ID_SEQ')\012   ,(select id from sample_types where code = 'CONTROL_LAYOUT')\012   ,(select id from property_types where code = 'PLATE_GEOMETRY')\012   ,true\012   ,true\012   ,(select id from persons where user_id ='system')\012);\012\012\012--==========================================\012--\012--   Data Migration\012--\012--==========================================\012\012---------------------------------------------------------------------------------\012--  As the 2 new sample_type_property_types records were defined to be mandatory,\012--  then there must be a corresponding SAMPLE_PROPERTIES record\012--  for each existing SAMPLES record. \012--\012--  Given the current knowledge of the data to be migrated, the following value\012--  is used for each Sample Property:\012--\012--  Linked to the controlled vocabulary term 384_WELLS_16X24\012--  of the controlled vocabulary PLATE_GEOMETRY.\012---------------------------------------------------------------------------------\012\012insert into sample_properties\012     ( id\012     , samp_id\012     , stpt_id\012     , cvte_id\012     , pers_id_registerer)\012select nextval('SAMPLE_PROPERTY_ID_SEQ')\012     , samp.id\012     , stpt.id\012     , cvte.id\012     , (select id from persons where user_id ='system')\012from sample_types saty\012, sample_type_property_types stpt\012, property_types prty\012, controlled_vocabularies covo\012, controlled_vocabulary_terms cvte\012, samples samp\012where saty.id = stpt.saty_id\012and   stpt.prty_id = prty.id\012and   prty.covo_id = covo.id \012and   covo.id = cvte.covo_id \012and   saty.id = samp.saty_id\012and   (saty.code = 'MASTER_PLATE' or saty.code = 'CONTROL_LAYOUT')\012and   prty.code = 'PLATE_GEOMETRY'\012and   cvte.code = '384_WELLS_16X24' ;\012