// Copyright 2021 Google LLC

//

// Licensed under the Apache License, Version 2.0 (the "License");

// you may not use this file except in compliance with the License.

// You may obtain a copy of the License at

//

// https://www.apache.org/licenses/LICENSE-2.0

//

// Unless required by applicable law or agreed to in writing, software

// distributed under the License is distributed on an "AS IS" BASIS,

// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

// See the License for the specific language governing permissions and

// limitations under the License.

package spannerdriver

import (

"context"

cryptorand "crypto/rand"

"database/sql"

"errors"

"flag"

"fmt"

"log"

"math"

"math/big"

"math/rand"

"net"

"os"

"reflect"

"slices"

"strconv"

"sync"

"testing"

"time"

"cloud.google.com/go/civil"

"cloud.google.com/go/spanner"

database "cloud.google.com/go/spanner/admin/database/apiv1"

"cloud.google.com/go/spanner/admin/database/apiv1/databasepb"

instance "cloud.google.com/go/spanner/admin/instance/apiv1"

"cloud.google.com/go/spanner/admin/instance/apiv1/instancepb"

"github.com/google/go-cmp/cmp"

"google.golang.org/api/iterator"

"google.golang.org/api/option"

"google.golang.org/grpc"

"google.golang.org/grpc/codes"

"google.golang.org/grpc/credentials/insecure"

)

var projectId, instanceId string

var skipped bool

var experimentalHost string

var caCertFile string

var clientCertFile string

var clientCertKey string

func init() {

flag.StringVar(&experimentalHost, "it.experimental-host", "", "Experimental host integration test flag")

flag.StringVar(&caCertFile, "it.ca-cert-file", "", "CA certificate file for experimental host integration test")

flag.StringVar(&clientCertFile, "it.client-cert-file", "", "Client certificate file for experimental host integration test")

flag.StringVar(&clientCertKey, "it.client-cert-key", "", "Client certificate key file for experimental host integration test")

var ok bool

// Get environment variables or set to default.

if instanceId, ok = os.LookupEnv("SPANNER_TEST_INSTANCE"); !ok {

instanceId = "test-instance"

}

if projectId, ok = os.LookupEnv("SPANNER_TEST_PROJECT"); !ok {

projectId = "test-project"

}

}

func runsOnEmulator() bool {

if _, ok := os.LookupEnv("SPANNER_EMULATOR_HOST"); ok {

return true

}

return false

}

func initTestInstance(config string) (cleanup func(), err error) {

ctx := context.Background()

instanceAdmin, err := instance.NewInstanceAdminClient(ctx)

if err != nil {

return nil, err

}

defer instanceAdmin.Close()

// Check if the instance exists or not.

_, err = instanceAdmin.GetInstance(ctx, &instancepb.GetInstanceRequest{

Name: fmt.Sprintf("projects/%s/instances/%s", projectId, instanceId),

})

if err == nil {

return func() {}, nil

}

if spanner.ErrCode(err) != codes.NotFound {

return nil, err

}

// Instance does not exist. Create a temporary instance for this test run.

// The instance will be deleted after the test run.

op, err := instanceAdmin.CreateInstance(ctx, &instancepb.CreateInstanceRequest{

Parent: fmt.Sprintf("projects/%s", projectId),

InstanceId: instanceId,

Instance: &instancepb.Instance{

Config: fmt.Sprintf("projects/%s/instanceConfigs/%s", projectId, config),

DisplayName: instanceId,

NodeCount: 1,

Labels: map[string]string{

"gosqltestinstance": "true",

"createdat": fmt.Sprintf("t%d", time.Now().Unix()),

},

},

})

if err != nil {

return nil, fmt.Errorf("could not create instance %s: %v", fmt.Sprintf("projects/%s/instances/%s", projectId, instanceId), err)

} else {

// Wait for the instance creation to finish.

_, err := op.Wait(ctx)

if err != nil {

return nil, fmt.Errorf("waiting for instance creation to finish failed: %v", err)

}

}

// Delete the instance after all tests have finished.

// Also delete any stale test instances that might still be around on the project.

return func() {

instanceAdmin, err := instance.NewInstanceAdminClient(ctx)

if err != nil {

return

}

// Delete this test instance.

instanceAdmin.DeleteInstance(ctx, &instancepb.DeleteInstanceRequest{

Name: fmt.Sprintf("projects/%s/instances/%s", projectId, instanceId),

})

// Also delete any other stale test instance.

instances := instanceAdmin.ListInstances(ctx, &instancepb.ListInstancesRequest{

Parent: fmt.Sprintf("projects/%s", projectId),

Filter: "label.gosqltestinstance:*",

})

for {

instance, err := instances.Next()

if err == iterator.Done {

break

}

if err != nil {

log.Printf("failed to fetch instances during cleanup: %v", err)

break

}

if createdAtString, ok := instance.Labels["createdat"]; ok {

// Strip the leading 't' from the value.

seconds, err := strconv.ParseInt(createdAtString[1:], 10, 64)

if err != nil {

log.Printf("failed to parse created time from string %q of instance %s: %v", createdAtString, instance.Name, err)

} else {

diff := time.Duration(time.Now().Unix()-seconds) * time.Second

if diff > time.Hour*2 {

log.Printf("deleting stale test instance %s", instance.Name)

instanceAdmin.DeleteInstance(ctx, &instancepb.DeleteInstanceRequest{

Name: instance.Name,

})

}

}

}

}

}, nil

}

