aboutsummaryrefslogtreecommitdiff
path: root/dbstore
diff options
context:
space:
mode:
authorPaul Duncan <pabs@pablotron.org>2022-02-20 09:41:20 -0500
committerPaul Duncan <pabs@pablotron.org>2022-02-20 09:41:20 -0500
commit52f286cb19f2d4b93a726cfeee4afad0d8bc6b61 (patch)
tree6578a8d944125a91eb7c754eec9aefad375a05ee /dbstore
parentc1a3a44dc43d9cbfe074cb5d8fd09e36112f67a0 (diff)
downloadcvez-52f286cb19f2d4b93a726cfeee4afad0d8bc6b61.tar.bz2
cvez-52f286cb19f2d4b93a726cfeee4afad0d8bc6b61.zip
dbstore/sql/init.sql: add cve_nvd_ids, remove existing cve_all triggers, add cve_fts_refresh with triggers
Diffstat (limited to 'dbstore')
-rw-r--r--dbstore/sql/init.sql929
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;