diff options
| -rw-r--r-- | dbstore/sql/init.sql | 24 |
1 files changed, 11 insertions, 13 deletions
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) |