H2Database internal DSLContext settings

[Jens Teglhus Møller]

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

[Lukas Eder]

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 

--
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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/1d2522a1-212d-4441-b8e4-1bf46716f296%40googlegroups.com.

[Jens Teglhus Møller]

Hi Lukas

My proposed change would only affect the H2Database it is isolated to the H2Database.java class so the change has limited impact on the codebase.

Generating the model is part of a larger maven multi module build where spinning up mysql would add docker as a dependency and slow down the build, I'm just not ready to go there yet (I do spin up mysql in docker on my dev machine to test the setup).

My goal is to be able to run my development environment against an in memory H2 database and the be able to deploy the same code against a mysql production database. It works pretty well but the list of configurations required to get everything working smoothly is piling up and my proposed change would just simplify the whole setup a bit.

Best regards Jens

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.

[Lukas Eder]

Hi Jens,

"Limited impact" is something that is really very easy to say, yet difficult to prove. I think you're underestimating the effect such a change has on our own efforts, including documenting and regression testing this, and thinking about it constantly when we might want to change something in this area. It might scratch your immediate itch, but given that it is a hack, rather than a thorough solution (e.g. MySQL on test containers seems more thorough), it might break someone else's assumptions, or it might not be good enough for you in the future, when you discover *another*, similar problem. In that case, will we be able to revert the change? Or will we break someone else's assumption about any of jOOQ's internals? Will we have to discuss this with you every time we change something in H2Database, or somewhere else?

All of this shows that it would really be unwise to start engaging in applying such quick fixes. From a different perspective: You seem to have *already* decided not to use the JPADatabase but roll your own instead, which seems to already do almost the same thing as JPADatabase, apart from the H2 JDBC URL. So, *your* effort to maintain this quick fix on your side seems to be drastically lower than ours, and the impact is guaranteed to be limited to your setup, not every H2Database (and by consequence JPADatabase and DDLDatabase) user's setup out there.

I hope this sheds some more light on why I am very reluctant to accept such quick fixes. They are almost never a good idea from a vendor perspective. Especially, if they're not really solving your actual problem (this reminds me of http://xyproblem.info).

I hope this helps,
Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/841e13f7-3cd8-45d5-9017-45d713395361%40googlegroups.com.

[Jens Teglhus Møller]

Hi Lukas

My argument is that H2 as a database supports running with identifiers in both uppercase and lowercase mode, but the jooq H2Database implementation limits its use to uppercase mode.

But there is probably a risk that changes like this leaks bugs into the generated model and you are the one that has to support all the quirks, so you make the calls.

I will let it rest ;-).

Best regards Jens

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/841e13f7-3cd8-45d5-9017-45d713395361%40googlegroups.com.

[Lukas Eder]

On Wed, Nov 6, 2019 at 3:49 PM Jens Teglhus Møller <j...@mostlyharmless.dk> wrote:

Hi Lukas

My argument is that H2 as a database supports running with identifiers in both uppercase and lowercase mode, but the jooq H2Database implementation limits its use to uppercase mode.

I know what you meant, and that's correct, but...

 

But there is probably a risk that changes like this leaks bugs into the generated model and you are the one that has to support all the quirks, so you make the calls.

... that, and I really don't like doing something only for this H2Database for all the many reasons I've mentioned before. I've thought about avoiding quoted identifiers on these jOOQ-meta queries in the past. I couldn't find the relevant issue just now, so I created one: https://github.com/jOOQ/jOOQ/issues/9511

We'll evaluate this in the future, but it will definitely not go into a patch release. So the earliest would be 3.13.0

[Lukas Eder]

Just to be clear, my criticism was for this to be done only in the H2Database ("simple change"). I would not mind changing this for all dialects, if we thoroughly regression test this.

[Lukas Eder]

Starting from jOOQ 3.13, we'll stop quoting identifiers by default in jOOQ-meta generated queries (code generation):

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

This change won't be backported due to the subtle regression risks this may pose, but I haven't found anything obvious, which is why we'll do this for version 3.13. Hope this will be useful for you!

Cheers,

Lukas

[Jens Teglhus Møller]

Lovely, thanks a lot :-).

We will upgrade to 3.13 as soon as it is released.

Best regards Jens