Locally extending jOOQ to support different mappings (question 2)

[The bear in Boulder]

This is a second question raised while looking at our existing code and asking how it can be better integrated into jOOQ.

We need to change the DataType conversions for the SQLTypes. For instance we use BigInteger and BigDecimal for all numeric SQL types, the Joda classes for all temporal SQL types, etc. This accounts for a fairly large amount of our custom code AND our ugly custom code.

I know one possibility is to fork and create new databases like MY_POSTGRES which is identical to POSTGRES except for the org.jooq.util.postgres.PostgresDataType class. If I understand the design I can use definitions like

    public static final DataType<BigInteger> SMALLINT = new DefaultDataType<BigInteger>(SQLDialect.POSTGRES, SQLDataType.SMALLINT, "smallint");

and

    public static final DataType<org.joda.time.LocalDate> DATE  = new DefaultDataType<org.joda.time.LocalDate>(SQLDialect.POSTGRES, SQLDataType.DATE, "date");

That's a bit annoying since we'll want to reuse the rest of the PostgreSQL-specific code. Could we just overwrite the entries in the DefaultDataType cache instead? That might eliminate the need for a fork... or would we need one anyway for tools.Convert and possibly other classes?

Again I know this is extremely advanced and generally discouraged. I'm only asking because the alternatives are also pretty painful.

Thanks,

Bear

[Lukas Eder]

Hi Bear,

Are you aware of the possibility to apply data type rewriting in the code generator, or to apply converters or data type bindings?

- https://www.jooq.org/doc/latest/manual/code-generation/data-type-rewrites

- https://www.jooq.org/doc/latest/manual/code-generation/custom-data-types

- https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings

Note that these things can also be applied even if you're not using a code generator. Let me know if you have questions about this.

Further comments inline.

2018-03-18 21:01 GMT+01:00 The bear in Boulder <bgi...@coyotesong.com>:

I know one possibility is to fork and create new databases like MY_POSTGRES which is identical to POSTGRES except for the org.jooq.util.postgres.PostgresDataType class.

That sounds like a lot of work to get it completely right.

 

That's a bit annoying since we'll want to reuse the rest of the PostgreSQL-specific code. Could we just overwrite the entries in the DefaultDataType cache instead?

You could, and it might work for some cases, but I'm not going to recommend, nor endorse it, as I have no idea what the overall effects of such a hack would be. It doesn't sound like a very good idea.

Again I know this is extremely advanced

Not so advanced :) jOOQ has had support for custom data type conversion since very early days, and the custom data type binding SPI solves almost every custom data type problem we've encountered on this list so far, by allowing to override how custom types are bound to JDBC.

The only missing feature as far as I can tell is to allow for binding a single client data type to a collection of columns (e.g. a range type to 2 columns "from", and "to").

Hope this helps,

Lukas

[Bear Giles]

I'll look into it. We can't use code generation so I haven't been exploring that part of the API.

(Well, as a mental exercise I wondered what would happen if we took the customer's SQL, ran it through code generation, ran that through a compiler, then loaded the new class via a custom classloader. But that's mostly as an April Fools prank for my boss because I have no trouble imagining how the PM would respond to that idea. ;-) )

Bear

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

[Bear Giles]

​Yes, the forcedType should go a long way towards cleaning up our code. You should probably mention it in the javadoc for DataType, Binding, and Converter. :-)

Wh​at did you have in mind? The connection details and schema are provided by the user at runtime so our options are limited but we could set up an empty schema if necessary. Would that generate the standard classes with converters we specify or is it all hidden in schema-specific calls? Or were you thinking of setting a runtime flag?

Bear

[Lukas Eder]

Heh, we're not far away from this. Since jOOQ 3.9, there's a SQL parser which can parse pretty much all SQL that is supported by the jOOQ API. Eventually, we'll implement a SQLDialect.JOOQ dialect, which will output Java code, rather than SQL code, which can then again be compiled :)

--
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+unsubscribe@googlegroups.com.

[Lukas Eder]

2018-03-19 17:24 GMT+01:00 Bear Giles <bgi...@coyotesong.com>:

​Yes, the forcedType should go a long way towards cleaning up our code. You should probably mention it in the javadoc for DataType, Binding, and Converter. :-)

That is a very good idea! I've created a feature request for this:

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

 

Wh​at did you have in mind? The connection details and schema are provided by the user at runtime so our options are limited but we could set up an empty schema if necessary. Would that generate the standard classes with converters we specify or is it all hidden in schema-specific calls? Or were you thinking of setting a runtime flag?

The idea is that generated code is really just a convenience for something you can create dynamically at runtime:

DataType<MyType> type = SQLDataType.VARCHAR.asConvertedDataType(new MyTypeConverter());

Field<MyType> field = field(name("my_table", "my_field"), type);

I hope this clarifies the idea a bit.

Lukas

[Bear Giles]

