This is now fixed on GitHub master. For the fix, I have changed the
aforementioned query in SQLServerTableDefinition to the following query:
for (Record record : create()
.select(
COLUMNS.COLUMN_NAME,
COLUMNS.ORDINAL_POSITION,
COLUMNS.DATA_TYPE,
COLUMNS.IS_NULLABLE,
COLUMNS.CHARACTER_MAXIMUM_LENGTH,
COLUMNS.NUMERIC_PRECISION,
COLUMNS.NUMERIC_SCALE,
identity)
.from(COLUMNS)
.join("sys.schemas s")
.on(COLUMNS.TABLE_SCHEMA.equal(field("s.name",
String.class)))
// sys.objects is used rather than sys.tables, to include
tables AND views
.join("sys.objects t")
.on("t.type in ('U', 'V')")
.and("t.schema_id = s.schema_id")
.and(COLUMNS.TABLE_NAME.equal(field("t.name",
String.class)))
.join("sys.columns c")
.on("c.object_id = t.object_id")
.and(COLUMNS.COLUMN_NAME.equal(field("c.name",
String.class)))
.where(COLUMNS.TABLE_SCHEMA.equal(getSchema().getName()))
.and(COLUMNS.TABLE_NAME.equal(getName()))
.orderBy(COLUMNS.ORDINAL_POSITION)
.fetch()) {
As I suspected, the problem originated from a cartesian product originating
from the join of COLUMNS with SYS.OBJECTS. An additional join clause
joining SYS.SCHEMAS resolves the issue.
I will merge this fix downstream to 2.x versions of jOOQ
Thanks again for reporting this.
Cheers
Lukas
2012/12/4 Lukas Eder <[email protected]>
> Thanks for reporting this. It seems as though jOOQ-meta's join predicate
> between columns and objects is wrong, creating a cartesian product. Here's
> the relevant query:
>
> for (Record record : create().select(
> COLUMNS.COLUMN_NAME,
> COLUMNS.ORDINAL_POSITION,
> COLUMNS.DATA_TYPE,
> COLUMNS.IS_NULLABLE,
> COLUMNS.CHARACTER_MAXIMUM_LENGTH,
> COLUMNS.NUMERIC_PRECISION,
> COLUMNS.NUMERIC_SCALE,
> identity)
> .from(COLUMNS)
> .join("sys.objects o")
> .on("o.type in ('U', 'V')")
> .and(COLUMNS.TABLE_NAME.equal(field("o.name", String.class)))
> .join("sys.columns c")
> .on("c.object_id = o.object_id")
> .and(COLUMNS.COLUMN_NAME.equal(field("c.name", String.class)))
> .where(COLUMNS.TABLE_SCHEMA.equal(getSchema().getName()))
> .and(COLUMNS.TABLE_NAME.equal(getName()))
> .orderBy(COLUMNS.ORDINAL_POSITION)
> .fetch()) {
>
> The join between INFORMATION_SCHEMA.COLUMNS and sys.objects is incomplete,
> which leads to problems when the same table name exists in multiple schemas.
>
> I have registered #1993 for this:
> https://github.com/jOOQ/jOOQ/issues/1993
>
> Cheers
> Lukas
>
>
> 2012/12/4 Gary Clark <[email protected]>
>
>> I'm not sure if this is something I'm doing wrong (quite possible) or if
>> there is a problem with the way code is being generated. Here is an
>> example schema:
>>
>> CREATE TABLE dbo.named_id (
>> name_id INT IDENTITY,
>> name VARCHAR(50)
>> )
>> GO
>> CREATE SCHEMA under10
>> GO
>> CREATE VIEW under10.named_id AS
>> SELECT *
>> FROM dbo.named_id
>> WHERE name_id < 10
>> GO
>> CREATE SCHEMA over10
>> GO
>> CREATE VIEW over10.named_id AS
>> SELECT *
>> FROM dbo.named_id
>> WHERE name_id > 10
>> GO
>>
>> The thing to notice is that each schema contains a view with the same
>> name and fields as the original table. This is being done to limit what
>> people can see by setting which schema they have access to witout
>> rewritting any of the reports. However, when I generate classes from JOOQ,
>> I'm seeing the same fields being repeated there. In this case, NAME_ID and
>> NAME are repeated in the NamedId class (extends TableImpl) and the
>> getters/setters are repeated in NamedIdRecord (extends TableRecordImpl).
>> The repeat count is the total number of schemas plus the original table in
>> dbo. There are 3 exact copies for the above example schema.
>>
>> In the generator XML I have <inputSchema>dbo</inputSchema> and have
>> tried using <includes>dbo\..*</includes> but the code generated is
>> unchanged. This is with versions 2.5.1 and 2.6.0 of JOOQ.
>>
>> My workaround for now is using a copy of the database with the extra
>> schemas removed. Its not an ideal situation, but it does let me continue
>> work. Are there any other ideas that I could try?
>>
>>
>
>
