Keys, fields, values and generics

[Thomas GILLET]

Hello all,

Happily using jOOQ for a while now, I'm starting to use it for a little more than just SQL generation and I'm struggling a little when working with keys (primary, unique, foreign).

First, the Key interface doesn't have any generic information about the key type: the only generic parameter represents the type of the owner table.
So for example, although both ends of a foreign key should have the same type, there is no type-safety to guarantee that. And more generally, it's problematic to write methods expecting a key with a certain type.

I tried other types to add genericity to my keys, like Row and its numbered friends, or jOOL tuples, but in the end it seems RecordType is more appropriate.
Its parameter being a single Record type rather than several data types, it's easier to work with. For example when extracting values of a key implementing RecordType<K>, I return a record of type K ; this can't be done with a Row, short of writing all 22 overloads.

But then sometimes I need to work with key values only, rather than key fields or a key record (for example when comparing values from both ends of a foreign key, I cannot compare full records since key columns may not have the same name).
Technically I could use RecordType again (since anyway all field containers are implemented on top of a Fields object), but judging from the name of the interface, its documentation, and the fact that DSL.recordType only take Field arguments, it doesn't feel like this type is meant to contain values.

So, to hold key fields:
- Use Key, adding a type parameter?
- Use RecordType? (but RecordType only contains Fields and not TableFields as do Key)
- Make Key extend RecordType ?

To hold key values:
- Create some new "RecordValue<R>" type, symmetrical to RecordType<R> ?

Or, giving up the difference between key fields and key values, use a single "RecordTuple<R>" type to hold anything?
Row could even extend this type (with each RowX<...> extending RecordTuple<RecordX<...>>).

--

After writing this long and mostly vague post, I realize I don't really know where I'm going with all that.
Hoping someone may see things a little more clearly... Please share!

[Lukas Eder]

Thank you very much for sharing your thoughts, Thomas.

2017-02-27 10:57 GMT+01:00 Thomas GILLET <thomas...@viveris.fr>:

Hello all,

Happily using jOOQ for a while now, I'm starting to use it for a little more than just SQL generation and I'm struggling a little when working with keys (primary, unique, foreign).

First, the Key interface doesn't have any generic information about the key type: the only generic parameter represents the type of the owner table.
So for example, although both ends of a foreign key should have the same type, there is no type-safety to guarantee that. And more generally, it's problematic to write methods expecting a key with a certain type.

I tried other types to add genericity to my keys, like Row and its numbered friends, or jOOL tuples, but in the end it seems RecordType is more appropriate.
Its parameter being a single Record type rather than several data types, it's easier to work with. For example when extracting values of a key implementing RecordType<K>, I return a record of type K ; this can't be done with a Row, short of writing all 22 overloads.

But then sometimes I need to work with key values only, rather than key fields or a key record (for example when comparing values from both ends of a foreign key, I cannot compare full records since key columns may not have the same name).
Technically I could use RecordType again (since anyway all field containers are implemented on top of a Fields object), but judging from the name of the interface, its documentation, and the fact that DSL.recordType only take Field arguments, it doesn't feel like this type is meant to contain values.

No, indeed. RecordType is a type descriptor, not an actual value / tuple. Here's the distinction:

- Row: an actual QueryPart that can generate a row constructor in SQL. It can contain column references as well as values

- RecordType: A type descriptor for records and rows

- Record: A map of column references / values pairs

 

So, to hold key fields:
- Use Key, adding a type parameter?
- Use RecordType? (but RecordType only contains Fields and not TableFields as do Key)
- Make Key extend RecordType ?

To hold key values:
- Create some new "RecordValue<R>" type, symmetrical to RecordType<R> ?

Or, giving up the difference between key fields and key values, use a single "RecordTuple<R>" type to hold anything?
Row could even extend this type (with each RowX<...> extending RecordTuple<RecordX<...>>).

Clearly, you won't be getting what you're looking for out of the existing type systems too soon, as it was not designed for this use-case, yet.

