From 22fd269069b8e47bc40fbd8681243bdd4bd61a47 Mon Sep 17 00:00:00 2001
From: Paul Duncan <pabs@pablotron.org>
Date: Sat, 5 Feb 2022 02:37:00 -0500
Subject: mv internal/dbstore dbstore

---
 dbstore/testdata/sql/fill-fts.sql     | 10 +++++
 dbstore/testdata/sql/init.sql         | 73 +++++++++++++++++++++++++++++++++++
 dbstore/testdata/sql/insert-cpe.sql   |  1 +
 dbstore/testdata/sql/insert-ref.sql   |  1 +
 dbstore/testdata/sql/insert-title.sql |  1 +
 dbstore/testdata/sql/table-exists.sql |  6 +++
 6 files changed, 92 insertions(+)
 create mode 100644 dbstore/testdata/sql/fill-fts.sql
 create mode 100644 dbstore/testdata/sql/init.sql
 create mode 100644 dbstore/testdata/sql/insert-cpe.sql
 create mode 100644 dbstore/testdata/sql/insert-ref.sql
 create mode 100644 dbstore/testdata/sql/insert-title.sql
 create mode 100644 dbstore/testdata/sql/table-exists.sql

(limited to 'dbstore/testdata/sql')

diff --git a/dbstore/testdata/sql/fill-fts.sql b/dbstore/testdata/sql/fill-fts.sql
new file mode 100644
index 0000000..5329ca7
--- /dev/null
+++ b/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/dbstore/testdata/sql/init.sql b/dbstore/testdata/sql/init.sql
new file mode 100644
index 0000000..a08b2f8
--- /dev/null
+++ b/dbstore/testdata/sql/init.sql
@@ -0,0 +1,73 @@
+-- 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;
+
+PRAGMA user_version = 314159;
diff --git a/dbstore/testdata/sql/insert-cpe.sql b/dbstore/testdata/sql/insert-cpe.sql
new file mode 100644
index 0000000..dca1c2b
--- /dev/null
+++ b/dbstore/testdata/sql/insert-cpe.sql
@@ -0,0 +1 @@
+INSERT INTO cpes(cpe_uri, cpe23) VALUES (?, ?);
diff --git a/dbstore/testdata/sql/insert-ref.sql b/dbstore/testdata/sql/insert-ref.sql
new file mode 100644
index 0000000..a39ab1c
--- /dev/null
+++ b/dbstore/testdata/sql/insert-ref.sql
@@ -0,0 +1 @@
+INSERT INTO cpe_refs(cpe_id, href, val) VALUES (?, ?, ?);
diff --git a/dbstore/testdata/sql/insert-title.sql b/dbstore/testdata/sql/insert-title.sql
new file mode 100644
index 0000000..5045d95
--- /dev/null
+++ b/dbstore/testdata/sql/insert-title.sql
@@ -0,0 +1 @@
+INSERT INTO cpe_titles(cpe_id, lang, val) VALUES (?, ?, ?);
diff --git a/dbstore/testdata/sql/table-exists.sql b/dbstore/testdata/sql/table-exists.sql
new file mode 100644
index 0000000..09d32a4
--- /dev/null
+++ b/dbstore/testdata/sql/table-exists.sql
@@ -0,0 +1,6 @@
+SELECT EXISTS(
+  SELECT 1
+    FROM sqlite_schema
+   WHERE type = 'table'
+     AND name = ?
+)
-- 
cgit v1.2.3