formatJSON ignores type converter in field definition

[rick.l...@gmail.com]

Hi,

when I have following field definition in a table:

public final org.jooq.TableField<my.jooq.json.serialize.test.tables.records.TestRecord, org.joda.time.DateTime> TIME_UTC
    = createField("TIME_UTC",
                  org.jooq.impl.SQLDataType.BIGINT.nullable(false),
                  this,
                  null,
                  new my.jooq.json.serialize.test.converter.DateTimeConverter());

With following Converter:

public class DateTimeConverter implements Converter<Long, DateTime>

formatJson returns the field definition:

...

"fields":[{"name":"TIME_UTC","type":"BIGINT"},

...

So far so good, as I assumed that jOOQ would use the underlying database type in order to serialize and deserialize the JSON string easily.

But the data part looks like this:

"2016-06-07T09:00:00.000+02:00"

formatJSON converts the custom data type from the fetched record by using toString().

(see: org.jooq.tools.json.JSONValue#toJSONString in github)

I expected the json serializer would use the converter to get the database type back as a Long value.

Afterwards the deserializer is not able to turn the String value to BIGINT and renders it to a null value.

My question: is this working as expected, is it a bug or is formatJSON not intended to be used with custom types?

Thanks in advance

Rick

[Lukas Eder]

Hi Rick,

Thank you very much for your report. That's a very interesting observation. It doesn't just affect the JSON export, but any interaction with jOOQ. The DataType associated with any Field is always the original database DataType, possibly enriched with a custom Converter and/or Binding. The value exposed to clients is of the type <U> as defined in the Converter and/or Binding, where <U> defaults to <T>, the database type, if no special Converter and/or Binding is specified.

So, as you can see, this is the expected behaviour, but of course it is very misleading, at least in a JSON export context, as consumers of such JSON documents have no means of guessing that the "type":"BIGINT" was converted to some custom type.

What should definitely be added is this extra information about the value having been converted. I've created a feature request for this:

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

Perhaps, there's also room for exporting the <T> type along with the <U> type, at least as an opt-in feature, but then the question is why there's a converter in the first place. I'll still need to think about that latter idea.

Hope this helps,

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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[rick.l...@gmail.com]

Thanks, I understand the issue.

But keep in mind, that the main issue in serialization and deserialization of jOOQ Records is the aforementioned use of toString as a last resort.

toString will yield in almost any case to serialized (json) data which is not deserializable anymore, as user types usually don't have a string based constructor.

So I would suggest to go with sql datatypes <T>.

Here the value of the user type <U> needs to be converted back to <T> during format json.

Then serialize the known sql datatype to json.

In this case json would have BIGINT with the Long value.

Deserialization goes in reverse order.

[Lukas Eder]

Hi Rick

2016-11-17 18:07 GMT+01:00 <rick.l...@gmail.com>:

Thanks, I understand the issue.

But keep in mind, that the main issue in serialization and deserialization of jOOQ Records is the aforementioned use of toString as a last resort.

toString will yield in almost any case to serialized (json) data which is not deserializable anymore, as user types usually don't have a string based constructor.

You're right. I'm sorry, I've overlooked that detail. I do think that is a separate topic from the binding/converter issue. I mean, if you want the BIGINT to be serialised to JSON, then why put a converter there in the first place? The converter will guarantee that the Result<?> object will contain your user type, not the database type. From then on, the database type is irrelevant in all further processing.

Now, I do agree that there should be extra functionality to intercept that last resort toString() call.

 

So I would suggest to go with sql datatypes <T>.

Here the value of the user type <U> needs to be converted back to <T> during format json.

Then serialize the known sql datatype to json.

In this case json would have BIGINT with the Long value.

Again, I don't see the point of your suggestion:

1. From a backwards compatibility perspective, that change is almost impossible (imagine a converter that doesn't change the type but just the value, etc...)

2. Why have a converter in place in the first place, if you as a user *know* that you'll want the database type in the end result?

In any case, I think that you're right in general, and jOOQ should offer an SPI (probably based again on Converter) for custom serialisation / deserialisation, e.g. a Converter<T, String>. I've registered another feature request for this:

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

Does that respond to your needs?

[rick.l...@gmail.com]

Thanks for your support!

Yes, at least it would work as expected when implementing the SPI on my own.

But as you mentioned SPI, I second-guessed, if it makes sense at all to implement a JSON de/serializer in jOOQ.

Finally you would find yourself building a jOOCKsen JSON library!?

In my use-case using jOOQ to serialize and deserialize was quite too hasty.

Maybe a library which excels on the matter of JSON de-serialization is the right tool.

[Lukas Eder]

Hi Rick,

2016-12-01 16:34 GMT+01:00 <rick.l...@gmail.com>:

Thanks for your support!

Yes, at least it would work as expected when implementing the SPI on my own.

But as you mentioned SPI, I second-guessed, if it makes sense at all to implement a JSON de/serializer in jOOQ.

Finally you would find yourself building a jOOCKsen JSON library!?

You're right, that certainly shouldn't be the goal here. When I said "custom serialisation", I didn't mean only JSON. jOOQ allows for serialising to different formats out of the box. The only thing that's a bit weird is the call to toString(), right now. So a new SPI (if it's implemented, that's not decided yet), would work for all serialisation formats.

 

In my use-case using jOOQ to serialize and deserialize was quite too hasty.

Apart from the missing custom data type serialisation support, was there anything else you found missing?

Maybe a library which excels on the matter of JSON de-serialization is the right tool.

Maybe. But that might mean a lot of extra work in the "default" use-case, which is to store some data as JSON (or CSV, etc.) and then load it again using the Loader API.

See, there are always "expert" tools for specific domains like serialisation. But if you ever worked with SQL Developer (or a similar SQL client), didn't you appreciate the fact that there was some out-of-the-box CSV export from time to time? Often, that's good enough, especially if the serialised format is not that important to you (i.e. if it's perfectly fine if jOOQ fully controls it).

Hope this clarifies the motivation behind the existing API.

Lukas

[rick.l...@gmail.com]

Apart from the missing custom data type serialisation support, was there anything else you found missing?

So far I didn't find any obvious functionality missing.

Maybe a library which excels on the matter of JSON de-serialization is the right tool.

Maybe. But that might mean a lot of extra work in the "default" use-case, which is to store some data as JSON (or CSV, etc.) and then load it again using the Loader API.

Yes, that was our short-term use-case. We exported data from one system, transferred it via HTTP/JSON to a remote system in order to import the data plain as it is.

As we are using custom converters for data types like date-time, we ran into the mentioned issue.

See, there are always "expert" tools for specific domains like serialisation. But if you ever worked with SQL Developer (or a similar SQL client), didn't you appreciate the fact that there was some out-of-the-box CSV export from time to time?

Yes, I'm using import/export in those tools (like SQuirrel) from time-to-time.

But in code it is sometimes easier to transfer data.

 

Often, that's good enough, especially if the serialised format is not that important to you (i.e. if it's perfectly fine if jOOQ fully controls it).

Yes, that's right. 

[Lukas Eder]

2016-12-02 9:30 GMT+01:00 <rick.l...@gmail.com>:

Apart from the missing custom data type serialisation support, was there anything else you found missing?

So far I didn't find any obvious functionality missing.

OK, so I'm hoping that this can be fixed easily with the new SPI :)

I'm thinking that it should be easy to combine existing Converter / Binding SPIs (which you have already) with an additional From/To String Serialisation SPI.

Thanks for all your feedback, that was very valuable!

Lukas

[simon.nie...@gmail.com]

Came across the same issue today, and as jOOQ features no. 5670 and 5673 seems to keep getting pushed back, I implemented a intermediate custom solution (see github). The CustomDatatypeProcessor takes a java.nio.file.Path, reads it and converts record data based on the TableRecord -> Fields -> Converter. Reusing will need some changes, mainly in the coerce() method, but maybe this will help someone who stumbles upon this thread.

Simon

[Lukas Eder]

Hi Simon,

2018-04-13 20:20 GMT+02:00 <simon.nie...@gmail.com>:

Came across the same issue today, and as jOOQ features no. 5670 and 5673 seems to keep getting pushed back

Indeed, the features haven't seen enough perceived attention yet, so other features were prioritised on the roadmap. It will definitely be very useful to be able to control the export / import formatting in the long run, though.

 

, I implemented a intermediate custom solution (see github). The CustomDatatypeProcessor takes a java.nio.file.Path, reads it and converts record data based on the TableRecord -> Fields -> Converter. Reusing will need some changes, mainly in the coerce() method, but maybe this will help someone who stumbles upon this thread.

Thanks a lot for sharing this with the community!

Lukas