Can't manage to use type rewrite

[Clément Allain]

Hello,

I want to take advantage of generating jOOQ code using LiquibaseDatabase and an H2 in memory database.

I manage to make all our liquibase changesets pass with H2. They were generated with an Oracle Database. Had some issues but it's ok now :)

But the type rewriting we used doesn't work anymore.

This was the original configuration.

<forcedTypes>
<forcedType>
<userType>java.lang.Boolean</userType>
<converter>com.packages.ByteToBooleanConverter</converter>
<name>BOOLEAN</name>
<expression>.*</expression>
<types>NUMBER\(1,\s*0\)</types>
</forcedType>
</forcedTypes>

Basically we were converting NUMBER(1, 0) to java Boolean.

So now I got Byte instead of Boolean, I tried a lot of different configurations without success.

Do you have any clue ?

[Lukas Eder]

Hi Clément

H2 doesn't have a NUMBER data type. While it recognises the NUMBER syntax for compatibility reasons, internally, the type is called DECIMAL. You can try this:

create table t (n number(1,0));

select type_name

from information_schema.columns

where table_name = 'T';

You will get:

TYPE_NAME|

---------|

DECIMAL  |

Note, we could have used information_schema.columns.column_type, which still refers to the original NUMBER(1, 0) type description, but I've never found column_type to be a very reliable source.

One way to solve this is by specifying:

  <types>(NUMBER|DECIMAL)\(1,\s*0\)</types>  

I 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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/bd8cb99c-a522-4d43-97ea-6214272a4cd6n%40googlegroups.com.

[Clément Allain]

Thank you a lot for your fast and correct answer

It works but the unused forced types message still appears. I think that is misleading.

[INFO] Unused ForcedType        : <name>BOOLEAN</name><includeExpression>.*</includeExpression><includeTypes>(NUMBER|DECIMAL)\(1,\s*0\)</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>

I was actually creating a starting project with Spring Boot/Liquibase to test a column creation. But I am not familiar at all with H2. Without your answer I would have based my thinking it was a number because in the left panel of the console it appears as a NUMBER(1, 0).

In the same subject, I am also trying to generate sequences with BigInteger types, as they were generated as BigInteger while using an Oracle database as input.

Now are generated as Long. Probably because the maxValue is way less with H2 than Oracle.

So I tried this:

<forcedType>
<userType>java.math.BigInteger</userType>
<expression>.*</expression>
<objectType>SEQUENCE</objectType>
</forcedType>

Seems to works but the generated Sequences class doesn't compile because Internal.createSequence still returns a Sequence<Long>.

public static final Sequence<BigInteger> SEQ_APPEL_TRACE = Internal.createSequence("SEQ_APPEL_TRACE", DefaultSchema.DEFAULT_SCHEMA, org.jooq.impl.SQLDataType.BIGINT, null, null, null, null, false, null);

How can I change the SQLDataType used as third parameters ?

Switching the generation from an Oracle instance to an H2 isn't as easy as I thought it would be

[Lukas Eder]

Hi Clément,

I'm glad you could get the first part to work. Some comments inline

On Mon, Sep 14, 2020 at 5:13 PM Clément Allain <dalc...@gmail.com> wrote:

It works but the unused forced types message still appears. I think that is misleading.

[INFO] Unused ForcedType        : <name>BOOLEAN</name><includeExpression>.*</includeExpression><includeTypes>(NUMBER|DECIMAL)\(1,\s*0\)</includeTypes><nullability>ALL</nullability><objectType>ALL</objectType>

That's curious, are you sure there isn't perhaps another forced type configuration that also applies, leaving this one redundant / unused? I don't see how we should log this if it were used, except there's a bug of course. Would you mind reporting one, possibly with help on how to reproduce this? https://github.com/jOOQ/jOOQ/issues/new/choose 

An MCVE would be very useful to help reproduce this: https://github.com/jOOQ/jOOQ-mcve

 

I was actually creating a starting project with Spring Boot/Liquibase to test a column creation. But I am not familiar at all with H2. Without your answer I would have based my thinking it was a number because in the left panel of the console it appears as a NUMBER(1, 0).

