Repository: cloudspannerecosystem/harbourbridge
Pull request #10 (2020-01-24 08:02:26)

Add tablemap.
Tablemap maintains the mappings between PostgreSQL tables and columns
and Spanner tables and columns. Note that PostgreSQL table and column
names can be essentially any string, but Spanner table and column
names must use a limited character set. So when we convert PostgreSQL
to Spanner, we potentially have to remap PostgreSQL table and column
names to make them legal Spanner.


Patch:
diff --git a/internal/postgres/tablemap.go b/internal/postgres/tablemap.go
new file mode 100644
index 0000000..5bb3ad8
--- /dev/null
+++ b/internal/postgres/tablemap.go
@@ -0,0 +1,117 @@
+// 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 postgres
+
+import (
+	"fmt"
+	"strconv"
+
+	"harbourbridge/internal"
+)
+
+// GetSpannerTable maps a PostgreSQL table name into a legal Spanner table
+// name. Note that PostgreSQL column names can be essentially any string, but
+// Spanner column names must use a limited character set. This means that
+// getSpannerTable may have to change a name to make it legal, we must ensure
+// that:
+// a) the new table name is legal
+// b) the new table name doesn't clash with other Spanner table names
+// c) we consistently return the same name for this table.
+func GetSpannerTable(conv *Conv, pgTable string) (string, error) {
+	if pgTable == "" {
+		return "", fmt.Errorf("bad parameter: table string is empty")
+	}
+	if sp, found := conv.toSpanner[pgTable]; found {
+		return sp.name, nil
+	}
+	spTable, _ := internal.FixName(pgTable)
+	if _, found := conv.toPostgres[spTable]; found {
+		// s has been used before i.e. FixName caused a collision.
+		// Add unique postfix: use number of tables so far.
+		// However, there is a chance this has already been used,
+		// so need to iterate.
+		id := len(conv.toSpanner)
+		for {
+			t := spTable + "_" + strconv.Itoa(id)
+			if _, found := conv.toPostgres[t]; !found {
+				spTable = t
+				break
+			}
+			id++
+		}
+	}
+	if spTable != pgTable {
+		internal.VerbosePrintf("Mapping PostgreSQL table %s to Spanner table %s\n", pgTable, spTable)
+	}
+	conv.toSpanner[pgTable] = nameAndCols{name: spTable, cols: make(map[string]string)}
+	conv.toPostgres[spTable] = nameAndCols{name: pgTable, cols: make(map[string]string)}
+	return spTable, nil
+}
+
+// GetSpannerCol maps a PostgreSQL table/column into a legal Spanner column
+// name. If mustExist is true, we return error if the column is new.
+// Note that PostgreSQL column names can be essentially any string, but
+// Spanner column names must use a limited character set. This means that
+// getSpannerCol may have to change a name to make it legal, we must ensure
+// that:
+// a) the new col name is legal
+// b) the new col name doesn't clash with other col names in the same table
+// c) we consistently return the same name for the same col.
+func GetSpannerCol(conv *Conv, pgTable, pgCol string, mustExist bool) (string, error) {
+	if pgTable == "" {
+		return "", fmt.Errorf("bad parameter: table string is empty")
+	}
+	if pgCol == "" {
+		return "", fmt.Errorf("bad parameter: col string is empty")
+	}
+	sp, found := conv.toSpanner[pgTable]
+	if !found {
+		return "", fmt.Errorf("unknown table %s", pgTable)
+	}
+	// Sanity check: do reverse mapping and check consistency.
+	// Consider dropping this check.
+	pg, found := conv.toPostgres[sp.name]
+	if !found || pg.name != pgTable {
+		return "", fmt.Errorf("internal error: table mapping inconsistency for table %s (%s)", pgTable, pg.name)
+	}
+	if spCol, found := sp.cols[pgCol]; found {
+		return spCol, nil
+	}
+	if mustExist {
+		return "", fmt.Errorf("table %s does not have a column %s", pgTable, pgCol)
+	}
+	spCol, _ := internal.FixName(pgCol)
+	if _, found := conv.toPostgres[sp.name].cols[spCol]; found {
+		// spCol has been used before i.e. FixName caused a collision.
+		// Add unique postfix: use number of cols in this table so far.
+		// However, there is a chance this has already been used,
+		// so need to iterate.
+		id := len(sp.cols)
+		for {
+			c := spCol + "_" + strconv.Itoa(id)
+			if _, found := conv.toPostgres[sp.name].cols[c]; !found {
+				spCol = c
+				break
+			}
+			id++
+		}
+	}
+	if spCol != pgCol {
+		internal.VerbosePrintf("Mapping PostgreSQL col %s (table %s) to Spanner col %s\n", pgCol, pgTable, spCol)
+	}
+	conv.toSpanner[pgTable].cols[pgCol] = spCol
+	conv.toPostgres[sp.name].cols[spCol] = pgCol
+	return spCol, nil
+}


