Repository: cloudspannerecosystem/harbourbridge
Pull request #360 (2022-08-08 17:39:38)

Added support for INCLUDE or STORING clause in indexes for sql server
HarbourBridge currently does not translate the INCLUDE clause to Spanner. Spanner’s STORING clause of the index can exhibit similar optimization behavior.


Patch:
diff --git a/schema/schema.go b/schema/schema.go
index 7a08cc9..511839e 100644
--- a/schema/schema.go
+++ b/schema/schema.go
@@ -89,10 +89,11 @@ type Key struct {
 // to handle lots of cases for the same concept. Our choice of an index representation for unique is largely
 // motivated by the fact that databases typically implement UNIQUE via an index.
 type Index struct {
-	Name   string
-	Unique bool
-	Keys   []Key
-	Id     string
+	Name          string
+	Unique        bool
+	Keys          []Key
+	Id            string
+	StoredColumns []string
 }
 
 // Type represents the type of a column.
diff --git a/sources/common/toddl.go b/sources/common/toddl.go
index 05b4f79..f6850f1 100644
--- a/sources/common/toddl.go
+++ b/sources/common/toddl.go
@@ -172,6 +172,7 @@ func cvtIndexes(conv *internal.Conv, spTableName string, srcTable string, srcInd
 	var spIndexes []ddl.CreateIndex
 	for _, srcIndex := range srcIndexes {
 		var spKeys []ddl.IndexKey
+		var spStoredColumns []string
 
 		for _, k := range srcIndex.Keys {
 			spCol, err := internal.GetSpannerCol(conv, srcTable, k.Column, true)
@@ -181,6 +182,14 @@ func cvtIndexes(conv *internal.Conv, spTableName string, srcTable string, srcInd
 			}
 			spKeys = append(spKeys, ddl.IndexKey{Col: spCol, Desc: k.Desc})
 		}
+		for _, k := range srcIndex.StoredColumns {
+			spCol, err := internal.GetSpannerCol(conv, srcTable, k, true)
+			if err != nil {
+				conv.Unexpected(fmt.Sprintf("Can't map index column name for table %s column %s", srcTable, k))
+				continue
+			}
+			spStoredColumns = append(spStoredColumns, spCol)
+		}
 		if srcIndex.Name == "" {
 			// Generate a name if index name is empty in MySQL.
 			// Collision of index name will be handled by ToSpannerIndexName.
@@ -188,10 +197,11 @@ func cvtIndexes(conv *internal.Conv, spTableName string, srcTable string, srcInd
 		}
 		spIndexName := internal.ToSpannerIndexName(conv, srcIndex.Name)
 		spIndex := ddl.CreateIndex{
-			Name:   spIndexName,
-			Table:  spTableName,
-			Unique: srcIndex.Unique,
-			Keys:   spKeys,
+			Name:          spIndexName,
+			Table:         spTableName,
+			Unique:        srcIndex.Unique,
+			Keys:          spKeys,
+			StoredColumns: spStoredColumns,
 		}
 		spIndexes = append(spIndexes, spIndex)
 		conv.Audit.ToSpannerFkIdx[srcTable].Index[srcIndex.Name] = spIndexName
diff --git a/sources/sqlserver/infoschema.go b/sources/sqlserver/infoschema.go
index 09cadba..5ba34bd 100644
--- a/sources/sqlserver/infoschema.go
+++ b/sources/sqlserver/infoschema.go
@@ -366,7 +366,8 @@ func (isi InfoSchemaImpl) GetIndexes(conv *internal.Conv, table common.SchemaAnd
 			IX.name, 
 			COL_NAME(IX.object_id, IXC.column_id) as [Column Name],
 			IX.is_unique,
-			IXC.is_descending_key 
+			IXC.is_descending_key,
+			IXC.is_included_column
 		FROM sys.indexes IX 
 		INNER JOIN sys.index_columns IXC 
 			ON  IX.object_id = IXC.object_id AND IX.index_id = IXC.index_id
@@ -385,12 +386,12 @@ func (isi InfoSchemaImpl) GetIndexes(conv *internal.Conv, table common.SchemaAnd
 		return nil, err
 	}
 	defer rows.Close()
-	var name, column, isUnique, collation string
+	var name, column, isUnique, collation, isStored string
 	indexMap := make(map[string]schema.Index)
 	var indexNames []string
 	var indexes []schema.Index
 	for rows.Next() {
-		if err := rows.Scan(&name, &column, &isUnique, &collation); err != nil {
+		if err := rows.Scan(&name, &column, &isUnique, &collation, &isStored); err != nil {
 			conv.Unexpected(fmt.Sprintf("Can't scan: %v", err))
 			continue
 		}
@@ -400,7 +401,11 @@ func (isi InfoSchemaImpl) GetIndexes(conv *internal.Conv, table common.SchemaAnd
 			indexMap[name] = schema.Index{Name: name, Unique: (isUnique == "true")}
 		}
 		index := indexMap[name]
-		index.Keys = append(index.Keys, schema.Key{Column: column, Desc: (collation == "DESC")})
+		if isStored == "false" {
+			index.Keys = append(index.Keys, schema.Key{Column: column, Desc: (collation == "DESC")})
+		} else {
+			index.StoredColumns = append(index.StoredColumns, column)
+		}
 		indexMap[name] = index
 	}
 	for _, k := range indexNames {
diff --git a/spanner/ddl/ast.go b/spanner/ddl/ast.go
index 254914c..2939f52 100644
--- a/spanner/ddl/ast.go
+++ b/spanner/ddl/ast.go
@@ -296,13 +296,14 @@ func (ct CreateTable) PrintCreateTable(config Config) string {
 // CreateIndex encodes the following DDL definition:
 //     create index: CREATE [UNIQUE] [NULL_FILTERED] INDEX index_name ON table_name ( key_part [, ...] ) [ storing_clause ] [ , interleave_clause ]
 type CreateIndex struct {
-	Name   string
-	Table  string
-	Unique bool
-	Keys   []IndexKey
-	Id     string
+	Name          string
+	Table         string
+	Unique        bool
+	Keys          []IndexKey
+	Id            string
+	StoredColumns []string
 	// We have no requirements for null-filtered option and
-	// storing/interleaving clauses yet, so we omit them for now.
+	// interleaving clauses yet, so we omit them for now.
 }
 
 // PrintCreateIndex unparses a CREATE INDEX statement.
@@ -311,11 +312,19 @@ func (ci CreateIndex) PrintCreateIndex(c Config) string {
 	for _, p := range ci.Keys {
 		keys = append(keys, p.PrintIndexKey(c))
 	}
-	var unique string
+	var unique, stored, storingClause string
 	if ci.Unique {
 		unique = "UNIQUE "
 	}
-	return fmt.Sprintf("CREATE %sINDEX %s ON %s (%s)", unique, c.quote(ci.Name), c.quote(ci.Table), strings.Join(keys, ", "))
+	if c.TargetDb == constants.TargetExperimentalPostgres {
+		stored = "INCLUDE"
+	} else {
+		stored = "STORING"
+	}
+	if ci.StoredColumns != nil {
+		storingClause = fmt.Sprintf(" %s (%s)", stored, strings.Join(ci.StoredColumns, ", "))
+	}
+	return fmt.Sprintf("CREATE %sINDEX %s ON %s (%s)%s", unique, c.quote(ci.Name), c.quote(ci.Table), strings.Join(keys, ", "), storingClause)
 }
 
 // PrintForeignKeyAlterTable unparses the foreign keys using ALTER TABLE.


Test patch:
diff --git a/sources/sqlserver/infoschema_test.go b/sources/sqlserver/infoschema_test.go
index fb2bc74..b85dac5 100644
--- a/sources/sqlserver/infoschema_test.go
+++ b/sources/sqlserver/infoschema_test.go
@@ -68,7 +68,7 @@ func TestProcessSchema(t *testing.T) {
 		}, {
 			query: "SELECT (.+) FROM sys.indexes (.+)",
 			args:  []driver.Value{"user", "dbo"},
-			cols:  []string{"index_name", "column_name", "column_position", "is_unique", "order"},
+			cols:  []string{"index_name", "column_name", "column_position", "is_unique", "order", "is_included_column"},
 		}, {
 			query: "SELECT (.+) FROM information_schema.COLUMNS (.+)",
 			args:  []driver.Value{"dbo", "user"},
@@ -92,7 +92,7 @@ func TestProcessSchema(t *testing.T) {
 		}, {
 			query: "SELECT (.+) FROM sys.indexes (.+)",
 			args:  []driver.Value{"test", "dbo"},
-			cols:  []string{"index_name", "column_name", "column_position", "is_unique", "order"},
+			cols:  []string{"index_name", "column_name", "column_position", "is_unique", "order", "is_included_column"},
 		}, {
 			query: "SELECT (.+) FROM information_schema.COLUMNS (.+)",
 			args:  []driver.Value{"dbo", "test"},
@@ -156,12 +156,12 @@ func TestProcessSchema(t *testing.T) {
 		}, {
 			query: "SELECT (.+) FROM sys.indexes (.+)",
 			args:  []driver.Value{"cart", "dbo"},
-			cols:  []string{"index_name", "column_name", "is_unique", "order"},
-			rows: [][]driver.Value{{"index1", "userid", "false", "ASC"},
-				{"index2", "userid", "true", "ASC"},
-				{"index2", "productid", "true", "DESC"},
-				{"index3", "productid", "true", "DESC"},
-				{"index3", "userid", "true", "ASC"},
+			cols:  []string{"index_name", "column_name", "is_unique", "order", "is_included_column"},
+			rows: [][]driver.Value{{"index1", "userid", "false", "ASC", "false"},
+				{"index2", "userid", "true", "ASC", "false"},
+				{"index2", "productid", "true", "DESC", "true"},
+				{"index3", "productid", "true", "DESC", "false"},
+				{"index3", "userid", "true", "ASC", "false"},
 			},
 		}, {
 			query: "SELECT (.+) FROM information_schema.COLUMNS (.+)",
@@ -187,7 +187,7 @@ func TestProcessSchema(t *testing.T) {
 		}, {
 			query: "SELECT (.+) FROM sys.indexes (.+)",
 			args:  []driver.Value{"product", "production"},
-			cols:  []string{"index_name", "column_name", "column_position", "is_unique", "order"},
+			cols:  []string{"index_name", "column_name", "column_position", "is_unique", "order", "is_included_column"},
 		}, {
 			query: "SELECT (.+) FROM information_schema.COLUMNS (.+)",
 			args:  []driver.Value{"production", "product"},
@@ -213,7 +213,7 @@ func TestProcessSchema(t *testing.T) {
 		}, {
 			query: "SELECT (.+) FROM sys.indexes (.+)",
 			args:  []driver.Value{"test_ref", "dbo"},
-			cols:  []string{"index_name", "column_name", "column_position", "is_unique", "order"},
+			cols:  []string{"index_name", "column_name", "column_position", "is_unique", "order", "is_included_column"},
 		}, {
 			query: "SELECT (.+) FROM information_schema.COLUMNS (.+)",
 			args:  []driver.Value{"dbo", "test_ref"},
@@ -301,7 +301,7 @@ func TestProcessSchema(t *testing.T) {
 			Fks: []ddl.Foreignkey{{Name: "fk_test2", Columns: []string{"productid"}, ReferTable: "production_product", ReferColumns: []string{"product_id"}},
 				{Name: "fk_test3", Columns: []string{"userid"}, ReferTable: "user", ReferColumns: []string{"user_id"}}},
 			Indexes: []ddl.CreateIndex{{Name: "index1", Table: "cart", Unique: false, Keys: []ddl.IndexKey{{Col: "userid", Desc: false}}},
-				{Name: "index2", Table: "cart", Unique: true, Keys: []ddl.IndexKey{{Col: "userid", Desc: false}, {Col: "productid", Desc: true}}},
+				{Name: "index2", Table: "cart", Unique: true, Keys: []ddl.IndexKey{{Col: "userid", Desc: false}}, StoredColumns: []string{"productid"}},
 				{Name: "index3", Table: "cart", Unique: true, Keys: []ddl.IndexKey{{Col: "productid", Desc: true}, {Col: "userid", Desc: false}}}}},
 		"production_product": {
 			Name:     "production_product",
diff --git a/spanner/ddl/ast_test.go b/spanner/ddl/ast_test.go
index fec721c..0a46e14 100644
--- a/spanner/ddl/ast_test.go
+++ b/spanner/ddl/ast_test.go
@@ -263,6 +263,7 @@ func TestPrintCreateIndex(t *testing.T) {
 			/*Unique =*/ false,
 			[]IndexKey{{Col: "col1", Desc: true}, {Col: "col2"}},
 			"1",
+			nil,
 		},
 		{
 			"myindex2",
@@ -270,6 +271,7 @@ func TestPrintCreateIndex(t *testing.T) {
 			/*Unique =*/ true,
 			[]IndexKey{{Col: "col1", Desc: true}, {Col: "col2"}},
 			"1",
+			nil,
 		}}
 	tests := []struct {
 		name       string