-- Create FILTERS table\012\012CREATE TABLE FILTERS (ID TECH_ID NOT NULL, DBIN_ID TECH_ID NOT NULL, NAME VARCHAR(200) NOT NULL, DESCRIPTION DESCRIPTION_1000,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, PERS_ID_REGISTERER TECH_ID NOT NULL, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, EXPRESSION VARCHAR(2000) NOT NULL, IS_PUBLIC BOOLEAN NOT NULL, GRID_ID VARCHAR(200) NOT NULL);\012CREATE SEQUENCE FILTER_ID_SEQ;\012ALTER TABLE FILTERS ADD CONSTRAINT FILT_PK PRIMARY KEY(ID);\012ALTER TABLE FILTERS ADD CONSTRAINT FILT_BK_UK UNIQUE(NAME, DBIN_ID, GRID_ID);\012ALTER TABLE FILTERS ADD CONSTRAINT FILT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);\012ALTER TABLE FILTERS ADD CONSTRAINT FILT_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012CREATE INDEX FILT_PERS_FK_I ON FILTERS (PERS_ID_REGISTERER);\012CREATE INDEX FILT_DBIN_FK_I ON FILTERS (DBIN_ID);\012GRANT SELECT ON SEQUENCE filter_id_seq TO GROUP OPENBIS_READONLY;\012GRANT SELECT ON TABLE filters TO GROUP OPENBIS_READONLY;\012\012-- Add missing foreign key to role assignments authorization group\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_AG_FK_GRANTEE FOREIGN KEY (AG_ID_GRANTEE) REFERENCES AUTHORIZATION_GROUPS(ID);\012\012--------------------------------------------------------------------------------------------------\012-- Modify dataset connections:\012-- 1. connection with sample shouldn't be mandatory any more\012-- 2. introduce an arc condition that dataset cannot be connected with a sample and a parent dataset at the same time\012--------------------------------------------------------------------------------------------------\012\012-- Weaken data-sample connection constraint - allow dataset to have no connection with sample\012ALTER TABLE data ALTER COLUMN samp_id DROP NOT NULL;\012\012-- Remove data-sample connection for datasets that have a parent\012\012UPDATE data \012   SET samp_id = NULL \012 WHERE id IN (SELECT DISTINCT data_id_child FROM data_set_relationships);\012\012-- With PostgreSQL 8.4 one can check that before migration there were no cycles in dataset relationships:\012--\012-- WITH RECURSIVE data_set_parents(id, parent_ids, cycle) AS (\012--         SELECT  r.data_id_child AS id, \012--                 ARRAY[CAST (r.data_id_parent AS bigint)] AS parent_ids, \012--                 false AS cycle \012--         FROM data_set_relationships r\012--     UNION ALL\012--         SELECT  r.data_id_child,\012--                 CAST (r.data_id_parent AS bigint) || p.parent_ids,\012--                 r.data_id_child = ANY(p.parent_ids)\012--         FROM data_set_relationships r, data_set_parents p\012--         WHERE r.data_id_parent = p.id AND NOT cycle\012-- )\012-- SELECT count(*) AS cycles FROM data_set_parents WHERE cycle = true;\012\012---------------------------------------------------------------------------------------------------\012--  Purpose:  Create DEFERRED triggers:\012--            * check_dataset_relationships_on_data_table_modification,\012--            * check_dataset_relationships_on_relationships_table_modification.\012--            They check that after all modifications of database (just before commit) \012--            if 'data'/'data_set_relationships' tables are among modified tables \012--            dataset is not connected with a sample and a parent dataset at the same time.\012--            This connections are held in two different tables so simple immediate trigger \012--            with arc check cannot be used and we need two deferred triggers.\012----------------------------------------------------------------------------------------------------\012\012-- trigger for 'data' table\012\012CREATE OR REPLACE FUNCTION check_dataset_relationships_on_data_table_modification() RETURNS trigger AS $$\012DECLARE\012\011counter\011INTEGER;\012BEGIN\012\011-- if there is a connection with a Sample there should not be any connection with a parent Data Set\012\011IF (NEW.samp_id IS NOT NULL) THEN\012\011\011-- count number of parents\012\011\011SELECT count(*) INTO counter \012\011\011\011FROM data_set_relationships \012\011\011\011WHERE data_id_child = NEW.id;\012\011\011IF (counter > 0) THEN\012\011\011\011RAISE EXCEPTION 'Insert/Update of Data Set (Code: %) failed because it cannot be connected with a Sample and a parent Data Set at the same time.', NEW.code;\012\011\011END IF;\012\011END IF;\012  RETURN NEW;\012END;\012$$ LANGUAGE 'plpgsql';\012\012CREATE CONSTRAINT TRIGGER check_dataset_relationships_on_data_table_modification \012  AFTER INSERT OR UPDATE ON data\012\011DEFERRABLE INITIALLY DEFERRED\012\011FOR EACH ROW \012\011EXECUTE PROCEDURE check_dataset_relationships_on_data_table_modification();\012\012-- trigger for 'data_set_relationships'\012\012CREATE OR REPLACE FUNCTION check_dataset_relationships_on_relationships_table_modification() RETURNS trigger AS $$\012DECLARE\012\011counter\011INTEGER;\012\011sample_id\011TECH_ID;\012\011data_code\011CODE;\012BEGIN\012\011-- child will have a parent added so it should not be connected with any sample\012\011SELECT samp_id, code INTO sample_id, data_code \012\011\011FROM data \012\011\011WHERE id = NEW.data_id_child;\012\011IF (sample_id IS NOT NULL) THEN\012\011\011RAISE EXCEPTION 'Insert/Update of Data Set (Code: %) failed because it cannot be connected to a Sample and to a parent Data Set at the same time.', data_code;\012\011END IF;\012\011RETURN NEW;\012END;\012$$ LANGUAGE 'plpgsql';\012  \012CREATE CONSTRAINT TRIGGER check_dataset_relationships_on_relationships_table_modification \012  AFTER INSERT OR UPDATE ON data_set_relationships\012\011DEFERRABLE INITIALLY DEFERRED\012\011FOR EACH ROW \012\011EXECUTE PROCEDURE check_dataset_relationships_on_relationships_table_modification();\012