diff options
Diffstat (limited to 'dbstore/sql/init.sql')
| -rw-r--r-- | dbstore/sql/init.sql | 185 |
1 files changed, 67 insertions, 118 deletions
diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql index 0e05c6a..cc83a02 100644 --- a/dbstore/sql/init.sql +++ b/dbstore/sql/init.sql @@ -22,25 +22,6 @@ CREATE TABLE cpes ( ) ); --- create all fts table (cross join across all --- titles and references, populated by triggers) -CREATE VIRTUAL TABLE cpe_fts_all USING fts5( - -- parent CPE id - cpe_id UNINDEXED, - - -- title - title, - - -- reference URL - href, - - -- reference name - val, - - -- use porter stemming - tokenize = 'porter' -); - -- create titles table CREATE TABLE cpe_titles ( -- title primary key @@ -51,66 +32,23 @@ CREATE TABLE cpe_titles ( REFERENCES cpes(cpe_id), -- language code - lang TEXT NOT NULL - CHECK (LENGTH(lang) >= 2), + lang TEXT NOT NULL CHECK ( + -- FIXME: is this upper bound correct + LENGTH(lang) BETWEEN 2 AND 16 + ), -- text value - val TEXT NOT NULL - CHECK (LENGTH(val) > 0), + val TEXT NOT NULL CHECK ( + -- FIXME: is this upper bound correct + LENGTH(val) BETWEEN 1 AND 1024 + ), -- prevent duplicate titles of the same name UNIQUE (cpe_id, lang) ); --- create titles fts table -CREATE VIRTUAL TABLE cpe_fts_titles USING fts5( - -- parent CPE id - cpe_id UNINDEXED, - - -- title - title, - - -- use porter stemming - tokenize = 'porter' -); - --- create titles all fts insert trigger -CREATE TRIGGER tr_cpe_titles_insert_fts_all AFTER INSERT ON cpe_titles - FOR EACH ROW WHEN (NEW.lang = 'en-US') BEGIN - INSERT INTO cpe_fts_all(cpe_id, title, href, val) - SELECT NEW.cpe_id, - NEW.val, - b.href, - b.val - - FROM cpes a - JOIN cpe_refs b - ON (b.cpe_id = a.cpe_id) - - WHERE a.cpe_id = NEW.cpe_id; - END; - --- create titles delete all fts trigger -CREATE TRIGGER tr_cpe_titles_delete_fts_all BEFORE DELETE ON cpe_titles - FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN - DELETE FROM cpe_fts_all - WHERE cpe_id = OLD.cpe_id - AND title = OLD.val; - END; - --- create titles all fts insert trigger -CREATE TRIGGER tr_cpe_titles_insert_fts_titles AFTER INSERT ON cpe_titles - FOR EACH ROW WHEN (NEW.lang = 'en-US') BEGIN - INSERT INTO cpe_fts_titles(cpe_id, title) VALUES (NEW.cpe_id, NEW.val); - END; - --- create titles delete titles fts trigger -CREATE TRIGGER tr_cpe_titles_delete_fts_titles BEFORE DELETE ON cpe_titles - FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN - DELETE FROM cpe_fts_titles - WHERE cpe_id = OLD.cpe_id - AND title = OLD.val; - END; +-- create language index +CREATE INDEX in_cpe_titles_lang ON cpe_titles(lang); -- create refs table CREATE TABLE cpe_refs ( @@ -123,69 +61,78 @@ CREATE TABLE cpe_refs ( -- url href TEXT NOT NULL CHECK ( - LENGTH(href) > 0 AND - (href LIKE 'http://%' OR href LIKE 'https://%') + LENGTH(href) BETWEEN 1 AND 1024 AND ( + href LIKE 'http://%' OR + href LIKE 'https://%' OR + href LIKE 'ftp://%' + ) ), -- text description - val TEXT NOT NULL - CHECK (LENGTH(val) > 0) + val TEXT NOT NULL CHECK ( + -- FIXME: check upper bound + LENGTH(val) BETWEEN 1 AND 1024 + ) ); --- create refs fts table -CREATE VIRTUAL TABLE cpe_fts_refs USING fts5( +-- create all fts table (cross join across all +-- titles and references, populated by triggers) +CREATE VIRTUAL TABLE cpes_fts USING fts5( -- parent CPE id cpe_id UNINDEXED, - -- reference URL - href, + -- titles + titles, - -- reference name - val, + -- reference URL (no bueno w/ porter stemming) + -- href, + + -- reference names + refs, -- use porter stemming tokenize = 'porter' ); --- create refs insert trigger for all fts -CREATE TRIGGER tr_cpe_refs_insert_fts_all AFTER INSERT ON cpe_refs - FOR EACH ROW BEGIN - INSERT INTO cpe_fts_all(cpe_id, title, href, val) - SELECT NEW.cpe_id, - b.val, - NEW.href, - NEW.val - - FROM cpes a - JOIN cpe_titles b - ON (b.cpe_id = a.cpe_id) - - WHERE a.cpe_id = NEW.cpe_id - AND b.lang = 'en-US'; - END; +-- dummy table to trigger cpes_fts refreshes. +CREATE TABLE cpes_fts_refresh ( + created_at TIMESTAMP WITH TIME ZONE NOT NULL + DEFAULT CURRENT_TIMESTAMP +); --- create refs delete trigger for all fts -CREATE TRIGGER tr_cpe_refs_delete_fts_all BEFORE DELETE ON cpe_refs +-- create before insert trigger on cpes_fts_refresh +CREATE TRIGGER tr_cpes_fts_refresh_before_insert + BEFORE INSERT ON cpes_fts_refresh FOR EACH ROW BEGIN - DELETE FROM cpe_fts_all - WHERE cpe_id = OLD.cpe_id - AND href = OLD.lang - AND val = OLD.val; + DELETE FROM cpes_fts; END; --- create refs insert trigger for refs fts -CREATE TRIGGER tr_cpe_refs_insert_fts_refs AFTER INSERT ON cpe_refs +-- create insert trigger on cpes_fts_refresh +CREATE TRIGGER tr_cpes_fts_refresh_after_insert + AFTER INSERT ON cpes_fts_refresh FOR EACH ROW BEGIN - INSERT INTO cpe_fts_refs(cpe_id, href, val) VALUES (NEW.cpe_id, NEW.href, NEW.val); - END; + INSERT INTO cpes_fts(cpe_id, titles, refs) + SELECT a.cpe_id, + b.vals, + -- c.href, + c.vals --- create refs delete trigger for refs fts -CREATE TRIGGER tr_cpe_refs_delete_fts_refs BEFORE DELETE ON cpe_refs - FOR EACH ROW BEGIN - DELETE FROM cpe_fts_refs - WHERE cpe_id = OLD.cpe_id - AND href = OLD.lang - AND val = OLD.val; + FROM cpes a + + JOIN ( + SELECT cpe_id, + group_concat(val, ' ') AS vals + FROM cpe_titles + WHERE lang = 'en-US' + GROUP BY cpe_id + ) b ON (b.cpe_id = a.cpe_id) + + JOIN ( + SELECT cpe_id, + group_concat(val, ' ') AS vals + FROM cpe_refs + GROUP BY cpe_id + ) c ON (c.cpe_id = a.cpe_id); END; -- @@ -196,7 +143,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 NOT NULL CHECK ( LENGTH(cpe23) > 28 AND cpe23 LIKE 'cpe:2.3:%:%:%:%:%:%:%:%:%:%:%' ), @@ -205,6 +152,8 @@ CREATE TABLE cpe_matches ( cpe22_uri TEXT NOT NULL ); +CREATE INDEX in_cpe_matches_cpe23 ON cpe_matches(cpe23); + -- cpe match vulnerabilities CREATE TABLE cpe_match_vulnerables ( cpe_match_id INTEGER PRIMARY KEY, @@ -265,7 +214,7 @@ CREATE TABLE cpe_match_version_maxs ( REFERENCES cpe_matches(cpe_match_id) ); --- map of matching +-- map of matches to cpe IDs CREATE TABLE cpe_match_cpes ( -- CPE match ID cpe_match_id INTEGER NOT NULL @@ -927,7 +876,7 @@ CREATE VIRTUAL TABLE cisa_vulns_fts USING fts5( ); -- --- Dummy table to trigger cve_fts refreshes. +-- Dummy table to trigger cisa_vulns_fts refreshes. -- CREATE TABLE cisa_vulns_fts_refresh ( created_at TIMESTAMP WITH TIME ZONE NOT NULL |