--liquibase formatted sql

--changeset afonso:1 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"11.0" SELECT CONCAT(CONCAT(CAST(SCHEMA_VERSION_MAJOR as VARCHAR(10)),'.'), CAST(SCHEMA_VERSION_MINOR AS VARCHAR(10))) AS CATALOGUE_VERSION FROM CTA_CATALOGUE;
--!!!THIS FALSE PRECONDITION IS HERE TO BLOCK AN UPGRADE WHILE THE DEVELOPMENT OF THE NEW CATALOGUE VERSION IS BEING DEVELOPED!!!
UPDATE CTA_CATALOGUE SET STATUS='UPGRADING';
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=12;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=0;
--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 afonso:2 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"11.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 DROP CONSTRAINT TAPE_STATE_CK;
ALTER TABLE TAPE ADD CONSTRAINT TAPE_STATE_CK CHECK(TAPE_STATE IN ('ACTIVE', 'REPACKING_PENDING', 'REPACKING', 'REPACKING_DISABLED', 'DISABLED', 'BROKEN_PENDING', 'BROKEN', 'EXPORTED_PENDING', 'EXPORTED'));
--rollback ALTER TABLE TAPE DROP CONSTRAINT TAPE_STATE_CK;
--rollback ALTER TABLE TAPE ADD CONSTRAINT TAPE_STATE_CK CHECK(TAPE_STATE IN ('ACTIVE', 'REPACKING_PENDING', 'REPACKING', 'DISABLED', 'BROKEN_PENDING', 'BROKEN'));

--changeset afonso:3 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"11.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 ADMIN_USER_AUN_UN_IDX RENAME TO ADMIN_USER_AUN_CI_UN_IDX;
ALTER INDEX DISK_INSTANCE_DIN_UN_IDX RENAME TO DISK_INSTANCE_DIN_CI_UN_IDX;
ALTER INDEX DISK_INSTNCE_SPCE_DISN_UN_IDX RENAME TO DISK_INSTANCE_SPACE_DISN_CI_UN_IDX;
ALTER INDEX DISK_SYSTEM_DSN_UN_IDX RENAME TO DISK_SYSTEM_DSN_CI_UN_IDX;
ALTER INDEX VIRTUAL_ORG_VON_UN_IDX RENAME TO VIRTUAL_ORG_VON_CI_UN_IDX;
ALTER INDEX STORAGE_CLASS_SCN_UN_IDX RENAME TO STORAGE_CLASS_SCN_CI_UN_IDX;
ALTER INDEX TAPE_POOL_TPN_UN_IDX RENAME TO TAPE_POOL_TPN_CI_UN_IDX;
ALTER INDEX MEDIA_TYPE_MTN_UN_IDX RENAME TO MEDIA_TYPE_MTN_CI_UN_IDX;
ALTER INDEX LOGICAL_LIBRARY_LLN_UN_IDX RENAME TO LOGICAL_LIBRARY_LLN_CI_UN_IDX;
ALTER INDEX TAPE_VID_UN_IDX RENAME TO TAPE_VID_CI_UN_IDX;
ALTER INDEX MOUNT_POLICY_MPN_UN_IDX RENAME TO MOUNT_POLICY_MPN_CI_UN_IDX;
ALTER INDEX DRIVE_STATE_DN_UN_IDX RENAME TO DRIVE_STATE_DN_CI_UN_IDX;
--rollback ALTER INDEX ADMIN_USER_AUN_CI_UN_IDX RENAME TO ADMIN_USER_AUN_UN_IDX;
--rollback ALTER INDEX DISK_INSTANCE_DIN_CI_UN_IDX RENAME TO DISK_INSTANCE_DIN_UN_IDX;
--rollback ALTER INDEX DISK_INSTANCE_SPACE_DISN_CI_UN_IDX RENAME TO DISK_INSTNCE_SPCE_DISN_UN_IDX;
--rollback ALTER INDEX DISK_SYSTEM_DSN_CI_UN_IDX RENAME TO DISK_SYSTEM_DSN_UN_IDX;
--rollback ALTER INDEX VIRTUAL_ORG_VON_CI_UN_IDX RENAME TO VIRTUAL_ORG_VON_UN_IDX;
--rollback ALTER INDEX STORAGE_CLASS_SCN_CI_UN_IDX RENAME TO STORAGE_CLASS_SCN_UN_IDX;
--rollback ALTER INDEX TAPE_POOL_TPN_CI_UN_IDX RENAME TO TAPE_POOL_TPN_UN_IDX;
--rollback ALTER INDEX MEDIA_TYPE_MTN_CI_UN_IDX RENAME TO MEDIA_TYPE_MTN_UN_IDX;
--rollback ALTER INDEX LOGICAL_LIBRARY_LLN_CI_UN_IDX RENAME TO LOGICAL_LIBRARY_LLN_UN_IDX;
--rollback ALTER INDEX TAPE_VID_CI_UN_IDX RENAME TO TAPE_VID_UN_IDX;
--rollback ALTER INDEX MOUNT_POLICY_MPN_CI_UN_IDX RENAME TO MOUNT_POLICY_MPN_UN_IDX;
--rollback ALTER INDEX DRIVE_STATE_DN_CI_UN_IDX RENAME TO DRIVE_STATE_DN_UN_IDX;

--changeset afonso:4 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"11.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 UNIQUE INDEX DISK_INSTANCE_SPACE_DISN_UN_IDX ON DISK_INSTANCE_SPACE(DISK_INSTANCE_SPACE_NAME);
CREATE UNIQUE INDEX VIRTUAL_ORG_VON_UN_IDX ON VIRTUAL_ORGANIZATION(VIRTUAL_ORGANIZATION_NAME);
CREATE UNIQUE INDEX STORAGE_CLASS_SCN_UN_IDX ON STORAGE_CLASS(STORAGE_CLASS_NAME);
CREATE UNIQUE INDEX TAPE_POOL_TPN_UN_IDX ON TAPE_POOL(TAPE_POOL_NAME);
CREATE UNIQUE INDEX MEDIA_TYPE_MTN_UN_IDX ON MEDIA_TYPE(MEDIA_TYPE_NAME);
CREATE UNIQUE INDEX LOGICAL_LIBRARY_LLN_UN_IDX ON LOGICAL_LIBRARY(LOGICAL_LIBRARY_NAME);
--rollback DROP INDEX DISK_INSTANCE_SPACE_DISN_UN_IDX;
--rollback DROP INDEX VIRTUAL_ORG_VON_UN_IDX;
--rollback DROP INDEX STORAGE_CLASS_SCN_UN_IDX;
--rollback DROP INDEX TAPE_POOL_TPN_UN_IDX;
--rollback DROP INDEX MEDIA_TYPE_MTN_UN_IDX;
--rollback DROP INDEX LOGICAL_LIBRARY_LLN_UN_IDX;

--changeset afonso:5 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"11.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_POOL ADD ENCRYPTION_KEY_NAME VARCHAR2(100);
--rollback ALTER TABLE TAPE_POOL DROP COLUMN ENCRYPTION_KEY_NAME;

--changeset afonso:6 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"11.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='PRODUCTION';
UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MAJOR=12;
UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=0;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=NULL;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=NULL;
--rollback UPDATE CTA_CATALOGUE SET STATUS='UPGRADING';
--rollback UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MAJOR=11;
--rollback UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=0;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=12;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=0;
