-- JAVA ch.systemsx.cisd.openbis.generic.server.dataaccess.migration.MigrationStepFrom022To023\012\012-- Add column GLOBAL_CODE to the DATABASE_INSTANCES table.\012-- Set it's value from the code, it will be updated later from java.\012\012ALTER TABLE DATABASE_INSTANCES ADD COLUMN GLOBAL_CODE CODE;\012update DATABASE_INSTANCES set GLOBAL_CODE = CODE;\012ALTER TABLE DATABASE_INSTANCES ADD CONSTRAINT DBIN_GLOBAL_CODE_UK UNIQUE(GLOBAL_CODE);\012ALTER TABLE DATABASE_INSTANCES alter column GLOBAL_CODE set NOT NULL;\012\012-- Add column the new FK column SAMP_ID_PART_OF of the SAMPLES table\012\012ALTER TABLE SAMPLES ADD COLUMN SAMP_ID_PART_OF TECH_ID;\012\012-- Drop constraint SAMP_BK_DBIN_UK of SAMPLES table\012\012ALTER TABLE SAMPLES DROP CONSTRAINT SAMP_BK_DBIN_UK;\012\012-- Drop constraint SAMP_BK_GROU_UK of SAMPLES table\012\012ALTER TABLE SAMPLES DROP CONSTRAINT SAMP_BK_GROU_UK;\012\012-- Add FK PART_OF to the SAMPLES table\012\012ALTER TABLE SAMPLES ADD CONSTRAINT SAMP_SAMP_FK_PART_OF FOREIGN KEY (SAMP_ID_PART_OF) REFERENCES SAMPLES(ID);\012\012-- Add Index for the FK Part Of of the SAMPLES table\012CREATE INDEX SAMP_SAMP_FK_I_PART_OF ON SAMPLES (SAMP_ID_PART_OF);\012\012-- Create the SAMPLE_MATERIAL_BATCHES table\012\012CREATE TABLE SAMPLE_MATERIAL_BATCHES (ID TECH_ID NOT NULL,SAMP_ID TECH_ID NOT NULL,MABA_ID TECH_ID NOT NULL);\012ALTER TABLE SAMPLE_MATERIAL_BATCHES ADD CONSTRAINT SAMB_PK PRIMARY KEY(ID);\012\012-- Create the sequence for the SAMPLE_MATERIAL_BATCHES table\012\012CREATE SEQUENCE SAMPLE_MATERIAL_BATCH_ID_SEQ;\012\012\012--  Create WELL as a new SAMPLE_TYPE\012\012insert into sample_types\012(id\012,code\012,description\012,dbin_id)\012values \012(nextval('SAMPLE_TYPE_ID_SEQ')\012,'WELL'\012,'Plate Well'\012,(select id from database_instances where is_original_source = true)\012);\012\012\012-- Transfer data from the SAMPLE_COMPONENTS table to the SAMPLES table\012\012INSERT INTO samples(\012              id\012            , code\012            , samp_id_top\012            , samp_id_generated_from\012            , saty_id\012            , pers_id_registerer\012            , inva_id\012            , samp_id_control_layout\012            , dbin_id\012            , grou_id\012            , samp_id_part_of)\012SELECT        nextval('sample_id_seq')\012            , saco.code\012            , null\012            , null\012            , (select id from sample_types where code = 'WELL')\012            , (select id from persons where user_id ='system')\012            , samp.inva_id\012            , null\012            , samp.dbin_id\012            , samp.grou_id\012            , saco.samp_id\012FROM samples samp INNER JOIN sample_components saco\012     ON   samp.id = saco.samp_id;\012\012\012-- Fill the SAMPLE_MATERIAL_BATCHES table via SAMPLE_C0MPONENT_MATERIALS, SAMPLE_C0MPONENTS, SAMPLES and MATERIAL_BATCHES tables\012\012INSERT INTO sample_material_batches(\012              id\012            , samp_id\012            , maba_id)\012SELECT        nextval('sample_material_batch_id_seq')\012            , well.id\012            , scma.maba_id\012FROM sample_component_materials scma \012   INNER JOIN material_batches maba\012     ON scma.maba_id = maba.id\012        INNER JOIN sample_components saco\012           ON scma.saco_id = saco.id\012              INNER JOIN samples well\012                 ON saco.code = well.code and saco.samp_id = well.samp_id_part_of\012;\012\012\012-- Drop the retired Database Objects\012\012DROP TABLE SAMPLE_COMPONENT_MATERIALS;\012DROP TABLE SAMPLE_COMPONENTS;\012\012DROP SEQUENCE SCMA_ID_SEQ;\012DROP SEQUENCE SAMPLE_COMPONENT_ID_SEQ;\012\012------------------------------------------------------------------------------------\012--  Purpose:  Create trigger SAMPLE_CODE_UNIQUENESS_CHECK \012------------------------------------------------------------------------------------\012\012CREATE OR REPLACE FUNCTION SAMPLE_CODE_UNIQUENESS_CHECK() RETURNS trigger AS $$\012DECLARE\012   counter  INTEGER;\012BEGIN\012\011IF (NEW.samp_id_part_of is NULL) THEN\012\011\011IF (NEW.dbin_id is not NULL) THEN\012\011\011\011SELECT count(*) into counter FROM samples \012\011\011\011\011where id != NEW.id and code = NEW.code and dbin_id = NEW.dbin_id;\012\011\011\011IF (counter > 0) THEN\012\011\011\011\011RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because database instance sample with the same code already exists.', NEW.code;\012\011\011\011END IF;\012\011\011ELSIF (NEW.grou_id is not NULL) THEN\012\011\011\011SELECT count(*) into counter FROM samples \012\011\011\011\011where id != NEW.id and code = NEW.code and grou_id = NEW.grou_id;\012\011\011\011IF (counter > 0) THEN\012\011\011\011\011RAISE EXCEPTION 'Insert/Update of Sample (Code: %) failed because group sample with the same code already exists.', NEW.code;\012\011\011\011END IF;\012\011\011END IF;\012        ELSE\012\011\011IF (NEW.dbin_id is not NULL) THEN\012\011\011\011SELECT count(*) into counter FROM samples \012\011\011\011\011where id != NEW.id and code = NEW.code and samp_id_part_of = NEW.samp_id_part_of and dbin_id = NEW.dbin_id;\012\011\011\011IF (counter > 0) THEN\012\011\011\011\011RAISE 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\011\011\011END IF;\012\011\011ELSIF (NEW.grou_id is not NULL) THEN\012\011\011\011SELECT count(*) into counter FROM samples \012\011\011\011\011where id != NEW.id and code = NEW.code and samp_id_part_of = NEW.samp_id_part_of and grou_id = NEW.grou_id;\012\011\011\011IF (counter > 0) THEN\012\011\011\011\011RAISE 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\011\011\011END IF;\012\011\011END IF;\012        END IF;   \012   RETURN NEW;\012END;\012$$ LANGUAGE 'plpgsql';\012\012CREATE TRIGGER SAMPLE_CODE_UNIQUENESS_CHECK BEFORE INSERT OR UPDATE ON SAMPLES\012    FOR EACH ROW EXECUTE PROCEDURE SAMPLE_CODE_UNIQUENESS_CHECK();\012