aboutsummaryrefslogtreecommitdiff
path: root/dbstore/sql
diff options
context:
space:
mode:
Diffstat (limited to 'dbstore/sql')
-rw-r--r--dbstore/sql/feed/insert-cve.sql5
-rw-r--r--dbstore/sql/init.sql32
2 files changed, 30 insertions, 7 deletions
diff --git a/dbstore/sql/feed/insert-cve.sql b/dbstore/sql/feed/insert-cve.sql
index 02c0a8a..9e62048 100644
--- a/dbstore/sql/feed/insert-cve.sql
+++ b/dbstore/sql/feed/insert-cve.sql
@@ -1,2 +1,5 @@
-- add cve
-INSERT INTO cves(item_id, nvd_cve_id, assigner) VALUES (?, ?, ?);
+INSERT INTO cves(item_id, cve_year, cve_num, assigner_id) VALUES (
+ ?, ?, ?,
+ (SELECT assigner_id FROM assigners WHERE assigner = ?)
+);
diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
index 9ea7aa6..82d8561 100644
--- a/dbstore/sql/init.sql
+++ b/dbstore/sql/init.sql
@@ -336,6 +336,19 @@ CREATE TABLE feed_items (
CREATE INDEX in_feed_items_feed_id ON feed_items(feed_id);
--
+-- CVE assigners
+--
+CREATE TABLE assigners (
+ -- assigner unique ID
+ assigner_id INTEGER PRIMARY KEY,
+
+ -- assigner value
+ assigner TEXT UNIQUE NOT NULL CHECK (
+ LENGTH(assigner) > 0
+ )
+);
+
+--
-- CVEs
--
-- NOTE: this combines the concept of an "item" and a "cve" from the
@@ -349,14 +362,21 @@ CREATE TABLE cves (
item_id INTEGER NOT NULL
REFERENCES feed_items(item_id),
- -- NVD CVE ID (e.g. "CVE-YYYY-XXXX)
- nvd_cve_id TEXT NOT NULL CHECK (
- LENGTH(nvd_cve_id) BETWEEN 1 AND 17 AND
- nvd_cve_id LIKE 'CVE-____-%'
+ -- year of NVD CVE ID, (e.g. the "XXXX" in "CVE-XXXX-YYYY")
+ cve_year SMALLINT NOT NULL CHECK (
+ cve_year BETWEEN 1999 AND 2126
+ ),
+
+ -- number of NVD CVE ID, (e.g. the "YYYY" in "CVE-XXXX-YYYY")
+ cve_num SMALLINT NOT NULL CHECK (
+ -- there is no upper bound, but let's pick
+ -- something sane
+ cve_num BETWEEN 1 AND 33554432
),
- -- assigner, or empty string
- assigner TEXT NOT NULL DEFAULT ''
+ -- assigner ID
+ assigner_id INTEGER NOT NULL
+ REFERENCES assigners(assigner_id)
);
-- create index