Support for other databases similar to DDLDatabase (using testcontainers and liquibase/flyway)

[Ulrich Grepel]

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

[Marshall Pierce]

This sounds like a good use case for docker-compose.

I have a demo project that shows one way to do it with docker-compose
and Gradle: https://bitbucket.org/marshallpierce/ktor-demo/src/master/

- clone the repo
- docker-compose up -d
- ./gradlew check

A postgres container is started, migrations will be applied, jooq code
gen run, etc. Tests have their own database (running in the same
postgresql container) that also has migrations applied. Feel free to
copy anything that's useful to you.

-Marshall

On 4/9/19 4:31 AM, Ulrich Grepel wrote:
> 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
> o starts testcontainers for PostgreSQL
> o lets liquibase do its job
> o 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 jooq-user+...@googlegroups.com
> <mailto:jooq-user+...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Hi Ulrich,

Thank you very much for your message and for sharing so many details about your planned process. I will comment inline

On Tue, Apr 9, 2019 at 12:31 PM Ulrich Grepel <u...@grepel.de> wrote:

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.

I think this is a very good approach in your case, since you want to profit from the full power of e.g. liquibase. While it would be possible, in principle, to emulate liquibase somewhere inside of the jOOQ code generation process, why not just use liquibase in the first place. Of course, using H2 database here will limit you to the least common denominator of H2 and whatever you're using in production. Other approaches you've mentioned suffer from the same limitation.

 

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.

Yes, from the code generation approach, but not from your migration. You'll still need liquibase eventually, and thus limit your liquibase integration. The DDLDatabase has been designed to work with migration tools like Flyway.

 

Works fine, except for https://github.com/jOOQ/jOOQ/issues/8386 which I actually did hit with my SQL/DDL script.

Indeed, due to how we're currently maintaining the parser, we cannot backport the many changes we're making to it to 3.11.x, so a lot of issues have been fixed already, but will be available only in jOOQ 3.12.

 

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.

In some distant future, we'll be able to parse a large part of those scripts. We don't have to be able to parse the procedure body entirely, as long as we can parse the signature, we can create dummy procedures in H2 - to some extent. Also, in the future, the DDLDatabase will simulate the DDL statements rather than executing them on H2, which will further remove some of the limitations. 

But we're not there yet.

 

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.

This has already been solved in jOOQ 3.12, but again, will not be backported to 3.11.x.

 

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.

Thanks for doing that :-) Well, you can always share your derived code here, if you think there's something worth discussing, or if you think others could profit from it. We're currently unlikely to merge any PR related to such an approach, as it will be difficult to maintain at this moment.

Having said so, in the long run, I would definitely favour a solution that uses a real PostgreSQL database for code generation. No tool in your tool chain should prevent you from getting the most out of your RDBMS. The DDLDatabase is a quick win for relatively simple scenarios, but it stops working at some point.

I also approve of the docker compose approach shared by Marshall, which works around the limitations imposed by Testcontainers.

Hope this helps,
Lukas

[Ulrich Grepel]

Hi Lukas, hi Marshall,

answering both mails in one go, since Lukas commented on Marshall's mail
as well.


Nice to hear that 3.12 will solve some more issues. I understand that
backporting is not an option. Any idea on the general timeframe of 3.12
availability? (days, weeks, months, years... a "logarithmic" estimation
like that would be sufficient for now).

(While DDLDatabase is designed with flyway in mind, it would basically
work with liquibase scripts as well - as long as the liquibase scripts
are plain SQL scripts with comments controlling what liquibase does.
This is now my personal favorite operating mode of liquibase, no need
for XML or any of the other variants. Looking at liquibase-with-SQL
there's actually no big difference between liquibase and flyway.)

For liquibase-with-H2 in gradle there's no need for more code, this
works out of the box using the well known liquibase and jOOQ plugins. No
first hand experience with flyway and its gradle plugin, but that should
be the same. liquibase-with-PostgreSQL or alternatively
flyway-with-PostgreSQL requires some PostgreSQL database. Same with
other databases. Testcontainers only suffers from the
separate-JVM-problem, i.e. you'll end up with two separate
testcontainers if you're trying the same approach as with H2. (With H2
you cannot use a pure in-memory database for the same reason.)

The approach I went right now is as I said a variation of DDLDatabase,
using testcontainers-PostgreSQL and liquibase (flyway would be
possible), not using jOOQs DDL parser, not using H2. So basically there
is no limit. What limits with testcontainers do you mean? (other than
the one with separate JVMs which is what the DDLDatabase-variant
eliminates.)

