aboutsummaryrefslogtreecommitdiff
path: root/internal/dbstore
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
parent014dccd167a20f063515ca6afd36601c8cf5830c (diff)
downloadcvez-82b100ca5cc56adb97d43d0c8df698af84214925.tar.bz2
cvez-82b100ca5cc56adb97d43d0c8df698af84214925.zip
add internal/dbstore
Diffstat (limited to 'internal/dbstore')
-rw-r--r--internal/dbstore/dbstore.go3
-rw-r--r--internal/dbstore/dbstore_test.go172
-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
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
Binary files differ