The really tough test cases do not lie in the integration tests, but in the
code generation sub-project.
I am currently trying to assemble a SQL statement of this kind for the
Postgres code generation extension:
create().select(
> Columns.COLUMN_NAME,
> Columns.ORDINAL_POSITION,
> create().decode()
> .value(Columns.DATA_TYPE)
> .when("ARRAY", create().select(formatType(ATTTYPID, ATTTYPMOD))
> .from(PG_ATTRIBUTE)
> .where(ATTRELID.equal((Field)
> create().plainSQLField(Columns.COLUMN_NAME + "::regclass")))
>
> .and(PgAttribute.ATTNAME.equal(Columns.COLUMN_NAME)).asField())
> .otherwise(Columns.DATA_TYPE),
> Columns.UDT_NAME)
> .from(COLUMNS)
> .where(Columns.TABLE_SCHEMA.equal(getSchemaName()))
> .and(Columns.TABLE_NAME.equal(getName()))
> .orderBy(Columns.ORDINAL_POSITION)
> .fetch();
>
This statement revealed lots of flaws to me, that are going to be fixed in
1.5.4:
1. The CASE clause with nested select is causing syntax errors:
https://sourceforge.net/apps/trac/jooq/ticket/220
2. The CASE field expression cannot be retrieved from the resulting
record easily, except if it were refactored out of the SQL statement, into a
dedicated Field<?> variable:
https://sourceforge.net/apps/trac/jooq/ticket/156
3. The ::regclass type cast must be expressed in a plainSQL. General
casting support is clearly desirable:
https://sourceforge.net/apps/trac/jooq/ticket/215
These are interesting news! The above query nicely depicts the power of jOOQ
in creating complex SQL, but also points out that there is still a lot to
do!
Looking forward to more insight into release 1.5.4
Cheers
Lukas Eder
