Repository: GoogleCloudPlatform/pgadapter (java)

Issue:
UUID parameter values do not work when the returned PG version is >= 8.3
Hibernate will try to use the `UUID` data type if PGAdapter reports that it is PostgreSQL version 8.3. or higher. `UUID` is currently not accepted as a valid input type, which causes the statement to fail.


Pull request:
feat: accept UUID as a parameter value
Some frameworks, such as Hibernate, will assume that the PostgreSQL server supports the UUID type if the version is >= 8.3. This adds support for incoming UUID parameter values. These are converted to STRING values before being sent to Cloud Spanner.

Fixes #517

Patch:
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/parsers/Parser.java b/src/main/java/com/google/cloud/spanner/pgadapter/parsers/Parser.java
index 1490cc3b..16141cec 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/parsers/Parser.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/parsers/Parser.java
@@ -89,6 +89,8 @@ public abstract class Parser<T> {
       case Oid.TEXT:
       case Oid.VARCHAR:
         return new StringParser(item, formatCode);
+      case Oid.UUID:
+        return new UuidParser(item, formatCode);
       case Oid.TIMESTAMP:
       case Oid.TIMESTAMPTZ:
         return new TimestampParser(item, formatCode, sessionState);
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/parsers/UuidParser.java b/src/main/java/com/google/cloud/spanner/pgadapter/parsers/UuidParser.java
new file mode 100644
index 00000000..01bebe59
--- /dev/null
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/parsers/UuidParser.java
@@ -0,0 +1,115 @@
+// 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 com.google.cloud.spanner.pgadapter.parsers;
+
+import com.google.api.core.InternalApi;
+import com.google.cloud.spanner.Statement;
+import com.google.cloud.spanner.pgadapter.error.PGException;
+import com.google.cloud.spanner.pgadapter.error.SQLState;
+import com.google.cloud.spanner.pgadapter.error.Severity;
+import java.nio.charset.StandardCharsets;
+import java.util.UUID;
+import javax.annotation.Nonnull;
+import org.postgresql.util.ByteConverter;
+
+/**
+ * Translate from wire protocol to UUID. This is currently a one-way conversion, as we only accept
+ * UUID as a parameter type. UUIDs are converted to strings.
+ */
+@InternalApi
+public class UuidParser extends Parser<String> {
+
+  UuidParser(byte[] item, FormatCode formatCode) {
+    switch (formatCode) {
+      case TEXT:
+        this.item =
+            item == null ? null : verifyStringValue(new String(item, StandardCharsets.UTF_8));
+        break;
+      case BINARY:
+        this.item = verifyBinaryValue(item);
+        break;
+      default:
+        handleInvalidFormat(formatCode);
+    }
+  }
+
+  static void handleInvalidFormat(FormatCode formatCode) {
+    throw PGException.newBuilder("Unsupported format: " + formatCode.name())
+        .setSQLState(SQLState.InternalError)
+        .setSeverity(Severity.ERROR)
+        .build();
+  }
+
+  @Override
+  public String stringParse() {
+    return this.item;
+  }
+
+  @Override
+  protected byte[] binaryParse() {
+    if (this.item == null) {
+      return null;
+    }
+    return binaryEncode(this.item);
+  }
+
+  static String verifyStringValue(@Nonnull String value) {
+    try {
+      //noinspection ResultOfMethodCallIgnored
+      UUID.fromString(value);
+      return value;
+    } catch (Exception exception) {
+      throw createInvalidUuidValueException(value, exception);
+    }
+  }
+
+  static String verifyBinaryValue(byte[] value) {
+    if (value == null) {
+      return null;
+    }
+    if (value.length != 16) {
+      throw PGException.newBuilder("Invalid UUID binary length: " + value.length)
+          .setSeverity(Severity.ERROR)
+          .setSQLState(SQLState.InvalidParameterValue)
+          .build();
+    }
+    return new UUID(ByteConverter.int8(value, 0), ByteConverter.int8(value, 8)).toString();
+  }
+
+  static byte[] binaryEncode(String value) {
+    try {
+      UUID uuid = UUID.fromString(value);
+      byte[] val = new byte[16];
+      ByteConverter.int8(val, 0, uuid.getMostSignificantBits());
+      ByteConverter.int8(val, 8, uuid.getLeastSignificantBits());
+      return val;
+    } catch (Exception exception) {
+      throw createInvalidUuidValueException(value, exception);
+    }
+  }
+
+  static PGException createInvalidUuidValueException(String value, Exception cause) {
+    return PGException.newBuilder("Invalid UUID: " + value)
+        .setSeverity(Severity.ERROR)
+        .setSQLState(SQLState.InvalidParameterValue)
+        .setCause(cause)
+        .build();
+  }
+
+  @Override
+  public void bind(Statement.Builder statementBuilder, String name) {
+    statementBuilder.bind(name).to(this.item);
+  }
+}


Test patch:
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
index 0676b60c..5e5e3d3f 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
@@ -23,6 +23,7 @@ import static org.junit.Assert.assertNull;
 import static org.junit.Assert.assertThrows;
 import static org.junit.Assert.assertTrue;
 import static org.junit.Assert.fail;
+import static org.junit.Assume.assumeTrue;
 
 import com.google.cloud.ByteArray;
 import com.google.cloud.Date;
@@ -3138,6 +3139,31 @@ public class JdbcMockServerTest extends AbstractMockServerTest {
     assertEquals(1, mockSpanner.countRequestsOfType(CommitRequest.class));
   }
 
+  @Test
+  public void testUUIDParameter() throws SQLException {
+    assumeTrue(pgVersion.equals("14.1"));
+
+    String jdbcSql = "SELECT * FROM all_types WHERE col_uuid=?";
+    String pgSql = "SELECT * FROM all_types WHERE col_uuid=$1";
+    UUID uuid = UUID.randomUUID();
+    mockSpanner.putStatementResult(
+        StatementResult.query(
+            Statement.newBuilder(pgSql).bind("p1").to(uuid.toString()).build(),
+            ALL_TYPES_RESULTSET));
+
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      try (PreparedStatement statement = connection.prepareStatement(jdbcSql)) {
+        statement.setObject(1, uuid);
+        try (ResultSet resultSet = statement.executeQuery()) {
+          assertTrue(resultSet.next());
+          assertFalse(resultSet.next());
+        }
+      }
+    }
+
+    assertEquals(1, mockSpanner.countRequestsOfType(ExecuteSqlRequest.class));
+  }
+
   private void verifySettingIsNull(Connection connection, String setting) throws SQLException {
     try (ResultSet resultSet =
         connection.createStatement().executeQuery(String.format("show %s", setting))) {
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/parsers/UuidParserTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/parsers/UuidParserTest.java
new file mode 100644
index 00000000..e406b686
--- /dev/null
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/parsers/UuidParserTest.java
@@ -0,0 +1,149 @@
+// Copyright 2022 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 com.google.cloud.spanner.pgadapter.parsers;
+
+import static com.google.cloud.spanner.pgadapter.parsers.UuidParser.binaryEncode;
+import static org.junit.Assert.assertArrayEquals;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertThrows;
+import static org.mockito.Mockito.mock;
+
+import com.google.cloud.spanner.pgadapter.error.PGException;
+import com.google.cloud.spanner.pgadapter.error.SQLState;
+import com.google.cloud.spanner.pgadapter.error.Severity;
+import com.google.cloud.spanner.pgadapter.parsers.Parser.FormatCode;
+import com.google.cloud.spanner.pgadapter.session.SessionState;
+import java.nio.charset.StandardCharsets;
+import java.util.UUID;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.JUnit4;
+import org.postgresql.core.Oid;
+import org.postgresql.util.ByteConverter;
+
+@RunWith(JUnit4.class)
+public class UuidParserTest {
+
+  @Test
+  public void testCreate() {
+    assertEquals(
+        UuidParser.class,
+        Parser.create(
+                mock(SessionState.class),
+                UUID.randomUUID().toString().getBytes(StandardCharsets.UTF_8),
+                Oid.UUID,
+                FormatCode.TEXT)
+            .getClass());
+    assertEquals(
+        UuidParser.class,
+        Parser.create(
+                mock(SessionState.class),
+                binaryEncode(UUID.randomUUID().toString()),
+                Oid.UUID,
+                FormatCode.BINARY)
+            .getClass());
+  }
+
+  @Test
+  public void testTextToText() {
+    String uuidStringValue = "c852ee2a-7521-4a70-a02f-2b9d0dd9c19a";
+    UuidParser parser =
+        new UuidParser(uuidStringValue.getBytes(StandardCharsets.UTF_8), FormatCode.TEXT);
+    assertEquals(uuidStringValue, parser.stringParse());
+
+    parser = new UuidParser(null, FormatCode.TEXT);
+    assertNull(parser.stringParse());
+  }
+
+  @Test
+  public void testTextToBinary() {
+    String uuidStringValue = "c852ee2a-7521-4a70-a02f-2b9d0dd9c19a";
+    UuidParser parser =
+        new UuidParser(uuidStringValue.getBytes(StandardCharsets.UTF_8), FormatCode.TEXT);
+    UUID uuid = UUID.fromString(uuidStringValue);
+    byte[] bytes = new byte[16];
+    ByteConverter.int8(bytes, 0, uuid.getMostSignificantBits());
+    ByteConverter.int8(bytes, 8, uuid.getLeastSignificantBits());
+    assertArrayEquals(bytes, parser.binaryParse());
+
+    parser = new UuidParser(null, FormatCode.TEXT);
+    assertNull(parser.binaryParse());
+  }
+
+  @Test
+  public void testBinaryToText() {
+    String uuidStringValue = "c852ee2a-7521-4a70-a02f-2b9d0dd9c19a";
+    UUID uuid = UUID.fromString(uuidStringValue);
+    byte[] bytes = new byte[16];
+    ByteConverter.int8(bytes, 0, uuid.getMostSignificantBits());
+    ByteConverter.int8(bytes, 8, uuid.getLeastSignificantBits());
+
+    UuidParser parser = new UuidParser(bytes, FormatCode.BINARY);
+    assertEquals(uuidStringValue, parser.stringParse());
+
+    parser = new UuidParser(null, FormatCode.BINARY);
+    assertNull(parser.stringParse());
+  }
+
+  @Test
+  public void testBinaryToBinary() {
+    String uuidStringValue = "c852ee2a-7521-4a70-a02f-2b9d0dd9c19a";
+    UUID uuid = UUID.fromString(uuidStringValue);
+    byte[] bytes = new byte[16];
+    ByteConverter.int8(bytes, 0, uuid.getMostSignificantBits());
+    ByteConverter.int8(bytes, 8, uuid.getLeastSignificantBits());
+
+    UuidParser parser = new UuidParser(bytes, FormatCode.BINARY);
+    assertArrayEquals(bytes, parser.binaryParse());
+
+    parser = new UuidParser(null, FormatCode.BINARY);
+    assertNull(parser.binaryParse());
+  }
+
+  @Test
+  public void testInvalidBinaryInput() {
+    PGException exception =
+        assertThrows(PGException.class, () -> new UuidParser(new byte[8], FormatCode.BINARY));
+    assertEquals(SQLState.InvalidParameterValue, exception.getSQLState());
+    assertEquals(Severity.ERROR, exception.getSeverity());
+  }
+
+  @Test
+  public void testInvalidTextInput() {
+    PGException exception =
+        assertThrows(
+            PGException.class,
+            () -> new UuidParser("foo".getBytes(StandardCharsets.UTF_8), FormatCode.TEXT));
+    assertEquals(SQLState.InvalidParameterValue, exception.getSQLState());
+    assertEquals(Severity.ERROR, exception.getSeverity());
+  }
+
+  @Test
+  public void testInvalidTextValueForBinaryEncode() {
+    PGException exception = assertThrows(PGException.class, () -> UuidParser.binaryEncode("bar"));
+    assertEquals(SQLState.InvalidParameterValue, exception.getSQLState());
+    assertEquals(Severity.ERROR, exception.getSeverity());
+  }
+
+  @Test
+  public void testHandleInvalidFormatCode() {
+    PGException exception =
+        assertThrows(PGException.class, () -> UuidParser.handleInvalidFormat(FormatCode.TEXT));
+    assertEquals(SQLState.InternalError, exception.getSQLState());
+    assertEquals(Severity.ERROR, exception.getSeverity());
+    assertEquals("Unsupported format: TEXT", exception.getMessage());
+  }
+}