From 22fd269069b8e47bc40fbd8681243bdd4bd61a47 Mon Sep 17 00:00:00 2001 From: Paul Duncan Date: Sat, 5 Feb 2022 02:37:00 -0500 Subject: mv internal/dbstore dbstore --- internal/dbstore/cpesearchtype_string.go | 25 -- internal/dbstore/dbstore.go | 255 ------------------- internal/dbstore/dbstore_test.go | 335 ------------------------- internal/dbstore/sql/cpe-search-all.sql | 46 ---- internal/dbstore/sql/cpe-search-ref.sql | 47 ---- internal/dbstore/sql/cpe-search-title.sql | 44 ---- internal/dbstore/sql/fill-fts.sql | 10 - internal/dbstore/sql/init.sql | 180 ------------- internal/dbstore/sql/insert-cpe.sql | 1 - internal/dbstore/sql/insert-ref.sql | 1 - internal/dbstore/sql/insert-title.sql | 1 - internal/dbstore/sql/old-cpe-search-all.sql | 69 ----- internal/dbstore/sql/table-exists.sql | 6 - internal/dbstore/testdata/sql/fill-fts.sql | 10 - internal/dbstore/testdata/sql/init.sql | 73 ------ 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/sql/table-exists.sql | 6 - internal/dbstore/testdata/test-0.xml.gz | Bin 1359 -> 0 bytes 20 files changed, 1112 deletions(-) delete mode 100644 internal/dbstore/cpesearchtype_string.go delete mode 100644 internal/dbstore/dbstore.go delete mode 100644 internal/dbstore/dbstore_test.go delete mode 100644 internal/dbstore/sql/cpe-search-all.sql delete mode 100644 internal/dbstore/sql/cpe-search-ref.sql delete mode 100644 internal/dbstore/sql/cpe-search-title.sql delete mode 100644 internal/dbstore/sql/fill-fts.sql delete mode 100644 internal/dbstore/sql/init.sql delete mode 100644 internal/dbstore/sql/insert-cpe.sql delete mode 100644 internal/dbstore/sql/insert-ref.sql delete mode 100644 internal/dbstore/sql/insert-title.sql delete mode 100644 internal/dbstore/sql/old-cpe-search-all.sql delete mode 100644 internal/dbstore/sql/table-exists.sql delete mode 100644 internal/dbstore/testdata/sql/fill-fts.sql delete mode 100644 internal/dbstore/testdata/sql/init.sql delete mode 100644 internal/dbstore/testdata/sql/insert-cpe.sql delete mode 100644 internal/dbstore/testdata/sql/insert-ref.sql delete mode 100644 internal/dbstore/testdata/sql/insert-title.sql delete mode 100644 internal/dbstore/testdata/sql/table-exists.sql delete mode 100644 internal/dbstore/testdata/test-0.xml.gz (limited to 'internal') diff --git a/internal/dbstore/cpesearchtype_string.go b/internal/dbstore/cpesearchtype_string.go deleted file mode 100644 index 1d4826e..0000000 --- a/internal/dbstore/cpesearchtype_string.go +++ /dev/null @@ -1,25 +0,0 @@ -// Code generated by "stringer -linecomment -type=CpeSearchType"; DO NOT EDIT. - -package dbstore - -import "strconv" - -func _() { - // An "invalid array index" compiler error signifies that the constant values have changed. - // Re-run the stringer command to generate them again. - var x [1]struct{} - _ = x[CpeSearchAll-0] - _ = x[CpeSearchTitle-1] - _ = x[CpeSearchRef-2] -} - -const _CpeSearchType_name = "cpe-search-allcpe-search-titlecpe-search-ref" - -var _CpeSearchType_index = [...]uint8{0, 14, 30, 44} - -func (i CpeSearchType) String() string { - if i >= CpeSearchType(len(_CpeSearchType_index)-1) { - return "CpeSearchType(" + strconv.FormatInt(int64(i), 10) + ")" - } - return _CpeSearchType_name[_CpeSearchType_index[i]:_CpeSearchType_index[i+1]] -} diff --git a/internal/dbstore/dbstore.go b/internal/dbstore/dbstore.go deleted file mode 100644 index 4df9fb9..0000000 --- a/internal/dbstore/dbstore.go +++ /dev/null @@ -1,255 +0,0 @@ -// database storage -package dbstore - -import ( - "context" - db_sql "database/sql" - "embed" - "encoding/json" - "fmt" - _ "github.com/mattn/go-sqlite3" - "github.com/pablotron/cvez/cpedict" -) - -//go:embed sql -var sqlFs embed.FS - -type DbStore struct { - db *db_sql.DB -} - -// open database -func Open(path string) (DbStore, error) { - var r DbStore - // init db - if db, err := db_sql.Open("sqlite3", path); err != nil { - return r, err - } else { - r.db = db - return r, nil - } -} - -// initialized database version -const initDbVersion = 314159 - -func (me DbStore) isInitialized(ctx context.Context) (bool, error) { - sql := "PRAGMA user_version;" - // get version - var version int32 - if err := me.db.QueryRowContext(ctx, sql).Scan(&version); err != nil { - return false, err - } - - // return result - return (version == initDbVersion), nil -} - -// initialize database -func (me DbStore) Init(ctx context.Context) error { - if inited, err := me.isInitialized(ctx); err != nil { - return err - } else if inited { - // already initialized, return success - return nil - } - - // read init query - sql, err := sqlFs.ReadFile("sql/init.sql") - if err != nil { - return err - } - - // exec init query, return result - _, err = me.db.ExecContext(ctx, string(sql)) - return err -} - -// get single query from embedded filesystem -func getQuery(id string) (string, error) { - // read query - if data, err := sqlFs.ReadFile(fmt.Sprintf("sql/%s.sql", id)); err != nil { - return "", err - } else { - // return query - return string(data), nil - } -} - -// return query map -func getQueries(ids []string) (map[string]string, error) { - r := make(map[string]string) - - for _, id := range(ids) { - // read query - if sql, err := getQuery(id); err != nil { - return r, fmt.Errorf("%s: %s", id, err.Error()) - } else { - // save query - r[id] = sql - } - } - - // return success - return r, nil -} - -// import CPE dictionary -func (me DbStore) AddCpeDictionary(ctx context.Context, dict cpedict.Dictionary) error { - // lazy-init db - if err := me.Init(ctx); err != nil { - return err - } - - // build query map - queryIds := []string { "insert-cpe", "insert-title", "insert-ref" } - queries, err := getQueries(queryIds) - if err != nil { - return err - } - - // begin context - tx, err := me.db.BeginTx(ctx, nil) - if err != nil { - return err - } - - // build statements - sts := make(map[string]*db_sql.Stmt) - for id, sql := range(queries) { - if st, err := tx.PrepareContext(ctx, sql); err != nil { - return err - } else { - sts[id] = st - defer sts[id].Close() - } - } - - // add items - for _, item := range(dict.Items) { - // add cpe - rs, err := sts["insert-cpe"].ExecContext(ctx, item.CpeUri, item.Cpe23Item.Name) - if err != nil { - return err - } - - // get last row ID - id, err := rs.LastInsertId() - if err != nil { - return err - } - - // add titles - for _, title := range(item.Titles) { - _, err := sts["insert-title"].ExecContext(ctx, id, title.Lang, title.Text) - if err != nil { - return err - } - } - - // add refs - for _, ref := range(item.References) { - _, err := sts["insert-ref"].ExecContext(ctx, id, ref.Href, ref.Text) - if err != nil { - return err - } - } - } - - // commit changes, return result - 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"` -} - -// search CPEs -func (me DbStore) CpeSearch( - ctx context.Context, - searchType CpeSearchType, - s string, -) ([]CpeSearchRow, error) { - var r []CpeSearchRow - - // lazy-init db - if err := me.Init(ctx); err != nil { - return r, err - } - - // get query - sql, err := getQuery(searchType.String()) - if err != nil { - return r, err - } - - // exec search query - rows, err := me.db.QueryContext(ctx, sql, db_sql.Named("q", s)) - if err != nil { - return r, err - } - - // walk results - for rows.Next() { - var sr CpeSearchRow - var titles string - var refs string - - // get row values - err = rows.Scan(&sr.CpeId, &sr.Cpe23, &titles, &refs, &sr.Rank) - if err != nil { - return r, err - } - - // unmarshal titles - if err = json.Unmarshal([]byte(titles), &sr.Titles); err != nil { - return r, err - } - - // unmarshal refs - if err = json.Unmarshal([]byte(refs), &sr.Refs); err != nil { - return r, err - } - - // append to results - r = append(r, sr) - } - - // close rows - if err = rows.Close(); err != nil { - return r, err - } - - // check for iteration errors - if err = rows.Err(); err != nil { - return r, err - } - - // return success - return r, nil -} diff --git a/internal/dbstore/dbstore_test.go b/internal/dbstore/dbstore_test.go deleted file mode 100644 index f957146..0000000 --- a/internal/dbstore/dbstore_test.go +++ /dev/null @@ -1,335 +0,0 @@ -package dbstore - -import ( - "compress/gzip" - "context" - db_sql "database/sql" - "encoding/xml" - "embed" - "errors" - "fmt" - _ "github.com/mattn/go-sqlite3" - "github.com/pablotron/cvez/cpedict" - io_fs "io/fs" - "os" - "reflect" - "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 ignoreTestSimple(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 - } -} - -// remove file if it exists -func removeFile(path string) error { - // remove file - err := os.Remove(path) - if err != nil && errors.Is(err, io_fs.ErrNotExist) { - return nil - } - - return err -} - -func createTestDb(ctx context.Context, path string) (DbStore, error) { - // remove existing file - if err := removeFile(path); err != nil { - return DbStore{}, err - } - - // open db - return Open(path) -} - -func seedTestDb(ctx context.Context, db DbStore) error { - // load test CPEs - dict, err := getTestDictionary("testdata/test-0.xml.gz") - if err != nil { - return err - } - - // add cpe dictionary - return db.AddCpeDictionary(ctx, dict) - - // TODO: seed with other data -} - -func TestOpen(t *testing.T) { - path := "./testdata/test-new.db" - ctx := context.Background() - - if _, err := createTestDb(ctx, path); err != nil { - t.Error(err) - return - } -} - -func TestAddCpeDictionary(t *testing.T) { - path := "./testdata/test-addcpedict.db" - ctx := context.Background() - - // create db - db, err := createTestDb(ctx, path) - 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 - } - - // add cpe dictionary - if err := db.AddCpeDictionary(ctx, dict); err != nil { - t.Error(err) - return - } -} - -// 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 TestCpeSearch(t *testing.T) { - path := "./testdata/test-search.db" - ctx := context.Background() - - tests := []struct { - t CpeSearchType // search type - q string // query string - exp []string // expected search results (cpe23s) - } {{ - t: CpeSearchAll, - q: "advisory AND book", - exp: []string { - "cpe:2.3:a:\\$0.99_kindle_books_project:\\$0.99_kindle_books:6:*:*:*:*:android:*:*", - }, - }, { - t: CpeSearchTitle, - q: "project", - exp: []string { - "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:\\$0.99_kindle_books_project:\\$0.99_kindle_books:6:*:*:*:*:android:*:*", - }, - }, { - t: CpeSearchRef, - q: "advisory", - exp: []string { - "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:*:*", - }, - }} - - // create db - db, err := createTestDb(ctx, path) - if err != nil { - t.Error(err) - return - } - - // seed test database - if err = seedTestDb(ctx, db); err != nil { - t.Error(err) - return - } - - for _, test := range(tests) { - t.Run(test.t.String(), func(t *testing.T) { - rows, err := db.CpeSearch(ctx, test.t, test.q) - if err != nil { - t.Error(err) - return - } - - // build ids - got := make([]string, len(rows)) - for i, row := range(rows) { - got[i] = row.Cpe23 - } - - if !reflect.DeepEqual(got, test.exp) { - t.Errorf("got \"%v\", exp \"%v\"", got, test.exp) - return - } - }) - } -} diff --git a/internal/dbstore/sql/cpe-search-all.sql b/internal/dbstore/sql/cpe-search-all.sql deleted file mode 100644 index 3260902..0000000 --- a/internal/dbstore/sql/cpe-search-all.sql +++ /dev/null @@ -1,46 +0,0 @@ -SELECT a.cpe_id, - a.cpe23, - COALESCE(c.titles, '[]') AS titles, - COALESCE(d.refs, '[]') AS refs, - b.rank - - FROM cpes a - JOIN ( - -- limit to "best" match (e.g., lowest rank) for given CPE - -- NOTE: it's not clear to me whether cross-index bm25() - -- comparisons are valid - SELECT cpe_id, - MIN(rank) AS rank - FROM cpe_fts_all a - WHERE cpe_fts_all MATCH :q - GROUP BY cpe_id - ) b ON (b.cpe_id = a.cpe_id) - - -- get all titles, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'lang', lang, - 'text', val - )) AS titles - - FROM cpe_titles - - GROUP BY cpe_id - ) c ON (c.cpe_id = b.cpe_id) - - -- get all refs, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'href', href, - 'text', val - )) AS refs - - FROM cpe_refs - - GROUP BY cpe_id - ) d ON (d.cpe_id = b.cpe_id) - - -- order by rank - ORDER BY b.rank diff --git a/internal/dbstore/sql/cpe-search-ref.sql b/internal/dbstore/sql/cpe-search-ref.sql deleted file mode 100644 index a2d3be6..0000000 --- a/internal/dbstore/sql/cpe-search-ref.sql +++ /dev/null @@ -1,47 +0,0 @@ -SELECT a.cpe_id, - a.cpe23, - COALESCE(c.titles, '[]') AS titles, - COALESCE(d.refs, '[]') AS refs, - b.rank - - -- find matching cpes - FROM cpes a - JOIN ( - -- there can be multiple matching references for a given - -- CPE, so use MIN() to limit the match to the most - -- "relevant", according to bm25() - SELECT cpe_id, - MIN(rank) AS rank - FROM cpe_fts_refs - WHERE cpe_fts_refs MATCH :q - GROUP BY cpe_id - ) b ON (b.cpe_id = a.cpe_id) - - -- get all titles, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'lang', lang, - 'text', val - )) AS titles - - FROM cpe_titles - - GROUP BY cpe_id - ) c ON (c.cpe_id = b.cpe_id) - - -- get all refs, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'href', href, - 'text', val - )) AS refs - - FROM cpe_refs - - GROUP BY cpe_id - ) d ON (d.cpe_id = b.cpe_id) - - -- order by rank - ORDER BY b.rank diff --git a/internal/dbstore/sql/cpe-search-title.sql b/internal/dbstore/sql/cpe-search-title.sql deleted file mode 100644 index 7421ffc..0000000 --- a/internal/dbstore/sql/cpe-search-title.sql +++ /dev/null @@ -1,44 +0,0 @@ -SELECT a.cpe_id, - a.cpe23, - COALESCE(c.titles, '[]') AS titles, - COALESCE(d.refs, '[]') AS refs, - b.rank - - -- find matching cpes - FROM cpes a - JOIN ( - SELECT cpe_id, - MIN(rank) AS rank - FROM cpe_fts_titles - WHERE cpe_fts_titles MATCH :q - GROUP BY cpe_id - ) b ON (b.cpe_id = a.cpe_id) - - -- get all titles, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'lang', lang, - 'text', val - )) AS titles - - FROM cpe_titles - - GROUP BY cpe_id - ) c ON (c.cpe_id = b.cpe_id) - - -- get all refs, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'href', href, - 'text', val - )) AS refs - - FROM cpe_refs - - GROUP BY cpe_id - ) d ON (d.cpe_id = b.cpe_id) - - -- order by rank - ORDER BY b.rank; diff --git a/internal/dbstore/sql/fill-fts.sql b/internal/dbstore/sql/fill-fts.sql deleted file mode 100644 index 5329ca7..0000000 --- a/internal/dbstore/sql/fill-fts.sql +++ /dev/null @@ -1,10 +0,0 @@ --- 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/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; diff --git a/internal/dbstore/sql/insert-cpe.sql b/internal/dbstore/sql/insert-cpe.sql deleted file mode 100644 index dca1c2b..0000000 --- a/internal/dbstore/sql/insert-cpe.sql +++ /dev/null @@ -1 +0,0 @@ -INSERT INTO cpes(cpe_uri, cpe23) VALUES (?, ?); diff --git a/internal/dbstore/sql/insert-ref.sql b/internal/dbstore/sql/insert-ref.sql deleted file mode 100644 index a39ab1c..0000000 --- a/internal/dbstore/sql/insert-ref.sql +++ /dev/null @@ -1 +0,0 @@ -INSERT INTO cpe_refs(cpe_id, href, val) VALUES (?, ?, ?); diff --git a/internal/dbstore/sql/insert-title.sql b/internal/dbstore/sql/insert-title.sql deleted file mode 100644 index 5045d95..0000000 --- a/internal/dbstore/sql/insert-title.sql +++ /dev/null @@ -1 +0,0 @@ -INSERT INTO cpe_titles(cpe_id, lang, val) VALUES (?, ?, ?); diff --git a/internal/dbstore/sql/old-cpe-search-all.sql b/internal/dbstore/sql/old-cpe-search-all.sql deleted file mode 100644 index 18e286f..0000000 --- a/internal/dbstore/sql/old-cpe-search-all.sql +++ /dev/null @@ -1,69 +0,0 @@ -SELECT a.cpe_id, - a.cpe23, - COALESCE(c.titles, '[]') AS titles, - COALESCE(d.refs, '[]') AS refs, - b.rank - - FROM cpes a - JOIN ( - -- limit to "best" match (e.g., lowest rank) for given CPE - -- NOTE: it's not clear to me whether cross-index bm25() - -- comparisons are valid - SELECT a.cpe_id, - MIN(a.rank) AS rank - - FROM ( - -- find title matches - SELECT b.cpe_id, - a.rank AS rank - - FROM cpe_titles_fts a - JOIN cpe_titles b - ON (b.cpe_title_id = a.rowid) - - WHERE cpe_titles_fts MATCH :q - - UNION ALL - - -- find reference matches - SELECT b.cpe_id, - a.rank - - FROM cpe_refs_fts a - JOIN cpe_refs b - ON (b.cpe_ref_id = a.rowid) - - WHERE cpe_refs_fts MATCH :q - ) a - - GROUP BY a.cpe_id - ) b ON (b.cpe_id = a.cpe_id) - - -- get all titles, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'lang', lang, - 'text', val - )) AS titles - - FROM cpe_titles - - GROUP BY cpe_id - ) c ON (c.cpe_id = b.cpe_id) - - -- get all refs, grouped by cpe - LEFT JOIN ( - SELECT cpe_id, - json_group_array(json_object( - 'href', href, - 'text', val - )) AS refs - - FROM cpe_refs - - GROUP BY cpe_id - ) d ON (d.cpe_id = b.cpe_id) - - -- order by rank - ORDER BY b.rank diff --git a/internal/dbstore/sql/table-exists.sql b/internal/dbstore/sql/table-exists.sql deleted file mode 100644 index 09d32a4..0000000 --- a/internal/dbstore/sql/table-exists.sql +++ /dev/null @@ -1,6 +0,0 @@ -SELECT EXISTS( - SELECT 1 - FROM sqlite_schema - WHERE type = 'table' - AND name = ? -) diff --git a/internal/dbstore/testdata/sql/fill-fts.sql b/internal/dbstore/testdata/sql/fill-fts.sql deleted file mode 100644 index 5329ca7..0000000 --- a/internal/dbstore/testdata/sql/fill-fts.sql +++ /dev/null @@ -1,10 +0,0 @@ --- 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 deleted file mode 100644 index a08b2f8..0000000 --- a/internal/dbstore/testdata/sql/init.sql +++ /dev/null @@ -1,73 +0,0 @@ --- 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; - -PRAGMA user_version = 314159; diff --git a/internal/dbstore/testdata/sql/insert-cpe.sql b/internal/dbstore/testdata/sql/insert-cpe.sql deleted file mode 100644 index dca1c2b..0000000 --- a/internal/dbstore/testdata/sql/insert-cpe.sql +++ /dev/null @@ -1 +0,0 @@ -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 deleted file mode 100644 index a39ab1c..0000000 --- a/internal/dbstore/testdata/sql/insert-ref.sql +++ /dev/null @@ -1 +0,0 @@ -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 deleted file mode 100644 index 5045d95..0000000 --- a/internal/dbstore/testdata/sql/insert-title.sql +++ /dev/null @@ -1 +0,0 @@ -INSERT INTO cpe_titles(cpe_id, lang, val) VALUES (?, ?, ?); diff --git a/internal/dbstore/testdata/sql/table-exists.sql b/internal/dbstore/testdata/sql/table-exists.sql deleted file mode 100644 index 09d32a4..0000000 --- a/internal/dbstore/testdata/sql/table-exists.sql +++ /dev/null @@ -1,6 +0,0 @@ -SELECT EXISTS( - SELECT 1 - FROM sqlite_schema - WHERE type = 'table' - AND name = ? -) diff --git a/internal/dbstore/testdata/test-0.xml.gz b/internal/dbstore/testdata/test-0.xml.gz deleted file mode 100644 index 110e965..0000000 Binary files a/internal/dbstore/testdata/test-0.xml.gz and /dev/null differ -- cgit v1.2.3