dbstore: store cve year and number instead of CVE ID, add and use assigners table

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