summaryrefslogtreecommitdiff
path: root/db/cert-checker.sql
blob: 4223e2b47c2f5dfd743644eafb5a3bb2bb21a817 (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
34
35
36
37
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 'chains_to_check'
CREATE TABLE IF NOT EXISTS chains_to_check (
  chain_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  check_id INTEGER NOT NULL,

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

-- Table 'certs_to_check'
CREATE TABLE IF NOT EXISTS certs_to_check (
  chain_id    INTEGER NOT NULL,
  certificate TEXT NOT NULL,
  cert_order  INTEGER NOT NULL,

  UNIQUE (chain_id, cert_order),

  PRIMARY KEY (chain_id, certificate),
  FOREIGN KEY (chain_id) REFERENCES chains_to_check(chain_id) ON DELETE CASCADE
);

COMMIT TRANSACTION;