From 82b100ca5cc56adb97d43d0c8df698af84214925 Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Fri, 4 Feb 2022 17:43:15 -0500 Subject: add internal/dbstore --- internal/dbstore/dbstore.go | 3 + internal/dbstore/dbstore_test.go | 172 +++++++++++++++++++++++++ internal/dbstore/testdata/sql/fill-fts.sql | 10 ++ internal/dbstore/testdata/sql/init.sql | 71 ++++++++++ internal/dbstore/testdata/sql/insert-cpe.sql | 1 + internal/dbstore/testdata/sql/insert-ref.sql | 1 + internal/dbstore/testdata/sql/insert-title.sql | 1 + internal/dbstore/testdata/test-0.xml.gz | Bin 0 -> 1359 bytes 8 files changed, 259 insertions(+) create mode 100644 internal/dbstore/dbstore.go create mode 100644 internal/dbstore/dbstore_test.go create mode 100644 internal/dbstore/testdata/sql/fill-fts.sql create mode 100644 internal/dbstore/testdata/sql/init.sql create mode 100644 internal/dbstore/testdata/sql/insert-cpe.sql create mode 100644 internal/dbstore/testdata/sql/insert-ref.sql create mode 100644 internal/dbstore/testdata/sql/insert-title.sql create mode 100644 internal/dbstore/testdata/test-0.xml.gz 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 Binary files /dev/null and b/internal/dbstore/testdata/test-0.xml.gz differ -- cgit v1.2.3