Hello Patrik,
Thanks for your detailed report! Indeed, the jOOQ query to collect
PostgreSQL enum types is not optimal for PostgreSQL 9.1+:
.select(
PG_NAMESPACE.NSPNAME,
PG_TYPE.TYPNAME,
PG_ENUM.ENUMLABEL)
.from(PG_ENUM)
.join(PG_TYPE).on("pg_enum.enumtypid = pg_type.oid")
.join(PG_NAMESPACE).on("pg_type.typnamespace = pg_namespace.oid")
.where(PG_NAMESPACE.NSPNAME.in(getInputSchemata()))
.orderBy(
PG_NAMESPACE.NSPNAME,
PG_TYPE.TYPNAME,
PG_ENUM.ENUMLABEL)
.fetch();
In PostgreSQL 9.1, the pg_enum.enumsortorder was added. Compare:
- http://www.postgresql.org/docs/9.0/static/catalog-pg-enum.html
- http://www.postgresql.org/docs/9.1/static/catalog-pg-enum.html
In order for jOOQ to be compatible between < 9.1 and 9.1+ versions, I guess
jOOQ could check for the existence of enumsortorder.
- If it exists, order by that
- If it doesn't exist, order by pg_enum.oid
Any other opinions?
I'll be tracking this as #2707. I think this can be fixed for jOOQ 3.2
https://github.com/jOOQ/jOOQ/issues/2707
Cheers
Lukas
2013/8/22 <[email protected]>
> Hello,
>
> I am using jOOQ for some time now, and I am very happy about it :)
>
> I'm just stumbling from time to time over some problems, and this is one
> of them:
>
> The order of the enum values generated in the enum classes is not the same
> as the order defined in the database.
>
> For instance:
>
> POSTGRES:
>
> create type weekday as enum ( 'SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY',
> 'THURSDAY', 'FRIDAY', 'SATURDAY');
>
> this results in:
>
> dwh=# select * from pg_enum where enumtypid='260085';
> enumtypid | enumsortorder | enumlabel
> -----------+---------------+-----------
> 260085 | 1 | SUNDAY
> 260085 | 2 | MONDAY
> 260085 | 3 | TUESDAY
> 260085 | 4 | WEDNESDAY
> 260085 | 5 | THURSDAY
> 260085 | 6 | FRIDAY
> 260085 | 7 | SATURDAY
>
> And this is the created enum:
>
> public enum Weekday implements org.jooq.EnumType {
>
> FRIDAY("FRIDAY"),
>
> MONDAY("MONDAY"),
>
> SATURDAY("SATURDAY"),
>
> SUNDAY("SUNDAY"),
>
> THURSDAY("THURSDAY"),
>
> TUESDAY("TUESDAY"),
>
> WEDNESDAY("WEDNESDAY"),
>
> ;
>
> Apparently, the query used by jOOQ to create the enum is using "order by
> enumlabel asc",
> not "order by enumsortorder". Therefore, the order of the enums are not in
> sync.
>
> This is a problem as soon you try something like :
>
> final static int SUNDAY = 1;
> Weekday.values()[SUNDAY];
>
> Sure, adding another constant to represent SUNDAY would be stupid, but if
> you use the
> Calendar class, you want to map your Calendar.SUNDAY somehow to the
> Weekday.SUNDAY.
> If they would be in the right order, Weekday.SUNDAY-1 would do the trick.
>
> As they are not in the right order, I have to pull some dirty tricks. And
> I am using jOOQ to
> get rid of dirty tricks and keep my database and my javacode "in sync".
>
> Is there any chance that jOOQ will produce the enums in the right order,
> is there a flag or
> configuration option, or is that something I will have to accept for the
> future ?
>
> Best,
> Patrik
>
>
> --
> 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/groups/opt_out.
>
--
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/groups/opt_out.
