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.
On Tuesday, April 9, 2019 at 8:38:46 AM UTC-7, Lukas Eder wrote: > > 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! > > On Tue, Apr 9, 2019 at 5:21 PM Ulrich Grepel <[email protected] <javascript:>> > wrote: > >> 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 >> >> -- >> 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] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > -- 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.