I'm still missing something. My code is

    private final DataType<LocalDate> dateType = SQLDataType.DATE.asConvertedDataType(new LocalDateConverter());

    private final Field<LocalDate> dateField = DSL.field(DSL.name("jooqtest", "dt"), dateType);

    @Test

    public void testDate() throws Exception {

        try (DSLContext ctx = DSL.using(connectionProvider, SQLDialect.POSTGRES)) {

            ResultQuery query = ctx.resultQuery("select dt from jooqtest limit 1");

            Record record = ctx.fetchOne(query);

            switch(record.field(0).getDataType().getSQLType()) {

                case Types.DATE:

                    Object obj = record.get(dateField);

                    System.out.println("object class: " + obj.getClass().getName());

                    break;

                default:

                    System.out.println("type: "  + dateField.getDataType().getSQLDataType());

            }

        }

and I get 

object class: java.sql.Date

instead of org.joda.time.LocalDate. I used the switch statement to ensure I'm specifying the correct SQLDataType.x.asConvertedDataType() value. The converter is a straightforward converter from java.sql.Date to org.joda.time.LocalDate.

I tried a couple other combinations. If I change the code to

                    Object obj = record.get(0, new LocalDateConverter());

or

                    Object obj = record.get(record.field(0), new LocalDateConverter());

it works but if I change it to

                    Object obj = record.get(dateField, new LocalDateConverter());

I get a compilation error. That doesn't surprise me but I don't know why the first bit of code didn't work then.

--

[Lukas Eder]

The problem is here:

            ResultQuery query = ctx.resultQuery("select dt from jooqtest limit 1");

How would you expect jOOQ to know that your dt column inside a plain SQL string is actually your dateField? It doesn't and it can't. So, the query is run directly as is against JDBC, and without any type information available at compile time, the ResultSetMetaData type information is used - you get a java.sql.Date, and since you switch on that type (the one retrieved via ResultSetMetaData), you get the behaviour you're observing.

This wouldn't happen, of course, if you had written:

ctx.select(dt).from(table(name("jooqtest"))).limit(1).fetchOne();

There's a feature request for allowing to pass type information to plain SQL ResultQuery types, but it hasn't been implemented yet:

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

The other option is the one you've mentioned further down, applying the converter on the Record that has been fetched without conversion.

[Bear Giles]

That's what I get for trying to figure this out so late in the evening. Thanks.

What I was thinking is that I defined dateField with a name

private final Field<LocalDate> dateField = DSL.field(DSL.name("jooqtest", "dt"), dateType);

(or just "dt") ​so when the record.get(field) was executed it could look at the record's field names (from the ResultSetMetaData) and the field's name and make the match that way.

​In any case I think we mostly (only?) use a RecordMapper so I guess the read code will look something like

   // outer loops elided...

    switch(field.getDataType().getSqlType()) {

        case Types.SMALL_INT:

        case Types.INT:

            map.put(field.getName(), BigIntegerType.convert(record.getField(field)));

            break;

​

        case Types.DATE:

            map.put(field.getName(), LocalDateType.convert(record.getField(field)));

            break;

​        ...

        case Types.ARRAY:

            List values = (mumble)

            switch(field.getDataType().getArrayDataType().getSqlType()) {

                case SMALL_INT:

                case INTEGER:

                    map.put(field.getName(), LocalDateType.convert(values));

                    break;

             }

             break;

        ...

        // Types corresponding to UDT will need to be handled by looking at additional information

​where the (mumble) ​involves the ResultSet -> Record tools, unless there's a more direct way to get this. I know I can't just use a map from sqltype to datatype since many databases define additional datatypes so we would have to check for null values and that's where we got into trouble before. Better to be a little verbose so it's clear that when we add support for new types we do it by adding a 'case' statement and a converter.

Now I'm wondering about the other side of this - I was thing that I could go through the Param list, do the same getSqlType() branch, etc., and that it would use the database metadata to determine the correct sqltype. Now I'm thinking that the information won't be available since we're using sql-based queries instead of queries built up like you did. I need to look more closely at how we're doing it now since I know we're able to throw data exceptions if someone is trying to convert a BigInteger into a SQL type that can't handle it.

--

[Lukas Eder]

2018-03-21 14:34 GMT+01:00 Bear Giles <bgi...@coyotesong.com>:

​where the (mumble) ​involves the ResultSet -> Record tools, unless there's a more direct way to get this.

Yes. As I said. Build the query using the jOOQ DSL API and let jOOQ execute the query for you. If you absolutely *need* to run plain SQL queries, you can still use this workaround:

ctx.select(dt).from("(select dt from jooqtest) jooqtest").fetch();

I don't fully understand your requirements and why you want to do these things with plain SQL rather than with jOOQ, but you certainly don't need to reproduce jOOQ's DefaultBinding internals the way you're doing right now.

I know I can't just use a map from sqltype to datatype since many databases define additional datatypes so we would have to check for null values and that's where we got into trouble before. Better to be a little verbose so it's clear that when we add support for new types we do it by adding a 'case' statement and a converter.

Again, I can't help here without understanding your requirements. From what you've shown so far, this is either being overengineered, or you have omitted quite a few requirements :)

 

Now I'm wondering about the other side of this - I was thing that I could go through the Param list, do the same getSqlType() branch, etc., and that it would use the database metadata to determine the correct sqltype. Now I'm thinking that the information won't be available since we're using sql-based queries instead of queries built up like you did. I need to look more closely at how we're doing it now since I know we're able to throw data exceptions if someone is trying to convert a BigInteger into a SQL type that can't handle it.

Even if you *have* to use plain SQL, you can still use the plain SQL templating API and let jOOQ and jOOQ's Converter / Binding SPIs apply here:

https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating 

E.g.

ctx.fetch("select dt from jooqtest where dt = {0}", val(myType, dt.getDataType()));

Hope this helps,

Lukas