enum type, point to the same class.

[Pay Liu]

Dear, 

I have a question about ENUM type of mySQL. (ref: UDT's including ARRAY and ENUM types)

I'm using  the same ENUM type defined in several tables. for instance, 

a column, its name is "priority", and its enum values are 'Low', 'Normal', 'High'

and this 'priority' column appears in TableOne and TableTwo.

The Enum Class from the code generator are "TableOnePriority" and TableTwoPriority".

These two class have the same content, but enum class name.

Is there a way to generate the same ENUM Class when using JOOQ code generator?

Thanks!

-Pay

[Lukas Eder]

Hello Pay,

> Is there a way to generate the same ENUM Class when using JOOQ code
> generator?

In jOOQ 2.0.1, I have added experimental support for custom-defined
enums in the source code generator. Here is the relevant release
announcement:
https://groups.google.com/forum/?fromgroups#!searchin/jooq-user/enum/jooq-user/rxMlFfu7LZk/KzIyDZ7NqKMJ

Unfortunately, not all integration tests for custom-defined enums pass
and I have not yet found the time to fix those failing tests yet. That
is why this feature is not yet officially documented.

For MySQL, however, most integration tests run smoothly. So if you're
willing to beta-test this, you can give it a shot! There is an example
jooq-codegen configuration here:
https://github.com/lukaseder/jOOQ/blob/master/jOOQ-test/configuration/org/jooq/configuration/lukas/mysql/library.properties

The two essential lines you'll have to add to your code generation
configuration properties file are these:

# Declare your own custom enum type (doesn't have to exist in the database)
# In this case, the "synthetic" type is called BOOLEAN_YN_UC
# and its values are Y and N (a comma-separated list of values)
generator.database.enum-type.BOOLEAN_YN_UC="Y",N

# Force that custom enum type upon all columns / parameters / attributes
# that match a given regular expression. In this case,
# table T_BOOLEANS
# column Y_N_UC
generator.database.forced-type.BOOLEAN_YN_UC=(?i:(.*?\.)?T_BOOLEANS\.Y_N_UC)

Any feedback is welcome!

Cheers
Lukas

[Pay Liu]

Dear Lukas,

thanks. it's working. and here is my suggestion. 

(1)  I also need to  exclude the column matching the forced-type.

the enum column that I force is generating to a custom Enum class. 

But the original enum class is still generated. That original one is unused any more, 

So I tried to exclude it. 

for example:

generator.database.excludes=T_BOOLEANS\.Y_N_UC

my feedback is ~

maybe the enum column could be excluded automatically when it'd be forced.

(2) naming convention is changed.

For example:

generator.database.enum-type.MyPriorityLevel="Low","Normal", "High"

my expected Enum Name is "MyPriorityLevel"

but the enum name is generated to be "Myprioritylevel"

My feedback is

Could the custom enum typ keep User's naming convention?

Thanks.

-Pay.

[Lukas Eder]

Hello Pay,

Thanks for your feedback! Glad it works for you

> (1)  I also need to  exclude the column matching the forced-type.

Yes, I'll file this as a bug:
https://sourceforge.net/apps/trac/jooq/ticket/1137

In MySQL, the orginial enum type can then be omitted.

> (2) naming convention is changed.

> generator.database.enum-type.MyPriorityLevel="Low","Normal", "High"

Write any of these:

generator.database.enum-type.MY_PRIORITY_LEVEL="Low","Normal", "High"
generator.database.enum-type.my_priority_level="Low","Normal", "High"

This behaves just like any other database artefacts, where
underscore_separated_words are transformed to CamelCase by
jOOQ-codegen

Cheers
Lukas

[Pay Liu]

Dear Lukas, 

very thanks!! Now, I'm having CamelCase for enum class name. very nice! 

And I have another idea about ENUM class. 

(1) First, refer to Master Data Generation of JOOQ. This is a powerful function. I'm using it, too.

     The enum class of Master Data have an ID number. This ID number is useful and convenient for parameter in programming.

(2) In mySql, the index of enum type is beginning with 1. (ref link) we can get the same effect by using index value or enum value. 

      

    For instance: 

         (A) select * from someTable where priority_level = 1;

         (B) select * from someTable where priority_level = 'Low';

    (A) and (B) get the same data.

What I'm thinking ...is it possible to get a enum Type Class with ID number, like below....

public enum MyPriorityLevel implements org.jooq.MasterDataType<java.lang.Integer> {

 

    Low(1, "Low"),

    Normal(2, "Normal"),

    High(3, "High");

     .....

}

thanks!

-Pay

 

[Lukas Eder]

Hi Pay

> very thanks!! Now, I'm having CamelCase for enum class name. very nice!

Glad it works!

>     For instance:
>          (A) select * from someTable where priority_level = 1;
>          (B) select * from someTable where priority_level = 'Low';
>
>     (A) and (B) get the same data.

I didn't know about this feature in MySQL.

> What I'm thinking ...is it possible to get a enum Type Class with ID number,
> like below....

Java's Enum class provides an ordinal() method. For instance,

MyPriorityLevel.Low.ordinal() == 0
MyPriorityLevel.Normal.ordinal() == 1
MyPriorityLevel.High.ordinal() == 2

The difference is that Java starts counting at 0, whereas MySQL starts
at 1, 0 being reserved for "NULL". Would that work for you? Other than
that, could you show me a use-case where such a synthetic ID is
useful? MySQL is the only database that has this feature, so I'm a bit
reluctant to add support for it in this way...

Cheers
Lukas

[Pay Liu]

Dear Lukas, 

Let me think how to describe that use-case....

my running project is presented by java web. Sometime, I need to pass param by ID in POST or GET Data. 

such as:

http://foo.com/showPriority?level=1 

In fact, I thought problem is not on ID. 

ENUM Class don't support valueOf(Integer ID), still need another custom function for reverse ID to Enum type. 

http://stackoverflow.com/questions/5316311/java-enum-reverse-look-up-best-practice

I'm used to create this kind of reverse function inside ENUM Class.

Otherwise, for the Enum class from JOOQ, I'd create an utility class for looking up the ID of enum type.

Or give me a right direction, :P

many thanks~

- Pay

[Lukas Eder]

Hello Pay,

> Let me think how to describe that use-case....
>
> my running project is presented by java web. Sometime, I need to pass param

> by ID in POST or GET Data. [...]

I understand.

> I'm used to create this kind of reverse function inside ENUM Class.
> Otherwise, for the Enum class from JOOQ, I'd create an utility class for
> looking up the ID of enum type.

I'd favour this solution over code generation for this specific case.
Right now, you can do this:
MyPriorityLevel.values()[index - 1]

But you probably want some fail-safety for wrong indexes and some null
handling. So what do you think about this implementation in
org.jooq.util.mysql.MySQLFactory:

------------------------------------------------
public static <E extends java.lang.Enum<E> & org.jooq.EnumType> E
enumType(Class<E> type, int index) {
if (index <= 0) {
return null;
}

E[] values = type.getEnumConstants();
if (index > values.length) {
return null;
}

return values[index - 1];
}
------------------------------------------------

Another option might be to consider Minuteproject for source code
generation. It has some nice enhancement for enums too:
http://minuteproject.wikispaces.com/JOOQ#toc8

This is a third-party product that has started to integrate with jOOQ
recently. You'd be a beta tester :-)

Cheers
Lukas

[Pay Liu]

Dear Lukas, 

You're right. not all RDBMS support ENUM type. It's not good idea to let ENUM class have ID value. 

The number of enum type in mySql is beginning with 1. Maybe other RDBMS would not. even no support number.....

(I'm not good on PostgreSQL. From Document, PostgreSQL seems not to support query by ID...)

But, you still also give me a good Generic utility from mySqlFactory, thanks!

I think it's good enough. I totally agree on this utility for mySql.

I took a look at "MinuteProject 4 JOOQ". Its enum type seem not to support looking-up ID. or I missed something.

Have a good day!

-Pay.

[Lukas Eder]

Hi Pay,

> (I'm not good on PostgreSQL. From Document, PostgreSQL seems not to support
> query by ID...)

I'm not aware of this, but I'm not so experienced with Postgres either

> But, you still also give me a good Generic utility from mySqlFactory,
> thanks!
> I think it's good enough. I totally agree on this utility for mySql.

This will be implemented in jOOQ 2.0.5:
https://sourceforge.net/apps/trac/jooq/ticket/1152

> I took a look at "MinuteProject 4 JOOQ". Its enum type seem not to support
> looking-up ID. or I missed something.

MinuteProject allows you to use custom velocity templates for source
code generation, so you can customise your generated source. I have to
try it myself too, though

Cheers
Lukas

[Christopher Deckers]

Hi Lukas, Pay,

> You're right. not all RDBMS support ENUM type. It's not good idea to let
> ENUM class have ID value.
> The number of enum type in mySql is beginning with 1. Maybe other RDBMS
> would not. even no support number.....

I am not sure I understand the issue here, but it may be similar to
something we do.

In our database, we store all sorts of enumerations as numbers.
There are no particular checks in the database that enforces valid
values, but they are manipulated as Java enums in the application. Our
enums may contain sequential or non-sequential values, in some cases
may have negative values, holes, null allowed. The Java enum enforces
those conditions.

Here is a standard enumeration, with a simple numbering logic:

public enum CivilStatus implements NumberBasedEnum {

SINGLE(1, "Single"),
MARRIED(2, "Married"),
DIVORCED(3, "Divorced"),
WIDOW(4, "Widow"),

;

private static EnumRegistry<CivilStatus> registry = new
EnumRegistry<>(CivilStatus.class);

private String name;

private CivilStatus(int intValue, String name) {
this.name = name;
if(ordinal() != intValue - 1) {
throw new IllegalStateException("The ordinal value must be
the same as the constant value - 1!");
}
}

@Override
public int intValue() {
return ordinal() + 1;
}

@Override
public String toString() {
return name;
}

public static CivilStatus getEnum(int enumvalue) {
return registry.getEnum(enumvalue);
}

}

The interface is very simple:

public interface NumberBasedEnum {
public int intValue();
}

The EnumRegistry class builds an internal map using
"EnumSet.allOf(enumClass)". It even has other methods like "public T
getEnum(String iName)". It also validates that intValues are unique.

We have about 200-300 such enums, so if jOOQ allowed us to say that
column X of table Y should be mapped to a number-based enum, it would
be excellent. I guess it would mean adding the getEnum(int) method to
the super interface, and maybe have to generify that super interface
but these are implementation considerations.

Appologies if I misunderstood and was off-topic.

Cheers,
-Christopher

[Lukas Eder]

Christopher,

> Appologies if I misunderstood and was off-topic.

This isn't off-topic. You're right on track.

> We have about 200-300 such enums, so if jOOQ allowed us to say that
> column X of table Y should be mapped to a number-based enum, it would
> be excellent. I guess it would mean adding the getEnum(int) method to
> the super interface, and maybe have to generify that super interface
> but these are implementation considerations.

This will be possible in some future version. But as I previously
mentioned in this thread, jOOQ's type system isn't ready for such
mapping yet, hence I'm not officially supporting these undocumented
features. But I can see the need for it.

Implementation hints / contributions are very welcome!

Cheers
Lukas

[Christopher Deckers]

> jOOQ's type system isn't ready for such
> mapping yet, hence I'm not officially supporting these undocumented
> features.

What about:

1. A mandatory super interface for all enumerations that jOOQ can recognise:
public static interface NumberBasedEnum<V extends Enum<V>> {
public int intValue();
public V getEnum(int intValue);
}

2. Add a method in SQLDataType to expose creating such enum types:
public static <X extends NumberBasedEnum<?>> SQLDataType<X>
createNumberBasedEnumType(Class<X> enumClass) {
return new SQLDataType<>(enumClass, enumClass.toString());
}

3. Generate a field with that type according to some user
configuration (or at least user can create its own generator):
public final org.jooq.TableField<test.generated.tables.records.SomeEnum,
TestEnum> SOMEENUM = createField("SomeEnum",
SQLDataType.createNumberBasedEnumType(TestEnum.class), this);

Where TestEnum is an enum like I explained in my previous e-mail:

public enum TestEnum implements SQLDataType.NumberBasedEnum<TestEnum> {
// Constructors can specify non-clashing IDs
A,
B,
;
@Override
public int intValue() {
// Whatever logic to return intValue
return ordinal();
}
@Override
public TestEnum getEnum(int intValue) {
// Whatever logic to find the enum, could be based on registry
return values()[intValue];
}
}

If the SQLDataType mapped Java type is a NumberBasedEnum, use that
info in jOOQ code to "intValue()" or "getEnum(intValue)".

Hope this helps,
-Christopher

[Lukas Eder]

> What about:
>
> 1. A mandatory super interface for all enumerations that jOOQ can recognise:

That would be EnumType, then:
http://www.jooq.org/javadoc/latest/org/jooq/EnumType.html

> public static interface NumberBasedEnum<V extends Enum<V>> {
>    public int intValue();
>    public V getEnum(int intValue);
> }

I think, intValue() would have to be generic...:

public interface EnumType<T> {
public T getLiteral(); // The existing literal corresponds to intValue()
}

public V getEnum() would have to be made static and moved to some
registry. It doesn't make sense in an interface, I think.

> 2. Add a method in SQLDataType to expose creating such enum types:

That would correspond to DataType.asEnumDataType():
http://www.jooq.org/javadoc/latest/org/jooq/DataType.html#asEnumDataType%28java.lang.Class%29

> 3. Generate a field with that type according to some user
> configuration (or at least user can create its own generator):
> public final org.jooq.TableField<test.generated.tables.records.SomeEnum,
> TestEnum> SOMEENUM = createField("SomeEnum",
> SQLDataType.createNumberBasedEnumType(TestEnum.class), this);

You're a magician! That would then correspond to that aforementioned
undocumented feature! Check out <enumTypes/> and <forcedTypes/> tags
in jOOQ 2.0.4's code generation configuration:
http://www.jooq.org/xsd/jooq-codegen-2.0.4.xsd

> Hope this helps,

Well... :-)
Seeing that you have very similar ideas is promising, as I am probably
on the right track with this existing undocumented feature. These are
the missing things:

- The "base type" for jOOQ's EnumType is lost, i.e. the synthetic
EnumType has no reference to its original DataType or to the generic
type <T>. This means, only VARCHAR enum types work right now (and some
others in lenient, "forgiving" databases)
- EnumType only knows its "literal". There is currently now way to add
another "synthetic index" as you and Pay propose.

Cheers
Lukas

PS: For further reference, this was the original discussion that lead
to these features:
https://groups.google.com/forum/#!topic/jooq-user/AC4ZbtBIQ6A/discussion

[Christopher Deckers]

> I think, intValue() would have to be generic...:

It depends. If it is, then you end up with Integer types for something
that cries for int primitive types (very common). In my case, I would
have to re-implement all my enums (200-300) which I would not do.

Let's be practical. In the world, there are only few real enumeration types:
- int values.
- String values.
- Eventually char values (e.g.: Y/N), which are like Strings of length one.

I don't deny that someone could make an EnumType<Date>, but I guess
you get my point :)

> public interface EnumType<T> {
>  public T getLiteral(); // The existing literal corresponds to intValue()
> }

Having answered my feeling on such a signature for int values, I will
elaborate on the impact of user code. Considering that these are enums
used in actual user code, I can bet that we would soon see:
if(myEnum.getLiteral() != null) {
// Do something.
}
because the problem with type wrapper is that they convey the null
possibility. I am sure some integrists would even go as far as using:
myEnum.getLiteral().intValue()

> public V getEnum() would have to be made static and moved to some
> registry. It doesn't make sense in an interface, I think.

True of course, I was too quick crafting the code.
My DB enums all have a static getEnum(int) method which is within the
enum itself. It would be more desirable to make closer the enum and
its lookup code rather than having to build a central registry.

Maybe you could have a contractual API: if a getEnum method is
available through reflection (loaded only once per enum and cached so
not a performance issue), then this API is used. If not, it would
iterate on the values() until intValue() returns the expected one.
Performance improvements would just be a matter of implementing the
getEnum method. Alternatively (or in addition if the getEnum method is
not declared), jOOQ could iterate once per enum and establish that
mapping once and for all, though I don't know if that can have nasty
performance issues in practical case (I doubt it).

> - The "base type" for jOOQ's EnumType is lost, i.e. the synthetic
> EnumType has no reference to its original DataType or to the generic
> type <T>. This means, only VARCHAR enum types work right now (and some
> others in lenient, "forgiving" databases)

Hence the practical approach I described:
- VARCHAR enums -> String-based enums
- NUMBER enums -> int-based enums
- CHAR enums -> char-based enums.

-Christopher

[Lukas Eder]

Hello Christopher,

Sorry for the delay. This E-Mail was lost in my mailbox

> Let's be practical. In the world, there are only few real enumeration types:
> - int values.
> - String values.
> - Eventually char values (e.g.: Y/N), which are like Strings of length one.

You're probably right. And since jOOQ doesn't support
Field<Character>, CHAR(1) and VARCHAR(1) and similar are also String
values. Thinking about it, the JPA @Enumerated annotation only
distinguishes between ordinal and string enum literals, too:
http://docs.oracle.com/javaee/6/api/javax/persistence/Enumerated.html

Speaking of which, this annotation should be rendered in generated
POJOs with <pojos/> and <jpaAnnotations/> both set to true. That's
https://sourceforge.net/apps/trac/jooq/ticket/1197

> Maybe you could have a contractual API: if a getEnum method is
> available through reflection (loaded only once per enum and cached so

> not a performance issue), then this API is used. [...]

This feels a bit fuzzy. I'd like to re-use the existing EnumType
interface and its getLiteral() attribute, even if itis nullable for
Integer wrappers. In fact there's nothing wrong with that, as NULL is
an acceptable enumeration value in SQL and jOOQ doesn't distinguish
nullable and not-nullable fields, so far.

Other options than making EnumType<T> generic are likely to introduce
incompatibilities for my MySQL and Postgres users, where true enums
(as opposed to synthetic ones) already exist. Besides, there can still
be static lookup methods outside of the enum, as implemented for Pay
in MySQLFactory.

> If not, it would
> iterate on the values() until intValue() returns the expected one.
> Performance improvements would just be a matter of implementing the
> getEnum method. Alternatively (or in addition if the getEnum method is
> not declared), jOOQ could iterate once per enum and establish that
> mapping once and for all, though I don't know if that can have nasty
> performance issues in practical case (I doubt it).

Enum.valueOf() works in similar ways. It maintains a global dictionary
that is maintained in java.lang.Class.enumConstantDirectory. I can get
some inspiration there...

Cheers
Lukas

[Christopher Deckers]

Hi Lukas,

>> Let's be practical.

> You're probably right.

but

> I'd like to re-use the existing EnumType
> interface and its getLiteral() attribute

So the real question is: do you want jOOQ to influence the global
design of an application or do you want jOOQ to help interfacing with
the database without being too intrusive in the application that is
using it? I can tell you that enumerations propagate far from the
simple database layer.

If you want to influence the application design, then of course you
can make up the rules.
If you want to help users simplify their database access, then re-use
their enums. If that means the generator should create a converter,
where users can define the rules for the bridge, then why not.

In practice, people build int enums the same way throughout an
application so generating a bridge would be simple. These enums tend
to have simple logic and once used in a program that rule cannot
change (use of the intValue() method to switch/case, or null checks if
toInteger(), etc.). I bet no jOOQ user has ever named that method
getLiteral(): they are not library makers but library users :)

