Thanks for #7640, that explains it nicely. Unifying these would be wonderful, since its very awkward to observe them behaving differently.
You're using PostgreSQL DDL, so what does this have to do with H2? > The DDL that I showed was for H2. Regarding your approach, would you mind explaining your setup a little bit. I think most of your confusion can be summarized here. My setup is the following: 1. Dual migrations are written for H2 and Postgres. Most often H2 are updated inline as simpler than 1-to-1. 2. Gradle runs Flyway migrations to create a persisted H2 db of the schema 3. Gradle runs the jOOQ generator ... 10. Java server starts and runs Flyway Postgres migrations 11. jOOQ is configured to use Postgres for runtime The above has the benefit of not checking in generated code and simpler iterations of the table designs during development. That is a point we disagree about, but quite a lot of boilerplate can be generated and I've never found checking it in to be beneficial (jsonSchema2Pojo, jaxb codegen, jooq, autoValue, protobuf, javaPoet, etc). The dual setup can be useful when transitioning a product from being ORM-based (leaning on H2 to for faster test coverage) to ease the transition to SQL first approach. The drawback is of course advanced SQL, which happens more so when ORMs are transitioned out entirely. Ideally I would generate the DDL against Postgres in a similar manner and not have any clashes, but I never have the bandwidth to rewrite the build to do so. The differences are usually minor enough to not prioritize the work, as problems rarely come up in practice. So the root problem is where the dialects differ and that causes the codegen to not match up nicely. It is not jOOQ's fault, since papering over dialects is near impossible, but can be a hinderance. But being able to paper over enums would reduces the boilerplate and let me put off the transition away from H2 a tad longer. On Monday, July 2, 2018 at 11:40:18 PM UTC-7, Lukas Eder wrote: > Hi Ben, > > H2 and MySQL implement per-column enum types, unlike PostgreSQL, which > supports true, reusable user-defined types. The two ways of declaring enums > haven't been unified, for no real reason than "not top prio". I thought > there was an issue to do this but haven't found it. Here is a newly created > one: > https://github.com/jOOQ/jOOQ/issues/7640 > > Regarding your approach, would you mind explaining your setup a little > bit. You're using PostgreSQL DDL, so what does this have to do with H2? > Regarding your second question, H2 doesn't support schema level enum types, > so there is no way to be able to properly cast an enum bind variable on > PostgreSQL, if the enum has not been generated using PostgreSQL, but you're > doing that, so why would you get enum-per-tables? > > Ultimately, what matters is that org.jooq.EnumType is properly implemented > including all the methods in there, for the enum to work on PostgreSQL. > > Lukas > > Am Di., 3. Juli 2018 um 01:58 Uhr schrieb <[email protected] <javascript:> > >: > >> Hi Lukas, >> >> I am trying to migrate to use the newly released support for H2's enum. >> The code is generated during the build and run against Postgres, so ideally >> this keeps the differences between migration scripts minimal. Unfortunately >> there are a few surprises. >> >> 1) Instead of a single enum for the given type, an enum-per-table is >> generated. I skimmed the issue tracker and I see hints as to why this might >> have been done, but not enough detail to explain why. >> >> Take an H2 migrations like, >> >> CREATE TYPE identity_type AS ENUM ('email', 'phone_number'); >> >> CREATE TABLE user_identity ( >> id serial primary key, >> user_id uuid NOT NULL, >> identity varchar(255) NOT NULL, >> identity_type identity_type NOT NULL, >> FOREIGN KEY (user_id) REFERENCES user_info(id) ON DELETE CASCADE >> ); >> >> CREATE TABLE access_code ( >> id serial primary key, >> identity varchar(255) NOT NULL, >> identity_type identity_type NOT NULL, >> code varchar(255) NOT NULL, >> type varchar(255) NOT NULL, >> identifiable boolean NOT NULL, >> CHECK type in ('confirm', 'forgot_password', 'invite', 'register') >> ); >> >> My naive expectation is that a single IdentityType enum is generated. I >> would then be able to use this enum in queries to either table. Instead >> there are two enums generated: AccessCodeIdentityType >> and UserIdentityIdentityType. These types are not interchangeable and >> conversion requires using their literal types in the enum's valueOf(). >> >> >> 2) The SQL queries do not contain the enum cast, causing Postgres to >> reject it. For example: >> >> SELECT >> user_identity.id, >> user_identity.user_id, >> user_identity.identity, >> user_identity.identity_type >> FROM user_identity >> WHERE ( >> user_identity.identity = '[email protected] <javascript:>' >> AND user_identity.identity_type = 'email' >> ); >> >> This fails with the error: >> PSQLException: ERROR: operator does not exist: identity_type = character >> varying >> >> In the previous code I performed the cast manually which adds some bloat >> that I was hoping to remove: >> >> private static final DataType<Object> IDENTITY_TYPE = >> new DefaultDataType<>(POSTGRES, SQLDataType.OTHER, "identity_type"); >> >> UserIdentityRecord record = db.selectFrom(USER_IDENTITY) >> .where(USER_IDENTITY.IDENTITY.eq((identity))) >> .and(USER_IDENTITY.IDENTITY_TYPE.eq(identityType(identityType))) >> .fetchOne(); >> >> private Field<String> identityType(String type) { >> return DSL.coerce(DSL.cast(type, IDENTITY_TYPE), >> USER_IDENTITY.IDENTITY_TYPE); >> } >> >> Is there a way to use generated H2 enums with Postgres at runtime? >> >> Thanks! >> Ben >> >> -- >> 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] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> > -- 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.
