The following pull request was submitted through Github. It can be accessed and reviewed at: https://github.com/lxc/lxd/pull/3704
This e-mail was sent by the LXC bot, direct replies will not reach the author unless they happen to be subscribed to this list. === Description (from pull-request) === 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 <[email protected]>
From f7efe86d15b2ca58ce4d2b4f48bfc642347fbac1 Mon Sep 17 00:00:00 2001 From: Free Ekanayaka <[email protected]> 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 <[email protected]> --- 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 +}
_______________________________________________ lxc-devel mailing list [email protected] http://lists.linuxcontainers.org/listinfo/lxc-devel
