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

3 files changed, 44 insertions, 8 deletions

diff --git a/dbstore/dbstore.go b/dbstore/dbstore.go
index b8cf990..98f3b61 100644
--- a/dbstore/dbstore.go
+++ b/dbstore/dbstore.go
@@ -397,6 +397,12 @@ func (me DbStore) addDescriptions(ctx context.Context, tx Tx, ds []nvd_feed.Desc
   return r, nil
 }
 
+// Add assigner if they are not already present in the database
+func (me DbStore) addAssigner(ctx context.Context, tx Tx, itemId int64, assigner string) error {
+  _, err := tx.Exec(ctx, "feed/insert-assigner", db_sql.Named("assigner", assigner))
+  return err
+}
+
 // Add CVE.
 func (me DbStore) addCve(ctx context.Context, tx Tx, itemId int64, cve nvd_feed.Cve) error {
   // cve ID
@@ -407,8 +413,14 @@ func (me DbStore) addCve(ctx context.Context, tx Tx, itemId int64, cve nvd_feed.
     return err
   }
 
+  // add cve assigner
+  if err := me.addAssigner(ctx, tx, itemId, cve.Metadata.Assigner); err != nil {
+    return err
+  }
+
   // add cve
-  rs, err := tx.Exec(ctx, "feed/insert-cve", itemId, cve.Metadata.Id.String(), cve.Metadata.Assigner)
+  nvdId := cve.Metadata.Id
+  rs, err := tx.Exec(ctx, "feed/insert-cve", itemId, nvdId.Year(), nvdId.Number(), cve.Metadata.Assigner)
   if  err != nil {
     return err
   }
@@ -660,6 +672,7 @@ func (me DbStore) addFeed(ctx context.Context, tx Tx, feed nvd_feed.Feed) (int64
 // query IDs used by AddCpeMatches()
 var addCveFeedQueryIds = []string {
   "feed/insert",
+  "feed/insert-assigner",
   "feed/insert-cve",
   "feed/insert-cve-desc",
   "feed/insert-cve-problem",
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