-- JAVA ch.systemsx.cisd.openbis.generic.server.dataaccess.migration.MigrationStepFrom023To024\012\012-- Drop constraint DBIN_GLOBAL_CODE_UK of DATABASE_INSTANCES table\012\012ALTER TABLE DATABASE_INSTANCES DROP CONSTRAINT DBIN_GLOBAL_CODE_UK;\012\012-- Name changes\012\012ALTER TABLE DATABASE_INSTANCES RENAME COLUMN GLOBAL_CODE TO UUID;\012\012-- Add constraint DBIN_UUID_UK to the DATABASE_INSTANCES table\012\012ALTER TABLE DATABASE_INSTANCES ADD CONSTRAINT DBIN_UUID_UK UNIQUE(UUID);\012\012-- Drop column in EXPERIMENTS\012\012ALTER TABLE EXPERIMENTS DROP COLUMN DESCRIPTION;\012\012-- Add index to SAMPLES table\012\012CREATE INDEX SAMP_CODE_I ON SAMPLES (CODE);\012\012-- Bug fix in trigger function SAMPLE_CODE_UNIQUENESS_CHECK()\012\012CREATE OR REPLACE FUNCTION SAMPLE_CODE_UNIQUENESS_CHECK() RETURNS trigger AS $$\012DECLARE\012   counter  INTEGER;\012BEGIN\012    IF (NEW.samp_id_part_of is NULL) THEN\012        IF (NEW.dbin_id is not NULL) THEN\012            SELECT count(*) into counter FROM samples \012                where id != NEW.id and code = NEW.code and samp_id_part_of is NULL and dbin_id = NEW.dbin_id;\012            IF (counter > 0) THEN\012                RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because database instance sample with the same code already exists.', NEW.code;\012            END IF;\012        ELSIF (NEW.grou_id is not NULL) THEN\012            SELECT count(*) into counter FROM samples \012                where id != NEW.id and code = NEW.code and samp_id_part_of is NULL and grou_id = NEW.grou_id;\012            IF (counter > 0) THEN\012                RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because group sample with the same code already exists.', NEW.code;\012            END IF;\012        END IF;\012        ELSE\012        IF (NEW.dbin_id is not NULL) THEN\012            SELECT count(*) into counter FROM samples \012                where id != NEW.id and code = NEW.code and samp_id_part_of = NEW.samp_id_part_of and dbin_id = NEW.dbin_id;\012            IF (counter > 0) THEN\012                RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because database instance sample with the same code and being the part of the same parent already exists.', NEW.code;\012            END IF;\012        ELSIF (NEW.grou_id is not NULL) THEN\012            SELECT count(*) into counter FROM samples \012                where id != NEW.id and code = NEW.code and samp_id_part_of = NEW.samp_id_part_of and grou_id = NEW.grou_id;\012            IF (counter > 0) THEN\012                RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because group sample with the same code and being the part of the same parent already exists.', NEW.code;\012            END IF;\012        END IF;\012        END IF;   \012   RETURN NEW;\012END;\012$$ LANGUAGE 'plpgsql';\012