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 <[email protected]> > 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 > > Am Mittwoch, 13. November 2013 17:20:03 UTC+1 schrieb 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 >> >> Le mercredi 13 novembre 2013 12:25:12 UTC+1, [email protected] a >> écrit : >>> >>> 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? >>> >> -- > 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]. > For more options, visit https://groups.google.com/groups/opt_out. > -- 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]. For more options, visit https://groups.google.com/groups/opt_out.