func createDBAdminClient(ctx context.Context) (*database.DatabaseAdminClient, error) {

if experimentalHost == "" {

return database.NewDatabaseAdminClient(ctx)

}

opts := []option.ClientOption{

option.WithEndpoint(experimentalHost),

option.WithoutAuthentication(),

}

if caCertFile != "" {

credOpts, err := createExperimentalHostCredentials(caCertFile, clientCertFile, clientCertKey)

if err != nil {

return nil, err

}

opts = append(opts, credOpts)

} else {

opts = append(opts, option.WithGRPCDialOption(grpc.WithTransportCredentials(insecure.NewCredentials())))

}

return database.NewDatabaseAdminClient(ctx, opts...)

}

func createTestDB(ctx context.Context, statements ...string) (dsn string, cleanup func(), err error) {

return createTestDBWithDialect(ctx, databasepb.DatabaseDialect_GOOGLE_STANDARD_SQL, statements...)

}

func createTestDBWithDialect(ctx context.Context, dialect databasepb.DatabaseDialect, statements ...string) (dsn string, cleanup func(), err error) {

databaseAdminClient, err := createDBAdminClient(ctx)

if err != nil {

return "", nil, err

}

defer databaseAdminClient.Close()

prefix, ok := os.LookupEnv("SPANNER_TEST_DBID")

if !ok {

prefix = "gotest"

}

var uniqueid [4]byte

if _, err = cryptorand.Read(uniqueid[:]); err != nil {

return "", nil, err

}

databaseId := fmt.Sprintf("%s-%x", prefix, uniqueid)

var opDB *database.CreateDatabaseOperation

if dialect == databasepb.DatabaseDialect_POSTGRESQL {

if len(statements) > 0 {

return "", nil, errors.New("PostgreSQL does not allow extra statements in the CREATE DATABASE call")

}

opDB, err = databaseAdminClient.CreateDatabase(ctx, &databasepb.CreateDatabaseRequest{

Parent: fmt.Sprintf("projects/%s/instances/%s", projectId, instanceId),

CreateStatement: fmt.Sprintf(`CREATE DATABASE "%s"`, databaseId),

DatabaseDialect: dialect,

})

} else {

opDB, err = databaseAdminClient.CreateDatabase(ctx, &databasepb.CreateDatabaseRequest{

Parent: fmt.Sprintf("projects/%s/instances/%s", projectId, instanceId),

CreateStatement: fmt.Sprintf("CREATE DATABASE `%s`", databaseId),

ExtraStatements: statements,

DatabaseDialect: dialect,

})

}

if err != nil {

return "", nil, err

} else {

// Wait for the database creation to finish.

_, err := opDB.Wait(ctx)

if err != nil {

return "", nil, fmt.Errorf("waiting for database creation to finish failed: %v", err)

}

}

dsn = "projects/" + projectId + "/instances/" + instanceId + "/databases/" + databaseId

if experimentalHost != "" {

dsn = experimentalHost + "/databases/" + databaseId

if caCertFile == "" {

dsn += "?use_plain_text=true"

} else {

dsn += "?ca_cert_file=" + caCertFile

if clientCertFile != "" && clientCertKey != "" {

dsn += ";client_cert_file=" + clientCertFile + ";client_cert_key=" + clientCertKey

}

}

dsn += ";is_experimental_host=true"

}

cleanup = func() {

databaseAdminClient, err := createDBAdminClient(ctx)

if err != nil {

return

}

defer databaseAdminClient.Close()

databaseAdminClient.DropDatabase(ctx, &databasepb.DropDatabaseRequest{

Database: fmt.Sprintf("projects/%s/instances/%s/databases/%s", projectId, instanceId, databaseId),

})

}

return

}

