Thanks again, Lukas.
I'll watch #1261. I agree with your proposal that they follow Postgres'
example for how to handle enum types.
Unfortunately I don't think jOOQ meta will be a viable solution. Only SQL
migrations are run during the build to codegen prior to the compilation
step, so Java migrations are not yet runnable. This could be done using a
dedicated module, but that can get messy. I would rather spend the time
rewriting the build step to use Postgres proper, rather than fudge it with
workaround hacks.
On Wednesday, July 4, 2018 at 2:51:47 AM UTC-7, Lukas Eder wrote:
>
> Hi Ben,
>
> Oh interesting, I didn't realise the PostgreSQL style CREATE TYPE syntax
> was added to H2. But it doesn't help either. Try this:
>
> CREATE TYPE e AS ENUM ('a', 'b');
> CREATE TABLE x (e e);
> SELECT *
> FROM information_schema.columns
> WHERE table_name = 'X';
>
> There is no reference to a type called "E" reported from
> information_schema.columns. The TYPE_NAME is "ENUM" and the COLUMN_TYPE is
> "ENUM('a', 'b')". I have created a feature request for this:
> https://github.com/h2database/h2database/issues/1261
>
> But I just thought of a different solution. Maybe it would be possible for
> you to override the H2Database from jOOQ meta to produce the appropriate
> type information yourself? Let me know if this is a viable workaround and
> I'll be very happy to answer any specific questions you may have about this.
>
> Thanks,
> Lukas
>
> Am Di., 3. Juli 2018 um 09:12 Uhr schrieb <[email protected] <javascript:>
> >:
>
>> 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]>:
>>>
>>>> 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]'
>>>> 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].
>>>> 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] <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.
