Jooq: Add configuration in DDLDatabase matching Settings.interpreterDialect

[lukaseder]

The current DDLDatabase works in many cases where users do not use any vendor-specific features, by translating vendor-specific SQL (that can be parsed by jOOQ) into H2's dialect.

Unfortunately, this means that a couple of features are not available from code generation via DDLDatabase, including:

• MySQL enum types
• MySQL unsigned types
• USE database commands and similar, which put the script into some context
• Much more

The advantage of the current approach is that we can "easily" support incremental migration scripts, i.e. scripts of the type:

create table x (a int);
alter table x add (b int);
alter table x add constraint pk_x primary key (a);

If we were able to interpret the above DDL on a mini database implementation that incrementally modifies a meta model (preferably the jOOQ meta model, or the XML content), we could also support more vendor-specific features.

This has been implemented via a variety of tasks. The remaining things to do here is

• [x] to allow code generator configurations to use the Settings.interpreterDialect flag: https://github.com/jOOQ/jOOQ/issues/9362
• [ ] to switch defaults to our new DDL interpretation.
• [ ] to refactor temporarily duplicated logic
• [ ] prevent regressions of e.g. https://github.com/jOOQ/jOOQ/issues/7202

[lukaseder]

There's an increasing number of people who would like to use the DDLDatabase but run into its H2-specific limitations because of how DDLDatabase is currently implemented. We should definitely prioritise this feature for jOOQ 3.12

[alexanderkoller]

Hi, we are some of the "increasing number of people" who are running into the limitations of DDLDatabase with H2.

Is there any estimate for a release date for jOOQ 3.12?

Alternatively, is there a way to make the DDLDatabase use H2 1.4.199 instead of 1.4.197? This seems to add support for enums.

[knutwannheden]

Is there any estimate for a release date for jOOQ 3.12?

We expect to release version 3.12 by the end of the month or early September.

Alternatively, is there a way to make the DDLDatabase use H2 1.4.199 instead of 1.4.197? This seems to add support for enums.

jOOQ's jooq-meta-extensions Maven module declares a dependency on H2 version 1.4.197. You can however declare a dependency in your own pom.xml against H2 version 1.4.199 and that is what should then. See also the section "Forcing a version" in the Maven documentation: https://cwiki.apache.org/confluence/display/MAVENOLD/Dependency+Mediation+and+Conflict+Resolution.

jOOQ 3.12 will depend on H2 1.4.199.

[alexanderkoller]

Great, thanks!

[lukaseder]

jOOQ's jooq-meta-extensions Maven module declares a dependency on H2 version 1.4.197. You can however declare a dependency in your own pom.xml against H2 version 1.4.199 and that is what should then

Possibly, you'll have to exclude our own transitive dependency explicitly.

[berngp]

@lukaseder are there any plans on making additional implementations of _adapters_ for datasources available? i.e. I assume you will have an H2 _adapter_ to be backward compatible, would you also plan to have additional adapters for let's say Postgres, MySQL, via Docker?

[lukaseder]

@berngp: I'm not quite sure what would be the use-case for this? Do you mean to spin up a throwaway Postgres database in test containers just for the code generator? Then yes, we have a feature request for this on our roadmap: #6551

[lukaseder]

@knutwannheden Does your current implementation solve this problem here? #8824

[knutwannheden]

Depends on your definition of "solve" 😉 We will definitely be able to allow this syntax. But when using H2 dialect DDL, we will in the future probably also want to have the possibility to raise an error, as would be the case when running the script against a real database. But that is for the future.

[lukaseder]

OK, my definition (for now) is: Do our tests cover this case :)

[knutwannheden]

No, not yet. At the moment neither the tests nor the implementation cover very much. But I think most things are in place to allow expanding both quite easily.

[lukaseder]

This turns out to be more difficult to implement than I initially thought. The fact that DDLDatabase will no longer extend AbstractInterpretingDatabase and H2Database, but AbstractMetaDatabase now, means:

• That a few features are not yet available (e.g. foreign keys: https://github.com/jOOQ/jOOQ/issues/3521)
• Case sensitivity works a bit differently
• Data types are reported a bit differently (regression risk, e.g. https://github.com/jOOQ/jOOQ/issues/7202)