JOOQ code generation with Liquibase, reserved keyword? (error with "value" column name)-CodePudding

I have a table defined in a liquibase script with a column named "value":

- column:
    name: value
    type: VARCHAR
    constraints:
      - nullable: false

dbms is postgresql

When running the JOOQ codegen with the maven plugin, it runs the liquibase script and I get the following error:

Syntax error in SQL statement "CREATE TABLE PUBLIC.TABLE_NAME (ID BIGINT AUTO_INCREMENT NOT NULL, ... , VALUE[*] VARCHAR NOT NULL)"; expected "identifier";

If I change the column name from "value" to anything else, it works. With JOOQ up to version 3.15, it works.

Any clue on how to handle this? I'd rather not change the name, I have multiple tables with a column named "value" so it's a quite big refactor, and naming wise I feel it's the most appropriate name for what it represents.

Solution

This is already fixed in the newer versions of liquibase, so you can manually specify which LB version to use in the jOOQ codegen:

<plugin>
        <groupId>org.jooq</groupId>
        <artifactId>jooq-codegen-maven</artifactId>
        <version>${jooq.version}</version>
        <dependencies>
          <dependency>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-core</artifactId>
            <version>${liquibase.version}</version>
          </dependency>
        ...
</plugin>

CodePudding user response：

Why this happens

The LiquibaseDatabase in jOOQ 3.16's code generation configuration works by simulating your migration against an in-memory H2 database, which, starting from H2 2.0 has incompatibly declared VALUE a keyword, which can no longer be used as an identifier without quoting.

Workaround in Liquibase

So, your workaround could be to quote all objects (or reserved words, if Liquibase is up to date with H2's latest changes): https://docs.liquibase.com/parameters/object-quoting-strategy.html

E.g.

databaseChangeLog:
    -  object-quoting-strategy: QUOTE_ALL_OBJECTS

However, this means that you should make sure to use only lower case identifiers in your Liquibase configuration, as to not accidentally create case sensitive identifiers in your PostgreSQL database

Upgrading Liquibase

I can't reproduce this with the latest versions of Liquibase. It seems they have fixed this and now support H2 2.x correctly

A future fix in jOOQ

jOOQ should fix this on the jOOQ side. Eventually, H2 will be removed from the equation (at least it will be possible to opt out of using it), and jOOQ will interpret the DDL generated by Liquibase directly in order to generate your code. The relevant feature request is: https://github.com/jOOQ/jOOQ/issues/7034