I can confirm on my side, that having the same constraint name in various schemata reproduces this problem. E.g.:
SCHEMA1.UC_MY_CONSTRAINT1 SCHEMA2.UC_MY_CONSTRAINT2 This is a common situation in environments where: - Every developer has their own copy of the common schema as a development schema - There are several mandators in the productive system. Every mandator has their own copy of the common schema In both cases, schema mapping is very useful: http://www.jooq.org/manual/ADVANCED/SchemaMapping/ This will be fixed in 2.0.4 as ticket #1133 https://sourceforge.net/apps/trac/jooq/ticket/1133 Cheers Lukas 2012/2/12 Lukas Eder <[email protected]>: > Hello Christopher, > > This looks like a bug in the Oracle integration for the source code > generator. I suspect that I have already fixed this issue while > implementing multi-schema support for the upcoming jOOQ 2.0.4: > https://sourceforge.net/apps/trac/jooq/ticket/282 > > When discovering unique keys, jooq-meta executes the following query > in jOOQ 2.0.3: > ------------------------------------------------------------------------------ > select > ALL_CONS_COLUMNS.CONSTRAINT_NAME, > ALL_CONS_COLUMNS.TABLE_NAME, > ALL_CONS_COLUMNS.COLUMN_NAME > from > ALL_CONS_COLUMNS > join > ALL_CONSTRAINTS > on > ALL_CONS_COLUMNS.CONSTRAINT_NAME = ALL_CONSTRAINTS.CONSTRAINT_NAME > where > ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'U' -- replace by 'P' if > querying for primary keys > and > ALL_CONSTRAINTS.CONSTRAINT_NAME not like 'BIN$%' > and > ALL_CONS_COLUMNS.OWNER = :inputSchema > order by > ALL_CONSTRAINTS.CONSTRAINT_NAME, > ALL_CONS_COLUMNS.POSITION > ------------------------------------------------------------------------------ > > The above query had a flaw in the join condition when joining > ALL_CONS_COLUMNS to ALL_CONSTRAINTS. It should in fact also join on > both tables' OWNER attribute: > > ------------------------------------------------------------------------------ > join > ALL_CONSTRAINTS > on > ALL_CONS_COLUMNS.OWNER = ALL_CONSTRAINTS.OWNER > and > ALL_CONS_COLUMNS.CONSTRAINT_NAME = ALL_CONSTRAINTS.CONSTRAINT_NAME > ------------------------------------------------------------------------------ > > Without this additional join condition, the query for unique / primary > keys may result in duplicate records if the same constraint exists in > several schemata. Can you confirm this is the case? > > Cheers > Lukas > > 2012/2/11 Chrriis <[email protected]>: >> Hi, >> >> I am currently experimenting with JOOQ. We currently use plain Java >> for our SQL statements and we target two possible RDBMS (SQLServer and >> Oracle). My experimentation is to see if I could generate a JOOQ model >> which could target both possible RDBMS. >> >> When analyzing the differences between the SQLServer and Oracle >> models, I found that the Keys were weird on the Oracle version (10g, >> 10.1.0.2.0). >> >> Here is a key from the Keys class on SQLServer (I manually renamed the >> columns for brevity): >> UC_MY_CONSTRAINT1 = createUniqueKey( >> test.generated.tables.Mytable.MY_TABLE, >> test.generated.tables.Mytable.MY_TABLE.KEY1, >> test.generated.tables.Mytable.MY_TABLE.KEY2, >> test.generated.tables.Mytable.MY_TABLE.KEY3, >> ... >> test.generated.tables.Mytable.MY_TABLE.KEY7 >> ); >> >> And here is the Oracle version: >> UC_MY_CONSTRAINT1 = createUniqueKey( >> test.generated.tables.Mytable.MY_TABLE, >> test.generated.tables.Mytable.MY_TABLE.KEY1, >> test.generated.tables.Mytable.MY_TABLE.KEY1, >> test.generated.tables.Mytable.MY_TABLE.KEY1, >> test.generated.tables.Mytable.MY_TABLE.KEY1, >> test.generated.tables.Mytable.MY_TABLE.KEY1, >> test.generated.tables.Mytable.MY_TABLE.KEY2, >> test.generated.tables.Mytable.MY_TABLE.KEY2, >> test.generated.tables.Mytable.MY_TABLE.KEY2, >> test.generated.tables.Mytable.MY_TABLE.KEY2, >> test.generated.tables.Mytable.MY_TABLE.KEY2, >> test.generated.tables.Mytable.MY_TABLE.KEY3, >> test.generated.tables.Mytable.MY_TABLE.KEY3, >> test.generated.tables.Mytable.MY_TABLE.KEY3, >> test.generated.tables.Mytable.MY_TABLE.KEY3, >> test.generated.tables.Mytable.MY_TABLE.KEY3, >> ... >> test.generated.tables.Mytable.MY_TABLE.KEY7 >> ); >> >> It seems that each column appears multiple time, the number being >> different depending on the query (sometimes 3, sometimes 7 times...) >> >> Note that when I run: >> select * from all_cons_columns where constraint_name = >> ''UC_MY_CONSTRAINT1" >> I only get one row per column. >> >> Is this a known problem? Do you have an idea what is happening? Is >> there a way to troubleshoot? >> >> Cheers, >> -Christopher