There are some unfortunate inconsistencies in jOOQ's API which are due to the late decision (version 3.0 only) to have those 22-arity "overloads" for a variety of types (but not for others). For some types, these 22-overloads make a lot of sense, mostly because you get immediate value in your SQL construction. For other types, they would have made sense, but were much too hard to implement correctly because of the limitations of the Java type systems, or because of backwards compatibility with jOOQ 2.0. For other types, they made no sense.

Yes, a Key should really be a "more compatible" type with RecordType. In fact, UpdatableRecord.key() returns a Record, which is covariantly overridden by concrete generated tables to return a Record1<T1>, or Record2<T1, T2>, etc. so this concept certainly exists in jOOQ. Just not for Key.

We might be able to redesign these API types in a way that improves the current situation. Certainly not in a backwards-compatible way, though. This means we would have two parallel APIs that do the same thing for the "rest" of version 3.x (or we fix this only in version 4.0).

Now, before we proceed, I'd really love to have some more concrete examples of use-cases where constraint meta data needs to be queried in client code. I know you're trying to implement that tree persistence on top of jOOQ (see other discussion), but let's not go to the bigger picture, let's focus on concrete operations that you're trying to do and where the lack of type information is bothering you. Do you have some minimal examples you could share?

Lukas

[Thomas GILLET]

Hello again,

 

No, indeed. RecordType is a type descriptor, not an actual value / tuple. Here's the distinction:

- Row: an actual QueryPart that can generate a row constructor in SQL. It can contain column references as well as values

- RecordType: A type descriptor for records and rows

- Record: A map of column references / values pairs

Thanks, that's what I thought. And that's what struck me as a lack of symmetry (RecordType being Record keys, what should hold Record values?), hence that "RecordValue" idea. And I didn't see Row as a good candidate because it's not Record-parameterized.

But then this RecordType/Row duality make me realize there are two different use cases here:

- use jOOQ to generate SQL

- use jOOQ-generated objects as a database model

So maybe that's another reason I tend to like RecordType more than Row: it's not part of the DSL, so it belongs to the second use-case (which is clearly the one I'm working with here). Also the 22-arity seems quite linked to the SQL generation...

Any thought about this distinction?

Clearly, you won't be getting what you're looking for out of the existing type systems too soon, as it was not designed for this use-case, yet.

There are some unfortunate inconsistencies in jOOQ's API which are due to the late decision (version 3.0 only) to have those 22-arity "overloads" for a variety of types (but not for others). For some types, these 22-overloads make a lot of sense, mostly because you get immediate value in your SQL construction. For other types, they would have made sense, but were much too hard to implement correctly because of the limitations of the Java type systems, or because of backwards compatibility with jOOQ 2.0. For other types, they made no sense.

Yes, a Key should really be a "more compatible" type with RecordType. In fact, UpdatableRecord.key() returns a Record, which is covariantly overridden by concrete generated tables to return a Record1<T1>, or Record2<T1, T2>, etc. so this concept certainly exists in jOOQ. Just not for Key.

We might be able to redesign these API types in a way that improves the current situation. Certainly not in a backwards-compatible way, though. This means we would have two parallel APIs that do the same thing for the "rest" of version 3.x (or we fix this only in version 4.0).

Yes I agree this is not a backward-compatible refactoring. It is a long shot anyway, not expecting changes in jOOQ in the next release ;)

 

Now, before we proceed, I'd really love to have some more concrete examples of use-cases where constraint meta data needs to be queried in client code. I know you're trying to implement that tree persistence on top of jOOQ (see other discussion), but let's not go to the bigger picture, let's focus on concrete operations that you're trying to do and where the lack of type information is bothering you. Do you have some minimal examples you could share?

With an hypothetical Key<R, K> type:

// Extracting a key
<K extends Record> K extract(Record record, Key<?, K> key)

But that's just a generic version of UpdatableRecord.key() as you pointed-out before.

// Copying key values
<K extends Record> void copy(Record fromRecord, Key<?, K> fromKey, Record toRecord, Key<?, K> toKey)

