summaryrefslogtreecommitdiff
path: root/db/cert-checker.sql
blob: 0103a49b504b5e1e705f1359ba2f22e6bbfe40b7 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
BEGIN EXCLUSIVE TRANSACTION;

-- Update here on every schema change! Integer value.
PRAGMA user_version = 1;

-- Table 'to_check'
CREATE TABLE IF NOT EXISTS to_check (
  check_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  app_id   TEXT NOT NULL,
  pkg_id   TEXT NOT NULL,
  uid      INTEGER NOT NULL,
  verified INTEGER NOT NULL,

  UNIQUE (app_id, pkg_id, uid) ON CONFLICT REPLACE
);

-- Table 'ocsp_urls'
CREATE TABLE IF NOT EXISTS ocsp_urls (
  issuer TEXT NOT NULL PRIMARY KEY,
  url    TEXT NOT NULL,
  date   INTEGER NOT NULL
);

-- Table 'certs_to_check'
CREATE TABLE IF NOT EXISTS certs_to_check (
  cert_id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  check_id    INTEGER NOT NULL,
  certificate TEXT NOT NULL,

  FOREIGN KEY (check_id) REFERENCES to_check(check_id) ON DELETE CASCADE
);

COMMIT TRANSACTION;