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

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

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

CREATE TABLE OCSP (
    ID BIGINT NOT NULL,
    IID INTEGER NOT NULL,
    IDENT VARCHAR(48) NOT NULL,
    GENERATED_AT BIGINT NOT NULL,
    NEXT_UPDATE BIGINT NOT NULL,
    RESP VARCHAR(4000) NOT NULL,
    CONSTRAINT "OCSP_pkey" 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 UPDATE NO ACTION ON DELETE CASCADE;

