-- remove the bug from previous db version\012ALTER TABLE ROLE_ASSIGNMENTS DROP CONSTRAINT ROAS_BK_UK;\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_GROUP_BK_UK UNIQUE(PERS_ID_GRANTEE,ROLE_CODE,GROU_ID);\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_INSTANCE_BK_UK UNIQUE(PERS_ID_GRANTEE,ROLE_CODE,DBIN_ID);\012\012-- add display properties to sample type\012\012ALTER TABLE SAMPLE_TYPES ADD COLUMN IS_LISTABLE BOOLEAN_CHAR NOT NULL DEFAULT 'T';\012ALTER TABLE SAMPLE_TYPES ADD COLUMN GENERATED_FROM_DEPTH INTEGER NOT NULL DEFAULT 0;\012ALTER TABLE SAMPLE_TYPES ADD COLUMN PART_OF_DEPTH INTEGER NOT NULL DEFAULT 0;\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD COLUMN IS_DISPLAYED BOOLEAN_CHAR NOT NULL DEFAULT 'T';\012\012update sample_types\012set generated_from_depth = 1\012where code = 'DILUTION_PLATE';\012\012update sample_types\012set generated_from_depth = 2\012where code = 'CELL_PLATE';\012\012update sample_types\012set generated_from_depth = 3\012where code = 'REINFECT_PLATE';\012\012update sample_types\012set is_listable = 'F', generated_from_depth = 0, part_of_depth = 1\012where code = 'WELL';\012\012----------------------------------------------------\012-- Unify the schema of database v018 migrated to the current version with the original schema.\012-- This code has been generated by the external tool.\012-- Note that sometimes the changes are not at all significant, but they let us unify the schemas.\012----------------------------------------------------\012\012DROP FUNCTION move_exp_samples_to_group();\012\012CREATE OR REPLACE FUNCTION controlled_vocabulary_check() RETURNS "trigger"\012    AS $$\012DECLARE\012   v_code  CODE;\012BEGIN\012\012   select code into v_code from data_types where id = NEW.daty_id;\012\012   -- Check if the data is of type "CONTROLLEDVOCABULARY"\012   if v_code = 'CONTROLLEDVOCABULARY' then\012      if NEW.covo_id IS NULL then\012         RAISE EXCEPTION 'Insert/Update of Property Type (Code: %) failed, as its Data Type is CONTROLLEDVOCABULARY, but it is not linked to a Controlled Vocabulary.', NEW.code;\012      end if;\012   end if;\012\012   RETURN NEW;\012\012END;\012$$\012    LANGUAGE plpgsql;\012\012\012CREATE OR REPLACE FUNCTION external_data_storage_format_check() RETURNS "trigger"\012    AS $$\012DECLARE\012   v_covo_code  CODE;\012   data_code CODE;\012BEGIN\012\012   select code into v_covo_code from controlled_vocabularies\012      where is_internal_namespace = true and \012         id = (select covo_id from controlled_vocabulary_terms where id = NEW.cvte_id_stor_fmt);\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      select code into data_code from data where id = NEW.data_id; \012      RAISE EXCEPTION 'Insert/Update of Data (Code: %) failed, as its Storage Format is %, but is required to be STORAGE_FORMAT.', data_code, v_covo_code;\012   end if;\012\012   RETURN NEW;\012\012END;\012$$\012    LANGUAGE plpgsql;\012\012\012CREATE SEQUENCE data_set_relationship_id_seq\012        START WITH 1\012        INCREMENT BY 1\012        NO MAXVALUE\012        NO MINVALUE\012        CACHE 1;\012\012ALTER SEQUENCE group_id_seq\012        RESTART WITH 1;\012\012ALTER SEQUENCE material_batch_id_seq\012        RESTART WITH 1;\012\012ALTER SEQUENCE material_id_seq\012        RESTART WITH 1;\012\012ALTER SEQUENCE material_property_id_seq\012        RESTART WITH 1;\012\012ALTER SEQUENCE project_id_seq\012        RESTART WITH 1;\012\012ALTER SEQUENCE sample_id_seq\012        RESTART WITH 1;\012\012ALTER SEQUENCE sample_property_id_seq\012        RESTART WITH 1;\012\012ALTER TABLE controlled_vocabularies\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE controlled_vocabulary_terms\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE data\012        ALTER COLUMN code DROP NOT NULL,\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL,\012        ALTER COLUMN is_placeholder SET DEFAULT false,\012        ALTER COLUMN is_placeholder DROP NOT NULL;\012\012ALTER TABLE experiment_attachments\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE experiment_properties\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE experiment_type_property_types\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE experiments\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE invalidations\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE material_batches\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE material_properties\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE material_type_property_types\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE materials\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE procedures\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE property_types\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE sample_properties\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE sample_type_property_types\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE samples\012        ALTER COLUMN registration_timestamp SET DEFAULT now(),\012        ALTER COLUMN registration_timestamp SET NOT NULL;\012\012ALTER TABLE data_set_relationships\012        DROP CONSTRAINT dsre_pk;\012\012ALTER TABLE sample_inputs\012        DROP CONSTRAINT sain_pk;\012\012ALTER TABLE sample_inputs\012        ADD CONSTRAINT sain_pk PRIMARY KEY (proc_id, samp_id);\012\012ALTER TABLE data\012        DROP CONSTRAINT data_proc_fk;\012\012ALTER TABLE data\012        DROP CONSTRAINT data_samp_derived_from_fk;\012\012ALTER TABLE data\012        DROP CONSTRAINT data_samp_fk;\012\012ALTER TABLE data\012        ADD CONSTRAINT data_proc_produced_by_fk FOREIGN KEY (proc_id_produced_by) REFERENCES procedures(id);\012\012ALTER TABLE data\012        ADD CONSTRAINT data_samp_fk_acquired_from FOREIGN KEY (samp_id_acquired_from) REFERENCES samples(id);\012\012ALTER TABLE data\012        ADD CONSTRAINT data_samp_fk_derived_from FOREIGN KEY (samp_id_derived_from) REFERENCES samples(id);\012\012ALTER TABLE data_set_relationships\012        ADD CONSTRAINT dsre_bk_uk UNIQUE (data_id_child, data_id_parent);\012\012-- There was a bug in migration from db 7 to 18 - the constraint was not created. So we create it if it does not exist.\012create function add_mapr_bk_uk_constraint() returns void AS $$\012begin\012   perform *\012     FROM information_schema.table_constraints WHERE constraint_name='mapr_bk_uk';\012   if not found\012   then\012\011\011\011ALTER TABLE material_properties\012        ADD CONSTRAINT mapr_bk_uk UNIQUE (mate_id, mtpt_id);\012   end if;\012end;\012$$ language 'plpgsql';\012select add_mapr_bk_uk_constraint();\012drop function add_mapr_bk_uk_constraint();\012\012-- There was a bug in migration from db 7 to 18 - the constraint was not dropped. So we drop it now 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\011\011\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\012ALTER TABLE persons\012        ADD CONSTRAINT pers_pers_fk FOREIGN KEY (pers_id_registerer) REFERENCES persons(id);\012\012ALTER TABLE sample_material_batches\012        ADD CONSTRAINT samb_bk_uk UNIQUE (maba_id, samp_id);\012\012ALTER TABLE sample_material_batches\012        ADD CONSTRAINT samb_maba_fk FOREIGN KEY (maba_id) REFERENCES material_batches(id);\012\012ALTER TABLE sample_material_batches\012        ADD CONSTRAINT samb_samp_fk FOREIGN KEY (samp_id) REFERENCES samples(id);\012\012CREATE INDEX samb_maba_fk_i ON sample_material_batches USING btree (maba_id);\012\012CREATE INDEX samb_samp_fk_i ON sample_material_batches USING btree (samp_id);\012\012----------------------------------------------------\012-- END Unify the schema\012----------------------------------------------------\012