path: root/internal/dbstore/dbstore_test.go

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
      }
    })
  }
}