Postgres UDT array with custom binding not supported

[leigh....@bitshift.com.au]

Hi there!

Perhaps an unusual case but one I ran into today:

If I have a Postgres UDT ('MyUdt') that contains a field mapped with a custom JOOQ binding (like a hstore or json field as mentioned here: http://www.jooq.org/doc/3.8/manual/code-generation/custom-data-type-bindings/) and that UDT is used to form an array field ('MyUdt[]') on another table, then JOOQ fails to generate the correct SQL to insert into that array of UDTs.

The code being triggered when binding the provided value to the SQL statement is in DefaultBinding.java:1108:

else if (actualType.isArray()) {
    switch (dialect.family()) {
        case POSTGRES: {
            ctx.statement().setString(ctx.index(), toPGArrayString((Object[]) value));
            break;
        }

The calls below toPGArrayString assume the object and it's fields are either JOOQ records or basic types that can be mapped with toString().  At this point, the fact that a field on the object in the array has a custom binding is not considered.

Using: Postgres 9.5.3, Jooq 3.8.1

Since this is quite likely a corner case experienced by only a few I'm not anticipating a fix soon - I'm planning to rework my schema and continue on for now (rather than even make a patch).

Thanks for any input,

Leigh

[Lukas Eder]

Hi Leigh,

Thank you very much for your enquiry. Would you mind posting an example of:

- Such a PostgreSQL UDT

- A table that uses it as an array

- Your Binding implementation

- Your code generator setup

Normally, you should not pass through the DefaultBinding for this case, but bind your variable yourself. The very reason why you're using your own binding. So, I'd like to see if there's a bug that prevents your binding from being applied, or just bad code generator setup.

Thanks

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Leigh Whiting]

Hi Lukas,

A contrived table and UDT setup to demonstrate the issue would be as follows:

CREATE TYPE message AS (
    definition TEXT,
    parameters HSTORE
);

CREATE TABLE person (
    id       BIGINT NOT NULL,
    messages message[],
    PRIMARY KEY (id)
);

Here is the binding implementation I am using to bind HSTORE to Map<String, String>.  It is pretty vanilla, copied straight from the Jooq examples (very helpful!).

public class JooqHStoreToMapBinding implements Binding<Object, Map<String, String>> {

    private static final long serialVersionUID = SerialVersion.SERIAL_VERSION_UID;
    private Converter converter = new Converter();

    @Override
    public org.jooq.Converter<Object, Map<String, String>> converter() {
        return converter;
    }

    @Override
    public void sql(final BindingSQLContext<Map<String, String>> ctx) throws SQLException {
        ctx.render().visit(DSL.val(ctx.convert(converter).value())).sql("::hstore");
    }

    @Override
    public void register(final BindingRegisterContext<Map<String, String>> ctx) throws SQLException {
        ctx.statement().registerOutParameter(ctx.index(), Types.CLOB);
    }

    /**
     * Gets a Map and convert it to a string on a {@link PreparedStatement}.
     */
    @Override
    public void set(final BindingSetStatementContext<Map<String, String>> ctx) throws SQLException {
        ctx.statement().setString(ctx.index(), Objects.toString(ctx.convert(converter).value(), null));
    }

    /**
     * Gets a string value from a {@link ResultSet} and converts it to a Map
     */
    @Override
    public void get(final BindingGetResultSetContext<Map<String, String>> ctx) throws SQLException {
        ctx.convert(converter).value(ctx.resultSet().getString(ctx.index()));
    }

    /**
     * Gets a string value from a {@link CallableStatement} and converts it to a Map
     */
    @Override
    public void get(final BindingGetStatementContext<Map<String, String>> ctx) throws SQLException {
        ctx.convert(converter).value(ctx.statement().getString(ctx.index()));
    }

    // The following methods are not required for Postgres

    @Override
    public void get(final BindingGetSQLInputContext<Map<String, String>> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    @Override
    public void set(final BindingSetSQLOutputContext<Map<String, String>> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }


    private static class Converter implements org.jooq.Converter<Object, Map<String, String>> {

        private static final long serialVersionUID = SerialVersion.SERIAL_VERSION_UID;

        private static final Map<String, String> INSTANCE = new HashMap<>();
        private static final char QUOTE = '"';
        private static final String ARROW = "=>";
        private static final char COMMA = ',';

        @Override
        public Object to(final Map<String, String> userObject) {
            // Convert a map into SQL values for a hstore:
            // "key1"=>"value1","key2"=>"value2" etc.
            final StringBuilder sb = new StringBuilder();
            for (final Map.Entry<String, String> entry : userObject.entrySet()) {
                sb.append(QUOTE).append(entry.getKey()).append(QUOTE).append(ARROW)
                        .append(QUOTE).append(entry.getValue()).append(QUOTE).append(COMMA);

            }

            return sb.toString();
        }

        @Override
        public Map<String, String> from(final Object databaseObject) {
            // Convert SQL values for a hstore into a map
            final Map<String, String> map = new HashMap<>();
            if (StringUtils.isEmpty(databaseObject.toString())) {
                return map;
            }

            for(final String kvPair : databaseObject.toString().replace("\"", "").split(",")) {
                final String[] keyAndValue = kvPair.split("=>");
                map.put(keyAndValue[0], keyAndValue[1]);
            }

            return map;
        }

        @Override
        public Class<Object> fromType() {
            return Object.class;
        }

        @SuppressWarnings("unchecked")
        @Override
        public Class<Map<String, String>> toType() {
            return (Class<Map<String, String>>) INSTANCE.getClass();
        }
    }
}

Finally, my code generator is setup like this to register the binding (gradle notation):

generator() {
    name('org.jooq.util.DefaultGenerator')
    strategy {
        name('org.jooq.util.DefaultGeneratorStrategy')
    }

    database() {
        name('org.jooq.util.postgres.PostgresDatabase')
        inputSchema(jooqSchema)

        forcedTypes() {
            forcedType() {
                userType('java.util.Map<String,String>')
                binding('JooqHStoreToMapBinding')
                expression('.*Message\\.parameters')
                types('.*')
            }
        }
    }
    generate() {
        relations(true)
        deprecated(false)
        records(true)
        immutablePojos(true)
        fluentSetters(true)
    }
    target() {
        packageName("ng.${project.name}.${jooqSchema}")
        directory(outputDirectory)
    }
}

The code generator produces MessageRecord with a parameters field of the correct type (Map<String, String>) and PersonRecord with a field of type MessageRecord[] as expected.

When the MessageRecord[] is serialised the custom binding for HSTORE appears not be called at all and the objects are not serialised correctly.

If the table field is just a message (rather than an array) then the binding is applied correctly.  Am I missing something with my code generation to apply the custom binding correctly to array elements?

Thanks for taking a look at this!

Regards,

Leigh

[Lukas Eder]

Thank you very much for your patience in this matter.

I have identified the issue and fixed it for jOOQ 3.9:
https://github.com/jOOQ/jOOQ/issues/5393

The fix will also be merged in 3.8.5. Indeed the Binding's Converter is not applied correctly when serialising the nested array of UDT to the PostgreSQL JDBC driver in String form.

Note, your current Converter implementation doesn't take into account some edge cases. I've created the following converter (using jOOQ 3.9 Converter construction API), which is a bit more robust:

    public Converter<Object, Map<String, String>> converter() {

        return Converter.ofNullable(

            Object.class,

            (Class<Map<String, String>>) (Class) Map.class,

            o -> {

                String[] split = o.toString().split(",\\s*");

                return split.length == 1 && split[0].equals("")

                    ? emptyMap()

                    : Stream.of(split)

                           .map(kv -> kv.split("=>"))

                           .collect(toMap(kv -> kv[0].replace("\"", ""), kv -> kv[1].replace("\"", "")));

            }

            ,

            m -> m.entrySet()

                  .stream()

                  .map(e -> "\"" + e.getKey() + "\"=>\"" + e.getValue() + "\",")

                  .collect(joining())

        );

    }

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Leigh Whiting]

Hi Lukas,

Thankyou so much for taking the time to look into this!  It will be very helpful to have a working solution in place.

Since posting this I've switched the converter implementation to just using the toString/fromString methods from org.postgresql.util.HStoreConverter (Postgres JDBC driver).  Not as portable as your solution but fine for our cases.

Thanks again,

Leigh

[Lukas Eder]

Oh that's great to know, thanks for letting us know about the HStoreConverter. I should definitely have a closer look at that package. Might be a good candidate to add some sugar on top in a new jooq-postgres module...

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Lukas Eder]

For the record, I have created a feature request for adding a new jooq-postgres module, which would include all of these PostgreSQL specific features:

https://github.com/jOOQ/jOOQ/issues/5507

I think that might be quite well received with the community.