2

I have a jOOQ codegen configuration failing because of a function not known by the framework. The configuration is using the org.jooq.meta.extensions.ddl.DDLDatabase, aiming to generate the code from the Flyway migration scripts. The error is as below:

Error running jOOQ code generation tool: Error while exporting schema: SQL [create table CUSTOMER (ID uuid not null default UUID_GENERATE_V4(), NAME varchar(255) not null, primary key (ID))]; Function "UUID_GENERATE_V4" not found;

By checking the documentation, I see there is a parseUnknownFunctions parser property that, as far as I understood, is supposed to disable this behaviour when set to IGNORE. This doesn't seem to have any effect.

I also understand there is a workaround to make jOOQ ignore parts of the SQL file by adding comments on it. This is not possible in my case, as I am not the owner of the SQL files.

Is there any other option I can try?

Below are the script causing the error and the jOOQ config in the pom.xml:

create table customer (
    id   uuid         not null default uuid_generate_v4(),
    name varchar(255) not null,
    primary key (id)
);

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <executions>
    <execution>
      <id>generate-jooq-sources</id>
      <phase>generate-sources</phase>
      <goals>
        <goal>generate</goal>
      </goals>
      <configuration>
        <generator>
          <database>
            <name>org.jooq.meta.extensions.ddl.DDLDatabase</name>
            <inputSchema>PUBLIC</inputSchema>
            <outputSchemaToDefault>true</outputSchemaToDefault>
            <outputCatalogToDefault>true</outputCatalogToDefault>
            <properties>
              <property>
                <key>sort</key>
                <value>semantic</value>
              </property>
              <property>
                <key>scripts</key>
                <value>src/main/resources/db/migration/*</value>
              </property>
              <property>
                <key>parseUnknownFunctions</key>
                <value>IGNORE</value>
              </property>
            </properties>
          </database>
          <target>
            <clean>true</clean>
            <packageName>com.product</packageName>
            <directory>${project.generated-sources}/jooq/src/main/java</directory>
          </target>
        </generator>
      </configuration>
    </execution>
  </executions>
  <dependencies>
    <dependency>
      <groupId>org.jooq</groupId>
      <artifactId>jooq-meta-extensions</artifactId>
      <version>${jooq.version}</version>
    </dependency>
  </dependencies>
</plugin>
renke
  • 1,180
  • 2
  • 12
  • 27

1 Answers1

0

If you look at the full stack trace, you'll see that the error isn't thrown by jOOQ but by H2:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "UUID_GENERATE_V4" not found; SQL statement:
create table CUSTOMER (ID uuid not null default UUID_GENERATE_V4(), NAME varchar(255) not null, primary key (ID)) [90022-200]
    at org.h2.message.DbException.getJdbcSQLException (DbException.java:576)
    at org.h2.message.DbException.getJdbcSQLException (DbException.java:429)
    at org.h2.message.DbException.get (DbException.java:205)
    at org.h2.message.DbException.get (DbException.java:181)
    at org.h2.command.Parser.readJavaFunction (Parser.java:3565)
    at org.h2.command.Parser.readFunction (Parser.java:3770)
    at org.h2.command.Parser.readTerm (Parser.java:4305)
    at org.h2.command.Parser.readFactor (Parser.java:3343)
    at org.h2.command.Parser.readSum (Parser.java:3330)
    at org.h2.command.Parser.readConcat (Parser.java:3305)
    at org.h2.command.Parser.readCondition (Parser.java:3108)
    at org.h2.command.Parser.readExpression (Parser.java:3059)
    at org.h2.command.Parser.parseColumnForTable (Parser.java:5727)
    at org.h2.command.Parser.parseTableColumnDefinition (Parser.java:8442)
    at org.h2.command.Parser.parseCreateTable (Parser.java:8379)
    at org.h2.command.Parser.parseCreate (Parser.java:6276)
    at org.h2.command.Parser.parsePrepared (Parser.java:903)
    at org.h2.command.Parser.parse (Parser.java:843)
    at org.h2.command.Parser.parse (Parser.java:815)
    at org.h2.command.Parser.prepareCommand (Parser.java:738)
    at org.h2.engine.Session.prepareLocal (Session.java:657)
    at org.h2.engine.Session.prepareCommand (Session.java:595)
    at org.h2.jdbc.JdbcConnection.prepareCommand (JdbcConnection.java:1235)
    at org.h2.jdbc.JdbcStatement.executeInternal (JdbcStatement.java:212)
    at org.h2.jdbc.JdbcStatement.execute (JdbcStatement.java:201)
    at org.jooq.tools.jdbc.DefaultStatement.execute (DefaultStatement.java:102)
    at org.jooq.impl.SettingsEnabledPreparedStatement.execute (SettingsEnabledPreparedStatement.java:227)
    at org.jooq.impl.AbstractQuery.execute (AbstractQuery.java:411)
    at org.jooq.impl.AbstractQuery.execute (AbstractQuery.java:332)
    at org.jooq.meta.extensions.ddl.DDLDatabase.load (DDLDatabase.java:183)
    at org.jooq.meta.extensions.ddl.DDLDatabase.lambda$0 (DDLDatabase.java:156)

The current (jOOQ 3.15) implementation of the DDLDatabase translates your DDL into the H2 dialect and runs it in an H2 in memory database to simulate your DDL. H2 doesn't support this function. Future jOOQ versions will interpret the DDL without using H2 (see https://github.com/jOOQ/jOOQ/issues/7034), but this is currently not the case.

As a workaround, you can use the jOOQ parser ignore comment syntax that is documented here: https://www.jooq.org/doc/latest/manual/code-generation/codegen-ddl/

Specifically:

create table customer (
    id   uuid         not null 
    /* [jooq ignore start] */ default uuid_generate_v4() /* [jooq ignore stop] */,
    name varchar(255) not null,
    primary key (id)
);

Alternatively (much better!) use one of the testcontainers based approaches documented here: https://github.com/jOOQ/jOOQ/issues/6551 or in this example: https://github.com/jOOQ/jOOQ/tree/main/jOOQ-examples/jOOQ-testcontainers-example

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509