-- -------\012-- Modify MATERIAL_BATCHES\012-- -------\012\012ALTER TABLE material_batches\012    DROP COLUMN proc_id;\012\012-- -------\012-- Modify SAMPLES\012-- -------\012\012ALTER TABLE samples\012    ADD COLUMN expe_id tech_id;\012\012ALTER TABLE samples\012    ADD CONSTRAINT samp_expe_fk FOREIGN KEY (expe_id) REFERENCES experiments(id);\012\012CREATE INDEX samp_expe_fk_i ON samples USING btree (expe_id);\012\012-- relink samples directly to experiments\012\012UPDATE samples\012SET expe_id = (SELECT e.id FROM experiments e, procedures p, sample_inputs si \012               WHERE si.samp_id = samples.id AND si.proc_id = p.id AND p.expe_id = e.id AND e.inva_id IS NULL);\012\012\012-- -------\012-- Modify DATA\012-- -------\012\012ALTER TABLE data\012    ADD COLUMN expe_id tech_id;\012\012ALTER TABLE data\012    ADD CONSTRAINT data_expe_fk FOREIGN KEY (expe_id) REFERENCES experiments(id);\012\012CREATE INDEX data_expe_fk_i ON data USING btree (expe_id);\012\012UPDATE data\012SET expe_id = (SELECT e.id FROM experiments e, procedures p \012               WHERE data.proc_id_produced_by = p.id AND p.expe_id = e.id);\012\012ALTER TABLE data\012    ALTER COLUMN expe_id SET NOT NULL;\012\012ALTER TABLE data\012    DROP COLUMN proc_id_produced_by;\012\012-- -------\012-- Drop PROCEDURES, PROCEDURE_TYPES, and SAMPLE_INPUTS\012-- -------\012\012DROP TABLE sample_inputs;\012DROP TABLE procedures;\012DROP TABLE procedure_types;\012\012DROP SEQUENCE procedure_id_seq;\012DROP SEQUENCE procedure_type_id_seq;\012\012-- -------\012-- Add CODE_SEQ\012-- -------\012\012CREATE SEQUENCE CODE_SEQ;\012\012-- -------\012-- Add new columns to DATA_STORES\012-- -------\012\012ALTER TABLE data_stores\012    ADD COLUMN remote_url character varying(250),\012    ADD COLUMN session_token character varying(50),\012    ADD COLUMN modification_timestamp time_stamp DEFAULT now();\012\012UPDATE data_stores\012SET remote_url = '', session_token='';\012\012ALTER TABLE data_stores\012    ALTER COLUMN remote_url SET NOT NULL,\012    ALTER COLUMN session_token SET NOT NULL;\012\012-- -------\012-- Drop foreign keys onto DATA_STORES\012-- -------\012\012ALTER TABLE database_instances\012    DROP COLUMN dast_id;\012\012ALTER TABLE groups\012    DROP COLUMN dast_id;\012\012ALTER TABLE projects\012    DROP COLUMN dast_id;\012\012ALTER TABLE experiments\012    DROP COLUMN dast_id;\012\012-- -------\012-- Add foreign key from DATA onto DATA_STORES\012-- -------\012\012ALTER TABLE data\012    ADD COLUMN dast_id tech_id;\012\012ALTER TABLE data\012    ADD CONSTRAINT data_dast_fk FOREIGN KEY (dast_id) REFERENCES data_stores(id);\012\012UPDATE data\012SET dast_id = (select id from data_stores where code = 'STANDARD');\012\012ALTER TABLE data\012    ALTER COLUMN dast_id SET NOT NULL;\012\012\012------------------------------------------------------------------------------------\012--  Purpose:  Replace trigger SAMPLE_CODE_UNIQUENESS_CHECK \012------------------------------------------------------------------------------------\012\012CREATE OR REPLACE FUNCTION SAMPLE_CODE_UNIQUENESS_CHECK() RETURNS trigger AS $$\012DECLARE\012   counter  INTEGER;\012BEGIN\012    LOCK TABLE samples IN EXCLUSIVE MODE;\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\012------------------------------------------------------------------------------------\012--  Purpose:  allow longer user ids \012------------------------------------------------------------------------------------\012\012alter table persons alter column user_id type varchar(50);\012drop domain user_id;\012create domain user_id as varchar(50);\012alter table persons alter column user_id type user_id;\012\012\012