----------------------------------------------------------------------------------------------\012--  File: migration-015-016.sql\012--\012-- \012--  This script enables the migration of the database schema from 015 to 016.\012-- \012--  Update History\012--\012--\012--\011Who\011\011\011When\011\011What\012--\011---\011\011\011----\011\011----\012--\011Bernd Rinn\0112008-03-22\011Initial Version \012--  \012----------------------------------------------------------------------------------------------\012\012\012--=================================\012-- New Domains\012--=================================\012\012-----------------------------------------------------------------------------------\012--  Purpose:  Change all CODE columns to LONG_CODE, then re-create DOMAIN CODE identical to LONG_CODE\012--  and change LONG_CODE columns back to the (new) CODE \012-----------------------------------------------------------------------------------\012\012-- First step: create new domain LONG_CODE\012CREATE DOMAIN LONG_CODE AS VARCHAR(40);\012\012-- Second step: change all CODE to LONG_CODE\012ALTER TABLE CONTROLLED_VOCABULARIES ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE DATA_TYPES ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE EXPERIMENTS ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE EXPERIMENT_TYPES ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE FILE_FORMAT_TYPES ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE LOCATOR_TYPES ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE MATERIALS ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE MATERIAL_BATCHES ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE MATERIAL_TYPES ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE OBSERVABLE_TYPES ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE ORGANIZATIONS ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE PROCEDURE_TYPES ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE PROJECTS ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE PROPERTY_TYPES ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE SAMPLES ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE SAMPLE_COMPONENTS ALTER COLUMN CODE TYPE LONG_CODE;\012ALTER TABLE SAMPLE_TYPES ALTER COLUMN CODE TYPE LONG_CODE;\012\012-- Third step: re-create CODE as VARCHAR(40)\012DROP DOMAIN CODE;\012CREATE DOMAIN CODE AS VARCHAR(40);\012\012-- Forth step: change all LONG_CODE columns back to CODE\012ALTER TABLE CONTROLLED_VOCABULARIES ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE DATA_TYPES ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE EXPERIMENTS ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE EXPERIMENT_TYPES ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE FILE_FORMAT_TYPES ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE LOCATOR_TYPES ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE MATERIALS ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE MATERIAL_BATCHES ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE MATERIAL_TYPES ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE OBSERVABLE_TYPES ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE ORGANIZATIONS ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE PROCEDURE_TYPES ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE PROJECTS ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE PROPERTY_TYPES ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE SAMPLES ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE SAMPLE_COMPONENTS ALTER COLUMN CODE TYPE CODE;\012ALTER TABLE SAMPLE_TYPES ALTER COLUMN CODE TYPE CODE;\012\012-- Fifth step: drop domain LONG_CODE\012DROP DOMAIN LONG_CODE;\012\012-----------------------------------------------------------------------------------\012--  Purpose:  Create new domain TIME_STAMP_DFL and change current users of TIME_STAMP to TIME_STAMP_DFL\012--  This adds time zone information and allows for a clean separation between registration time stamps\012--  which should have a default of CURRENT_TIMESTAMP and other time stamps which should not. \012-----------------------------------------------------------------------------------\012\012-- First step: create new domain TIME_STAMP_DFL\012CREATE DOMAIN TIME_STAMP_DFL AS TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP;\012\012-- Second step: change all TIME_STAMP columns to TIME_STAMP_DFL\012ALTER TABLE CONTROLLED_VOCABULARIES ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE CONTROLLED_VOCABULARIES ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE CONTROLLED_VOCABULARIES ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE CONTROLLED_VOCABULARY_TERMS ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE CONTROLLED_VOCABULARY_TERMS ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE CONTROLLED_VOCABULARY_TERMS ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE DATA ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE DATA ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE DATA ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE EXPERIMENTS ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE EXPERIMENTS ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE EXPERIMENTS ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE EXPERIMENT_ATTACHMENTS ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE EXPERIMENT_ATTACHMENTS ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE EXPERIMENT_ATTACHMENTS ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE EXPERIMENT_PROPERTIES ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE EXPERIMENT_PROPERTIES ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE EXPERIMENT_PROPERTIES ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE INVALIDATIONS ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE INVALIDATIONS ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE INVALIDATIONS ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE MATERIALS ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE MATERIALS ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE MATERIALS ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE MATERIAL_BATCHES ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE MATERIAL_BATCHES ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE MATERIAL_BATCHES ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE MATERIAL_PROPERTIES ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE MATERIAL_PROPERTIES ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE MATERIAL_PROPERTIES ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE PROCEDURES ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE PROCEDURES ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE PROCEDURES ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE SAMPLES ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE SAMPLES ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE SAMPLES ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE SAMPLE_PROPERTIES ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE SAMPLE_PROPERTIES ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE SAMPLE_PROPERTIES ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP TYPE TIME_STAMP_DFL;\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP DROP NOT NULL;\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ALTER COLUMN REGISTRATION_TIMESTAMP DROP DEFAULT;\012\012-- Third step: drop old domain TIME_STAMP\012DROP DOMAIN TIME_STAMP;\012\012-- Forth step: create new domain time_stamp\012CREATE DOMAIN TIME_STAMP AS TIMESTAMP WITH TIME ZONE;\012\012--=================================\012-- New Tables and related objects\012--=================================\012\012------------------------------------------------------------------------------------\012-- table DATA_SET_RELATIONSHIPS (new)\012------------------------------------------------------------------------------------\012\012-- Creating tables\012\012CREATE TABLE DATA_SET_RELATIONSHIPS (ID TECH_ID NOT NULL,DATA_ID_PARENT TECH_ID NOT NULL,DATA_ID_CHILD TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL);\012\012-- Creating primary key constraints\012\012ALTER TABLE DATA_SET_RELATIONSHIPS ADD CONSTRAINT DSRE_PK PRIMARY KEY(ID);\012\012-- Creating unique constraints\012\012ALTER TABLE DATA_SET_RELATIONSHIPS ADD CONSTRAINT DSRE_BK_UK UNIQUE(DATA_ID_CHILD,DATA_ID_PARENT);\012\012-- Creating foreign key constraints\012\012ALTER TABLE DATA_SET_RELATIONSHIPS ADD CONSTRAINT DSRE_DATA_FK_CHILD FOREIGN KEY (DATA_ID_CHILD) REFERENCES DATA(ID);\012ALTER TABLE DATA_SET_RELATIONSHIPS ADD CONSTRAINT DSRE_DATA_FK_PARENT FOREIGN KEY (DATA_ID_PARENT) REFERENCES DATA(ID);\012\012-- Creating sequences\012\012CREATE SEQUENCE DATA_SET_RELATIONSHIP_ID_SEQ;\012\012-- Creating indexes\012\012CREATE INDEX DSRE_DATA_FK_I_CHILD ON DATA_SET_RELATIONSHIPS (DATA_ID_CHILD);\012CREATE INDEX DSRE_DATA_FK_I_PARENT ON DATA_SET_RELATIONSHIPS (DATA_ID_PARENT);\012\012--====================================\012-- Drop Tables and related objects\012--====================================\012\012------------------------------------------------------------------------------------\012-- table DATA_VALUES (deleted)\012------------------------------------------------------------------------------------\012\012-- Delete constraints\012\012ALTER TABLE DATA_VALUES DROP CONSTRAINT DAVA_PK;\012ALTER TABLE DATA_VALUES DROP CONSTRAINT DAVA_BK_UK;\012ALTER TABLE DATA_VALUES DROP CONSTRAINT DAVA_DATA_FK;\012ALTER TABLE DATA_VALUES DROP CONSTRAINT DAVA_SACO_FK;\012\012-- Delete indices\012\012DROP INDEX DAVA_DATA_FK_I;\012DROP INDEX DAVA_SACO_FK_I;\012\012-- Delete sequences\012\012DROP SEQUENCE DATA_VALUE_ID_SEQ;\012\012-- Delete tables\012\012DROP TABLE DATA_VALUES;\012\012--====================================\012-- Modifications of existing objects\012--====================================\012\012------------------------------------------------------------------------------------\012-- table ORGANIZATIONS (changed)\012-- * rename to GROUPS\012--\012-- table DATA (changed)\012-- * rename column PROC_ID_ACQUIRED_BY into: PROC_ID_PRODUCED_BY\012-- * add columns:\012-- ** IS_PLACEHOLDER BOOLEAN_CHAR\012-- ** CODE CODE\012-- ** DATA_PRODUCER_CODE CODE\012-- ** PRODUCTION_TIMESTAMP TIME_STAMP\012--\012-- table EXTERNAL_DATA (changed)\012-- * add column:\012-- ** CVTE_ID_STOR_FMT TECH_ID NOT NULL\012--\012-- table PROPERTY_TYPES (changed)\012-- * add column IS_MANAGED_INTERNALLY BOOLEAN_CHAR NOT NULL DEFAULT 'F'\012-- * add unique constraint PRTY_BK_UK_LBL for column LABEL\012--\012-- tables EXPERIMENT_TYPE_PROPERTY_TYPES, MATERIAL_TYPE_PROPERTY_TYPES, SAMPLE_TYPE_PROPERTY_TYPES\012-- * make the assignments of property types DESCRIPTION, NUCLEOTIDE_SEQUENCE, OFFSET and GENE_SYMBOL \012--   to material types internally managed \012--\012-- table PROCEDURE_TYPES (changed)\012-- * add column IS_DATA_ACQUISITION BOOLEAN_CHAR NOT NULL DEFAULT 'F'.\012--\012-- all tables\012-- * convert all codes to upper case\012------------------------------------------------------------------------------------\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Rename table ORGANIZATIONS to GROUPS.\012---------------------------------------------------------------------------------------------------\012\012ALTER TABLE PROJECTS DROP CONSTRAINT PROJ_ORGA_FK;\012ALTER TABLE ORGANIZATIONS DROP CONSTRAINT ORGA_BK_UK;\012ALTER TABLE ORGANIZATIONS DROP CONSTRAINT ORGA_PK;\012ALTER TABLE ORGANIZATIONS RENAME TO GROUPS;\012ALTER INDEX PROJ_ORGA_FK_I RENAME TO PROJ_GROU_FK_I;\012select RENAME_SEQUENCE('ORGANIZATION_ID_SEQ', 'GROUP_ID_SEQ');\012ALTER TABLE PROJECTS RENAME COLUMN ORGA_ID TO GROU_ID;\012ALTER TABLE GROUPS ADD CONSTRAINT GROU_PK PRIMARY KEY(ID);\012ALTER TABLE GROUPS ADD CONSTRAINT GROU_BK_UK UNIQUE(CODE);\012ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_GROU_FK FOREIGN KEY (GROU_ID) REFERENCES GROUPS(ID);\012\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Add columnd IS_PLACEHOLDER of table DATA.\012---------------------------------------------------------------------------------------------------\012\012ALTER TABLE DATA ADD COLUMN IS_PLACEHOLDER BOOLEAN_CHAR NOT NULL;\012\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Add and populate column CODE of table DATA.\012---------------------------------------------------------------------------------------------------\012\012ALTER TABLE DATA ADD COLUMN CODE CODE;\012\012CREATE OR REPLACE FUNCTION populate_data_codes() RETURNS integer AS $$\012DECLARE\012    rec RECORD;\012BEGIN\012    FOR rec IN SELECT * FROM data LOOP\012        rec.code := to_char(rec.registration_timestamp, 'YYYYMMDDHH24MISSMS') || '-' || to_char(rec.id, 'FM99999999999999999999');\012        update data set code=rec.code where id=rec.id;\012    END LOOP;\012    RETURN 1;\012END;\012$$ LANGUAGE 'plpgsql';\012\012SELECT populate_data_codes();\012DROP FUNCTION populate_data_codes();\012\012ALTER TABLE DATA ALTER COLUMN CODE SET NOT NULL;\012ALTER TABLE DATA ADD CONSTRAINT DATA_BK_UK UNIQUE(CODE);\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Add and populate columns CVTE_ID_STOR_FMT.\012---------------------------------------------------------------------------------------------------\012\012ALTER TABLE EXTERNAL_DATA ADD COLUMN CVTE_ID_STOR_FMT TECH_ID;\012ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_STOR_FMT_FK FOREIGN KEY (CVTE_ID_STOR_FMT) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);\012\012------------------------------------------------------------------------------------\012--  Purpose:  Create trigger EXTERNAL_DATA_STORAGE_FORMAT_CHECK \012------------------------------------------------------------------------------------\012\012CREATE OR REPLACE FUNCTION EXTERNAL_DATA_STORAGE_FORMAT_CHECK() RETURNS trigger AS $$\012DECLARE\012   v_covo_code  CODE;\012BEGIN\012\012   select code into v_covo_code from controlled_vocabularies\012      where id = (select covo_id from controlled_vocabulary_terms where id = NEW.cvte_id_stor_fmt);\012\012   -- Check if the data storage format is a term of the controlled vocabulary "STORAGE_FORMAT"\012   if v_covo_code != '.STORAGE_FORMAT' then\012      RAISE EXCEPTION 'Insert/Update of Data (Code: %) failed, as its Storage Format is %, but is required to be STORAGE_FORMAT.', NEW.code, v_covo_code;\012   end if;\012\012   RETURN NEW;\012\012END;\012$$ LANGUAGE 'plpgsql';\012\012CREATE TRIGGER EXTERNAL_DATA_STORAGE_FORMAT_CHECK BEFORE INSERT OR UPDATE ON EXTERNAL_DATA\012    FOR EACH ROW EXECUTE PROCEDURE EXTERNAL_DATA_STORAGE_FORMAT_CHECK();\012\012-----------------------------------------------------------------------------------\012--  Purpose:  Create Controlled Vocabulary .STORAGE_FORMAT\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       , '.STORAGE_FORMAT'\012       , 'The on-disk storage format of a data set'\012       , (select id from persons where user_id ='system')\012       , true);\012\012\012-----------------------------------------------------------------------------------\012--  Purpose:  Create Controlled Vocabulary Terms for STORAGE_FORMAT\012-----------------------------------------------------------------------------------\012insert into controlled_vocabulary_terms \012       ( id\012       , code\012       , covo_id \012       , pers_id_registerer )\012values  (nextval('CVTE_ID_SEQ')\012       , 'PROPRIETARY'\012       , (select id from controlled_vocabularies where code = '.STORAGE_FORMAT')\012       , (select id from persons where user_id ='system'));\012\012insert into controlled_vocabulary_terms \012       ( id\012       , code\012       , covo_id \012       , pers_id_registerer )\012values  (nextval('CVTE_ID_SEQ')\012       , 'BDS_DIRECTORY'\012       , (select id from controlled_vocabularies where code = '.STORAGE_FORMAT')\012       , (select id from persons where user_id ='system'));\012\012\012--------------------------------------------------------------------------\012--  Purpose:  Insert an entry for OBSERVABLE_TYPE UNKNOWN\012--------------------------------------------------------------------------\012\012insert into observable_types\012(id\012,code\012,description)\012values \012(nextval('OBSERVABLE_TYPE_ID_SEQ')\012,'UNKNOWN'\012,'Unknown'\012);\012\012----------------------------------------------------------------------\012--  Purpose:  Insert an entry for PROCEDURE_TYPE UNKNOWN\012-----------------------------------------------------------------------\012\012insert into procedure_types\012(id\012,code\012,description)\012values \012(nextval('PROCEDURE_TYPE_ID_SEQ')\012,'UNKNOWN'\012,'Unknown'\012);\012\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Populate external_data.cvte_id_stor_fmt with a default value.\012---------------------------------------------------------------------------------------------------\012\012CREATE OR REPLACE FUNCTION polulate_external_data_cvte_id_stor_fmt() RETURNS integer AS $$\012DECLARE\012    rec RECORD;\012    dfl_stor_fmt_code VARCHAR := 'PROPRIETARY';\012    dfl_stor_fmt_id TECH_ID;\012BEGIN\012    select cvte.id into dfl_stor_fmt_id from controlled_vocabulary_terms cvte, controlled_vocabularies cv \012        where cvte.code = dfl_stor_fmt_code and cvte.covo_id = cv.id and cv.code = '.STORAGE_FORMAT'; \012    update external_data set cvte_id_stor_fmt = dfl_stor_fmt_id;\012    RETURN 1;\012END;\012$$ LANGUAGE 'plpgsql';\012\012SELECT polulate_external_data_cvte_id_stor_fmt();\012DROP FUNCTION polulate_external_data_cvte_id_stor_fmt();\012\012-- Now that the field is populated, create the not null constraint for it.\012\012ALTER TABLE EXTERNAL_DATA ALTER COLUMN CVTE_ID_STOR_FMT SET NOT NULL;\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Add and populate columns DATA_PRODUCER_CODE and PRODUCTION_TIMESTAMP to table DATA.\012---------------------------------------------------------------------------------------------------\012\012ALTER TABLE DATA ADD COLUMN DATA_PRODUCER_CODE CODE;\012ALTER TABLE DATA ADD COLUMN PRODUCTION_TIMESTAMP TIME_STAMP;\012\012CREATE OR REPLACE FUNCTION populate_data_producer_code_and_production_timestamp() RETURNS integer AS $$\012DECLARE\012    rec RECORD;\012    loty_rel_code VARCHAR := 'RELATIVE_LOCATION';\012    loty_rel_id TECH_ID;\012    meas_ts TIMESTAMP;\012    ds_code CODE;\012    meas_ts_str TEXT;\012BEGIN\012    select id into loty_rel_id from locator_types where code = loty_rel_code; \012    FOR rec IN SELECT d.id, ed.location FROM data d, external_data ed where d.id = ed.data_id and ed.loty_id = loty_rel_id LOOP\012        meas_ts_str = substring(rec.location from '^.*/([0-9]{14})_[^_]+_(.*)$');\012        ds_code := substring(rec.location from '^.*/[0-9]{14}_([^_]+)_(.*)$');\012        IF meas_ts_str is not null and ds_code is not null THEN\012            meas_ts := to_timestamp(meas_ts_str, 'YYYYMMDDHH24MISS');\012            update data set production_timestamp=meas_ts, data_producer_code=ds_code where id=rec.id;\012        END IF;\012    END LOOP;\012    RETURN 1;\012END;\012$$ LANGUAGE 'plpgsql';\012\012SELECT populate_data_producer_code_and_production_timestamp();\012DROP FUNCTION populate_data_producer_code_and_production_timestamp();\012\012-- Rename columns\012\012ALTER TABLE DATA RENAME COLUMN PROC_ID_ACQUIRED_BY to PROC_ID_PRODUCED_BY;\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Populate table DATA_SET_RELATIONSHIPS, by using heuristics.\012---------------------------------------------------------------------------------------------------\012\012CREATE OR REPLACE FUNCTION abs_interval(ts1 time_stamp_dfl, ts2 time_stamp_dfl) returns interval AS $$\012BEGIN\012    IF ts1 < ts2 THEN\012        RETURN ts2 - ts1;\012    ELSE\012        RETURN ts1 - ts2;\012    END IF;\012END;\012$$ LANGUAGE 'plpgsql';\012\012CREATE OR REPLACE FUNCTION populate_data_set_relationships() RETURNS INTEGER AS $$\012DECLARE\012    source_id TECH_ID;\012    rec_derived RECORD;\012    rec_acquired RECORD;\012    rel_location TEXT;\012    data_set_code TEXT;\012    data_set_code_trial TEXT;\012    loty_rel_code TEXT := 'RELATIVE_LOCATION';\012    loty_rel_id TECH_ID;\012    count INTEGER := 0;\012BEGIN\012    select id into loty_rel_id from locator_types where code = loty_rel_code;\012    FOR rec_derived IN select id, samp_id_derived_from, registration_timestamp from data where samp_id_derived_from is not null LOOP\012        source_id := NULL;\012        select location into rel_location from external_data where data_id = rec_derived.id and loty_id = loty_rel_id;\012        IF rel_location is not null then\012            data_set_code := substring(rel_location from '^.*/([0-9]{14}_[^_]+)_.*$');\012            IF data_set_code is not null THEN\012                FOR rec_acquired IN select id, samp_id_acquired_from from data where samp_id_acquired_from = rec_derived.samp_id_derived_from LOOP\012                    select location into rel_location from external_data where data_id = rec_acquired.id and loty_id = loty_rel_id;\012                    IF rel_location is not null THEN\012                        data_set_code_trial := substring(rel_location from '^.*/([0-9]{14}_[^_]+)_.*$');\012                        IF data_set_code_trial = data_set_code THEN\012                            source_id = rec_acquired.id;\012                            EXIT;\012                        END IF;\012                    END IF;\012                END LOOP;\012            END IF;\012        END IF;\012        -- If the location didn't tell us about data set relationships, try the backup strategy.\012        IF source_id is null THEN\012            -- Use the measured data set as source that is closest in registration timestamp.\012            select id into source_id from data where samp_id_acquired_from = rec_derived.samp_id_derived_from \012                and abs_interval(registration_timestamp, rec_derived.registration_timestamp) \012                    = (select min(abs_interval(registration_timestamp, rec_derived.registration_timestamp)) \012                        from data where samp_id_acquired_from = rec_derived.samp_id_derived_from);\012        END IF;\012        IF source_id is not null THEN\012            insert into data_set_relationships (id, data_id_parent, data_id_child)\012                values (nextval('DATA_SET_RELATIONSHIP_ID_SEQ'), source_id, rec_derived.id);\012            count := count + 1;\012        END IF;\012    END LOOP;\012    RETURN count;\012END;\012$$ LANGUAGE 'plpgsql';\012\012SELECT populate_data_set_relationships();\012DROP FUNCTION populate_data_set_relationships();\012DROP FUNCTION abs_interval(time_stamp_dfl, time_stamp_dfl);\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Add column IS_MANAGED_INTERNALLY to table PROPERTY_TYPES. Internally managed \012--  properties can not be assigned by users of openBIS, but only by the system. \012---------------------------------------------------------------------------------------------------\012\012ALTER TABLE PROPERTY_TYPES ADD COLUMN IS_MANAGED_INTERNALLY BOOLEAN_CHAR NOT NULL DEFAULT 'F';\012\012-- Update already existing property type PLATE_GEOMETRY\012update property_types set is_managed_internally = true, code = '.' || code where code = 'PLATE_GEOMETRY';\012\012-- Update already existing controlled vocabulary PLATE_GEOMETRY \012update controlled_vocabularies set code = '.' || code where code = 'PLATE_GEOMETRY';\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Add unique constraint on column LABEL. \012---------------------------------------------------------------------------------------------------\012\012CREATE OR REPLACE FUNCTION make_prty_labels_unique() RETURNS INTEGER AS $$\012DECLARE\012    prev_key TEXT := NULL;\012    this_key TEXT;\012    dup_count INTEGER;\012    cnt INTEGER := 0;\012    already_exists INTEGER;\012    new_label TEXT;\012    this_id TECH_ID;\012BEGIN\012    FOR this_id, this_key IN select id, label from property_types order by label,id LOOP\012        IF this_key = prev_key THEN\012            dup_count := dup_count + 1;\012            cnt := cnt + 1;\012        ELSE\012            dup_count := 0;\012        END IF;\012        IF dup_count > 0 THEN\012            -- Check whether the new key already exists.\012            LOOP\012                new_label := this_key || '(' || dup_count::text || ')';\012                select count(*) into already_exists from property_types where label = new_label;\012                IF already_exists = 0 THEN\012                    EXIT;\012                END IF;\012                dup_count := dup_count + 1;\012            END LOOP;\012            update property_types set label = new_label where id = this_id;\012        END IF;\012        prev_key := this_key;\012    END LOOP;\012    RETURN cnt;\012END;\012$$ LANGUAGE 'plpgsql';\012\012SELECT make_prty_labels_unique();\012DROP FUNCTION make_prty_labels_unique();\012\012ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_BK_UK_LBL UNIQUE(LABEL);\012\012-----------------------------------------------------------------------------------\012--  Purpose:  Add Controlled Vocabulary Terms to PLATE_GEOMETRY\012-----------------------------------------------------------------------------------\012\012insert into controlled_vocabulary_terms \012       ( id\012       , code\012       , covo_id \012       , pers_id_registerer)\012values  (nextval('CVTE_ID_SEQ')\012       , '96_WELLS_8X12'\012       , (select id from controlled_vocabularies where code = '.PLATE_GEOMETRY')\012       , (select id from persons where user_id = 'system'));\012\012insert into controlled_vocabulary_terms \012       ( id\012       , code\012       , covo_id \012       , pers_id_registerer)\012values  (nextval('CVTE_ID_SEQ')\012       , '1536_WELLS_32X48'\012       , (select id from controlled_vocabularies where code = '.PLATE_GEOMETRY')\012       , (select id from persons where user_id = 'system'));\012\012\012-----------------------------------------------------------------------------------\012--  Purpose:  Rename Observable Types: IMAGE -> HCS_IMAGE, IMAGE_ANALYSIS_DATA -> HCS_IMAGE_ANALYSIS_DATA\012--  and descriptions accordingly\012-----------------------------------------------------------------------------------\012\012update observable_types set code = 'HCS_IMAGE', description = 'Data derived from analysis of HCS images' where code = 'IMAGE';\012update observable_types set code = 'HCS_IMAGE_ANALYSIS_DATA', description = 'Data derived from analysis of HCS images' where code = 'IMAGE_ANALYSIS_DATA';\012\012\012-----------------------------------------------------------------------------------\012--  Purpose: Make the assignments of property types DESCRIPTION, NUCLEOTIDE_SEQUENCE, OFFSET \012--  and GENE_SYMBOL to material types internally managed. \012-----------------------------------------------------------------------------------\012\012update material_type_property_types set is_managed_internally = true\012    where prty_id in (select id from property_types where code = 'DESCRIPTION' \012        or code = 'NUCLEOTIDE_SEQUENCE' or code = 'OFFSET' or code = 'GENE_SYMBOL');\012\012-----------------------------------------------------------------------------------\012--  Purpose: Add column IS_DATA_ACQUISITION BOOLEAN_CHAR NOT NULL DEFAULT 'F' to table PROCEDURE_TYPES.\012-----------------------------------------------------------------------------------\012\012ALTER TABLE PROCEDURE_TYPES ADD COLUMN IS_DATA_ACQUISITION BOOLEAN_CHAR NOT NULL DEFAULT 'F';\012\012-- Set the new attribute for procedure type DATA_ACQUISITION\012update procedure_types set is_data_acquisition = true where code = 'DATA_ACQUISITION';\012\012-----------------------------------------------------------------------------------\012--  Purpose: Convert all codes to upper case. \012-----------------------------------------------------------------------------------\012\012CREATE OR REPLACE FUNCTION column_to_uppercase_fix_duplicates(tbl TEXT, clm TEXT) RETURNS INTEGER AS $$\012DECLARE\012    prev_key TEXT := NULL;\012    this_key TEXT;\012    dup_count INTEGER;\012    count INTEGER := 0;\012    already_exists INTEGER;\012    new_clm TEXT;\012BEGIN\012    FOR this_key IN EXECUTE 'select ' || clm || ' from ' || tbl \012        || ' order by upper(' || clm || '),id' LOOP\012        IF upper(this_key) = upper(prev_key) THEN\012            dup_count := dup_count + 1;\012            count := count + 1;\012        ELSE\012            dup_count := 0;\012        END IF;\012        IF dup_count > 0 THEN\012            -- Check whether the new key already exists.\012            LOOP\012                new_clm := this_key || '(' || dup_count::text || ')';\012                EXECUTE 'select count(*) from ' || tbl \012                    || ' where upper(' || clm || ') = ' || quote_literal(upper(new_clm)) \012                    into already_exists;\012                IF already_exists = 0 THEN\012                    EXIT;\012                END IF;\012                dup_count := dup_count + 1;\012            END LOOP;\012            EXECUTE 'update ' || tbl || ' set ' || clm || ' = ' || quote_literal(new_clm) || \012                ' where ' || clm || ' = ' || quote_literal(this_key);\012        END IF;\012        prev_key := this_key;\012    END LOOP;\012    EXECUTE 'update ' || tbl || ' set ' || clm || ' = upper(' || clm || ')';\012    RETURN count;\012END;\012$$ LANGUAGE 'plpgsql';\012\012\012CREATE OR REPLACE FUNCTION column_to_uppercase_fix_duplicates(tbl TEXT, clm TEXT, clm2 TEXT) RETURNS INTEGER AS $$\012DECLARE\012    prev_key TEXT := NULL;\012    prev_key2 TECH_ID;\012    this_key TEXT;\012    this_key2 TECH_ID;\012    dup_count INTEGER;\012    count INTEGER := 0;\012    already_exists INTEGER;\012    new_clm TEXT;\012BEGIN\012    FOR this_key, this_key2 IN EXECUTE 'select ' || clm || ',' || clm2 || ' from ' || tbl \012        || ' order by ' || clm2 || ',upper(' ||  clm || '),id' LOOP\012        IF upper(this_key) = upper(prev_key) and this_key2 = prev_key2 THEN\012            dup_count := dup_count + 1;\012            count := count + 1;\012        ELSE\012            dup_count := 0;\012        END IF;\012        IF dup_count > 0 THEN\012            -- Check whether the new key already exists.\012            LOOP\012                new_clm := this_key || '(' || dup_count::text || ')';\012                EXECUTE 'select count(*) from ' || tbl \012                    || ' where upper(' || clm || ') = ' || quote_literal(upper(new_clm)) \012                    || ' and ' || clm2 || ' = ' || quote_literal(this_key2) \012                    into already_exists;\012                IF already_exists = 0 THEN\012                    EXIT;\012                END IF;\012                dup_count := dup_count + 1;\012            END LOOP;\012            EXECUTE 'update ' || tbl || ' set ' || clm || ' = ' || quote_literal(new_clm) || \012                ' where ' || clm || ' = ' || quote_literal(this_key) || ' and ' \012                || clm2 || ' = ' || quote_literal(this_key2);\012        END IF;\012        prev_key := this_key;\012        prev_key2 := this_key2;\012    END LOOP;\012    EXECUTE 'update ' || tbl || ' set ' || clm || ' = upper(' || clm || ')';\012    RETURN count;\012END;\012$$ LANGUAGE 'plpgsql';\012\012\012SELECT column_to_uppercase_fix_duplicates('samples', 'code');\012SELECT column_to_uppercase_fix_duplicates('property_types', 'code');\012SELECT column_to_uppercase_fix_duplicates('controlled_vocabularies', 'code');\012\012SELECT column_to_uppercase_fix_duplicates('projects', 'code', 'grou_id');\012SELECT column_to_uppercase_fix_duplicates('experiments', 'code', 'proj_id');\012SELECT column_to_uppercase_fix_duplicates('controlled_vocabulary_terms', 'code', 'covo_id');\012SELECT column_to_uppercase_fix_duplicates('materials', 'code', 'maty_id');\012SELECT column_to_uppercase_fix_duplicates('material_batches', 'code', 'mate_id');\012SELECT column_to_uppercase_fix_duplicates('sample_components', 'code', 'samp_id');\012\012-- These will only need changes if someone entered them through the 'back door' directly on the server.\012\012SELECT column_to_uppercase_fix_duplicates('groups', 'code');\012SELECT column_to_uppercase_fix_duplicates('sample_types', 'code');\012SELECT column_to_uppercase_fix_duplicates('material_types', 'code');\012SELECT column_to_uppercase_fix_duplicates('experiment_types', 'code');\012SELECT column_to_uppercase_fix_duplicates('procedure_types', 'code');\012SELECT column_to_uppercase_fix_duplicates('observable_types', 'code');\012SELECT column_to_uppercase_fix_duplicates('locator_types', 'code');\012SELECT column_to_uppercase_fix_duplicates('file_format_types', 'code');\012SELECT column_to_uppercase_fix_duplicates('data_types', 'code');\012\012DROP FUNCTION column_to_uppercase_fix_duplicates(TEXT, TEXT);\012DROP FUNCTION column_to_uppercase_fix_duplicates(TEXT, TEXT, TEXT);\012\012-----------------------------------------------------------------------------------\012--  Purpose:  Delete sample_components with no associated material_batches.\012--  This deletes superfluous controls of master plates\012-----------------------------------------------------------------------------------\012\012delete from sample_components\012where  id in (select sc.id \012              from   sample_components as sc left join sample_component_materials as scm \012              on     scm.saco_id = sc.id \012              where  scm.saco_id is null\012              )\012;\012