aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorPaul Duncan <pabs@pablotron.org>2022-02-20 10:52:51 -0500
committerPaul Duncan <pabs@pablotron.org>2022-02-20 10:52:51 -0500
commit14dc55d984a9791aeb7d242427a3d54f879f678c (patch)
treea3c6b0210019b155f0cddccec1b2103eaac239cb
parenta1a37bbc35ac1f793f05254edd142d0673c14c94 (diff)
downloadcvez-14dc55d984a9791aeb7d242427a3d54f879f678c.tar.bz2
cvez-14dc55d984a9791aeb7d242427a3d54f879f678c.zip
dbstore/sql/init.sql: remove per-table cve fts triggers, add size notes and TODO to cve fts refresh table
-rw-r--r--dbstore/sql/init.sql498
1 files 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