-- Migration from 053 to 054\012\012\012-- Add RELATIONSHIP_TYPES table\012CREATE TABLE relationship_types (id TECH_ID NOT NULL, code CODE NOT NULL, label COLUMN_LABEL, parent_label COLUMN_LABEL, child_label COLUMN_LABEL, description DESCRIPTION_2000, registration_timestamp TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, pers_id_registerer TECH_ID NOT NULL, is_managed_internally BOOLEAN_CHAR NOT NULL DEFAULT 'F', is_internal_namespace BOOLEAN_CHAR NOT NULL DEFAULT 'F', dbin_id TECH_ID NOT NULL);\012\012-- Add SAMPLE_RELATIONSHIPS table\012CREATE TABLE sample_relationships (id TECH_ID NOT NULL, sample_id_parent TECH_ID NOT NULL, relationship_id TECH_ID NOT NULL, sample_id_child TECH_ID NOT NULL);\012\012-- Add/update constraints\012ALTER TABLE relationship_types ADD CONSTRAINT rety_pk PRIMARY KEY (id);\012ALTER TABLE relationship_types ADD CONSTRAINT rety_uk UNIQUE(code,dbin_id);\012ALTER TABLE sample_relationships ADD CONSTRAINT sare_pk PRIMARY KEY (id);\012ALTER TABLE sample_relationships ADD CONSTRAINT sare_bk_uk UNIQUE(sample_id_child,sample_id_parent,relationship_id);\012ALTER TABLE sample_relationships ADD CONSTRAINT sare_data_fk_child FOREIGN KEY (sample_id_child) REFERENCES samples(id) ON DELETE CASCADE;\012ALTER TABLE sample_relationships ADD CONSTRAINT sare_data_fk_parent FOREIGN KEY (sample_id_parent) REFERENCES samples(id) ON DELETE CASCADE;\012ALTER TABLE sample_relationships ADD CONSTRAINT sare_data_fk_relationship FOREIGN KEY (relationship_id) REFERENCES relationship_types(id);\012\012-- Create index\012CREATE INDEX sare_data_fk_i_child ON sample_relationships (sample_id_child);\012CREATE INDEX sare_data_fk_i_parent ON sample_relationships (sample_id_parent);\012CREATE INDEX sare_data_fk_i_relationship ON sample_relationships (relationship_id);\012\012-- Create sequence for RELATIONSHIP_TYPES\012CREATE SEQUENCE RELATIONSHIP_TYPE_ID_SEQ;\012CREATE SEQUENCE SAMPLE_RELATIONSHIP_ID_SEQ;\012\012-- Create initial relationships\012insert into relationship_types\012(id, \012code, \012label, \012parent_label, \012child_label, \012description, \012pers_id_registerer, \012is_managed_internally, \012is_internal_namespace, \012dbin_id) \012values\012(\012nextval('RELATIONSHIP_TYPE_ID_SEQ'),\012'PARENT_CHILD',\012'Parent - Child', \012'Parent', \012'Child', \012'Parent - Child relationship', \012(select id from persons where user_id ='system'), \012'T', \012'T', \012(select id from database_instances where is_original_source = 'T')\012);\012\012insert into relationship_types\012(id, \012code, \012label, \012parent_label, \012child_label, \012description, \012pers_id_registerer, \012is_managed_internally, \012is_internal_namespace, \012dbin_id) \012values\012(\012nextval('RELATIONSHIP_TYPE_ID_SEQ'),\012'PLATE_CONTROL_LAYOUT',\012'Plate - Control Layout', \012'Plate', \012'Control Layout', \012'Plate - Control Layout relationship', \012(select id from persons where user_id ='system'), \012'T', \012'T', \012(select id from database_instances where is_original_source = 'T')\012); \012\012\012-- Migrate sample relationships to new schema\012INSERT INTO sample_relationships (id, sample_id_parent,sample_id_child,relationship_id) (select distinct nextval('SAMPLE_RELATIONSHIP_ID_SEQ') as id, s.SAMP_ID_GENERATED_FROM as parent_id, s.ID as child_id, rt.id as relationship_id from samples s, relationship_types rt  WHERE rt.code = 'PARENT_CHILD' and s.SAMP_ID_GENERATED_FROM is not null); \012INSERT INTO sample_relationships (id, sample_id_parent,sample_id_child,relationship_id) (select distinct nextval('SAMPLE_RELATIONSHIP_ID_SEQ') as id, s.SAMP_ID_CONTROL_LAYOUT as parent_id, s.ID as child_id, rt.id as relationship_id from samples s, relationship_types rt  WHERE rt.code = 'PLATE_CONTROL_LAYOUT' and s.SAMP_ID_CONTROL_LAYOUT is not null);\012\012-- Drop old sample relations\012ALTER TABLE SAMPLES DROP COLUMN SAMP_ID_TOP;\012ALTER TABLE SAMPLES DROP COLUMN SAMP_ID_GENERATED_FROM;\012ALTER TABLE SAMPLES DROP COLUMN SAMP_ID_CONTROL_LAYOUT;\012\012--------------------------------------------------------------------------------------\012--------------------------------------------------------------------------------------\012-- This is a screening specific migration. Nothing will be performed on openBIS databases \012-- which are not screening specific.\012-- \012-- This migration for each existing connection between oligo well, oligo material and gene material\012-- creates a direct connection between the well and the gene. \012--------------------------------------------------------------------------------------\012--------------------------------------------------------------------------------------\012 \012\011\012CREATE OR REPLACE FUNCTION CONNECT_WELLS_WITH_GENES() RETURNS void AS $$\012DECLARE\012\011counter  int;\012\011oligo_well_exists bool;\012BEGIN\012\011--------------------------------------------------\012\011-- create a gene property and assign it to oligo well\012\011--------------------------------------------------\012\011\012\011select true \012\011into oligo_well_exists\012\011from sample_types \012\011where code = 'OLIGO_WELL';\012\011\012\011if oligo_well_exists IS NULL then \012\011\011-- skip migration if there are no oligo wells\012\011\011return;\012\011end if;   \012\011\012\011insert into property_types(\012\011\011id, \012\011\011code, description, label, \012\011\011daty_id,\012\011\011pers_id_registerer,\012\011\011dbin_id,\012\011\011maty_prop_id) \012\011values(\012\011\011\011nextval('PROPERTY_TYPE_ID_SEQ'), \012\011\011\011'GENE','Inhibited gene','Gene',\012\011\011\011(select id from data_types where code = 'MATERIAL'), \012\011\011\011(select id from persons where user_id ='system'), \012\011\011\011(select id from database_instances where is_original_source = 'T'), \012\011\011\011(select id from material_types where code = 'GENE')\012\011\011);\012\011\011\012\011insert into sample_type_property_types( \012\011  id,\012\011  saty_id,\012\011  prty_id,\012\011  is_mandatory,\012\011  pers_id_registerer,\012\011  ordinal\012\011) values(\012\011\011\011nextval('stpt_id_seq'), \012\011\011\011(select id from sample_types where code = 'OLIGO_WELL'),\012\011\011\011(select id from property_types where code = 'GENE'),\012\011\011\011false,\012\011\011\011(select id from persons where user_id ='system'),\012\011\011\011(select max(ordinal)+1 from sample_type_property_types \012\011\011\011\011where saty_id = (select id from sample_types where code = 'OLIGO_WELL'))\012\011\011);\012\012\011--------------------------------------------------\012\011-- create a gene material property for each oligo well\012\011--------------------------------------------------\012\011\011\012\011select \011count(*)\012\011into counter\012\011from\012\011\011samples well, sample_types well_type, sample_properties well_props, \012\011\011materials well_material, material_properties well_material_props,\012\011\011materials nested_well_material\012\011where\012\011\011well_type.code = 'OLIGO_WELL' and\012\011\011-- find 'well_material' assigned to the well\012\011\011well_props.samp_id = well.id and well_material.id = well_props.mate_prop_id and \012\011\011-- additional joins to entity type tables\012\011\011well_type.id = well.saty_id and\012\011\011-- well content material property\012\011\011well_material_props.mate_id = well_material.id and \012\011\011-- material connected to the material in the well (e.g. gene)\012\011\011well_material_props.mate_prop_id = nested_well_material.id and\012\011\011nested_well_material.maty_id = (select id from material_types where code = 'GENE');\012\011\012\011if counter = 0 then \012\011\011-- skip migration if there are no genes indirectly connected to oligo wells\012\011\011return;\012\011end if;   \012\012\011insert into sample_properties(id, samp_id, stpt_id, mate_prop_id, pers_id_registerer) (\012\011\011select \011nextval('sample_property_id_seq') id, \012\011\011\011well.id samp_id, \012\011\011\011(select stpt.id from sample_type_property_types stpt, property_types props where stpt.prty_id = props.id and props.code='GENE') stpt_id,\012\011\011\011nested_well_material.id mate_prop_id,\012\011\011\011(select id from persons where user_id ='system') pers_id_registerer \012\011\011from\012\011\011\011samples well, sample_types well_type, sample_properties well_props, \012\011\011\011materials well_material, material_properties well_material_props,\012\011\011\011materials nested_well_material\012\011\011where\012\011\011\011well_type.code = 'OLIGO_WELL' and\012\011\011\011-- find 'well_material' assigned to the well\012\011\011\011well_props.samp_id = well.id and well_material.id = well_props.mate_prop_id and \012\011\011\011-- additional joins to entity type tables\012\011\011\011well_type.id = well.saty_id and\012\011\011\011-- well content material property\012\011\011\011well_material_props.mate_id = well_material.id and \012\011\011\011-- material connected to the material in the well (e.g. gene)\012\011\011\011well_material_props.mate_prop_id = nested_well_material.id and\012\011\011\011nested_well_material.maty_id = (select id from material_types where code = 'GENE')\012\011);\012\012END;\012$$ LANGUAGE 'plpgsql';\012\012select CONNECT_WELLS_WITH_GENES();\012drop function CONNECT_WELLS_WITH_GENES();\012\012--------------------------------------------------------------------------------------\012--------------------------------------------------------------------------------------\012\012-- drop dataset triggers\012\012DROP TRIGGER check_dataset_relationships_on_data_table_modification ON data;\012DROP FUNCTION check_dataset_relationships_on_data_table_modification();\012DROP TRIGGER check_dataset_relationships_on_relationships_table_modification ON data_set_relationships;\012DROP FUNCTION check_dataset_relationships_on_relationships_table_modification();\012 \012\012\012\012\012\012\012\012