I'm looking for the "best" way to integrate jOOQ class generation in gradle build scripts. I would prefer not having to have an up-and-running database for the target system, since that defeats "check out and build" scenarios. I would also prefer not checking in the generated classes, but rather generating them on the fly.
I do use liquibase with a SQL/DDL script, but this is not and should not be limited to SQL/DDL (liquibase supports XML and other variants) or liquibase itself (flyway offers similar features, especially when using SQL/DDL scripts). It is fairly easy using a H2 database: - use liquibase gradle plugin to set up the schema in a file system based H2 database underneath $builddir - use Etienne Studer's gradle plugin to generate the classes, with the jooq plugin depending on the update task of the liquibase plugin. This works just fine. Another approach is using jOOQ's own DDLDatabase, which allows using an on-the-fly in-memory H2 database, being fed an SQL script, which could be the very same liquibase script (assuming it is SQL and not XML or other). This eliminates liquibase from the above approach. Works fine, except for https://github.com/jOOQ/jOOQ/issues/8386 which I actually did hit with my SQL/DDL script. But there is another problem: while H2 is a fine database, it does not allow everything. In production, I'm going to use PostgreSQL. Including some stored procedures and triggers. And these are, obviously, part of the SQL/DDL script. Which H2 doesn't understand. So I would like to use PostgreSQL for the jOOQ generator. But still without actually installing a local PostgreSQL instance. PostgreSQL doesn't have an in-memory-option like H2. But there is testcontainers, which I'm using for my DAO unittests as well. It requires docker to be installed locally, but that is fine. Less "special" than PostgreSQL. Now my approach would be similar to the above liquibase and jooq gradle plugin, but using a testcontainers based PostgreSQL database. Unfortunately, this doesn't work, because Etienne Studer's plugin does, for valid reasons, start up a separate JVM for the jOOQ code generator. Which causes the PostgreSQL container not to be found by the code generator. Two possible approaches: - use a separately written Java program that - starts testcontainers for PostgreSQL - lets liquibase do its job - lets jOOQ do its job - or use a variation of DDLDatabase which basically does the same. I chose the second approach. Right now it is working "just fine", however the code still requires some improvement. Other than the DDLDatabase version it does not use the jOOQ internal SQL/DDL parser, but delegates that job to liquibase (I propably could have used flyway as well). Improvements include: - do not use the DDLDatabase way to scan directories for the SQL scripts (when using liquibase with more than one file, you'd usually have a separate XML file using includes of other files, which can be SQL/DDL files as well, and flyway basically does the same thing as well). - allow more variations - right now this is fixed to PostgreSQL, but it could be done with every database supported by testcontainers, liquibase/flyway and jOOQ. Using this approach has the definite advantage that the SQL/DDL script can use every feature of the target database (not limited to H2's feature set). Using the real target database solves another issue, especially with the combination of H2 and PostgreSQL: H2 does everything in uppercase, PostgreSQL in lowercase, jOOQ uses quoted identifiers. This doesn't mix well. While this could be solved in various ways (quotes in the DDL script - but what about the "public" vs. "PUBLIC" schema name?, or using RenderNameStyle.AS_IS), but just using "the real thing" eliminates this altogether. I could provide (after cleanup) the code I derived from DDLDatabase, but according to the jOOQ contribution rules I'm asking first for opinions, ideas and other suggestions. The code obviously depends on liquibase, flyway and testcontainers, but that could be made an optional dependency. Uli -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