Personaly, my team would never migrate to a scheme that mandates
changing the enumeration logic. This is too risky for our application
for various reasons. The most tolerable intrusion would be renaming
the method that returns the int value and/or implement an interface,
though "getLiteral" would not mean much to anyone which complexifies
the code.

Let me expand on a hypothetical generator-based solution, which I
think could cover all enum types (String, int, Integer, Date if you
like, etc.).

1. Say we have this xx.SomeEnum declaration in a codebase:
public enum SomeEnum {
public intValue() {...}
public static SomeEnum getEnum(int x) {...}
}

2. Then specify to the generator the logic for each enum:
enum: xx.SomeEnum
valueType: int
valueGetter: intValue
enumLookup: getEnum
sqlDataType: SMALLINT

3. I declare for the generator the column mapping of that enum:
TableX.ColumnY: xx.SomeEnum

4. The generator builds a central lookup class to convert the value
to/from an object.
This would be used by jOOQ when using/retrieving a value to/from the DB.

public class EnumLookup {
private final Map<Enum, EnumMapper<>> map = new HashMap<>();
public EnumLookup() {
map.put(SomeEnum.class, new EnumMapper<SomeEnum, Integer> {
public SomeEnum toEnum(Integer x) {
return SomeEnum.getEnum(x); // Autobox
}
public Integer fromEnum(SomeEnum e) {
return e == null? null: e.intValue(); // Autobox
}
public SQLDataType getDataType(SomeEnum e) {
return SQLDataType.SMALLINT;
}
}
}
public <T extends Enum<T>> EnumMapper<T, Object> getMapper(Class<T> clazz) {
return map.get(clazz);
}
}

Using the EnumMapper interface:

interface EnumMapper<T extends Enum<T>, U> {
public T toEnum(U x);
public Integer fromEnum(T e);
public SQLDataType getDataType(T e);
}

5. Use SomeEnum in field signatures

public final org.jooq.TableField<test.generated.tables.records.SomeRecord,
SomeEnum> MY_ENUM = createField("MyEnum",
SQLDataType.createEnumType(SomeEnum.class), this);

6. Use the lookup when marchalling/unmarshalling the enum.

This work for any type of literal, whether they are object or
primitive type. Bonus: we have a central place where we see all enum
declarations and how they get translated.

Other things could get added to the generator description like whether
a null enumeration is allowed (when an object literal is used).

> Other options than making EnumType<T> generic are likely to introduce
> incompatibilities for my MySQL and Postgres users, where true enums
> (as opposed to synthetic ones) already exist.

I am sure the mapper solves these kind of issues, by boxing/unboxing
(basically adapting) primitive to objects.

> Besides, there can still
> be static lookup methods outside of the enum, as implemented for Pay
> in MySQLFactory.

Maybe my naive generator descriptor should be enhanced to support
defining a helper class from which to look up if not directly
available on the enum itself. I believe this can be solved with the
above solution.

> Enum.valueOf() works in similar ways. It maintains a global dictionary
> that is maintained in java.lang.Class.enumConstantDirectory. I can get
> some inspiration there...

Yes, and EnumLookup (or rather, the mapper if it is turned into an
abstract class), could aggregate the values in a map at the first call
to avoid going many times to potentially avoid calling many times
badly implemented implementations of getEnum(x).

Please let me know what you think!
-Christopher

[Lukas Eder]

Hello Christopher,

> If you want to influence the application design, then of course you
> can make up the rules.

That is not a goal, but a consequence from the current design, as you've noticed.

> If you want to help users simplify their database access, then re-use
> their enums.

That would be very nice.

> If that means the generator should create a converter,
> where users can define the rules for the bridge, then why not.

Generating the converter... Why didn't we talk about that before! Finally, the idea that convinces me! :-)
This will circumvent the need for naming conventions / marker interfaces / annotations / other means enum recognition. Let me review your steps with that in mind

> 1. Say we have this xx.SomeEnum declaration in a codebase:

Fine.

> 2. Then specify to the generator the logic for each enum:
> enum: xx.SomeEnum
> valueType: int
> valueGetter: intValue
> enumLookup: getEnum
> sqlDataType: SMALLINT

How about this instead:
  enum: xx.SomeEnum
  sqlDataType: SMALLINT
  valueGetter: intValue (optional, defaults to Enum.name(), Enum.ordinal(), depending on sqlDataType)

valueType is not needed as the type is sufficiently defined by SMALLINT. From both your code as well as from mine, below, I don't see a need for a distinction between int/Integer, short/Short, etc
Also, the enumLookup is not needed, as the generated lookup class could maintain its own generated reverse lookup.

> 3. I declare for the generator the column mapping of that enum:
> TableX.ColumnY: xx.SomeEnum

Yes. With regular expression support as usual.

> 4. The generator builds a central lookup class to convert the value
> to/from an object.
> This would be used by jOOQ when using/retrieving a value to/from the DB.

That's a good start. How about this alternative here (checked with an Eclipse Java 6 compiler):

This is the same as you proposed:
----------------------------------------------------------------
interface EnumMapper<E extends Enum<E>, T> {
   E toEnum(T x);
   T fromEnum(E e);
   DataType<T> getDataType(E e);
}

enum SomeEnum {
   A(1),
   B(2),
   C(3);
   private final int val;
   private SomeEnum(int val) {
       this.val = val;
   }
   public int intVal() {
       return val;
   }
}
----------------------------------------------------------------

The lookup is slightly different. If jOOQ can generate things, why not generate a static final mapper instance for every configured enum? This instance is globally accessible, just like any other generated artefact.
----------------------------------------------------------------
public class EnumLookup {
   public static final EnumMapper<SomeEnum, Integer> SOME_ENUM_MAPPER = new SomeEnumMapper();

