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
181
182
183
184
185
186
187
188
|
-- 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 CHECK(
LENGTH(cpe_uri) > 5 AND
cpe_uri LIKE 'cpe:/%'
),
-- cpe 2.3 formatting string
cpe23 TEXT UNIQUE NOT NULL CHECK(
LENGTH(cpe23) > 28 AND
cpe23 LIKE 'cpe:2.3:%'
)
);
-- 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 AND
(href LIKE 'http://%' OR href LIKE 'https://%')
),
-- 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;
|
