SQLite "Cannot parse or interpret sql for table" error with both unique columns and foreign key constraints

[vptp]

Expected behavior

Code generation maven plugin to run successfully without logging any errors.
Generate unique keys for all UNIQUE columns.
Generate DAO.fetchOneByX functions for all UNIQUE columns.

Actual behavior

Error logged during code generation:

[INFO] Cannot parse or interpret sql for table test2: CREATE TABLE test2
...
org.jooq.exception.DataDefinitionException: TableImpl does not exist: test

Unique keys missing for some tables.
Test2Dao.fetchOneByCd function generated.

Steps to reproduce the problem

MCVE jOOQ-mcve-kotlin-sqlite directory

The problem appears to require the combination of columns with UNIQUE constraints and columns with FOREIGN KEY constraints in the same table.
It appears that while processing the unique keys for each table, parsing the foreign keys in the SQL causes an exception which results in aborting processing unique keys. Hence the DAO.fetchOneByX method is not generated.

jOOQ 3.14.16 generates the DAO.fetchOneByX as expected. jOOQ versions 3.15.0 to 3.19.8 omit the DAO.fetchOneByX in some cases.

jOOQ Version

jOOQ 3.19.8

Database product and version

SQLite 3.40.1.0

Java Version

openjdk version "17.0.10" 2024-01-16

JDBC / R2DBC driver name and version (include name if unofficial driver)

org.xerial:sqlite-jdbc:3.40.1.0

[lukaseder]

Thanks a lot for your report. I can reproduce the problem. Will look into this right away.

[lukaseder]

I see, we're interpreting meta DDL SQL table by table, with eager foreign key resolution. But the tables are ordered alphabetically, not in their key order (which can have cycles), so we should delay the foreign key checks, e.g. using Settings.interpreterDelayForeignKeyDeclarations

• Add Settings.interpreterDelayForeignKeyDeclarations #9508

[lukaseder]

It doesn't happen without the unique constraint, because that particular code path that interprets all the DDL is only invoked when looking up unique constraints.

[lukaseder]

Fixed in versions:

• 3.20.0
• 3.19.9 (SQLite "Cannot parse or interpret sql for table" error with both unique columns and foreign key constraints - Merge [#16675] #16676)
• 3.18.16 (SQLite "Cannot parse or interpret sql for table" error with both unique columns and foreign key constraints - Merge [#16675] #16677)
• 3.17.25 (SQLite "Cannot parse or interpret sql for table" error with both unique columns and foreign key constraints - Merge [#16675] #16678)

[vptp]

Thanks for looking at this so quickly