Looking at Marshall's approach using docker-compose I see a longer
living PostgreSQL. Docker[-Compose], as usual, eliminates the installing
hassles. But the database lifecycle is manually controlled. And the
gradle scripts used by Marshall actually use a fixed port for the
PostgreSQL database, which could be problematic in Jenkins environments
where more than just one build might be running on a single machine,
rendering such a fixed port as "already in use". The testcontainers
approach maps this to an ephemeral port instead. This could maybe be
changed in the docker-compose approach, but there's more than one place
where such a dynamic port number must be injected into, and there won't
be an easy to get source for the port number either.


I'll clean up my code and post it here.

Uli

[Lukas Eder]

Ulrich,

On Tue, Apr 9, 2019 at 2:38 PM Ulrich Grepel <u...@grepel.de> wrote:

Nice to hear that 3.12 will solve some more issues. I understand that
backporting is not an option. Any idea on the general timeframe of 3.12
availability? (days, weeks, months, years... a "logarithmic" estimation
like that would be sufficient for now).

Months, in that case :) But hopefully less than two.

 

(While DDLDatabase is designed with flyway in mind, it would basically
work with liquibase scripts as well - as long as the liquibase scripts
are plain SQL scripts with comments controlling what liquibase does.

Yes, you're probably right.

 

For liquibase-with-H2 in gradle there's no need for more code, this
works out of the box using the well known liquibase and jOOQ plugins. No
first hand experience with flyway and its gradle plugin, but that should
be the same. liquibase-with-PostgreSQL or alternatively
flyway-with-PostgreSQL requires some PostgreSQL database. Same with
other databases. Testcontainers only suffers from the
separate-JVM-problem, i.e. you'll end up with two separate
testcontainers if you're trying the same approach as with H2. (With H2
you cannot use a pure in-memory database for the same reason.)

The approach I went right now is as I said a variation of DDLDatabase,
using testcontainers-PostgreSQL and liquibase (flyway would be
possible), not using jOOQs DDL parser, not using H2. So basically there
is no limit. What limits with testcontainers do you mean? (other than
the one with separate JVMs which is what the DDLDatabase-variant
eliminates.)

Not all RDBMS are currently supported by testcontainers. I think I remembered you saying that additional RDBMS should be supported in the future.

[Ulrich Grepel]

Lukas,

Am Dienstag, 9. April 2019 16:22:26 UTC+2 schrieb Lukas Eder:

Months, in that case :) But hopefully less than two.

good! Thanks!

 

Not all RDBMS are currently supported by testcontainers. I think I remembered you saying that additional RDBMS should be supported in the future.

Ah, ok, that's another kind of limitation of course.

I said that my current approach is using PostgreSQL via testcontainers, but that it is not limited to PostgreSQL. I'm not going to add additional database support to testcontainers itself, so at least myself I would be limited to what testcontainers offers. Which is, limiting this to SQL and ignoring NoSQL:

• MySQL
• MariaDB
• Postgres
• Oracle-XE
• MS SQL Server

All of these are supported by jOOQ as well (at least by the commercial version), and I think this is a fairly important subset. So at least a lot of cases could be solved by that approach, with possibly more if testcontainers adds support.

Uli

[Ulrich Grepel]

Ok, here's the promised source of my approach (using just a suggested package name):

I highlighted the relevant parts changed from the original DDLDatabase, ignoring cosmetic changes.

package org.jooq.meta.extensions.ddl.postgresql;

import liquibase.Contexts;
import liquibase.LabelExpression;
import liquibase.Liquibase;
import liquibase.database.Database;
import liquibase.database.DatabaseFactory;
import liquibase.database.jvm.JdbcConnection;
import liquibase.exception.LiquibaseException;
import liquibase.resource.AbstractResourceAccessor;
import org.jooq.DSLContext;
import org.jooq.exception.DataAccessException;
import org.jooq.impl.DSL;
import org.jooq.impl.ParserException;
import org.jooq.meta.extensions.tools.FileComparator;
import org.jooq.meta.postgres.PostgresDatabase;
import org.jooq.tools.JooqLogger;
import org.jooq.tools.jdbc.JDBCUtils;
import org.testcontainers.containers.PostgreSQLContainer;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Scanner;
import java.util.Set;
import java.util.regex.Pattern;

import static org.jooq.tools.StringUtils.isBlank;

