aboutsummaryrefslogtreecommitdiff
path: root/internal/dbstore/sql/init.sql
blob: a08b2f88a203c8485b220526ac952bbe36a09649 (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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
-- enable foreign keys
PRAGMA foreign_keys = true;

-- create cpes table
CREATE TABLE cpes (
  cpe_id  INTEGER PRIMARY KEY,

  -- cpe uri 
  cpe_uri TEXT UNIQUE NOT NULL,

  -- cpe 2.3 formatting string
  cpe23   TEXT UNIQUE NOT NULL
);

-- create titles table
CREATE TABLE cpe_titles (
  -- external CPE id
  cpe_id  INT NOT NULL
          REFERENCES cpes(cpe_id),

  -- language code
  lang    TEXT NOT NULL,

  -- text value
  val     TEXT NOT NULL,

  UNIQUE (cpe_id, lang)
);

-- create titles fts table
CREATE VIRTUAL TABLE cpe_titles_fts USING fts5(title, tokenize = 'porter');

-- create titles insert trigger
CREATE TRIGGER tr_cpe_titles_insert AFTER INSERT ON cpe_titles
  FOR EACH ROW WHEN (NEW.lang = 'en-US') BEGIN
    INSERT INTO cpe_titles_fts(rowid, title) VALUES (NEW.cpe_id, NEW.val);
  END;

-- create titles delete trigger
CREATE TRIGGER tr_cpe_titles_delete BEFORE DELETE ON cpe_titles
  FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN
    DELETE FROM cpe_titles_fts WHERE rowid = OLD.cpe_id;
  END;

-- create refs table
CREATE TABLE cpe_refs (
  -- external CPE id
  cpe_id  INT NOT NULL
          REFERENCES cpes(cpe_id),

  -- url
  href    TEXT NOT NULL,

  -- text description
  val     TEXT NOT NULL
);

-- create refs fts
CREATE VIRTUAL TABLE cpe_refs_fts USING fts5(cpe_id UNINDEXED, href, val);

-- create refs insert trigger
CREATE TRIGGER tr_cpe_refs_insert AFTER INSERT ON cpe_refs
  FOR EACH ROW BEGIN
    INSERT INTO cpe_refs_fts(cpe_id, href, val) VALUES (NEW.cpe_id, NEW.href, NEW.val);
  END;

-- create refs delete trigger
CREATE TRIGGER tr_cpe_refs_delete BEFORE DELETE ON cpe_refs
  FOR EACH ROW BEGIN
    DELETE FROM cpe_refs_fts WHERE rowid = OLD.cpe_id;
  END;

PRAGMA user_version = 314159;