   private static final class SomeEnumMapper
   implements EnumMapper<SomeEnum, Integer> {
       // No need to provide jOOQ with a static method
       // reference for the reverse lookup. jOOQ-generated
       // mappers can calculate that for themselves.
       // Unless I'm missing something?
       final Map<Integer, SomeEnum> reverseLookup =
           new HashMap<Integer, SomeEnum>();

       public SomeEnumMapper() {
           for (SomeEnum e : SomeEnum.values()) {
               // intVal() is known to be the relevant method
               reverseLookup.put(e.intVal(), e);
           }
       }

       @Override
       public SomeEnum toEnum(Integer x) {
           return reverseLookup.get(x);
       }

       @Override
       public Integer fromEnum(SomeEnum e) {
           return e.intVal();
       }

       @Override
       public DataType<Integer> getDataType(SomeEnum e) {
           return SQLDataType.INTEGER;
       }
   }
}
----------------------------------------------------------------

Last but not least, the mapped field can reference the global static mapper. I'll surely find a way to get the mapper through to the relevant parts of jOOQ's type system.
----------------------------------------------------------------
final class TableX extends TableImpl<TableXRecord> {
    public static final TableX TABLE_X = new TableX();
    public final TableField<TableXRecord, SomeEnum> COLUMN_Y =
        createField(
            "COLUMN_Y",
            SQLDataType.INTEGER.asEnumDataType(
                SomeEnum.class,
                EnumLookup.SOME_ENUM_MAPPER),
            TABLE_X);
}
----------------------------------------------------------------

> This work for any type of literal, whether they are object or
> primitive type. Bonus: we have a central place where we see all enum
> declarations and how they get translated.

Exactly. It's a very nice approach! There's not even any reflection involved!

> Other things could get added to the generator description like whether
> a null enumeration is allowed (when an object literal is used).

No need to explicitly specify this. If intVal() were integerVal(), your SomeEnum class could return null for one enum value, which would be treated just the same as all other values in SomeEnumMapper's constructor, initialising the reverse lookup. toEnum(null) would then return X, when fromEnum(X) returns null

> I am sure the mapper solves these kind of issues, by boxing/unboxing
> (basically adapting) primitive to objects.

Right.

> Please let me know what you think!

I think this is the best solution possible. The key to success is to generate a mapper from only three pieces of information:
- SQL data type
- Enum class
- Optional enum value accessor method (defaulting to Enum.name() for VARCHAR types, Enum.ordinal() for NUMBER types)

The existing, experimental functionality can be kept as is. For some users, it's nice to have the "custom" enum class generated, too. The generated enum classes (both synthetic and true ones, from MySQL, Postgres) can live without that marker interface. The generated mapper replaces it.

What can I say? Thanks for insisting! :-)
I guess I can finally officially support this feature in the next minor release.

