Hi David, 2015-09-16 22:16 GMT+02:00 David ‘Bombe’ Roden <[email protected]>:
> Hi everyone, > > I have a problem with insertInto().returning().fetchOne() returning null. > I have traced it back to a table.getIdentity() call returning null, and > rightfully so, because in the automatically generated tables getIdentity() > is not overridden and the default implementation does indeed return null. > Yes, this is a known issue, with some related discussions here: https://github.com/jOOQ/jOOQ/issues/2374 > Is there any way I can get the code generator to generate identity > information for a table without relying on the database? > > My situation is as follows: > > - I keep track of database changes using Flyway. > - I use a Postgres database for dev and prod deployments. > - I use H2 for unit tests. > - Involved versions: H2 1.4.188, jOOQ 3.6.2, Postgres 9.3, Flyway 3.2.1. > > This means that I want/need to use the same SQL scripts to generate and > update the Postgres tables and the H2 tables for unit tests. I also use the > H2 database to generate the jOOQ code from. I would personally strongly recommend using PostgreSQL for source code generation. There are a variety of features in PostgreSQL that are very nice to have, which you won't have access to if you make H2 your master database, conceptually. Also, you wouldn't run into this particular issue :) > As jOOQ uses different means to find out whether a column is an identity > column with Postgres and H2 the code generated from the H2 database does > not include identity information. > There are many options... *Tweaking your SQL file:* In H2, you would be using AUTO_INCREMENT or IDENTITY to generate an internal sequence associated with a column: http://h2database.com/html/grammar.html?highlight=auto_increment&search=auto#column_definition Unfortunately, PostgreSQL doesn't yet support the SQL standard IDENTITY, but uses the SERIAL or SERIAL8 pseudo data type in DDL statements. You could, perhaps, use the following technique: PostgreSQL: CREATE TABLE t ( id SERIAL8 NOT NULL, ... ) Transform the above prior to loading the SQL statement into H2: CREATE TABLE t ( id BIGINT NOT NULL AUTO_INCREMENT, ... ) I suspect that regular expessions might work here: - Search: SERIAL\s+(NOT\s+NULL)? - Replace: INT $1 AUTO_INCREMENT And... - Search: SERIAL8\s+(NOT\s+NULL)? - Replace: BIGINT $1 AUTO_INCREMENT *Using the XMLDatabase:* An entirely different solution is to use the built-in org.jooq.util.xml.XMLDatabase meta data source (from jOOQ-meta). You would need to have a schema representation in XML following this format: http://www.jooq.org/xsd/jooq-meta-3.5.4.xsd The XML format mimicks the SQL Standard's INFORMATION_SCHEMA layout. There is a column property for identity generation: <element name="identity_generation" type="string" minOccurs="0" maxOccurs="1 "/> *Writing your own Database:* Of course, you could take some inspiration from the above XMLDatabase and roll your own. You can construct schema meta data any way you want, then. *Using JPA:* Perhaps a bit extreme, but some people have successfully exported a schema (e.g. from PostgreSQL) via Hibernate, re-imported it (e.g. into H2) via Hibernate, and re-exported it (e.g. from H2) using jOOQ: http://vladmihalcea.com/2013/12/06/jooq-facts-from-jpa-annotations-to-jooq-table-mappings/ Hope this helps :) -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
