-- Migration from 059 to 060

------------------------------------------------------------------------------------
--  Drop trigger on queries.entity_type_code column to enable storing a regexp there.
------------------------------------------------------------------------------------

DROP TRIGGER query_entity_type_code_check ON queries;
DROP FUNCTION query_entity_type_code_check();

------------------------------------------------------------------------------------
-- Dynamic properties
------------------------------------------------------------------------------------

-- Create SCRIPTS table

CREATE TABLE SCRIPTS (ID TECH_ID NOT NULL,DBIN_ID TECH_ID NOT NULL,NAME VARCHAR(200) NOT NULL,DESCRIPTION DESCRIPTION_2000,SCRIPT TEXT_VALUE NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL);
CREATE SEQUENCE SCRIPT_ID_SEQ;
ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_PK PRIMARY KEY(ID);
ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_UK UNIQUE(NAME,DBIN_ID);
ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);
ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);
CREATE INDEX SCRIPT_PERS_FK_I ON SCRIPTS (PERS_ID_REGISTERER);
CREATE INDEX SCRIPT_DBIN_FK_I ON SCRIPTS (DBIN_ID);

-- Add IS_DYNAMIC column to *_PROPERTY_TYPES tables 

ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD COLUMN IS_DYNAMIC BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD COLUMN IS_DYNAMIC BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD COLUMN IS_DYNAMIC BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD COLUMN IS_DYNAMIC BOOLEAN NOT NULL DEFAULT FALSE;

-- Add SCRIPT_ID column to *_PROPERTY_TYPES tables 

ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD COLUMN SCRIPT_ID TECH_ID;
ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD COLUMN SCRIPT_ID TECH_ID;
ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD COLUMN SCRIPT_ID TECH_ID;
ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD COLUMN SCRIPT_ID TECH_ID;

-- Make SCRIPT_ID reference SCRIPTS

ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);
ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);
ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);
ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD CONSTRAINT DSTPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);

-- Check SCRIPT_ID is filled when IS_DYNAMIC is TRUE

ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_CK CHECK ((IS_DYNAMIC IS TRUE AND SCRIPT_ID IS NOT NULL) OR (IS_DYNAMIC IS FALSE AND SCRIPT_ID IS NULL));
ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_CK CHECK ((IS_DYNAMIC IS TRUE AND SCRIPT_ID IS NOT NULL) OR (IS_DYNAMIC IS FALSE AND SCRIPT_ID IS NULL));
ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_CK CHECK ((IS_DYNAMIC IS TRUE AND SCRIPT_ID IS NOT NULL) OR (IS_DYNAMIC IS FALSE AND SCRIPT_ID IS NULL));
ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD CONSTRAINT DSTPT_CK CHECK ((IS_DYNAMIC IS TRUE AND SCRIPT_ID IS NOT NULL) OR (IS_DYNAMIC IS FALSE AND SCRIPT_ID IS NULL));

