-- -------\012-- Change attachment table names, they should no longer mention the experiment\012-- -------\012\012ALTER TABLE experiment_attachment_contents RENAME TO attachment_contents;\012ALTER TABLE experiment_attachments RENAME TO attachments;\012\012ALTER TABLE EXPERIMENT_ATTACHMENT_ID_SEQ RENAME TO ATTACHMENT_ID_SEQ;\012ALTER TABLE EXPERIMENT_ATTACHMENT_CONTENT_ID_SEQ RENAME TO ATTACHMENT_CONTENT_ID_SEQ;\012\012ALTER INDEX EXAT_EXPE_FK_I RENAME TO ATTA_EXPE_FK_I;\012ALTER INDEX EXAT_PERS_FK_I RENAME TO ATTA_PERS_FK_I;\012-- Index couldn't be renamed because it might not exist\012DROP INDEX IF EXISTS EXAT_EXAC_FK_I; \012CREATE INDEX ATTA_EXAC_FK_I ON ATTACHMENTS (EXAC_ID);\012\012-- it's not possible to rename constraints, we drop and create them with different names\012\012ALTER TABLE attachments\012\011DROP CONSTRAINT exat_cont_fk;\012ALTER TABLE attachments\012\011DROP CONSTRAINT exat_expe_fk;\012ALTER TABLE attachments\012\011DROP CONSTRAINT exat_pers_fk;\012ALTER TABLE attachments\012\011DROP CONSTRAINT exat_pk;\012\012ALTER TABLE attachments\012\011ADD CONSTRAINT atta_pk PRIMARY KEY (id);\012\012ALTER TABLE attachments\012\011ADD CONSTRAINT atta_cont_fk FOREIGN KEY (exac_id) REFERENCES attachment_contents(id);\012\012ALTER TABLE attachments\012\011ADD CONSTRAINT atta_expe_fk FOREIGN KEY (expe_id) REFERENCES experiments(id);\012\012ALTER TABLE attachments\012\011ADD CONSTRAINT atta_pers_fk FOREIGN KEY (pers_id_registerer) REFERENCES persons(id);\012\012-- -------\012-- Add an arc connection from attachment to project and sample tables\012-- -------\012\012ALTER TABLE attachments\012\011ADD COLUMN samp_id tech_id,\012\011ADD COLUMN proj_id tech_id,\012\011ALTER COLUMN expe_id DROP NOT NULL;\012\011\012ALTER TABLE attachments\012\011DROP CONSTRAINT exat_bk_uk;\012\011\012ALTER TABLE attachments\012\011ADD CONSTRAINT atta_arc_ck CHECK ((((((expe_id IS NOT NULL) AND (proj_id IS NULL)) AND (samp_id IS NULL)) OR (((expe_id IS NULL) AND (proj_id IS NOT NULL)) AND (samp_id IS NULL))) OR (((expe_id IS NULL) AND (proj_id IS NULL)) AND (samp_id IS NOT NULL))));\012\012ALTER TABLE attachments\012\011ADD CONSTRAINT atta_expe_bk_uk UNIQUE (expe_id, file_name, version);\012\012ALTER TABLE attachments\012\011ADD CONSTRAINT atta_proj_bk_uk UNIQUE (proj_id, file_name, version);\012\012ALTER TABLE attachments\012\011ADD CONSTRAINT atta_samp_bk_uk UNIQUE (samp_id, file_name, version);\012\012ALTER TABLE attachments\012\011ADD CONSTRAINT atta_proj_fk FOREIGN KEY (proj_id) REFERENCES projects(id);\012\012ALTER TABLE attachments\012\011ADD CONSTRAINT atta_samp_fk FOREIGN KEY (samp_id) REFERENCES samples(id);\012\012CREATE INDEX ATTA_SAMP_FK_I ON ATTACHMENTS (SAMP_ID);\012CREATE INDEX ATTA_PROJ_FK_I ON ATTACHMENTS (PROJ_ID);\012\012-- -------\012-- Add modification timestamp to project table to allow edition\012-- -------\012\012ALTER TABLE projects\012\011ADD COLUMN modification_timestamp TIME_STAMP DEFAULT CURRENT_TIMESTAMP;\012\011\012-- -------\012-- Add new datatypes\012-- -------\012\012insert into data_types\012(id\012 ,code\012 ,description)\012 values \012 (nextval('DATA_TYPE_ID_SEQ')\012 ,'HYPERLINK'\012 ,'Address of a web page'\012);\012\012insert into data_types\012(id\012 ,code\012 ,description)\012 values \012 (nextval('DATA_TYPE_ID_SEQ')\012 ,'MULTILINE_VARCHAR'\012 ,'Long text'\012);\012\012-- -------\012-- Added explicit lock Samples table in exclusive mode to prevent race condition\012-- -------\012\012CREATE OR REPLACE FUNCTION SAMPLE_CODE_UNIQUENESS_CHECK() RETURNS trigger AS $$\012DECLARE\012   counter  INTEGER;\012BEGIN\012\011LOCK TABLE samples IN EXCLUSIVE MODE;\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 samp_id_part_of is NULL 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 samp_id_part_of is NULL 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