dbstore: add DbStore#CpeMatchSearch() and backing query

2 files changed, 71 insertions, 5 deletions

diff --git a/dbstore/dbstore.go b/dbstore/dbstore.go
index acaad7c..d30d0a8 100644
--- a/dbstore/dbstore.go
+++ b/dbstore/dbstore.go
@@ -232,11 +232,11 @@ func (me DbStore) CpeSearch(
 
 // query IDs used by AddCpeMatches()
 var addCpeMatchesQueryIds = []string {
-  "cpe-match-insert",
-  "cpe-match-insert-vulnerability",
-  "cpe-match-insert-version-min",
-  "cpe-match-insert-version-max",
-  "cpe-match-insert-cpe",
+  "cpe-match/insert",
+  "cpe-match/insert-vulnerability",
+  "cpe-match/insert-version-min",
+  "cpe-match/insert-version-max",
+  "cpe-match/insert-name",
 }
 
 // import CPE matches
@@ -329,3 +329,58 @@ func (me DbStore) AddCpeMatches(ctx context.Context, matches cpematch.Matches) e
   // commit changes, return result
   return tx.Commit()
 }
+
+// search CPE matches
+func (me DbStore) CpeMatchSearch(
+  ctx context.Context,
+  match string,
+) ([]string, error) {
+  var r []string
+
+  // lazy-init db
+  if err := me.Init(ctx); err != nil {
+    return r, err
+  }
+
+  // get query
+  // FIXME: cache this?
+  sql, err := getQuery("cpe-match/search.sql")
+  if err != nil {
+    return r, err
+  }
+
+  // exec search query
+  rows, err := me.db.QueryContext(ctx, sql, match)
+  if err != nil {
+    return r, err
+  }
+
+  // walk results
+  for rows.Next() {
+    var s string
+    if err := rows.Scan(&s); err != nil {
+      // return error
+      return r, err
+    } else {
+      // append to results
+      r = append(r, s)
+    }
+  }
+
+  // close rows
+  // FIXME: is this correct? i am following the example from the
+  // database/sql documentation, but it is messy and it seems
+  // counterintuitive to close the row set and then do an additional
+  // test for iteration errors...
+  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/dbstore/sql/cpe-match/search.sql b/dbstore/sql/cpe-match/search.sql
new file mode 100644
index 0000000..45dd1f6
--- /dev/null
+++ b/dbstore/sql/cpe-match/search.sql
@@ -0,0 +1,11 @@
+SELECT c.cpe23
+
+  FROM cpe_matches a
+  JOIN cpe_match_cpes b
+    ON (b.cpe_match_id = a.cpe_match_id)
+  JOIN cpes c
+    ON (c.cpe_id = b.cpe_id)
+
+ WHERE a.cpe23 = ?
+
+ ORDER BY c.cpe23