# Database

## Extracting items

The sqlitebrowser may be used to examine the elements of the database.

The database schema is documented Schema All cryptographic items are stored as base64 DER format and must be decoded before feeding them to OpenSSL:

    | base64 -d | openssl <x509|req|crl|pkcs8> -inform DER ...

Extract the private key with internal name ‘pk8key’ from the database:

    sqlite3 ~/xca.xdb "SELECT private FROM view_private WHERE name='pk8key'" | base64 -d | openssl pkcs8 -inform DER

Extract a CRL:

    sqlite3 ~/xca.xdb "SELECT crl FROM view_crls WHERE name='ca'" | base64 -d | openssl crl -inform DER

Modify the comment of an item with id 3:

    sqlite3 ~/xca.xdb "UPDATE items SET comment='My notes' WHERE id=3"

The item names are not required to be unique anymore. Each table view in XCA has an optional column “Primary key” that may be shown to get the ID of the item in the database.

## Schema

    /* The  32bit hash  in public_keys, x509super, requests, certs and crls
     * is used to quickly find items in the DB by reference.
     * It consists of the first 4 bytes of a SHA1 hash.
     * Collisions are of course possible.
     *
     * All binaries are stored Base64 encoded in a column of type
     * ' B64_BLOB ' It is defined here as 'VARCHAR(8000)'
     */

    #define B64_BLOB  VARCHAR(8000)

    /*
     * The B64(DER(something)) function means DER encode something
     * and then Base64 encode that.
     * So finally this is PEM without newlines, header and footer
     *
     * Dates are alway stored as 'CHAR(15)' in the
     * ASN.1 Generalized time 'yyyyMMddHHmmssZ' format
     */

    #define DB_DATE  CHAR(15)

    /*
     * Configuration settings from
     *  the Options dialog, window size, last export directory,
     *  default key type and size,
     *  table column (position, sort order, visibility)
     */

      -- Schema Version 0
      CREATE TABLE settings (
    	 key_ CHAR(20) UNIQUE,          -- mySql does not like 'key' or 'option
    	 value   B64_BLOB  )
      INSERT INTO settings (key_, value) VALUES ('schema', '  SCHEMA_VERSION  ')

    /*
     * All items (keys, tokens, requests, certs, crls, templates)
     * are stored here with the primary key and some common data
     * The other tables containing the details reference the 'id'
     * as FOREIGN KEY.
     */
      CREATE TABLE items(
    	 id INTEGER PRIMARY KEY,
    	 name VARCHAR(128),                  -- Internal name of the item
    	 type INTEGER,                  -- enum pki_type
    	 source INTEGER,                  -- enum pki_source
    	 date   DB_DATE  ,                  -- Time of insertion (creation/import)
    	 comment VARCHAR(2048),
    	 stamp INTEGER NOT NULL DEFAULT 0,   -- indicate concurrent access
    	 del SMALLINT NOT NULL DEFAULT 0)

    /*
     * Storage of public keys. Private keys and tokens also store
     * their public part here.
     */
      CREATE TABLE public_keys (
    	 item INTEGER,                  -- reference to items(id)
    	 type CHAR(4),                  -- RSA DSA EC (as text)
    	 hash INTEGER,                  -- 32 bit hash
    	 len INTEGER,                          -- key size in bits
    	 'public'   B64_BLOB  ,          -- B64(DER(public key))
    	 FOREIGN KEY (item) REFERENCES items (id))

    /*
     * The private part of RSA, DSA, EC keys.
     * references to 'items' and 'public_keys'
     */
      CREATE TABLE private_keys (
    	 item INTEGER,                  -- reference to items(id)
    	 ownPass INTEGER,                  -- Encrypted by DB pwd or own pwd
    	 private   B64_BLOB  ,          -- B64(Encrypt(DER(private key)))
    	 FOREIGN KEY (item) REFERENCES items (id))

    /*
     * Smart cards or other PKCS#11 tokens
     * references to 'items' and 'public_keys'
     */
      CREATE TABLE tokens (
    	 item INTEGER,                    -- reference to items(id)
    	 card_manufacturer VARCHAR(64),   -- Card location data
    	 card_serial VARCHAR(64),            -- as text
    	 card_model VARCHAR(64),
    	 card_label VARCHAR(64),
    	 slot_label VARCHAR(64),
    	 object_id VARCHAR(64),            -- Unique ID on the token
    	 FOREIGN KEY (item) REFERENCES items (id))

    /*
     * Encryption and hash mechanisms supported by a token
     */
      CREATE TABLE token_mechanism (
    	 item INTEGER,                  -- reference to items(id)
    	 mechanism INTEGER,                  -- PKCS#11: CK_MECHANISM_TYPE
    	 FOREIGN KEY (item) REFERENCES items (id))

    /*
     * An X509 Super class, consisting of a
     *  - Distinguishd name hash
     *  - Referenced key in the database
     *  - hash of the public key, used for lookups if there
     *    is no key to reference
     * used by Requests and certificates and the use-counter of keys:
     * 'SELECT from x509super WHERE pkey=?'
     */
      CREATE TABLE x509super (
    	 item INTEGER,                  -- reference to items(id)
    	 subj_hash INTEGER,             -- 32 bit hash of the Distinguished name
    	 pkey INTEGER,                  -- reference to the key items(id)
    	 key_hash INTEGER,              -- 32 bit hash of the public key
    	 FOREIGN KEY (item) REFERENCES items (id),
    	 FOREIGN KEY (pkey) REFERENCES items (id))

    /*
     * PKCS#10 Certificate request details
     * also takes information from the 'x509super' table.
     */
      CREATE TABLE requests (
    	 item INTEGER,                  -- reference to items(id)
    	 hash INTEGER,                  -- 32 bit hash of the request
    	 signed INTEGER,                -- Whether it was once signed.
    	 request   B64_BLOB  ,          -- B64(DER(PKCS#10 request))
    	 FOREIGN KEY (item) REFERENCES items (id))

    /*
     * X509 certificate details
     * also takes information from the 'x509super' table.
     * The content of the columns: hash, iss_hash, serial, ca
     * can also be retrieved directly from the certificate, but are good
     * to lurk around for faster lookup
     */
      CREATE TABLE certs (
    	 item INTEGER,                  -- reference to items(id)
    	 hash INTEGER,                  -- 32 bit hash of the cert
    	 iss_hash INTEGER,              -- 32 bit hash of the issuer DN
    	 serial VARCHAR(64),            -- Serial number of the certificate
    	 issuer INTEGER,                -- The items(id) of the issuer or NULL
    	 ca INTEGER,                    -- CA: yes / no from BasicConstraints
    	 cert   B64_BLOB  ,             -- B64(DER(certificate))
    	 FOREIGN KEY (item) REFERENCES items (id),
    	 FOREIGN KEY (issuer) REFERENCES items (id))

    /*
     * X509 cartificate Authority data
     */
      CREATE TABLE authority (
    	 item INTEGER,                  -- reference to items(id)
    	 template INTEGER,              -- items(id) of the default template
    	 crlExpire   DB_DATE  ,         -- CRL expiry date
    	 crlNo INTEGER,                 -- Last CRL Number
    	 crlDays INTEGER,               -- CRL days until renewal
    	 dnPolicy VARCHAR(1024),        -- DistinguishedName policy (UNUSED)
    	 FOREIGN KEY (item) REFERENCES items (id),
    	 FOREIGN KEY (template) REFERENCES items (id))

    /*
     * Storage of CRLs
     */
      CREATE TABLE crls (
    	 item INTEGER,                  -- reference to items(id)
    	 hash INTEGER,                  -- 32 bit hash of the CRL
    	 num INTEGER,                   -- Number of revoked certificates
    	 iss_hash INTEGER,              -- 32 bit hash of the issuer DN
    	 issuer INTEGER,                -- The items(id) of the issuer or NULL
    	 crl   B64_BLOB  ,              -- B64(DER(revocation list))
    	 FOREIGN KEY (item) REFERENCES items (id),
    	 FOREIGN KEY (issuer) REFERENCES items (id))

    /*
     * Revocations (serial, date, reason, issuer) used to create new
     * CRLs. 'Manage revocations'
     */
      CREATE TABLE revocations (
    	 caId INTEGER,                  -- reference to certs(item)
    	 serial VARCHAR(64),            -- Serial of the revoked certificate
    	 date   DB_DATE  ,              -- Time of creating the revocation
    	 invaldate   DB_DATE  ,         -- Time of invalidation
    	 crlNo INTEGER,                 -- Crl Number of CRL of first appearance
    	 reasonBit INTEGER,             -- Bit number of the revocation reason
    	 FOREIGN KEY (caId) REFERENCES items (id))

    /*
     * Templates
     */
      CREATE TABLE templates (
    	 item INTEGER,                  -- reference to items(id)
    	 version INTEGER,               -- Version of the template format
    	 template   B64_BLOB  ,         -- The base64 encoded template
    	 FOREIGN KEY (item) REFERENCES items (id))

    /* Views */
      CREATE VIEW view_public_keys AS SELECT
    	 items.id, items.name, items.type AS item_type, items.date,
    	 items.source, items.comment,
    	 public_keys.type as key_type, public_keys.len, public_keys.'public',
    	 private_keys.ownPass,
    	 tokens.card_manufacturer, tokens.card_serial, tokens.card_model,
    	 tokens.card_label, tokens.slot_label, tokens.object_id
    	 FROM public_keys LEFT JOIN items ON public_keys.item = items.id
    	 LEFT JOIN private_keys ON private_keys.item = public_keys.item
    	 LEFT JOIN tokens ON public_keys.item = tokens.item

      CREATE VIEW view_certs AS SELECT
    	 items.id, items.name, items.type, items.date AS item_date,
    	 items.source, items.comment,
    	 x509super.pkey,
    	 certs.serial AS certs_serial, certs.issuer, certs.ca, certs.cert,
    	 authority.template, authority.crlExpire,
    	 authority.crlNo AS auth_crlno, authority.crlDays, authority.dnPolicy,
    	 revocations.serial, revocations.date, revocations.invaldate,
    	 revocations.crlNo, revocations.reasonBit
    	 FROM certs LEFT JOIN items ON certs.item = items.id
    	 LEFT JOIN x509super ON x509super.item = certs.item
    	 LEFT JOIN authority ON authority.item = certs.item
    	 LEFT JOIN revocations ON revocations.caId = certs.issuer
    	                         AND revocations.serial = certs.serial

      CREATE VIEW view_requests AS SELECT
    	 items.id, items.name, items.type, items.date,
    	 items.source, items.comment,
    	 x509super.pkey,
    	 requests.request, requests.signed
    	 FROM requests LEFT JOIN items ON requests.item = items.id
    	 LEFT JOIN x509super ON x509super.item = requests.item

      CREATE VIEW view_crls AS SELECT
    	 items.id, items.name, items.type, items.date,
    	 items.source, items.comment,
    	 crls.num, crls.issuer, crls.crl
    	 FROM crls LEFT JOIN items ON crls.item = items.id

      CREATE VIEW view_templates AS SELECT
    	 items.id, items.name, items.type, items.date,
    	 items.source, items.comment,
    	 templates.version, templates.template
    	 FROM templates LEFT JOIN items ON templates.item = items.id

      CREATE VIEW view_private AS SELECT
    	 name, private FROM private_keys JOIN items ON
    	 items.id = private_keys.item

      CREATE INDEX i_settings_key_ ON settings (key_)
      CREATE INDEX i_items_id ON items (id)
      CREATE INDEX i_public_keys_item ON public_keys (item)
      CREATE INDEX i_public_keys_hash ON public_keys (hash)
      CREATE INDEX i_private_keys_item ON private_keys (item)
      CREATE INDEX i_tokens_item ON tokens (item)
      CREATE INDEX i_token_mechanism_item ON token_mechanism (item)
      CREATE INDEX i_x509super_item ON x509super (item)
      CREATE INDEX i_x509super_subj_hash ON x509super (subj_hash)
      CREATE INDEX i_x509super_key_hash ON x509super (key_hash)
      CREATE INDEX i_x509super_pkey ON x509super (pkey)
      CREATE INDEX i_requests_item ON requests (item)
      CREATE INDEX i_requests_hash ON requests (hash)
      CREATE INDEX i_certs_item ON certs (item)
      CREATE INDEX i_certs_hash ON certs (hash)
      CREATE INDEX i_certs_iss_hash ON certs (iss_hash)
      CREATE INDEX i_certs_serial ON certs (serial)
      CREATE INDEX i_certs_issuer ON certs (issuer)
      CREATE INDEX i_certs_ca ON certs (ca)
      CREATE INDEX i_authority_item ON authority (item)
      CREATE INDEX i_crls_item ON crls (item)
      CREATE INDEX i_crls_hash ON crls (hash)
      CREATE INDEX i_crls_iss_hash ON crls (iss_hash)
      CREATE INDEX i_crls_issuer ON crls (issuer)
      CREATE INDEX i_revocations_caId_serial ON revocations (caId, serial)
      CREATE INDEX i_templates_item ON templates (item)
      CREATE INDEX i_items_stamp ON items (stamp)

    	;
    /* Schema Version 2: Views added to quickly load the data */

    /* Schema Version 3: Add indexes over hashes and primary, foreign keys */

    /* Schema Version 4: Add private key view to extract a private key with:
    	mysql:      mysql -sNp -u xca xca_msq -e
    	or sqlite:  sqlite3 ~/sqlxdb.xdb
    	or psql:    psql -t -h 192.168.140.7 -U xca -d xca_pg -c
    	        'SELECT private FROM view_private WHERE name='pk8key';' |\
    	        base64 -d | openssl pkcs8 -inform DER
     * First mysql/psql will ask for a password and then OpenSSL will ask for
     * the database password.
     */

    /* Schema Version 5: Extend settings value size from 1024 to B64_BLOB
     * SQLite does not support 'ALTER TABLE settings MODIFY ...'
     */

      -- Schema Version 5
      ALTER TABLE settings RENAME TO __settings
      CREATE TABLE settings (
    	 key_ CHAR(20) UNIQUE,          -- mySql does not like 'key' or 'option'
    	 value   B64_BLOB  )
      INSERT INTO settings(key_, value)
    	 SELECT key_, value
    	 FROM __settings
      DROP TABLE __settings
      UPDATE settings SET value='6' WHERE key_='schema'
    	;

      -- Schema Version 6
      ALTER TABLE items ADD del SMALLINT NOT NULL DEFAULT 0
      CREATE INDEX i_items_del ON items (del)
      UPDATE settings SET value='7' WHERE key_='schema'
    	;

    /* When adding new tables or views, also add them to the list
     * in XSqlQuery::rewriteQuery(QString) in lib/sql.cpp
     */