Repository: snowflakedb/gosnowflake
Pull request #1135 (2024-05-20 12:47:28)

SNOW-1437452 Support semistructured types bindings
### Description

SNOW-1437452 Implemented support for binding semistructured types

### Checklist
- [x] Created tests which fail without the change (if possible)
- [x] Extended the README / documentation, if necessary


codecov[bot]: ## [Codecov](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1135?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb) Report
All modified and coverable lines are covered by tests :white_check_mark:
> Project coverage is 75.43%. Comparing base [(`7f760af`)](https://app.codecov.io/gh/snowflakedb/gosnowflake/commit/7f760afab64d3271ff45c5d6afa3e209344750e3?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb) to head [(`7291dea`)](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1135?dropdown=coverage&src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb).


<details><summary>Additional details and impacted files</summary>


```diff
@@            Coverage Diff             @@
##           master    #1135      +/-   ##
==========================================
+ Coverage   75.42%   75.43%   +0.01%     
==========================================
  Files          53       53              
  Lines       11266    11272       +6     
==========================================
+ Hits         8497     8503       +6     
  Misses       2769     2769              
```



</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1135?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb).


Patch:
diff --git a/datatype.go b/datatype.go
index 1245054..f9e55dc 100644
--- a/datatype.go
+++ b/datatype.go
@@ -124,6 +124,12 @@ func dataTypeMode(v driver.Value) (tsmode snowflakeType, err error) {
 			tsmode = timestampTzType
 		case bytes.Equal(bd, DataTypeBinary):
 			tsmode = binaryType
+		case bytes.Equal(bd, DataTypeObject):
+			tsmode = objectType
+		case bytes.Equal(bd, DataTypeArray):
+			tsmode = arrayType
+		case bytes.Equal(bd, DataTypeVariant):
+			tsmode = variantType
 		default:
 			return nullType, fmt.Errorf(errMsgInvalidByteArray, v)
 		}
diff --git a/doc.go b/doc.go
index 25f0eda..d2c26e0 100644
--- a/doc.go
+++ b/doc.go
@@ -431,6 +431,22 @@ Example table definition:
 
 The data not have any corresponding schema, so values in table may be slightly different.
 
+### Semistructured types
+
+Semistuctured variants, objects and arrays are always represented as strings for scanning:
+
+		rows, err := db.Query("SELECT {'a': 'b'}::OBJECT")
+		// handle error
+		defer rows.Close()
+		rows.Next()
+		var v string
+		err := rows.Scan(&v)
+
+When inserting, a marker indicating correct type must be used, for example:
+
+	db.Exec("CREATE TABLE test_object_binding (obj OBJECT)")
+	db.Exec("INSERT INTO test_object_binding SELECT (?)", DataTypeObject, "{'s': 'some string'}")
+
 ### Structured types
 
 Structured types differentiate from semistructured types by having specific schema.
@@ -484,6 +500,8 @@ If you want to scan array of structs, you have to use a helper function ScanArra
 	var res map[string]*simpleObject
 	err := rows.Scan(ScanMapOfScanners(&res))
 
+## Using higher precision numbers
+
 The following example shows how to retrieve very large values using the math/big
 package. This example retrieves a large INTEGER value to an interface and then
 extracts a big.Int value from that interface. If the value fits into an int64,


