dbstore/sql/init.sql: remove per-table cve fts triggers, add size notes and TODO to cve fts refresh table

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