Re: (How to) Make composite PGObject with Text? (Was: (How to) Make SQLData of UUID?)

Hello,

My response is inline, too

вт, 23 июл. 2019 г. в 13:49, Dave Cramer <pg(at)fastcrypt(dot)com>:

> See my response inline
>
> On Mon, 22 Jul 2019 at 20:45, Alexander Myodov <amyodov(at)gmail(dot)com> wrote:
>
>> Well, seems like was my primary mistake in original email was trying to
>> use the SQLData/typemap approach; seems it is still not supported by
>> org.postgresql driver. And the primary way of using the PostgreSQL's
>> composite types in Java is to make a matching PGObject subclass, and
>> bind it through PGConnection::addDataType.
>>
> I'd be curious why and how the SQLData/typemap is better ?
>

It seems a part of JDBC standard interfaces, rather clearly defined and
well documented, see an example at
https://docs.oracle.com/javase/tutorial/jdbc/basics/sqlcustommapping.html.

> Alas, there is rather little documentation on actually doing that; and the
>> existing github code gives some hints, but insufficiently. I actually
>> managed to map the type created like CREATE TYPE MYFUNC_RETURN_TYPE AS (key
>> UUID) to a Java class. But then faced even a more basic issue: how to
>> bind a type containing the text strings (like Text or varchar)?
>>
> We would love for you to contribute to the documentation
>
>> I’d love to; but for now, I am trying to at least figure everything out
myself :)

> The problems are: 1. how to parse (setValue) the serialized literal with
>> this type properly? 2. And more importantly, how to serialize it (getValue)
>> properly for PostgreSQL?
>> Both questions assume that the string may be as wild as it happens - any
>> Unicode, any single or double quotes, any combinations of backslash
>> characters.
>>
>> are you sure you need to serialize it properly? The driver does encode
> strings that it sends to the backend.
>
> https://github.com/pgjdbc/pgjdbc/blob/d0453b7582975438eb889082cffcfd8ebe6e3e4d/pgjdbc/src/main/java/org/postgresql/core/v3/SimpleParameterList.java#L386
>
You are right, UTF8 compatibility seems fine already. Most of the problems
is with proper quoting/escaping.

> *2.* More problem is with the serializing the data in the String
>> getValue().
>> I’ll have to create the string like ("Value of a","Value of b"); if some
>> of them e.g. a is NULL, the string will look something like (,"Value of
>> b").
>> Well, okay; I should add leading/trailing brackets and split them with
>> comma; and if a or b is null, put an empty string instead of it.
>> But how to properly escape the string itself?
>> The best I found is String.format("\"%s\"", Utils.escapeLiteral(null,
>> str, true)) – but it doesn’t seem to handle various combinations of
>> single or double quotes.
>>
>> Any idea how to escape the strings properly then?
>>
>
> If you send such a string into setString does it fail ? (I'm genuinely
> curious)
>
I had no chance to test it with setString (as my ultimate target is to use
it through setObject/getObject, which would accept/return my created MyType2
class).
But on the other hand, I seem to have enough luck to escape it properly, so
it passes all the data forward and back, and no symbols (which came to my
mind at least) fail.

(For future readers:)
I seem to have get some success with the getValue() implementation.
The general procedure to escape strings is like this:
Do NOT use the Utils.escapeLiteral() (what is the first idea that comes to
the mind). Instead, do the following:
if the incoming object is null, the result string (token) should have an
empty string ("").
Otherwise, replace each double-quote with two double-quotes; replace each
backslash with two backslashes; surround it with double quotes.

String preEscaped = str
// replace <"> to <"">
.replaceAll("\"", "\"\"")
// replace <\> to <\\>
.replaceAll("\\\\", "\\\\\\\\");

String escaped = String.format("\"%s\"", preEscaped);

This may be made into a helper method, like, tokenizeString. There may be
other methods like tokenizeUuid, tokenizeBytea. Here are some examples:

public static String tokenizeString(String str) {
if (str == null) {
return "";
} else {
String escaped = str
// replace <"> to <"">
.replaceAll("\"", "\"\"")
// replace <\> to <\\>
.replaceAll("\\\\", "\\\\\\\\");
return String.format("\"%s\"", escaped);
}
}

public static String tokenizeUuid(UUID uuid) {
return (uuid == null) ? "" : uuid.toString();
}

public static String tokenizeBytea(byte[] bytea) {
return (bytea == null) ?
"" :
String.format("\"\\\\x%s\"", Utils.toHexString(bytea));
}

And the final getValue() implementation (for our test class, having two
String fields, String a and String b) will look like this:

@Override public String getValue() {
return String.format("(%s,%s,%s,%s)",
tokenizeString(a), tokenizeString(b)
);
}

This seems rather unsafe and low-level but it seems working.

The most documentation on this that I could find, is in
https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-IO-SYNTAX. I
wrap the data it with parentheses as needed, and reference the part “Double
quotes and backslashes embedded in field values will be doubled” to do my
custom “string tokenization”. This documentation article also suggests to
use ROW() method but it doesn’t work inside getValue()-provided strings.

> Dave
>
>>
>> пн, 22 июл. 2019 г. в 00:58, Alexander Myodov <amyodov(at)gmail(dot)com>:
>>
>>> Hello!
>>>
>>> I seem to miss something obvious maybe, but I cannot find a way to use
>>> UUID as SQLData; for example, to make a proper JDBC handling of custom
>>> PostgreSQL type containing an UUID.
>>>
>>> Imagine I made a custom type returned from some PL/PgSQL function:
>>>
>>> CREATE TYPE MYFUNC_RETURN_TYPE AS
>>>
>>> (
>>>
>>> key UUID
>>>
>>> );
>>>
>>>
>>> Now, to handle it in JDBC, I need to make a custom type like MyfuncReturnType implements
>>> SQLData, and then add it to the type map of the connection. In
>>> MyfuncReturnType, I’ll need to implement void writeSQL(SQLOutput stream)
>>> method, and at some point I’ll need to do something like this: to call
>>> either
>>>
>>> SQLOutput::writeObject(SQLData x)
>>>
>>> or
>>>
>>> SQLOutput::writeObject(Object x, SQLType targetSqlType),
>>>
>>> passing my UUID in somehow.
>>>
>>>
>>> But UUID doesn’t satisfy SQLData interface in any way; and if I use the
>>> second method, I do not have a proper SQLType for UUID anywhere.
>>>
>>>
>>> I previously used the pgjdbc-ng driver, and they had a collection of
>>> postgresql-specific SQLType's; but that driver had other problems, so I
>>> decided to switch to the mainstream JDBC driver, and faced this lack.
>>>
>>>
>>> Any hints please?
>>>
>>> --
>>> Alex Myodov
>>>
>>
>>
>> --
>> Alex Myodov
>>
>

--
Alex Myodov