Add methods for test spanner db
Change-Id: I53ebab5697a64612fd7098b777202c3d7b5f41a6
Reviewed-on: https://skia-review.googlesource.com/c/buildbot/+/984676
Auto-Submit: Prakhar Asthana <pasthana@google.com>
Commit-Queue: Wenbin Zhang <wenbinzhang@google.com>
Reviewed-by: Wenbin Zhang <wenbinzhang@google.com>
diff --git a/golden/go/sql/sqltest/BUILD.bazel b/golden/go/sql/sqltest/BUILD.bazel
index 92bab84..0b94b7c 100644
--- a/golden/go/sql/sqltest/BUILD.bazel
+++ b/golden/go/sql/sqltest/BUILD.bazel
@@ -10,10 +10,13 @@
"//bazel/external/cockroachdb",
"//go/emulators",
"//go/emulators/cockroachdb_instance",
+ "//go/emulators/gcp_emulator",
+ "//go/emulators/pgadapter",
"//go/skerr",
"//go/sql/sqlutil",
"//go/util",
"//golden/go/sql/schema",
+ "//golden/go/sql/schema/spanner",
"@com_github_jackc_pgx_v4//pgxpool",
"@com_github_stretchr_testify//require",
],
diff --git a/golden/go/sql/sqltest/sqltest.go b/golden/go/sql/sqltest/sqltest.go
index 0d8a6ad..10c231d 100644
--- a/golden/go/sql/sqltest/sqltest.go
+++ b/golden/go/sql/sqltest/sqltest.go
@@ -18,10 +18,13 @@
"go.skia.org/infra/bazel/external/cockroachdb"
"go.skia.org/infra/go/emulators"
"go.skia.org/infra/go/emulators/cockroachdb_instance"
+ "go.skia.org/infra/go/emulators/gcp_emulator"
+ "go.skia.org/infra/go/emulators/pgadapter"
"go.skia.org/infra/go/skerr"
"go.skia.org/infra/go/sql/sqlutil"
"go.skia.org/infra/go/util"
"go.skia.org/infra/golden/go/sql/schema"
+ "go.skia.org/infra/golden/go/sql/schema/spanner"
)
// NewCockroachDBForTests creates a randomly named database on a test CockroachDB instance (aka the
@@ -70,6 +73,35 @@
return db
}
+func NewSpannerDBForTests(ctx context.Context, t testing.TB) *pgxpool.Pool {
+ // Ensure that both spanner emulator and pgadapter are running first.
+ gcp_emulator.RequireSpanner(t)
+ pgadapter.Require(t)
+
+ n, err := rand.Int(rand.Reader, big.NewInt(math.MaxInt64))
+ require.NoError(t, err)
+ databaseName := "for_tests" + n.String()
+
+ if len(databaseName) > 30 {
+ databaseName = databaseName[:30]
+ }
+ host := emulators.GetEmulatorHostEnvVar(emulators.PGAdapter)
+ connectionString := fmt.Sprintf("postgresql://root@%s/%s?sslmode=disable", host, databaseName)
+
+ conn, err := pgxpool.Connect(ctx, connectionString)
+ require.NoError(t, err)
+ return conn
+}
+
+// NewSpannerDBForTestsWithProductionSchema returns a SQL database with the production
+// schema. It will be aimed at a randomly named database.
+func NewSpannerDBForTestsWithProductionSchema(ctx context.Context, t testing.TB) *pgxpool.Pool {
+ db := NewSpannerDBForTests(ctx, t)
+ _, err := db.Exec(ctx, spanner.Schema)
+ require.NoError(t, err)
+ return db
+}
+
// SQLExporter is an abstraction around a type that can be written as a single row in a SQL table.
type SQLExporter interface {
// ToSQLRow returns the column names and the column data that should be written for this row.
@@ -158,6 +190,11 @@
return skerr.Wrapf(err, "Inserting %d rows into table %s", numRows, name)
}
+// ----------------------- WARNING ------------------------
+//
+// INCOMPATIBLE WITH SPANNER EMULATOR
+//
+// --------------------------------------------------------
// GetAllRows returns all rows for a given table. The passed in row param must be a pointer type
// that implements the sqltest.Scanner interface. The passed in row may optionally implement the
// sqltest.RowsOrder interface to specify an ordering to return the rows in (this can make for
@@ -217,6 +254,7 @@
// prior to the update, and that the slice with new rows contains the affected row after the
// update.
func GetRowChanges[T any](ctx context.Context, t *testing.T, db *pgxpool.Pool, table string, t0 time.Time) (missingRows, newRows []T) {
+
getRows := func(statement string) []T {
rows, err := db.Query(ctx, statement)
require.NoError(t, err)
diff --git a/golden/go/sql/sqltest/sqltest_test.go b/golden/go/sql/sqltest/sqltest_test.go
index 24342bc..6635568 100644
--- a/golden/go/sql/sqltest/sqltest_test.go
+++ b/golden/go/sql/sqltest/sqltest_test.go
@@ -14,11 +14,11 @@
"go.skia.org/infra/golden/go/sql/sqltest"
)
-func TestBulkInsertDataTables_ValidData_Success(t *testing.T) {
+func TestBulkInsertDataTables_ValidData_Success_cdb(t *testing.T) {
ctx := context.Background()
db := sqltest.NewCockroachDBForTests(ctx, t)
- _, err := db.Exec(ctx, testSchema)
+ _, err := db.Exec(ctx, testSchema_cdb)
require.NoError(t, err)
err = sqltest.BulkInsertDataTables(ctx, db, testTables{
@@ -74,11 +74,11 @@
}}, actualRows2)
}
-func TestBulkInsertDataTables_InvalidForeignKeys_ReturnsError(t *testing.T) {
+func TestBulkInsertDataTables_InvalidForeignKeys_ReturnsError_cdb(t *testing.T) {
ctx := context.Background()
db := sqltest.NewCockroachDBForTests(ctx, t)
- _, err := db.Exec(ctx, testSchema)
+ _, err := db.Exec(ctx, testSchema_cdb)
require.NoError(t, err)
err = sqltest.BulkInsertDataTables(ctx, db, testTables{
@@ -92,11 +92,11 @@
require.Error(t, err)
}
-func TestGetAllRows_RowsOrderByDefined_ReturnsInOrder(t *testing.T) {
+func TestGetAllRows_RowsOrderByDefined_ReturnsInOrder_cdb(t *testing.T) {
ctx := context.Background()
db := sqltest.NewCockroachDBForTests(ctx, t)
- _, err := db.Exec(ctx, testSchema)
+ _, err := db.Exec(ctx, testSchema_cdb)
require.NoError(t, err)
err = sqltest.BulkInsertDataTables(ctx, db, testTables{
@@ -118,11 +118,11 @@
}, actualRows)
}
-func TestGetAllRows_RowsOrderNotDefined_ReturnsInAnyOrder(t *testing.T) {
+func TestGetAllRows_RowsOrderNotDefined_ReturnsInAnyOrder_cdb(t *testing.T) {
ctx := context.Background()
db := sqltest.NewCockroachDBForTests(ctx, t)
- _, err := db.Exec(ctx, testSchema)
+ _, err := db.Exec(ctx, testSchema_cdb)
require.NoError(t, err)
err = sqltest.BulkInsertDataTables(ctx, db, testTables{
@@ -142,11 +142,11 @@
}, actualRows)
}
-func TestGetRowChanges_RowsOrderDefined_ReturnsInOrder(t *testing.T) {
+func TestGetRowChanges_RowsOrderDefined_ReturnsInOrder_cdb(t *testing.T) {
ctx := context.Background()
db := sqltest.NewCockroachDBForTests(ctx, t)
- _, err := db.Exec(ctx, testSchema)
+ _, err := db.Exec(ctx, testSchema_cdb)
require.NoError(t, err)
err = sqltest.BulkInsertDataTables(ctx, db, testTables{
@@ -177,11 +177,11 @@
}, newRows)
}
-func TestGetRowChanges_RowsOrderNotDefined_ReturnsInAnyOrder(t *testing.T) {
+func TestGetRowChanges_RowsOrderNotDefined_ReturnsInAnyOrder_cdb(t *testing.T) {
ctx := context.Background()
db := sqltest.NewCockroachDBForTests(ctx, t)
- _, err := db.Exec(ctx, testSchema)
+ _, err := db.Exec(ctx, testSchema_cdb)
require.NoError(t, err)
err = sqltest.BulkInsertDataTables(ctx, db, testTables{
@@ -212,6 +212,134 @@
}, newRows)
}
+func TestBulkInsertDataTables_ValidData_Success(t *testing.T) {
+
+ ctx := context.Background()
+ db := sqltest.NewSpannerDBForTests(ctx, t)
+ _, err := db.Exec(ctx, testSchema)
+ require.NoError(t, err)
+
+ err = sqltest.BulkInsertDataTables(ctx, db, testTables{
+ TableOne: []tableOneRow{
+ {ColumnOne: "apple", ColumnTwo: "banana"},
+ {ColumnOne: "cherry", ColumnTwo: "durian"},
+ {ColumnOne: "elderberry", ColumnTwo: "fig"},
+ },
+ TableTwo: []tableTwoRow{
+ // The ComputedColumn is intentionally omitted here to make sure the appropriate
+ // value is inserted at the SQL level.
+ {SpecialColumnOne: []byte("arugula"), ForeignColumnTwo: "apple"},
+ {SpecialColumnOne: []byte("beet"), ForeignColumnTwo: "elderberry"},
+ },
+ })
+ require.NoError(t, err)
+
+ // Spotcheck data
+ rows, err := db.Query(ctx, `SELECT * FROM TableOne`)
+ require.NoError(t, err)
+ defer rows.Close()
+ var actualRows []tableOneRow
+ for rows.Next() {
+ var r tableOneRow
+ assert.NoError(t, rows.Scan(&r.ColumnOne, &r.ColumnTwo))
+ actualRows = append(actualRows, r)
+ }
+ assert.ElementsMatch(t, []tableOneRow{{
+ ColumnOne: "apple", ColumnTwo: "banana",
+ }, {
+ ColumnOne: "cherry", ColumnTwo: "durian",
+ }, {
+ ColumnOne: "elderberry", ColumnTwo: "fig",
+ }}, actualRows)
+
+ rows, err = db.Query(ctx, `SELECT * FROM TableTwo`)
+ require.NoError(t, err)
+ defer rows.Close()
+ var actualRows2 []tableTwoRow
+ for rows.Next() {
+ var r tableTwoRow
+ assert.NoError(t, rows.Scan(&r.SpecialColumnOne, &r.ForeignColumnTwo, &r.ComputedColumnThree))
+ actualRows2 = append(actualRows2, r)
+ }
+ assert.ElementsMatch(t, []tableTwoRow{{
+ SpecialColumnOne: []byte("arugula"),
+ ForeignColumnTwo: "apple",
+ ComputedColumnThree: 5,
+ }, {
+ SpecialColumnOne: []byte("beet"),
+ ForeignColumnTwo: "elderberry",
+ ComputedColumnThree: 10,
+ }}, actualRows2)
+}
+
+func TestBulkInsertDataTables_InvalidForeignKeys_ReturnsError(t *testing.T) {
+
+ ctx := context.Background()
+ db := sqltest.NewSpannerDBForTests(ctx, t)
+ _, err := db.Exec(ctx, testSchema)
+ require.NoError(t, err)
+
+ err = sqltest.BulkInsertDataTables(ctx, db, testTables{
+ TableOne: []tableOneRow{
+ {ColumnOne: "apple", ColumnTwo: "banana"},
+ },
+ TableTwo: []tableTwoRow{
+ {SpecialColumnOne: []byte("beet"), ForeignColumnTwo: "elderberry"},
+ },
+ })
+ require.Error(t, err)
+}
+
+func TestGetAllRows_RowsOrderByDefined_ReturnsInOrder(t *testing.T) {
+
+ ctx := context.Background()
+ db := sqltest.NewSpannerDBForTests(ctx, t)
+ _, err := db.Exec(ctx, testSchema)
+ require.NoError(t, err)
+
+ err = sqltest.BulkInsertDataTables(ctx, db, testTables{
+ TableThree: []tableThreeRow{
+ {ColumnOne: "apricots", ColumnBool: schema.NBNull, ColumnTS: ts("2021-02-01T00:00:00Z")},
+ {ColumnOne: "chorizo", ColumnBool: schema.NBFalse, ColumnTS: ts("2021-04-01T00:00:00Z")},
+ {ColumnOne: "extra cheese", ColumnBool: schema.NBTrue, ColumnTS: ts("2021-03-01T00:00:00Z")},
+ },
+ })
+ require.NoError(t, err)
+
+ actualRows := sqltest.GetAllRows(ctx, t, db, "TableThree", &tableThreeRow{}).([]tableThreeRow)
+ // The order matters because tableThreeRow has RowsOrderBy defined, which should return
+ // the rows in descending order by column.
+ assert.Equal(t, []tableThreeRow{
+ {ColumnOne: "chorizo", ColumnBool: schema.NBFalse, ColumnTS: ts("2021-04-01T00:00:00Z")},
+ {ColumnOne: "extra cheese", ColumnBool: schema.NBTrue, ColumnTS: ts("2021-03-01T00:00:00Z")},
+ {ColumnOne: "apricots", ColumnBool: schema.NBNull, ColumnTS: ts("2021-02-01T00:00:00Z")},
+ }, actualRows)
+}
+
+func TestGetAllRows_RowsOrderNotDefined_ReturnsInAnyOrder(t *testing.T) {
+
+ ctx := context.Background()
+ db := sqltest.NewSpannerDBForTests(ctx, t)
+ _, err := db.Exec(ctx, testSchema)
+ require.NoError(t, err)
+
+ err = sqltest.BulkInsertDataTables(ctx, db, testTables{
+ TableOne: []tableOneRow{
+ {ColumnOne: "apple", ColumnTwo: "banana"},
+ {ColumnOne: "cherry", ColumnTwo: "durian"},
+ {ColumnOne: "elderberry", ColumnTwo: "fig"},
+ },
+ })
+ require.NoError(t, err)
+
+ actualRows := sqltest.GetAllRows(ctx, t, db, "TableOne", &tableOneRow{}).([]tableOneRow)
+ assert.ElementsMatch(t, []tableOneRow{
+ {ColumnOne: "apple", ColumnTwo: "banana"},
+ {ColumnOne: "cherry", ColumnTwo: "durian"},
+ {ColumnOne: "elderberry", ColumnTwo: "fig"},
+ }, actualRows)
+}
+
func ts(s string) time.Time {
ct, err := time.Parse(time.RFC3339, s)
if err != nil {
@@ -220,14 +348,14 @@
return ct.UTC()
}
-const testSchema = `CREATE TABLE IF NOT EXISTS TableOne (
+const testSchema_cdb = `CREATE TABLE IF NOT EXISTS TableOne (
column_one STRING PRIMARY KEY,
column_two STRING NOT NULL
);
CREATE TABLE IF NOT EXISTS TableTwo (
special_column_one BYTES PRIMARY KEY,
foreign_column_two STRING REFERENCES TableOne (column_one),
- computed_column_three INT8 AS (char_length(foreign_column_two)) STORED NOT NULL
+ computed_column_three INT8 NOT NULL
);
CREATE TABLE IF NOT EXISTS TableThree (
column_one STRING PRIMARY KEY,
@@ -236,6 +364,25 @@
);
`
+const testSchema = `CREATE TABLE TableOne (
+ column_one TEXT PRIMARY KEY,
+ column_two TEXT NOT NULL
+);
+
+CREATE TABLE TableTwo (
+ special_column_one BYTEA PRIMARY KEY,
+ foreign_column_two TEXT,
+ computed_column_three INT8 NOT NULL,
+ CONSTRAINT FK_TableTwo_TableOne FOREIGN KEY (foreign_column_two) REFERENCES TableOne (column_one)
+);
+
+CREATE TABLE TableThree (
+ column_one TEXT PRIMARY KEY,
+ column_bool BOOL,
+ column_ts TIMESTAMP WITH TIME ZONE NOT NULL
+);
+ `
+
type testTables struct {
TableOne []tableOneRow
TableTwo []tableTwoRow
@@ -268,8 +415,9 @@
}
func (r tableTwoRow) ToSQLRow() (colNames []string, colData []interface{}) {
- return []string{"special_column_one", "foreign_column_two"}, []interface{}{
- r.SpecialColumnOne, r.ForeignColumnTwo,
+ computedLength := len(r.ForeignColumnTwo)
+ return []string{"special_column_one", "foreign_column_two", "computed_column_three"}, []interface{}{
+ r.SpecialColumnOne, r.ForeignColumnTwo, computedLength,
}
}