Please also review my suggestions for potential flaws, misunderstandings. I think I'll have time for a first draft implementation this Friday

Cheers
Lukas

[Christopher Deckers]

Hi Lukas,

After sending my e-mail, I thought of various ideas. I had one that
could simplify the whole issue of enums and more.

But first, let me explain something I found which is not about enums
but which happens to be covered by what I have in mind.
In our application, we do not use the sql Date objects. Instead, we
have our own class, and because of the number of dates we keep in
memory (think time series) we keep a cache of those dates.
When using jOOQ, we would have to manually transform our Date objects
to the sql dates when making a jOOQ call, and vice versa when parsing
the record.
What if jOOQ could apply a transformation not just for enums but also
on those other objects?

1. Let's start with a type mapper interface:

interface TypeMapper<T, U> {
public T to(U x);
public U from(T e);
public SQLDataType getDataType(T e);
}

2. In the generator, let's define certain columns of certain tables to
use type mapping:

MyTable1.MyDateColumn: com.xx.CustomDate
MyTable2.MyOtherDateColumn: com.xx.CustomDate
MyTable2.MyEnum: com.xx.SomeEnum

3. The generator declares the field as a mapped type.

public final org.jooq.TableField<test.generated.tables.records.MyDateColumnRecord,
CustomDate> MY_DATE_COLUMN = createField("MyDateColumn",
SQLDataType.createMappedType(CustomDate.class), this);

4. In the code, the user configures the Settings with the right binding:

Settings settings = new Settings();
TypeMapper customDateMapper = new TypeMapper<com.xx.CustomDate,
java.sql.Date>() {
public com.xx.CustomDate to(java.sql.Date d) {
return xxx;
}
public java.sql.Date from(com.xx.CustomDate d) {
return xxx;
}
public SQLDataType getDataType(T e) {
return SQLDataType.DATE;
}
};
settings.map(MyTable1.MyDateColumn, customDateMapper);
settings.map(MyTable2.MyOtherDateColumn, customDateMapper);
TypeMapper myEnumMapper = new TypeMapper<com.xx.SomeEnum, Integer>() {
public SomeEnum to(Integer i) {
return xxx;
}
public Integer to(SomeEnum e) {
return xxx;
}
public SQLDataType getDataType(T e) {
return SQLDataType.SMALLINT;
}
}
settings.map(MyTable2.MyEnum, myEnumMapper);

The mapping configuration is up to the user, and can be tweaked if
needed (cache handling, same custom type with different mapping logic
depending on column, etc.). The user can place the mapping code
wherever he wants, and can implement enums the way he likes because he
controls the mapping.
When jOOQ tries to map a value and a mapper cannot be found, an
exception is thrown. This is not a problem because to start using a
mapped type the user would have had to define the mapping in the first
place.

Please, let me know what you think of this version :)
-Christopher

[Lukas Eder]

> Please, let me know what you think of this version :)

What I think?
I think I'm about to re-implement Hibernate!

But I like your ideas, keep going!
So to put it short, the type-mapping configuration options would look like this:

Master data type:
- input: table/column/pk column
- output: generated enum with two value attributes
- applied to: all referencing foreign keys

Vendor-specific enum:
- input: DB-enum type
- output: generated enum with one value attribute
- applied to: all referencing columns

Synthetic enum:
- input: list of literals
- output: generated enum with one value attribute
- applied to: all columns/attributes/parameters matching a regex

Custom enum:
- input: enum type/data type/value attribute accessor
- output: enum type mapper
- applied to: all columns/attributes/parameters matching a regex

Custom type:
- input: custom type/data type/custom type mapper
- output: no specific output
- applied to: all columns/attributes/parameters matching a regex

Sounds nice!

[Christopher Deckers]

> What I think?
> I think I'm about to re-implement Hibernate!

