[lxc-devel] [lxd/master] Add db/query sub-package with common query helpers

[freeekanayaka on Github]

A non-text attachment was scrubbed...
Name: not available
Type: text/x-mailbox
Size: 518 bytes
Desc: not available
URL: <http://lists.linuxcontainers.org/pipermail/lxc-devel/attachments/20170822/34431746/attachment.bin>
-------------- next part --------------
From f7efe86d15b2ca58ce4d2b4f48bfc642347fbac1 Mon Sep 17 00:00:00 2001
From: Free Ekanayaka <free.ekanayaka at canonical.com>
Date: Sun, 20 Aug 2017 14:59:07 +0000
Subject: [PATCH] Add db/query sub-package with common query helpers

Introduce helpers around database/sql to execute various kinds of very
common SQL read queries. This package will grow as needed in follow-up
branches.

Signed-off-by: Free Ekanayaka <free.ekanayaka at canonical.com>
---
 lxd/db/query/doc.go         |  3 ++
 lxd/db/query/slices.go      | 83 ++++++++++++++++++++++++++++++++++++++++++
 lxd/db/query/slices_test.go | 89 +++++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 175 insertions(+)
 create mode 100644 lxd/db/query/doc.go
 create mode 100644 lxd/db/query/slices.go
 create mode 100644 lxd/db/query/slices_test.go

diff --git a/lxd/db/query/doc.go b/lxd/db/query/doc.go
new file mode 100644
index 000000000..eef2fc7bf
--- /dev/null
+++ b/lxd/db/query/doc.go
@@ -0,0 +1,3 @@
+// Package query implements helpers around database/sql to execute various
+// kinds of very common SQL read queries.
+package query
diff --git a/lxd/db/query/slices.go b/lxd/db/query/slices.go
new file mode 100644
index 000000000..472620848
--- /dev/null
+++ b/lxd/db/query/slices.go
@@ -0,0 +1,83 @@
+package query
+
+import (
+	"database/sql"
+	"fmt"
+	"strings"
+)
+
+// Strings executes a statement which must yield rows with a single string
+// column. It returns the list of column values.
+func Strings(tx *sql.Tx, query string) ([]string, error) {
+	values := []string{}
+	scan := func(rows *sql.Rows) error {
+		var value string
+		if err := rows.Scan(&value); err != nil {
+			return err
+		}
+		values = append(values, value)
+		return nil
+	}
+
+	if err := scanSingleColumn(tx, query, "TEXT", scan); err != nil {
+		return nil, err
+	}
+
+	return values, nil
+}
+
+// Integers executes a statement which must yield rows with a single integer
+// column. It returns the list of column values.
+func Integers(tx *sql.Tx, query string) ([]int, error) {
+	values := []int{}
+	scan := func(rows *sql.Rows) error {
+		var value int
+		if err := rows.Scan(&value); err != nil {
+			return err
+		}
+		values = append(values, value)
+		return nil
+	}
+
+	if err := scanSingleColumn(tx, query, "INTEGER", scan); err != nil {
+		return nil, err
+	}
+
+	return values, nil
+}
+
+// Execute the given query and ensure that it yields rows with a single column
+// of the given database type. For every row yielded, execute the given
+// scanner.
+func scanSingleColumn(tx *sql.Tx, query string, typeName string, scan scanFunc) error {
+	rows, err := tx.Query(query)
+	if err != nil {
+		return err
+	}
+	defer rows.Close()
+
+	types, err := rows.ColumnTypes()
+	if err != nil {
+		return err
+	}
+	if len(types) != 1 {
+		return fmt.Errorf("query yields %d columns, not 1", len(types))
+	}
+	actualTypeName := strings.ToUpper(types[0].DatabaseTypeName())
+	if actualTypeName != typeName {
+		return fmt.Errorf("query yields %s column, not %s", actualTypeName, typeName)
+	}
+
+	for rows.Next() {
+		if err := scan(rows); err != nil {
+			return err
+		}
+	}
+	if err := rows.Err(); err != nil {
+		return err
+	}
+	return nil
+}
+
+// Function to scan a single row.
+type scanFunc func(*sql.Rows) error
diff --git a/lxd/db/query/slices_test.go b/lxd/db/query/slices_test.go
new file mode 100644
index 000000000..e079f3649
--- /dev/null
+++ b/lxd/db/query/slices_test.go
@@ -0,0 +1,89 @@
+package query_test
+
+import (
+	"database/sql"
+	"fmt"
+	"testing"
+
+	_ "github.com/mattn/go-sqlite3"
+	"github.com/mpvl/subtest"
+	"github.com/stretchr/testify/assert"
+
+	"github.com/lxc/lxd/lxd/db/query"
+)
+
+// Exercise possible failure modes.
+func TestStrings_Error(t *testing.T) {
+	cases := []struct {
+		query string
+		error string
+	}{
+		{"garbage", "near \"garbage\": syntax error"},
+		{"SELECT id, name FROM test", "query yields 2 columns, not 1"},
+		{"SELECT id FROM test", "query yields INTEGER column, not TEXT"},
+	}
+	for _, c := range cases {
+		subtest.Run(t, c.query, func(t *testing.T) {
+			tx := newTxForSlices()
+			values, err := query.Strings(tx, c.query)
+			assert.EqualError(t, err, c.error)
+			assert.Nil(t, values)
+		})
+	}
+}
+
+// All values yield by the query are returned.
+func TestStrings(t *testing.T) {
+	tx := newTxForSlices()
+	values, err := query.Strings(tx, "SELECT name FROM test ORDER BY name")
+	assert.Nil(t, err)
+	assert.Equal(t, []string{"bar", "foo"}, values)
+}
+
+// Exercise possible failure modes.
+func TestIntegers_Error(t *testing.T) {
+	cases := []struct {
+		query string
+		error string
+	}{
+		{"garbage", "near \"garbage\": syntax error"},
+		{"SELECT id, name FROM test", "query yields 2 columns, not 1"},
+		{"SELECT name FROM test", "query yields TEXT column, not INTEGER"},
+	}
+	for _, c := range cases {
+		subtest.Run(t, c.query, func(t *testing.T) {
+			tx := newTxForSlices()
+			values, err := query.Integers(tx, c.query)
+			assert.EqualError(t, err, c.error)
+			assert.Nil(t, values)
+		})
+	}
+}
+
+// All values yield by the query are returned.
+func TestIntegers(t *testing.T) {
+	tx := newTxForSlices()
+	values, err := query.Integers(tx, "SELECT id FROM test ORDER BY id")
+	assert.Nil(t, err)
+	assert.Equal(t, []int{0, 1}, values)
+}
+
+// Return a new transaction against an in-memory SQLite database with a single
+// test table populated with a few rows.
+func newTxForSlices() *sql.Tx {
+	db, err := sql.Open("sqlite3", ":memory:")
+	if err != nil {
+		panic(fmt.Sprintf("failed to create database: %v", err))
+	}
+	if _, err := db.Exec("CREATE TABLE test (id INTEGER, name TEXT)"); err != nil {
+		panic(fmt.Sprintf("failed to create table: %v", err))
+	}
+	if _, err := db.Exec("INSERT INTO test VALUES (0, 'foo'), (1, 'bar')"); err != nil {
+		panic(fmt.Sprintf("failed to insert rows: %v", err))
+	}
+	tx, err := db.Begin()
+	if err != nil {
+		panic(fmt.Sprintf("failed to begin transaction: %v", err))
+	}
+	return tx
+}