-- move screenable plates to a group, because it's no longer possible\012-- to have such plates on the instance level\012\012CREATE OR REPLACE FUNCTION move_exp_samples_to_group() RETURNS integer AS $$\012DECLARE\012    sample RECORD;\012    groupId Integer;\012BEGIN\012  FOR sample IN \012    (SELECT * FROM samples s inner join sample_types t on s.saty_id = t.id \012\011where t.code in ('CELL_PLATE', 'REINFECT_PLATE'))\012  LOOP\012\011if (sample.dbin_id is not NULL) THEN\012\011\011select proj.grou_id into groupId from\012\011\011\011samples sa\012\011\011\011left outer join sample_inputs si on sa.id = si.samp_id \012\011\011\011left outer join procedures proc on proc.id = si.proc_id  \012\011\011\011left outer join experiments ex on ex.id = proc.expe_id\012\011\011\011left outer join projects proj on proj.id = ex.proj_id\012\011\011where sa.id = sample.id;\012\011\011if (groupId is NULL) THEN\012\011\011\011select id into groupId from groups limit 1;\012\011\011END IF;\012\011\011if (groupId is NULL) THEN\012\011\011\011RAISE EXCEPTION 'There is no group in the database - internal error!';\012\011\011END IF;\012\011\011update samples set grou_id = groupId, dbin_id = NULL\012\011\011  where id = sample.id;\012\011END IF;\012  END LOOP;\012  RETURN 1;\012END;\012$$ LANGUAGE 'plpgsql';\012\012select move_exp_samples_to_group();\012\012-- Create new table DATA_STORES.\012\012CREATE TABLE DATA_STORES (ID TECH_ID NOT NULL,DBIN_ID TECH_ID NOT NULL,CODE CODE NOT NULL,DOWNLOAD_URL VARCHAR(1024) NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP);\012CREATE SEQUENCE DATA_STORE_ID_SEQ;\012ALTER TABLE DATA_STORES ADD CONSTRAINT DAST_PK PRIMARY KEY(ID);\012ALTER TABLE DATA_STORES ADD CONSTRAINT DAST_BK_UK UNIQUE(CODE,DBIN_ID);\012ALTER TABLE DATA_STORES ADD CONSTRAINT DAST_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012CREATE INDEX DAST_DBIN_FK_I ON DATA_STORES (DBIN_ID);\012\012-- Add new column DAST_ID (connection to DATA_STORES table) to tables DATABASE_INSTANCES, GROUPS, PROJECTS, EXPERIMENTS.\012\012ALTER TABLE DATABASE_INSTANCES ADD COLUMN DAST_ID TECH_ID;\012ALTER TABLE GROUPS ADD COLUMN DAST_ID TECH_ID;\012ALTER TABLE PROJECTS ADD COLUMN DAST_ID TECH_ID;\012ALTER TABLE EXPERIMENTS ADD COLUMN DAST_ID TECH_ID;\012\012-- Create the STANDARD data store and assign it to the home database instance.\012 \012insert into data_stores\012(id\012,code\012,download_url\012,dbin_id)\012values \012(nextval('DATA_STORE_ID_SEQ')\012,'STANDARD'\012,''\012,(select id from database_instances where is_original_source = true)\012);\012\012update database_instances set dast_id = (select id from data_stores where code = 'STANDARD') where is_original_source = true;\012\012-- Add foreign key constratints.\012\012ALTER TABLE DATABASE_INSTANCES ADD CONSTRAINT DBIN_DAST_FK FOREIGN KEY (DAST_ID) REFERENCES DATA_STORES(ID);\012ALTER TABLE GROUPS ADD CONSTRAINT GROU_DAST_FK FOREIGN KEY (DAST_ID) REFERENCES DATA_STORES(ID);\012ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_DAST_FK FOREIGN KEY (DAST_ID) REFERENCES DATA_STORES(ID);\012ALTER TABLE EXPERIMENTS ADD CONSTRAINT EXPE_DAST_FK FOREIGN KEY (DAST_ID) REFERENCES DATA_STORES(ID);\012