Code generation on PostgreSQL - junit tests with HSQLDB

[eugen...@netrapid.de]

Hi,

currently we're evaluating the use of jOOQ for a new project.

We're facing two issues for JUnit testing.


Setting

• We use the gradle jooq plugin for code generation with a PostgreSQL database as source. This database is initially filled by liquibase-scripts.
• We use an HSQL-Spring-embedded-database for unit testing. Here we configured jooq to use the HSQLDB dialect at runtime.
• We try to mimic the PostgreSQL syntax with the HSQLDB by executing "SET DATABASE SQL SYNTAX PGS TRUE;" on startup.
• Versions:
  
• jOOQ: 3.2.0
• HSQLDB: 2.3.1

First issue

When executing a query the following exception is thrown:

org.jooq.exception.DataAccessException: SQL [select "public"."person_example"."name", "public"."person_example"."age" from "public"."person_example" limit ? offset ?]; invalid schema name: public
    at org.jooq.impl.Utils.translate(Utils.java:1158)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:495)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:325)
    at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:324)
    at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:1034)
    [...]

When I manually rename the name for the public schema to "PUBLIC" (in org.jooq.generated.Public), this issue seems to be resolved. So it seems to be a problem with case-sensitivity. Is there a better solution?


Second issue

When the first issue is manually resolved, this exception is thrown:

org.jooq.exception.DataAccessException: SQL [select "PUBLIC"."person_example"."name", "PUBLIC"."person_example"."age" from "PUBLIC"."person_example" limit ? offset ?]; user lacks privilege or object not found: person_example
    at org.jooq.impl.Utils.translate(Utils.java:1158)
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:495)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:325)
    at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:324)
    at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:1034)
    [...]

In Liquibase we explicitly stated to insert into the schema "PUBLIC". Since the database is only created on the fly, it is hard to see its real content...

Any idea on that?



For our better understanding: Is the code generated by the code-generator somehow database-specific or generic?

Thanks a lot!

Eugen

PS. Are there news on putting the gradle jooq generator into a jooq release?

[Stéphane Cl]

Hello,
I do similar things except that I test on H2.
Disabling Schema rendering helped a lot, here's my code, you may need to adapt it a bit since it's targeting jooq 2.6 :

        Connection conn = DriverManager.getConnection("jdbc:h2:mem:test;DATABASE_TO_UPPER=false;MODE=PostgreSQL", "user", "password");
        RunScript.execute(conn, new FileReader( appDirectory + File.separator + "conf" + File.separator + "baseh2.sql"));

        Settings settings = new Settings()
                .withRenderSchema(false);

        sqlFactory = new Factory(conn, SQLDialect.H2, settings );
        //do stuff with factory

Hope it helps

[eugen...@netrapid.de]

Thanks, Stéphane!

Since we are using Spring, I created a class JooqConfiguration that extends org.jooq.impl.DefaultConfiguration and included it as Spring bean. There I could now set the Settings. Great.

Concerning the HSQLDB, we're now considering having every deloper setting up a local PostgreSQL db only for testing. This speeds things up since the liquibase scripts do not need to be run for every test run.

Eugen

[Lukas Eder]

Hi guys,

Nice idea @Stéphane. Yes, in simple situations, it is often desireable to turn off schema name rendering. I'm actually wondering, if the default setting for this flag should change in jOOQ 4.0. From my perspective, schema names should be rendered by default, as I've always worked with rather complex databases with dozens of schemas. But your real-life experience may differ. Maybe, I should even set up a poll for such questions, when work starts for jOOQ 4.0 (by mid-late 2014).

I've created an issue to track this idea:

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

@Eugen:

There's another setting you might use: RenderNameStyle. You can then set this to RenderNameStyle.AS_IS (or UPPER, LOWER). This will omit the quotes around generated names and make identifiers case-insensitive (in most databases). This is also being discussed in parallel in another thread by Darren Shepherd:

https://groups.google.com/d/msg/jooq-user/UA2AkO0yFVQ/mdwFJP-m0SsJ

Another option is to use runtime schema mapping, where you can translate "PUBLIC" to "public" or vice-versa, depending on the SQLDialect:

http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-schema-mapping

However, your best bet in the long run is to stay in complete control of case-sensitivity by making casing part of your database object naming strategy. As you're having issues with the "PUBLIC" or "public" schemas, I'll further recommend omitting that schema entirely. I shall soon blog about this, as case-sensitivity is a completely underestimated caveat in cross-vendor SQL compatibility... Search this user group and you'll find a few threads dealing with the matter :-)

https://groups.google.com/forum/#!searchin/jooq-user/case$20sensitivity

Cheers

Lukas

2013/11/14 <eugen...@netrapid.de>

--
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.
For more options, visit https://groups.google.com/groups/opt_out.