-- IGNORE-ERROR
ALTER TABLE OCSP DROP FOREIGN KEY FK_OCSP_ISSUER1;

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

-- changeset xipki:1
CREATE TABLE ISSUER (
    ID INT NOT NULL,
    S1C CHAR(28) NOT NULL COMMENT 'base64 encoded SHA1 sum of the certificate',
    CERT VARCHAR(6000) NOT NULL,
    CONSTRAINT PK_ISSUER PRIMARY KEY (ID)
);

CREATE TABLE OCSP (
    ID BIGINT NOT NULL,
    IID INT NOT NULL,
    IDENT VARCHAR(48) NOT NULL COMMENT 'Identifier consists of hex(SIG_ALG) | hex(CERTHASH_ALG) | hex(serial number)',
    GENERATED_AT BIGINT NOT NULL COMMENT 'generatedAt, seconds since January 1, 1970, 00:00:00 GMT',
    NEXT_UPDATE BIGINT NOT NULL COMMENT 'next update, seconds since January 1, 1970, 00:00:00 GMT',
    RESP VARCHAR(4000) NOT NULL COMMENT 'Base64 DER-encoded OCSP response',
    CONSTRAINT PK_OCSP PRIMARY KEY (ID)
)
COMMENT='Only OCSP response without nonce is cached here';

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