/**
 * The PostgreSQL DDL database
 * <p>
 * This meta data source uses testcontainers to start up a real PostgreSQL database
 * (requiring a running docker installation to do so), then uses Liquibase to
 * run a set of SQL/DDL scripts, then reverse engineering the outcome.
 * <p>
 * The SQL scripts are located in the <code>scripts</code> scripts property
 * available from {@link #getProperties()}.
 * <p>
 * The PostgreSQL version can be controlled with the <code>postgresversion</code>
 * postgresversion property available from [@Link #getProperties()}.
 * <p>
 * Derived from Lukas Eder's DDLDatabase
 *
 * @author Ulrich Grepel
 */
public class PostgresDDLDatabase extends PostgresDatabase {

    private static final JooqLogger log    = JooqLogger.getLogger(PostgresDDLDatabase.class);
    private static final Pattern    P_NAME = Pattern.compile("(?s:.*?\"([^\"]*)\".*)");

    private PostgreSQLContainer     postgreSQLContainer;
    private String                  encoding;
    private Connection              connection;
    private DSLContext              ctx;
    private Comparator<File>        fileComparator;

    @Override
    protected DSLContext create0() {
        if (connection == null) {
            String scripts = getProperties().getProperty("scripts");
            encoding = getProperties().getProperty("encoding", "UTF-8");
            String sort = getProperties().getProperty("sort", "semantic").toLowerCase();
            String postgresversion = getProperties().getProperty("postgresversion", "11.2");

            if ("alphanumeric".equals(sort))
                fileComparator = new Comparator<File>() {
                    @Override
                    public int compare(File o1, File o2) {
                        return o1.compareTo(o2);
                    }
                };
            else if ("none".equals(sort))
                fileComparator = null;
            else
                fileComparator = FileComparator.INSTANCE;

            if (isBlank(scripts)) {
                scripts = "";
                log.warn("No scripts defined", "It is recommended that you provide an explicit script directory to scan");
            }

            try {
                postgreSQLContainer = new PostgreSQLContainer(PostgreSQLContainer.IMAGE + ":" + postgresversion);
                postgreSQLContainer.start();

                connection = postgreSQLContainer.createConnection("");

                ctx = DSL.using(connection);

                // TODO: is this requried with Testcontainers/PostgreSQL/Liquibase?
                // [#7771] [#8011] Ignore all parsed storage clauses when executing the statements
                ctx.data("org.jooq.meta.extensions.ddl.ignore-storage-clauses", true);

                InputStream in = null;
                boolean loaded = false;
                in = PostgresDDLDatabase.class.getResourceAsStream(scripts);
                if (in != null) {
                    log.info("Reading from classpath: " + scripts);
                    load(in, scripts);
                    loaded = true;
                }
                else {
                    File file = new File(scripts);

                    if (file.exists()) {
                        load(file, null);
                        loaded = true;
                    }
                    else if (scripts.contains("*") || scripts.contains("?")) {
                        file = new File(scripts.replaceAll("[*?].*", ""));

                        Pattern pattern = Pattern.compile("^.*?"
                           + scripts
                            .replace("\\", "/")
                            .replace(".", "\\.")
                            .replace("?", ".")
                            .replace("**", ".+?")
                            .replace("*", "[^/]*")
                           + "$"
                        );

                        load(file, pattern);
                        loaded = true;
                    }
                }

                if (!loaded)
                    log.error("Could not find script source : " + scripts);
            }
            catch (ParserException e) {
                log.error("An exception occurred while parsing script source : " + scripts + ". Please report this error to https://github.com/jOOQ/jOOQ/issues/new", e);
                throw e;
            }
            catch (Exception e) {
                throw new DataAccessException("Error while exporting schema", e);
            }
        }

        return ctx;
    }

    private void load(File file, Pattern pattern) throws IOException {
        if (file.isFile()) {
            if (pattern == null || pattern.matcher(file.getCanonicalPath().replace("\\", "/")).matches()) {
                log.info("Reading from: " + file + " [*]");
                load(new FileInputStream(file), file.getName());
            }
        }
        else if (file.isDirectory()) {
            log.info("Reading from: " + file);

            File[] files = file.listFiles();

            if (files != null) {
                if (fileComparator != null)
                    Arrays.sort(files, fileComparator);

                for (File f : files)
                    load(f, pattern);
            }
        }

        // [#7767] Backtrack to a parent directory in case the current file pattern doesn't match yet
        else if (!file.exists() && file.getParentFile() != null) {
            load(file.getParentFile(), pattern);
        }
    }

    private static class StreamResourceAccessor extends AbstractResourceAccessor {

        /*
         * Liquibase tries to re-use the passed ResourceAccessor, which won't work with just
         * passing one single given InputStream as the search result. That's why we're buffering
         * the stream.
         */
        private final String sql;

