getConfig=\
SELECT \
    "value" \
FROM \
    "config" \
WHERE \
    "id" = ?

addConfig=\
INSERT INTO \
    "config" ("id", "value") \
VALUES \
    (?, ?)

updateConfig=\
UPDATE \
    "config" \
SET \
    "value" = ? \
WHERE \
    "id" = ?

removeConfig=\
DELETE FROM \
    "config" \
WHERE \
    "id" = ?

getNonce=\
SELECT \
    "created", "expires" \
FROM \
    "nonces" \
WHERE \
    "id" = ?

addNonce=\
INSERT INTO \
    "nonces" ("id", "created", "expires") \
VALUES \
    (?, ?, ?)

removeNonce=\
DELETE FROM \
    "nonces" \
WHERE \
    "id" = ?

getExpiredNonceIDs=\
SELECT \
    "id" \
FROM \
    "nonces" \
WHERE \
    "expires" <= ?

getAccount=\
SELECT \
    "created", "status", "jwk" \
FROM \
    "accounts" \
WHERE \
    "id" = ?

getAccountContacts=\
SELECT \
    "contact" \
FROM \
    "account_contacts" \
WHERE \
    "account_id" = ?

addAccount=\
INSERT INTO \
    "accounts" ("id", "created", "status", "jwk") \
VALUES \
    (?, ?, ?, ?)

updateAccount=\
UPDATE \
    "accounts" \
SET \
    "status" = ? \
WHERE \
    "id" = ?

addAccountContacts=\
INSERT INTO \
    "account_contacts" ("account_id", "contact") \
VALUES \
    (?, ?)

removeAccountContacts=\
DELETE FROM \
    "account_contacts" \
WHERE \
    "account_id" = ?

getOrder=\
SELECT \
    "account_id", "created", "status", "expires", "not_before", "not_after", "cert_id" \
FROM \
    "orders" \
WHERE \
    "id" = ?

getExpiredOrderIDs=\
SELECT \
    "id" \
FROM \
    "orders" \
WHERE \
    "expires" <= ?

getOrderIdentifiers=\
SELECT \
    "type", "value" \
FROM \
    "order_identifiers" \
WHERE \
    "order_id" = ?

getOrderAuthorizations=\
SELECT \
    "authz_id" \
FROM \
    "order_authorizations" \
WHERE \
    "order_id" = ?

getOrdersByAccount=\
SELECT \
    "id", "created", "status", "expires", "not_before", "not_after", "cert_id" \
FROM \
    "orders" \
WHERE \
    "account_id" = ?

getOrdersByAuthorizationAndStatus=\
SELECT \
    o."id", o."account_id", o."created", o."status", o."expires", o."not_before", o."not_after", o."cert_id" \
FROM \
    "orders" o, "order_authorizations" oa \
WHERE \
    o."id" = oa."order_id" AND oa."authz_id" = ? AND o."status" = ?

getOrderByCertificate=\
SELECT \
    "id", "account_id", "created", "status", "expires", "not_before", "not_after" \
FROM \
    "orders" \
WHERE \
    "cert_id" = ?

addOrder=\
INSERT INTO \
    "orders" ("id", "account_id", "created", "status", "expires", "not_before", "not_after", "cert_id") \
VALUES \
    (?, ?, ?, ?, ?, ?, ?, ?)

addOrderIdentifiers=\
INSERT INTO \
    "order_identifiers" ("order_id", "type", "value") \
VALUES \
    (?, ?, ?)

removeOrderIdentifiers=\
DELETE FROM \
    "order_identifiers" \
WHERE \
    "order_id" = ?

addOrderAuthorizations=\
INSERT INTO \
    "order_authorizations" ("order_id", "authz_id") \
VALUES \
    (?, ?)

removeOrderAuthorizations=\
DELETE FROM \
    "order_authorizations" \
WHERE \
    "order_id" = ?

updateOrder=\
UPDATE \
    "orders" \
SET \
    "status" = ?, "cert_id" = ?, "expires" = ? \
WHERE \
    "id" = ?

removeOrder=\
DELETE FROM \
    "orders" \
WHERE \
    "id" = ?

getAuthorization=\
SELECT \
    "account_id", "created", "status", "expires", "identifier_type", "identifier_value", "wildcard" \
FROM \
    "authorizations" \
WHERE \
    "id" = ?

getAuthorizationByChallenge=\
SELECT \
    a."id", a."account_id", a."created", a."status", a."expires", a."identifier_type", a."identifier_value", a."wildcard" \
FROM \
    "authorizations" a, "authorization_challenges" ac \
WHERE \
    a."id" = ac."authz_id" AND ac."id" = ?

getRevocationAuthorizations=\
SELECT \
    "id", "created", "status", "expires", "identifier_type", "identifier_value", "wildcard" \
FROM \
    "authorizations" \
WHERE \
    "account_id" = ? and "status" = 'valid' and "expires" > ?

getExpiredAuthorizationIDs=\
SELECT \
    "id" \
FROM \
    "authorizations" \
WHERE \
    "expires" <= ?

getAuthorizationChallenges=\
SELECT \
    "id", "type", "token", "status", "validated" \
FROM \
    "authorization_challenges" \
WHERE \
    "authz_id" = ?

addAuthorization=\
INSERT INTO \
    "authorizations" ("id", "account_id", "created", "status", "expires", "identifier_type", "identifier_value", "wildcard") \
VALUES \
    (?, ?, ?, ?, ?, ?, ?, ?)

updateAuthorization=\
UPDATE \
    "authorizations" \
SET \
    "status" = ?, "expires" = ? \
WHERE \
    "id" = ?

removeAuthorization=\
DELETE FROM \
    "authorizations" \
WHERE \
    "id" = ?

removeAuthorizationChallenges=\
DELETE FROM \
    "authorization_challenges" \
WHERE \
    "authz_id" = ?

addAuthorizationChallenges=\
INSERT INTO \
    "authorization_challenges" ("id", "authz_id", "type", "token", "status", "validated") \
VALUES \
    (?, ?, ?, ?, ?, ?)

getCertificate=\
SELECT \
    "created", "data", "expires" \
FROM \
    "certificates" \
WHERE \
    "id" = ?

getExpiredCertificateIDs=\
SELECT \
    "id" \
FROM \
    "certificates" \
WHERE \
    "expires" <= ?

addCertificate=\
INSERT INTO \
    "certificates" ("id", "created", "data", "expires") \
VALUES \
    (?, ?, ?, ?)

removeCertificate=\
DELETE FROM \
    "certificates" \
WHERE \
    "id" = ?
