aboutsummaryrefslogtreecommitdiff
path: root/dbstore/sql/init.sql
blob: 37eef54730d53df061fc1c21d7cd7fee4bcdb1d2 (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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
-- enable foreign keys
PRAGMA foreign_keys = true;

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

  -- cpe uri 
  cpe_uri       TEXT UNIQUE NOT NULL,

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

-- create all fts table (cross join across all
-- titles and references, populated by triggers)
CREATE VIRTUAL TABLE cpe_fts_all USING fts5(
  -- parent CPE id
  cpe_id UNINDEXED,

  -- title
  title,

  -- reference URL
  href,

  -- reference name
  val,

  -- use porter stemming
  tokenize = 'porter'
);

-- create titles table
CREATE TABLE cpe_titles (
  -- title primary key
  cpe_title_id  INTEGER PRIMARY KEY,

  -- external CPE id
  cpe_id        INT NOT NULL
                REFERENCES cpes(cpe_id),

  -- language code
  lang          TEXT NOT NULL
                CHECK (LENGTH(lang) > 0),

  -- text value
  val           TEXT NOT NULL
                CHECK (LENGTH(val) > 0),

  -- prevent duplicate titles of the same name
  UNIQUE (cpe_id, lang)
);

-- create titles fts table
CREATE VIRTUAL TABLE cpe_fts_titles USING fts5(
  -- parent CPE id
  cpe_id UNINDEXED,

  -- title
  title,

  -- use porter stemming
  tokenize = 'porter'
);

-- create titles all fts insert trigger
CREATE TRIGGER tr_cpe_titles_insert_fts_all AFTER INSERT ON cpe_titles
  FOR EACH ROW WHEN (NEW.lang = 'en-US') BEGIN
    INSERT INTO cpe_fts_all(cpe_id, title, href, val) 
      SELECT NEW.cpe_id,
             NEW.val,
             b.href,
             b.val

        FROM cpes a
        JOIN cpe_refs b
          ON (b.cpe_id = a.cpe_id)

       WHERE a.cpe_id = NEW.cpe_id;
  END;

-- create titles delete all fts trigger
CREATE TRIGGER tr_cpe_titles_delete_fts_all BEFORE DELETE ON cpe_titles
  FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN
    DELETE FROM cpe_fts_all
     WHERE cpe_id = OLD.cpe_id
       AND title = OLD.val;
  END;

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

-- create titles delete titles fts trigger
CREATE TRIGGER tr_cpe_titles_delete_fts_titles BEFORE DELETE ON cpe_titles
  FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN
    DELETE FROM cpe_fts_titles
     WHERE cpe_id = OLD.cpe_id
       AND title = OLD.val;
  END;

-- create refs table
CREATE TABLE cpe_refs (
  -- reference unique id
  cpe_ref_id    INTEGER PRIMARY KEY,

  -- external CPE id
  cpe_id        INT NOT NULL
                REFERENCES cpes(cpe_id),

  -- url
  href          TEXT NOT NULL
                CHECK (LENGTH(href) > 0),

  -- text description
  val           TEXT NOT NULL
                CHECK (LENGTH(val) > 0)
);

-- create refs fts table
CREATE VIRTUAL TABLE cpe_fts_refs USING fts5(
  -- parent CPE id
  cpe_id UNINDEXED,

  -- reference URL
  href,

  -- reference name
  val,

  -- use porter stemming
  tokenize = 'porter'
);

-- create refs insert trigger for all fts
CREATE TRIGGER tr_cpe_refs_insert_fts_all AFTER INSERT ON cpe_refs
  FOR EACH ROW BEGIN
    INSERT INTO cpe_fts_all(cpe_id, title, href, val) 
      SELECT NEW.cpe_id,
             b.val,
             NEW.href,
             NEW.val

        FROM cpes a
        JOIN cpe_titles b
          ON (b.cpe_id = a.cpe_id)

       WHERE a.cpe_id = NEW.cpe_id
         AND b.lang = 'en-US';
  END;

-- create refs delete trigger for all fts
CREATE TRIGGER tr_cpe_refs_delete_fts_all BEFORE DELETE ON cpe_refs
  FOR EACH ROW BEGIN
    DELETE FROM cpe_fts_all
     WHERE cpe_id = OLD.cpe_id
       AND href = OLD.lang
       AND val = OLD.val;
  END;

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

-- create refs delete trigger for refs fts
CREATE TRIGGER tr_cpe_refs_delete_fts_refs BEFORE DELETE ON cpe_refs
  FOR EACH ROW BEGIN
    DELETE FROM cpe_fts_refs
     WHERE cpe_id = OLD.cpe_id
       AND href = OLD.lang
       AND val = OLD.val;
  END;

PRAGMA user_version = 314159;