dbstore/sql/init.sql: add cve_nvd_ids, remove existing cve_all triggers, add cve_fts_refresh with triggers

1 files changed, 512 insertions, 417 deletions

diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
index c406dd6..d38ca37 100644
--- a/dbstore/sql/init.sql
+++ b/dbstore/sql/init.sql
@@ -383,6 +383,14 @@ CREATE TABLE cves (
 CREATE INDEX in_cves_item_id ON cves(item_id);
 
 --
+-- NVD CVE IDs (e.g. "CVE-YYYY-NNNN").
+--
+CREATE VIEW cve_nvd_ids AS
+  SELECT cve_id,
+         'CVE-' || a.cve_year || '-' || printf('%04d', a.cve_num) AS nvd_cve_id
+    FROM cves;
+
+--
 -- CVE descriptions
 --
 CREATE TABLE cve_descriptions (
@@ -685,412 +693,510 @@ 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
+-- -- 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;
+-- 
 
-        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;
+--
+-- Dummy table to trigger cve_fts refreshes.
+--
+CREATE TABLE cve_fts_refresh (
+  created_at        TIMESTAMP WITH TIME ZONE NOT NULL
+                    DEFAULT CURRENT_TIMESTAMP
+);
 
--- 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
+-- create before insert trigger on cve_fts_refresh
+CREATE TRIGGER tr_cve_fts_refresh_before_insert
+  BEFORE INSERT ON cve_fts_refresh
   FOR EACH ROW BEGIN
-    DELETE FROM cve_fts_all
-     WHERE item_id = OLD.item_id
-       AND v2_severity_id = OLD.v2_severity_id;
+    DELETE FROM cve_fts_all;
   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
+CREATE TRIGGER tr_cve_fts_refresh_after_insert
+  AFTER INSERT ON cve_fts_refresh
   FOR EACH ROW BEGIN
     INSERT INTO cve_fts_all(
       item_id,
@@ -1119,9 +1225,7 @@ CREATE TRIGGER tr_item_cvss_v3_impacts_insert_fts_all AFTER INSERT ON item_cvss_
              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
+             n.nvd_cve_id, -- nvd_cve_id
              b.assigner, -- assigner
              COALESCE(d.value, ''), -- cve_desc
              COALESCE(g.value, ''), -- pt_desc
@@ -1130,7 +1234,7 @@ CREATE TRIGGER tr_item_cvss_v3_impacts_insert_fts_all AFTER INSERT ON item_cvss_
              COALESCE(h.source, ''), -- ref_source
              COALESCE(i.tag, ''), -- ref_tag
              COALESCE(k.name, ''), -- v2_severity
-             m.name -- v3_severity
+             COALESCE(m.name, '') -- v3_severity
 
         FROM cves a
         JOIN assigners b
@@ -1153,19 +1257,10 @@ CREATE TRIGGER tr_item_cvss_v3_impacts_insert_fts_all AFTER INSERT ON item_cvss_
           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
+        LEFT JOIN item_cvss_v3_impacts l
           ON (l.item_id = a.item_id)
-        JOIN severities m
+        LEFT 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;
+        JOIN nvd_cve_ids n
+          ON (n.cve_id = a.cve_id);
   END;