add internal/dbstore

8 files changed, 259 insertions, 0 deletions

diff --git a/internal/dbstore/dbstore.go b/internal/dbstore/dbstore.go
new file mode 100644
index 0000000..416d672
--- /dev/null
+++ b/internal/dbstore/dbstore.go
@@ -0,0 +1,3 @@
+// database storage
+package dbstore
+
diff --git a/internal/dbstore/dbstore_test.go b/internal/dbstore/dbstore_test.go
new file mode 100644
index 0000000..974573d
--- /dev/null
+++ b/internal/dbstore/dbstore_test.go
@@ -0,0 +1,172 @@
+package dbstore
+
+import (
+  "compress/gzip"
+  db_sql "database/sql"
+  "encoding/xml"
+  "embed"
+  "errors"
+  "fmt"
+  _ "github.com/mattn/go-sqlite3"
+  "github.com/pablotron/cvez/cpedict"
+  io_fs "io/fs"
+  "os"
+  "testing"
+)
+
+func getTestDictionary(path string) (cpedict.Dictionary, error) {
+  var dict cpedict.Dictionary
+
+  // open test data
+  f, err := os.Open(path)
+  if err != nil {
+    return dict, err
+  }
+  defer f.Close()
+
+  // create zip reader
+  gz, err := gzip.NewReader(f)
+  if err != nil {
+    return dict, err
+  }
+  defer gz.Close()
+
+  // create xml decoder
+  d := xml.NewDecoder(gz)
+
+  // decode xml
+  if err = d.Decode(&dict); err != nil {
+    return dict, err
+  }
+
+  // return success
+  return dict, nil
+}
+//go:embed testdata/sql/*.sql
+var testSqlFs embed.FS
+
+var testSqlIds = map[string]bool {
+  "init": false,
+  "insert-cpe": true,
+  "insert-title": true,
+  "insert-ref": true,
+}
+
+func getTestQueries() (map[string]string, error) {
+  r := make(map[string]string)
+
+  for id, _ := range(testSqlIds) {
+    path := fmt.Sprintf("testdata/sql/%s.sql", id)
+    if data, err := testSqlFs.ReadFile(path); err != nil {
+      return r, err
+    } else {
+      r[id] = string(data)
+    }
+  }
+
+  return r, nil
+}
+
+func TestSimple(t *testing.T) {
+  testDbPath := "./testdata/foo.db"
+  // get queries
+  queries, err := getTestQueries()
+  if err != nil {
+    t.Error(err)
+    return
+  }
+
+  // load test CPEs
+  dict, err := getTestDictionary("testdata/test-0.xml.gz")
+  if err != nil {
+    t.Error(err)
+    return
+  }
+
+  // does test db exist?
+  if _, err = os.Stat(testDbPath); err != nil {
+    if !errors.Is(err, io_fs.ErrNotExist) {
+      t.Error(err)
+      return
+    }
+  } else if err == nil {
+    // remove test db
+    if err = os.Remove(testDbPath); err != nil {
+      t.Error(err)
+      return
+    }
+  }
+
+  // init db
+  db, err := db_sql.Open("sqlite3", testDbPath)
+  if err != nil {
+    t.Error(err)
+    return
+  }
+  defer db.Close()
+
+  // init tables
+  if _, err := db.Exec(queries["init"]); err != nil {
+    t.Error(err)
+    return
+  }
+
+  tx, err := db.Begin()
+  if err != nil {
+    t.Error(err)
+    return
+  }
+
+  // build statements
+  sts := make(map[string]*db_sql.Stmt)
+  for id, use := range(testSqlIds) {
+    if use {
+      if st, err := tx.Prepare(queries[id]); err != nil {
+        t.Error(err)
+        return
+      } else {
+        sts[id] = st
+        defer sts[id].Close()
+      }
+    }
+  }
+
+  // add items
+  for _, item := range(dict.Items) {
+    // add cpe
+    rs, err := sts["insert-cpe"].Exec(item.CpeUri, item.Cpe23Item.Name);
+    if err != nil {
+      t.Error(err)
+      return
+    }
+
+    // get last row ID
+    id, err := rs.LastInsertId()
+    if err != nil {
+      t.Error(err)
+      return
+    }
+
+    // add titles
+    for _, title := range(item.Titles) {
+      if _, err := sts["insert-title"].Exec(id, title.Lang, title.Text); err != nil {
+        t.Error(err)
+        return
+      }
+    }
+
+    // add refs
+    for _, ref := range(item.References) {
+      if _, err := sts["insert-ref"].Exec(id, ref.Href, ref.Text); err != nil {
+        t.Error(err)
+        return
+      }
+    }
+  }
+
+  // commit changes
+  if err = tx.Commit(); err != nil {
+    t.Error(err)
+    return
+  }
+}
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