diff options
author | Paul Duncan <pabs@pablotron.org> | 2022-02-04 17:43:15 -0500 |
---|---|---|
committer | Paul Duncan <pabs@pablotron.org> | 2022-02-04 17:43:15 -0500 |
commit | 82b100ca5cc56adb97d43d0c8df698af84214925 (patch) | |
tree | a60e7e5202ac07bdce841cb05ac50d90160ff1e1 /internal/dbstore/testdata | |
parent | 014dccd167a20f063515ca6afd36601c8cf5830c (diff) | |
download | cvez-82b100ca5cc56adb97d43d0c8df698af84214925.tar.bz2 cvez-82b100ca5cc56adb97d43d0c8df698af84214925.zip |
add internal/dbstore
Diffstat (limited to 'internal/dbstore/testdata')
-rw-r--r-- | internal/dbstore/testdata/sql/fill-fts.sql | 10 | ||||
-rw-r--r-- | internal/dbstore/testdata/sql/init.sql | 71 | ||||
-rw-r--r-- | internal/dbstore/testdata/sql/insert-cpe.sql | 1 | ||||
-rw-r--r-- | internal/dbstore/testdata/sql/insert-ref.sql | 1 | ||||
-rw-r--r-- | internal/dbstore/testdata/sql/insert-title.sql | 1 | ||||
-rw-r--r-- | internal/dbstore/testdata/test-0.xml.gz | bin | 0 -> 1359 bytes |
6 files changed, 84 insertions, 0 deletions
diff --git a/internal/dbstore/testdata/sql/fill-fts.sql b/internal/dbstore/testdata/sql/fill-fts.sql new file mode 100644 index 0000000..5329ca7 --- /dev/null +++ b/internal/dbstore/testdata/sql/fill-fts.sql @@ -0,0 +1,10 @@ +-- 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/testdata/sql/init.sql b/internal/dbstore/testdata/sql/init.sql new file mode 100644 index 0000000..1aa2941 --- /dev/null +++ b/internal/dbstore/testdata/sql/init.sql @@ -0,0 +1,71 @@ +-- enable foreign keys +PRAGMA foreign_keys = true; + +-- create cpes table +CREATE TABLE cpes ( + cpe_id INTEGER PRIMARY KEY, + + -- cpe uri + cpe_uri TEXT UNIQUE NOT NULL, + + -- cpe 2.3 formatting string + cpe23 TEXT UNIQUE NOT NULL +); + +-- create titles table +CREATE TABLE cpe_titles ( + -- external CPE id + cpe_id INT NOT NULL + REFERENCES cpes(cpe_id), + + -- language code + lang TEXT NOT NULL, + + -- text value + val TEXT NOT NULL, + + UNIQUE (cpe_id, lang) +); + +-- create titles fts table +CREATE VIRTUAL TABLE cpe_titles_fts USING fts5(title, tokenize = 'porter'); + +-- create titles insert trigger +CREATE TRIGGER tr_cpe_titles_insert AFTER INSERT ON cpe_titles + FOR EACH ROW WHEN (NEW.lang = 'en-US') BEGIN + INSERT INTO cpe_titles_fts(rowid, title) VALUES (NEW.cpe_id, NEW.val); + END; + +-- create titles delete trigger +CREATE TRIGGER tr_cpe_titles_delete BEFORE DELETE ON cpe_titles + FOR EACH ROW WHEN (OLD.lang = 'en-US') BEGIN + DELETE FROM cpe_titles_fts WHERE rowid = OLD.cpe_id; + END; + +-- create refs table +CREATE TABLE cpe_refs ( + -- external CPE id + cpe_id INT NOT NULL + REFERENCES cpes(cpe_id), + + -- url + href TEXT NOT NULL, + + -- text description + val TEXT NOT NULL +); + +-- create refs fts +CREATE VIRTUAL TABLE cpe_refs_fts USING fts5(cpe_id UNINDEXED, href, val); + +-- create refs insert trigger +CREATE TRIGGER tr_cpe_refs_insert AFTER INSERT ON cpe_refs + FOR EACH ROW BEGIN + INSERT INTO cpe_refs_fts(cpe_id, href, val) VALUES (NEW.cpe_id, NEW.href, NEW.val); + END; + +-- create refs delete trigger +CREATE TRIGGER tr_cpe_refs_delete BEFORE DELETE ON cpe_refs + FOR EACH ROW BEGIN + DELETE FROM cpe_refs_fts WHERE rowid = OLD.cpe_id; + END; diff --git a/internal/dbstore/testdata/sql/insert-cpe.sql b/internal/dbstore/testdata/sql/insert-cpe.sql new file mode 100644 index 0000000..dca1c2b --- /dev/null +++ b/internal/dbstore/testdata/sql/insert-cpe.sql @@ -0,0 +1 @@ +INSERT INTO cpes(cpe_uri, cpe23) VALUES (?, ?); diff --git a/internal/dbstore/testdata/sql/insert-ref.sql b/internal/dbstore/testdata/sql/insert-ref.sql new file mode 100644 index 0000000..a39ab1c --- /dev/null +++ b/internal/dbstore/testdata/sql/insert-ref.sql @@ -0,0 +1 @@ +INSERT INTO cpe_refs(cpe_id, href, val) VALUES (?, ?, ?); diff --git a/internal/dbstore/testdata/sql/insert-title.sql b/internal/dbstore/testdata/sql/insert-title.sql new file mode 100644 index 0000000..5045d95 --- /dev/null +++ b/internal/dbstore/testdata/sql/insert-title.sql @@ -0,0 +1 @@ +INSERT INTO cpe_titles(cpe_id, lang, val) VALUES (?, ?, ?); diff --git a/internal/dbstore/testdata/test-0.xml.gz b/internal/dbstore/testdata/test-0.xml.gz Binary files differnew file mode 100644 index 0000000..110e965 --- /dev/null +++ b/internal/dbstore/testdata/test-0.xml.gz |