diff options
| -rw-r--r-- | dbstore/sql/init.sql | 929 |
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; |