Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package spanner
import (
"context"
"fmt"
"math"
"os"
"testing"
"time"
"cloud.google.com/go/spanner"
spannerdb "cloud.google.com/go/spanner/admin/database/apiv1"
"cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
sppb "cloud.google.com/go/spanner/apiv1/spannerpb"
"github.com/stretchr/testify/require"
"google.golang.org/protobuf/types/known/structpb"
"google.golang.org/protobuf/types/known/timestamppb"
v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
"github.com/bytebase/bytebase/backend/plugin/db"
"github.com/bytebase/bytebase/backend/plugin/db/util"
)
// testDatabase returns the database that SPANNER_TEST_DATABASE names, as
// projects/default/instances/default/databases/d, in the Spanner that the client
// reaches without TLS or credentials through SPANNER_EMULATOR_HOST. CI provides
// no Spanner, so the test is skipped without them.
func testDatabase(t *testing.T) string {
t.Helper()
database := os.Getenv("SPANNER_TEST_DATABASE")
if os.Getenv("SPANNER_EMULATOR_HOST") == "" || database == "" {
t.Skip("set SPANNER_EMULATOR_HOST and SPANNER_TEST_DATABASE to run against Spanner")
}
return database
}
// TestExplainDMLWithoutRunningIt needs a Spanner that plans queries, which the
// emulator does not do and Spanner Omni does.
func TestExplainDMLWithoutRunningIt(t *testing.T) {
database := testDatabase(t)
a := require.New(t)
ctx := context.Background()
admin, err := spannerdb.NewDatabaseAdminClient(ctx)
a.NoError(err)
defer admin.Close()
table := fmt.Sprintf("ExplainDML%d", time.Now().UnixNano())
updateDDL := func(statement string) {
op, err := admin.UpdateDatabaseDdl(ctx, &databasepb.UpdateDatabaseDdlRequest{
Database: database,
Statements: []string{statement},
})
a.NoError(err)
a.NoError(op.Wait(ctx))
}
updateDDL(fmt.Sprintf("CREATE TABLE %s (Id INT64 NOT NULL, Total INT64) PRIMARY KEY (Id)", table))
defer updateDDL(fmt.Sprintf("DROP TABLE %s", table))
client, err := spanner.NewClient(ctx, database)
a.NoError(err)
defer client.Close()
_, err = client.Apply(ctx, []*spanner.Mutation{
spanner.Insert(table, []string{"Id", "Total"}, []any{int64(1), int64(10)}),
})
a.NoError(err)
driver := &Driver{client: client}
results, err := driver.explainStatement(ctx, fmt.Sprintf(
"UPDATE %[1]s SET Total = Total + 1 WHERE Id = 1; SELECT Total FROM %[1]s", table,
))
a.NoError(err)
a.Len(results, 2)
for _, result := range results {
a.Empty(result.Error, result.Statement)
a.Contains(result.Rows[0].Values[0].GetStringValue(), `"planNodes"`)
}
row, err := client.Single().ReadRow(ctx, table, spanner.Key{int64(1)}, []string{"Total"})
a.NoError(err)
var total int64
a.NoError(row.Column(0, &total))
a.Equal(int64(10), total)
}
func TestQueryConnLimitsQueryStartingWithWith(t *testing.T) {
a := require.New(t)
ctx := context.Background()
client, err := spanner.NewClient(ctx, testDatabase(t))
a.NoError(err)
defer client.Close()
driver := &Driver{client: client}
for _, statement := range []string{
"WITH n AS (SELECT x FROM UNNEST([3, 1, 2]) AS x) SELECT x FROM n ORDER BY x",
// The rewrite keeps a non-literal LIMIT, so the driver caps the rows.
"WITH n AS (SELECT x FROM UNNEST([3, 1, 2]) AS x) SELECT x FROM n ORDER BY x LIMIT CAST(3 AS INT64)",
} {
results, err := driver.QueryConn(ctx, nil, statement, db.QueryContext{
Limit: 2,
MaximumSQLResultSize: math.MaxInt64,
})
a.NoError(err)
a.Len(results, 1)
a.Empty(results[0].Error, results[0].Statement)
var values []int64
for _, row := range results[0].Rows {
values = append(values, row.Values[0].GetInt64Value())
}
a.Equal([]int64{1, 2}, values, results[0].Statement)
}
}
func TestGetDatabaseFromDSN(t *testing.T) {
tests := []struct {
dsn string
match bool
want string
}{
{
dsn: "projects/p/instances/i/databases/d",
match: true,
want: "d",
},
{
dsn: "projects/p/instances/i/databases/",
match: false,
want: "",
},
}
a := require.New(t)
for i := range tests {
test := tests[i]
got, err := getDatabaseFromDSN(test.dsn)
if test.match {
a.NoError(err)
a.Equal(test.want, got)
} else {
a.Error(err)
}
}
}
func TestGetColumnTypeName(t *testing.T) {
tests := []struct {
spannerType sppb.Type
want string
}{
{
spannerType: sppb.Type{
Code: sppb.TypeCode_JSON,
},
want: "JSON",
},
{
spannerType: sppb.Type{
Code: sppb.TypeCode_DATE,
},
want: "DATE",
},
{
spannerType: sppb.Type{
Code: sppb.TypeCode_TIMESTAMP,
},
want: "TIMESTAMP",
},
{
spannerType: sppb.Type{
Code: sppb.TypeCode_ARRAY,
ArrayElementType: &sppb.Type{
Code: sppb.TypeCode_BYTES,
},
},
want: "[]BYTES",
},
}
a := require.New(t)
for i := range tests {
got, err := getColumnTypeName(&tests[i].spannerType)
a.NoError(err)
a.Equal(tests[i].want, got)
}
}
// Helper function to create expected timestamp RowValue for tests
func timestampRowValue(rfc3339 string, accuracy int32) *v1pb.RowValue {
t, _ := time.Parse(time.RFC3339Nano, rfc3339)
return &v1pb.RowValue{Kind: &v1pb.RowValue_TimestampValue{
TimestampValue: &v1pb.RowValue_Timestamp{
GoogleTimestamp: timestamppb.New(t),
Accuracy: accuracy,
},
}}
}
func TestConvertSpannerValue(t *testing.T) {
tests := []struct {
name string
colType *sppb.Type
value *structpb.Value
expected *v1pb.RowValue
}{
{
name: "nil value",
colType: nil,
value: nil,
expected: util.NullRowValue,
},
{
name: "null value",
colType: &sppb.Type{Code: sppb.TypeCode_STRING},
value: structpb.NewNullValue(),
expected: util.NullRowValue,
},
{
name: "string value",
colType: &sppb.Type{Code: sppb.TypeCode_STRING},
value: structpb.NewStringValue("hello"),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{
StringValue: "hello",
}},
},
{
name: "bool value - true",
colType: &sppb.Type{Code: sppb.TypeCode_BOOL},
value: structpb.NewBoolValue(true),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_BoolValue{
BoolValue: true,
}},
},
{
name: "bool value - false",
colType: &sppb.Type{Code: sppb.TypeCode_BOOL},
value: structpb.NewBoolValue(false),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_BoolValue{
BoolValue: false,
}},
},
{
name: "float64 value",
colType: &sppb.Type{Code: sppb.TypeCode_FLOAT64},
value: structpb.NewNumberValue(3.14159),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_DoubleValue{
DoubleValue: 3.14159,
}},
},
{
name: "int64 small value",
colType: &sppb.Type{Code: sppb.TypeCode_INT64},
value: structpb.NewNumberValue(42),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_Int64Value{
Int64Value: 42,
}},
},
{
name: "int64 large value - near 2^53",
colType: &sppb.Type{Code: sppb.TypeCode_INT64},
value: structpb.NewNumberValue(9007199254740992), // 2^53
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_Int64Value{
Int64Value: 9007199254740992,
}},
},
{
name: "int64 large value - within float64 safe range",
colType: &sppb.Type{Code: sppb.TypeCode_INT64},
value: structpb.NewNumberValue(9007199254740991), // 2^53 - 1 (max safe int in float64)
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_Int64Value{
Int64Value: 9007199254740991,
}},
},
{
name: "int64 negative large value - within float64 safe range",
colType: &sppb.Type{Code: sppb.TypeCode_INT64},
value: structpb.NewNumberValue(-9007199254740991), // -(2^53 - 1)
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_Int64Value{
Int64Value: -9007199254740991,
}},
},
{
name: "int64 as string - small value",
colType: &sppb.Type{Code: sppb.TypeCode_INT64},
value: structpb.NewStringValue("42"),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_Int64Value{
Int64Value: 42,
}},
},
{
name: "int64 as string - max value",
colType: &sppb.Type{Code: sppb.TypeCode_INT64},
value: structpb.NewStringValue("9223372036854775807"),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_Int64Value{
Int64Value: math.MaxInt64,
}},
},
{
name: "int64 as string - min value",
colType: &sppb.Type{Code: sppb.TypeCode_INT64},
value: structpb.NewStringValue("-9223372036854775808"),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_Int64Value{
Int64Value: math.MinInt64,
}},
},
{
name: "int64 as string - invalid falls back to string",
colType: &sppb.Type{Code: sppb.TypeCode_INT64},
value: structpb.NewStringValue("not-a-number"),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{
StringValue: "not-a-number",
}},
},
{
name: "number without type metadata defaults to double",
colType: nil,
value: structpb.NewNumberValue(42),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_DoubleValue{
DoubleValue: 42,
}},
},
{
name: "array value",
colType: &sppb.Type{Code: sppb.TypeCode_ARRAY},
value: structpb.NewListValue(&structpb.ListValue{Values: []*structpb.Value{structpb.NewNumberValue(1), structpb.NewNumberValue(2), structpb.NewNumberValue(3)}}),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{
StringValue: "[1,2,3]",
}},
},
{
name: "struct value",
colType: &sppb.Type{Code: sppb.TypeCode_STRUCT},
value: structpb.NewStructValue(&structpb.Struct{Fields: map[string]*structpb.Value{
"name": structpb.NewStringValue("Alice"),
"age": structpb.NewNumberValue(30),
}}),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{
StringValue: `{"age":30,"name":"Alice"}`,
}},
},
{
name: "nested array",
colType: &sppb.Type{Code: sppb.TypeCode_ARRAY},
value: structpb.NewListValue(&structpb.ListValue{Values: []*structpb.Value{
structpb.NewListValue(&structpb.ListValue{Values: []*structpb.Value{structpb.NewNumberValue(1), structpb.NewNumberValue(2)}}),
structpb.NewListValue(&structpb.ListValue{Values: []*structpb.Value{structpb.NewNumberValue(3), structpb.NewNumberValue(4)}}),
}}),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{
StringValue: "[[1,2],[3,4]]",
}},
},
{
name: "array of structs",
colType: &sppb.Type{Code: sppb.TypeCode_ARRAY},
value: structpb.NewListValue(&structpb.ListValue{Values: []*structpb.Value{
structpb.NewStructValue(&structpb.Struct{Fields: map[string]*structpb.Value{"id": structpb.NewNumberValue(1)}}),
structpb.NewStructValue(&structpb.Struct{Fields: map[string]*structpb.Value{"id": structpb.NewNumberValue(2)}}),
}}),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{
StringValue: `[{"id":1},{"id":2}]`,
}},
},
{
name: "timestamp value",
colType: &sppb.Type{Code: sppb.TypeCode_TIMESTAMP},
value: structpb.NewStringValue("2024-01-15T10:30:45.123456Z"),
expected: timestampRowValue("2024-01-15T10:30:45.123456Z", 6),
},
{
name: "timestamp with nanosecond precision",
colType: &sppb.Type{Code: sppb.TypeCode_TIMESTAMP},
value: structpb.NewStringValue("2024-01-15T10:30:45.123456789Z"),
expected: timestampRowValue("2024-01-15T10:30:45.123456789Z", 9),
},
{
name: "timestamp without fractional seconds",
colType: &sppb.Type{Code: sppb.TypeCode_TIMESTAMP},
value: structpb.NewStringValue("2024-01-15T10:30:45Z"),
expected: timestampRowValue("2024-01-15T10:30:45Z", 6),
},
{
name: "timestamp invalid format falls back to string",
colType: &sppb.Type{Code: sppb.TypeCode_TIMESTAMP},
value: structpb.NewStringValue("not-a-timestamp"),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{
StringValue: "not-a-timestamp",
}},
},
{
name: "bytes value",
colType: &sppb.Type{Code: sppb.TypeCode_BYTES},
value: structpb.NewStringValue("SGVsbG8gV29ybGQ="), // "Hello World" in base64
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_BytesValue{
BytesValue: []byte("Hello World"),
}},
},
{
name: "bytes invalid base64 falls back to string",
colType: &sppb.Type{Code: sppb.TypeCode_BYTES},
value: structpb.NewStringValue("not-valid-base64!"),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{
StringValue: "not-valid-base64!",
}},
},
{
name: "date value",
colType: &sppb.Type{Code: sppb.TypeCode_DATE},
value: structpb.NewStringValue("2024-01-15"),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{
StringValue: "2024-01-15",
}},
},
{
name: "json value",
colType: &sppb.Type{Code: sppb.TypeCode_JSON},
value: structpb.NewStringValue(`{"name":"Alice","age":30}`),
expected: &v1pb.RowValue{Kind: &v1pb.RowValue_StringValue{
StringValue: `{"name":"Alice","age":30}`,
}},
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
a := require.New(t)
result := convertSpannerValue(tt.colType, tt.value)
a.Equal(tt.expected, result)
})
}
}