----------------------------------------------------------------------------------------------\012--  File: migration-018-019.sql\012--\012-- \012--  This script enables the migration of the database schema from 018 to 019.\012-- \012----------------------------------------------------------------------------------------------\012\012\012\012--=================================\012-- Create New Objects\012--=================================\012\012-- Domains\012\012CREATE DOMAIN DESCRIPTION_1000 AS VARCHAR(1000);\012CREATE DOMAIN AUTHORIZATION_ROLE as CODE CHECK (VALUE IN ('ADMIN', 'USER', 'OBSERVER', 'ETL_SERVER')) NOT NULL;\012\012\012-- Tables\012\012CREATE TABLE DATABASE_INSTANCES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,IS_ORIGINAL_SOURCE BOOLEAN_CHAR NOT NULL DEFAULT 'F',REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP);\012CREATE TABLE ROLE_ASSIGNMENTS (ID TECH_ID NOT NULL,ROLE_CODE AUTHORIZATION_ROLE,GROU_ID TECH_ID,DBIN_ID TECH_ID,PERS_ID_GRANTEE TECH_ID NOT NULL,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP);\012\012-- Columns \012\012-- Create the new DBIN_ID columns initially as NULLable!\012\012ALTER TABLE CONTROLLED_VOCABULARIES ADD COLUMN DBIN_ID TECH_ID;\012ALTER TABLE EXPERIMENT_TYPES ADD COLUMN DBIN_ID TECH_ID;\012ALTER TABLE EXPERIMENTS ADD COLUMN DESCRIPTION DESCRIPTION_250;\012ALTER TABLE EXPERIMENTS ADD COLUMN IS_PUBLIC BOOLEAN_CHAR NOT NULL DEFAULT 'F';\012ALTER TABLE FILE_FORMAT_TYPES ADD COLUMN DBIN_ID TECH_ID;\012ALTER TABLE GROUPS ADD COLUMN DBIN_ID TECH_ID;\012ALTER TABLE GROUPS ADD COLUMN GROU_ID_PARENT TECH_ID;\012ALTER TABLE GROUPS ADD COLUMN PERS_ID_LEADER TECH_ID;\012ALTER TABLE GROUPS ADD COLUMN DESCRIPTION DESCRIPTION_250;\012ALTER TABLE GROUPS ADD COLUMN REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP;\012ALTER TABLE GROUPS ADD COLUMN PERS_ID_REGISTERER TECH_ID;\012ALTER TABLE MATERIALS ADD COLUMN DBIN_ID TECH_ID;\012ALTER TABLE MATERIAL_TYPES ADD COLUMN DBIN_ID TECH_ID;\012ALTER TABLE OBSERVABLE_TYPES ADD COLUMN DBIN_ID TECH_ID;\012ALTER TABLE PERSONS ADD COLUMN DBIN_ID TECH_ID;\012ALTER TABLE PERSONS ADD COLUMN PERS_ID_REGISTERER TECH_ID;\012ALTER TABLE PERSONS ADD COLUMN REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP;\012ALTER TABLE PROCEDURE_TYPES ADD COLUMN DBIN_ID TECH_ID;\012ALTER TABLE PROJECTS ADD COLUMN PERS_ID_LEADER TECH_ID;\012ALTER TABLE PROJECTS ADD COLUMN PERS_ID_REGISTERER TECH_ID;\012ALTER TABLE PROJECTS ADD COLUMN REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP;\012ALTER TABLE PROJECTS ADD COLUMN DESCRIPTION DESCRIPTION_1000;\012ALTER TABLE PROPERTY_TYPES ADD COLUMN DBIN_ID TECH_ID;\012ALTER TABLE SAMPLES ADD COLUMN DBIN_ID TECH_ID ;\012ALTER TABLE SAMPLES ADD COLUMN GROU_ID TECH_ID ;\012ALTER TABLE SAMPLE_TYPES ADD COLUMN DBIN_ID TECH_ID;\012\012\012-- Primary Key Constraints\012\012ALTER TABLE DATABASE_INSTANCES ADD CONSTRAINT DBIN_PK PRIMARY KEY(ID);\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PK PRIMARY KEY(ID);\012\012-- Unique Key Constraints\012\012ALTER TABLE DATABASE_INSTANCES ADD CONSTRAINT DBIN_BK_UK UNIQUE(CODE);\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_BK_UK UNIQUE(PERS_ID_GRANTEE,ROLE_CODE,GROU_ID,DBIN_ID);\012\012-- Foreign Key Constraints\012\012ALTER TABLE CONTROLLED_VOCABULARIES ADD CONSTRAINT COVO_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE GROUPS ADD CONSTRAINT GROU_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE GROUPS ADD CONSTRAINT GROU_GROU_FK FOREIGN KEY (GROU_ID_PARENT) REFERENCES GROUPS(ID);\012ALTER TABLE GROUPS ADD CONSTRAINT GROU_PERS_FK_LEADER FOREIGN KEY (PERS_ID_LEADER) REFERENCES PERSONS(ID);\012ALTER TABLE GROUPS ADD CONSTRAINT GROU_PERS_FK_REGISTERER FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);\012ALTER TABLE MATERIALS ADD CONSTRAINT MATE_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE OBSERVABLE_TYPES ADD CONSTRAINT OBTY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE PERSONS ADD CONSTRAINT PERS_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE PROCEDURE_TYPES ADD CONSTRAINT PCTY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_PERS_FK_LEADER FOREIGN KEY (PERS_ID_LEADER) REFERENCES PERSONS(ID);\012ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_PERS_FK_REGISTERER FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);\012ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_GROU_FK FOREIGN KEY (GROU_ID) REFERENCES GROUPS(ID);\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PERS_FK_GRANTEE FOREIGN KEY (PERS_ID_GRANTEE) REFERENCES PERSONS(ID);\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PERS_FK_REGISTERER FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);\012ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_GROU_FK FOREIGN KEY (GROU_ID) REFERENCES GROUPS(ID);\012ALTER TABLE SAMPLE_TYPES ADD CONSTRAINT SATY_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012\012\012-- Sequences\012\012CREATE SEQUENCE DATABASE_INSTANCE_ID_SEQ;\012CREATE SEQUENCE ROLE_ASSIGNMENT_ID_SEQ;\012\012-- Indices\012\012CREATE INDEX EXDA_CVTE_FK_I ON EXTERNAL_DATA (CVTE_ID_STOR_FMT);\012CREATE INDEX GROU_DBIN_FK_I ON GROUPS (DBIN_ID);\012CREATE INDEX GROU_GROU_FK_I ON GROUPS (GROU_ID_PARENT);\012CREATE INDEX GROU_PERS_FK_I ON GROUPS (PERS_ID_LEADER);\012CREATE INDEX GROU_PERS_REGISTERED_BY_FK_I ON GROUPS (PERS_ID_REGISTERER);\012CREATE INDEX PROJ_PERS_FK_I_LEADER ON PROJECTS (PERS_ID_LEADER);\012CREATE INDEX PROJ_PERS_FK_I_REGISTERER ON PROJECTS (PERS_ID_REGISTERER);\012CREATE INDEX ROAS_DBIN_FK_I ON ROLE_ASSIGNMENTS (DBIN_ID);\012CREATE INDEX ROAS_GROU_FK_I ON ROLE_ASSIGNMENTS (GROU_ID);\012CREATE INDEX ROAS_PERS_FK_I_GRANTEE ON ROLE_ASSIGNMENTS (PERS_ID_GRANTEE);\012CREATE INDEX ROAS_PERS_FK_I_REGISTERER ON ROLE_ASSIGNMENTS (PERS_ID_REGISTERER);\012\012\012--====================================\012-- Alter Objects\012--====================================\012\012-- Domains\012\012ALTER DOMAIN BOOLEAN_CHAR_OR_UNKNOWN SET DEFAULT 'U';\012\012-- Tables\012\012ALTER TABLE PERSONS ALTER COLUMN USER_ID SET NOT NULL;\012\012-- Primary Key Constraints\012\012\012-- Unique Key Constraints\012\012   -- Drop the Unique Key Constraints\012ALTER TABLE CONTROLLED_VOCABULARIES DROP CONSTRAINT COVO_BK_UK ;\012ALTER TABLE EXPERIMENT_TYPES DROP CONSTRAINT EXTY_BK_UK ;\012ALTER TABLE FILE_FORMAT_TYPES DROP CONSTRAINT FFTY_BK_UK ;\012ALTER TABLE GROUPS DROP CONSTRAINT GROU_BK_UK ;\012ALTER TABLE MATERIALS DROP CONSTRAINT MATE_BK_UK ;\012ALTER TABLE MATERIAL_TYPES DROP CONSTRAINT MATY_BK_UK ;\012ALTER TABLE OBSERVABLE_TYPES DROP CONSTRAINT OBTY_BK_UK ;\012ALTER TABLE PERSONS DROP CONSTRAINT PERS_BK_UK ;\012ALTER TABLE PROCEDURE_TYPES DROP CONSTRAINT PCTY_BK_UK ;\012ALTER TABLE PROPERTY_TYPES DROP CONSTRAINT PRTY_BK_UK ;\012ALTER TABLE ROLE_ASSIGNMENTS DROP CONSTRAINT ROAS_BK_UK ;\012ALTER TABLE SAMPLES DROP CONSTRAINT SAMP_BK_UK ;\012ALTER TABLE SAMPLE_TYPES DROP CONSTRAINT SATY_BK_UK ;\012\012-- There was a bug in migration - the constraint was not created. So we drop it only if it exists.\012create function remove_maty_uk_constraint() returns void AS $$\012begin\012   perform *\012     FROM information_schema.table_constraints WHERE constraint_name='MATY_UK';\012   if found\012   then\012\011ALTER TABLE MATERIAL_TYPES DROP CONSTRAINT MATY_UK ;\012   end if;\012end;\012$$ language 'plpgsql';\012select remove_maty_uk_constraint();\012drop function remove_maty_uk_constraint();\012\012   -- ReCreate the Unique Key Constraints with the extended columns\012ALTER TABLE CONTROLLED_VOCABULARIES ADD CONSTRAINT COVO_BK_UK UNIQUE(CODE,IS_INTERNAL_NAMESPACE,DBIN_ID);\012ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_BK_UK UNIQUE(CODE,DBIN_ID);\012ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_BK_UK UNIQUE(CODE,DBIN_ID);\012ALTER TABLE GROUPS ADD CONSTRAINT GROU_BK_UK UNIQUE(CODE,DBIN_ID);\012ALTER TABLE MATERIALS ADD CONSTRAINT MATE_BK_UK UNIQUE(CODE,MATY_ID,DBIN_ID);\012ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_BK_UK UNIQUE(CODE,DBIN_ID);\012ALTER TABLE OBSERVABLE_TYPES ADD CONSTRAINT OBTY_BK_UK UNIQUE(CODE,DBIN_ID);\012ALTER TABLE PERSONS ADD CONSTRAINT PERS_BK_UK UNIQUE(DBIN_ID,USER_ID);\012ALTER TABLE PROCEDURE_TYPES ADD CONSTRAINT PCTY_BK_UK UNIQUE(CODE,DBIN_ID);\012ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_BK_UK UNIQUE(CODE,IS_INTERNAL_NAMESPACE,DBIN_ID);\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_BK_UK UNIQUE(PERS_ID_GRANTEE,ROLE_CODE,GROU_ID,DBIN_ID);\012ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_BK_UK UNIQUE(CODE,DBIN_ID,GROU_ID);\012ALTER TABLE SAMPLE_TYPES ADD CONSTRAINT SATY_BK_UK UNIQUE(CODE,DBIN_ID);\012\012\012\012-- Foreign Key Constraints\012\012   -- Rename according to the naming convention\012ALTER TABLE EXTERNAL_DATA DROP CONSTRAINT EXDA_STOR_FMT_FK;\012ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_CVTE_FK FOREIGN KEY (CVTE_ID_STOR_FMT) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);\012\012\012-- Check Constraints\012\012-- Indexes\012\012   -- Rename according to the naming convention\012DROP INDEX DATA_SAMP_DERIVED_FROM_FK_I ;\012DROP INDEX DATA_SAMP_FK_I ;\012CREATE INDEX DATA_SAMP_FK_I_ACQUIRED_FROM ON DATA (SAMP_ID_ACQUIRED_FROM);\012CREATE INDEX DATA_SAMP_FK_I_DERIVED_FROM ON DATA (SAMP_ID_DERIVED_FROM);\012\012\012\012\012--====================================\012-- Delete Objects\012--====================================\012\012-- Domains\012\012DROP DOMAIN DOUBLE_PRECISION_VALUE;\012\012-- Tables\012\012-- Sequences\012\012-- Indices\012\012\012--=================================\012-- Data Migration\012--=================================\012\012INSERT INTO database_instances(\012              id\012            , code\012            , is_original_source)\012    VALUES (  nextval('DATABASE_INSTANCE_ID_SEQ')\012            , 'SYSTEM_DEFAULT'\012            , 'T');\012\012\012-----------------------------------------------------------------------------------\012-- Fill the FK column in all tables that reference the DATABASE_INSTANCES table\012-----------------------------------------------------------------------------------\012\012update CONTROLLED_VOCABULARIES  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012update EXPERIMENT_TYPES  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012update FILE_FORMAT_TYPES  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012update GROUPS  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012update MATERIALS  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012update MATERIAL_TYPES  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012update OBSERVABLE_TYPES  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012update PERSONS  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012update PROCEDURE_TYPES  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012update PROPERTY_TYPES  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012update SAMPLES  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012update SAMPLE_TYPES  \012set dbin_id = (select id from database_instances where code = 'SYSTEM_DEFAULT');\012\012\012-----------------------------------------------------------------------------------\012-- Fill the FK column in all tables that newly reference the PERSONS table\012-----------------------------------------------------------------------------------\012\012update GROUPS  \012set PERS_ID_REGISTERER = (select id from persons where user_id = 'system');\012\012\012\012\012-- Columns \012\012-- Change certain new columns to possess the NOT NULL constraint\012\012ALTER TABLE CONTROLLED_VOCABULARIES ALTER COLUMN DBIN_ID SET NOT NULL;\012ALTER TABLE EXPERIMENT_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;\012ALTER TABLE FILE_FORMAT_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;\012ALTER TABLE GROUPS ALTER COLUMN DBIN_ID SET NOT NULL;\012ALTER TABLE GROUPS ALTER COLUMN PERS_ID_REGISTERER SET NOT NULL;\012ALTER TABLE MATERIALS ALTER COLUMN DBIN_ID SET NOT NULL;\012ALTER TABLE MATERIAL_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;\012ALTER TABLE OBSERVABLE_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;\012ALTER TABLE PERSONS ALTER COLUMN DBIN_ID SET NOT NULL;\012ALTER TABLE PROCEDURE_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;\012ALTER TABLE PROPERTY_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;\012ALTER TABLE SAMPLE_TYPES ALTER COLUMN DBIN_ID SET NOT NULL;\012\012-- Check Constraints\012\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_DBIN_GROU_ARC_CK CHECK ((DBIN_ID IS NOT NULL AND GROU_ID IS NULL) OR (DBIN_ID IS NULL AND GROU_ID IS NOT NULL));\012ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_DBIN_GROU_ARC_CK CHECK ((DBIN_ID IS NOT NULL AND GROU_ID IS NULL) OR (DBIN_ID IS NULL AND GROU_ID IS NOT NULL));\012