// Which I thought could be written with Row types, but it cannot,
// since passing a Row2 and Row19 below will just make K resolve to Row.
<K extends Row> void copy(Record fromRecord, K fromKey, Record toRecord, K toKey)

Showing that a subclassed type (Row) need the 22-arity to ensure type-safety, while a parameterized type (RecordType) doesn't.

// When using a type with a parameter for the primary key type ...
class TreeNode<K extends Record, R extends TableRecord<R>>

// ... it could be great to have a factory like this
<K extends Record, R extends TableRecord<R>> TreeNode<K, R> create(Key<R, K> key)

// ... or even like this, if Table had a key type
<K extends Record, R extends TableRecord<R>> TreeNode<K, R> create(Table<R, K> table)

Well, if Key gets a additional type, it's only fair that UpdatableRecord gets one too. And from there why not go all the way to Table (or rather some kind of UpdatableTable subtype)?

// For CRUD, the "get" (or "delete") operation needs an ID, that is values matching a primary key
<K extends Record, R extends TableRecord<R>> R get(TreeNode<K, R> node, SomeValueType<K> id)

With a RecordType-like container for values, you can call the last method as get(node, DSL.valueType("some", "key", "value")).

The idea being that you only need the 22 overloads on the factory method (as is done for DSL.recordType()) and then everything is handled with generics.

I realize that this CRUD thing is already addressed in the generated DAOs, where there is a notion of primary key type. But it's all based on generated overloads with specific types, so that's not exactly my point. Well unless I try to generate DAOs with tree capabilities... that's another way...

Thomas

[Lukas Eder]

Hi Thomas,

2017-03-06 12:09 GMT+01:00 Thomas GILLET <thomas...@viveris.fr>:

Hello again,

 

No, indeed. RecordType is a type descriptor, not an actual value / tuple. Here's the distinction:

- Row: an actual QueryPart that can generate a row constructor in SQL. It can contain column references as well as values

- RecordType: A type descriptor for records and rows

- Record: A map of column references / values pairs

Thanks, that's what I thought. And that's what struck me as a lack of symmetry (RecordType being Record keys, what should hold Record values?), hence that "RecordValue" idea. And I didn't see Row as a good candidate because it's not Record-parameterized.

But then this RecordType/Row duality make me realize there are two different use cases here:

- use jOOQ to generate SQL

- use jOOQ-generated objects as a database model

So maybe that's another reason I tend to like RecordType more than Row: it's not part of the DSL, so it belongs to the second use-case (which is clearly the one I'm working with here). Also the 22-arity seems quite linked to the SQL generation...

Any thought about this distinction?

Indeed, there is some historic irregularity in these API types. 22-arity is indeed linked mostly to the SQL generation, although early jOOQ 3.x releases also snuck it into the rest of the type system. Perhaps that was a mistake.

The interesting thing with an API like jOOQ's is that it is almost not possible to design it correctly up front. It grew exploratorily, and each major release cleans up quite a few things. E.g. 4.0 will clean up the distinction between:

- Model API (mutable, JavaBeans style)

- DSL API (immutable, SQL style)

Currently, the two API types are often confused, especially with the DSL API being mutable.

At the same time, I'm not 100% convinced that the RecordType type is really so useful on its own. I'm still hoping that a more reusable row type type will cristallise (within the constraints of the limited Java possibilities - don't we all wish for higher kinded types?), but that has not been discovered yet.

 

 

Now, before we proceed, I'd really love to have some more concrete examples of use-cases where constraint meta data needs to be queried in client code. I know you're trying to implement that tree persistence on top of jOOQ (see other discussion), but let's not go to the bigger picture, let's focus on concrete operations that you're trying to do and where the lack of type information is bothering you. Do you have some minimal examples you could share?

With an hypothetical Key<R, K> type: [...]

Thanks for the explanations. That's very useful. Perhaps there's an improvement in there for a future jOOQ version. I'll revisit this discussion when I run again into passing around keys. This also happens internally in UpdatableRecord, although the pain simply hasn't been big enough yet to factor out common logic here into the public API.

Best Regards,

Lukas