-- Migration from 042 to 043\012CREATE DOMAIN GRID_EXPRESSION AS VARCHAR(2000);\012CREATE DOMAIN GRID_ID AS VARCHAR(200);\012\012CREATE TABLE GRID_CUSTOM_COLUMNS (ID TECH_ID NOT NULL, DBIN_ID TECH_ID NOT NULL, CODE VARCHAR(200) NOT NULL, LABEL column_label NOT NULL, DESCRIPTION DESCRIPTION_1000,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, PERS_ID_REGISTERER TECH_ID NOT NULL, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, EXPRESSION GRID_EXPRESSION NOT NULL, IS_PUBLIC BOOLEAN NOT NULL, GRID_ID GRID_ID NOT NULL);\012CREATE SEQUENCE GRID_CUSTOM_COLUMNS_ID_SEQ;\012ALTER TABLE GRID_CUSTOM_COLUMNS ADD CONSTRAINT GRID_CUSTOM_COLUMNS_PK PRIMARY KEY(ID);\012ALTER TABLE GRID_CUSTOM_COLUMNS ADD CONSTRAINT GRID_CUSTOM_COLUMNS_BK_UK UNIQUE(CODE, DBIN_ID, GRID_ID);\012\012ALTER TABLE GRID_CUSTOM_COLUMNS ADD CONSTRAINT GRID_CUSTOM_COLUMNS_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID);\012ALTER TABLE GRID_CUSTOM_COLUMNS ADD CONSTRAINT GRID_CUSTOM_COLUMNS_DBIN_FK FOREIGN KEY (DBIN_ID) REFERENCES DATABASE_INSTANCES(ID);\012\012CREATE INDEX GRID_CUSTOM_COLUMNS_PERS_FK_I ON GRID_CUSTOM_COLUMNS (PERS_ID_REGISTERER);\012CREATE INDEX GRID_CUSTOM_COLUMNS_DBIN_FK_I ON GRID_CUSTOM_COLUMNS (DBIN_ID);\012\012-- drop troublesome unique constraint in events table\012\012ALTER TABLE EVENTS DROP CONSTRAINT EVNT_BK_UK;\012\012-- Add ordinal column that is a positive and not null integer to controlled vocabulary terms table. \012-- Initially ordinal values of terms in one controlled vocabulary start from 1 \012-- and increase with increase of term id (so initial order will depend on registration order). \012-- Ordinals should be unique inside a controlled vocabulary but because we use bulk update we can't\012-- easily create this constraint.\012ALTER TABLE controlled_vocabulary_terms ADD COLUMN ordinal BIGINT;\012UPDATE controlled_vocabulary_terms SET ordinal = (\012\011SELECT count(*) FROM controlled_vocabulary_terms c \012\011WHERE c.covo_id = controlled_vocabulary_terms.covo_id AND c.id <= controlled_vocabulary_terms.id\012);\012ALTER TABLE controlled_vocabulary_terms ALTER COLUMN ordinal SET NOT NULL;\012ALTER TABLE controlled_vocabulary_terms ADD CONSTRAINT cvte_ck CHECK (ordinal > 0);\012