aboutsummaryrefslogtreecommitdiff
path: root/dbstore/sql
diff options
context:
space:
mode:
Diffstat (limited to 'dbstore/sql')
-rw-r--r--dbstore/sql/cpe-match/insert-name.sql9
-rw-r--r--dbstore/sql/init.sql185
2 files changed, 71 insertions, 123 deletions
diff --git a/dbstore/sql/cpe-match/insert-name.sql b/dbstore/sql/cpe-match/insert-name.sql
index adc99f4..ebdb638 100644
--- a/dbstore/sql/cpe-match/insert-name.sql
+++ b/dbstore/sql/cpe-match/insert-name.sql
@@ -1,5 +1,4 @@
-INSERT INTO cpe_match_cpes(
- cpe_match_id,
- cpe_id,
- cpe22_uri
-) VALUES (?, (SELECT cpe_id FROM cpes WHERE cpe23 = ?), ?);
+INSERT INTO cpe_match_cpes(cpe_match_id, cpe_id, cpe22_uri)
+ SELECT ?, cpe_id, ?
+ FROM cpes
+ WHERE cpe23 = ?;
diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
index 0e05c6a..cc83a02 100644
--- a/dbstore/sql/init.sql
+++ b/dbstore/sql/init.sql
@@ -22,25 +22,6 @@ CREATE TABLE cpes (
)
);
--- 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
@@ -51,66 +32,23 @@ CREATE TABLE cpe_titles (
REFERENCES cpes(cpe_id),
-- language code
- lang TEXT NOT NULL
- CHECK (LENGTH(lang) >= 2),
+ lang TEXT NOT NULL CHECK (
+ -- FIXME: is this upper bound correct
+ LENGTH(lang) BETWEEN 2 AND 16
+ ),
-- text value
- val TEXT NOT NULL
- CHECK (LENGTH(val) > 0),
+ val TEXT NOT NULL CHECK (
+ -- FIXME: is this upper bound correct
+ LENGTH(val) BETWEEN 1 AND 1024
+ ),
-- 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 language index
+CREATE INDEX in_cpe_titles_lang ON cpe_titles(lang);
-- create refs table
CREATE TABLE cpe_refs (
@@ -123,69 +61,78 @@ CREATE TABLE cpe_refs (
-- url
href TEXT NOT NULL CHECK (
- LENGTH(href) > 0 AND
- (href LIKE 'http://%' OR href LIKE 'https://%')
+ LENGTH(href) BETWEEN 1 AND 1024 AND (
+ href LIKE 'http://%' OR
+ href LIKE 'https://%' OR
+ href LIKE 'ftp://%'
+ )
),
-- text description
- val TEXT NOT NULL
- CHECK (LENGTH(val) > 0)
+ val TEXT NOT NULL CHECK (
+ -- FIXME: check upper bound
+ LENGTH(val) BETWEEN 1 AND 1024
+ )
);
--- create refs fts table
-CREATE VIRTUAL TABLE cpe_fts_refs USING fts5(
+-- create all fts table (cross join across all
+-- titles and references, populated by triggers)
+CREATE VIRTUAL TABLE cpes_fts USING fts5(
-- parent CPE id
cpe_id UNINDEXED,
- -- reference URL
- href,
+ -- titles
+ titles,
- -- reference name
- val,
+ -- reference URL (no bueno w/ porter stemming)
+ -- href,
+
+ -- reference names
+ refs,
-- 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;
+-- dummy table to trigger cpes_fts refreshes.
+CREATE TABLE cpes_fts_refresh (
+ created_at TIMESTAMP WITH TIME ZONE NOT NULL
+ DEFAULT CURRENT_TIMESTAMP
+);
--- create refs delete trigger for all fts
-CREATE TRIGGER tr_cpe_refs_delete_fts_all BEFORE DELETE ON cpe_refs
+-- create before insert trigger on cpes_fts_refresh
+CREATE TRIGGER tr_cpes_fts_refresh_before_insert
+ BEFORE INSERT ON cpes_fts_refresh
FOR EACH ROW BEGIN
- DELETE FROM cpe_fts_all
- WHERE cpe_id = OLD.cpe_id
- AND href = OLD.lang
- AND val = OLD.val;
+ DELETE FROM cpes_fts;
END;
--- create refs insert trigger for refs fts
-CREATE TRIGGER tr_cpe_refs_insert_fts_refs AFTER INSERT ON cpe_refs
+-- create insert trigger on cpes_fts_refresh
+CREATE TRIGGER tr_cpes_fts_refresh_after_insert
+ AFTER INSERT ON cpes_fts_refresh
FOR EACH ROW BEGIN
- INSERT INTO cpe_fts_refs(cpe_id, href, val) VALUES (NEW.cpe_id, NEW.href, NEW.val);
- END;
+ INSERT INTO cpes_fts(cpe_id, titles, refs)
+ SELECT a.cpe_id,
+ b.vals,
+ -- c.href,
+ c.vals
--- 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;
+ FROM cpes a
+
+ JOIN (
+ SELECT cpe_id,
+ group_concat(val, ' ') AS vals
+ FROM cpe_titles
+ WHERE lang = 'en-US'
+ GROUP BY cpe_id
+ ) b ON (b.cpe_id = a.cpe_id)
+
+ JOIN (
+ SELECT cpe_id,
+ group_concat(val, ' ') AS vals
+ FROM cpe_refs
+ GROUP BY cpe_id
+ ) c ON (c.cpe_id = a.cpe_id);
END;
--
@@ -196,7 +143,7 @@ CREATE TABLE cpe_matches (
cpe_match_id INTEGER PRIMARY KEY,
-- modern CPE 2.3 formatted string
- cpe23 TEXT UNIQUE NOT NULL CHECK (
+ cpe23 TEXT NOT NULL CHECK (
LENGTH(cpe23) > 28 AND
cpe23 LIKE 'cpe:2.3:%:%:%:%:%:%:%:%:%:%:%'
),
@@ -205,6 +152,8 @@ CREATE TABLE cpe_matches (
cpe22_uri TEXT NOT NULL
);
+CREATE INDEX in_cpe_matches_cpe23 ON cpe_matches(cpe23);
+
-- cpe match vulnerabilities
CREATE TABLE cpe_match_vulnerables (
cpe_match_id INTEGER PRIMARY KEY,
@@ -265,7 +214,7 @@ CREATE TABLE cpe_match_version_maxs (
REFERENCES cpe_matches(cpe_match_id)
);
--- map of matching
+-- map of matches to cpe IDs
CREATE TABLE cpe_match_cpes (
-- CPE match ID
cpe_match_id INTEGER NOT NULL
@@ -927,7 +876,7 @@ CREATE VIRTUAL TABLE cisa_vulns_fts USING fts5(
);
--
--- Dummy table to trigger cve_fts refreshes.
+-- Dummy table to trigger cisa_vulns_fts refreshes.
--
CREATE TABLE cisa_vulns_fts_refresh (
created_at TIMESTAMP WITH TIME ZONE NOT NULL