func initIntegrationTests() (cleanup func(), err error) {

flag.Parse() // Needed for testing.Short().

noop := func() {}

if experimentalHost != "" {

projectId = experimentalHostProject

instanceId = experimentalHostInstance

// instance management is not available on experimental host

return noop, nil

}

if testing.Short() {

log.Println("Integration tests skipped in -short mode.")

return noop, nil

}

_, hasCredentials := os.LookupEnv("GOOGLE_APPLICATION_CREDENTIALS")

_, hasEmulator := os.LookupEnv("SPANNER_EMULATOR_HOST")

if !(hasCredentials || hasEmulator) {

log.Println("Skipping integration tests as no credentials and no emulator host has been set")

skipped = true

return noop, nil

}

// Automatically create test instance if necessary.

config := "regional-us-east1"

if _, ok := os.LookupEnv("SPANNER_EMULATOR_HOST"); ok {

config = "emulator-config"

}

cleanup, err = initTestInstance(config)

if err != nil {

return nil, err

}

return cleanup, nil

}

func TestMain(m *testing.M) {

cleanup, err := initIntegrationTests()

if err != nil {

log.Fatalf("could not init integration tests: %v", err)

os.Exit(1)

}

res := m.Run()

cleanup()

os.Exit(res)

}

func skipIfShort(t *testing.T) {

if testing.Short() {

t.Skip("Integration tests skipped in -short mode.")

}

if skipped {

t.Skip("Integration tests skipped")

}

}

func TestAutoConfigEmulator(t *testing.T) {

skipIfShort(t)

if !runsOnEmulator() {

t.Skip("autoConfigEmulator=true only works when connected to the emulator")

}

t.Parallel()

ctx := context.Background()

for range 2 {

db, err := sql.Open("spanner", "projects/emulator-project/instances/test-instance/databases/test-database;autoConfigEmulator=true")

if err != nil {

t.Fatalf("could not connect to emulator: %v", err)

}

row := db.QueryRowContext(ctx, "select 1")

if row.Err() != nil {

t.Fatalf("could not execute select 1: %v", row.Err())

}

var v int64

if err := row.Scan(&v); err != nil {

t.Fatalf("could not scan value from select 1: %v", err)

}

if g, w := v, int64(1); g != w {

t.Fatalf("value mismatch:\n Got %d\nWant %d", g, w)

}

_ = db.Close()

}

}

func TestAutoConfigEmulatorPostgreSql(t *testing.T) {

skipIfShort(t)

if !runsOnEmulator() {

t.Skip("autoConfigEmulator=true only works when connected to the emulator")

}

t.Parallel()

ctx := context.Background()

for range 2 {

db, err := sql.Open("spanner", "projects/emulator-project/instances/test-instance/databases/test-database-pg;autoConfigEmulator=true;dialect=postgresql")

if err != nil {

t.Fatalf("could not connect to emulator: %v", err)

}

// Execute a query that only works on PostgreSQL.

row := db.QueryRowContext(ctx, "select $1", "Hello World")

if row.Err() != nil {

t.Fatalf("could not execute select: %v", row.Err())

}

var msg string

if err := row.Scan(&msg); err != nil {

t.Fatalf("could not scan value from select: %v", err)

}

if g, w := msg, "Hello World"; g != w {

t.Fatalf("value mismatch:\n Got %v\nWant %v", g, w)

}

_ = db.Close()

}

}