True, there is a line to draw somewhere. But if you are ready to map
enums, then you could as well map any type. That would greatly
simplify the user's life if they can define the bridge between their
type and the SQL type and have the jOOQ table fields using that user
type.

> So to put it short, the type-mapping configuration options would look like this:

I am not sure why you make a specific distinction between custom enums
and custom types. A custom enum happens to be a custom type in my
view.

It is also unclear in your e-mail how the mapper is defined (in the
generator or by users in their code).
I tend to think that in-code definition is more flexible, allows all
sort of mapping rules (with caching, boxing, etc) than trying to
generate such code. Moreover, the user needs to establish the mapping
rules whatever approach but they do not need to refer to model
generation related files and do not need to re-generate if a
refactoring changes their internal enum/types mapping logic.

jOOQ could provide a default enum mapper for enums that implement a
particular interface and in which mapper a map is built for later
lookup.

With a code approach, their needs to be a clear place where to
establish the mappers. It could be registered in the Settings (like in
my previous e-mail), or it could also be in the field itself:

MyDateMapper myDateMapper = new MyDateMapper();
MyTable1.MyDateColumn.setMapper(myDateMapper);
MyTable2.MyOtherDateColumn.setMapper(myDateMapper);

TypeMapper myEnumMapper = new TypeMapper<com.xx.SomeEnum, Integer>() {
public SomeEnum to(Integer i) {
return xxx;
}
public Integer to(SomeEnum e) {
return xxx;
}
public SQLDataType getDataType(T e) {
return SQLDataType.SMALLINT;
}
}

MyTable2.MyEnum.setMapper(myEnumMapper);
// Assume MyJOOQEnumImpl implements JOOQEnum interface with getLiteral()
MyTable3.MyOtherEnum.setMapper(new JOOQEnumMapper(MyJOOQEnumImpl.class));

The above code would have to be called once when the application
starts and it would be the user's responsibility.

Consider my code where I have about 200-300 enums. I would have
200-300 mapping lines, but they would be the same: I would only have
to create my generic custom enum mapper (similar to the JOOQ enum
described above) and instanciate it for every enum. Very readible in
my view (don't forget that the field and the mapper are typed, so I
cannot plug a wrong mapper on that field).

Hope this helps,
-Christopher

[Lukas Eder]

> I am not sure why you make a specific distinction between custom enums
> and custom types. A custom enum happens to be a custom type in my
> view.

A mapper for enums can be generated by jOOQ with the slightly adapted
design you originally proposed. It will be useful to 80% of the users
wanting to provide jOOQ with meta-information about their custom enum
types. It will be very simple to configure and to use, there is little
effort involved.

A mapper for arbitrary custom types cannot be generated. You can still
consider your custom enums to be custom types and define your custom,
non-generated mappers though. This is for power-users wanting to go
much farther than just mapping simple enum types, like Pay (and
others) originally did.

Separating the two notions helps keeping things simple and focused for
enum types. I am not aware of any JPA implementation that has a simple
solution like the enum-mapper code generation like you originally
proposed it. However, many JPA implementations provide the "power-user
solution" for arbitrary mapping.

> It is also unclear in your e-mail how the mapper is defined (in the
> generator or by users in their code).
> I tend to think that in-code definition is more flexible, allows all
> sort of mapping rules (with caching, boxing, etc) than trying to
> generate such code.

That's why it is distinguished. If you don't need the flexibility for
enums, let jOOQ generate the enum mapper. If you need it, provide jOOQ
with a mapper reference.

> Moreover, the user needs to establish the mapping
> rules whatever approach but they do not need to refer to model
> generation related files and do not need to re-generate if a
> refactoring changes their internal enum/types mapping logic.

Then they shouldn't choose to generate mappers but go for the second
approach and provide jOOQ with a custom mapper reference.

> jOOQ could provide a default enum mapper for enums that implement a
> particular interface and in which mapper a map is built for later
> lookup.

That is not possible without reflection, because of the intValue()
method. That "default enum mapper" needs to know how to map an
instance of SomeEnum to its SomeEnum.intValue() and vice versa. Of
course, generated code will extend an AbstractEnumMapper class (as
opposed to simply implementing the interface), handling some base
logic, to keep generated code slim.

> With a code approach, their needs to be a clear place where to
> establish the mappers. It could be registered in the Settings (like in
> my previous e-mail), or it could also be in the field itself:

I prefer the Field. Or more specifically, the field's DataType. I can
see the more advanced use-case for replacing the default mapper by any
compatible mapper in the Settings.

> MyTable2.MyEnum.setMapper(myEnumMapper);
> // Assume MyJOOQEnumImpl implements JOOQEnum interface with getLiteral()
> MyTable3.MyOtherEnum.setMapper(new JOOQEnumMapper(MyJOOQEnumImpl.class));
> The above code would have to be called once when the application
> starts and it would be the user's responsibility.

I'd like to avoid delegating initialisation of jOOQ's internals to
client code. That can go terribly wrong. MyTable2.MyEnum is to be
considered an immutable column reference. It shouldn't be possible to
change its configured state like this. If mappers are referenced from
a Field's DataType, however, loading the generated classes takes care
of loading (and initialising) the generated mappers automatically.
Anyway, your idea and mine aren't contradictions...

> Consider my code where I have about 200-300 enums. I would have
> 200-300 mapping lines, but they would be the same: I would only have
> to create my generic custom enum mapper (similar to the JOOQ enum
> described above) and instanciate it for every enum. Very readible in
> my view (don't forget that the field and the mapper are typed, so I
> cannot plug a wrong mapper on that field).

Yes, ultimately, this is going to the right direction.

[Christopher Deckers]

Hi Lukas,

> A mapper for enums can be generated by jOOQ with the slightly adapted
> design you originally proposed. It will be useful to 80% of the users

I agree.

> A mapper for arbitrary custom types cannot be generated. You can still
> consider your custom enums to be custom types and define your custom,
> non-generated mappers though.

Good.
Flexibility here is good: use what the library provides, but for
advanced stuff there is a way.

> I prefer the Field. Or more specifically, the field's DataType. I can
> see the more advanced use-case for replacing the default mapper by any
> compatible mapper in the Settings.
>

> I'd like to avoid delegating initialisation of jOOQ's internals to
> client code. That can go terribly wrong. MyTable2.MyEnum is to be
> considered an immutable column reference. It shouldn't be possible to
> change its configured state like this. If mappers are referenced from
> a Field's DataType, however, loading the generated classes takes care
> of loading (and initialising) the generated mappers automatically.

I am not sure how you want to ensure that mappers get initialized
before the fields when dealing with custom types.

> Anyway, your idea and mine aren't contradictions...

No, I think we are on the same train of thought now, so I am going to
be less of a pain :)

Cheers,
-Christopher

[Lukas Eder]

Hello Christopher,

A first draft of this implementation is committed to SVN trunk and
GitHub. While implementing this, the general feeling of how the API
should look got a lot better. Essentially, the new org.jooq.Converter
type is stabilising:
https://github.com/lukaseder/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/Converter.java

Here are some facts:

1. I'm calling this type Converter rather than Mapper, as the notion
of "conversion" is already present in jOOQ's org.jooq.tools.Convert
and various other places. So this whole discussion is about custom
data type conversion.

2. I found that it is necessary for jOOQ's internals to get a hold of
both Java types involved in type conversion, i.e. the database type
(e.g. java.lang.Integer, java.sql.Date, etc) and the custom type
(com.example.MyEnum, com.example.MyDateType).

3. There are two elements to be configured in source code generation:
3.1. The "custom type" (e.g. MyEnum and MyEnumConverter)
3.2. The "forced type" (e.g. MyEnum and a regular expression matching
all columns upon which this forced type should be mapped). "Forced
types" can be used for other purposes, too. For example, forcing a
NUMBER(1) column to be java.lang.Boolean. This is currently still
experimental but should be working in 2.1.0
A sample configuration can be seen here:
https://github.com/lukaseder/jOOQ/blob/f53818504ef1d0345a8aae40fd29b7a9ccd6976a/jOOQ-test/configuration/org/jooq/configuration/lukas/hsqldb/library.xml

