How to scope a MySQL JOOQ rename table query to the same database?

user_1149970 0
Viewed 219

I have a scala application that manages multiple MySQL database schemas, which includes modifying (adding, renaming, etc.) tables. The commands are issued over a connection pool that connects to a generic management database in the database server.

Because the application is designed to be cross-database, I use JOOQ to render SQL queries (execution is done via a separate JDBC module).

I experience issues with JOOQs alterTable(...).renameTo(...) DSL - consider the following example:

We have a table "TestTable" in database "TestDatabase". Let's say I want to rename that table simply to "Foo", keeping it in "TestDatabase".

This code: 

...
val context = DSL.using(SQLDialect.MYSQL_5_7)
val query = context
              .alterTable(table(name("TestDatabase", "TestDatabase")))
              .renameTo(name("TestDatabase", "Foo"))
...

Generates: ALTER TABLE `TestDatabase`.`TestTable` RENAME TO `Foo` However, since the connection pool I'm using is connected to my management database, it just renames the table to "Foo" and moves it to my management database. I would have expected the SQL to be: ALTER TABLE `TestDatabase`.`TestTable` RENAME TO `TestDatabase`.`Foo`. I tried a variety of alternatives to invoke the .renameTo method and convice it to use the fully qualified name, to no avail:

  • .renameTo(table(name(...) -> same behaviour.
  • .renameTo("`TestDatabase`.`Foo`") -> Escapes the name with backticks, treats it as one name instead of a qualified name.

I'm wondering if I'm missing something, if this is intended behaviour, or maybe even a bug or design shortcoming of JOOQ.

Is there a way to rename the table using fully qualified names?

Thank you!

jooq scala mysql
1 Answers

That's a bug in jOOQ: https://github.com/jOOQ/jOOQ/issues/8042

Your workaround is close. This doesn't work:

.renameTo("`TestDatabase`.`Foo`")

As you've noticed, behind the scenes, the DSL.name() API is used to wrap the target name, because the renameTo() method doesn't implement the plain SQL templating API. You can, however, explicitly use plain SQL templating by writing as a workaround:

.renameTo(table("`TestDatabase`.`Foo`"))
user_5217990
Related