Обсуждение: (How to) Make SQLData of UUID?

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

TLDR, primary question: when I create a custom subclass of PGObject to match a composite type containing the Text/varchar data; how do I properly deserialize these text fields in void PGObject::setValue(String s) and how do I properly serialize them in String PGObject::getValue()?

More explanation (for the readers and even for myself):

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.

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

Imagine I have a different type CREATE TYPE MYTYPE2 AS (a TEXT, b TEXT).

To use it in Java, both ways (receiving in the ResultSets, and setting in the prepared statements), I’ll need to write the matching Java class, something like this:

public abstract class MyType2 extends PGobject implements Serializable, Cloneable {

    public String a;
    public String b;

    // Not all the necessary methods are shown

    public MyType2() { setType(""); }

    public MyType2(String a, String b) {
        this();
        this.a = a; 

        this.b = b; 

    }

    @Override
    public void setValue(String s) throws SQLException {
        // This method will parse
        // the record literal from PostgreSQL

        // and set the data
    }

    @Override public String getValue() {
        // This method will convert

        // the data into the PostgreSQL-compatible

        // record literal

    }

}

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.

1. Actually, I seem to have enough luck parsing the literal inside the void setValue(String s).

The incoming string will be something like ("ABC","DEF"); with some weird handling of NULLs.

Create a new PGTokenizer that cuts the leading and the trailing brackets, and that assumes the items are split with comma:

PGtokenizer t = new PGtokenizer(PGtokenizer.removePara(s), ',');

Then get both tokens:
String aRaw = t.getToken(0), bRaw = t.getToken(1);

Interestingly, there may be no getToken(1), if b is NULL. Therefore, if t.getSize() == 1 (rather than 2), I should consider b = null, but that’s a different story.

Then I’ll check both aRaw and bRaw. If they are empty strings, the according field will be null, too.
And finally, I should remove the leading and trailing double quotes if they exist; and then,.. I manually deal with the backslashes and double-quotes as they income doubled!? 

a = PGtokenizer.remove(aRaw, "\"", "\"")
    // replace <""> to <">
    .replaceAll("\"\"", "\"")
    // replace <\\> to <\>
    .replaceAll("\\\\\\\\", "\\\\");

Is that right way to deal with it, so low-level? But there seems no helper de-escaping functions that I could use and be safe. But don’t I miss something else to be de-escaped?..

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?

пн, 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

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 ? 

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 

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

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)

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

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

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. 

(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

Hello,

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. 

I guess it. It seems a huge job even at the first glance, especially after I walked through the PGObject (and some reference gis-related subclasses) implementation. Maybe in the future, someday... cause it is really way simpler to use.

For example, the type I’ve mentioned before, CREATE TYPE MYTYPE2 AS (a TEXT, b TEXT), would be bound to Java with something as basic as:

@Override

public void readSQL(SQLInput in, String type) throws SQLException {

    a = in.readString();

    b = in.readString();

}

@Override

public void writeSQL(SQLOutput out) throws SQLException {

    out.writeString(a);

    out.writeString(b);

}

But well, for now what I’ve found seems sufficient at least for me. I’ll think about an article on this, maybe, so some people trying to find out anything on how to subclass PGObject, will at least find something.

And the serialization-deserialization helpers like I’ve mentioned above could be good to have maybe in the official code; like, even in Utils class.

(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

Nice! I’ve seen it whenever I wrote any PL/PgSQL code, but haven’t guessed out it could be used here as well. It will add some overhead for the delimiters, but well, it could be a good improvement and basically easier.

And what about my other escaping-deescaping code? Does it look safe and reasonable, or I may be missing something else?

--

Alex Myodov