-- 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 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) >= 2), -- 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; -- -- 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 unique id 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 NOT NULL, PRIMARY KEY (cpe_match_id, cpe_id) ); -- -- Descriptions of CVEs and CVE problem types. -- CREATE TABLE descriptions ( -- description unique ID desc_id INTEGER PRIMARY KEY, -- description language lang TEXT NOT NULL CHECK (LENGTH(lang) >= 2), -- description text value TEXT NOT NULL CHECK ( LENGTH(value) > 0 ) ); -- -- CVE feeds. -- CREATE TABLE feeds ( -- feed unique ID feed_id INTEGER PRIMARY KEY, -- time that feed was added created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, -- data timestamp data_time TIMESTAMP WITH TIME ZONE NOT NULL ); -- -- CVE feed items. -- CREATE TABLE feed_items ( item_id INTEGER PRIMARY KEY, -- feed that this CVE belongs to feed_id INTEGER NOT NULL REFERENCES feeds(feed_id), -- published timestamp published_at TIMESTAMP WITH TIME ZONE NOT NULL, -- last modified timestamp modified_at TIMESTAMP WITH TIME ZONE NOT NULL ); -- create index CREATE INDEX in_feed_items_feed_id ON feed_items(feed_id); -- -- CVE assigners -- CREATE TABLE assigners ( -- assigner unique ID assigner_id INTEGER PRIMARY KEY, -- assigner value assigner TEXT UNIQUE NOT NULL CHECK ( LENGTH(assigner) > 0 ) ); -- -- CVEs -- -- NOTE: this combines the concept of an "item" and a "cve" from the -- source feed. maybe we should split them up again? -- CREATE TABLE cves ( -- CVE unique ID cve_id INTEGER PRIMARY KEY, -- feed item that this CVE belongs to item_id INTEGER NOT NULL REFERENCES feed_items(item_id), -- year of NVD CVE ID, (e.g. the "XXXX" in "CVE-XXXX-YYYY") cve_year SMALLINT NOT NULL CHECK ( cve_year BETWEEN 1999 AND 2126 ), -- number of NVD CVE ID, (e.g. the "YYYY" in "CVE-XXXX-YYYY") cve_num SMALLINT NOT NULL CHECK ( -- there is no upper bound, but let's pick -- something sane cve_num BETWEEN 1 AND 33554432 ), -- assigner ID assigner_id INTEGER NOT NULL REFERENCES assigners(assigner_id) ); -- create index CREATE INDEX in_cves_item_id ON cves(item_id); -- -- NVD CVE IDs (e.g. "CVE-YYYY-NNNN"). -- CREATE VIEW cve_nvd_ids AS SELECT cve_id, 'CVE-' || a.cve_year || '-' || printf('%04d', a.cve_num) AS nvd_cve_id FROM cves; -- -- CVE descriptions -- CREATE TABLE cve_descriptions ( -- CVE unique ID cve_id INTEGER NOT NULL REFERENCES cves(cve_id), -- description unique ID desc_id INTEGER NOT NULL REFERENCES descriptions(desc_id), PRIMARY KEY (cve_id, desc_id) ); -- create index CREATE INDEX in_cve_descriptions_cve_id ON cve_descriptions(cve_id); -- -- CVE problem types -- CREATE TABLE cve_problems ( -- problem type unique ID pt_id INTEGER PRIMARY KEY, -- CVE that this problem type belongs to cve_id INTEGER NOT NULL REFERENCES cves(cve_id) ); -- create index CREATE INDEX in_cve_problems_cve_id ON cve_problems(cve_id); -- -- CVE problem type descriptions -- CREATE TABLE cve_problem_descriptions ( -- problem type that this description belongs to pt_id INTEGER NOT NULL REFERENCES cve_problems(pt_id), -- description unique ID desc_id INTEGER NOT NULL REFERENCES descriptions(desc_id), PRIMARY KEY (pt_id, desc_id) ); -- -- CVE references -- CREATE TABLE cve_refs ( -- cve reference unique ID cve_ref_id INTEGER PRIMARY KEY, -- CVE unique ID cve_id INTEGER NOT NULL REFERENCES cves(cve_id), -- reference URL url TEXT NOT NULL CHECK ( -- FIXME: not sure about length here LENGTH(url) BETWEEN 7 AND 1024 ), -- reference name name TEXT NOT NULL CHECK ( -- FIXME: not sure about length here LENGTH(name) BETWEEN 1 AND 1024 ), -- reference name source TEXT NOT NULL ); -- create index CREATE INDEX in_cve_refs_cve_id ON cve_refs(cve_id); -- -- cve reference tags -- CREATE TABLE cve_ref_tags ( cve_ref_id INTEGER NOT NULL REFERENCES cve_refs(cve_ref_id), -- tag tag TEXT NOT NULL CHECK ( -- FIXME: not sure about maximum length LENGTH(tag) BETWEEN 1 AND 128 ), -- prevent duplicate tags UNIQUE (cve_ref_id, tag) ); -- create index CREATE INDEX in_cve_ref_tags_cve_ref_id ON cve_ref_tags(cve_ref_id); -- -- CVSS severities. -- CREATE TABLE severities ( -- severity unique ID severity_id INTEGER PRIMARY KEY, -- severity name name TEXT UNIQUE NOT NULL CHECK ( -- limit to know severities name IN ('NONE', 'LOW', 'MEDIUM', 'HIGH', 'CRITICAL') ), -- severity sort order, from low to high sort SMALLINT UNIQUE NOT NULL ); -- add severities INSERT INTO severities(severity_id, name, sort) VALUES (1, 'NONE', 1), (2, 'LOW', 2), (3, 'MEDIUM', 3), (4, 'HIGH', 4), (5, 'CRITICAL', 5); -- -- item cvss v2 impacts -- CREATE TABLE item_cvss_v2_impacts ( -- feed item that this impact belongs to item_id INTEGER NOT NULL REFERENCES feed_items(item_id), -- cvss v2 version -- FIXME: don't need this, check during import -- v2_version TEXT NOT NULL CHECK ( -- LENGTH(v2_version) > 0 -- ), -- cvss v2 vector v2_vector TEXT NOT NULL CHECK ( -- TODO: enforce this LENGTH(v2_vector) > 0 ), -- cvss v2 base score -- (NOTE: multiplied by 10 and stored as a tinyint) v2_base_score TINYINT NOT NULL CHECK ( v2_base_score BETWEEN 0 AND 100 ), -- cvss v2 severity v2_severity_id INTEGER NOT NULL REFERENCES severities(severity_id), -- cvss v2 impact score -- (NOTE: multiplied by 10 and stored as a tinyint) v2_impact_score TINYINT NOT NULL CHECK ( v2_impact_score BETWEEN 0 AND 100 ), -- cvss v2 exploitability score -- (NOTE: multiplied by 10 and stored as a tinyint) v2_expl_score TINYINT NOT NULL CHECK ( v2_expl_score BETWEEN 0 AND 100 ), -- cvss v2 insufficient info v2_insufficient_info BOOLEAN NOT NULL, -- cvss v2 insufficient info v2_obtain_all_priv BOOLEAN NOT NULL, -- cvss v2 obtain user privileges v2_obtain_user_priv BOOLEAN NOT NULL, -- cvss v2 obtain other privileges v2_obtain_other_priv BOOLEAN NOT NULL, -- cvss v2 user interaction required? v2_ui_req BOOLEAN NOT NULL ); -- create indices CREATE INDEX in_item_cvss_v2_impacts_item_id ON item_cvss_v2_impacts(item_id); CREATE INDEX in_item_cvss_v2_impacts_v2_severity_id ON item_cvss_v2_impacts(v2_severity_id); -- -- item cvss v3 impacts -- CREATE TABLE item_cvss_v3_impacts ( -- feed item that this impact belongs to item_id INTEGER NOT NULL REFERENCES feed_items(item_id), -- FIXME: don't need this, check during import -- v3_version TEXT NOT NULL CHECK ( -- LENGTH(v3_version) > 0 -- ), -- cvss v3 vector v3_vector TEXT NOT NULL CHECK ( -- TODO: enforce this LENGTH(v3_vector) > 0 AND v3_vector LIKE 'CVSS:3._/%' ), -- cvss v3 base score -- (NOTE: multiplied by 10 and stored as a tinyint) v3_base_score TINYINT NOT NULL CHECK ( v3_base_score BETWEEN 0 AND 100 ), -- cvss v3 base severity v3_severity_id INTEGER NOT NULL REFERENCES severities(severity_id), -- cvss v3 exploitability score -- (NOTE: multiplied by 10 and stored as a tinyint) v3_expl_score TINYINT NOT NULL CHECK ( v3_expl_score BETWEEN 0 AND 100 ), -- cvss v3 impact score -- (NOTE: multiplied by 10 and stored as a tinyint) v3_impact_score TINYINT NOT NULL CHECK ( v3_impact_score BETWEEN 0 AND 100 ) ); -- create indices CREATE INDEX in_item_cvss_v3_impacts_item_id ON item_cvss_v3_impacts(item_id); CREATE INDEX in_item_cvss_v3_impacts_v3_severity_id ON item_cvss_v3_impacts(v3_severity_id); -- -- create cve all fts table -- -- join across all of the following: -- * NVD CVE ID -- * assigner -- * cve description(s) -- * cve problem type description(s) -- * cve reference(s) (url, name, source, and tags) -- * cvss v2 severity name -- * cvss v3 severity name -- -- this virtual table is populated and cleared by triggers on the -- corresponding cve tables (see triggers below). -- CREATE VIRTUAL TABLE cve_fts_all USING fts5( -- parent item ID item_id UNINDEXED, -- parent CVE id cve_id UNINDEXED, -- cve description ID cve_desc_id UNINDEXED, -- problem type ID pt_desc_id UNINDEXED, -- cve ref ID cve_ref_id UNINDEXED, -- v2 severity ID v2_severity_id UNINDEXED, -- v3 severity ID v3_severity_id UNINDEXED, -- NVD CVE ID (e.g. CVE-XXXX-YYYY) nvd_cve_id, -- CVE assigner assigner, -- CVE description cve_desc, -- problem type description pt_desc, -- reference URL ref_url, -- reference name ref_name, -- reference source ref_source, -- reference tag ref_tag, -- cvss v2 severity v2_severity, -- cvss v3 severity v3_severity, -- use porter stemming tokenize = 'porter' ); -- -- create insert trigger on cve_descriptions -- CREATE TRIGGER tr_cve_descriptions_insert_fts_all AFTER INSERT ON cve_descriptions -- FOR EACH ROW BEGIN -- INSERT INTO cve_fts_all( -- item_id, -- cve_id, -- cve_desc_id, -- pt_desc_id, -- cve_ref_id, -- v2_severity_id, -- v3_severity_id, -- -- nvd_cve_id, -- assigner, -- cve_desc, -- pt_desc, -- ref_url, -- ref_name, -- ref_source, -- ref_tag, -- v2_severity, -- v3_severity -- ) -- SELECT a.item_id, -- item_id -- NEW.cve_id, -- cve_id -- d.desc_id, -- cve_desc_id -- g.desc_id, -- pt_desc_id -- h.cve_ref_id, -- cve_ref_id -- j.v2_severity_id, -- v2_severity_id -- l.v3_severity_id, -- v3_severity_id -- -- -- build CVE number (FIXME: move to view?) -- 'CVE-' || a.cve_year || '-' || printf('%04d', a.cve_num), -- nvd_cve_id -- b.assigner, -- assigner -- d.value, -- cve_desc -- COALESCE(g.value, ''), -- pt_desc -- COALESCE(h.url, ''), -- ref_url -- COALESCE(h.name, ''), -- ref_name -- COALESCE(h.source, ''), -- ref_source -- COALESCE(i.tag, ''), -- ref_tag -- COALESCE(k.name, ''), -- v2_severity -- COALESCE(m.name, '') -- v3_severity -- -- FROM cves a -- JOIN assigners b -- ON (b.assigner_id = a.assigner_id) -- JOIN cve_descriptions c -- ON (c.cve_id = a.cve_id) -- JOIN descriptions d -- ON (d.desc_id = c.desc_id) -- LEFT JOIN cve_problems e -- ON (e.cve_id = a.cve_id) -- LEFT JOIN cve_problem_descriptions f -- ON (f.pt_id = e.pt_id) -- LEFT JOIN descriptions g -- ON (g.desc_id = f.desc_id) -- LEFT JOIN cve_refs h -- ON (h.cve_id = a.cve_id) -- LEFT JOIN cve_ref_tags i -- ON (i.cve_ref_id = h.cve_ref_id) -- LEFT JOIN item_cvss_v2_impacts j -- ON (j.item_id = a.item_id) -- LEFT JOIN severities k -- ON (k.severity_id = j.v2_severity_id) -- LEFT JOIN item_cvss_v3_impacts l -- ON (l.item_id = a.item_id) -- LEFT JOIN severities m -- ON (m.severity_id = l.v3_severity_id) -- -- WHERE a.cve_id = NEW.cve_id -- AND c.desc_id = NEW.desc_id -- AND d.lang LIKE 'en%'; -- END; -- -- -- create delete trigger on cve_descriptions -- CREATE TRIGGER tr_cve_descriptions_delete_fts_all BEFORE DELETE ON cve_descriptions -- FOR EACH ROW BEGIN -- DELETE FROM cve_fts_all -- WHERE cve_id = OLD.cve_id -- AND cve_desc_id = OLD.desc_id; -- END; -- -- -- create insert trigger on cve_problem_descriptions -- CREATE TRIGGER tr_cve_problem_descriptions_insert_fts_all AFTER INSERT ON cve_problem_descriptions -- FOR EACH ROW BEGIN -- INSERT INTO cve_fts_all( -- item_id, -- cve_id, -- cve_desc_id, -- pt_desc_id, -- cve_ref_id, -- v2_severity_id, -- v3_severity_id, -- -- nvd_cve_id, -- assigner, -- cve_desc, -- pt_desc, -- ref_url, -- ref_name, -- ref_source, -- ref_tag, -- v2_severity, -- v3_severity -- ) -- SELECT a.item_id, -- item_id -- a.cve_id, -- cve_id -- d.desc_id, -- cve_desc_id -- g.desc_id, -- pt_desc_id -- h.cve_ref_id, -- cve_ref_id -- j.v2_severity_id, -- v2_severity_id -- l.v3_severity_id, -- v3_severity_id -- -- -- build CVE number (FIXME: move to view?) -- 'CVE-' || a.cve_year || '-' || printf('%04d', a.cve_num), -- nvd_cve_id -- b.assigner, -- assigner -- COALESCE(d.value, ''), -- cve_desc -- g.value, -- pt_desc -- COALESCE(h.url, ''), -- ref_url -- COALESCE(h.name, ''), -- ref_name -- COALESCE(h.source, ''), -- ref_source -- COALESCE(i.tag, ''), -- ref_tag -- COALESCE(k.name, ''), -- v2_severity -- COALESCE(m.name, '') -- v3_severity -- -- FROM cves a -- JOIN assigners b -- ON (b.assigner_id = a.assigner_id) -- LEFT JOIN cve_descriptions c -- ON (c.cve_id = a.cve_id) -- LEFT JOIN descriptions d -- ON (d.desc_id = c.desc_id) -- JOIN cve_problems e -- ON (e.cve_id = a.cve_id) -- JOIN cve_problem_descriptions f -- ON (f.pt_id = e.pt_id) -- JOIN descriptions g -- ON (g.desc_id = f.desc_id) -- LEFT JOIN cve_refs h -- ON (h.cve_id = a.cve_id) -- LEFT JOIN cve_ref_tags i -- ON (i.cve_ref_id = h.cve_ref_id) -- LEFT JOIN item_cvss_v2_impacts j -- ON (j.item_id = a.item_id) -- LEFT JOIN severities k -- ON (k.severity_id = j.v2_severity_id) -- LEFT JOIN item_cvss_v3_impacts l -- ON (l.item_id = a.item_id) -- LEFT JOIN severities m -- ON (m.severity_id = l.v3_severity_id) -- -- WHERE f.pt_id = NEW.pt_id -- AND f.desc_id = NEW.desc_id -- AND g.lang LIKE 'en%'; -- END; -- -- -- create delete trigger on cve_problem_descriptions -- CREATE TRIGGER tr_cve_problem_descriptions_delete_fts_all BEFORE DELETE ON cve_problem_descriptions -- FOR EACH ROW BEGIN -- DELETE FROM cve_fts_all -- WHERE pt_desc_id = OLD.desc_id; -- END; -- -- -- create insert trigger on cve_refs -- CREATE TRIGGER tr_cve_refs_insert_fts_all AFTER INSERT ON cve_refs -- FOR EACH ROW BEGIN -- INSERT INTO cve_fts_all( -- item_id, -- cve_id, -- cve_desc_id, -- pt_desc_id, -- cve_ref_id, -- v2_severity_id, -- v3_severity_id, -- -- nvd_cve_id, -- assigner, -- cve_desc, -- pt_desc, -- ref_url, -- ref_name, -- ref_source, -- ref_tag, -- v2_severity, -- v3_severity -- ) -- SELECT a.item_id, -- item_id -- a.cve_id, -- cve_id -- d.desc_id, -- cve_desc_id -- g.desc_id, -- pt_desc_id -- h.cve_ref_id, -- cve_ref_id -- j.v2_severity_id, -- v2_severity_id -- l.v3_severity_id, -- v3_severity_id -- -- -- build CVE number (FIXME: move to view?) -- 'CVE-' || a.cve_year || '-' || printf('%04d', a.cve_num), -- nvd_cve_id -- b.assigner, -- assigner -- COALESCE(d.value, ''), -- cve_desc -- COALESCE(g.value, ''), -- pt_desc -- h.url, -- ref_url -- h.name, -- ref_name -- h.source, -- ref_source -- COALESCE(i.tag, ''), -- ref_tag -- COALESCE(k.name, ''), -- v2_severity -- COALESCE(m.name, '') -- v3_severity -- -- FROM cves a -- JOIN assigners b -- ON (b.assigner_id = a.assigner_id) -- LEFT JOIN cve_descriptions c -- ON (c.cve_id = a.cve_id) -- LEFT JOIN descriptions d -- ON (d.desc_id = c.desc_id) -- LEFT JOIN cve_problems e -- ON (e.cve_id = a.cve_id) -- LEFT JOIN cve_problem_descriptions f -- ON (f.pt_id = e.pt_id) -- LEFT JOIN descriptions g -- ON (g.desc_id = f.desc_id) -- JOIN cve_refs h -- ON (h.cve_id = a.cve_id) -- LEFT JOIN cve_ref_tags i -- ON (i.cve_ref_id = h.cve_ref_id) -- LEFT JOIN item_cvss_v2_impacts j -- ON (j.item_id = a.item_id) -- LEFT JOIN severities k -- ON (k.severity_id = j.v2_severity_id) -- LEFT JOIN item_cvss_v3_impacts l -- ON (l.item_id = a.item_id) -- LEFT JOIN severities m -- ON (m.severity_id = l.v3_severity_id) -- -- WHERE h.cve_ref_id = NEW.cve_ref_id; -- END; -- -- -- create delete trigger on cve_refs -- CREATE TRIGGER tr_cve_refs_delete_fts_all BEFORE DELETE ON cve_refs -- FOR EACH ROW BEGIN -- DELETE FROM cve_fts_all -- WHERE cve_ref_id = OLD.cve_ref_id; -- END; -- -- -- create insert trigger on cve_ref_tags -- CREATE TRIGGER tr_cve_ref_tags_insert_fts_all AFTER INSERT ON cve_ref_tags -- FOR EACH ROW BEGIN -- INSERT INTO cve_fts_all( -- item_id, -- cve_id, -- cve_desc_id, -- pt_desc_id, -- cve_ref_id, -- v2_severity_id, -- v3_severity_id, -- -- nvd_cve_id, -- assigner, -- cve_desc, -- pt_desc, -- ref_url, -- ref_name, -- ref_source, -- ref_tag, -- v2_severity, -- v3_severity -- ) -- SELECT a.item_id, -- item_id -- a.cve_id, -- cve_id -- d.desc_id, -- cve_desc_id -- g.desc_id, -- pt_desc_id -- h.cve_ref_id, -- cve_ref_id -- j.v2_severity_id, -- v2_severity_id -- l.v3_severity_id, -- v3_severity_id -- -- -- build CVE number (FIXME: move to view?) -- 'CVE-' || a.cve_year || '-' || printf('%04d', a.cve_num), -- nvd_cve_id -- b.assigner, -- assigner -- COALESCE(d.value, ''), -- cve_desc -- COALESCE(g.value, ''), -- pt_desc -- h.url, -- ref_url -- h.name, -- ref_name -- h.source, -- ref_source -- i.tag, -- ref_tag -- COALESCE(k.name, ''), -- v2_severity -- COALESCE(m.name, '') -- v3_severity -- -- FROM cves a -- JOIN assigners b -- ON (b.assigner_id = a.assigner_id) -- LEFT JOIN cve_descriptions c -- ON (c.cve_id = a.cve_id) -- LEFT JOIN descriptions d -- ON (d.desc_id = c.desc_id) -- LEFT JOIN cve_problems e -- ON (e.cve_id = a.cve_id) -- LEFT JOIN cve_problem_descriptions f -- ON (f.pt_id = e.pt_id) -- LEFT JOIN descriptions g -- ON (g.desc_id = f.desc_id) -- JOIN cve_refs h -- ON (h.cve_id = a.cve_id) -- JOIN cve_ref_tags i -- ON (i.cve_ref_id = h.cve_ref_id) -- LEFT JOIN item_cvss_v2_impacts j -- ON (j.item_id = a.item_id) -- LEFT JOIN severities k -- ON (k.severity_id = j.v2_severity_id) -- LEFT JOIN item_cvss_v3_impacts l -- ON (l.item_id = a.item_id) -- LEFT JOIN severities m -- ON (m.severity_id = l.v3_severity_id) -- -- WHERE i.cve_ref_id = NEW.cve_ref_id -- AND i.tag = NEW.tag; -- END; -- -- -- create delete trigger on cve_ref_tags -- CREATE TRIGGER tr_cve_ref_tags_delete_fts_all BEFORE DELETE ON cve_ref_tags -- FOR EACH ROW BEGIN -- DELETE FROM cve_fts_all -- WHERE cve_ref_id = OLD.cve_ref_id -- AND ref_tag = OLD.tag; -- END; -- -- -- create insert trigger on item_cvss_v2_impacts -- CREATE TRIGGER tr_item_cvss_v2_impacts_insert_fts_all AFTER INSERT ON item_cvss_v2_impacts -- FOR EACH ROW BEGIN -- INSERT INTO cve_fts_all( -- item_id, -- cve_id, -- cve_desc_id, -- pt_desc_id, -- cve_ref_id, -- v2_severity_id, -- v3_severity_id, -- -- nvd_cve_id, -- assigner, -- cve_desc, -- pt_desc, -- ref_url, -- ref_name, -- ref_source, -- ref_tag, -- v2_severity, -- v3_severity -- ) -- SELECT a.item_id, -- item_id -- a.cve_id, -- cve_id -- d.desc_id, -- cve_desc_id -- g.desc_id, -- pt_desc_id -- h.cve_ref_id, -- cve_ref_id -- j.v2_severity_id, -- v2_severity_id -- l.v3_severity_id, -- v3_severity_id -- -- -- build CVE number (FIXME: move to view?) -- 'CVE-' || a.cve_year || '-' || printf('%04d', a.cve_num), -- nvd_cve_id -- b.assigner, -- assigner -- COALESCE(d.value, ''), -- cve_desc -- COALESCE(g.value, ''), -- pt_desc -- COALESCE(h.url, ''), -- ref_url -- COALESCE(h.name, ''), -- ref_name -- COALESCE(h.source, ''), -- ref_source -- COALESCE(i.tag, ''), -- ref_tag -- k.name, -- v2_severity -- COALESCE(m.name, '') -- v3_severity -- -- FROM cves a -- JOIN assigners b -- ON (b.assigner_id = a.assigner_id) -- LEFT JOIN cve_descriptions c -- ON (c.cve_id = a.cve_id) -- LEFT JOIN descriptions d -- ON (d.desc_id = c.desc_id) -- LEFT JOIN cve_problems e -- ON (e.cve_id = a.cve_id) -- LEFT JOIN cve_problem_descriptions f -- ON (f.pt_id = e.pt_id) -- LEFT JOIN descriptions g -- ON (g.desc_id = f.desc_id) -- LEFT JOIN cve_refs h -- ON (h.cve_id = a.cve_id) -- LEFT JOIN cve_ref_tags i -- ON (i.cve_ref_id = h.cve_ref_id) -- JOIN item_cvss_v2_impacts j -- ON (j.item_id = a.item_id) -- JOIN severities k -- ON (k.severity_id = j.v2_severity_id) -- LEFT JOIN item_cvss_v3_impacts l -- ON (l.item_id = a.item_id) -- LEFT JOIN severities m -- ON (m.severity_id = l.v3_severity_id) -- -- WHERE j.item_id = NEW.item_id -- AND j.v2_severity_id = NEW.v2_severity_id; -- END; -- -- -- create delete trigger on item_cvss_v2_impacts -- CREATE TRIGGER tr_item_cvss_v2_impacts_delete_fts_all BEFORE DELETE ON item_cvss_v2_impacts -- FOR EACH ROW BEGIN -- DELETE FROM cve_fts_all -- WHERE item_id = OLD.item_id -- AND v2_severity_id = OLD.v2_severity_id; -- END; -- -- -- create insert trigger on item_cvss_v3_impacts -- CREATE TRIGGER tr_item_cvss_v3_impacts_insert_fts_all AFTER INSERT ON item_cvss_v3_impacts -- FOR EACH ROW BEGIN -- INSERT INTO cve_fts_all( -- item_id, -- cve_id, -- cve_desc_id, -- pt_desc_id, -- cve_ref_id, -- v2_severity_id, -- v3_severity_id, -- -- nvd_cve_id, -- assigner, -- cve_desc, -- pt_desc, -- ref_url, -- ref_name, -- ref_source, -- ref_tag, -- v2_severity, -- v3_severity -- ) -- SELECT a.item_id, -- item_id -- a.cve_id, -- cve_id -- d.desc_id, -- cve_desc_id -- g.desc_id, -- pt_desc_id -- h.cve_ref_id, -- cve_ref_id -- j.v2_severity_id, -- v2_severity_id -- l.v3_severity_id, -- v3_severity_id -- -- -- build CVE number (FIXME: move to view?) -- 'CVE-' || a.cve_year || '-' || printf('%04d', a.cve_num), -- nvd_cve_id -- b.assigner, -- assigner -- COALESCE(d.value, ''), -- cve_desc -- COALESCE(g.value, ''), -- pt_desc -- COALESCE(h.url, ''), -- ref_url -- COALESCE(h.name, ''), -- ref_name -- COALESCE(h.source, ''), -- ref_source -- COALESCE(i.tag, ''), -- ref_tag -- COALESCE(k.name, ''), -- v2_severity -- m.name -- v3_severity -- -- FROM cves a -- JOIN assigners b -- ON (b.assigner_id = a.assigner_id) -- LEFT JOIN cve_descriptions c -- ON (c.cve_id = a.cve_id) -- LEFT JOIN descriptions d -- ON (d.desc_id = c.desc_id) -- LEFT JOIN cve_problems e -- ON (e.cve_id = a.cve_id) -- LEFT JOIN cve_problem_descriptions f -- ON (f.pt_id = e.pt_id) -- LEFT JOIN descriptions g -- ON (g.desc_id = f.desc_id) -- LEFT JOIN cve_refs h -- ON (h.cve_id = a.cve_id) -- LEFT JOIN cve_ref_tags i -- ON (i.cve_ref_id = h.cve_ref_id) -- LEFT JOIN item_cvss_v2_impacts j -- ON (j.item_id = a.item_id) -- LEFT JOIN severities k -- ON (k.severity_id = j.v2_severity_id) -- JOIN item_cvss_v3_impacts l -- ON (l.item_id = a.item_id) -- JOIN severities m -- ON (m.severity_id = l.v3_severity_id) -- -- WHERE l.item_id = NEW.item_id -- AND l.v3_severity_id = NEW.v3_severity_id; -- END; -- -- -- create delete trigger on item_cvss_v3_impacts -- CREATE TRIGGER tr_item_cvss_v3_impacts_delete_fts_all BEFORE DELETE ON item_cvss_v3_impacts -- FOR EACH ROW BEGIN -- DELETE FROM cve_fts_all -- WHERE item_id = OLD.item_id -- AND v3_severity_id = OLD.v3_severity_id; -- END; -- -- -- Dummy table to trigger cve_fts refreshes. -- CREATE TABLE cve_fts_refresh ( created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP ); -- create before insert trigger on cve_fts_refresh CREATE TRIGGER tr_cve_fts_refresh_before_insert BEFORE INSERT ON cve_fts_refresh FOR EACH ROW BEGIN DELETE FROM cve_fts_all; END; -- create insert trigger on item_cvss_v3_impacts CREATE TRIGGER tr_cve_fts_refresh_after_insert AFTER INSERT ON cve_fts_refresh FOR EACH ROW BEGIN INSERT INTO cve_fts_all( item_id, cve_id, cve_desc_id, pt_desc_id, cve_ref_id, v2_severity_id, v3_severity_id, nvd_cve_id, assigner, cve_desc, pt_desc, ref_url, ref_name, ref_source, ref_tag, v2_severity, v3_severity ) SELECT a.item_id, -- item_id a.cve_id, -- cve_id d.desc_id, -- cve_desc_id g.desc_id, -- pt_desc_id h.cve_ref_id, -- cve_ref_id j.v2_severity_id, -- v2_severity_id l.v3_severity_id, -- v3_severity_id n.nvd_cve_id, -- nvd_cve_id b.assigner, -- assigner COALESCE(d.value, ''), -- cve_desc COALESCE(g.value, ''), -- pt_desc COALESCE(h.url, ''), -- ref_url COALESCE(h.name, ''), -- ref_name COALESCE(h.source, ''), -- ref_source COALESCE(i.tag, ''), -- ref_tag COALESCE(k.name, ''), -- v2_severity COALESCE(m.name, '') -- v3_severity FROM cves a JOIN assigners b ON (b.assigner_id = a.assigner_id) LEFT JOIN cve_descriptions c ON (c.cve_id = a.cve_id) LEFT JOIN descriptions d ON (d.desc_id = c.desc_id) LEFT JOIN cve_problems e ON (e.cve_id = a.cve_id) LEFT JOIN cve_problem_descriptions f ON (f.pt_id = e.pt_id) LEFT JOIN descriptions g ON (g.desc_id = f.desc_id) LEFT JOIN cve_refs h ON (h.cve_id = a.cve_id) LEFT JOIN cve_ref_tags i ON (i.cve_ref_id = h.cve_ref_id) LEFT JOIN item_cvss_v2_impacts j ON (j.item_id = a.item_id) LEFT JOIN severities k ON (k.severity_id = j.v2_severity_id) LEFT JOIN item_cvss_v3_impacts l ON (l.item_id = a.item_id) LEFT JOIN severities m ON (m.severity_id = l.v3_severity_id) JOIN nvd_cve_ids n ON (n.cve_id = a.cve_id); END;