aboutsummaryrefslogtreecommitdiff
path: root/internal/dbstore/testdata
diff options
context:
space:
mode:
authorPaul Duncan <pabs@pablotron.org>2022-02-04 17:43:15 -0500
committerPaul Duncan <pabs@pablotron.org>2022-02-04 17:43:15 -0500
commit82b100ca5cc56adb97d43d0c8df698af84214925 (patch)
treea60e7e5202ac07bdce841cb05ac50d90160ff1e1 /internal/dbstore/testdata
parent014dccd167a20f063515ca6afd36601c8cf5830c (diff)
downloadcvez-82b100ca5cc56adb97d43d0c8df698af84214925.tar.bz2
cvez-82b100ca5cc56adb97d43d0c8df698af84214925.zip
add internal/dbstore
Diffstat (limited to 'internal/dbstore/testdata')
-rw-r--r--internal/dbstore/testdata/sql/fill-fts.sql10
-rw-r--r--internal/dbstore/testdata/sql/init.sql71
-rw-r--r--internal/dbstore/testdata/sql/insert-cpe.sql1
-rw-r--r--internal/dbstore/testdata/sql/insert-ref.sql1
-rw-r--r--internal/dbstore/testdata/sql/insert-title.sql1
-rw-r--r--internal/dbstore/testdata/test-0.xml.gzbin0 -> 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
new file mode 100644
index 0000000..110e965
--- /dev/null
+++ b/internal/dbstore/testdata/test-0.xml.gz
Binary files differ