Repository: cloudspannerecosystem/harbourbridge
Pull request #51 (2020-03-10 08:35:13)

Add infoschema unit test.
Adds infoschema_test.go and does minor refactor to allow use of
go-sql-mock library (moves some functionality from infoschema.go to
main.go: now all flag process happens in main.go).


Patch:
diff --git a/go.mod b/go.mod
index bbeb5dc..12cb399 100644
--- a/go.mod
+++ b/go.mod
@@ -5,6 +5,7 @@ go 1.13
 require (
 	cloud.google.com/go v0.46.3
 	cloud.google.com/go/spanner v1.0.1-0.20191018063856-b24632667359
+	github.com/DATA-DOG/go-sqlmock v1.4.1
 	github.com/goware/modvendor v0.0.0-20190516042800-ce72b408a8fe // indirect
 	github.com/lfittl/pg_query_go v1.0.0
 	github.com/lib/pq v1.3.0
diff --git a/go.sum b/go.sum
index d6f3735..1f6aa38 100644
--- a/go.sum
+++ b/go.sum
@@ -18,6 +18,8 @@ cloud.google.com/go/spanner v1.0.1-0.20191018063856-b24632667359/go.mod h1:JSevd
 cloud.google.com/go/storage v1.0.0/go.mod h1:IhtSnM/ZTZV8YYJWCY8RULGVqBDmpoyjwiyrjsg+URw=
 github.com/BurntSushi/toml v0.3.1/go.mod h1:xHWCNGjB5oqiDr8zfno3MHue2Ht5sIBksp03qcyfWMU=
 github.com/BurntSushi/xgb v0.0.0-20160522181843-27f122750802/go.mod h1:IVnqGOEym/WlBOVXweHU+Q+/VP0lqqI8lqeDx9IjBqo=
+github.com/DATA-DOG/go-sqlmock v1.4.1 h1:ThlnYciV1iM/V0OSF/dtkqWb6xo5qITT1TJBG1MRDJM=
+github.com/DATA-DOG/go-sqlmock v1.4.1/go.mod h1:f/Ixk793poVmq4qj/V1dPUg2JEAKC73Q5eFN3EC/SaM=
 github.com/client9/misspell v0.3.4/go.mod h1:qj6jICC3Q7zFZvVWo7KLAzC3yx5G7kyvSDkc90ppPyw=
 github.com/davecgh/go-spew v1.1.0 h1:ZDRjVQ15GmhC3fiQ8ni8+OwkZQO4DARzQgrnXU1Liz8=
 github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
diff --git a/internal/infoschema.go b/internal/infoschema.go
index 41d68d2..25fa371 100644
--- a/internal/infoschema.go
+++ b/internal/infoschema.go
@@ -17,33 +17,17 @@ package internal
 import (
 	"database/sql"
 	"fmt"
-	"os"
-	"strings"
-	"syscall"
 
 	_ "github.com/lib/pq"
-	"golang.org/x/crypto/ssh/terminal"
 
 	"github.com/cloudspannerecosystem/harbourbridge/schema"
 )
 
-// ProcessInfoSchema performs schema conversion for source database.
-// Schema information is obtained from the information_schema tables.
-// The driver used to access the database is specified by 'driver'.
-func ProcessInfoSchema(conv *Conv, driver string) error {
-	server := os.Getenv("PGHOST")
-	port := os.Getenv("PGPORT")
-	user := os.Getenv("PGUSER")
-	dbname := os.Getenv("PGDATABASE")
-	if server == "" || port == "" || user == "" || dbname == "" {
-		fmt.Printf("Please specify host, port, user and database using PGHOST, PGPORT, PGUSER and PGDATABASE environment variables\n")
-		return fmt.Errorf("Could not connect to source database")
-	}
-	password := getPassword()
-	db, err := sql.Open(driver, fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=disable", server, port, user, password, dbname))
-	if err != nil {
-		return err
-	}
+// ProcessInfoSchema performs schema conversion for source database
+// 'db'. We assume that the source database supports information
+// schema tables. These tables are a broadly supported ANSI standard,
+// and we use them to obtain source database's schema information.
+func ProcessInfoSchema(conv *Conv, db *sql.DB) error {
 	// TODO: All of the queries to get tables and table data
 	// should be in a single transaction to ensure we obtain a
 	// consistent snapshot of schema information across tables
@@ -55,37 +39,25 @@ func ProcessInfoSchema(conv *Conv, driver string) error {
 		return err
 	}
 	for _, t := range tables {
-		processTable(conv, db, t)
+		if err := processTable(conv, db, t); err != nil {
+			return err
+		}
 	}
 	schemaToDDL(conv)
 	conv.AddPrimaryKeys()
 	return nil
 }
 
-func getPassword() string {
-	password := os.Getenv("PGPASSWORD")
-	if password != "" {
-		return password
-	}
-	fmt.Print("Enter Password: ")
-	bytePassword, err := terminal.ReadPassword(int(syscall.Stdin))
-	if err != nil {
-		fmt.Println("\nCoudln't read password")
-		return ""
-	}
-	fmt.Printf("\n")
-	return strings.TrimSpace(string(bytePassword))
-}
-
 type schemaAndName struct {
-	schema string
+	schema string // PostgreSQL schema (aka namespace for PostgreSQL objects).
 	name   string
 }
 
 func getTables(db *sql.DB) ([]schemaAndName, error) {
-	ignoredSchemas := make(map[string]bool)
+	ignored := make(map[string]bool)
+	// Ignore all system tables: we just want to convert user tables.
 	for _, s := range []string{"information_schema", "postgres", "pg_catalog", "pg_temp_1", "pg_toast", "pg_toast_temp_1"} {
-		ignoredSchemas[s] = true
+		ignored[s] = true
 	}
 	q := "SELECT table_schema, table_name FROM information_schema.tables where table_type = 'BASE TABLE'"
 	rows, err := db.Query(q)
@@ -97,7 +69,7 @@ func getTables(db *sql.DB) ([]schemaAndName, error) {
 	var tables []schemaAndName
 	for rows.Next() {
 		rows.Scan(&tableSchema, &tableName)
-		if !ignoredSchemas[tableSchema] {
+		if !ignored[tableSchema] {
 			tables = append(tables, schemaAndName{schema: tableSchema, name: tableName})
 		}
 	}
diff --git a/main.go b/main.go
index 426639c..b8f107c 100644
--- a/main.go
+++ b/main.go
@@ -21,6 +21,7 @@ import (
 	"bufio"
 	"context"
 	"crypto/rand"
+	"database/sql"
 	"flag"
 	"fmt"
 	"io"
@@ -36,6 +37,8 @@ import (
 	sp "cloud.google.com/go/spanner"
 	database "cloud.google.com/go/spanner/admin/database/apiv1"
 	instance "cloud.google.com/go/spanner/admin/instance/apiv1"
+	_ "github.com/lib/pq"
+	"golang.org/x/crypto/ssh/terminal"
 	"google.golang.org/api/iterator"
 	adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
 	instancepb "google.golang.org/genproto/googleapis/spanner/admin/instance/v1"
@@ -142,24 +145,37 @@ func sourceToSpanner(driver, projectID, instanceID, dbName string, outputFilePre
 	fmt.Println(`###################################################################
 Accessing a source DB via an database/sql driver is an experimental
 feature that is not fully implemented. Currently we only do schema
-conversion and we only support postgres.
+conversion and we only support PostgreSQL.
 ###################################################################`)
 	if driver != "postgres" {
 		return fmt.Errorf("Driver %s not supported", driver)
 	}
+	server := os.Getenv("PGHOST")
+	port := os.Getenv("PGPORT")
+	user := os.Getenv("PGUSER")
+	dbname := os.Getenv("PGDATABASE")
+	if server == "" || port == "" || user == "" || dbname == "" {
+		fmt.Printf("Please specify host, port, user and database using PGHOST, PGPORT, PGUSER and PGDATABASE environment variables\n")
+		return fmt.Errorf("Could not connect to source database")
+	}
+	password := getPassword()
+	sourceDB, err := sql.Open(driver, fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=disable", server, port, user, password, dbname))
+	if err != nil {
+		return err
+	}
 	conv := internal.MakeConv()
-	err := internal.ProcessInfoSchema(conv, driver)
+	err = internal.ProcessInfoSchema(conv, sourceDB)
 	if err != nil {
 		return err
 	}
 	writeSchemaFile(conv, now, outputFilePrefix+schemaFile)
-	db, err := createDatabase(projectID, instanceID, dbName, conv)
+	spannerDB, err := createDatabase(projectID, instanceID, dbName, conv)
 	if err != nil {
 		fmt.Printf("\nCan't create database: %v\n", err)
 		return fmt.Errorf("can't create database")
 	}
 	badWrites := make(map[string]int64) // Empty bad writes since no data conversion yet.
-	banner := getBanner(now, db)
+	banner := getBanner(now, spannerDB)
 	report(badWrites, 0, banner, conv, outputFilePrefix+reportFile)
 	return nil
 }
@@ -495,6 +511,21 @@ func getDatabaseName(now time.Time) (string, error) {
 	return generateName(fmt.Sprintf("pg_dump_%s", now.Format("2006-01-02")))
 }
 
+func getPassword() string {
+	password := os.Getenv("PGPASSWORD")
+	if password != "" {
+		return password
+	}
+	fmt.Print("Enter Password: ")
+	bytePassword, err := terminal.ReadPassword(int(syscall.Stdin))
+	if err != nil {
+		fmt.Println("\nCoudln't read password")
+		return ""
+	}
+	fmt.Printf("\n")
+	return strings.TrimSpace(string(bytePassword))
+}
+
 // analyzeError inspects an error returned from Cloud Spanner and adds information
 // about potential root causes e.g. authentication issues.
 func analyzeError(err error, project, instance string) error {


Test patch:
diff --git a/internal/infoschema_test.go b/internal/infoschema_test.go
new file mode 100644
index 0000000..71359ca
--- /dev/null
+++ b/internal/infoschema_test.go
@@ -0,0 +1,156 @@
+// Copyright 2020 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
+//
+//      http://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 internal
+
+import (
+	"database/sql/driver"
+	"testing"
+
+	"github.com/DATA-DOG/go-sqlmock"
+	"github.com/cloudspannerecosystem/harbourbridge/spanner/ddl"
+	"github.com/stretchr/testify/assert"
+)
+
+func TestProcessInfoSchema(t *testing.T) {
+	type mockSpec struct {
+		query string
+		args  []driver.Value   // Query args.
+		cols  []string         // Columns names for returned rows.
+		rows  [][]driver.Value // Set of rows returned.
+	}
+	db, mock, err := sqlmock.New()
+	assert.Nil(t, err)
+
+	for _, m := range []mockSpec{
+		{
+			query: "SELECT table_schema, table_name FROM information_schema.tables where table_type = 'BASE TABLE'",
+			cols:  []string{"table_schema", "table_name"},
+			rows: [][]driver.Value{
+				{"public", "cart"},
+				{"public", "test"}},
+		}, {
+			query: "SELECT (.+) FROM information_schema.COLUMNS (.+)",
+			args:  []driver.Value{"public", "cart"},
+			cols:  []string{"column_name", "data_type", "data_type", "is_nullable", "column_default", "character_maximum_length", "numeric_precision", "numeric_scale"},
+			rows: [][]driver.Value{
+				{"productid", "text", nil, "NO", nil, nil, nil, nil},
+				{"userid", "text", nil, "NO", nil, nil, nil, nil},
+				{"quantity", "bigint", nil, "YES", nil, nil, 64, 0}},
+		}, {
+			query: "SELECT (.+) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS (.+)",
+			args:  []driver.Value{"public", "cart"},
+			cols:  []string{"column_name", "constraint_type"},
+			rows: [][]driver.Value{
+				{"productid", "PRIMARY KEY"},
+				{"userid", "PRIMARY KEY"}},
+		}, {
+			query: "SELECT (.+) FROM information_schema.COLUMNS (.+)",
+			args:  []driver.Value{"public", "test"},
+			cols:  []string{"column_name", "data_type", "data_type", "is_nullable", "column_default", "character_maximum_length", "numeric_precision", "numeric_scale"},
+			rows: [][]driver.Value{
+				{"id", "bigint", nil, "NO", nil, nil, 64, 0},
+				{"aint", "ARRAY", "integer", "YES", nil, nil, nil, nil},
+				{"atext", "ARRAY", "text", "YES", nil, nil, nil, nil},
+				{"b", "boolean", nil, "YES", nil, nil, nil, nil},
+				{"bs", "bigint", nil, "NO", "nextval('test11_bs_seq'::regclass)", nil, 64, 0},
+				{"by", "bytea", nil, "YES", nil, nil, nil, nil},
+				{"c", "character", nil, "YES", nil, 1, nil, nil},
+				{"c8", "character", nil, "YES", nil, 8, nil, nil},
+				{"d", "date", nil, "YES", nil, nil, nil, nil},
+				{"f8", "double precision", nil, "YES", nil, nil, 53, nil},
+				{"f4", "real", nil, "YES", nil, nil, 24, nil},
+				{"i8", "bigint", nil, "YES", nil, nil, 64, 0},
+				{"i4", "integer", nil, "YES", nil, nil, 32, 0},
+				{"i2", "smallint", nil, "YES", nil, nil, 16, 0},
+				{"num", "numeric", nil, "YES", nil, nil, nil, nil},
+				{"s", "integer", nil, "NO", "nextval('test11_s_seq'::regclass)", nil, 32, 0},
+				{"ts", "timestamp without time zone", nil, "YES", nil, nil, nil, nil},
+				{"tz", "timestamp with time zone", nil, "YES", nil, nil, nil, nil},
+				{"txt", "text", nil, "YES", nil, nil, nil, nil},
+				{"vc", "character varying", nil, "YES", nil, nil, nil, nil},
+				{"vc6", "character varying", nil, "YES", nil, 6, nil, nil}},
+		},
+		{
+			query: "SELECT (.+) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS (.+)",
+			args:  []driver.Value{"public", "test"},
+			cols:  []string{"column_name", "constraint_type"},
+			rows:  [][]driver.Value{{"id", "PRIMARY KEY"}},
+		},
+	} {
+		rows := sqlmock.NewRows(m.cols)
+		for _, r := range m.rows {
+			rows.AddRow(r...)
+		}
+		if len(m.args) > 0 {
+			mock.ExpectQuery(m.query).WithArgs(m.args...).WillReturnRows(rows)
+		} else {
+			mock.ExpectQuery(m.query).WillReturnRows(rows)
+		}
+	}
+	conv := MakeConv()
+	err = ProcessInfoSchema(conv, db)
+	assert.Nil(t, err)
+	expectedSchema := map[string]ddl.CreateTable{
+		"cart": ddl.CreateTable{
+			Name:     "cart",
+			ColNames: []string{"productid", "userid", "quantity"},
+			ColDefs: map[string]ddl.ColumnDef{
+				"productid": ddl.ColumnDef{Name: "productid", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
+				"userid":    ddl.ColumnDef{Name: "userid", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
+				"quantity":  ddl.ColumnDef{Name: "quantity", T: ddl.Int64{}},
+			},
+			Pks: []ddl.IndexKey{ddl.IndexKey{Col: "productid"}, ddl.IndexKey{Col: "userid"}}},
+		"test": ddl.CreateTable{
+			Name:     "test",
+			ColNames: []string{"id", "aint", "atext", "b", "bs", "by", "c", "c8", "d", "f8", "f4", "i8", "i4", "i2", "num", "s", "ts", "tz", "txt", "vc", "vc6"},
+			ColDefs: map[string]ddl.ColumnDef{
+				"id":    ddl.ColumnDef{Name: "id", T: ddl.Int64{}, NotNull: true},
+				"aint":  ddl.ColumnDef{Name: "aint", T: ddl.Int64{}, IsArray: true},
+				"atext": ddl.ColumnDef{Name: "atext", T: ddl.String{Len: ddl.MaxLength{}}, IsArray: true},
+				"b":     ddl.ColumnDef{Name: "b", T: ddl.Bool{}},
+				"bs":    ddl.ColumnDef{Name: "bs", T: ddl.Int64{}, NotNull: true},
+				"by":    ddl.ColumnDef{Name: "by", T: ddl.Bytes{Len: ddl.MaxLength{}}},
+				"c":     ddl.ColumnDef{Name: "c", T: ddl.String{Len: ddl.Int64Length{Value: 1}}},
+				"c8":    ddl.ColumnDef{Name: "c8", T: ddl.String{Len: ddl.Int64Length{Value: 8}}},
+				"d":     ddl.ColumnDef{Name: "d", T: ddl.Date{}},
+				"f8":    ddl.ColumnDef{Name: "f8", T: ddl.Float64{}},
+				"f4":    ddl.ColumnDef{Name: "f4", T: ddl.Float64{}},
+				"i8":    ddl.ColumnDef{Name: "i8", T: ddl.Int64{}},
+				"i4":    ddl.ColumnDef{Name: "i4", T: ddl.Int64{}},
+				"i2":    ddl.ColumnDef{Name: "i2", T: ddl.Int64{}},
+				"num":   ddl.ColumnDef{Name: "num", T: ddl.Float64{}},
+				"s":     ddl.ColumnDef{Name: "s", T: ddl.Int64{}, NotNull: true},
+				"ts":    ddl.ColumnDef{Name: "ts", T: ddl.Timestamp{}},
+				"tz":    ddl.ColumnDef{Name: "tz", T: ddl.Timestamp{}},
+				"txt":   ddl.ColumnDef{Name: "txt", T: ddl.String{Len: ddl.MaxLength{}}},
+				"vc":    ddl.ColumnDef{Name: "vc", T: ddl.String{Len: ddl.MaxLength{}}},
+				"vc6":   ddl.ColumnDef{Name: "vc6", T: ddl.String{Len: ddl.Int64Length{Value: 6}}},
+			},
+			Pks: []ddl.IndexKey{ddl.IndexKey{Col: "id"}}},
+	}
+	assert.Equal(t, expectedSchema, stripSchemaComments(conv.spSchema))
+	assert.Equal(t, len(conv.issues["cart"]), 0)
+	expectedIssues := map[string][]schemaIssue{
+		"aint": []schemaIssue{widened},
+		"bs":   []schemaIssue{defaultValue},
+		"f4":   []schemaIssue{widened},
+		"i4":   []schemaIssue{widened},
+		"i2":   []schemaIssue{widened},
+		"num":  []schemaIssue{numeric},
+		"s":    []schemaIssue{widened, defaultValue},
+		"ts":   []schemaIssue{timestamp},
+	}
+	assert.Equal(t, expectedIssues, conv.issues["test"])
+}