Correct way to handle attached SQLite databases with jOOQ

Question

I'm trying to do a join on a table in an attached SQLite database (https://sqlite.org/lang_attach.html) and I'm having trouble getting jOOQ to format the field names correctly. The query that jOOQ is generating is quoting the name of the attached database, but this causes the query to fail:

select "attached.MemberTransactions".time, "attached.MemberTransactions".tableName, "attached.MemberTransactions".id
from MemberTransactions as curr
  full outer join attached.MemberTransactions
    on (curr.time, curr.tableName, curr.id) = ("attached.MemberTransactions".time, "attached.MemberTransactions".tableName, "attached.MemberTransactions".id)
where curr.id is null
order by "attached.MemberTransactions".time asc

Removing the quotes fixes the query, i.e.:

select attached.MemberTransactions.time, attached.MemberTransactions.tableName, attached.MemberTransactions.id
from MemberTransactions as curr
  full outer join attached.MemberTransactions
    on (curr.time, curr.tableName, curr.id) = (attached.MemberTransactions.time, attached.MemberTransactions.tableName, attached.MemberTransactions.id)
where curr.id is null
order by attached.MemberTransactions.time asc

This is how I'm generating the query:

try(Connection connection = DatabaseConnector.createConnection(baseFileName))
{
  connection.prepareStatement(String.format("ATTACH DATABASE \"%s\" AS %s", attachFileName, ATTACHED_DB_NAME)).execute();
  DSLContext context = DSL.using(connection, SQLDialect.SQLITE);
// ...
String otherTableName = String.format("%s.%s", DatabaseConnector.ATTACHED_DB_NAME, Membertransactions.MEMBERTRANSACTIONS.getName());
Membertransactions otherTable = Membertransactions.MEMBERTRANSACTIONS.as(otherTableName);
Membertransactions currTable = Membertransactions.MEMBERTRANSACTIONS.as("curr");
return context.select(otherTable.TIME, otherTable.TABLENAME, otherTable.ID)
  .from(currTable.fullOuterJoin(otherTableName).on(currTable.eq(otherTable)).asTable())
  .where(currTable.ID.isNull())
  .fetchInto(MembertransactionsRecord.class);

I've tried various workarounds, but I haven't found anything that fixes all of the field names. I can pass raw strings to fullOuterJoin() or from(), but select() can't take raw strings. I need to specifically select otherTable's fields because otherwise, fetchInto() uses the null-valued side of the join; this means I can't just use selectFrom() with a raw string and drop the orderBy() call as a workaround. I can pass an unquoted qualified name from DSL.unquotedName() to the as() call that I'm assigning to otherTable, but then the field names in the query stop being qualified for some reason (i.e. instead of "\"attached.MemberTransactions\".*", I get "MemberTransactions.*").

Is there any way I can force the field names to be inserted into the query as fully-qualified and unquoted? Or is there any official support for the SQLite attach statement that I missed which would handle this case correctly?

Can you please show the SQL query that you would like to get? — Lukas Eder
– Lukas Eder, Commented Sep 5 at 10:07
Regarding: I can pass an unquoted qualified name from DSL.unquotedName() to the as() call that I'm assigning to otherTable, but then the field names in the query stop being qualified for some reason, you could try to create a more minimal example (in a new question) that shows when this happens. I don't think it does, ordinarily. — Lukas Eder
– Lukas Eder, Commented Sep 8 at 5:44

Lukas Eder · Accepted Answer · 2025-09-08 05:51:17Z

The default behaviour of Table.as(String) is to wrap the argument in DSL.name() and that is rendered according to Settings.renderQuotedNames.

Most RDBMS allow for special characters in their identifiers, including the . that you placed in your identifier. There's no reason for jOOQ to assume that you really intended for this period to have any qualifying semantics as opposed to just being a part of the identifier, unless you explicitly tell jOOQ not to quote an identifier using DSL.unquotedName().

I cannot reproduce the issue from your description:

I can pass an unquoted qualified name from DSL.unquotedName() to the as() call that I'm assigning to otherTable, but then the field names in the query stop being qualified for some reason

For any generated table like T_BOOK in an example, the following:

System.out.println(ctx.select(T_BOOK.as(unquotedName("a.b")).ID));

Just prints:

select a.b.`ID`

In any case, it's probably worth supporting attached databases as ordinary schemas in jOOQ's code generator and elsewhere:

https://github.com/jOOQ/jOOQ/issues/19008

Thomas DeSilva · Accepted Answer · 2025-09-05 19:26:14Z

Explicitly creating Fields with unquoted qualified names (as in the answer to this question: Qualifying a temporary table column name in jOOQ) instead of getting the Fields from an aliased Table works.

String otherTableName = String.format("%s.%s", DatabaseConnector.ATTACHED_DB_NAME, Membertransactions.MEMBERTRANSACTIONS.getName());
Membertransactions currTable = Membertransactions.MEMBERTRANSACTIONS.as("curr");
Field<Long> otherTableTime = DSL.field(DSL.unquotedName(DatabaseConnector.ATTACHED_DB_NAME, Membertransactions.MEMBERTRANSACTIONS.getName(), Membertransactions.MEMBERTRANSACTIONS.TIME.getName()), Long.class);
Field<String> otherTableTablename = DSL.field(DSL.unquotedName(DatabaseConnector.ATTACHED_DB_NAME, Membertransactions.MEMBERTRANSACTIONS.getName(), Membertransactions.MEMBERTRANSACTIONS.TABLENAME.getName()), String.class);
Field<String> otherTableId = DSL.field(DSL.unquotedName(DatabaseConnector.ATTACHED_DB_NAME, Membertransactions.MEMBERTRANSACTIONS.getName(), Membertransactions.MEMBERTRANSACTIONS.ID.getName()), String.class);
return context.select(otherTableTime, otherTableTablename, otherTableId)
  .from(currTable.fullOuterJoin(otherTableName)
    .on(currTable.TIME.eq(otherTableTime)
      .and(currTable.TABLENAME.eq(otherTableTablename))
      .and(currTable.ID.eq(otherTableId)))
    .asTable())
  .where(currTable.ID.isNull())
  .fetchInto(MembertransactionsRecord.class);

Collectives™ on Stack Overflow

Correct way to handle attached SQLite databases with jOOQ

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related