aboutsummaryrefslogtreecommitdiff
path: root/dbstore/sql
diff options
context:
space:
mode:
authorPaul Duncan <pabs@pablotron.org>2022-02-17 23:19:32 -0500
committerPaul Duncan <pabs@pablotron.org>2022-02-17 23:19:32 -0500
commit103dbf934637d80e9624702d039ed2f7a153548d (patch)
tree8ec7817b91dc30b6536b8fb39fc03f5f9753d18f /dbstore/sql
parente3ebcaa10a97c7c50958a2c5bc87a6d48303260b (diff)
downloadcvez-103dbf934637d80e9624702d039ed2f7a153548d.tar.bz2
cvez-103dbf934637d80e9624702d039ed2f7a153548d.zip
dbstore/sql/init.sql: add cpe_match* tables
Diffstat (limited to 'dbstore/sql')
-rw-r--r--dbstore/sql/init.sql103
1 files changed, 99 insertions, 4 deletions
diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
index af5aed3..5836642 100644
--- a/dbstore/sql/init.sql
+++ b/dbstore/sql/init.sql
@@ -1,12 +1,15 @@
-- enable foreign keys
PRAGMA foreign_keys = true;
+-- set database version
+PRAGMA user_version = 314159;
+
-- create cpes table
CREATE TABLE cpes (
-- cpe unique ID
cpe_id INTEGER PRIMARY KEY,
- -- cpe uri
+ -- cpe uri
cpe_uri TEXT UNIQUE NOT NULL CHECK(
LENGTH(cpe_uri) > 5 AND
cpe_uri LIKE 'cpe:/%'
@@ -74,7 +77,7 @@ CREATE VIRTUAL TABLE cpe_fts_titles USING fts5(
-- 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)
+ INSERT INTO cpe_fts_all(cpe_id, title, href, val)
SELECT NEW.cpe_id,
NEW.val,
b.href,
@@ -147,7 +150,7 @@ CREATE VIRTUAL TABLE cpe_fts_refs USING fts5(
-- 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)
+ INSERT INTO cpe_fts_all(cpe_id, title, href, val)
SELECT NEW.cpe_id,
b.val,
NEW.href,
@@ -185,4 +188,96 @@ CREATE TRIGGER tr_cpe_refs_delete_fts_refs BEFORE DELETE ON cpe_refs
AND val = OLD.val;
END;
-PRAGMA user_version = 314159;
+--
+-- cpe matches table
+--
+CREATE TABLE cpe_matches (
+ -- cpe match unique ID
+ cpe_match_id INTEGER PRIMARY KEY,
+
+ -- modern CPE 2.3 formatted string
+ cpe23 TEXT UNIQUE NOT NULL CHECK(
+ LENGTH(cpe23) > 28 AND
+ cpe23 LIKE 'cpe:2.3:%'
+ ),
+
+ -- old CPE 2.2 URI
+ cpe22_uri TEXT NOT NULL
+);
+
+-- cpe match vulnerabilities
+CREATE TABLE cpe_match_vulnerables (
+ cpe_match_id INTEGER PRIMARY KEY,
+
+ -- true if this match is vulnerable, and false
+ -- otherwise
+ is_vulnerable BOOLEAN NOT NULL,
+
+ -- add foreign key constraint
+ FOREIGN KEY (cpe_match_id)
+ REFERENCES cpe_matches(cpe_match_id)
+);
+
+--
+-- cpe match version minimums (e.g. version starts)
+--
+CREATE TABLE cpe_match_version_mins (
+ cpe_match_id INTEGER PRIMARY KEY,
+
+ -- valid values
+ --
+ -- * true: boundary includes this version (e.g. it was specified by
+ -- versionStartIncluding)
+ -- * false: boundary excludes this version (e.g., it was specified by
+ -- versionStartExcluding)
+ is_inclusive BOOLEAN NOT NULL,
+
+ -- minimum version
+ min_version TEXT NOT NULL
+ CHECK (LENGTH(min_version) > 0),
+
+ -- add foreign key constraint
+ FOREIGN KEY (cpe_match_id)
+ REFERENCES cpe_matches(cpe_match_id)
+);
+
+--
+-- cpe match version maximums (e.g. version ends)
+--
+CREATE TABLE cpe_match_version_maxs (
+ cpe_match_id INTEGER PRIMARY KEY,
+
+ -- valid values
+ --
+ -- * true: boundary includes this version (e.g. it was specified by
+ -- versionEndIncluding)
+ -- * false: boundary excludes this version (e.g., it was specified by
+ -- versionEndExcluding)
+ is_inclusive BOOLEAN NOT NULL,
+
+ -- maximum version
+ max_version TEXT NOT NULL
+ CHECK (LENGTH(max_version) > 0),
+
+ -- add foreign key constraint
+ FOREIGN KEY (cpe_match_id)
+ REFERENCES cpe_matches(cpe_match_id)
+);
+
+-- map of matching
+CREATE TABLE cpe_match_cpes (
+ -- CPE match ID
+ cpe_match_id INTEGER NOT NULL
+ REFERENCES cpe_matches(cpe_match_id),
+
+ -- CPE ID
+ cpe_id INTEGER NOT NULL
+ REFERENCES cpes(cpe_id),
+
+ -- nullable cpe22 URI
+ -- normally we would want this in a separate table, but
+ -- we don't care that much about this value
+ cpe22_uri TEXT,
+
+ PRIMARY KEY (cpe_match_id, cpe_id)
+);