Re: (How to) Make composite PGObject with Text? (Was: (How to) MakeSQLData of UUID?) - Mailing list pgsql-jdbc

Hello,

My response is inline, too

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

See my response inline

On Mon, 22 Jul 2019 at 20:45, Alexander Myodov <amyodov@gmail.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@gmail.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