dbstore/sql/init.sql: add cve_fts_all (note: 10x test db size increase, from 31M to 320M)

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;