-- Migration from 059 to 060\012\012------------------------------------------------------------------------------------\012--  Drop trigger on queries.entity_type_code column to enable storing a regexp there.\012------------------------------------------------------------------------------------\012\012DROP TRIGGER query_entity_type_code_check ON queries;\012DROP FUNCTION query_entity_type_code_check();\012\012------------------------------------------------------------------------------------\012-- Dynamic properties\012------------------------------------------------------------------------------------\012\012-- Create SCRIPTS table\012\012CREATE 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);\012CREATE SEQUENCE SCRIPT_ID_SEQ;\012ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_PK PRIMARY KEY(ID);\012ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_UK UNIQUE(NAME,DBIN_ID);\012ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);\012CREATE INDEX SCRIPT_PERS_FK_I ON SCRIPTS (PERS_ID_REGISTERER);\012CREATE INDEX SCRIPT_DBIN_FK_I ON SCRIPTS (DBIN_ID);\012\012-- Add IS_DYNAMIC column to *_PROPERTY_TYPES tables \012\012ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD COLUMN IS_DYNAMIC BOOLEAN NOT NULL DEFAULT FALSE;\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD COLUMN IS_DYNAMIC BOOLEAN NOT NULL DEFAULT FALSE;\012ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD COLUMN IS_DYNAMIC BOOLEAN NOT NULL DEFAULT FALSE;\012ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD COLUMN IS_DYNAMIC BOOLEAN NOT NULL DEFAULT FALSE;\012\012-- Add SCRIPT_ID column to *_PROPERTY_TYPES tables \012\012ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD COLUMN SCRIPT_ID TECH_ID;\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD COLUMN SCRIPT_ID TECH_ID;\012ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD COLUMN SCRIPT_ID TECH_ID;\012ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD COLUMN SCRIPT_ID TECH_ID;\012\012-- Make SCRIPT_ID reference SCRIPTS\012\012ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);\012ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);\012ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);\012ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD CONSTRAINT DSTPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);\012\012-- Check SCRIPT_ID is filled when IS_DYNAMIC is TRUE\012\012ALTER 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));\012ALTER 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));\012ALTER 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));\012ALTER 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));\012\012