-- ABS-127540

-- Alter table DBCONSISTENCYCHECK
ALTER TABLE DBCONSISTENCYCHECK ADD 
  (ABSFIXABLE      VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_DBCONSISTENCYCHECK_ABSFIXABLE CHECK (ABSFIXABLE IN ('Y', 'N')),
  SEVERITY         NUMBER DEFAULT 0 NOT NULL,
  CORRECTIONSCRIPT CLOB NULL,
  STATUS           NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_DBCONSISTENCYCHECK_STATUS CHECK (STATUS IN (0, 1, 2)),
  LASTRUN          DATE);
  
COMMENT ON COLUMN DBCONSISTENCYCHECK.STATUS IS '0 = Not run 1 = Passed 2 = Failed';

-- Create table DBCONSISTENCYCHECKLOG
CREATE SEQUENCE SEQ_DBCONSISTENCYCHECKLOG START WITH 1 INCREMENT BY 1;

CREATE TABLE DBCONSISTENCYCHECKLOG
  (DBCONSISTENCYCHECKLOG_ID NUMBER NOT NULL,
  DBCONSISTENCYCHECK_ID     NUMBER NOT NULL,
  MESSAGE                   VARCHAR2(2000) NOT NULL,
  TIMESTAMP                 DATE NOT NULL);

ALTER TABLE DBCONSISTENCYCHECKLOG
  ADD CONSTRAINT XPKDBCONSISTENCYCHECKLOG PRIMARY KEY (DBCONSISTENCYCHECKLOG_ID);
  
CREATE INDEX XIF1DBCONSISTENCYCHECKLOG ON DBCONSISTENCYCHECKLOG (DBCONSISTENCYCHECK_ID ASC);

ALTER TABLE DBCONSISTENCYCHECKLOG
  ADD CONSTRAINT FK_DBCONSISTENCYCHECKLOG_DBCONSISTENCYCHECKID FOREIGN KEY (DBCONSISTENCYCHECK_ID)
      REFERENCES DBCONSISTENCYCHECK (DBCONSISTENCYCHECK_ID)ON DELETE CASCADE;
