From a558815292d83f21097b190bf5a8baae6c6997c7 Mon Sep 17 00:00:00 2001
From: Paul Duncan <pabs@pablotron.org>
Date: Sat, 19 Feb 2022 16:47:38 -0500
Subject: dbstore/sql/init.sql: s/severeties/severities/, tighten cpe23, lang,
 and cve ID constraints

---
 dbstore/sql/init.sql | 24 +++++++++++-------------
 1 file changed, 11 insertions(+), 13 deletions(-)

(limited to 'dbstore')

diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
index 68daae4..9ea7aa6 100644
--- a/dbstore/sql/init.sql
+++ b/dbstore/sql/init.sql
@@ -18,7 +18,7 @@ CREATE TABLE cpes (
   -- cpe 2.3 formatting string
   cpe23             TEXT UNIQUE NOT NULL CHECK (
                       LENGTH(cpe23) > 28 AND
-                      cpe23 LIKE 'cpe:2.3:%'
+                      cpe23 LIKE 'cpe:2.3:%:%:%:%:%:%:%:%:%:%:%'
                     )
 );
 
@@ -52,7 +52,7 @@ CREATE TABLE cpe_titles (
 
   -- language code
   lang              TEXT NOT NULL
-                    CHECK (LENGTH(lang) > 0),
+                    CHECK (LENGTH(lang) >= 2),
 
   -- text value
   val               TEXT NOT NULL
@@ -198,7 +198,7 @@ CREATE TABLE cpe_matches (
   -- modern CPE 2.3 formatted string
   cpe23             TEXT UNIQUE NOT NULL CHECK (
                       LENGTH(cpe23) > 28 AND
-                      cpe23 LIKE 'cpe:2.3:%'
+                      cpe23 LIKE 'cpe:2.3:%:%:%:%:%:%:%:%:%:%:%'
                     ),
 
   -- old CPE 2.2 URI
@@ -278,7 +278,7 @@ CREATE TABLE cpe_match_cpes (
   -- 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 NOT NULL,
 
   PRIMARY KEY (cpe_match_id, cpe_id)
 );
@@ -291,10 +291,8 @@ CREATE TABLE descriptions (
   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 (LENGTH(lang) >= 2),
 
   -- description text
   value             TEXT NOT NULL CHECK (
@@ -353,8 +351,8 @@ CREATE TABLE cves (
 
   -- 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-%'
+                      LENGTH(nvd_cve_id) BETWEEN 1 AND 17 AND
+                      nvd_cve_id LIKE 'CVE-____-%'
                     ),
 
   -- assigner, or empty string
@@ -515,7 +513,7 @@ CREATE TABLE item_cvss_v2_impacts (
 
   -- cvss v2 severity
   v2_severity_id    INTEGER NOT NULL
-                    REFERENCES severeties(severity_id),
+                    REFERENCES severities(severity_id),
 
   -- cvss v2 impact score
   -- (NOTE: multiplied by 10 and stored as a tinyint)
@@ -566,7 +564,7 @@ CREATE TABLE item_cvss_v3_impacts (
   v3_vector         TEXT NOT NULL CHECK (
                       -- TODO: enforce this
                       LENGTH(v3_vector) > 0 AND
-                      v3_vector LIKE 'CVSS:3.1/%'
+                      v3_vector LIKE 'CVSS:3._/%'
                     ),
 
   -- cvss v3 base score
@@ -577,7 +575,7 @@ CREATE TABLE item_cvss_v3_impacts (
 
   -- cvss v3 base severity
   v3_severity_id    INTEGER NOT NULL
-                    REFERENCES severeties(severity_id),
+                    REFERENCES severities(severity_id),
 
   -- cvss v3 exploitability score
   -- (NOTE: multiplied by 10 and stored as a tinyint)
-- 
cgit v1.2.3