        public StreamResourceAccessor(String sql) {
            this.sql = sql;
        }

        @Override
        public Set<InputStream> getResourcesAsStream(String name) {

            return Set.of(new ByteArrayInputStream(sql.getBytes(StandardCharsets.UTF_8)));
        }

        @Override
        public Set<String> list(String arg0, String arg1, boolean arg2, boolean arg3, boolean arg4) {
            return null;
        }

        @Override
        public ClassLoader toClassLoader() {
            return null;
        }

    }

    private void load(InputStream in, String name) {
        try {
            Scanner s = new Scanner(in, encoding).useDelimiter("\\A");
            String sql = s.hasNext() ? s.next() : "";

            Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
            Liquibase liquibase = new Liquibase(name, new StreamResourceAccessor(sql), database);

            liquibase.update(new Contexts(), new LabelExpression());
        } catch (LiquibaseException e) {
            log.error("liquibase update failed", e);
            throw new DataAccessException("Error while executing liquibase", e);
        }
        finally {
            if (in != null)
                try {
                    in.close();
                }
                catch (Exception ignore) {}
        }
    }

    @Override
    public void close() {

        JDBCUtils.safeClose(connection);
        connection = null;
        if (postgreSQLContainer != null) {
            postgreSQLContainer.close();
        }
        ctx = null;
        super.close();
    }
}

And a bit of gradle script:

    jooq {
        version = '3.11.10'
        edition = 'OSS'
        zfdb(sourceSets.main) {
            jdbc {
                driver = 'org.testcontainers.jdbc.ContainerDatabaseDriver'
                url = ''    // required dummy entry
            }
            generator {
                name = 'org.jooq.codegen.DefaultGenerator'
                strategy {
                    name = 'org.jooq.codegen.DefaultGeneratorStrategy'
                }
                database {
                    name = 'org.jooq.meta.extensions.ddl.postgresql.PostgresDDLDatabase'
                    inputSchema = 'public'
                    excludes = 'DATABASECHANGELOG|DATABASECHANGELOGLOCK'
                    properties {
                        property {
                            key = 'scripts'
                            value = 'src/main/resources/db/dbmigrate.sql'
                        }
                        property {
                            key = 'sort'
                            value = 'semantic'
                        }
                        property {
                            key = 'postgresversion'
                            value = '11.2'
                        }
                    }
                }
                generate {
                    relations = true
                    deprecated = false
                    records = true
                    pojos = true
                    fluentSetters = true
                    daos = true
                    javaTimeTypes = true
                }
                target {
                    packageName = 'de.whatever.project.jooq'
                }
            }
        }
    }



sort=semantic is default, so it could be skipped. And of course you'll have to do a bit more of settings finetuning to suit your exact needs...

What I haven't shown is the maven subproject that actually compiles the PostgresDDLDatabase class, but I'm sure you'll get the idea...

Uli

[Ulrich Grepel]

Am Dienstag, 9. April 2019 17:21:12 UTC+2 schrieb Ulrich Grepel:

I highlighted the relevant parts changed from the original DDLDatabase, ignoring cosmetic changes.

...and forgot one:

[Lukas Eder]

Hi Uli

On Tue, Apr 9, 2019 at 5:04 PM Ulrich Grepel <u...@grepel.de> wrote:

I said that my current approach is using PostgreSQL via testcontainers, but that it is not limited to PostgreSQL. I'm not going to add additional database support to testcontainers itself, so at least myself I would be limited to what testcontainers offers. Which is, limiting this to SQL and ignoring NoSQL:

• MySQL
• MariaDB
• Postgres
• Oracle-XE
• MS SQL Server

All of these are supported by jOOQ as well (at least by the commercial version), and I think this is a fairly important subset. So at least a lot of cases could be solved by that approach, with possibly more if testcontainers adds support.

Yes, jOOQ supports them, and all of them should run on docker, too.

[Lukas Eder]

Thanks a lot for sharing! This looks quite actionable indeed. I probably overestimated the integration efforts in the past, perhaps such an extension can be supported officially. We'd have to abstract over the migration utility (Liquibase, Flyway) to make sure they are correctly pluggable, and also over the database abstraction (Testcontainers, so far), but then this could be an out of the box feature. The jOOQ-meta-extensions module is allowed to have any number of third party dependencies. So, I'll definitely think about reviving this feature request:

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

Thanks a lot for sharing! 

--

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.

[ben....@gmail.com]

For what it's worth, in my backlog is to try https://github.com/ayedo/jooq-modelator which automates a docker-based solution for Gradle.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.