4. You were right, essentially, about custom enum types being a
special case of arbitrary custom types. As of now, they are treated no
differently. Some convenience in code generation may be added in the
next release, in order to be able to generate custom enum converters.
For now, there is EnumConverter, which can be extended by manually
written custom converters (overriding the to() method):
https://github.com/lukaseder/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/EnumConverter.java

5. Generated tables will use the custom type as bound for <T>. A
sample table can be seen here:
https://github.com/lukaseder/jOOQ/blob/f53818504ef1d0345a8aae40fd29b7a9ccd6976a/jOOQ-test/src/org/jooq/test/hsqldb/generatedclasses/tables/TMappedTypes.java

6. There is a central custom type registry providing a mapping between
MyEnum -> MyEnumConverter. This is the registry:
https://github.com/lukaseder/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/DataTypes.java

It is used by jOOQ to know how to bind and inline custom types in
SQL. A custom type can only be converted by a single converter,
globally. i.e. it is currently not possible to have MyEnum ->
Converter1 in one place, and MyEnum -> Converter2 in another. The
reason for this is, jOOQ needs an unambiguous registry in
"context-less" cases like this:
create.select(MyEnum.VAL).fetch();

This will be an open point. I'm curious about your feedback on
this topic. You suggested to use the Factory's Settings as an
(additional) Converter registry. This might be a viable workaround for
a future release, if several converters are really needed for the same
custom type. I feel it might be a bit overkill, though.

7. Fields (Parameters) hold direct references to their
"ConvertedDataType". This means that when fetching data from a
ResultSet (CallableStatement, SQLInput), this information is available
directly from the Field.

I feel this is on a good track!

Here's a preliminary, simplistic test-case for these converters. It
involves custom converters for enums and java.util.Date /
java.util.Calendar:
https://github.com/lukaseder/jOOQ/blob/f53818504ef1d0345a8aae40fd29b7a9ccd6976a/jOOQ-test/src/org/jooq/test/jOOQHSQLDBTest.java#L683

Next week, I'll create many more test cases for all sorts of queries
and I'll consolidate the existing experimental functionality.

> No, I think we are on the same train of thought now, so I am going to
> be less of a pain :)

You may speak up again :-)

Cheers
Lukas

[Christopher Deckers]

Hi Lukas,

1. I'm calling this type Converter rather than Mapper

I don't mind, I am fine with Converter. I will use mapper in the rest of this e-mail though to differentiate from type conversions performed by the RDBMS. You may need to think about it too to see if using converter is not going to confuse users and cause ambiguous discussions when troubleshooting user issues. The general sequence of a call to the RDBMS is:
x -> type mapping -> y -> type conversion -> z
 

2. I found that it is necessary for jOOQ's internals to get a hold of
both Java types involved in type conversion

What about my case where I have 2 back-ends (SQLServer and Oracle)? Let's say I have a BIT in one (Boolean) and a Number in the other (Short). For fields, you said that this would work because there is type conversion. But is this going to work with type mapping if I generate a model against one of the RDBMS and run on the other?

3.1. The "custom type" (e.g. MyEnum and MyEnumConverter)

Out of curiosity, can we augment an already existing MyEnum to implement the Converter interface so all code is located at a single place if we want it to? Would jOOQ allow it?

 

3.2. The "forced type" (e.g. MyEnum and a regular expression matching
all columns upon which this forced type should be mapped). "Forced
types" can be used for other purposes, too. For example, forcing a
NUMBER(1) column to be java.lang.Boolean.

Somehow this seems to answer my question above, but I just want to be sure :)

   A sample configuration can be seen here:
   https://github.com/lukaseder/jOOQ/blob/f53818504ef1d0345a8aae40fd29b7a9ccd6976a/jOOQ-test/configuration/org/jooq/configuration/lukas/hsqldb/library.xml

<literals>y,&quot;n&quot;</literals>
<literals>&quot;Y&quot;,N</literals>

Don't you mean:
<literals>&quot;y&quot;,&quot;n&quot;</literals>
<literals>&quot;Y&quot;,&quot;N&quot;</literals>

 

For now, there is EnumConverter, which can be extended by manually
written custom converters (overriding the to() method):
   https://github.com/lukaseder/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/EnumConverter.java

This looks good and very easy to use.

 

5. Generated tables will use the custom type as bound for <T>. A
sample table can be seen here:
   https://github.com/lukaseder/jOOQ/blob/f53818504ef1d0345a8aae40fd29b7a9ccd6976a/jOOQ-test/src/org/jooq/test/hsqldb/generatedclasses/tables/TMappedTypes.java

This also looks good!
 

6. There is a central custom type registry providing a mapping between
MyEnum -> MyEnumConverter. This is the registry:
   https://github.com/lukaseder/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/DataTypes.java

What would happen if one generated schema defines certain mappings, and another generated schema defines other mappings but for the same types (especially calendars or dates)? Running with one would be fine but could running with two result in collisions in the registry?

 

   It is used by jOOQ to know how to bind and inline custom types in
SQL. A custom type can only be converted by a single converter,
globally. i.e. it is currently not possible to have MyEnum ->
Converter1 in one place, and MyEnum -> Converter2 in another. The
reason for this is, jOOQ needs an unambiguous registry in
"context-less" cases like this:
   create.select(MyEnum.VAL).fetch();

It is context-less when talking about columns, but the schema is still something to consider (cf. my previous remark).

That being said, for a given schema I agree with you (and in practice, I don't know if multi-schema collisions for different mapping definitions would occur that often).
 

This will be an open point. I'm curious about your feedback on
this topic. You suggested to use the Factory's Settings as an
(additional) Converter registry. This might be a viable workaround for
a future release, if several converters are really needed for the same
custom type. I feel it might be a bit overkill, though.

I fully agree with you. I don't think it is needed...
 

7. Fields (Parameters) hold direct references to their
"ConvertedDataType". This means that when fetching data from a
ResultSet (CallableStatement, SQLInput), this information is available
directly from the Field.

Very good!

 

I feel this is on a good track!

Indeed! Now I need to find the time to actually generate a model with some custom mapping. I will try to do that during the week-end or begining of next week. I'll let you know when I get there if I have any particular remarks.


Keep up the good work! :)
-Christopher

[Christopher Deckers]

Hi Lukas,

I want to come back on something I said, now that the nights has passed and some thoughts came to my mind.

   It is used by jOOQ to know how to bind and inline custom types in
SQL. A custom type can only be converted by a single converter,
globally. i.e. it is currently not possible to have MyEnum ->
Converter1 in one place, and MyEnum -> Converter2 in another. The
reason for this is, jOOQ needs an unambiguous registry in
"context-less" cases like this:
   create.select(MyEnum.VAL).fetch();

MyEnum is a field, so if a converter is attached to the field definition somehow, then it is unambiguous.

Having different converters resolving to the same custom type is actually important and I have a practical test case.

