-- migration-019-020.sql\012\012\012-- Creating domains\012\012--------\012-- Added\012--------\012\012CREATE DOMAIN EVENT_TYPE AS VARCHAR(40) CHECK (VALUE IN ('DELETION', 'INVALIDATION', 'MOVEMENT'));\012\012----------\012-- Changed\012----------\012\012-- Change the domain AUTHORIZATION_ROLE by changing its TYPE to VARCHAR(40) and dropping the NOT NULL constraint\012\012ALTER TABLE ROLE_ASSIGNMENTS ALTER COLUMN ROLE_CODE TYPE CODE;\012DROP DOMAIN AUTHORIZATION_ROLE;\012CREATE DOMAIN AUTHORIZATION_ROLE AS VARCHAR(40) CHECK (VALUE IN ('ADMIN', 'USER', 'OBSERVER', 'ETL_SERVER')) ;\012ALTER TABLE ROLE_ASSIGNMENTS ALTER COLUMN ROLE_CODE TYPE AUTHORIZATION_ROLE;\012\012\012-- Creating tables\012\012--------\012-- Added\012--------\012\012CREATE TABLE EVENTS (ID TECH_ID NOT NULL,EVENT_TYPE EVENT_TYPE NOT NULL,DATA_ID TECH_ID,DESCRIPTION DESCRIPTION_250,REASON DESCRIPTION_250,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP);\012\012----------\012-- Changed\012----------\012\012ALTER TABLE DATA ADD COLUMN IS_DELETED BOOLEAN_CHAR DEFAULT 'F';\012ALTER TABLE DATA ADD COLUMN IS_VALID BOOLEAN_CHAR DEFAULT 'T';\012\012ALTER TABLE EXTERNAL_DATA ADD COLUMN CVTE_ID_STORE TECH_ID;\012\012ALTER TABLE PERSONS ADD COLUMN GROU_ID TECH_ID;\012\012ALTER TABLE ROLE_ASSIGNMENTS ALTER COLUMN ROLE_CODE SET NOT NULL;\012\012UPDATE EXPERIMENTS SET PERS_ID_REGISTERER = (SELECT ID FROM PERSONS WHERE USER_ID ='system') WHERE PERS_ID_REGISTERER IS NULL;\012ALTER TABLE EXPERIMENTS ALTER COLUMN PERS_ID_REGISTERER SET NOT NULL;\012\012UPDATE PROJECTS SET PERS_ID_REGISTERER = (SELECT ID FROM PERSONS WHERE USER_ID ='system') WHERE PERS_ID_REGISTERER IS NULL;\012ALTER TABLE PROJECTS ALTER COLUMN PERS_ID_REGISTERER SET NOT NULL;\012\012UPDATE PERSONS SET GROU_ID = (SELECT ID FROM GROUPS LIMIT 1);\012\012-- Creating sequences\012\012--------\012-- Added\012--------\012\012CREATE SEQUENCE EVENT_ID_SEQ;\012\012\012-- Creating primary key constraints\012\012--------\012-- Added\012--------\012\012ALTER TABLE EVENTS ADD CONSTRAINT EVNT_PK PRIMARY KEY(ID);\012\012\012-- Creating unique constraints\012\012ALTER TABLE EVENTS ADD CONSTRAINT EVNT_BK_UK UNIQUE(EVENT_TYPE,DATA_ID);\012\012----------\012-- Changed\012----------\012\012ALTER TABLE ROLE_ASSIGNMENTS DROP CONSTRAINT ROAS_BK_UK ;\012\012ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_BK_UK UNIQUE(PERS_ID_GRANTEE,ROLE_CODE,GROU_ID,DBIN_ID);\012\012\012-- Creating foreign key constraints\012\012--------\012-- Added\012--------\012\012ALTER TABLE EVENTS ADD CONSTRAINT EVNT_DATA_FK FOREIGN KEY (DATA_ID) REFERENCES DATA(ID);\012ALTER TABLE EVENTS ADD CONSTRAINT EVNT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);\012ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_CVTE_STORED_ON_FK FOREIGN KEY (CVTE_ID_STORE) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);\012ALTER TABLE PERSONS ADD CONSTRAINT PERS_GROU_FK FOREIGN KEY (GROU_ID) REFERENCES GROUPS(ID);\012\012\012\012-- Creating check constraints\012\012\012-- Creating indices\012\012--------\012-- Added\012--------\012\012CREATE INDEX EVNT_DATA_FK_I ON EVENTS (DATA_ID);\012CREATE INDEX EVNT_PERS_FK_I ON EVENTS (PERS_ID_REGISTERER);\012CREATE INDEX EXDA_CVTE_STORED_ON_FK_I ON EXTERNAL_DATA (CVTE_ID_STORE);\012CREATE INDEX PERS_GROU_FK_I ON PERSONS (GROU_ID);\012