-- Migration from 111 to 112

CREATE TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS (ID TECH_ID, DBIN_ID TECH_ID, CODE CODE, LABEL TEXT_VALUE, URL_TEMPLATE TEXT_VALUE, IS_OPENBIS BOOLEAN DEFAULT FALSE NOT NULL);

CREATE SEQUENCE EXTERNAL_DATA_MANAGEMENT_SYSTEM_ID_SEQ;

ALTER TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS ADD CONSTRAINT EDMS_PK PRIMARY KEY(ID);
ALTER TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS ADD CONSTRAINT EDMS_CODE_UK UNIQUE(CODE, DBIN_ID);

ALTER TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS ADD CONSTRAINT EDMS_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);

GRANT SELECT ON SEQUENCE EXTERNAL_DATA_MANAGEMENT_SYSTEM_ID_SEQ TO GROUP OPENBIS_READONLY;
GRANT SELECT ON TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS TO GROUP OPENBIS_READONLY;