Test patch:
diff --git a/internal/postgres/tablemap_test.go b/internal/postgres/tablemap_test.go
new file mode 100644
index 0000000..e057bb5
--- /dev/null
+++ b/internal/postgres/tablemap_test.go
@@ -0,0 +1,93 @@
+// 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 postgres
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/assert"
+)
+
+func TestGetSpannerTable(t *testing.T) {
+	conv := MakeConv()
+	basicTests := []struct {
+		name    string // Name of test.
+		pgTable string // PostgreSQL table name to test.
+		error   bool   // Whether an error is expected.
+		spTable string // Expected Spanner table name.
+	}{
+		{"Empty", "", true, ""},
+		{"Good name", "table", false, "table"},
+		{"Good name: setup collision", "tab_le_5", false, "tab_le_5"},
+		{"Good name: setup collision (2)", "tab_le_6", false, "tab_le_6"},
+		{"Illegal character", "tab\nle", false, "tab_le"},
+		{"Illegal character with collision (1)", "tab\tle", false, "tab_le_4"},
+		{"Illegal character with collision (2)", "tab?le", false, "tab_le_7"}, // Must skip tab_le_5 and tab_le_6.
+		{"Collision with previous remapping", "tab_le_4", false, "tab_le_4_6"},
+		{"Illegal start character", "2table", false, "Atable"},
+		{"Illegal start character with collision (1)", "_table", false, "Atable_8"},
+		{"Illegal start character with collision (2)", "\ntable", false, "Atable_9"},
+	}
+	for _, tc := range basicTests {
+		spTable, err := GetSpannerTable(conv, tc.pgTable)
+		if tc.error {
+			assert.NotNil(t, err, tc.name)
+			continue
+		}
+		assert.Equal(t, tc.spTable, spTable, tc.name)
+		// Run again to check we get same result.
+		s2, err := GetSpannerTable(conv, tc.pgTable)
+		assert.Nil(t, err, tc.name)
+		assert.Equal(t, spTable, s2, tc.name)
+	}
+}
+
+func TestGetSpannerCol(t *testing.T) {
+	conv := MakeConv()
+	basicTests := []struct {
+		name    string // Name of test.
+		pgTable string // PostgreSQL table name to test.
+		pgCol   string // PostgreSQL col name to test.
+		error   bool   // Whether an error is expected.
+		spCol   string // Expected Spanner column name.
+	}{
+		{"Empty table", "", "col", true, ""},
+		{"Empty col", "table", "", true, ""},
+		{"Good name", "table", "col", false, "col"},
+		{"Bad table", "ta.b\nle", "col", false, "col"},
+		{"Bad col", "table", "c\nol", false, "c_ol"},
+		{"Bad table and col", "t.able", "c\no\nl", false, "c_o_l"},
+		{"table1 good name 1", "table1", "col", false, "col"},
+		{"table1 good name 2", "table1", "c_ol", false, "c_ol"},
+		{"table1 good name 3", "table1", "c_ol_5", false, "c_ol_5"},
+		{"table1 good name 4", "table1", "c_ol_6", false, "c_ol_6"},
+		{"table1 collision 1", "table1", "c\tol", false, "c_ol_4"},
+		{"table1 collision 2", "table1", "c\nol", false, "c_ol_7"}, // Skip c_ol_5 and c_ol_6.
+		{"table1 collision 3", "table1", "c?ol", false, "c_ol_8"},
+	}
+	for _, tc := range basicTests {
+		_, err1 := GetSpannerTable(conv, tc.pgTable) // Ensure table is known.
+		spCol, err2 := GetSpannerCol(conv, tc.pgTable, tc.pgCol, false)
+		if tc.error {
+			assert.True(t, err1 != nil || err2 != nil, tc.name)
+			continue
+		}
+		assert.Equal(t, tc.spCol, spCol, tc.name)
+		// Run again to check we get same result.
+		spCol2, err := GetSpannerCol(conv, tc.pgTable, tc.pgCol, false)
+		assert.Nil(t, err, tc.name)
+		assert.Equal(t, spCol, spCol2, tc.name)
+	}
+}