How to scope a MySQL JOOQ rename table query to the same database?
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!
mysql scala jooq
asked Nov 8 '18 at 15:06
DominicDominic
9517
add a comment |
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!
mysql scala jooq
asked Nov 8 '18 at 15:06
DominicDominic
9517
Did you try using raw jdbc connections?
– Mon Calamari
Nov 8 '18 at 15:13
@MonCalamari That works of course, however, this question is specifically about keeping the JOOQ abstraction for cross DB support, or else I'd have to move this code into DB specific code for every DB I want to support.
– Dominic
Nov 8 '18 at 15:17
If I were you I would raise an issue with github.com/jOOQ/jOOQ
– Mon Calamari
Nov 8 '18 at 15:21
@MonCalamari Thanks, I will consider it.
– Dominic
Nov 8 '18 at 15:51
add a comment |
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!
mysql scala jooq
asked Nov 8 '18 at 15:06
DominicDominic
9517
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!
mysql scala jooq
mysql scala jooq
asked Nov 8 '18 at 15:06
DominicDominic
9517
asked Nov 8 '18 at 15:06
DominicDominic
9517
asked Nov 8 '18 at 15:06
DominicDominic
9517
asked Nov 8 '18 at 15:06
DominicDominic
9517
asked Nov 8 '18 at 15:06
DominicDominic
9517
9517
Did you try using raw jdbc connections?
– Mon Calamari
Nov 8 '18 at 15:13
@MonCalamari That works of course, however, this question is specifically about keeping the JOOQ abstraction for cross DB support, or else I'd have to move this code into DB specific code for every DB I want to support.
– Dominic
Nov 8 '18 at 15:17
If I were you I would raise an issue with github.com/jOOQ/jOOQ
– Mon Calamari
Nov 8 '18 at 15:21
@MonCalamari Thanks, I will consider it.
– Dominic
Nov 8 '18 at 15:51
add a comment |
Did you try using raw jdbc connections?
– Mon Calamari
Nov 8 '18 at 15:13
@MonCalamari That works of course, however, this question is specifically about keeping the JOOQ abstraction for cross DB support, or else I'd have to move this code into DB specific code for every DB I want to support.
– Dominic
Nov 8 '18 at 15:17
If I were you I would raise an issue with github.com/jOOQ/jOOQ
– Mon Calamari
Nov 8 '18 at 15:21
@MonCalamari Thanks, I will consider it.
– Dominic
Nov 8 '18 at 15:51
Did you try using raw jdbc connections?
– Mon Calamari
Nov 8 '18 at 15:13
Did you try using raw jdbc connections?
– Mon Calamari
Nov 8 '18 at 15:13
@MonCalamari That works of course, however, this question is specifically about keeping the JOOQ abstraction for cross DB support, or else I'd have to move this code into DB specific code for every DB I want to support.
– Dominic
Nov 8 '18 at 15:17
@MonCalamari That works of course, however, this question is specifically about keeping the JOOQ abstraction for cross DB support, or else I'd have to move this code into DB specific code for every DB I want to support.
– Dominic
Nov 8 '18 at 15:17
If I were you I would raise an issue with github.com/jOOQ/jOOQ
– Mon Calamari
Nov 8 '18 at 15:21
If I were you I would raise an issue with github.com/jOOQ/jOOQ
– Mon Calamari
Nov 8 '18 at 15:21
@MonCalamari Thanks, I will consider it.
– Dominic
Nov 8 '18 at 15:51
@MonCalamari Thanks, I will consider it.
– Dominic
Nov 8 '18 at 15:51
add a comment |
1 Answer
1
active
oldest
votes
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`"))
answered Nov 13 '18 at 10:51
Lukas EderLukas Eder
133k70433949
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53210516%2fhow-to-scope-a-mysql-jooq-rename-table-query-to-the-same-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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`"))
answered Nov 13 '18 at 10:51
Lukas EderLukas Eder
133k70433949
add a comment |
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`"))
answered Nov 13 '18 at 10:51
Lukas EderLukas Eder
133k70433949
add a comment |
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`"))
answered Nov 13 '18 at 10:51
Lukas EderLukas Eder
133k70433949
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`"))
answered Nov 13 '18 at 10:51
Lukas EderLukas Eder
133k70433949
answered Nov 13 '18 at 10:51
Lukas EderLukas Eder
133k70433949
answered Nov 13 '18 at 10:51
Lukas EderLukas Eder
133k70433949
answered Nov 13 '18 at 10:51
Lukas EderLukas Eder
133k70433949
133k70433949
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53210516%2fhow-to-scope-a-mysql-jooq-rename-table-query-to-the-same-database%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Did you try using raw jdbc connections?
– Mon Calamari
Nov 8 '18 at 15:13
@MonCalamari That works of course, however, this question is specifically about keeping the JOOQ abstraction for cross DB support, or else I'd have to move this code into DB specific code for every DB I want to support.
– Dominic
Nov 8 '18 at 15:17
If I were you I would raise an issue with github.com/jOOQ/jOOQ
– Mon Calamari
Nov 8 '18 at 15:21
@MonCalamari Thanks, I will consider it.
– Dominic
Nov 8 '18 at 15:51