func TestQueryContext(t *testing.T) {

skipIfShort(t)

t.Parallel()

// Create test database.

ctx := context.Background()

dsn, cleanup, err := createTestDB(ctx,

`CREATE TABLE TestQueryContext (

A STRING(1024),

B STRING(1024),

C STRING(1024)

) PRIMARY KEY (A)`)

if err != nil {

t.Fatalf("failed to create test db: %v", err)

}

defer cleanup()

// Open db.

db, err := sql.Open("spanner", dsn)

if err != nil {

t.Fatal(err)

}

defer db.Close()

_, err = db.ExecContext(ctx, `INSERT INTO TestQueryContext (A, B, C)

VALUES ("a1", "b1", "c1"), ("a2", "b2", "c2") , ("a3", "b3", "c3") `)

if err != nil {

t.Fatal(err)

}

type testQueryContextRow struct {

A, B, C string

}

tests := []struct {

name string

input string

want []testQueryContextRow

wantErrorQuery bool

wantErrorScan bool

wantErrorClose bool

}{

{

name: "empty query",

wantErrorClose: true,

input: "",

want: []testQueryContextRow{},

},

{

name: "syntax error",

wantErrorClose: true,

input: "SELECT SELECT * FROM TestQueryContext",

want: []testQueryContextRow{},

},

{

name: "return nothing",

input: "SELECT * FROM TestQueryContext WHERE A = \"hihihi\"",

want: []testQueryContextRow{},

},

{

name: "select one tuple",

input: "SELECT * FROM TestQueryContext WHERE A = \"a1\"",

want: []testQueryContextRow{

{A: "a1", B: "b1", C: "c1"},

},

},

{

name: "select subset of tuples",

input: "SELECT * FROM TestQueryContext WHERE A = \"a1\" OR A = \"a2\"",

want: []testQueryContextRow{

{A: "a1", B: "b1", C: "c1"},

{A: "a2", B: "b2", C: "c2"},

},

},

{

name: "select subset of tuples with !=",

input: "SELECT * FROM TestQueryContext WHERE A != \"a3\"",

want: []testQueryContextRow{

{A: "a1", B: "b1", C: "c1"},

{A: "a2", B: "b2", C: "c2"},

},

},

{

name: "select entire table",

input: "SELECT * FROM TestQueryContext ORDER BY A",

want: []testQueryContextRow{

{A: "a1", B: "b1", C: "c1"},

{A: "a2", B: "b2", C: "c2"},

{A: "a3", B: "b3", C: "c3"},

},

},

{

name: "query non existent table",

wantErrorClose: true,

input: "SELECT * FROM NonExistent",

want: []testQueryContextRow{},

},

}

// Run tests

for _, tc := range tests {

rows, err := db.QueryContext(ctx, tc.input)

if (err != nil) && (!tc.wantErrorQuery) {

t.Errorf("%s: unexpected query error: %v", tc.name, err)

}

if (err == nil) && (tc.wantErrorQuery) {

t.Errorf("%s: expected query error but error was %v", tc.name, err)

}

got := []testQueryContextRow{}

for rows.Next() {

var curr testQueryContextRow

err := rows.Scan(&curr.A, &curr.B, &curr.C)

if (err != nil) && (!tc.wantErrorScan) {

t.Errorf("%s: unexpected query error: %v", tc.name, err)

}

if (err == nil) && (tc.wantErrorScan) {

t.Errorf("%s: expected query error but error was %v", tc.name, err)

}

got = append(got, curr)

}

rows.Close()

err = rows.Err()

if (err != nil) && (!tc.wantErrorClose) {

t.Errorf("%s: unexpected query error: %v", tc.name, err)

}

if (err == nil) && (tc.wantErrorClose) {

t.Errorf("%s: expected query error but error was %v", tc.name, err)

}

if !reflect.DeepEqual(tc.want, got) {

t.Errorf("Test failed: %s. want: %v, got: %v", tc.name, tc.want, got)

}

}

}

