-- -------\012-- add modification_timestamp field to allow edition with Hibernate\012-- -------\012\012ALTER TABLE samples\012\011ADD COLUMN modification_timestamp TIME_STAMP DEFAULT CURRENT_TIMESTAMP;\012\012ALTER TABLE experiments\012\011ADD COLUMN modification_timestamp TIME_STAMP DEFAULT CURRENT_TIMESTAMP;\012\012ALTER TABLE materials\012\011ADD COLUMN modification_timestamp TIME_STAMP DEFAULT CURRENT_TIMESTAMP;\012\012ALTER TABLE experiment_properties\012\011ADD COLUMN modification_timestamp TIME_STAMP DEFAULT CURRENT_TIMESTAMP;\012\012ALTER TABLE material_properties\012\011ADD COLUMN modification_timestamp TIME_STAMP DEFAULT CURRENT_TIMESTAMP;\012\012ALTER TABLE sample_properties\012\011ADD COLUMN modification_timestamp TIME_STAMP DEFAULT CURRENT_TIMESTAMP;\012\012-- -------\012-- add property data type which references materials\012-- -------\012\012ALTER TABLE property_types\012\011ADD COLUMN maty_prop_id tech_id;\012\012ALTER TABLE property_types\012\011ADD CONSTRAINT prty_maty_fk FOREIGN KEY (maty_prop_id) REFERENCES material_types(id);\012\012ALTER TABLE experiment_properties\012\011ADD COLUMN mate_prop_id tech_id;\012\012ALTER TABLE material_properties\012\011ADD COLUMN mate_prop_id tech_id;\012\012ALTER TABLE sample_properties\012\011ADD COLUMN mate_prop_id tech_id;\012\012ALTER TABLE experiment_properties\012\011ADD CONSTRAINT expr_mapr_fk FOREIGN KEY (mate_prop_id) REFERENCES materials(id);\012\012ALTER TABLE material_properties\012\011ADD CONSTRAINT mapr_mapr_fk FOREIGN KEY (mate_prop_id) REFERENCES materials(id);\012\012ALTER TABLE sample_properties\012\011ADD CONSTRAINT sapr_mapr_fk FOREIGN KEY (mate_prop_id) REFERENCES materials(id);\012\012insert into data_types\012(id\012 ,code\012 ,description)\012 values \012 (nextval('DATA_TYPE_ID_SEQ')\012 ,'MATERIAL'\012 ,'Reference to a material'\012);\012\012ALTER TABLE MATERIAL_PROPERTIES DROP CONSTRAINT MAPR_CK;\012ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_CK CHECK \012\011((VALUE IS NOT NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL) OR \012\011 (VALUE IS NULL AND CVTE_ID IS NOT NULL AND MATE_PROP_ID IS NULL) OR\012\011 (VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NOT NULL)\012\011);\012\012ALTER TABLE EXPERIMENT_PROPERTIES DROP CONSTRAINT EXPR_CK;\012ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_CK CHECK \012\011((VALUE IS NOT NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL) OR \012\011 (VALUE IS NULL AND CVTE_ID IS NOT NULL AND MATE_PROP_ID IS NULL) OR\012\011 (VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NOT NULL)\012\011);\012\012ALTER TABLE SAMPLE_PROPERTIES DROP CONSTRAINT SAPR_CK;\012ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_CK CHECK \012\011((VALUE IS NOT NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL) OR \012\011 (VALUE IS NULL AND CVTE_ID IS NOT NULL AND MATE_PROP_ID IS NULL) OR\012\011 (VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NOT NULL)\012\011);\012\012------------------------------------------------------------------------------------\012--  Purpose:  Create trigger MATERIAL/SAMPLE/EXPERIMENT _PROPERTY_WITH_MATERIAL_DATA_TYPE_CHECK\012--            It checks that if material property value is assigned to the entity,\012--\011\011\011\011\011\011then the material type is equal to the one described by property type.\012------------------------------------------------------------------------------------\012\012CREATE OR REPLACE FUNCTION MATERIAL_PROPERTY_WITH_MATERIAL_DATA_TYPE_CHECK() RETURNS trigger AS $$\012DECLARE\012   v_type_id  CODE;\012   v_type_id_prop  CODE;\012BEGIN\012   if NEW.mate_prop_id IS NOT NULL then\012\011\011\011-- find material type id of the property type \012\011\011\011select pt.maty_prop_id into v_type_id_prop \012\011\011\011  from material_type_property_types etpt, property_types pt \012\011\011\011 where NEW.mtpt_id = etpt.id AND etpt.prty_id = pt.id;\012\011\011\012\011\011\011if v_type_id_prop IS NOT NULL then\012\011\011\011\011-- find material type id of the material which consists the entity's property value\012\011\011\011\011select entity.maty_id into v_type_id \012\011\011\011\011  from materials entity\012\011\011\011\011 where NEW.mate_prop_id = entity.id;\012\011\011\011\011if v_type_id != v_type_id_prop then\012\011\011\011\011\011RAISE EXCEPTION 'Insert/Update of property value referencing material (id: %) failed, as referenced material type is different than expected (id %, expected id: %).', \012\011\011\011\011\011\011\011 NEW.mate_prop_id, v_type_id, v_type_id_prop;\012\011\011\011\011end if;\012\011\011\011end if;\012   end if;\012   RETURN NEW;\012END;\012$$ LANGUAGE 'plpgsql';\012\012CREATE TRIGGER MATERIAL_PROPERTY_WITH_MATERIAL_DATA_TYPE_CHECK BEFORE INSERT OR UPDATE ON material_properties\012    FOR EACH ROW EXECUTE PROCEDURE MATERIAL_PROPERTY_WITH_MATERIAL_DATA_TYPE_CHECK();\012    \012CREATE OR REPLACE FUNCTION SAMPLE_PROPERTY_WITH_MATERIAL_DATA_TYPE_CHECK() RETURNS trigger AS $$\012DECLARE\012   v_type_id  CODE;\012   v_type_id_prop  CODE;\012BEGIN\012   if NEW.mate_prop_id IS NOT NULL then\012\011\011\011-- find material type id of the property type \012\011\011\011select pt.maty_prop_id into v_type_id_prop \012\011\011\011  from sample_type_property_types etpt, property_types pt \012\011\011\011 where NEW.stpt_id = etpt.id AND etpt.prty_id = pt.id;\012\011\011\012\011\011\011if v_type_id_prop IS NOT NULL then\012\011\011\011\011-- find material type id of the material which consists the entity's property value\012\011\011\011\011select entity.maty_id into v_type_id \012\011\011\011\011  from materials entity\012\011\011\011\011 where NEW.mate_prop_id = entity.id;\012\011\011\011\011if v_type_id != v_type_id_prop then\012\011\011\011\011\011RAISE EXCEPTION 'Insert/Update of property value referencing material (id: %) failed, as referenced material type is different than expected (id %, expected id: %).', \012\011\011\011\011\011\011\011\011\011\011\011\011 NEW.mate_prop_id, v_type_id, v_type_id_prop;\012\011\011\011\011end if;\012\011\011\011end if;\012   end if;\012   RETURN NEW;\012END;\012$$ LANGUAGE 'plpgsql';\012\012CREATE TRIGGER SAMPLE_PROPERTY_WITH_MATERIAL_DATA_TYPE_CHECK BEFORE INSERT OR UPDATE ON sample_properties\012    FOR EACH ROW EXECUTE PROCEDURE SAMPLE_PROPERTY_WITH_MATERIAL_DATA_TYPE_CHECK();\012    \012CREATE OR REPLACE FUNCTION EXPERIMENT_PROPERTY_WITH_MATERIAL_DATA_TYPE_CHECK() RETURNS trigger AS $$\012DECLARE\012   v_type_id  CODE;\012   v_type_id_prop  CODE;\012BEGIN\012   if NEW.mate_prop_id IS NOT NULL then\012\011\011\011-- find material type id of the property type \012\011\011\011select pt.maty_prop_id into v_type_id_prop \012\011\011\011  from experiment_type_property_types etpt, property_types pt \012\011\011\011 where NEW.etpt_id = etpt.id AND etpt.prty_id = pt.id;\012\011\011\012\011\011\011if v_type_id_prop IS NOT NULL then\012\011\011\011\011-- find material type id of the material which consists the entity's property value\012\011\011\011\011select entity.maty_id into v_type_id \012\011\011\011\011  from materials entity\012\011\011\011\011 where NEW.mate_prop_id = entity.id;\012\011\011\011\011if v_type_id != v_type_id_prop then\012\011\011\011\011\011RAISE EXCEPTION 'Insert/Update of property value referencing material (id: %) failed, as referenced material type is different than expected (id %, expected id: %).', \012\011\011\011\011\011\011\011\011\011\011\011\011 NEW.mate_prop_id, v_type_id, v_type_id_prop;\012\011\011\011\011end if;\012\011\011\011end if;\012   end if;\012   RETURN NEW;\012END;\012$$ LANGUAGE 'plpgsql';\012\012CREATE TRIGGER EXPERIMENT_PROPERTY_WITH_MATERIAL_DATA_TYPE_CHECK BEFORE INSERT OR UPDATE ON experiment_properties\012    FOR EACH ROW EXECUTE PROCEDURE EXPERIMENT_PROPERTY_WITH_MATERIAL_DATA_TYPE_CHECK();\012       \012-- -------\012-- remove the reminescents of the OBSERVABLE_TYPE\012-- -------\012\012ALTER TABLE data\012\011DROP CONSTRAINT data_obty_fk;\012\012ALTER TABLE data_set_types\012\011DROP CONSTRAINT obty_pk;\012\012ALTER TABLE data_set_types\012\011ADD CONSTRAINT dsty_pk PRIMARY KEY (id);\012\012ALTER TABLE data\012\011ADD CONSTRAINT data_dsty_fk FOREIGN KEY (dsty_id) REFERENCES data_set_types(id);\012\012ALTER TABLE data_set_types\012\011DROP CONSTRAINT obty_bk_uk;\012\012ALTER TABLE data_set_types\012\011DROP CONSTRAINT obty_dbin_fk;\012\012ALTER TABLE data_set_types\012\011ADD CONSTRAINT dsty_bk_uk UNIQUE (code, dbin_id);\012\012ALTER TABLE data_set_types\012\011ADD CONSTRAINT dsty_dbin_fk FOREIGN KEY (dbin_id) REFERENCES database_instances(id);\012\012DROP INDEX data_obty_fk_i;\012\012CREATE INDEX data_dsty_fk_i ON data(dsty_id);\012