From 63a3351c9527f9f774862329b56f487339b09903 Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Sat, 5 Feb 2022 11:06:55 -0500 Subject: dbstore: refactor, separate cpesearchrow and cpesearchtype, add cpesearchtype_test --- dbstore/cpesearchrow.go | 50 +++++++++++++++++++++++++++++++ dbstore/cpesearchrow_test.go | 69 +++++++++++++++++++++++++++++++++++++++++++ dbstore/cpesearchtype.go | 13 ++++++++ dbstore/cpesearchtype_test.go | 24 +++++++++++++++ dbstore/dbstore.go | 55 ---------------------------------- dbstore/dbstore_test.go | 62 -------------------------------------- 6 files changed, 156 insertions(+), 117 deletions(-) create mode 100644 dbstore/cpesearchrow.go create mode 100644 dbstore/cpesearchrow_test.go create mode 100644 dbstore/cpesearchtype.go create mode 100644 dbstore/cpesearchtype_test.go diff --git a/dbstore/cpesearchrow.go b/dbstore/cpesearchrow.go new file mode 100644 index 0000000..87de9a8 --- /dev/null +++ b/dbstore/cpesearchrow.go @@ -0,0 +1,50 @@ +package dbstore + +import ( + db_sql "database/sql" + "encoding/json" + "github.com/pablotron/cvez/cpedict" +) + +// title search result +type CpeSearchRow struct { + // Database CPE ID + CpeId int64 `json:"cpe_id"` + + // v2.3 formatting string + Cpe23 string `json:"cpe23"` + + // titles + Titles []cpedict.Title `json:"titles"` + + // references + Refs []cpedict.Reference `json:"refs"` + + // search result rank + Rank float32 `json:"rank"` +} + +// Unmarshal CPE search row from row set. +func unmarshalCpeSearchRow(rows *db_sql.Rows) (CpeSearchRow, error) { + var r CpeSearchRow + var titles string + var refs string + + // get row values + if err := rows.Scan(&r.CpeId, &r.Cpe23, &titles, &refs, &r.Rank); err != nil { + return r, err + } + + // unmarshal titles + if err := json.Unmarshal([]byte(titles), &r.Titles); err != nil { + return r, err + } + + // unmarshal refs + if err := json.Unmarshal([]byte(refs), &r.Refs); err != nil { + return r, err + } + + // return sccess + return r, nil +} diff --git a/dbstore/cpesearchrow_test.go b/dbstore/cpesearchrow_test.go new file mode 100644 index 0000000..bfcfc61 --- /dev/null +++ b/dbstore/cpesearchrow_test.go @@ -0,0 +1,69 @@ +package dbstore + +import ( + "context" + "testing" +) + +// sqlite> select a.cpe23 from cpes a join (select cpe_id, min(rank) as rank from cpe_fts_all where cpe_fts_all match 'advisory' group by cpe_id) b on (b.cpe_id = a.cpe_id) order by b.rank; +// sqlite> select a.cpe23 from cpes a join (select cpe_id, min(rank) as rank from cpe_fts_all where cpe_fts_all match 'advisory AND book' group by cpe_id) b on (b.cpe_id = a.cpe_id) order by b.rank; +// cpe:2.3:a:\$0.99_kindle_books_project:\$0.99_kindle_books:6:*:*:*:*:android:*:* +// +// sqlite> select c.cpe_id, c.cpe23, a.rank from cpe_titles_fts a join cpe_titles b on (b.cpe_title_id = a.rowid) join cpes c on (c.cpe_id = b.cpe_id) where cpe_titles_fts match 'project' order by a.rank; +// 2|cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:-:*:*:*:*:node.js:*:*|-0.775759508773217 +// 3|cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.1.0:*:*:*:*:node.js:*:*|-0.66983333682734 +// 4|cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.2.0:*:*:*:*:node.js:*:*|-0.66983333682734 +// 5|cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.2.1:*:*:*:*:node.js:*:*|-0.66983333682734 +// 1|cpe:2.3:a:\$0.99_kindle_books_project:\$0.99_kindle_books:6:*:*:*:*:android:*:*|-0.545655647541265 +// +// sqlite> select a.cpe23 from cpes a join (select cpe_id, min(rank) as rank from cpe_fts_refs where cpe_fts_refs match 'advisory' group by cpe_id) b on (b.cpe_id = a.cpe_id) order by b.rank; +// cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:-:*:*:*:*:node.js:*:* +// cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.1.0:*:*:*:*:node.js:*:* +// cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.2.0:*:*:*:*:node.js:*:* +// cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.2.1:*:*:*:*:node.js:*:* +// cpe:2.3:a:360totalsecurity:360_total_security:12.1.0.1005:*:*:*:*:*:*:* +// cpe:2.3:a:\$0.99_kindle_books_project:\$0.99_kindle_books:6:*:*:*:*:android:*:* + +func TestUnmarshalCpeSearchRow(t *testing.T) { + tests := []struct { + name string + sql string + } {{ + name: "scan", + sql: "select true", + }, { + name: "titles", + sql: "select 1, 'asdf', 'bad', '[]', 0.0", + }, { + name: "titles", + sql: "select 1, 'asdf', '[]', 'bad', 0.0", + }} + + ctx := context.Background() + path := "./testdata/test-unmarshalcpesearchrow-fail.db" + + // create db + db, err := createTestDb(ctx, path) + if err != nil { + t.Error(err) + return + } + + for _, test := range(tests) { + t.Run(test.name, func(t *testing.T) { + // exec dummy query + rows, err := db.db.QueryContext(ctx, test.sql) + if err != nil { + t.Error(err) + return + } + + rows.Next() + + if got, err := unmarshalCpeSearchRow(rows); err == nil { + t.Errorf("got %v, exp error", got) + } + }) + } +} + diff --git a/dbstore/cpesearchtype.go b/dbstore/cpesearchtype.go new file mode 100644 index 0000000..45865dc --- /dev/null +++ b/dbstore/cpesearchtype.go @@ -0,0 +1,13 @@ +// database storage +package dbstore + +//go:generate stringer -linecomment -type=CpeSearchType + +// CPE search type +type CpeSearchType byte + +const ( + CpeSearchAll CpeSearchType = iota //cpe-search-all + CpeSearchTitle // cpe-search-title + CpeSearchRef // cpe-search-ref +) diff --git a/dbstore/cpesearchtype_test.go b/dbstore/cpesearchtype_test.go new file mode 100644 index 0000000..1421d3c --- /dev/null +++ b/dbstore/cpesearchtype_test.go @@ -0,0 +1,24 @@ +package dbstore + +import "testing" + +func TestCpeSearchTypeString(t *testing.T) { + tests := []struct { + val CpeSearchType + exp string + } { + { CpeSearchAll, "cpe-search-all" }, + { CpeSearchTitle, "cpe-search-title" }, + { CpeSearchRef, "cpe-search-ref" }, + { CpeSearchType(255), "CpeSearchType(255)" }, + } + + for _, test := range(tests) { + t.Run(test.exp, func(t *testing.T) { + got := test.val.String() + if got != test.exp { + t.Errorf("got \"%s\", exp \"%s\"", got, test.exp) + } + }) + } +} diff --git a/dbstore/dbstore.go b/dbstore/dbstore.go index 00b8fe2..7f22f3b 100644 --- a/dbstore/dbstore.go +++ b/dbstore/dbstore.go @@ -5,7 +5,6 @@ import ( "context" db_sql "database/sql" "embed" - "encoding/json" "fmt" _ "github.com/mattn/go-sqlite3" "github.com/pablotron/cvez/cpedict" @@ -160,60 +159,6 @@ func (me DbStore) AddCpeDictionary(ctx context.Context, dict cpedict.Dictionary) return tx.Commit() } -//go:generate stringer -linecomment -type=CpeSearchType - -// CPE search type -type CpeSearchType byte - -const ( - CpeSearchAll CpeSearchType = iota //cpe-search-all - CpeSearchTitle // cpe-search-title - CpeSearchRef // cpe-search-ref -) - -// title search result -type CpeSearchRow struct { - // Database CPE ID - CpeId int64 `json:"cpe_id"` - - // v2.3 formatting string - Cpe23 string `json:"cpe23"` - - // titles - Titles []cpedict.Title `json:"titles"` - - // references - Refs []cpedict.Reference `json:"refs"` - - // search result rank - Rank float32 `json:"rank"` -} - -// Unmarshal CPE search row from row set. -func unmarshalCpeSearchRow(rows *db_sql.Rows) (CpeSearchRow, error) { - var r CpeSearchRow - var titles string - var refs string - - // get row values - if err := rows.Scan(&r.CpeId, &r.Cpe23, &titles, &refs, &r.Rank); err != nil { - return r, err - } - - // unmarshal titles - if err := json.Unmarshal([]byte(titles), &r.Titles); err != nil { - return r, err - } - - // unmarshal refs - if err := json.Unmarshal([]byte(refs), &r.Refs); err != nil { - return r, err - } - - // return sccess - return r, nil -} - // search CPEs func (me DbStore) CpeSearch( ctx context.Context, diff --git a/dbstore/dbstore_test.go b/dbstore/dbstore_test.go index c2b0c64..829d3c3 100644 --- a/dbstore/dbstore_test.go +++ b/dbstore/dbstore_test.go @@ -436,68 +436,6 @@ func TestAddCpeDictionaryFail(t *testing.T) { } } -// sqlite> select a.cpe23 from cpes a join (select cpe_id, min(rank) as rank from cpe_fts_all where cpe_fts_all match 'advisory' group by cpe_id) b on (b.cpe_id = a.cpe_id) order by b.rank; -// sqlite> select a.cpe23 from cpes a join (select cpe_id, min(rank) as rank from cpe_fts_all where cpe_fts_all match 'advisory AND book' group by cpe_id) b on (b.cpe_id = a.cpe_id) order by b.rank; -// cpe:2.3:a:\$0.99_kindle_books_project:\$0.99_kindle_books:6:*:*:*:*:android:*:* -// -// sqlite> select c.cpe_id, c.cpe23, a.rank from cpe_titles_fts a join cpe_titles b on (b.cpe_title_id = a.rowid) join cpes c on (c.cpe_id = b.cpe_id) where cpe_titles_fts match 'project' order by a.rank; -// 2|cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:-:*:*:*:*:node.js:*:*|-0.775759508773217 -// 3|cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.1.0:*:*:*:*:node.js:*:*|-0.66983333682734 -// 4|cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.2.0:*:*:*:*:node.js:*:*|-0.66983333682734 -// 5|cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.2.1:*:*:*:*:node.js:*:*|-0.66983333682734 -// 1|cpe:2.3:a:\$0.99_kindle_books_project:\$0.99_kindle_books:6:*:*:*:*:android:*:*|-0.545655647541265 -// -// sqlite> select a.cpe23 from cpes a join (select cpe_id, min(rank) as rank from cpe_fts_refs where cpe_fts_refs match 'advisory' group by cpe_id) b on (b.cpe_id = a.cpe_id) order by b.rank; -// cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:-:*:*:*:*:node.js:*:* -// cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.1.0:*:*:*:*:node.js:*:* -// cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.2.0:*:*:*:*:node.js:*:* -// cpe:2.3:a:\@thi.ng\/egf_project:\@thi.ng\/egf:0.2.1:*:*:*:*:node.js:*:* -// cpe:2.3:a:360totalsecurity:360_total_security:12.1.0.1005:*:*:*:*:*:*:* -// cpe:2.3:a:\$0.99_kindle_books_project:\$0.99_kindle_books:6:*:*:*:*:android:*:* - -func TestUnmarshalCpeSearchRow(t *testing.T) { - tests := []struct { - name string - sql string - } {{ - name: "scan", - sql: "select true", - }, { - name: "titles", - sql: "select 1, 'asdf', 'bad', '[]', 0.0", - }, { - name: "titles", - sql: "select 1, 'asdf', '[]', 'bad', 0.0", - }} - - ctx := context.Background() - path := "./testdata/test-unmarshalcpesearchrow-fail.db" - - // create db - db, err := createTestDb(ctx, path) - if err != nil { - t.Error(err) - return - } - - for _, test := range(tests) { - t.Run(test.name, func(t *testing.T) { - // exec dummy query - rows, err := db.db.QueryContext(ctx, test.sql) - if err != nil { - t.Error(err) - return - } - - rows.Next() - - if got, err := unmarshalCpeSearchRow(rows); err == nil { - t.Errorf("got %v, exp error", got) - } - }) - } -} - func TestCpeSearch(t *testing.T) { path := "./testdata/test-search.db" ctx := context.Background() -- cgit v1.2.3