JOOQ H2(DDLDatabase)代码生成失败,因为KEY是一个保留字。

huangapple go评论61阅读模式
英文:

JOOQ H2 (DDLDatabase) code generaton fails because KEY is a reserved word

问题

在较新版本的H2中,KEY是一个保留字。JOOQ代码生成遵循此文档1失败,因为我们在某些表中将KEY用作列名:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table if not exists FACT_ORDER ([*]KEY varchar not null, ...)"; expected "identifier";
SQL statement:
create table if not exists FACT_ORDER (KEY varchar not null, ...) [42001-214]

重命名列不是一个可行的解决方案。 SQL DDL文件也不能被编辑,因为Liquibase使用相同的文件。

尝试但未成功:
H2有一个配置命令,可以使用SET NON_KEYWORDS KEY命令将KEY视为非保留字(H2文档)。

我尝试将该命令添加到生成器中,但仍然遇到相同的错误:

    val target = new Target();
    target.setPackageName("com.mycompany.codegen.orderdb");
    target.setDirectory(targetPath);
    val generator =
        new Generator()
            .withDatabase(
                new Database()
                    .withName("org.jooq.meta.extensions.ddl.DDLDatabase")
                    .withProperties(
                        new Property().withKey("sql").withValue("SET NON_KEYWORDS KEY;"),
                        new Property().withKey("scripts").withValue(tempDir + "/*.sql"),
                        new Property().withKey("sort").withValue("alphanumeric")))
            .withTarget(target);
    val generate = new Generate();
    generate.setPojos(true);
    generate.setRecords(true);
    generate.setPojosEqualsAndHashCode(true);
    generate.setFluentSetters(true);
    generator.setGenerate(generate);
    GenerationTool.generate(
        new Configuration().withGenerator(generator).withLogging(getLogLevel()));
英文:

In newer versions of H2, KEY is a reserved word. JOOQ code generation following this doc fails because we have KEY as column name in some tables:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table if not exists FACT_ORDER ([*]KEY varchar not null, ...)"; expected "identifier";
SQL statement:
create table if not exists FACT_ORDER (KEY varchar not null, ...) [42001-214]

Renaming the column is not a feasible solution. The SQL DDL file cannot be edited either as the same file is used by Liquibase.

Tried but not working:
H2 has a configuration command to not consider KEY as a reserved word using SET NON_KEYWORDS KEY command (H2 doc).

I tried adding the command to the generator, but still am getting the same error:

    val target = new Target();
    target.setPackageName("com.mycompany.codegen.orderdb");
    target.setDirectory(targetPath);
    val generator =
        new Generator()
            .withDatabase(
                new Database()
                    .withName("org.jooq.meta.extensions.ddl.DDLDatabase")
                    .withProperties(
                        new Property().withKey("sql").withValue("SET NON_KEYWORDS KEY;"),
                        new Property().withKey("scripts").withValue(tempDir + "/*.sql"),
                        new Property().withKey("sort").withValue("alphanumeric")))
            .withTarget(target);
    val generate = new Generate();
    generate.setPojos(true);
    generate.setRecords(true);
    generate.setPojosEqualsAndHashCode(true);
    generate.setFluentSetters(true);
    generator.setGenerate(generate);
    GenerationTool.generate(
        new Configuration().withGenerator(generator).withLogging(getLogLevel()));

答案1

得分: 1

你可以在 DDLDatabase 上指定 defaultNameCase 标志

<!-- 未引用对象的默认名称大小写:

     - as_is: 未引用对象名称保持不变
     - upper: 未引用对象名称转换为大写(大多数数据库)
     - lower: 未引用对象名称转换为小写(例如 PostgreSQL) -->
<property>
  <key>defaultNameCase</key>
  <value>lower</value>
</property>

或者通过编程配置,只需添加:

new Property().withKey("defaultNameCase").withValue("lower")

根据目标关系型数据库选择 lowerupper,取决于您喜欢的大小写。在幕后,DDLDatabase 将转换所有 DDL 为带引号的大小写。一旦标识符被引用,它们将在 DDLDatabase 使用的内存中的 H2 数据库上运行。

当然,您也可以直接在您的 DDL 中引用标识符。

英文:

You can specify the defaultNameCase flag on the DDLDatabase:

&lt;!-- The default name case for unquoted objects:

     - as_is: unquoted object names are kept unquoted
     - upper: unquoted object names are turned into upper case (most databases)
     - lower: unquoted object names are turned into lower case (e.g. PostgreSQL) --&gt;
&lt;property&gt;
  &lt;key&gt;defaultNameCase&lt;/key&gt;
  &lt;value&gt;lower&lt;/value&gt;
&lt;/property&gt;

Or with your programmatic configuration, just add:

new Property().withKey(&quot;defaultNameCase&quot;).withValue(&quot;lower&quot;)

Just pick lower or upper depending on what case you prefer on your target RDBMS. Behind the scenes, the DDLDatabase will transform all DDL to be quoted and lower/upper case. Once the identifiers are quoted, they will work on the in-memory H2 database used by the DDLDatabase.

Of course, you can also quote the identifier in your DDL directly, instead.

huangapple
  • 本文由 发表于 2023年3月9日 14:33:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/75681158.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定