diff options
author | Paul Duncan <pabs@pablotron.org> | 2022-02-05 02:37:00 -0500 |
---|---|---|
committer | Paul Duncan <pabs@pablotron.org> | 2022-02-05 02:37:00 -0500 |
commit | 22fd269069b8e47bc40fbd8681243bdd4bd61a47 (patch) | |
tree | e16bbe17d985d1d567f83926fd1b7a417948083a /internal/dbstore/sql | |
parent | 56cc399430d127cb628b3abe84bc653d2b0ce59b (diff) | |
download | cvez-22fd269069b8e47bc40fbd8681243bdd4bd61a47.tar.bz2 cvez-22fd269069b8e47bc40fbd8681243bdd4bd61a47.zip |
mv internal/dbstore dbstore
Diffstat (limited to 'internal/dbstore/sql')
-rw-r--r-- | internal/dbstore/sql/cpe-search-all.sql | 46 | ||||
-rw-r--r-- | internal/dbstore/sql/cpe-search-ref.sql | 47 | ||||
-rw-r--r-- | internal/dbstore/sql/cpe-search-title.sql | 44 | ||||
-rw-r--r-- | internal/dbstore/sql/fill-fts.sql | 10 | ||||
-rw-r--r-- | internal/dbstore/sql/init.sql | 180 | ||||
-rw-r--r-- | internal/dbstore/sql/insert-cpe.sql | 1 | ||||
-rw-r--r-- | internal/dbstore/sql/insert-ref.sql | 1 | ||||
-rw-r--r-- | internal/dbstore/sql/insert-title.sql | 1 | ||||
-rw-r--r-- | internal/dbstore/sql/old-cpe-search-all.sql | 69 | ||||
-rw-r--r-- | internal/dbstore/sql/table-exists.sql | 6 |
10 files changed, 0 insertions, 405 deletions
diff --git a/internal/dbstore/sql/cpe-search-all.sql b/internal/dbstore/sql/cpe-search-all.sql deleted file mode 100644 index 3260902..0000000 --- a/internal/dbstore/sql/cpe-search-all.sql +++ /dev/null @@ -1,46 +0,0 @@ -SELECT a.cpe_id, - a.cpe23, - COALESCE(c.titles, '[]') AS titles, - COALESCE(d.refs, '[]') AS refs, - b.rank - - FROM cpes a - JOIN ( - -- limit to "best" match (e.g., lowest rank) for given CPE - -- NOTE: it's not clear to me whether cross-index bm25() - -- comparisons are valid - SELECT cpe_id, - MIN(rank) AS rank - FROM cpe_fts_all a - WHERE cpe_fts_all MATCH :q - GROUP BY cpe_id - ) b ON (b.cpe_id = a.cpe_id) - - -- get all titles, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'lang', lang, - 'text', val - )) AS titles - - FROM cpe_titles - - GROUP BY cpe_id - ) c ON (c.cpe_id = b.cpe_id) - - -- get all refs, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'href', href, - 'text', val - )) AS refs - - FROM cpe_refs - - GROUP BY cpe_id - ) d ON (d.cpe_id = b.cpe_id) - - -- order by rank - ORDER BY b.rank diff --git a/internal/dbstore/sql/cpe-search-ref.sql b/internal/dbstore/sql/cpe-search-ref.sql deleted file mode 100644 index a2d3be6..0000000 --- a/internal/dbstore/sql/cpe-search-ref.sql +++ /dev/null @@ -1,47 +0,0 @@ -SELECT a.cpe_id, - a.cpe23, - COALESCE(c.titles, '[]') AS titles, - COALESCE(d.refs, '[]') AS refs, - b.rank - - -- find matching cpes - FROM cpes a - JOIN ( - -- there can be multiple matching references for a given - -- CPE, so use MIN() to limit the match to the most - -- "relevant", according to bm25() - SELECT cpe_id, - MIN(rank) AS rank - FROM cpe_fts_refs - WHERE cpe_fts_refs MATCH :q - GROUP BY cpe_id - ) b ON (b.cpe_id = a.cpe_id) - - -- get all titles, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'lang', lang, - 'text', val - )) AS titles - - FROM cpe_titles - - GROUP BY cpe_id - ) c ON (c.cpe_id = b.cpe_id) - - -- get all refs, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'href', href, - 'text', val - )) AS refs - - FROM cpe_refs - - GROUP BY cpe_id - ) d ON (d.cpe_id = b.cpe_id) - - -- order by rank - ORDER BY b.rank diff --git a/internal/dbstore/sql/cpe-search-title.sql b/internal/dbstore/sql/cpe-search-title.sql deleted file mode 100644 index 7421ffc..0000000 --- a/internal/dbstore/sql/cpe-search-title.sql +++ /dev/null @@ -1,44 +0,0 @@ -SELECT a.cpe_id, - a.cpe23, - COALESCE(c.titles, '[]') AS titles, - COALESCE(d.refs, '[]') AS refs, - b.rank - - -- find matching cpes - FROM cpes a - JOIN ( - SELECT cpe_id, - MIN(rank) AS rank - FROM cpe_fts_titles - WHERE cpe_fts_titles MATCH :q - GROUP BY cpe_id - ) b ON (b.cpe_id = a.cpe_id) - - -- get all titles, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'lang', lang, - 'text', val - )) AS titles - - FROM cpe_titles - - GROUP BY cpe_id - ) c ON (c.cpe_id = b.cpe_id) - - -- get all refs, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'href', href, - 'text', val - )) AS refs - - FROM cpe_refs - - GROUP BY cpe_id - ) d ON (d.cpe_id = b.cpe_id) - - -- order by rank - ORDER BY b.rank; diff --git a/internal/dbstore/sql/fill-fts.sql b/internal/dbstore/sql/fill-fts.sql deleted file mode 100644 index 5329ca7..0000000 --- a/internal/dbstore/sql/fill-fts.sql +++ /dev/null @@ -1,10 +0,0 @@ --- populate fts table -INSERT INTO cpe_fts(rowid, title) - SELECT a.cpe_id, - COALESCE(b.val, '') - - FROM cpes a - LEFT JOIN cpe_titles b - ON (b.cpe_id = a.cpe_id) - - WHERE b.lang = 'en-US'; diff --git a/internal/dbstore/sql/init.sql b/internal/dbstore/sql/init.sql deleted file mode 100644 index 37eef54..0000000 --- a/internal/dbstore/sql/init.sql +++ /dev/null @@ -1,180 +0,0 @@ --- enable foreign keys -PRAGMA foreign_keys = true; - --- create cpes table -CREATE TABLE cpes ( - -- cpe unique ID - cpe_id INTEGER PRIMARY KEY, - - -- cpe uri - cpe_uri TEXT UNIQUE NOT NULL, - - -- cpe 2.3 formatting string - cpe23 TEXT UNIQUE NOT NULL -); - --- 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 - cpe_title_id INTEGER PRIMARY KEY, - - -- external CPE id - cpe_id INT NOT NULL - REFERENCES cpes(cpe_id), - - -- language code - lang TEXT NOT NULL - CHECK (LENGTH(lang) > 0), - - -- text value - val TEXT NOT NULL - CHECK (LENGTH(val) > 0), - - -- 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 refs table -CREATE TABLE cpe_refs ( - -- reference unique id - cpe_ref_id INTEGER PRIMARY KEY, - - -- external CPE id - cpe_id INT NOT NULL - REFERENCES cpes(cpe_id), - - -- url - href TEXT NOT NULL - CHECK (LENGTH(href) > 0), - - -- text description - val TEXT NOT NULL - CHECK (LENGTH(val) > 0) -); - --- create refs fts table -CREATE VIRTUAL TABLE cpe_fts_refs USING fts5( - -- parent CPE id - cpe_id UNINDEXED, - - -- reference URL - href, - - -- reference name - val, - - -- 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; - --- create refs delete trigger for all fts -CREATE TRIGGER tr_cpe_refs_delete_fts_all BEFORE DELETE ON cpe_refs - FOR EACH ROW BEGIN - DELETE FROM cpe_fts_all - WHERE cpe_id = OLD.cpe_id - AND href = OLD.lang - AND val = OLD.val; - END; - --- create refs insert trigger for refs fts -CREATE TRIGGER tr_cpe_refs_insert_fts_refs AFTER INSERT ON cpe_refs - FOR EACH ROW BEGIN - INSERT INTO cpe_fts_refs(cpe_id, href, val) VALUES (NEW.cpe_id, NEW.href, NEW.val); - END; - --- 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; - END; - -PRAGMA user_version = 314159; diff --git a/internal/dbstore/sql/insert-cpe.sql b/internal/dbstore/sql/insert-cpe.sql deleted file mode 100644 index dca1c2b..0000000 --- a/internal/dbstore/sql/insert-cpe.sql +++ /dev/null @@ -1 +0,0 @@ -INSERT INTO cpes(cpe_uri, cpe23) VALUES (?, ?); diff --git a/internal/dbstore/sql/insert-ref.sql b/internal/dbstore/sql/insert-ref.sql deleted file mode 100644 index a39ab1c..0000000 --- a/internal/dbstore/sql/insert-ref.sql +++ /dev/null @@ -1 +0,0 @@ -INSERT INTO cpe_refs(cpe_id, href, val) VALUES (?, ?, ?); diff --git a/internal/dbstore/sql/insert-title.sql b/internal/dbstore/sql/insert-title.sql deleted file mode 100644 index 5045d95..0000000 --- a/internal/dbstore/sql/insert-title.sql +++ /dev/null @@ -1 +0,0 @@ -INSERT INTO cpe_titles(cpe_id, lang, val) VALUES (?, ?, ?); diff --git a/internal/dbstore/sql/old-cpe-search-all.sql b/internal/dbstore/sql/old-cpe-search-all.sql deleted file mode 100644 index 18e286f..0000000 --- a/internal/dbstore/sql/old-cpe-search-all.sql +++ /dev/null @@ -1,69 +0,0 @@ -SELECT a.cpe_id, - a.cpe23, - COALESCE(c.titles, '[]') AS titles, - COALESCE(d.refs, '[]') AS refs, - b.rank - - FROM cpes a - JOIN ( - -- limit to "best" match (e.g., lowest rank) for given CPE - -- NOTE: it's not clear to me whether cross-index bm25() - -- comparisons are valid - SELECT a.cpe_id, - MIN(a.rank) AS rank - - FROM ( - -- find title matches - SELECT b.cpe_id, - a.rank AS rank - - FROM cpe_titles_fts a - JOIN cpe_titles b - ON (b.cpe_title_id = a.rowid) - - WHERE cpe_titles_fts MATCH :q - - UNION ALL - - -- find reference matches - SELECT b.cpe_id, - a.rank - - FROM cpe_refs_fts a - JOIN cpe_refs b - ON (b.cpe_ref_id = a.rowid) - - WHERE cpe_refs_fts MATCH :q - ) a - - GROUP BY a.cpe_id - ) b ON (b.cpe_id = a.cpe_id) - - -- get all titles, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'lang', lang, - 'text', val - )) AS titles - - FROM cpe_titles - - GROUP BY cpe_id - ) c ON (c.cpe_id = b.cpe_id) - - -- get all refs, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'href', href, - 'text', val - )) AS refs - - FROM cpe_refs - - GROUP BY cpe_id - ) d ON (d.cpe_id = b.cpe_id) - - -- order by rank - ORDER BY b.rank diff --git a/internal/dbstore/sql/table-exists.sql b/internal/dbstore/sql/table-exists.sql deleted file mode 100644 index 09d32a4..0000000 --- a/internal/dbstore/sql/table-exists.sql +++ /dev/null @@ -1,6 +0,0 @@ -SELECT EXISTS( - SELECT 1 - FROM sqlite_schema - WHERE type = 'table' - AND name = ? -) |