jOOQ Code Generator Ignores PKs on H2

[Adam]

Hi,

I've been experimenting with jOOQ and, first of all, I have to say I'm very impressed so far.  There just seems to be one snag that I've encountered regarding using H2's in-memory database alongside the jOOQ code generator, in that it only generates org.jooq.impl.TableRecordImpl classes for all tables rather than updatable records.

The output from the code generator prints things like this:

Feb 11, 2014 5:16:54 PM org.jooq.util.DefaultRelations info
INFO: Ignoring primary key     : constraint_4(column unavailable)
Feb 11, 2014 5:16:54 PM org.jooq.util.DefaultRelations info
INFO: Ignoring primary key     : constraint_47(column unavailable)

On closer inspection, it seems that the column is unavailable because the code in org.jooq.util.h2.H2Database#loadPrimaryKeys is failing to extract the primary key ColumnDefinition from its table by column name:

1           TableDefinition table = getTable(schema, tableName);
2           if (table != null) {
3               String[] columnNames = columnList.split("[,]+");
4
5               for (String columnName : columnNames) {
6                   relations.addPrimaryKey(primaryKey, table.getColumn(columnName));
7               }
8           }

The column name is coming in upper case but this doesn't agree with the TableDefinition.  If I change the call to table.getColumn(columnName) (line 6 above) to be table.getColumn(columnName, true) then it works perfectly and generates updatable table records as I'd expect.

I've had to extend the H2Database class and override the loadPrimaryKeys method to fix this, but, given that nobody else seems to have this problem, I can't help feeling I'm missing something and there must be a better way to handle this case-sensitivity issue.  Is there a less cumbersome way to get code generation to work with H2 or is this actually a bug?

Many thanks,

Adam

[Lukas Eder]

Hello Adam,

Yes, that patch would be a workaround for the issue you're having, but I wouldn't recommend this. Can you provide me with the output of this query here:

SELECT "INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_SCHEMA",
  "INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_NAME",
  "INFORMATION_SCHEMA"."CONSTRAINTS"."COLUMN_LIST",
  "INFORMATION_SCHEMA"."CONSTRAINTS"."CONSTRAINT_NAME"
FROM "INFORMATION_SCHEMA"."CONSTRAINTS"
WHERE ("INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_SCHEMA" IN ('PUBLIC') -- Replace with actual schema
AND "INFORMATION_SCHEMA"."CONSTRAINTS"."CONSTRAINT_TYPE"  = 'PRIMARY KEY')
ORDER BY "INFORMATION_SCHEMA"."CONSTRAINTS"."TABLE_SCHEMA" ASC,
  "INFORMATION_SCHEMA"."CONSTRAINTS"."CONSTRAINT_NAME" ASC,
  "INFORMATION_SCHEMA"."CONSTRAINTS"."COLUMN_LIST" ASC

Also, could you provide me with the DDL of the table whose primary key is not discovered? I cannot reproduce this issue with:

create table test ("Aa" int, "BB" int, "cc" int, primary key ("Aa", "BB", "cc"));
select * from information_schema.constraints where lower(table_name) = 'test';

Just to be sure, are you using the latest jOOQ and H2 versions?

Cheers

Lukas

[atg...@googlemail.com]

Hi Lukas,

Thanks for your prompt reply.

First of all, I can confirm that I am indeed using the latest versions of jOOQ and H2, which I understand to be 3.2.3 and 1.3.175 respectively.

I've observed the issue for all tables containing a primary key, so this simple table is enough to demonstrate it:

CREATE TABLE consultant (
  ConsultantID int unsigned NOT NULL auto_increment,
  Name varchar NOT NULL,
  JobTitle varchar NOT NULL default '',
  IsDeleted tinyint unsigned NOT NULL default 0,
  PRIMARY KEY (ConsultantID)
);

My H2 database connection URL is: jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;IGNORECASE=true

After a little bit of experimentation with the query you've provided, I think I've seen what's causing the problem.  If I run this query using the connection described my the above URL then it prints the following results:

  PUBLIC, CONSULTANT, CONSULTANTID, CONSTRAINT_1

However, the DB connection URL we were intending to use for the application includes MySQL compatibility mode.  This is because the schema was originally designed for MySQL but it's been ported over to run on H2.  With MySQL compatibility mode, the query yields the following:

  public,consultant,CONSULTANTID,constraint_1

Without compatibility mode, the PK is found correctly because the results come in upper case and therefore a Record is generated that inherits from org.jooq.impl.UpdatableRecordImpl as desired. 

Regards,

