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
