diff options
| -rw-r--r-- | dbstore/sql/init.sql | 103 |
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) +); |