Adam

[Lukas Eder]

Hi Adam,

Thanks for your analysis. I see, so IGNORECASE does not correctly report column names from all dictionary views.

Casing is an eternal hassle in SQL, and it is hard to get case-insensitivity right. This is why jOOQ renders quotes around names by default (e.g. "CONSULTANTID"). My general recommendation is to do the same in DDL. Of course, if you set this IGNORECASE flag to true, magical things might happen :-)

For this case, I suggest you file a bug at the H2 user group. Even with IGNORECASE=true, the JDBC driver and the INFORMATION_SCHEMA *should* always return the same identifier. I suspect that this has just been forgotten.

Cheers

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Lukas Eder]

An update on this issue. I've discussed this with Thomas Müller from H2, as your issue made me discover another issue that I've reported here:

https://groups.google.com/forum/#!topic/h2-database/me_teu3Shbc

Thomas is thinking about removing the COLUMN_LIST column entirely, which apparently wasn't intended for formal use. Maybe, there's a better solution for jOOQ when joining CONSTRAINTS and INDEXES:

https://github.com/jOOQ/jOOQ/issues/3042

I'll try to see if this can make it into jOOQ 3.3.0 and 3.2.4

[atg...@googlemail.com]

Hi Lukas

Thanks for investigating further.

For what it's worth, it actually seems like the IGNORECASE setting makes no difference here; it's only MODE=MySQL that causes the case to be different.

Running the query for constraints with case-sensitivity on (H2 default) produces the following results:

URL: jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MODE=MySQL
Outputs: public,consultant,CONSULTANTID,constraint_1

URL: jdbc:h2:mem:test;DB_CLOSE_DELAY=-1
Outputs: PUBLIC,CONSULTANT,CONSULTANTID,CONSTRAINT_1

According do the H2 documentation about MySQL Compatibility Mode, The fact that "Meta data calls return identifiers in lower case" is by design: http://h2database.com/html/features.html#compatibility

Therefore, it seems to me that jOOQ cannot assume the case of meta data when using H2 because, as far as I can see, there's no way of jOOQ's code generator knowing whether or not the underlying database is in compatibility mode.  In fact, even if you could determine compatibility mode, it's probably not wise to dabble in such a thing.  If the column list does disappear and a better way to join indexes and constraints is established then this might solve the problem but, if not, would it still not be reasonable to use case-insensitivity for code generation against H2?

Cheers,

Adam


On Tuesday, 11 February 2014 17:36:34 UTC, Adam wrote:

[Lukas Eder]

Hi Adam,

2014-02-13 13:32 GMT+01:00 <atg...@googlemail.com>:

Hi Lukas

Thanks for investigating further.

For what it's worth, it actually seems like the IGNORECASE setting makes no difference here; it's only MODE=MySQL that causes the case to be different.

Running the query for constraints with case-sensitivity on (H2 default) produces the following results:

URL: jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MODE=MySQL
Outputs: public,consultant,CONSULTANTID,constraint_1

URL: jdbc:h2:mem:test;DB_CLOSE_DELAY=-1
Outputs: PUBLIC,CONSULTANT,CONSULTANTID,CONSTRAINT_1

According do the H2 documentation about MySQL Compatibility Mode, The fact that "Meta data calls return identifiers in lower case" is by design: http://h2database.com/html/features.html#compatibility

I think Thomas is a PostgreSQL aficionado and thus chose lower case. Many other databases report case-insensitive names as upper-case. I believe that the SQL standard is indifferent with respect to that, though.

Therefore, it seems to me that jOOQ cannot assume the case of meta data when using H2 because, as far as I can see, there's no way of jOOQ's code generator knowing whether or not the underlying database is in compatibility mode.  In fact, even if you could determine compatibility mode, it's probably not wise to dabble in such a thing.

It would be a terrible idea :-) But a global explicit flag might eventually be useful. I'm not sure if it is feasible, though.

 

If the column list does disappear and a better way to join indexes and constraints is established then this might solve the problem but, if not, would it still not be reasonable to use case-insensitivity for code generation against H2?

My personal opinion is to always stay in full control of casing, especially when you support several databases, and when you're starting a project. It's the only way to get it right because apart from SQLite, all databases correctly implement quoted, case-sensitive names.

If you're adding H2 to a legacy, case-insensitive MySQL database, then I'm not quite sure what the best approach would be...

Cheers

Lukas

[Lukas Eder]

#3042 is fixed on GitHub master and will be included in jOOQ 3.3.0. Hope this will work around your issue for the time being.

Cheers

Lukas