Test patch:
diff --git a/assert_test.go b/assert_test.go
index 40dc449..df5cead 100644
--- a/assert_test.go
+++ b/assert_test.go
@@ -6,6 +6,7 @@ import (
 	"bytes"
 	"fmt"
 	"reflect"
+	"regexp"
 	"runtime"
 	"strings"
 	"testing"
@@ -35,6 +36,10 @@ func assertEqualF(t *testing.T, actual any, expected any, descriptions ...string
 	fatalOnNonEmpty(t, validateEqual(actual, expected, descriptions...))
 }
 
+func assertEqualIgnoringWhitespaceE(t *testing.T, actual string, expected string, descriptions ...string) {
+	errorOnNonEmpty(t, validateEqualIgnoringWhitespace(actual, expected, descriptions...))
+}
+
 func assertDeepEqualE(t *testing.T, actual any, expected any, descriptions ...string) {
 	errorOnNonEmpty(t, validateDeepEqual(actual, expected, descriptions...))
 }
@@ -123,6 +128,22 @@ func validateEqual(actual any, expected any, descriptions ...string) string {
 	return fmt.Sprintf("expected \"%s\" to be equal to \"%s\" but was not. %s", actual, expected, desc)
 }
 
+func removeWhitespaces(s string) string {
+	pattern, err := regexp.Compile(`\s+`)
+	if err != nil {
+		panic(err)
+	}
+	return pattern.ReplaceAllString(s, "")
+}
+
+func validateEqualIgnoringWhitespace(actual string, expected string, descriptions ...string) string {
+	if removeWhitespaces(expected) == removeWhitespaces(actual) {
+		return ""
+	}
+	desc := joinDescriptions(descriptions...)
+	return fmt.Sprintf("expected \"%s\" to be equal to \"%s\" but was not. %s", actual, expected, desc)
+}
+
 func validateDeepEqual(actual any, expected any, descriptions ...string) string {
 	if reflect.DeepEqual(actual, expected) {
 		return ""
diff --git a/datatype_test.go b/datatype_test.go
index 6b61be7..0f8ca42 100644
--- a/datatype_test.go
+++ b/datatype_test.go
@@ -8,20 +8,21 @@ import (
 	"testing"
 )
 
-type tcDataTypeMode struct {
-	tp    driver.Value
-	tmode snowflakeType
-	err   error
-}
-
 func TestDataTypeMode(t *testing.T) {
-	var testcases = []tcDataTypeMode{
+	var testcases = []struct {
+		tp    driver.Value
+		tmode snowflakeType
+		err   error
+	}{
 		{tp: DataTypeTimestampLtz, tmode: timestampLtzType, err: nil},
 		{tp: DataTypeTimestampNtz, tmode: timestampNtzType, err: nil},
 		{tp: DataTypeTimestampTz, tmode: timestampTzType, err: nil},
 		{tp: DataTypeDate, tmode: dateType, err: nil},
 		{tp: DataTypeTime, tmode: timeType, err: nil},
 		{tp: DataTypeBinary, tmode: binaryType, err: nil},
+		{tp: DataTypeObject, tmode: objectType, err: nil},
+		{tp: DataTypeArray, tmode: arrayType, err: nil},
+		{tp: DataTypeVariant, tmode: variantType, err: nil},
 		{tp: DataTypeFixed, tmode: fixedType,
 			err: fmt.Errorf(errMsgInvalidByteArray, DataTypeFixed)},
 		{tp: DataTypeReal, tmode: realType,
diff --git a/structured_type_test.go b/structured_type_read_test.go
similarity index 100%
rename from structured_type_test.go
rename to structured_type_read_test.go
diff --git a/structured_type_write_test.go b/structured_type_write_test.go
new file mode 100644
index 0000000..0514684
--- /dev/null
+++ b/structured_type_write_test.go
@@ -0,0 +1,129 @@
+package gosnowflake
+
+import (
+	"database/sql"
+	"testing"
+)
+
+func TestBindingVariant(t *testing.T) {
+	runDBTest(t, func(dbt *DBTest) {
+		skipStructuredTypesTestsOnGHActions(t)
+		dbt.mustExec("CREATE TABLE test_variant_binding (var VARIANT)")
+		defer func() {
+			dbt.mustExec("DROP TABLE IF EXISTS test_variant_binding")
+		}()
+		dbt.mustExec("ALTER SESSION SET ENABLE_OBJECT_TYPED_BINDS = true")
+		dbt.mustExec("ALTER SESSION SET ENABLE_STRUCTURED_TYPES_IN_BINDS = Enable")
+		dbt.mustExec("INSERT INTO test_variant_binding SELECT (?)", DataTypeVariant, nil)
+		dbt.mustExec("INSERT INTO test_variant_binding SELECT (?)", DataTypeVariant, sql.NullString{Valid: false})
+		dbt.mustExec("INSERT INTO test_variant_binding SELECT (?)", DataTypeVariant, "{'s': 'some string'}")
+		dbt.mustExec("INSERT INTO test_variant_binding SELECT (?)", DataTypeVariant, sql.NullString{Valid: true, String: "{'s': 'some string2'}"})
+		rows := dbt.mustQuery("SELECT * FROM test_variant_binding")
+		defer rows.Close()
+		var res sql.NullString
+
+		assertTrueF(t, rows.Next())
+		err := rows.Scan(&res)
+		assertNilF(t, err)
+		assertFalseF(t, res.Valid)
+
+		assertTrueF(t, rows.Next())
+		err = rows.Scan(&res)
+		assertNilF(t, err)
+		assertFalseF(t, res.Valid)
+
+		assertTrueF(t, rows.Next())
+		err = rows.Scan(&res)
+		assertNilF(t, err)
+		assertTrueE(t, res.Valid)
+		assertEqualIgnoringWhitespaceE(t, res.String, `{"s": "some string"}`)
+
+		assertTrueF(t, rows.Next())
+		err = rows.Scan(&res)
+		assertNilF(t, err)
+		assertTrueE(t, res.Valid)
+		assertEqualIgnoringWhitespaceE(t, res.String, `{"s": "some string2"}`)
+	})
+}
+
+func TestBindingObjectWithoutSchema(t *testing.T) {
+	skipStructuredTypesTestsOnGHActions(t)
+	runDBTest(t, func(dbt *DBTest) {
+		dbt.mustExec("CREATE TABLE test_object_binding (obj OBJECT)")
+		defer func() {
+			dbt.mustExec("DROP TABLE IF EXISTS test_object_binding")
+		}()
+		dbt.mustExec("ALTER SESSION SET ENABLE_OBJECT_TYPED_BINDS = true")
+		dbt.mustExec("ALTER SESSION SET ENABLE_STRUCTURED_TYPES_IN_BINDS = Enable")
+		dbt.mustExec("INSERT INTO test_object_binding SELECT (?)", DataTypeObject, nil)
+		dbt.mustExec("INSERT INTO test_object_binding SELECT (?)", DataTypeObject, sql.NullString{Valid: false})
+		dbt.mustExec("INSERT INTO test_object_binding SELECT (?)", DataTypeObject, "{'s': 'some string'}")
+		dbt.mustExec("INSERT INTO test_object_binding SELECT (?)", DataTypeObject, sql.NullString{Valid: true, String: "{'s': 'some string2'}"})
+		rows := dbt.mustQuery("SELECT * FROM test_object_binding")
+		defer rows.Close()
+		var res sql.NullString
+
+		assertTrueF(t, rows.Next())
+		err := rows.Scan(&res)
+		assertNilF(t, err)
+		assertFalseF(t, res.Valid)
+
+		assertTrueF(t, rows.Next())
+		err = rows.Scan(&res)
+		assertNilF(t, err)
+		assertFalseF(t, res.Valid)
+
+		assertTrueF(t, rows.Next())
+		err = rows.Scan(&res)
+		assertNilF(t, err)
+		assertTrueE(t, res.Valid)
+		assertEqualIgnoringWhitespaceE(t, res.String, `{"s": "some string"}`)
+
+		assertTrueF(t, rows.Next())
+		err = rows.Scan(&res)
+		assertNilF(t, err)
+		assertTrueE(t, res.Valid)
+		assertEqualIgnoringWhitespaceE(t, res.String, `{"s": "some string2"}`)
+	})
+}
+
+func TestBindingArrayWithoutSchema(t *testing.T) {
+	skipStructuredTypesTestsOnGHActions(t)
+	runDBTest(t, func(dbt *DBTest) {
+		dbt.mustExec("CREATE TABLE test_array_binding (arr ARRAY)")
+		defer func() {
+			dbt.mustExec("DROP TABLE IF EXISTS test_array_binding")
+		}()
+		dbt.mustExec("ALTER SESSION SET ENABLE_OBJECT_TYPED_BINDS = true")
+		dbt.mustExec("ALTER SESSION SET ENABLE_STRUCTURED_TYPES_IN_BINDS = Enable")
+		dbt.mustExec("INSERT INTO test_array_binding SELECT (?)", DataTypeArray, nil)
+		dbt.mustExec("INSERT INTO test_array_binding SELECT (?)", DataTypeArray, sql.NullString{Valid: false})
+		dbt.mustExec("INSERT INTO test_array_binding SELECT (?)", DataTypeArray, "[1, 2, 3]")
+		dbt.mustExec("INSERT INTO test_array_binding SELECT (?)", DataTypeArray, sql.NullString{Valid: true, String: "[1, 2, 3]"})
+		rows := dbt.mustQuery("SELECT * FROM test_array_binding")
+		defer rows.Close()
+		var res sql.NullString
+
+		assertTrueF(t, rows.Next())
+		err := rows.Scan(&res)
+		assertNilF(t, err)
+		assertFalseF(t, res.Valid)
+
+		assertTrueF(t, rows.Next())
+		err = rows.Scan(&res)
+		assertNilF(t, err)
+		assertFalseF(t, res.Valid)
+
+		assertTrueF(t, rows.Next())
+		err = rows.Scan(&res)
+		assertNilF(t, err)
+		assertTrueE(t, res.Valid)
+		assertEqualIgnoringWhitespaceE(t, res.String, `[1, 2, 3]`)
+
+		assertTrueF(t, rows.Next())
+		err = rows.Scan(&res)
+		assertNilF(t, err)
+		assertTrueE(t, res.Valid)
+		assertEqualIgnoringWhitespaceE(t, res.String, `[1, 2, 3]`)
+	})
+}