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/sql/init.sql | 180 +++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 180 insertions(+)
 create mode 100644 dbstore/sql/init.sql

(limited to 'dbstore/sql/init.sql')

diff --git a/dbstore/sql/init.sql b/dbstore/sql/init.sql
new file mode 100644
index 0000000..37eef54
--- /dev/null
+++ b/dbstore/sql/init.sql
@@ -0,0 +1,180 @@
+-- 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;
-- 
cgit v1.2.3