Hi Lukas my answers below. Il giorno giovedì 24 maggio 2012 17:15:27 UTC+2, Lukas Eder ha scritto: > > Hi Alex > > > I tried to debug the code following the execution within jOOQ classes > and it > > seems that the SchemaMapping classes in the Factory hold correct values > but > > something goes wrong during SQL statement generation. > > > > Can anyone confirm this? > > Thanks for your report. I generally cannot confirm this. Note though, > that there has been a change #1315 in jOOQ 2.3.0 setting the default > schema in generated factories: > https://sourceforge.net/apps/trac/jooq/ticket/1315 > > So if you have two schemata A and B, and you use AFactory, BFactory to > generate your SQL, the schema name is no longer rendered. >
I think this is the behaviour I am observing but on postgres this makes all queries trigger an error. I think all the problems derive from the postgres concept of schema search path and the fact that when connecting to postgres as a particular user only tables belonging to the public schema can be queryed without any schema prefix. Let me depict my situation with an example: dbserver |- db1 (owner user1) | |- sch1 (owner user1) | | \_tb1 | |- sch2 (owner user1) | \_ public (owner postgres) | \_tb2 \_ postgres (owner postgres) \_public (owner postgres) the postgres dbms has two databases, the first one has two schemas besides the public one, the second database is the default postgres database (owned by the administrator user postgres). I generated the classes connecting to jdbc:postgresql://dbserver/db1 as user user1 and used sch1 as <inputSchema> obtaining Sch1Factory. A query on tb1 is rendered as select tb1.field1,... from tb1 where ... as you say but with postgres connecting as user 1 only queries on tb2 could work that way. The working query on tb1 should be select sch1.tb1.field1 ... from sch1.tb1 where ... as generated by jOOQ 2.2.2. This explanation is supported by the fact that a workaround is to use the Factory.use(schema) deprecated method that in case of postgres sets the schema search path to the factory schema. Another observation is that with jOOQ 2.3.1 only classes generated specifying <inputSchema>public</inputSchema> works out of the box. I know that one of jOOQ goals is to abstract from the db vendor but is there a better way to deal with my scenario? What if I generate classes for the whole db1 setting no <inputSchema>? Other than that, there has also been #1286, a setting that allows for > entirely omitting schema name rendering: > https://sourceforge.net/apps/trac/jooq/ticket/1286 > Forcing the schema rendering on the Sch1Factory passing a new Settings().withRenderSchema(true) has no effect in my case. Does any of this match your observations? >