In our code base, we store 3 types of dates with the same custom type (let's call it MyDateType):
- Dates, where null means a null date.
- Dates used as min dates of ranges, where null means -infinity.
- Dates used as max dates of ranges, where null means +infinity.

MyDateType has states to represent -infinity or +infinity. It is up to the converter to convert null to the right value. It is also up to the converter to convert from MyDateType to null for the appropriate values and reject by throwing an exception when value is forbidden in the field context (e.g.: passing +infinity for a min date).

If this cannot be supported, then it means we will have to pre/post process values with our own converters which defeats the purpose of this type mapping.

Please let me know what you think!
-Christopher

[Lukas Eder]

Hi Christopher,

>>> jOOQ needs an unambiguous registry in
>>> "context-less" cases like this:
>>>    create.select(MyEnum.VAL).fetch();
>
> MyEnum is a field, so if a converter is attached to the field definition
> somehow, then it is unambiguous.

I'm not sure what you mean. In my example, MyEnum is an enum type, and
VAL is a value of type MyEnum. In other words, this query corresponds
to

SELECT 'VAL' FROM DUAL

There is no "field" involved in this case. That's why a central
registry is needed here, to find a converter for the custom type
MyEnum, which is passed to jOOQ without "context"

> Having different converters resolving to the same custom type is actually
> important and I have a practical test case.
>
> In our code base, we store 3 types of dates with the same custom type (let's
> call it MyDateType):
> - Dates, where null means a null date.
> - Dates used as min dates of ranges, where null means -infinity.
> - Dates used as max dates of ranges, where null means +infinity.

I'm not sure if I understand this design. Why would you use NULL for
something other than NULL? That smells fishy to me. Can you show some
examples where this would be practicable?

Cheers
Lukas

[Lukas Eder]

Hello,

> What about my case where I have 2 back-ends (SQLServer and Oracle)? Let's
> say I have a BIT in one (Boolean) and a Number in the other (Short). For
> fields, you said that this would work because there is type conversion. But
> is this going to work with type mapping if I generate a model against one of
> the RDBMS and run on the other?

Once the schema is generated, a field's type will then be fixed to
Boolean for SQL Server's BIT. This will work with Oracle's NUMBER
field. You don't need a converter for that. If you want to generate
the schema from Oracle's NUMBER type, you'll have to apply a "forced
type" upon that field

> Out of curiosity, can we augment an already existing MyEnum to implement the
> Converter interface so all code is located at a single place if we want it
> to? Would jOOQ allow it?

Java enum types can implement any interface. I don't see any problem
with that. Beware that enum types cannot extend base classes for
convenience...

> What would happen if one generated schema defines certain mappings, and
> another generated schema defines other mappings but for the same types
> (especially calendars or dates)? Running with one would be fine but could
> running with two result in collisions in the registry?

The mappings' scope is not a single schema.

Cheers
Lukas

[Christopher Deckers]

Hi Lukas,

I'm not sure what you mean. In my example, MyEnum is an enum type, and
VAL is a value of type MyEnum. In other words, this query corresponds
to

   SELECT 'VAL' FROM DUAL

I think here is the mistake: only fields or converters should be used: VAL should not be allowed. The special case of DUAL would require the converter.

I am not sure what syntax it should look like, but using a type which can map to a single conversion logic will cause problems (there are actually several cases I thought about).
 

There is no "field" involved in this case. That's why a central
registry is needed here, to find a converter for the custom type
MyEnum, which is passed to jOOQ without "context"

Let's take the example I provided (we will discuss the "fishiness" of it later in this e-mail). Let's consider 3 converters:

MyRangeBeginDateConverter
MyRangeEndDateConverter
MyRangeDateConverter

If I read a value from dual, I may want the -infinity, +infinity, null, or a date depending on the business usage of that date. It is up to me to add the right conversion.

create.select(MyRangeBeginDateConverter.to(MyEnum.VAL)).fetch();

or something like that. Type checking should be enforced so that invalid combinations are prevented (like using the enum directly, etc).
Note that I know that I want a begin date for a range in the above example, so I don't think jOOQ should do hidden magic to resolve that value: explicitely using the appropriate converter makes sense.

 

I'm not sure if I understand this design. Why would you use NULL for
something other than NULL? That smells fishy to me. Can you show some
examples where this would be practicable?

We have the notion of a range, where [startDate, endDate] is so that a range can either be [-inf, date], [date1, date2] (date2 obviously greater than date1), and [date1, +inf]. null is not allowed because a range cannot have an unknown bound.
It is logical to store that range as 2 dates, and the inifinity is the same as a missing date (no lower/upper bound) but our code does not allow nulls for ranges. The RDBMS stores that null. Our code could handle a null, but our logic is so that non-null dates (-inf, +inf) work with all date comparisons without having to do any null checks. Those 2 dates are obviously of the same type, which happens to be the same type as our regular dates (which do not allow +-inf but allow nulls).
From a business point of view it makes sense, and I don't see what is wrong with this design.

Of course, we can discard that use case labeling it as "fishy" to simplify jOOQ's impementation, but I don't think it should work that way :)

-Christopher

[Christopher Deckers]

Hi Lukas,

> Out of curiosity, can we augment an already existing MyEnum to implement the
> Converter interface so all code is located at a single place if we want it
> to? Would jOOQ allow it?

Java enum types can implement any interface. I don't see any problem
with that. Beware that enum types cannot extend base classes for
convenience...

I know. My innocent question was more like: are you sure that the names you chose in your Converter class cannot clash with user-enums so that they can safely implement this interface?
to() and from() may already exist because these are common terminology, whereas toSQLType()/fromSQLType() are unlikely to exist in user enums, and they can even call them or know what these methods do from their name.

 

> What would happen if one generated schema defines certain mappings, and
> another generated schema defines other mappings but for the same types
> (especially calendars or dates)? Running with one would be fine but could
> running with two result in collisions in the registry?

The mappings' scope is not a single schema.

which means collisions.

-Christopher

[Christopher Deckers]

Hi Lukas,

Let's assume there is no central repository, and fields know their mapper. Let's also consider that with custom types you need to specify the converter for things like "select X from dual".

The normal flow is:
x -> mapping -> y -> conversion -> z

I think I already mentioned another application of type mapping some time ago: data validation.

Mapping can check that values are coherent and can raise exceptions. If I take the case of my dates in ranges, a business rule is so that "MyRangeBeginDateConverter" must refuse null or +inf Java values and the Java to SQL method must raise an exception in this illegal state.
Another similar example: let's consider an enum that is used at different places, and which is stored in the database as numbers. Maybe certain of its usage would not accept certain values: a dedicated converter for those columns may perform this validation to protect against inserting silly value (and yes, in some cases we don't want to add more constraints in the database and want to have application specific checks).

x -> mapping + checks -> y -> conversion -> z

In fact, a corolary is that the mapping stage may decide to not map at all, but only apply business rules: when data is converted to SQL, it can throw an exception if that data is not acceptable business wise.
y -> check -> y -> conversion -> z

I think having the possibility of applying simple business validation of values when going into and from jOOQ directly at the mapping stage is a nice feature and removes the burden of external checks everytime a particular column is accessed. It is also simple to understand and does not require an additional dedicated API.

Hope this helps,
-Christopher

[Lukas Eder]

> I know. My innocent question was more like: are you sure that the names you
> chose in your Converter class cannot clash with user-enums so that they can
> safely implement this interface?
> to() and from() may already exist because these are common terminology,
> whereas toSQLType()/fromSQLType() are unlikely to exist in user enums, and
> they can even call them or know what these methods do from their name.

That's life with implementing interfaces... Check out
java.lang.Runnable. It ships with run()...

Anyway, maybe from() and to() aren't the best choices. I found them
somewhat confusing and difficult to explain when writing the Javadoc.
So this discussion is maybe not even necessary... ;-)

[Lukas Eder]

> Of course, we can discard that use case labeling it as "fishy" to simplify
> jOOQ's impementation, but I don't think it should work that way :)

OK, I can see how NULL can be used to model +/- inf now

[Lukas Eder]

> I think having the possibility of applying simple business validation of
> values when going into and from jOOQ directly at the mapping stage is a nice
> feature and removes the burden of external checks everytime a particular
> column is accessed. It is also simple to understand and does not require an
> additional dedicated API.

I think the current design isn't entirely orthogonal to what you are
now planning to think about. Anyway, using converters/mappers also as
validators is up to you. jOOQ will be oblivious of these things and
propagate your custom exception wrapped in an
org.jooq.exception.DataTypeException...

Yet, I have to re-explain about jOOQ applying some magic with a
central registry. See, <T> is bound to MyEnum in generated code. This
means that all of jOOQ's API is concerned by this. My HSQLDB test case
mentions predicates:

T_MAPPED_TYPES.DEFAULT_ENUM_NAME.equal(StringEnum.A)

The equal method will use the bound of <T> to enforce compile-time
safety. It is not possible to compare such a field with String! This
is a simple example. You may argue that org.jooq.Comparison knows both
sides of the predicate, so it can "guess" the converter from the
left-hand side. But that's not a good move, as the bound of <T> can
propagate further, using expressions like:

nvl(T_MAPPED_TYPES.DEFAULT_ENUM_NAME, StringEnum.A).equal(StringEnum.A)

The Field.equal(T) method is a convenience method for
Field.equal(Field<T>) using Factory.val(T) internally to create the
bind value for the predicate's right-hand side. This is why I chose
the original example of selecting 'VAL' from dual. It *must* be
possible to create bind values for all custom types that are somehow
registered as user types in some type conversion. In jOOQ's internals,
the <T> type of all QueryParts *must* remain consistent. It will only
be converted when inlined in SQL or when bound as bind variable. Hence
there *must* be a "magic" central registry, because it is
programmatically impossible for jOOQ to resolve all custom types
through complex SQL expressions by accessing *some* involved field
which has a specific converter attached...

Solution for your use case, you have several options:
--------------------------------------------------------

1) Don't use jooq-codegen, to generate your user-defined date types as
bounds for <T>. You can still use jOOQ's converters and convert to
java.sql.Timestamp *before* binding, and convert to your type again in
the new Record <T, U> U getValue(Field<T> field, Converter<? super T,
U> converter) method *after* fetching (i.e. jOOQ will never see your
custom type)

2) Move that sort of mapping entirely out of jOOQ. Maybe there's a
much simpler solution, as this all feels super-overengineered (at
least to me...)

3) Create dedicated types for your 3 date types: NullableDate
(null=null), LowerBoundDate (null=-inf), UpperBoundDate (null=+inf).
All three extending some base type. Then you can use a dedicated
converter for each date type. This additional type-safety might even
be useful for your application's Java code

4) (probably not practicable): You can always use a non-null value to
represent -inf/+inf. Just like Integer.MIN_VALUE and Integer.MAX_VALUE
could be used for that.

[Christopher Deckers]

Hi Lukas,

I think the current design isn't entirely orthogonal to what you are
now planning to think about.

I am merely expanding on the converter idea to see how it can be used, abused, and eventually try to find new usages. I am just thinking out loud in case it triggers ideas for improvements in jOOQ.

Yet, I have to re-explain about jOOQ applying some magic with a
central registry.

I got it. I just wanted to express valid use cases with regards to the central-registry limitation in case you had ideas to tweak the current design.
 

Solution for your use case, you have several options:

1) Don't use jooq-codegen

This means using the converter explicitely externally. Of course this can be done, and any conversion mecanic can be used. It means that we explicitely use the raw SQL type.

2) Move that sort of mapping entirely out of jOOQ. Maybe there's a
much simpler solution, as this all feels super-overengineered (at
least to me...)

With the converter feature, you enter the realm of mapping business data to tables automatically. I never said everything could be done! But maybe slight modifications could allow more. You see, enforcing certain rules on the DB API (jOOQ exposed interface to the DB) can be very powerful, because you immediately know what you can do with less possibilities of errors when accessing/modifying data.
 

3) Create dedicated types for your 3 date types: NullableDate
(null=null), LowerBoundDate (null=-inf), UpperBoundDate (null=+inf).
All three extending some base type. Then you can use a dedicated
converter for each date type. This additional type-safety might even
be useful for your application's Java code

No because the end date can be used as a regular date in other contexts. Like "retrieve data at a date, which happends to be the end date of a particular time series, if not infinity". We don't want conversions because the DB mapping cannot cope with a single unified date with different constraints depending on the columns.
 

4) (probably not practicable): You can always use a non-null value to
represent -inf/+inf. Just like Integer.MIN_VALUE and Integer.MAX_VALUE
could be used for that.

Yes, there are many possible work arounds.


OK, let's sum up where we are.

- Central registry is needed because in complex queries we do not have a particular field to work with.
- Because registry is central, there cannot be different conversion rules.

For enumerations, this is of course not a problem, except if someone wanted to apply data validation in the converter depending on the field.

I have one more thought about mapping my date types: what if the to/from method of the converter had the name of the field as a parameter? Is that something you have in your context?
The name could be null (in case of VAL), or could be the name of the field (StartDate, EndDate, SomeDate), or even the reference to the field itself (though I doubt this is possible). This could be used by a user to change the conversion strategy depending on the field, provided they defined strict naming rules.
In my case for example, all start and end dates have the same name, whatever table is using them, because we have consistent names in our database schema.

I want to come back on this:

>> What would happen if one generated schema defines certain mappings, and
>> another generated schema defines other mappings but for the same types
>> (especially calendars or dates)? Running with one would be fine but could
>> running with two result in collisions in the registry?
> The mappings' scope is not a single schema.

Why wouldn't you generate the registry in a particular schema (or in the same package and referenced by a schema) instead of an independant class? That way, all from/to calls get the mappers from the schema and there would not be any risks of collisions. As an analogy, you don't generate tables of a schema in a central registry, but have this linked to a schema.

Cheers,
-Christopher

[Lukas Eder]

Hi Christopher,

> With the converter feature, you enter the realm of mapping business data to
> tables automatically. I never said everything could be done! But maybe
> slight modifications could allow more. You see, enforcing certain rules on
> the DB API (jOOQ exposed interface to the DB) can be very powerful, because
> you immediately know what you can do with less possibilities of errors when
> accessing/modifying data.

I agree, it can be powerful. I have a co-worker saying "jOOQ: A device
whose mystery is only exceeded by its power" :-)

> No because the end date can be used as a regular date in other contexts.
> Like "retrieve data at a date, which happends to be the end date of a

> particular time series, if not infinity". [...]

Now, this model is confusing me again ;-) But never mind. I begin to
realise that you have a subtle type system wrapped around dates that
is too hard to handle for jOOQ... That's why it's probably best to be
implemented (and validated) on a higher level.

> - Central registry is needed because in complex queries we do not have a
> particular field to work with.
> - Because registry is central, there cannot be different conversion rules.

Exactly.

> I have one more thought about mapping my date types: what if the to/from
> method of the converter had the name of the field as a parameter? Is that
> something you have in your context?

There is no "field" in the render / bind context. <T> can be bound
entirely out of context - recall select(val(MyEnum.A)). Hence, no name
available. Note, Fields / names are available in *certain* contexts,
but making that a general rule would be unwise. Such an API would
appear to be useless in most cases that are not very simple queries.

What comes to my mind now: For some well-defined cases, you could
actually use CustomCondition and CustomField and other custom types
from jOOQ:
http://www.jooq.org/manual/JOOQ/Extend/

When you create a CustomCondition, you can add whatever checks and
context you want in this condition.

> Why wouldn't you generate the registry in a particular schema (or in the
> same package and referenced by a schema) instead of an independant class?

For the same reason. The schema would be referenced by Fields /
Tables. Hence, I do not have any schema reference at render / bind
time. There is no schema in create.select(val(MyEnum.A)).fetch()

Look at custom types to be of an application-wide scope. Not to be
confused with user-defined types, which are declared on a schema-level
in the database itself...

Cheers
Lukas

[Christopher Deckers]

Hi Lukas,

 

Look at custom types to be of an application-wide scope. Not to be
confused with user-defined types, which are declared on a schema-level
in the database itself...

OK, I think I have covered all aspects, nothing else comes to my mind. So this is the rule: application-wide mapping.
If there are scope issues, maybe later a mapping definition could be attached to a factory or settings...

In the case of my dates, I think the best design from a jOOQ user perspective is to create 3 intermediate date types:
- JOOQDateType
- JOOQBeginDateType
- JOOQEndDateType

These 3 have different jOOQ converters and also have static methods on them to convert from/to my real business date types.
So, before calling jOOQ, and enforced by jOOQ type system, I have no choice but to use these 3 intermediate types. The static conversion method would perform the appropriate business checks.

It is effectively an external conversion, located at the time of calling jOOQ. It is better than using the SQL date type because it forces validation of certain business rules and forces the user of the API to provide appropriate types.

I think the current design is good!

Cheers,
-Christopher