I've been discussing the various INFORMATION_SCHEMA tables with the H2 maintainers for quite a while. There is a lot of legacy in them. I'm not sure what the rationale for each one of these things is, and why they're being kept the way they are. What you're seeing in the menu seems to be the COLUMN_TYPE column, which doesn't contain valid data types, but some formatted version of the data type.

 

In the same subject, I am also trying to generate sequences with BigInteger types, as they were generated as BigInteger while using an Oracle database as input.

Now are generated as Long. Probably because the maxValue is way less with H2 than Oracle.

So I tried this:

<forcedType>
<userType>java.math.BigInteger</userType>
<expression>.*</expression>
<objectType>SEQUENCE</objectType>
</forcedType>

I recommend using this instead:

<forcedType>

<name>DECIMAL(38, 0)</name>

<expression>.*</expression>

<objectType>SEQUENCE</objectType>

</forcedType>

If you provide a <userType/>, you also have to provide a <converter/> or <binding/>. See: https://www.jooq.org/doc/latset/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types

[Lukas Eder]

On Tuesday, September 15, 2020 at 1:08:27 PM UTC+2 Lukas Eder wrote:

 

I was actually creating a starting project with Spring Boot/Liquibase to test a column creation. But I am not familiar at all with H2. Without your answer I would have based my thinking it was a number because in the left panel of the console it appears as a NUMBER(1, 0).

I've been discussing the various INFORMATION_SCHEMA tables with the H2 maintainers for quite a while. There is a lot of legacy in them. I'm not sure what the rationale for each one of these things is, and why they're being kept the way they are. What you're seeing in the menu seems to be the COLUMN_TYPE column, which doesn't contain valid data types, but some formatted version of the data type.

Here's some interesting insight into what's going to change between H2 1.4.200 and H2 2.0:

https://github.com/jOOQ/jOOQ/issues/10231#issuecomment-692736218

[Clément Allain]

Thanks for your answers, it now builds, runs and passes all our integrations tests!

I tried to reproduce the misleading "Unused forcedType" INFO log by creating few tables with different kind of columns, but failed, look like it's not that easy..

There isn't any redundant forcedType configuration in our work project either, but a complex and old database on which we used liquibase to generate a legacy changelog.xml years ago to start using liquibase.

If you think of any hints, I could give it another try.

About H2, thank you for noticing, I didn't check the issue today. For H2 future upgrade, the dependency is managed by Spring Boot, I hope it won't break our build some day.

Anyway I am pretty happy that it works! It should avoid us some issues we had because we used the same database for our development environment and our production builds.
Mostly when we forgot to rollback the database schema used and there has been some new columns in development which shouldn't go to production.

Thank you again Lukas!

[Lukas Eder]

Hi Clément,

Great to hear your tests are running!

On Tue, Sep 15, 2020 at 5:50 PM Clément Allain <dalc...@gmail.com> wrote:

Thanks for your answers, it now builds, runs and passes all our integrations tests!

I tried to reproduce the misleading "Unused forcedType" INFO log by creating few tables with different kind of columns, but failed, look like it's not that easy..

There isn't any redundant forcedType configuration in our work project either, but a complex and old database on which we used liquibase to generate a legacy changelog.xml years ago to start using liquibase.

If you think of any hints, I could give it another try.

Well, if you have time to use a debugger on this, you could place some breakpoints, e.g. on AbstractDatabase.markUsed(ForcedType):

https://github.com/jOOQ/jOOQ/blob/version-3.13.4/jOOQ-meta/src/main/java/org/jooq/meta/AbstractDatabase.java#L1334

And AbstractDatabase.getConfiguredForcedType(Definition definition, DataTypeDefinition definedType)

https://github.com/jOOQ/jOOQ/blob/version-3.13.4/jOOQ-meta/src/main/java/org/jooq/meta/AbstractDatabase.java#L1690

Both in jOOQ-meta. You can debug the code generator using mvnDebug.

 

About H2, thank you for noticing, I didn't check the issue today. For H2 future upgrade, the dependency is managed by Spring Boot, I hope it won't break our build some day.

I wouldn't bet on it. H2 2.0 seems to be quite backwards incompatible in many subtle ways. We'll try to get a release out on our side as quickly as possible once they release H2 2.0. Until then, I think it's best to postpone this upgrade for critical projects.