aboutsummaryrefslogtreecommitdiff
path: root/dbstore
diff options
context:
space:
mode:
authorPaul Duncan <pabs@pablotron.org>2022-02-19 22:53:59 -0500
committerPaul Duncan <pabs@pablotron.org>2022-02-19 22:53:59 -0500
commita84fa75165f0ec80a3bf604939ca55f9076c1282 (patch)
tree600317f1328a8eda6f531d5a1403bed623516dc2 /dbstore
parent6384ecac4b0d5f47e532fd9de660cf8ddcb5f04b (diff)
downloadcvez-a84fa75165f0ec80a3bf604939ca55f9076c1282.tar.bz2
cvez-a84fa75165f0ec80a3bf604939ca55f9076c1282.zip
dbstore: add CveSearch() and test (but not test data)
Diffstat (limited to 'dbstore')
-rw-r--r--dbstore/cvesearchrow.go15
-rw-r--r--dbstore/sql/feed/search.sql32
2 files changed, 47 insertions, 0 deletions
diff --git a/dbstore/cvesearchrow.go b/dbstore/cvesearchrow.go
new file mode 100644
index 0000000..4128347
--- /dev/null
+++ b/dbstore/cvesearchrow.go
@@ -0,0 +1,15 @@
+package dbstore
+
+import (
+ "github.com/pablotron/cvez/feed"
+)
+
+// CveSearch() result
+type CveSearchRow struct {
+ Id int64 `json:"cve_id"` // internal database CVE ID
+ CveId feed.CveId `json:"nvd_cve_id"` // NVD CVE ID
+ Description string `json:"description"` // CVE description (english)
+ V3BaseScore feed.Score `json:"v3_base_score"` // CVSSv3 base score
+ V3Severity feed.Severity `json:"v3_severity"` // CVSSv3 severity
+ Rank float32 `json:"rank"` // search result rank
+}
diff --git a/dbstore/sql/feed/search.sql b/dbstore/sql/feed/search.sql
new file mode 100644
index 0000000..cb80470
--- /dev/null
+++ b/dbstore/sql/feed/search.sql
@@ -0,0 +1,32 @@
+-- search CVEs
+-- FIXME: should this be moved to cve/search
+SELECT COALESCE(json_group_array(json_object(
+ 'cve_id', b.cve_id,
+ 'nvd_cve_id', printf('CVE-%4d-%04d', b.cve_year, b.cve_num),
+ 'description', d.value,
+ 'v3_base_score', e.v3_base_score / 10.0,
+ 'v3_severity', f.name,
+ 'rank', a.min_rank
+ )), '[]')
+
+ FROM (
+ SELECT cve_id,
+ MIN(rank) AS min_rank
+ FROM cve_fts_all
+ WHERE cve_fts_all MATCH :q
+ GROUP BY cve_id
+ ) a
+ JOIN cves b
+ ON (b.cve_id = a.cve_id)
+ JOIN cve_descriptions c
+ ON (c.cve_id = b.cve_id)
+ JOIN descriptions d
+ ON (d.desc_id = c.desc_id)
+ JOIN item_cvss_v3_impacts e
+ ON (e.item_id = b.item_id)
+ JOIN severities f
+ ON (f.severity_id = e.v3_severity_id)
+
+ WHERE d.lang = 'en'
+
+ ORDER BY a.min_rank;