From 14dc55d984a9791aeb7d242427a3d54f879f678c Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Sun, 20 Feb 2022 10:52:51 -0500 Subject: dbstore/sql/init.sql: remove per-table cve fts triggers, add size notes and TODO to cve fts refresh table --- dbstore/sql/init.sql | 498 ++------------------------------------------------- 1 file changed, 12 insertions(+), 486 deletions(-) diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql index b8f56e9..be5e4fb 100644 --- a/dbstore/sql/init.sql +++ b/dbstore/sql/init.sql @@ -693,492 +693,6 @@ CREATE VIRTUAL TABLE cve_fts_all USING fts5( 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. -- @@ -1195,6 +709,18 @@ CREATE TRIGGER tr_cve_fts_refresh_before_insert END; -- create insert trigger on item_cvss_v3_impacts +-- +-- Notes: +-- * test sizes: +-- - no index: ~31M +-- - per-table triggers: ~320M +-- - full refresh trigger: ~120M +-- +-- TODO: +-- * use group_concat() to concatenate tags +-- * remove all IDs except cve_id +-- * remove severities +-- CREATE TRIGGER tr_cve_fts_refresh_after_insert AFTER INSERT ON cve_fts_refresh FOR EACH ROW BEGIN -- cgit v1.2.3