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.
