fix(mssql)!: Fix JSON transformer & column types (#7010)

candiduslynx · web-flow · commit 748c7df498df · 2023-01-23T13:25:12.000Z
Fixes #7001 BEGIN_COMMIT_OVERRIDE fix(mssql): Change column types from `varchar` to `nvarchar` to properly store Unicode characters BREAKING-CHANGE: Change column types from `varchar` to `nvarchar` to properly store Unicode characters fix(mssql): Fix JSON transformer to store unescaped data END_COMMIT_OVERRIDE
diff --git a/plugins/destination/mssql/client/queries/create_table_test.go b/plugins/destination/mssql/client/queries/create_table_test.go
@@ -13,7 +13,7 @@ func TestCreateTable(t *testing.T) {
 		expected   = `CREATE TABLE [cq].[table_name] (
   [_cq_id] uniqueidentifier UNIQUE NOT NULL,
   [_cq_parent_id] uniqueidentifier,
-  [_cq_source_name] varchar(8000),
+  [_cq_source_name] nvarchar(4000),
   [_cq_sync_time] datetimeoffset,
   [extra_col] float NOT NULL
   CONSTRAINT [table_name_cqpk] PRIMARY KEY (
diff --git a/plugins/destination/mssql/client/queries/tvp_test.go b/plugins/destination/mssql/client/queries/tvp_test.go
@@ -68,7 +68,7 @@ func TestTVPAddType(t *testing.T) {
 		expected   = `CREATE TYPE [cq].[cq_tbl_table_name] AS TABLE (
   [_cq_id] uniqueidentifier UNIQUE NOT NULL,
   [_cq_parent_id] uniqueidentifier,
-  [_cq_source_name] varchar(8000),
+  [_cq_source_name] nvarchar(4000),
   [_cq_sync_time] datetimeoffset,
   [extra_col_pk1] float NOT NULL,
   [extra_col_pk2] bit NOT NULL,
diff --git a/plugins/destination/mssql/client/queries/types.go b/plugins/destination/mssql/client/queries/types.go
@@ -25,15 +25,15 @@ func SQLType(t schema.ValueType) string {
 		schema.TypeCIDR,
 		schema.TypeMacAddr,
 		schema.TypeInet:
-		return "varchar(8000)" // feasible to see these as PK, so need to limit the value
+		return "nvarchar(4000)" // feasible to see these as PK, so need to limit the value
 	case schema.TypeStringArray,
 		schema.TypeJSON,
 		schema.TypeUUIDArray,
 		schema.TypeCIDRArray,
 		schema.TypeMacAddrArray,
 		schema.TypeInetArray,
 		schema.TypeIntArray:
-		return "varchar(max)"
+		return "nvarchar(max)"
 	default:
 		panic("unknown type " + t.String())
 	}
diff --git a/plugins/destination/mssql/client/transform.go b/plugins/destination/mssql/client/transform.go
@@ -47,7 +47,8 @@ func (*Client) TransformJSON(v *schema.JSON) any {
 	if v.Status != schema.Present {
 		return nil
 	}
-	return string(v.Bytes)
+
+	return unescape(string(v.Bytes))
 }
 
 func (*Client) TransformUUID(v *schema.UUID) any {
@@ -109,7 +110,7 @@ func (*Client) TransformText(v *schema.Text) any {
 	if v.Status != schema.Present {
 		return nil
 	}
-	return v.String()
+	return v.Str
 }
 
 func (*Client) TransformCIDRArray(v *schema.CIDRArray) any {
diff --git a/plugins/destination/mssql/client/unescape_test.go b/plugins/destination/mssql/client/unescape_test.go
@@ -0,0 +1,25 @@
+package client
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/require"
+)
+
+func TestUnescape(t *testing.T) {
+	type testCase struct {
+		have, want string
+	}
+	for _, tc := range []testCase{
+		{
+			have: `{"BillTo":"P\u0026T"}`,
+			want: `{"BillTo":"P&T"}`,
+		},
+		{
+			have: `{"BillTo":"P&T"}`,
+			want: `{"BillTo":"P&T"}`,
+		},
+	} {
+		require.Equal(t, tc.want, unescape(tc.have))
+	}
+}
diff --git a/plugins/destination/mssql/client/unicode.go b/plugins/destination/mssql/client/unicode.go
@@ -0,0 +1,25 @@
+package client
+
+import (
+	"strconv"
+)
+
+func unescape(str string) string {
+	out := ""
+	for len(str) > 0 {
+		if str[0] == '\\' {
+			if len(str) > 5 && str[1] == 'u' {
+				u, err := strconv.ParseUint(str[2:6], 16, 64)
+				if err == nil {
+					out += string(byte(u))
+					str = str[6:]
+					continue
+				}
+			}
+		}
+		out += str[:1]
+		str = str[1:]
+	}
+
+	return out
+}

Original file line number	Diff line number	Diff line change
`@@ -47,7 +47,8 @@ func (Client) TransformJSON(v schema.JSON) any {`
`47`	`47`	`if v.Status != schema.Present {`
`48`	`48`	`return nil`
`49`	`49`	`}`
`50`		`- return string(v.Bytes)`
	`50`	`+`
	`51`	`+ return unescape(string(v.Bytes))`
`51`	`52`	`}`
`52`	`53`
`53`	`54`	`func (Client) TransformUUID(v schema.UUID) any {`
`@@ -109,7 +110,7 @@ func (Client) TransformText(v schema.Text) any {`
`109`	`110`	`if v.Status != schema.Present {`
`110`	`111`	`return nil`
`111`	`112`	`}`
`112`		`- return v.String()`
	`113`	`+ return v.Str`
`113`	`114`	`}`
`114`	`115`
`115`	`116`	`func (Client) TransformCIDRArray(v schema.CIDRArray) any {`