dbstore/sql/init.sql: clean up indentation, add item v2 and v3 impacts

1 files changed, 234 insertions, 99 deletions

diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
index a480a58..68daae4 100644
--- a/dbstore/sql/init.sql
+++ b/dbstore/sql/init.sql
@@ -7,19 +7,19 @@ PRAGMA user_version = 314159;
 -- create cpes table
 CREATE TABLE cpes (
   -- cpe unique ID
-  cpe_id        INTEGER PRIMARY KEY,
+  cpe_id            INTEGER PRIMARY KEY,
 
   -- cpe uri
-  cpe_uri       TEXT UNIQUE NOT NULL CHECK (
-                  LENGTH(cpe_uri) > 5 AND
-                  cpe_uri LIKE 'cpe:/%'
-                ),
+  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 (
-                  LENGTH(cpe23) > 28 AND
-                  cpe23 LIKE 'cpe:2.3:%'
-                )
+  cpe23             TEXT UNIQUE NOT NULL CHECK (
+                      LENGTH(cpe23) > 28 AND
+                      cpe23 LIKE 'cpe:2.3:%'
+                    )
 );
 
 -- create all fts table (cross join across all
@@ -44,19 +44,19 @@ CREATE VIRTUAL TABLE cpe_fts_all USING fts5(
 -- create titles table
 CREATE TABLE cpe_titles (
   -- title primary key
-  cpe_title_id  INTEGER PRIMARY KEY,
+  cpe_title_id      INTEGER PRIMARY KEY,
 
   -- external CPE id
-  cpe_id        INT NOT NULL
-                REFERENCES cpes(cpe_id),
+  cpe_id            INT NOT NULL
+                    REFERENCES cpes(cpe_id),
 
   -- language code
-  lang          TEXT NOT NULL
-                CHECK (LENGTH(lang) > 0),
+  lang              TEXT NOT NULL
+                    CHECK (LENGTH(lang) > 0),
 
   -- text value
-  val           TEXT NOT NULL
-                CHECK (LENGTH(val) > 0),
+  val               TEXT NOT NULL
+                    CHECK (LENGTH(val) > 0),
 
   -- prevent duplicate titles of the same name
   UNIQUE (cpe_id, lang)
@@ -115,21 +115,21 @@ CREATE TRIGGER tr_cpe_titles_delete_fts_titles BEFORE DELETE ON cpe_titles
 -- create refs table
 CREATE TABLE cpe_refs (
   -- reference unique id
-  cpe_ref_id    INTEGER PRIMARY KEY,
+  cpe_ref_id        INTEGER PRIMARY KEY,
 
   -- external CPE id
-  cpe_id        INT NOT NULL
-                REFERENCES cpes(cpe_id),
+  cpe_id            INT NOT NULL
+                    REFERENCES cpes(cpe_id),
 
   -- url
-  href          TEXT NOT NULL CHECK (
-                  LENGTH(href) > 0 AND
-                  (href LIKE 'http://%' OR href LIKE 'https://%')
-                ),
+  href              TEXT NOT NULL CHECK (
+                      LENGTH(href) > 0 AND
+                      (href LIKE 'http://%' OR href LIKE 'https://%')
+                    ),
 
   -- text description
-  val           TEXT NOT NULL
-                CHECK (LENGTH(val) > 0)
+  val               TEXT NOT NULL
+                    CHECK (LENGTH(val) > 0)
 );
 
 -- create refs fts table
@@ -196,22 +196,22 @@ CREATE TABLE cpe_matches (
   cpe_match_id  INTEGER PRIMARY KEY,
 
   -- modern CPE 2.3 formatted string
-  cpe23         TEXT UNIQUE NOT NULL CHECK (
-                  LENGTH(cpe23) > 28 AND
-                  cpe23 LIKE 'cpe:2.3:%'
-                ),
+  cpe23             TEXT UNIQUE NOT NULL CHECK (
+                      LENGTH(cpe23) > 28 AND
+                      cpe23 LIKE 'cpe:2.3:%'
+                    ),
 
   -- old CPE 2.2 URI
-  cpe22_uri     TEXT NOT NULL
+  cpe22_uri         TEXT NOT NULL
 );
 
 -- cpe match vulnerabilities
 CREATE TABLE cpe_match_vulnerables (
-  cpe_match_id  INTEGER PRIMARY KEY,
+  cpe_match_id      INTEGER PRIMARY KEY,
 
   -- true if this match is vulnerable, and false
   -- otherwise
-  is_vulnerable BOOLEAN NOT NULL,
+  is_vulnerable     BOOLEAN NOT NULL,
 
   -- add foreign key constraint
   FOREIGN KEY (cpe_match_id)
@@ -222,7 +222,7 @@ CREATE TABLE cpe_match_vulnerables (
 -- cpe match version minimums (e.g. version starts)
 --
 CREATE TABLE cpe_match_version_mins (
-  cpe_match_id  INTEGER PRIMARY KEY,
+  cpe_match_id      INTEGER PRIMARY KEY,
 
   -- valid values
   --
@@ -230,11 +230,11 @@ CREATE TABLE cpe_match_version_mins (
   --   versionStartIncluding)
   -- * false: boundary excludes this version (e.g., it was specified by
   --   versionStartExcluding)
-  is_inclusive  BOOLEAN NOT NULL,
+  is_inclusive      BOOLEAN NOT NULL,
 
   -- minimum version
-  min_version   TEXT NOT NULL
-                CHECK (LENGTH(min_version) > 0),
+  min_version       TEXT NOT NULL
+                    CHECK (LENGTH(min_version) > 0),
 
   -- add foreign key constraint
   FOREIGN KEY (cpe_match_id)
@@ -245,7 +245,8 @@ CREATE TABLE cpe_match_version_mins (
 -- cpe match version maximums (e.g. version ends)
 --
 CREATE TABLE cpe_match_version_maxs (
-  cpe_match_id  INTEGER PRIMARY KEY,
+  -- cpe match unique id
+  cpe_match_id      INTEGER PRIMARY KEY,
 
   -- valid values
   --
@@ -253,11 +254,11 @@ CREATE TABLE cpe_match_version_maxs (
   --   versionEndIncluding)
   -- * false: boundary excludes this version (e.g., it was specified by
   --   versionEndExcluding)
-  is_inclusive  BOOLEAN NOT NULL,
+  is_inclusive      BOOLEAN NOT NULL,
 
   -- maximum version
-  max_version   TEXT NOT NULL
-                CHECK (LENGTH(max_version) > 0),
+  max_version       TEXT NOT NULL
+                    CHECK (LENGTH(max_version) > 0),
 
   -- add foreign key constraint
   FOREIGN KEY (cpe_match_id)
@@ -267,17 +268,17 @@ CREATE TABLE cpe_match_version_maxs (
 -- map of matching
 CREATE TABLE cpe_match_cpes (
   -- CPE match ID
-  cpe_match_id  INTEGER NOT NULL
-                REFERENCES cpe_matches(cpe_match_id),
+  cpe_match_id      INTEGER NOT NULL
+                    REFERENCES cpe_matches(cpe_match_id),
 
   -- CPE ID
-  cpe_id        INTEGER NOT NULL
-                REFERENCES cpes(cpe_id),
+  cpe_id            INTEGER NOT NULL
+                    REFERENCES cpes(cpe_id),
 
   -- nullable cpe22 URI
   -- normally we would want this in a separate table, but
   -- we don't care that much about this value
-  cpe22_uri     TEXT,
+  cpe22_uri         TEXT,
 
   PRIMARY KEY (cpe_match_id, cpe_id)
 );
@@ -287,18 +288,18 @@ CREATE TABLE cpe_match_cpes (
 --
 CREATE TABLE descriptions (
   -- description unique ID
-  desc_id       INTEGER PRIMARY KEY,
+  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
-                ),
+  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
-                )
+  value             TEXT NOT NULL CHECK (
+                      LENGTH(value) > 0
+                    )
 );
 
 --
@@ -306,31 +307,31 @@ CREATE TABLE descriptions (
 --
 CREATE TABLE feeds (
   -- feed unique ID
-  feed_id       INTEGER PRIMARY KEY,
+  feed_id           INTEGER PRIMARY KEY,
 
   -- time that feed was added
-  created_at    TIMESTAMP WITH TIME ZONE NOT NULL
-                DEFAULT CURRENT_TIMESTAMP,
+  created_at        TIMESTAMP WITH TIME ZONE NOT NULL
+                    DEFAULT CURRENT_TIMESTAMP,
 
   -- data timestamp
-  data_time     TIMESTAMP WITH TIME ZONE NOT NULL
+  data_time         TIMESTAMP WITH TIME ZONE NOT NULL
 );
 
 --
 -- CVE feed items.
 --
 CREATE TABLE feed_items (
-  item_id       INTEGER PRIMARY KEY,
+  item_id           INTEGER PRIMARY KEY,
 
   -- feed that this CVE belongs to
-  feed_id       INTEGER NOT NULL
-                REFERENCES feeds(feed_id),
+  feed_id           INTEGER NOT NULL
+                    REFERENCES feeds(feed_id),
 
   -- published timestamp
-  published_at  TIMESTAMP WITH TIME ZONE NOT NULL,
+  published_at      TIMESTAMP WITH TIME ZONE NOT NULL,
 
   -- last modified timestamp
-  modified_at   TIMESTAMP WITH TIME ZONE NOT NULL
+  modified_at       TIMESTAMP WITH TIME ZONE NOT NULL
 );
 
 -- create index
@@ -344,20 +345,20 @@ CREATE INDEX in_feed_items_feed_id ON feed_items(feed_id);
 --
 CREATE TABLE cves (
   -- CVE unique ID
-  cve_id        INTEGER PRIMARY KEY,
+  cve_id            INTEGER PRIMARY KEY,
 
   -- feed item that this CVE belongs to
-  item_id       INTEGER NOT NULL
-                REFERENCES feed_items(item_id),
+  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-%'
-                ),
+  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 ''
+  assigner          TEXT NOT NULL DEFAULT ''
 );
 
 -- create index
@@ -368,12 +369,12 @@ CREATE INDEX in_cves_item_id ON cves(item_id);
 --
 CREATE TABLE cve_descriptions (
   -- CVE unique ID
-  cve_id        INTEGER NOT NULL
-                REFERENCES cves(cve_id),
+  cve_id            INTEGER NOT NULL
+                    REFERENCES cves(cve_id),
 
   -- description unique ID
-  desc_id       INTEGER NOT NULL
-                REFERENCES descriptions(desc_id),
+  desc_id           INTEGER NOT NULL
+                    REFERENCES descriptions(desc_id),
 
   PRIMARY KEY (cve_id, desc_id)
 );
@@ -386,11 +387,11 @@ CREATE INDEX in_cve_descriptions_cve_id ON cve_descriptions(cve_id);
 --
 CREATE TABLE cve_problems (
   -- problem type unique ID
-  pt_id         INTEGER PRIMARY KEY,
+  pt_id             INTEGER PRIMARY KEY,
 
   -- CVE that this problem type belongs to
-  cve_id        INTEGER NOT NULL
-                REFERENCES cves(cve_id)
+  cve_id            INTEGER NOT NULL
+                    REFERENCES cves(cve_id)
 );
 
 -- create index
@@ -401,12 +402,12 @@ CREATE INDEX in_cve_problems_cve_id ON cve_problems(cve_id);
 --
 CREATE TABLE cve_problem_descriptions (
   -- problem type that this description belongs to
-  pt_id         INTEGER NOT NULL
-                REFERENCES cve_problems(pt_id),
+  pt_id             INTEGER NOT NULL
+                    REFERENCES cve_problems(pt_id),
 
   -- description unique ID
-  desc_id       INTEGER NOT NULL
-                REFERENCES descriptions(desc_id),
+  desc_id           INTEGER NOT NULL
+                    REFERENCES descriptions(desc_id),
 
   PRIMARY KEY (pt_id, desc_id)
 );
@@ -416,26 +417,26 @@ CREATE TABLE cve_problem_descriptions (
 --
 CREATE TABLE cve_refs (
   -- cve reference unique ID
-  cve_ref_id    INTEGER PRIMARY KEY,
+  cve_ref_id        INTEGER PRIMARY KEY,
 
   -- CVE unique ID
-  cve_id        INTEGER NOT NULL
-                REFERENCES cves(cve_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
-                ),
+  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
-                ),
+  name              TEXT NOT NULL CHECK (
+                      -- FIXME: not sure about length here
+                      LENGTH(name) BETWEEN 1 AND 1024
+                    ),
 
   -- reference name
-  source        TEXT NOT NULL
+  source            TEXT NOT NULL
 );
 
 -- create index
@@ -445,14 +446,14 @@ 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),
+  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
-                ),
+  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)
@@ -460,3 +461,137 @@ CREATE TABLE cve_ref_tags (
 
 -- create index
 CREATE INDEX in_cve_ref_tags_cve_ref_id ON cve_ref_tags(cve_ref_id);
+
+--
+-- CVSS severities.
+--
+CREATE TABLE severities (
+  -- severity unique ID
+  severity_id       INTEGER PRIMARY KEY,
+
+  -- severity name
+  name              TEXT UNIQUE NOT NULL CHECK (
+                      -- limit to know severities
+                      name IN ('NONE', 'LOW', 'MEDIUM', 'HIGH', 'CRITICAL')
+                    ),
+
+  -- severity sort order, from low to high
+  sort              SMALLINT UNIQUE NOT NULL
+);
+
+-- add severities
+INSERT INTO severities(severity_id, name, sort) VALUES
+  (1, 'NONE', 1),
+  (2, 'LOW', 2),
+  (3, 'MEDIUM', 3),
+  (4, 'HIGH', 4),
+  (5, 'CRITICAL', 5);
+
+--
+-- item cvss v2 impacts
+--
+CREATE TABLE item_cvss_v2_impacts (
+  -- feed item that this impact belongs to
+  item_id           INTEGER NOT NULL
+                    REFERENCES feed_items(item_id),
+
+  -- cvss v2 version
+  -- FIXME: don't need this, check during import
+  -- v2_version        TEXT NOT NULL CHECK (
+  --                     LENGTH(v2_version) > 0
+  --                   ),
+
+  -- cvss v2 vector
+  v2_vector         TEXT NOT NULL CHECK (
+                      -- TODO: enforce this
+                      LENGTH(v2_vector) > 0
+                    ),
+
+  -- cvss v2 base score
+  -- (NOTE: multiplied by 10 and stored as a tinyint)
+  v2_base_score     TINYINT NOT NULL CHECK (
+                      v2_base_score BETWEEN 0 AND 100
+                    ),
+
+  -- cvss v2 severity
+  v2_severity_id    INTEGER NOT NULL
+                    REFERENCES severeties(severity_id),
+
+  -- cvss v2 impact score
+  -- (NOTE: multiplied by 10 and stored as a tinyint)
+  v2_impact_score   TINYINT NOT NULL CHECK (
+                      v2_impact_score BETWEEN 0 AND 100
+                    ),
+
+  -- cvss v2 exploitability score
+  -- (NOTE: multiplied by 10 and stored as a tinyint)
+  v2_expl_score     TINYINT NOT NULL CHECK (
+                      v2_expl_score BETWEEN 0 AND 100
+                    ),
+
+  -- cvss v2 insufficient info
+  v2_insufficient_info  BOOLEAN NOT NULL,
+
+  -- cvss v2 insufficient info
+  v2_obtain_all_priv    BOOLEAN NOT NULL,
+
+  -- cvss v2 obtain user privileges
+  v2_obtain_user_priv   BOOLEAN NOT NULL,
+
+  -- cvss v2 obtain other privileges
+  v2_obtain_other_priv  BOOLEAN NOT NULL,
+
+  -- cvss v2 user interaction required?
+  v2_ui_req             BOOLEAN NOT NULL
+);
+
+-- create indices
+CREATE INDEX in_item_cvss_v2_impacts_item_id ON item_cvss_v2_impacts(item_id);
+CREATE INDEX in_item_cvss_v2_impacts_v2_severity_id ON item_cvss_v2_impacts(v2_severity_id);
+
+--
+-- item cvss v3 impacts
+--
+CREATE TABLE item_cvss_v3_impacts (
+  -- feed item that this impact belongs to
+  item_id           INTEGER NOT NULL
+                    REFERENCES feed_items(item_id),
+
+  -- FIXME: don't need this, check during import
+  -- v3_version        TEXT NOT NULL CHECK (
+  --                     LENGTH(v3_version) > 0
+  --                   ),
+
+  -- cvss v3 vector
+  v3_vector         TEXT NOT NULL CHECK (
+                      -- TODO: enforce this
+                      LENGTH(v3_vector) > 0 AND
+                      v3_vector LIKE 'CVSS:3.1/%'
+                    ),
+
+  -- cvss v3 base score
+  -- (NOTE: multiplied by 10 and stored as a tinyint)
+  v3_base_score     TINYINT NOT NULL CHECK (
+                      v3_base_score BETWEEN 0 AND 100
+                    ),
+
+  -- cvss v3 base severity
+  v3_severity_id    INTEGER NOT NULL
+                    REFERENCES severeties(severity_id),
+
+  -- cvss v3 exploitability score
+  -- (NOTE: multiplied by 10 and stored as a tinyint)
+  v3_expl_score     TINYINT NOT NULL CHECK (
+                      v3_expl_score BETWEEN 0 AND 100
+                    ),
+
+  -- cvss v3 impact score
+  -- (NOTE: multiplied by 10 and stored as a tinyint)
+  v3_impact_score   TINYINT NOT NULL CHECK (
+                      v3_impact_score BETWEEN 0 AND 100
+                    )
+);
+
+-- create indices
+CREATE INDEX in_item_cvss_v3_impacts_item_id ON item_cvss_v3_impacts(item_id);
+CREATE INDEX in_item_cvss_v3_impacts_v3_severity_id ON item_cvss_v3_impacts(v3_severity_id);