func TestTypeRoundTrip(t *testing.T) {

skipIfShort(t)

t.Parallel()

// This test checks that all types in checkIsValidType can be used as a query argument and scanned as a result.

//

// The commented out types are currently not working correctly.

//

// The tests that use "scan" could be improved to allow scanning into the same type,

// that it was converted to.

ctx := context.Background()

dsn, cleanup, err := createTestDB(ctx)

if err != nil {

t.Fatalf("failed to create test db: %v", err)

}

defer cleanup()

// Open db.

// send_typed_strings=true is required to include a type code for string values.

// Otherwise, string values are sent as untyped strings in order to allow Spanner to infer the type.

// Using untyped strings is recommended for most applications, as it allows the application to just use

// standard string values for any type that is encoded as strings in Spanner (e.g. JSON, DATE, TIMESTAMP, etc.).

db, err := sql.Open("spanner", dsn+";send_typed_strings=true")

if err != nil {

t.Fatal(err)

}

defer db.Close()

now := time.Now().UTC() // spanner doesn't preserve timezone

tests := []struct {

in any

scan any // can be nil, if `in` matches output type.

skipeq bool

}{

{in: sql.NullInt64{Valid: true, Int64: 197}},

{in: sql.NullTime{Valid: true, Time: now}},

{in: sql.NullString{Valid: true, String: "hello"}},

{in: sql.NullFloat64{Valid: true, Float64: 3.14}},

{in: sql.NullBool{Valid: true, Bool: true}},

// {in: sql.NullInt32{Valid: true, Int32: 197}},

// string variants

{in: "hello"},

{in: spanner.NullString{Valid: true, StringVal: "hello"}},

{in: []string{"hello"}, scan: pointerTo([]spanner.NullString{})},

{in: []spanner.NullString{{Valid: true, StringVal: "hello"}}},

// *string variants

{in: pointerTo("hello")},

{in: []*string{pointerTo("hello")}, scan: pointerTo([]spanner.NullString{})},

// []byte variants

{in: []byte{1, 2, 3}},

{in: [][]byte{{1, 2, 3}}},

// uint, *uint variants

{in: uint(197)},

{in: []uint{197}, scan: pointerTo([]spanner.NullInt64{})},

{in: pointerTo(uint(197))},

{in: []*uint{pointerTo(uint(197))}, scan: pointerTo([]spanner.NullInt64{})},

{in: pointerTo([]uint{197}), scan: pointerTo([]spanner.NullInt64{})},

// int, *int variants

{in: int(197)},

{in: []int{197}, scan: pointerTo([]spanner.NullInt64{})},

{in: pointerTo(int(197))},

{in: []*int{pointerTo(197)}, scan: pointerTo([]spanner.NullInt64{})},

{in: pointerTo([]int{197}), scan: pointerTo([]spanner.NullInt64{})},

// int64, *int64 variants

{in: int64(197)},

{in: []int64{}, scan: pointerTo([]spanner.NullInt64{})},

{in: spanner.NullInt64{Valid: true, Int64: 197}},

{in: []spanner.NullInt64{{Valid: true, Int64: 197}}},

{in: pointerTo(int64(197))},

{in: []*int64{pointerTo(int64(197))}, scan: pointerTo([]spanner.NullInt64{})},

// uint64, *uint64 variants

{in: uint64(197)},

{in: []uint64{}, scan: pointerTo([]spanner.NullInt64{})},

{in: pointerTo(uint64(197))},

{in: []*uint64{pointerTo(uint64(197))}, scan: pointerTo([]spanner.NullInt64{})},

// bool variants

{in: true},

{in: []bool{true}, scan: pointerTo([]spanner.NullBool{})},

{in: spanner.NullBool{Valid: true, Bool: true}},

{in: []spanner.NullBool{{Valid: true, Bool: true}}, scan: pointerTo([]spanner.NullBool{})},

{in: pointerTo(true)},

{in: []*bool{pointerTo(true)}, scan: pointerTo([]spanner.NullBool{})},

// float32 variants

{in: float32(3.14)},

{in: []float32{3.14}, scan: pointerTo([]spanner.NullFloat32{})},

{in: spanner.NullFloat32{Valid: true, Float32: 3.14}},

{in: []spanner.NullFloat32{{Valid: true, Float32: 3.14}}},

{in: pointerTo(float32(3.14))},

{in: []*float32{pointerTo(float32(3.14))}, scan: pointerTo([]spanner.NullFloat32{})},

// float64 variants

{in: float64(3.14)},

{in: []float64{3.14}, scan: pointerTo([]spanner.NullFloat64{})},

{in: spanner.NullFloat64{Valid: true, Float64: 3.14}},

{in: []spanner.NullFloat64{{Valid: true, Float64: 3.14}}},

{in: pointerTo(float64(3.14))},

{in: []*float64{pointerTo(float64(3.14))}, scan: pointerTo([]spanner.NullFloat64{})},

// Numeric, big.Rat variants

{in: *big.NewRat(19, 17), skipeq: true},

{in: []big.Rat{*big.NewRat(19, 17)}, scan: pointerTo([]spanner.NullNumeric{})},

{in: big.NewRat(19, 17), skipeq: true},

{in: []*big.Rat{big.NewRat(19, 17)}, scan: pointerTo([]spanner.NullNumeric{})},

{in: spanner.NullNumeric{Valid: true, Numeric: *big.NewRat(19, 100)}},

{in: []spanner.NullNumeric{{Valid: true, Numeric: *big.NewRat(19, 100)}}},

// time.Time variants

{in: now},

{in: []time.Time{now}, scan: pointerTo([]spanner.NullTime{})},

{in: spanner.NullTime{Valid: true, Time: now}},

{in: []spanner.NullTime{{Valid: true, Time: now}}},

{in: pointerTo(now)},

{in: []*time.Time{pointerTo(now)}, scan: pointerTo([]spanner.NullTime{})},

// civil.Date variants

{in: civil.DateOf(now)},

{in: []civil.Date{civil.DateOf(now)}, scan: pointerTo([]spanner.NullDate{})},

{in: spanner.NullDate{Valid: true, Date: civil.DateOf(now)}},

{in: []spanner.NullDate{{Valid: true, Date: civil.DateOf(now)}}},

{in: pointerTo(civil.DateOf(now))},

{in: []*civil.Date{pointerTo(civil.DateOf(now))}, scan: pointerTo([]spanner.NullDate{})},

// JSON variants

{in: spanner.NullJSON{Valid: true, Value: map[string]any{"a": 13}}, skipeq: true},

{in: []spanner.NullJSON{{Valid: true, Value: map[string]any{"a": 13}}}, skipeq: true},

// Standard library type alias examples

{in: net.IPv6loopback},

{in: time.Duration(1)},

}

for _, test := range tests {

t.Run(fmt.Sprintf("%T", test.in), func(t *testing.T) {

out := test.scan

if out == nil {

inType := reflect.TypeOf(test.in)

out = reflect.New(inType).Interface()

}

err := db.QueryRow("SELECT @in", test.in).Scan(out)

if err != nil {

t.Fatalf("failed to query and scan: %v", err)

}

if test.scan == nil && !test.skipeq {

// if the scanned type and input type are the same

// then we can easily check the result.

outelem := reflect.ValueOf(out).Elem().Interface()

if !reflect.DeepEqual(test.in, outelem) {

t.Fatalf("wrong result:\ngot %#v\nwant %#v", outelem, test.in)

}

}

})

}

}

