aboutsummaryrefslogtreecommitdiff
path: root/dbstore/sql
diff options
context:
space:
mode:
authorPaul Duncan <pabs@pablotron.org>2022-02-19 09:34:25 -0500
committerPaul Duncan <pabs@pablotron.org>2022-02-19 09:34:25 -0500
commit248001ac1d2a95131062624c46f4c5824a8371a7 (patch)
tree04513d122edefc3dcfb4db3c8c915beaa352a0d4 /dbstore/sql
parent479aa477c7a7d1711c3266fe626fb5ed544ac64c (diff)
downloadcvez-248001ac1d2a95131062624c46f4c5824a8371a7.tar.bz2
cvez-248001ac1d2a95131062624c46f4c5824a8371a7.zip
dbstore/sql/init.sql: add initial cve tables
Diffstat (limited to 'dbstore/sql')
-rw-r--r--dbstore/sql/init.sql185
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);