createdb.sql

create table if not exists certs(serial text PRIMARY KEY, status integer, "revocationReason" integer, "revocationDate" integer, "notBefore" integer, "notAfter" integer, subject text, owner text, role text, cert bytea, cn text, fingerprint bytea, "sHash" bytea, "iAndSHash" bytea, "sKIDHash" bytea);
create index if not exists subj_idx on certs(subject);
create index if not exists status_idx on certs(status);
create index if not exists from_idx on certs("notBefore");
create index if not exists to_idx on certs("notAfter");
create index if not exists owner_idx on certs(owner);
create index if not exists role_idx on certs(role);
create index if not exists cn_idx on certs(cn); 
create index if not exists fingerprint_idx on certs(fingerprint);
create index if not exists sHash_idx on certs("sHash");
create index if not exists iAndSHash_idx on certs("iAndSHash");
create index if not exists sKIDHash_idx on certs("sKIDHash");
create table if not exists cert_req_ids(serial text PRIMARY KEY, "certReqId" text, timestamp integer, nonce text, "transactionID" text);
create index if not exists certReqId_idx on cert_req_ids("certReqId");
create index if not exists transactionID_idx on cert_req_ids("transactionID");
create table if not exists keys(kid text PRIMARY KEY, key text);
create table if not exists nonces(nonce text PRIMARY KEY, ip text, expires integer);
create index if not exists ip_idx on nonces(ip);
create index if not exists expires_idx on nonces(expires);
create table if not exists accounts(id text PRIMARY KEY, status integer, "termsOfServiceAgreed" integer, jwk_hash text, kid text, jwk bytea, contacts bytea, "externalAccountBinding" bytea);
create index if not exists jwk_hash_idx on accounts(jwk_hash);
create index if not exists account_status_idx on accounts(status);
create index if not exists account_kid_idx on accounts(kid);
create table if not exists orders(id text PRIMARY KEY, status integer, expires integer, identifiers bytea, "notBefore" integer, "notAfter" integer, "certSerial" text, account text, foreign key(account) references accounts(id) ON DELETE CASCADE);
create index if not exists order_status_idx on orders(status);
create index if not exists order_expires_idx on orders(expires);
create index if not exists notBefore_idx on orders("notBefore");
create index if not exists notAfter_idx on orders("notAfter");
create table if not exists authorizations(id text PRIMARY KEY, identifier bytea, status integer, expires integer, wildcard integer, "order" text, foreign key("order") references orders(id) ON DELETE CASCADE);
create index if not exists authorization_status_idx on authorizations(status);
create index if not exists authorization_expires_idx on authorizations(expires);
create table if not exists challenges(id text PRIMARY KEY, type text, url text, status integer, token text, error text, validated integer, "authorization" text, foreign key("authorization") references authorizations(id) ON DELETE CASCADE);
create index if not exists type_idx on challenges(type);
create index if not exists challenge_status_idx on challenges(status);
create index if not exists token_idx on challenges(token);
create index if not exists validated_idx on challenges(validated);