--liquibase formatted sql

--changeset ccaffy:1 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE CTA_CATALOGUE
ADD (
  NEXT_SCHEMA_VERSION_MAJOR NUMERIC(20,0),
  NEXT_SCHEMA_VERSION_MINOR NUMERIC(20,0),
  STATUS VARCHAR2(100)
);
--rollback ALTER TABLE CTA_CATALOGUE
--rollback DROP (
--rollback    NEXT_SCHEMA_VERSION_MAJOR, NEXT_SCHEMA_VERSION_MINOR, STATUS
--rollback );

--changeset ccaffy:2 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE CTA_CATALOGUE ADD CONSTRAINT CATALOGUE_STATUS_CONTENT_CK CHECK((NEXT_SCHEMA_VERSION_MAJOR IS NULL AND NEXT_SCHEMA_VERSION_MINOR IS NULL AND STATUS='PRODUCTION') OR (NEXT_SCHEMA_VERSION_MAJOR IS NOT NULL AND NEXT_SCHEMA_VERSION_MINOR IS NOT NULL AND STATUS='UPGRADING')) INITIALLY DEFERRED;
--rollback ALTER TABLE CTA_CATALOGUE DROP CONSTRAINT CATALOGUE_STATUS_CONTENT_CK;

--changeset ccaffy:3 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;

UPDATE CTA_CATALOGUE SET STATUS='UPGRADING';
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=1;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=1;

--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=NULL;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=NULL;
--rollback UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION';

--changeset ccaffy:4 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER INDEX TEMP_T_F_I_B_ARCHIVE_FILE_ID_I RENAME TO TEMP_T_F_I_B_AFI_IDX;
--rollback ALTER INDEX TEMP_T_F_I_B_AFI_IDX RENAME TO TEMP_T_F_I_B_ARCHIVE_FILE_ID_I;

--changeset ccaffy:5 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE USAGESTATS RENAME CONSTRAINT NN_USAGESTATS_GID TO USAGESTATS_GID_NN;
--rollback ALTER TABLE USAGESTATS RENAME CONSTRAINT USAGESTATS_GID_NN TO NN_USAGESTATS_GID;

--changeset ccaffy:6 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE USAGESTATS RENAME CONSTRAINT NN_USAGESTATS_TS TO USAGESTATS_TS_NN;
--rollback ALTER TABLE USAGESTATS RENAME CONSTRAINT USAGESTATS_TS_NN TO NN_USAGESTATS_TS;

--changeset ccaffy:7 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE USAGESTATS RENAME CONSTRAINT PK_USAGESTATS_GID_TS TO USAGESTATS_GID_TS_PK;
--rollback ALTER TABLE USAGESTATS RENAME CONSTRAINT USAGESTATS_GID_TS_PK TO PK_USAGESTATS_GID_TS;

--changeset ccaffy:8 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE ARCHIVE_ROUTE ADD CONSTRAINT ARCHIVE_ROUTE_SCI_TPI_UN UNIQUE(STORAGE_CLASS_ID, TAPE_POOL_ID);
--rollback ALTER TABLE ARCHIVE_ROUTE DROP CONSTRAINT ARCHIVE_ROUTE_SCI_TPI_UN;

--changeset ccaffy:9 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE ARCHIVE_ROUTE RENAME CONSTRAINT ARCHIVE_ROUTE_COPY_NB_GT_ZERO TO ARCHIVE_ROUTE_COPY_NB_GT_0_CK;
--rollback ALTER TABLE ARCHIVE_ROUTE RENAME CONSTRAINT ARCHIVE_ROUTE_COPY_NB_GT_0_CK TO ARCHIVE_ROUTE_COPY_NB_GT_ZERO;

--changeset ccaffy:10 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE TAPE_FILE RENAME CONSTRAINT TAPE_FILE_COPY_NB_GT_ZERO TO TAPE_FILE_COPY_NB_GT_0_CK;
--rollback ALTER TABLE TAPE_FILE RENAME CONSTRAINT TAPE_FILE_COPY_NB_GT_0_CK TO TAPE_FILE_COPY_NB_GT_ZERO;

--changeset ccaffy:11 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE LOGICAL_LIBRARY RENAME CONSTRAINT LOGICAL_LIBRARY_LLN TO LOGICAL_LIBRARY_LLN_UN;
--rollback ALTER TABLE LOGICAL_LIBRARY RENAME CONSTRAINT LOGICAL_LIBRARY_LLN_UN TO LOGICAL_LIBRARY_LLN;

--changeset ccaffy:12 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
CREATE INDEX ARCHIVE_FILE_DFI_IDX ON ARCHIVE_FILE(DISK_FILE_ID);
--rollback DROP INDEX ARCHIVE_FILE_DFI_IDX;

--changeset ccaffy:13 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE TAPE
ADD (
  NB_MASTER_FILES         NUMERIC(20, 0) DEFAULT 0 CONSTRAINT TAPE_NB_MASTER_FILES_NN NOT NULL,
  MASTER_DATA_IN_BYTES    NUMERIC(20, 0) DEFAULT 0 CONSTRAINT TAPE_MASTER_DATA_IN_BYTES_NN NOT NULL,
  DIRTY                   CHAR(1)        DEFAULT '1' CONSTRAINT TAPE_DIRTY_NN NOT NULL,
  CONSTRAINT  TAPE_DIRTY_BOOL_CK CHECK(DIRTY IN ('0','1'))
);
--rollback ALTER TABLE TAPE
--rollback DROP (
--rollback   NB_MASTER_FILES, MASTER_DATA_IN_BYTES, DIRTY
--rollback );

--changeset ccaffy:14 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE STORAGE_CLASS
DROP CONSTRAINT STORAGE_CLASS_DIN_SCN_UN;
--rollback ALTER TABLE STORAGE_CLASS ADD (
--rollback CONSTRAINT STORAGE_CLASS_DIN_SCN_UN UNIQUE(DISK_INSTANCE_NAME, STORAGE_CLASS_NAME)
--rollback );

--changeset ccaffy:15 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
ALTER TABLE STORAGE_CLASS ADD CONSTRAINT STORAGE_CLASS_SCN_UN UNIQUE(STORAGE_CLASS_NAME);
--rollback ALTER TABLE STORAGE_CLASS DROP CONSTRAINT STORAGE_CLASS_SCN_UN;

--changeset ccaffy:16 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=1;
--rollback UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=0

--changeset ccaffy:17 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"1.1" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=NULL;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=NULL;
UPDATE CTA_CATALOGUE SET STATUS='PRODUCTION';
--rollback UPDATE CTA_CATALOGUE SET STATUS='UPGRADING';
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=1;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=1;
