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

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On Tue, 23 Jul 2019 at 08:43, Alexander Myodov <amyodov(at)gmail(dot)com> wrote:

> 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
> .
>

Yes, we did not implement it. PR's are welcome, but it's a bigger job than
it appears at first glance.

>
>
>
>> 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 :)
>
no worries.

>
>
>> 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.
>

So you may have much easier time using dollar quoting
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING

>
> 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
>