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
|
-- 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;
|
