mv internal/dbstore dbstore

1 files changed, 0 insertions, 180 deletions

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;