diff options
| -rw-r--r-- | dbstore/sql/init.sql | 556 |
1 files changed, 556 insertions, 0 deletions
diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql index 82d8561..c406dd6 100644 --- a/dbstore/sql/init.sql +++ b/dbstore/sql/init.sql @@ -613,3 +613,559 @@ CREATE TABLE item_cvss_v3_impacts ( -- 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; |