diff options
Diffstat (limited to 'dbstore/sql')
| -rw-r--r-- | dbstore/sql/init.sql | 185 |
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); |