func TestExecContextDml(t *testing.T) {

skipIfShort(t)

t.Parallel()

ctx := context.Background()

dsn, cleanup, err := createTestDB(ctx,

`CREATE TABLE TestExecContextDml (

key INT64,

testString STRING(1024),

testBytes BYTES(1024),

testInt INT64,

testFloat FLOAT64,

testBool BOOL

) PRIMARY KEY (key)`)

if err != nil {

t.Fatalf("failed to create test db: %v", err)

}

defer cleanup()

// Open db.

db, err := sql.Open("spanner", dsn)

if err != nil {

t.Fatal(err)

}

defer db.Close()

type testExecContextDmlRow struct {

key int

testString string

testBytes []byte

testInt int

testFloat float64

testBool bool

}

type then struct {

query string

wantRows []testExecContextDmlRow

wantError bool

}

tests := []struct {

name string

given string

when string

then then

}{

{

name: "insert single tuple",

when: `

INSERT INTO TestExecContextDml

(key, testString, testBytes, testInt, testFloat, testBool)

VALUES (1, "one", CAST("one" as bytes), 42, 42, true ) `,

then: then{

query: `SELECT * FROM TestExecContextDml WHERE key = 1`,

wantRows: []testExecContextDmlRow{

{key: 1, testString: "one", testBytes: []byte("one"), testInt: 42, testFloat: 42, testBool: true},

},

},

},

{

name: "insert multiple tuples",

when: `

INSERT INTO TestExecContextDml

(key, testString, testBytes, testInt, testFloat, testBool)

VALUES (2, "two", CAST("two" as bytes), 42, 42, true ), (3, "three", CAST("three" as bytes), 42, 42, true )`,

then: then{

query: `SELECT * FROM TestExecContextDml ORDER BY key`,

wantRows: []testExecContextDmlRow{

{key: 2, testString: "two", testBytes: []byte("two"), testInt: 42, testFloat: 42, testBool: true},

{key: 3, testString: "three", testBytes: []byte("three"), testInt: 42, testFloat: 42, testBool: true},

},

},

},

{

name: "insert syntax error",

when: `

INSERT INSERT INTO TestExecContextDml

(key, testString, testBytes, testInt, testFloat, testBool)

VALUES (101, "one", CAST("one" as bytes), 42, 42, true ) `,

then: then{

wantError: true,

},

},

{

name: "insert lower case dml",

when: `

insert into TestExecContextDml

(key, testString, testBytes, testInt, testFloat, testBool)

VALUES (4, "four", CAST("four" as bytes), 42, 42, true ) `,

then: then{

query: `SELECT * FROM TestExecContextDml ORDER BY key`,

wantRows: []testExecContextDmlRow{

{key: 4, testString: "four", testBytes: []byte("four"), testInt: 42, testFloat: 42, testBool: true},

},

},

},

{

name: "insert lower case table name",

when: `

INSERT INTO testexeccontextdml (key, testString, testBytes, testInt, testFloat, testBool)

VALUES (5, "five", CAST("five" as bytes), 42, 42, true ) `,

then: then{

query: `SELECT * FROM TestExecContextDml ORDER BY key`,

wantRows: []testExecContextDmlRow{

{key: 5, testString: "five", testBytes: []byte("five"), testInt: 42, testFloat: 42, testBool: true},

},

},

},

{

name: "wrong types",

when: `

INSERT INSERT INTO TestExecContextDml (key, testString, testBytes, testInt, testFloat, testBool)

VALUES (102, 56, 56, 42, hello, "i should be a bool" ) `,

then: then{

wantError: true,

},

},

{

name: "insert primary key duplicate",

when: `

INSERT INSERT INTO TestExecContextDml (key, testString, testBytes, testInt, testFloat, testBool)

VALUES (1, "one", CAST("one" as bytes), 42, 42, true ), (1, "one", CAST("one" as bytes), 42, 42, true ) `,

then: then{

wantError: true,

},

},

{

name: "insert null into primary key",

when: `

INSERT INSERT INTO TestExecContextDml

(key, testString, testBytes, testInt, testFloat, testBool)

VALUES (null, "one", CAST("one" as bytes), 42, 42, true ) `,

then: then{

wantError: true,

},

},

{

name: "insert too many values",

when: `

INSERT INSERT INTO TestExecContextDml

(key, testString, testBytes, testInt, testFloat, testBool)

VALUES (null, "one", CAST("one" as bytes), 42, 42, true, 42 ) `,

then: then{

wantError: true,

},

},

{

name: "insert too few values",

when: `

INSERT INSERT INTO TestExecContextDml

(key, testString, testBytes, testInt, testFloat, testBool)

VALUES (null, "one", CAST("one" as bytes), 42 ) `,

then: then{

wantError: true,

},

},

{

name: "delete single tuple",

given: `

INSERT INTO TestExecContextDml (key, testString, testBytes, testInt, testFloat, testBool)

VALUES (1, "one", CAST("one" as bytes), 42, 42, true ), (2, "two", CAST("two" as bytes), 42, 42, true )`,

when: `DELETE FROM TestExecContextDml WHERE key = 1`,

then: then{

query: `SELECT * FROM TestExecContextDml`,

wantRows: []testExecContextDmlRow{

{key: 2, testString: "two", testBytes: []byte("two"), testInt: 42, testFloat: 42, testBool: true},

},

},

},

{

name: "delete multiple tuples with subthen",

given: `

INSERT INTO TestExecContextDml (key, testString, testBytes, testInt, testFloat, testBool)

VALUES (1, "one", CAST("one" as bytes), 42, 42, true ), (2, "two", CAST("two" as bytes), 42, 42, true ),

(3, "three", CAST("three" as bytes), 42, 42, true ), (4, "four", CAST("four" as bytes), 42, 42, true )`,

when: `

DELETE FROM TestExecContextDml WHERE key

IN (SELECT key FROM TestExecContextDml WHERE key != 4)`,

then: then{

query: `SELECT * FROM TestExecContextDml ORDER BY key`,

wantRows: []testExecContextDmlRow{

{key: 4, testString: "four", testBytes: []byte("four"), testInt: 42, testFloat: 42, testBool: true},

},

},

},

{

name: "delete all tuples",

given: `

INSERT INTO TestExecContextDml (key, testString, testBytes, testInt, testFloat, testBool)

VALUES (1, "one", CAST("one" as bytes), 42, 42, true ), (2, "two", CAST("two" as bytes), 42, 42, true ),

(3, "three", CAST("three" as bytes), 42, 42, true ), (4, "four", CAST("four" as bytes), 42, 42, true )`,

when: `DELETE FROM TestExecContextDml WHERE true`,

then: then{

query: `SELECT * FROM TestExecContextDml`,

wantRows: []testExecContextDmlRow{},

},

},

{

name: "update one tuple",

given: `

INSERT INTO TestExecContextDml (key, testString, testBytes, testInt, testFloat, testBool)

VALUES (1, "one", CAST("one" as bytes), 42, 42, true ), (2, "two", CAST("two" as bytes), 42, 42, true ),

(3, "three", CAST("three" as bytes), 42, 42, true )`,

when: `UPDATE TestExecContextDml SET testSTring = "updated" WHERE key = 1`,

then: then{

query: `SELECT * FROM TestExecContextDml ORDER BY key`,

wantRows: []testExecContextDmlRow{

{key: 1, testString: "updated", testBytes: []byte("one"), testInt: 42, testFloat: 42, testBool: true},

{key: 2, testString: "two", testBytes: []byte("two"), testInt: 42, testFloat: 42, testBool: true},

{key: 3, testString: "three", testBytes: []byte("three"), testInt: 42, testFloat: 42, testBool: true},

},

},

},

{

name: "update multiple tuples",

given: `

INSERT INTO TestExecContextDml (key, testString, testBytes, testInt, testFloat, testBool)

VALUES (1, "one", CAST("one" as bytes), 42, 42, true ), (2, "two", CAST("two" as bytes), 42, 42, true ),

(3, "three", CAST("three" as bytes), 42, 42, true )`,

when: `UPDATE TestExecContextDml SET testSTring = "updated" WHERE key = 2 OR key = 3`,

then: then{

query: `SELECT * FROM TestExecContextDml ORDER BY key`,

wantRows: []testExecContextDmlRow{

{key: 1, testString: "one", testBytes: []byte("one"), testInt: 42, testFloat: 42, testBool: true},

{key: 2, testString: "updated", testBytes: []byte("two"), testInt: 42, testFloat: 42, testBool: true},

{key: 3, testString: "updated", testBytes: []byte("three"), testInt: 42, testFloat: 42, testBool: true},

},

},

},

{

name: "update primary key",

given: `

INSERT INTO TestExecContextDml (key, testString, testBytes, testInt, testFloat, testBool)

VALUES (1, "one", CAST("one" as bytes), 42, 42, true )`,

when: `UPDATE TestExecContextDml SET key = 100 WHERE key = 1`,

then: then{

wantError: true,

},

},

{

name: "update nothing",

given: `

INSERT INTO TestExecContextDml (key, testString, testBytes, testInt, testFloat, testBool)

VALUES (1, "one", CAST("one" as bytes), 42, 42, true ), (2, "two", CAST("two" as bytes), 42, 42, true ),

(3, "three", CAST("three" as bytes), 42, 42, true )`,

when: `UPDATE TestExecContextDml SET testSTring = "nothing" WHERE false`,

then: then{

query: `SELECT * FROM TestExecContextDml ORDER BY key`,

wantRows: []testExecContextDmlRow{

{key: 1, testString: "one", testBytes: []byte("one"), testInt: 42, testFloat: 42, testBool: true},

{key: 2, testString: "two", testBytes: []byte("two"), testInt: 42, testFloat: 42, testBool: true},

{key: 3, testString: "three", testBytes: []byte("three"), testInt: 42, testFloat: 42, testBool: true},

},

},

},

{

name: "update everything",

given: `

INSERT INTO TestExecContextDml (key, testString, testBytes, testInt, testFloat, testBool)

VALUES (1, "one", CAST("one" as bytes), 42, 42, true ), (2, "two", CAST("two" as bytes), 42, 42, true ),

(3, "three", CAST("three" as bytes), 42, 42, true )`,

when: `UPDATE TestExecContextDml SET testSTring = "everything" WHERE true `,

then: then{

query: `SELECT * FROM TestExecContextDml WHERE testString = "everything"`,

wantRows: []testExecContextDmlRow{

{key: 1, testString: "everything", testBytes: []byte("one"), testInt: 42, testFloat: 42, testBool: true},

{key: 2, testString: "everything", testBytes: []byte("two"), testInt: 42, testFloat: 42, testBool: true},

{key: 3, testString: "everything", testBytes: []byte("three"), testInt: 42, testFloat: 42, testBool: true},

},

},

},

{

name: "update to wrong type",

when: `UPDATE TestExecContextDml SET testBool = 100 WHERE key = 1`,

then: then{

wantError: true,

},

},

}

// Run tests.

for _, tc := range tests {

// Set up test table.

if tc.given != "" {

if _, err = db.ExecContext(ctx, tc.given); err != nil {

t.Errorf("%s: error setting up table: %v", tc.name, err)

}

}

// Run DML.

_, err = db.ExecContext(ctx, tc.when)

if (err != nil) && (!tc.then.wantError) {

t.Errorf("%s: unexpected query error: %v", tc.name, err)

}

if (err == nil) && (tc.then.wantError) {

t.Errorf("%s: expected query error but error was %v", tc.name, err)

}

// Check rows returned.

if tc.then.query != "" {

rows, err := db.QueryContext(ctx, tc.then.query)

if err != nil {

t.Errorf("%s: error from QueryContext : %v", tc.name, err)

}

got := []testExecContextDmlRow{}

for rows.Next() {

var curr testExecContextDmlRow

err := rows.Scan(

&curr.key, &curr.testString, &curr.testBytes, &curr.testInt, &curr.testFloat, &curr.testBool)

if err != nil {

t.Errorf("%s: error while scanning rows: %v", tc.name, err)

}

got = append(got, curr)

}

rows.Close()

if err = rows.Err(); err != nil {

t.Errorf("%s: error on rows.close: %v", tc.name, err)

}

if !reflect.DeepEqual(tc.then.wantRows, got) {

t.Errorf("Unexpecred rows: %s. want: %v, got: %v", tc.name, tc.then.wantRows, got)

}