Hi Jens, >From your previous messages to this group, I take that using the out of the box JPADatabase is not an option.
I'm not 100% sure what should be the correct solution here when you use H2 in that MySQL mode. I mean, the database will do some emulations similar to jOOQ's, but they will inevitably break at some point. One option could be (?) to not use the in-memory mode of H2, but write the database to a file, and reopen it in H2 mode, instead of MySQL mode, prior to passing it to the jOOQ code generator. I wouldn't want to integrate your PR now. Not all databases are case sensitive based on quotes alone. E.g. SQL Server isn't, and MySQL also isn't in some cases. We're using the H2Database in a variety of cases where we run an in-memory database to emulate the actual database. So, I wouldn't want to make this change and maintain it in the future, because we would now be "guaranteeing" not to break this hack again. Since you've been trying a few workarounds over the past months now, what keeps you from just spinning up an actual MySQL database for code generation? You could easily run it with Testcontainers. Note that we're also experimenting with that for our out-of-the-box solutions, as we're aware of the many limitations of using H2 for the task, even if users aren't targeting H2: https://github.com/jOOQ/jOOQ/issues/6551 Thanks, Lukas On Wed, Nov 6, 2019 at 9:17 AM Jens Teglhus Møller <[email protected]> wrote: > Hi > > I'm still battling with my probably quite non-standard setup where I try > to create my jooq model from JPA via H2Database so it will be compatible > with mysql. > > So I create an H2 database with the following > url > jdbc:h2:mem:testdb;MODE=MYSQL;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE > then I let hibernate create tables and then I feed the database into jooq > generator (using the H2Database). > > This means that all tables/schemas are created with lower case identifiers. > > What then happens is that code generation fails with: > > ERROR org.jooq.meta.AbstractDatabase - Could not load schemata > org.jooq.exception.DataAccessException: SQL [select > "INFORMATION_SCHEMA"."SCHEMATA"."SCHEMA_NAME", > "INFORMATION_SCHEMA"."SCHEMATA"."REMARKS" from > "INFORMATION_SCHEMA"."SCHEMATA"]; Schema "INFORMATION_SCHEMA" not found; > SQL statement: > select "INFORMATION_SCHEMA"."SCHEMATA"."SCHEMA_NAME", > "INFORMATION_SCHEMA"."SCHEMATA"."REMARKS" from > "INFORMATION_SCHEMA"."SCHEMATA" [90079-199] > > I narrowed it down to H2 being case sensitive when using quoted > identifiers, If I remove all the quotes the query runs. > > Question is if you would consider a PR that changes H2Database.create0() > method from: > > @Override > protected DSLContext create0() { > return DSL.using(getConnection(), SQLDialect.H2); > } > > into something like > > @Override > protected DSLContext create0() { > return DSL.using(getConnection(), SQLDialect.H2, new > Settings().withRenderQuotedNames(RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED)); > } > > I would expect it to be safe since this context is only used internally in > the H2Database class and it only works on system tables that do not have > funny names that need quoting. > > Or am I going around the the wrong way. > > I tried building jooq locally with above change and the fixes my issue. > > I did manage to work around the issue by using new > Settings().withRenderNameCase(RenderNameCase.LOWER) when accessing the > mysql database, but I also think the proposed code change makes the > H2Database more robust against different H2 configurations. > > Best regards Jens > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/1d2522a1-212d-4441-b8e4-1bf46716f296%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/1d2522a1-212d-4441-b8e4-1bf46716f296%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7VzhRTPkxbhLYiY2hizULQj1QfrJiZdfPL9VqX8vAt2w%40mail.gmail.com.
