-- IGNORE-ERROR
ALTER TABLE OCSP DROP CONSTRAINT FK_OCSP_ISSUER1;

DROP TABLE IF EXISTS ISSUER;
DROP TABLE IF EXISTS OCSP;

-- changeset xipki:1
CREATE TABLE ISSUER (
    ID INTEGER NOT NULL,
    S1C CHAR(28) NOT NULL,
    CERT VARCHAR2(6000) NOT NULL,
    CONSTRAINT PK_ISSUER PRIMARY KEY (ID)
);

COMMENT ON COLUMN ISSUER.S1C IS 'base64 encoded SHA1 sum of the certificate';

CREATE TABLE OCSP (
    ID NUMBER(38, 0) NOT NULL,
    IID INTEGER NOT NULL,
    IDENT VARCHAR2(48) NOT NULL,
    GENERATED_AT NUMBER(38, 0) NOT NULL,
    NEXT_UPDATE NUMBER(38, 0) NOT NULL,
    RESP VARCHAR2(4000) NOT NULL,
    CONSTRAINT PK_OCSP PRIMARY KEY (ID)
);

COMMENT ON TABLE OCSP IS 'Only OCSP response without nonce is cached here';
COMMENT ON COLUMN OCSP.IDENT IS 'Identifier consists of hex(SIG_ALG) | hex(CERTHASH_ALG) | hex(serial number)';
COMMENT ON COLUMN OCSP.GENERATED_AT IS 'generatedAt, seconds since January 1, 1970, 00:00:00 GMT';
COMMENT ON COLUMN OCSP.NEXT_UPDATE IS 'next update, seconds since January 1, 1970, 00:00:00 GMT';
COMMENT ON COLUMN OCSP.RESP IS 'Base64 DER-encoded OCSP response';

-- changeset xipki:2
ALTER TABLE OCSP ADD CONSTRAINT FK_OCSP_ISSUER1
    FOREIGN KEY (IID) REFERENCES ISSUER (ID)
    ON DELETE CASCADE;

