dbstore/sql/init.sql: add initial cve tables

1 files changed, 182 insertions, 3 deletions

diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
index 5836642..a480a58 100644
--- a/dbstore/sql/init.sql
+++ b/dbstore/sql/init.sql
@@ -10,13 +10,13 @@ CREATE TABLE cpes (
   cpe_id        INTEGER PRIMARY KEY,
 
   -- cpe uri
-  cpe_uri       TEXT UNIQUE NOT NULL CHECK(
+  cpe_uri       TEXT UNIQUE NOT NULL CHECK (
                   LENGTH(cpe_uri) > 5 AND
                   cpe_uri LIKE 'cpe:/%'
                 ),
 
   -- cpe 2.3 formatting string
-  cpe23         TEXT UNIQUE NOT NULL CHECK(
+  cpe23         TEXT UNIQUE NOT NULL CHECK (
                   LENGTH(cpe23) > 28 AND
                   cpe23 LIKE 'cpe:2.3:%'
                 )
@@ -196,7 +196,7 @@ CREATE TABLE cpe_matches (
   cpe_match_id  INTEGER PRIMARY KEY,
 
   -- modern CPE 2.3 formatted string
-  cpe23         TEXT UNIQUE NOT NULL CHECK(
+  cpe23         TEXT UNIQUE NOT NULL CHECK (
                   LENGTH(cpe23) > 28 AND
                   cpe23 LIKE 'cpe:2.3:%'
                 ),
@@ -281,3 +281,182 @@ CREATE TABLE cpe_match_cpes (
 
   PRIMARY KEY (cpe_match_id, cpe_id)
 );
+
+--
+-- Descriptions of CVEs and CVE problem types.
+--
+CREATE TABLE descriptions (
+  -- description unique ID
+  desc_id       INTEGER PRIMARY KEY,
+
+  -- description language
+  lang          TEXT NOT NULL CHECK (
+                  -- FIXME: not sure about maximum value here
+                  LENGTH(lang) BETWEEN 1 AND 10
+                ),
+
+  -- description text
+  value         TEXT NOT NULL CHECK (
+                  LENGTH(value) > 0
+                )
+);
+
+--
+-- CVE feeds.
+--
+CREATE TABLE feeds (
+  -- feed unique ID
+  feed_id       INTEGER PRIMARY KEY,
+
+  -- time that feed was added
+  created_at    TIMESTAMP WITH TIME ZONE NOT NULL
+                DEFAULT CURRENT_TIMESTAMP,
+
+  -- data timestamp
+  data_time     TIMESTAMP WITH TIME ZONE NOT NULL
+);
+
+--
+-- CVE feed items.
+--
+CREATE TABLE feed_items (
+  item_id       INTEGER PRIMARY KEY,
+
+  -- feed that this CVE belongs to
+  feed_id       INTEGER NOT NULL
+                REFERENCES feeds(feed_id),
+
+  -- published timestamp
+  published_at  TIMESTAMP WITH TIME ZONE NOT NULL,
+
+  -- last modified timestamp
+  modified_at   TIMESTAMP WITH TIME ZONE NOT NULL
+);
+
+-- create index
+CREATE INDEX in_feed_items_feed_id ON feed_items(feed_id);
+
+--
+-- CVEs
+--
+-- NOTE: this combines the concept of an "item" and a "cve" from the
+-- source feed.  maybe we should split them up again?
+--
+CREATE TABLE cves (
+  -- CVE unique ID
+  cve_id        INTEGER PRIMARY KEY,
+
+  -- feed item that this CVE belongs to
+  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 9 AND 17 AND
+                  nvd_cve_id LIKE 'CVE-%'
+                ),
+
+  -- assigner, or empty string
+  assigner      TEXT NOT NULL DEFAULT ''
+);
+
+-- create index
+CREATE INDEX in_cves_item_id ON cves(item_id);
+
+--
+-- CVE descriptions
+--
+CREATE TABLE cve_descriptions (
+  -- CVE unique ID
+  cve_id        INTEGER NOT NULL
+                REFERENCES cves(cve_id),
+
+  -- description unique ID
+  desc_id       INTEGER NOT NULL
+                REFERENCES descriptions(desc_id),
+
+  PRIMARY KEY (cve_id, desc_id)
+);
+
+-- create index
+CREATE INDEX in_cve_descriptions_cve_id ON cve_descriptions(cve_id);
+
+--
+-- CVE problem types
+--
+CREATE TABLE cve_problems (
+  -- problem type unique ID
+  pt_id         INTEGER PRIMARY KEY,
+
+  -- CVE that this problem type belongs to
+  cve_id        INTEGER NOT NULL
+                REFERENCES cves(cve_id)
+);
+
+-- create index
+CREATE INDEX in_cve_problems_cve_id ON cve_problems(cve_id);
+
+--
+-- CVE problem type descriptions
+--
+CREATE TABLE cve_problem_descriptions (
+  -- problem type that this description belongs to
+  pt_id         INTEGER NOT NULL
+                REFERENCES cve_problems(pt_id),
+
+  -- description unique ID
+  desc_id       INTEGER NOT NULL
+                REFERENCES descriptions(desc_id),
+
+  PRIMARY KEY (pt_id, desc_id)
+);
+
+--
+-- CVE references
+--
+CREATE TABLE cve_refs (
+  -- cve reference unique ID
+  cve_ref_id    INTEGER PRIMARY KEY,
+
+  -- CVE unique ID
+  cve_id        INTEGER NOT NULL
+                REFERENCES cves(cve_id),
+
+  -- reference URL
+  url           TEXT NOT NULL CHECK (
+                  -- FIXME: not sure about length here
+                  LENGTH(url) BETWEEN 7 AND 1024
+                ),
+
+  -- reference name
+  name          TEXT NOT NULL CHECK (
+                  -- FIXME: not sure about length here
+                  LENGTH(name) BETWEEN 1 AND 1024
+                ),
+
+  -- reference name
+  source        TEXT NOT NULL
+);
+
+-- create index
+CREATE INDEX in_cve_refs_cve_id ON cve_refs(cve_id);
+
+--
+-- cve reference tags
+--
+CREATE TABLE cve_ref_tags (
+  cve_ref_id    INTEGER NOT NULL
+                REFERENCES cve_refs(cve_ref_id),
+
+  -- tag
+  tag           TEXT NOT NULL CHECK (
+                  -- FIXME: not sure about maximum length
+                  LENGTH(tag) BETWEEN 1 AND 128
+                ),
+
+  -- prevent duplicate tags
+  UNIQUE (cve_ref_id, tag)
+);
+
+-- create index
+CREATE INDEX in_cve_ref_tags_cve_ref_id ON cve_ref_tags(cve_ref_id);