--liquibase formatted sql

--changeset lwardena:1 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"12.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=14;
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 lwardena:2 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"12.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 CONSTRAINT TAPE_VID_CK CHECK(VID = UPPER(VID));
--rollback ALTER TABLE TAPE DROP CONSTRAINT TAPE_VID_CK;

--changeset lwardena:3 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"12.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 VIRTUAL_ORGANIZATION ADD IS_REPACK_VO CHAR(1);
ALTER TABLE VIRTUAL_ORGANIZATION ADD CONSTRAINT VIRTUAL_ORGANIZATION_IS_REPACK_VO_BOOL_CK CHECK(IS_REPACK_VO = '1' OR IS_REPACK_VO IS NULL);
CREATE UNIQUE INDEX VIRTUAL_ORG_IRVO_UN_IDX ON VIRTUAL_ORGANIZATION(IS_REPACK_VO);
--rollback DROP INDEX VIRTUAL_ORG_IRVO_UN_IDX;
--rollback ALTER TABLE VIRTUAL_ORGANIZATION DROP CONSTRAINT VIRTUAL_ORGANIZATION_IS_REPACK_VO_BOOL_CK;
--rollback ALTER TABLE VIRTUAL_ORGANIZATION DROP COLUMN IS_REPACK_VO;

--changeset lwardena:4 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"12.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 TABLE PHYSICAL_LIBRARY (
  PHYSICAL_LIBRARY_ID                  NUMERIC(20, 0)       CONSTRAINT PHYSICAL_LIBRARY_ID_NN   NOT NULL,
  PHYSICAL_LIBRARY_NAME                VARCHAR2(100)        CONSTRAINT PHYSICAL_LIBRARY_NA_NN   NOT NULL,
  PHYSICAL_LIBRARY_MANUFACTURER        VARCHAR2(100)        CONSTRAINT PHYSICAL_LIBRARY_MA_NN   NOT NULL,
  PHYSICAL_LIBRARY_MODEL               VARCHAR2(100)        CONSTRAINT PHYSICAL_LIBRARY_MO_NN   NOT NULL,
  PHYSICAL_LIBRARY_TYPE                VARCHAR2(100)        ,
  GUI_URL                              VARCHAR2(1000)       ,
  WEBCAM_URL                           VARCHAR2(1000)       ,
  PHYSICAL_LOCATION                    VARCHAR2(100)        ,
  NB_PHYSICAL_CARTRIDGE_SLOTS          NUMERIC(20, 0)       CONSTRAINT PHYSICAL_LIBRARY_CS_NN   NOT NULL,
  NB_AVAILABLE_CARTRIDGE_SLOTS         NUMERIC(20, 0)       ,
  NB_PHYSICAL_DRIVE_SLOTS              NUMERIC(20, 0)       CONSTRAINT PHYSICAL_LIBRARY_DS_NN   NOT NULL,
  USER_COMMENT                         VARCHAR2(1000)       ,
  CREATION_LOG_USER_NAME               VARCHAR2(100)        CONSTRAINT PHYSICAL_LIBRARY_UN_NN   NOT NULL,
  CREATION_LOG_HOST_NAME               VARCHAR2(100)        CONSTRAINT PHYSICAL_LIBRARY_HN_NN   NOT NULL,
  CREATION_LOG_TIME                    NUMERIC(20, 0)       CONSTRAINT PHYSICAL_LIBRARY_CLT_NN  NOT NULL,
  LAST_UPDATE_USER_NAME                VARCHAR2(100)        CONSTRAINT PHYSICAL_LIBRARY_LUUN_NN NOT NULL,
  LAST_UPDATE_HOST_NAME                VARCHAR2(100)        CONSTRAINT PHYSICAL_LIBRARY_LUHN_NN NOT NULL,
  LAST_UPDATE_TIME                     NUMERIC(20, 0)       CONSTRAINT PHYSICAL_LIBRARY_LUT_NN  NOT NULL,
  CONSTRAINT PHYSICAL_LIBRARY_PK PRIMARY KEY(PHYSICAL_LIBRARY_ID)
);
CREATE UNIQUE INDEX PHYSICAL_LIBRARY_PLN_CI_UN_IDX ON PHYSICAL_LIBRARY(LOWER(PHYSICAL_LIBRARY_NAME));
ALTER TABLE LOGICAL_LIBRARY ADD PHYSICAL_LIBRARY_ID NUMERIC(20, 0);
ALTER TABLE LOGICAL_LIBRARY ADD CONSTRAINT LOGICAL_LIBRARY_PLI_FK FOREIGN KEY(PHYSICAL_LIBRARY_ID) REFERENCES PHYSICAL_LIBRARY(PHYSICAL_LIBRARY_ID);
CREATE INDEX LOGICAL_LIBRARY_PLI_IDX ON LOGICAL_LIBRARY(PHYSICAL_LIBRARY_ID);
CREATE SEQUENCE PHYSICAL_LIBRARY_ID_SEQ
  INCREMENT BY 1
  START WITH 1
  NOMAXVALUE
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;
--rollback DROP SEQUENCE PHYSICAL_LIBRARY_ID_SEQ;
--rollback DROP INDEX LOGICAL_LIBRARY_PLI_IDX;
--rollback ALTER TABLE LOGICAL_LIBRARY DROP CONSTRAINT LOGICAL_LIBRARY_PLI_FK;
--rollback ALTER TABLE LOGICAL_LIBRARY DROP COLUMN PHYSICAL_LIBRARY_ID;
--rollback DROP INDEX PHYSICAL_LIBRARY_PLN_CI_UN_IDX;
--rollback DROP TABLE PHYSICAL_LIBRARY;

--changeset lwardena:5 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"12.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 PURCHASE_ORDER VARCHAR2(100);
--rollback ALTER TABLE TAPE DROP COLUMN PURCHASE_ORDER;

--changeset lwardena:6 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"12.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=14;
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=12;
--rollback UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=0;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=14;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=0;
