--liquibase formatted sql

--changeset jocamare:1 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"4.3" 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=4;
UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=4;
--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 jocamare:2 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"4.3" 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 DRIVE_STATE (
  DRIVE_NAME                  VARCHAR2(100)       CONSTRAINT DRIVE_DN_NN NOT NULL,
  HOST                        VARCHAR2(100)       CONSTRAINT DRIVE_H_NN  NOT NULL,
  LOGICAL_LIBRARY             VARCHAR2(100)       CONSTRAINT DRIVE_LL_NN NOT NULL,
  SESSION_ID                  NUMERIC(20, 0),
  BYTES_TRANSFERED_IN_SESSION NUMERIC(20, 0),
  FILES_TRANSFERED_IN_SESSION NUMERIC(20, 0),
  SESSION_START_TIME          NUMERIC(20, 0),
  SESSION_ELAPSED_TIME        NUMERIC(20, 0),
  MOUNT_START_TIME            NUMERIC(20, 0),
  TRANSFER_START_TIME         NUMERIC(20, 0),
  UNLOAD_START_TIME           NUMERIC(20, 0),
  UNMOUNT_START_TIME          NUMERIC(20, 0),
  DRAINING_START_TIME         NUMERIC(20, 0),
  DOWN_OR_UP_START_TIME       NUMERIC(20, 0),
  PROBE_START_TIME            NUMERIC(20, 0),
  CLEANUP_START_TIME          NUMERIC(20, 0),
  START_START_TIME            NUMERIC(20, 0),
  SHUTDOWN_TIME               NUMERIC(20, 0),
  MOUNT_TYPE                  VARCHAR2(100)    DEFAULT 'NO_MOUNT' CONSTRAINT DRIVE_MT_NN NOT NULL,
  DRIVE_STATUS                VARCHAR2(100)    DEFAULT 'UNKNOWN' CONSTRAINT DRIVE_DS_NN NOT NULL,
  DESIRED_UP                  CHAR(1)          DEFAULT '0' CONSTRAINT DRIVE_DU_NN  NOT NULL,
  DESIRED_FORCE_DOWN          CHAR(1)          DEFAULT '0' CONSTRAINT DRIVE_DFD_NN NOT NULL,
  REASON_UP_DOWN              VARCHAR2(1000),
  CURRENT_VID                 VARCHAR2(100),
  CTA_VERSION                 VARCHAR2(100),
  CURRENT_PRIORITY            NUMERIC(20, 0),
  CURRENT_ACTIVITY            VARCHAR2(100),
  CURRENT_TAPE_POOL           VARCHAR2(100),
  NEXT_MOUNT_TYPE             VARCHAR2(100)    DEFAULT 'NO_MOUNT' CONSTRAINT DRIVE_NMT_NN NOT NULL,
  NEXT_VID                    VARCHAR2(100),
  NEXT_PRIORITY               NUMERIC(20, 0),
  NEXT_ACTIVITY               VARCHAR2(100),
  NEXT_TAPE_POOL              VARCHAR2(100),
  DEV_FILE_NAME               VARCHAR2(100),
  RAW_LIBRARY_SLOT            VARCHAR2(100),
  CURRENT_VO                  VARCHAR2(100),
  NEXT_VO                     VARCHAR2(100),
  USER_COMMENT                VARCHAR2(1000),
  CREATION_LOG_USER_NAME      VARCHAR2(100),
  CREATION_LOG_HOST_NAME      VARCHAR2(100),
  CREATION_LOG_TIME           NUMERIC(20, 0),
  LAST_UPDATE_USER_NAME       VARCHAR2(100),
  LAST_UPDATE_HOST_NAME       VARCHAR2(100),
  LAST_UPDATE_TIME            NUMERIC(20, 0),
  DISK_SYSTEM_NAME            VARCHAR2(100)       CONSTRAINT DRIVE_DSN_NN NOT NULL,
  RESERVED_BYTES              NUMERIC(20, 0)      CONSTRAINT DRIVE_RB_NN  NOT NULL,
  CONSTRAINT DRIVE_DN_PK PRIMARY KEY(DRIVE_NAME),
  CONSTRAINT DRIVE_DU_BOOL_CK CHECK(DESIRED_UP IN ('0', '1')),
  CONSTRAINT DRIVE_DFD_BOOL_CK CHECK(DESIRED_FORCE_DOWN IN ('0', '1')),
  CONSTRAINT DRIVE_DS_STRING_CK CHECK(DRIVE_STATUS IN ('DOWN', 'UP', 'PROBING', 'STARTING',
  'MOUNTING', 'TRANSFERING', 'UNLOADING', 'UNMOUNTING', 'DRAININGTODISK', 'CLEANINGUP', 'SHUTDOWN',
  'UNKNOWN')),
  CONSTRAINT DRIVE_MT_STRING_CK CHECK(MOUNT_TYPE IN ('NO_MOUNT', 'ARCHIVE_FOR_USER',
  'ARCHIVE_FOR_REPACK', 'RETRIEVE', 'LABEL', 'ARCHIVE_ALL_TYPES')),
  CONSTRAINT DRIVE_NMT_STRING_CK CHECK(NEXT_MOUNT_TYPE IN ('NO_MOUNT', 'ARCHIVE_FOR_USER',
  'ARCHIVE_FOR_REPACK', 'RETRIEVE', 'LABEL', 'ARCHIVE_ALL_TYPES'))
);
--rollback DROP TABLE DRIVE_STATE;

--changeset jocamare:3 failOnError:true dbms:oracle
--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:"4.3" 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=4;
UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=4;
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=4;
--rollback UPDATE CTA_CATALOGUE SET SCHEMA_VERSION_MINOR=3;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MAJOR=4;
--rollback UPDATE CTA_CATALOGUE SET NEXT_SCHEMA_VERSION_MINOR=4;
