multiple foreign keys on the same column

[Sander Plas]

Hi all,

I have this PostgreSQL table:

               Table "public.persoon"
       Column       |       Type        | Modifiers
--------------------+-------------------+-----------
 code               | character varying | not null
 activiteit_code    | character varying |
 groep_code         | character varying |
 afdeling_code      | character varying |
Indexes:
    "persoon_pkey" PRIMARY KEY, btree (code)
Foreign-key constraints:
    "persoon_activiteit_code_fkey" FOREIGN KEY (activiteit_code, afdeling_code) REFERENCES activiteit(code, afdeling_code) ON DELETE CASCADE
    "persoon_afdeling_code_fkey" FOREIGN KEY (afdeling_code) REFERENCES afdeling(code) ON DELETE CASCADE
    "persoon_groep_code_fkey" FOREIGN KEY (groep_code, afdeling_code) REFERENCES groep(code, afdeling_code) ON DELETE CASCADE

jOOQ picks up the "persoon_activiteit_code_fkey" and "persoon_groep_code_fkey" keys just fine, but no code is generated for the "persoon_afdeling_code_fkey" foreign key.

When i drop persoon_activiteit_code_fkey , persoon_afdeling_code_fkey gets picked up. For some reason, dropping persoon_groep_code_fkey does not have the same effect.

If i rename persoon_afdeling_code_fkey to persoon_aafdeling_code_fkey (to make it the first one in alphabetical order), all three foreign keys are generated.

I get the impression that jOOQ currently assumes that every column can have just one foreign key. If there's more than one FK per column, only the first one it finds is generated and the others ignored (unless they're multi-column and picked up for another column).

Is this indeed the case and is there anything that can be done about this?

thanks

Sander

[Lukas Eder]

Hello Sander,

> I get the impression that jOOQ currently assumes that every column can have
> just one foreign key. If there's more than one FK per column, only the first
> one it finds is generated and the others ignored (unless they're
> multi-column and picked up for another column).

Yes, this is indeed what jOOQ-meta is currently based on. I actually
wasn't aware of this being possible. The current code generator
behaviour certainly isn't correct. I have registered #2025 for this
https://github.com/jOOQ/jOOQ/issues/2025

> Is this indeed the case and is there anything that can be done about this?

I'm afraid you can probably not work around this issue except:

- if you apply some renaming tricks like you did (although, that might
not be very reliable)
- if you change your DDL

While referencing compound unique keys might seem like a good idea at
first, I always find it easier to actually reference
sequence-generated IDENTITY columns and make those ID's the primary
key within a table, instead of some unique key. It depends on how much
normalisation you want in your schema, of course. With pre-defined
codes, the CRUD is certainly easier to write, when no
sequence-generated values are involved...

Note, I'm assuming that the (code, afdeling_code) tuples are indeed
UNIQUE within the activiteit and groep tables.

Cheers
Lukas

[Sander Plas]

On Wednesday, December 19, 2012 5:20:25 PM UTC+1, Lukas Eder wrote:

Yes, this is indeed what jOOQ-meta is currently based on. I actually
wasn't aware of this being possible. The current code generator
behaviour certainly isn't correct. I have registered #2025 for this
https://github.com/jOOQ/jOOQ/issues/2025

Thanks Lukas!
 

While referencing compound unique keys might seem like a good idea at
first, I always find it easier to actually reference
sequence-generated IDENTITY columns and make those ID's the primary
key within a table, instead of some unique key. It depends on how much
normalisation you want in your schema, of course. With pre-defined
codes, the CRUD is certainly easier to write, when no
sequence-generated values are involved...

 

The reason i'm using multiple foreign keys on the same column is that the "afdeling" of a "persoon" limits the available "groep" and "activiteit" records. Adding the afdeling_code in the groep and activiteit FK's assures that it is impossible to choose a groep or activiteit that does not match the chosen afdeling.

I have thought about adding a separate table that lists all possible afdeling/groep/activiteit combinations and let persoon refer to that table, but the problem is that although "afdeling" is mandatory, groep and activiteit are not.

[Lukas Eder]

> The reason i'm using multiple foreign keys on the same column is that the
> "afdeling" of a "persoon" limits the available "groep" and "activiteit"
> records. Adding the afdeling_code in the groep and activiteit FK's assures
> that it is impossible to choose a groep or activiteit that does not match
> the chosen afdeling.

That makes sense. I've never thought about using foreign key
constraints in such a way.

From a quick glance at the code, I think that jOOQ's core model of
referential meta data (org.jooq.UniqueKey, org.jooq.ForeignKey) will
not be affected, so this is a jOOQ-meta (and thus, jOOQ-codegen) issue
only. It shouldn't be too hard to fix.

> I have thought about adding a separate table that lists all possible
> afdeling/groep/activiteit combinations and let persoon refer to that table,
> but the problem is that although "afdeling" is mandatory, groep and
> activiteit are not.

Yes, these M:N:O:P multi-relationships are not easy to maintain and to
understand. You've probably chosen the best way to model your data.

Cheers
Lukas

[Lukas Eder]

> From a quick glance at the code, I think that jOOQ's core model of
> referential meta data (org.jooq.UniqueKey, org.jooq.ForeignKey) will
> not be affected, so this is a jOOQ-meta (and thus, jOOQ-codegen) issue
> only. It shouldn't be too hard to fix.

I can easily reproduce this issue with the H2 database. and the
following test table:

CREATE TABLE x_test_case_2025 (
ref_id int NOT NULL,
ref_name VARCHAR(10) NOT NULL,

CONSTRAINT fk_x_test_case_2025_1 FOREIGN KEY(ref_id) REFERENCES
x_test_case_85(id),
CONSTRAINT fk_x_test_case_2025_2 FOREIGN KEY(ref_id) REFERENCES
x_test_case_71(id),
CONSTRAINT fk_x_test_case_2025_3 FOREIGN KEY(ref_id, ref_name)
REFERENCES x_unused(id, name)
);

I'll fix this ASAP

Cheers
Lukas

[Lukas Eder]

> I'll fix this ASAP

Fixing this in jooq-meta and jooq-codegen is easy in jOOQ 3.0, given
the few backwards-incompatibilities that are needed to allow for
multiple foreign keys per column. However, once this is fixed, new
issues arise with other code generation features that expected at most
one foreign key per column. These are:

1. Generated setters, setting column values from a referenced record
(introduced with #1510)
2. Generated fetch methods

While 1) was a feature that I wasn't very happy with from the
beginning (various possible and still unresolved naming collision
scenarios for little gain of functionality), 2) is a bit more tricky.
The fetch methods are probably quite useful to some jOOQ users, but I
think I'll have to remove them in the way they are implemented today.

Possibly, it would be wiser to add a generic fetch(ForeignKey) and
fetchOne(ForeignKey) method, that allows for navigating data through
foreign keys...

Anyway, given the impact this change seems to have, I doubt I can
merge it to a 2.x version.

Cheers
Lukas

[Durchholz, Joachim]

> Possibly, it would be wiser to add a generic fetch(ForeignKey) and
> fetchOne(ForeignKey) method, that allows for navigating data through foreign keys...

Definitely.

I have an N:M join table that links predecessor to successor articles.
So it has two foreign keys, one to the predecessor article, one to the successor article. And it would make a huge difference which of the two FKs I'm following!

In other tables, I'm having stuff like "company-specific relationships". Say, each article has an additional company field, and the predecessor-successor article would have three fiels:
company
pred
succ
The predecessor FK would consist of company&pred, the successor FK of company&succ. That's a case of overlapping FKs.

[Lukas Eder]

>> Possibly, it would be wiser to add a generic fetch(ForeignKey) and
>> fetchOne(ForeignKey) method, that allows for navigating data through foreign keys...
>
> Definitely.

In fact, the projected API will include (simplified):

---------------------------------------------------------------
interface TableRecord<R extends TableRecord<R>> {
<O extends UpdatableRecord<O>> O fetchParent(ForeignKey<R, O> key)
throws DataAccessException;
}

interface UpdatableRecord<R extends UpdatableRecord<R>> {
<O extends TableRecord<O>> O fetchChild(ForeignKey<O, R> key)
throws InvalidResultException, DataAccessException;
<O extends TableRecord<O>> Result<O> fetchChildren(ForeignKey<O,
R> key) throws DataAccessException;
}

interface ForeignKey<R extends Record, O extends Record> {
O fetchParent(R record) throws DataAccessException;
Result<O> fetchParents(R... records) throws DataAccessException;
Result<O> fetchParents(Collection<? extends R> records) throws
DataAccessException;
Result<R> fetchChildren(O record) throws DataAccessException;
Result<R> fetchChildren(O... records) throws DataAccessException;
Result<R> fetchChildren(Collection<? extends O> records) throws
DataAccessException;
}
---------------------------------------------------------------

The notions of "fetch()" and "fetchOne()" aren't unambiguously
applicable to directed foreign key navigation. Better terms that are
frequently used in relational literature and examples are "parent"
(referenced record) and "child" (referencing record).

I'm open to alternative naming schemes, as this is something new to the jOOQ API

> I have an N:M join table that links predecessor to successor articles.
> So it has two foreign keys, one to the predecessor article, one to the successor article. And it would make a huge difference which of the two FKs I'm following!

The current implementation of jOOQ-codegen would generate something like:

fetchArticle[List]ByPred()
fetchArticle[List]BySucc()

So, disambiguation is already implemented - at least for single-column
foreign keys.

> In other tables, I'm having stuff like "company-specific relationships". Say, each article has an additional company field, and the predecessor-successor article would have three fiels:
> company
> pred
> succ
> The predecessor FK would consist of company&pred, the successor FK of company&succ. That's a case of overlapping FKs.

Yes. That's where jOOQ-codegen will probably fail, as of version
2.6.x. This will be unambiguous, once the new API is published, where
you can fetch parents / children, by following specific foreign key
relationships.

Cheers
Lukas

[Durchholz, Joachim]

> Better terms that are frequently used in relational literature and
> examples are "parent" (referenced record) and "child" (referencing
> record).

Problem with that is that it doesn't cleanly transfer to one-to-one relationships.
Also, at least for me, parent-child carries the connotation that if the parent record dies, the child records should go away, too (i.e. true hierarchical ownership). I don't know whether that's just me or majority usage.

I usually stick with to-one and to-many in my terminology to avoid these complications.

> I'm open to alternative naming schemes, as this is something new
> to the jOOQ API

> ...

> fetchArticle[List]ByPred()
> fetchArticle[List]BySucc()

So fetchArticle would be following a to-one-relationship, and fetchArticleList a to-many one?
That would be fine by me.
Maybe say Set instead of List. Sets can't contain duplicates, which is a defining property here.

Some generators (notably Hibernate Tools reveng) use a plural, so that would be fetchArticle and fetchArticles.
The resulting compactness is nice. However, generated plurals are often not what a programmer would expect (gory details in http://en.wikipedia.org/wiki/English_plural ). In other words, this works only if manual intervention is easy or expected.

Regards,
Jo

[Lukas Eder]

Hello

>> Better terms that are frequently used in relational literature and
>> examples are "parent" (referenced record) and "child" (referencing
>> record).
>
> Problem with that is that it doesn't cleanly transfer to one-to-one relationships.
> Also, at least for me, parent-child carries the connotation that if the parent record dies, the child records should go away, too (i.e. true hierarchical ownership). I don't know whether that's just me or majority usage.
>
> I usually stick with to-one and to-many in my terminology to avoid these complications.

to-one and to-many are higher-level concepts. SQL only knows
references / foreign keys. I.e. there is no such thing as a one-to-one
relationship in SQL. Of course, you can combine a foreign key with a
unique key and a not null constraint on the referencing side to
enforce an (informal!) one-to-one semantics.

Trying to automatically map referential constraints to higher-level
concepts is where Hibernate / JPA fails. There is no easy way to
automatically map what Sander is doing with his multi-column
referential constraints (from the original post in this thread). This
is where the "object-oriented / relational impedance mismatch" unveils
fundamental problems in such mapping attempts.

>> fetchArticle[List]ByPred()
>> fetchArticle[List]BySucc()
>
> So fetchArticle would be following a to-one-relationship, and fetchArticleList a to-many one?

Yes.

- X.fetchArticle means that you want to fetch the one article record
that is referenced by X
- Y.fetchArticleList means that you want to fetch all article records
that reference Y

The two are the inverse of each other.

> That would be fine by me.

Note, though, that for many reasons, I will remove these generated
methods in jOOQ 3.0. Most importantly because it is very hard to find
a good name mangling strategy that suits all.

Of course, jOOQ users will always be able to extend the code generator
to re-introduce these generated navigation methods.

> Maybe say Set instead of List. Sets can't contain duplicates, which is a defining property here.

In relational theory, Set might be a better choice. But there are two
drawbacks of choosing Set over List when fetching "child" records
through foreign key navigation:

1) It is possible to have two identical "child" records in SQL. A Set
would erroneously remove the duplicate.
2) SQL allows for ordering records (a non-relational SQL feature).
This semantic would be lost if Set was chosen.

> Some generators (notably Hibernate Tools reveng) use a plural, so that would be fetchArticle and fetchArticles.
> The resulting compactness is nice. However, generated plurals are often not what a programmer would expect (gory details in http://en.wikipedia.org/wiki/English_plural ). In other words, this works only if manual intervention is easy or expected.

Early versions of jOOQ also used plurals. But depending on your
database naming conventions, table names may already be in a plural
form. Other users may choose a language other than English for their
table names. In that case, adding an "s" doesn't make sense. Then,
there is the (slight) risk of introducing collisions between the
ARTICLE and ARTICLES tables. This can happen between ARTICLE and
ARTICLE_LIST too, of course. Which leads me back to wanting to remove
these generated methods in jOOQ 3.0

Cheers
Lukas

[Durchholz, Joachim]

>> I usually stick with to-one and to-many in my terminology [...]

>
> to-one and to-many are higher-level concepts. SQL only knows
> references / foreign keys. I.e. there is no such thing as a
> one-to-one relationship in SQL.

It's a to-one relationship if the FK fully covers any unique key, to-many otherwise.

Uniqueness is indeed a central concept in SQL. It is
- used by any practitioner (not just for joins but about anywhere)
- used by Jooq to decide whether it should be fetch[Entity] or fetch[Entity]List
- even used in the standards, e.g. to determine what views could be updatable.

>> Of course, you can combine a foreign key with a unique key and
> a not null constraint on the referencing side to enforce an
> (informal!) one-to-one semantics.

It's the uniqueness that determines to-one vs. to-many.
The not null constraints actually decides whether the to-one is optional or not.

> Trying to automatically map referential constraints to higher-level
> concepts is where Hibernate / JPA fails.

Yes, but I found this particular detail to work well.
It fails for views in Oracle because Oracle doesn't give you uniqueness information of views in metadata. However, this fails at the uniqueness (i.e. SQL) level, not at at higher level.

> There is no easy way to automatically map what Sander is doing
> with his multi-column referential constraints (from the
> original post in this thread).

Well, it isn't straightforward, but it's all there.

> This is where the "object-oriented / relational impedance mismatch"
> unveils fundamental problems in such mapping attempts.

to-many and to-one actually map easily to a simple reference and a set, respectively. There's no fundamental mismatch here.

In my book, the fundamental mismatches are elsewhere:
- Java has no concept of a transaction; you can easily modify objects and forget to roll them back. The only clean workaround is to throw away the entire JVM after a transaction and repopulate the next transaction from the database.
- Databases have no concept of subtype, or polymorphic table. You can work around this, but it remains awkward and can miss opportunities for constraint checking.
- Many (most?) databases can't do enums as a primitive type.
- Database types (table structures) and Java types (classes) cover overlapping semantics, creating a lot of implicit redundancy.

>> Maybe say Set instead of List. Sets can't contain duplicates,
>> which is a defining property here.
>
> In relational theory, Set might be a better choice. But there
> are two drawbacks of choosing Set over List when fetching "child"
> records through foreign key navigation:
>
> 1) It is possible to have two identical "child" records in SQL.
> A Set would erroneously remove the duplicate.

Only if the is_equal function does not fully cover any unique key of the "child" table.
Solution: Use Java's is_equal.

> 2) SQL allows for ordering records (a non-relational SQL
> feature).
> This semantic would be lost if Set was chosen.

Java Sets do have an ordering, imposed by the iterator.
It can be unstable, so whatever type of Set is used, it should implement SortedSet, reproducing the order in which the records were retrieved.

> [Collision] can happen between ARTICLE and ARTICLE_LIST too, of

> course. Which leads me back to wanting to remove these generated
> methods in jOOQ 3.0

I can feel the pain, but having to define your own generator strategies would be more painful for 99% of use cases.
I don't know enough details of Jooq's code generation to make meaningful proposals, unfortunately.

[Lukas Eder]

>>> I usually stick with to-one and to-many in my terminology [...]
>>
>> to-one and to-many are higher-level concepts. SQL only knows
>> references / foreign keys. I.e. there is no such thing as a
>> one-to-one relationship in SQL.
>
> It's a to-one relationship if the FK fully covers any unique key, to-many otherwise.

OK, let's assume that this knowledge can be used in a formal way (i.e.
let's assume we didn't forget some weird corner-case involving some
complex constraint setups). According to you, should this change the
way jOOQ deals with foreign key relationships? If yes, how?

>> 1) It is possible to have two identical "child" records in SQL.
>> A Set would erroneously remove the duplicate.
>
> Only if the is_equal function does not fully cover any unique key of the "child" table.
> Solution: Use Java's is_equal.

How about

CREATE TABLE x (
val INT,
CONSTRAINT x_to_y FOREIGN KEY(x) REFERENCES y(val)
);
INSERT INTO x VALUES (1), (1);

Now, jOOQ's Record.equal() method would return true, if comparing the
two records in table x. This behaviour is consistent with:

-- Returns only one record
SELECT DISTINCT * FROM x

I would expect Y.getX{List|Set} to return two records, right?

>> 2) SQL allows for ordering records (a non-relational SQL
>> feature).
>> This semantic would be lost if Set was chosen.
>
> Java Sets do have an ordering, imposed by the iterator.
> It can be unstable, so whatever type of Set is used, it
> should implement SortedSet, reproducing the order in
> which the records were retrieved.

I see your point. SortedSet.comparator() would then have to return a
comparator that has some knowledge of the implicit "ROWNUM" of every
Record contained in the Result, given the context of a concrete query
execution. Note, if natural ordering was introduced in Records, it
should be consistent with the < comparison operator for row value
expressions, e.g. ROW(A, B) < ROW(C, D). This wouldn't be the same as
"ROWNUM" ordering. As a matter of fact, I think I should think about
introducing natural ordering to Records:
https://github.com/jOOQ/jOOQ/issues/2107

Nonetheless, most databases and JDBC allow for row access by index in
tables and cursors, which is a strong indicator for using lists to me.
And then, again, the way I see it, ROW(1) and ROW(1) are two Records /
rows for which jOOQ should return r1.equals(r2) == true. This
contradicts the general contract taken from the SortedSet Javadoc:

"[...] the ordering maintained by a sorted set (whether or not an
explicit comparator is provided) must be consistent with equals if the
sorted set is to correctly implement the Set interface [...]"

>> [Collision] can happen between ARTICLE and ARTICLE_LIST too, of
>> course. Which leads me back to wanting to remove these generated
>> methods in jOOQ 3.0
>
> I can feel the pain, but having to define your own generator strategies would be more painful for 99% of use cases.
> I don't know enough details of Jooq's code generation to make meaningful proposals, unfortunately.

I'm not quite sure what you mean. Anyway, the newly introduced methods
fully cover what was covered unreliably by the code generator before.
They make use of the generated ForeignKey objects, which contain all
the information needed to navigate parent-child (both to-one and
to-many) relationships.

[Durchholz, Joachim]

>>>> I usually stick with to-one and to-many in my terminology [...]
>>>
>>> to-one and to-many are higher-level concepts. SQL only knows
>>> references / foreign keys. I.e. there is no such thing as a
>>> one-to-one relationship in SQL.
>>
>> It's a to-one relationship if the FK fully covers any unique key,
>> to-many otherwise.
>
> OK, let's assume that this knowledge can be used in a formal way
> (i.e. let's assume we didn't forget some weird corner-case
> involving some complex constraint setups).

I think it's indeed formally sound.
If the referred-to record is uniquely identified, it's a to-one
because only a single record can match (if any). If it's not
uniquely identified, it's a to-many because potentially multiple
records can match.

Establishing whether a given combination of fields is unique
can be a challenge.
One, some constraints other than unique keys can establish
uniqueness. I don't think that's an issue since people don't
expect query optimizers to recognize these kinds of uniqueness,
and hence construct their databases so that all uniqueness
constraints are expressed via unique keys.
Two, views built from joins typically don't provide information
on relevant unique keys. There may be ways around that, but it's
going to not work in all databases or for all kinds of views; on
the other hand, to establish primary key information, Jooq needs
to do something in that area anyway.

> According to you, should this change the way jOOQ deals with
> foreign key relationships? If yes, how?

I think it mostly affects code generation. If you generate a
reference to another table, you need to know whether it should
be a List or not.

Code that fills query results into Pojo structures will need to
know whether it's a List or not, too, but whether that kind of
code is affected depends on whether it's taking its uniqueness
information from the Pojo's metadata or from the RDBMS metadata.

>>> 1) It is possible to have two identical "child" records in SQL.
>>> A Set would erroneously remove the duplicate.
>>
>> Only if the is_equal function does not fully cover any unique
>> key of the "child" table.
>> Solution: Use Java's is_equal.
>
> How about
>
> CREATE TABLE x (
> val INT,
> CONSTRAINT x_to_y FOREIGN KEY(x) REFERENCES y(val)
> );
> INSERT INTO x VALUES (1), (1);
>
> Now, jOOQ's Record.equal() method would return true,
> if comparing the two records in table x.

That's why I'd use Java's equality, i.e. object identity, for
a Set.

> I would expect Y.getX{List|Set} to return two records, right?

Yep. SELECT DISTINCT should give different results than SELECT
in such cases, it would be highly surprising if it didn't.

> Nonetheless, most databases and JDBC allow for row access by
> index in tables and cursors, which is a strong indicator for
> using lists to me.

Yes, from the database side, it's lists.
I'm seeing it from the pojo side.

The read-only case on the pojo side is relatively uninteresting;
for what it's worth, one could declare a to-many reference as
Iterable and it would work well enough.

Things get interesting when the Java code adds records.
For a Set, multiply add()in the same record is okay, for a List,
it isn't, you need to check whether the record was in the List
before (and that check could be inefficient, depending on what
implementation is behind that List interface).

> And then, again, the way I see it, ROW(1) and ROW(1) are two
> Records / rows for which jOOQ should return r1.equals(r2) == true.

Hmm... I see the point.
However, in that case, object equality isn't the right comparator
for the Set, it should use object identity.
This probably means using a HashSet and a Comparator that uses ==.

Note that we should have r1 == r2 iff they refer to the same record
in the database.
To establish whether two records refer to the same database record,
Jooq would need to know all fields of any unique key. (This get
somewhat complicated if we're talking about a row from a query
result with aggregation and/or joins, but the concept does work
out well enough to be actually useful; we're in the tracks of the
"updatable view" concept here.)

[Lukas Eder]

>> OK, let's assume that this knowledge can be used in a formal way
>> (i.e. let's assume we didn't forget some weird corner-case
>> involving some complex constraint setups).
>

> Establishing whether a given combination of fields is unique
> can be a challenge.
> One, some constraints other than unique keys can establish
> uniqueness.

> I don't think that's an issue [...]

Yes, as you say, once there is a unique key that covers exactly the
columns of a foreign key, that's a sufficient piece of information to
infer a to-one relationship. I guess we could disregard more advanced
and implicit uniqueness

> Two, views built from joins typically don't provide information
> on relevant unique keys. There may be ways around that, but it's
> going to not work in all databases or for all kinds of views; on
> the other hand, to establish primary key information, Jooq needs
> to do something in that area anyway.

Yes, we were discussing this before:
https://groups.google.com/d/topic/jooq-user/FhDh-kkdxhw/discussion

>> According to you, should this change the way jOOQ deals with
>> foreign key relationships? If yes, how?
>
> I think it mostly affects code generation. If you generate a
> reference to another table, you need to know whether it should
> be a List or not.

OK, but with the generation of these fetch{Entity}[List] having been
removed in jOOQ 3.0, it seems to me like the distinction is no longer
useful right now. We can re-enact it, once (if) more sophisticated
code generation is implemented for POJOs

> Code that fills query results into Pojo structures will need to
> know whether it's a List or not, too, but whether that kind of
> code is affected depends on whether it's taking its uniqueness
> information from the Pojo's metadata or from the RDBMS metadata.

Right. The relevant discussion is here:
https://groups.google.com/d/topic/jooq-user/qqV-0uOX1r8/discussion

This is about introducing support for things like JPA's @OneToOne and
@OneToMany annotations (and potentially, others)

I think that - if jOOQ were to go down that road - the POJO should be
considered part of the "Java domain", whereas the org.jooq.Record
should be considered part of the "RDBMS domain". A record should
always model a database record, the way it was retrieved from the
database, where POJOs can be of any form, mapped through well-defined
rules.

In that sense, the POJO itself (and its metadata) should make the
rules for POJO mapping. Maybe, this also obsoletes the discussion
whether a Set or List is more appropriate for to-many relationships -
at least in the context of a Record?

>> Nonetheless, most databases and JDBC allow for row access by
>> index in tables and cursors, which is a strong indicator for
>> using lists to me.
>
> Yes, from the database side, it's lists.
> I'm seeing it from the pojo side.

OK, so let's establish this:

- database side: org.jooq.Record
- pojo side: com.example.MyPojo

>> And then, again, the way I see it, ROW(1) and ROW(1) are two
>> Records / rows for which jOOQ should return r1.equals(r2) == true.
>
> Hmm... I see the point.
> However, in that case, object equality isn't the right comparator
> for the Set, it should use object identity.
> This probably means using a HashSet and a Comparator that uses ==.
> Note that we should have r1 == r2 iff they refer to the same record
> in the database.

If object identity had a relevant semantics, two consecutive
executions of the same / a similar query would have to produce
identical records. Such a requirement seems impossible to solve, to
me. An example:

SELECT * FROM my_table WHERE ID = 1;
SELECT * FROM my_table WHERE ID = 1
UNION
SELECT * FROM my_table WHERE 1 = 0;

If ID is the primary key, the above queries are guaranteed to produce
two times the identical record. However, it is impossible to reliably
establish identity. Maybe Oracle's ROWID or Postgres' OID, etc could
be used. But not all databases expose such row ID's.

SQL isn't about concrete rows in concrete tables. SQL is about
creating ad-hoc records through arbitrary projections. Once
projections are involved, there is probably no formal way to
re-establish the original underlying unique constraints anymore. I
think this was the conclusion of our previous discussion, which I
cited at the beginning of this message:
https://groups.google.com/d/topic/jooq-user/FhDh-kkdxhw/discussion

> To establish whether two records refer to the same database record,
> Jooq would need to know all fields of any unique key. (This get
> somewhat complicated if we're talking about a row from a query
> result with aggregation and/or joins, but the concept does work
> out well enough to be actually useful; we're in the tracks of the
> "updatable view" concept here.)

Feel free to provide a reliable implementation draft, then :-)

[Durchholz, Joachim]

Getting around to finally answering this, sorry for the delay...

>>> According to you, should this change the way jOOQ deals with foreign
>>> key relationships? If yes, how?
>>
>> I think it mostly affects code generation. If you generate a reference
>> to another table, you need to know whether it should be a List or not.
>
> OK, but with the generation of these fetch{Entity}[List] having been
> removed in jOOQ 3.0, it seems to me like the distinction is no longer
> useful right now. We can re-enact it, once (if) more sophisticated code
> generation is implemented for POJOs

Now I have to show how little of Jooq I've seen yet ;-) , but anyway:
Is there a workaround for code that uses these fetch{Entity} and
fetch{Entity}List calls?

>> Code that fills query results into Pojo structures will need to know
>> whether it's a List or not, too, but whether that kind of code is
>> affected depends on whether it's taking its uniqueness information
>> from the Pojo's metadata or from the RDBMS metadata.
>
> Right. The relevant discussion is here:
> https://groups.google.com/d/topic/jooq-user/qqV-0uOX1r8/discussion
>
> This is about introducing support for things like JPA's @OneToOne and
> @OneToMany annotations (and potentially, others)

I'm seeing the laziness issue.
Yes, that's an evil can of worms. I'm seeing multiple solutions, none
of them really satisfactory:
1) Lazy load with sessions. That's just the dreaded lazy instantiation
exceptions right back in.
2) Lazy load with version checking in the referring record. I.e. load
lazily, including whatever is needed to establish that the database
version of the referring-to record wasn't modified since it was last
loaded. This can get complicated quickly since you may need to check
more records (maybe actually the one you're loading, but it wasn't
loaded).
3) Eager load, just place nulls in the references. Chaos will ensue
if these Pojos are inadvertently passed to code that assumes valid
values in these references.
4) Eager load, just point the references to a BadPojo object that
throws an exception whenever any property (maybe except PK values)
is referened. We're getting these exceptions in the exact same
circumstances as Hibernate would throw a lazy instantiation
exception, which means the code is getting unmodular because the
first loader of any record needs to know exactly what referred-to
objects might be needed (even when there are no optimization issues).
5) As 4, but add an API to add post-hoc loading without regard for
inconsistencies. Plus hopefully an API for explicitly checking
record versions (the challenge here would be that updates in a child
record might invalidate denormalized aggregated values in the parent,
and the code needs to fail if you first load the parent and child
records later after some third party did an update - but it should
be the application programmer's choicde whether he wants failure,
or a silent merge, or just gimme the data dammit and I don't care
about the sums in the parent because they're just advisory anyway).

I'd not recommend using the same annotations as JPA.
Aspect 1: Principle of least surprise. People with a JPA background
might expect Jooq to do lazy loading if they see the same
annotations being used.
Aspect 2: Coexistence. It would be desirable to allow the same
Pojo class to work in JPA and with Jooq. If there's a case where
Jooq usage would need different annotation values than JPA usage,
you can't reuse a Jooq class in a JPA context or vice versa. I
don't know whether such a case exists, but it might come up as
JPA evolves, so I'd want to avoid that.

I would recommend use similar names.
Maybe @ToOne and @ToMany.
Or maybe @JooqManyToOne, @JooqOneToMany.

> I think that - if jOOQ were to go down that road - the POJO
> should be considered part of the "Java domain", whereas the
> org.jooq.Record should be considered part of the "RDBMS domain".

I'm not sure whether that's a good idea.
Records are good for iterating over all fields, Pojos are good
for business rule implementation. This seems orthogonal to the
questions of lazy loading to me. Actually, I'd like to have the
same object exposing both the Record and the pojo interface
sometimes (rare but happens).
I'm not sure that this is easy or even really possible in Java.

> In that sense, the POJO itself (and its metadata) should make
> the rules for POJO mapping. Maybe, this also obsoletes the
> discussion whether a Set or List is more appropriate for to-many
> relationships - at least in the context of a Record?

Heh. I'm not sold on any concrete interface there.
Not much anyway :-)

Jokes aside, the more interesting question (for me) is whether
the relational links between Records should really be handled
differently than those between pojos.

It might also be violating the principle of least surprise.

>>> And then, again, the way I see it, ROW(1) and ROW(1) are two Records
>>> / rows for which jOOQ should return r1.equals(r2) == true.
>>
>> Hmm... I see the point.
>> However, in that case, object equality isn't the right comparator for
>> the Set, it should use object identity.
>> This probably means using a HashSet and a Comparator that uses ==.
>> Note that we should have r1 == r2 iff they refer to the same record in
>> the database.
>
> If object identity had a relevant semantics,

Well, it does, even in an RDBMS. It's obscure and hard to
put to productive use - for example, once two records are
equal, you can't UPDATE or DELETE one of them without
affecting the other, unless you use a "somewhat icky"
construct like ROWNUM or OID/ROWID.
But even in 100% clean SQL, COUNT(*) does show a difference,
and this IS exploited in practice, so relevant.

> two consecutive executions of the same / a similar query
> would have to produce identical records.

Only if you have a guarantee that identity inside the
RDBMS maps to identity inside the JVM.
Which, of course, it doesn't. Not even with a fully
normalized schema where every table has a PK. Does Jooq
even want to guarantee this kind of equivalence, across
transactions? DB connections/sessions? JVMs?

> SQL isn't about concrete rows in concrete tables.
> SQL is about creating ad-hoc records through arbitrary
> projections. Once projections are involved, there is
> probably no formal way to re-establish the original
> underlying unique constraints anymore.

Of course not.
The easiest example for that would be any projection that
drops all PK and UK fields.
Or something like
SELECT 1 FROM some_table
if some_table has more than 1 row.

I believe that uniqueness-maintaining projections are
fundamentally different from uniqueness-dropping ones,
address different use cases and possibly might benefit
from different kinds of APIs.
I haven't spent much thought on that idea yet, though.

>> To establish whether two records refer to the same database record,
>> Jooq would need to know all fields of any unique key. (This get
>> somewhat complicated if we're talking about a row from a query result
>> with aggregation and/or joins, but the concept does work out well
>> enough to be actually useful; we're in the tracks of the "updatable
>> view" concept here.)
>
> Feel free to provide a reliable implementation draft, then :-)

Heh. I'd love to, but that's firmly outside my time budget.
Unfortunately. There's enough interesting stuff to do that
I'd like to split myself into a dozen programmers.
And I believe once I do that, I'll find that that's not
even nearly enough - there is SO MUCH that needs attention...

[Lukas Eder]

Hello,

I'm currently not at home. I'll try to respond to this e-mail within the next three weeks. Thanks for your understanding

Cheers,
Lukas

[Durchholz, Joachim]

Heh. No problem. This is about future evolution of Jooq so there's no hurry for anyone anyway.

Besides, I took my time, too :-)

---

From: jooq...@googlegroups.com [mailto:jooq...@googlegroups.com] On Behalf Of Lukas Eder
Sent: Tuesday, January 15, 2013 3:11 AM
To: jooq...@googlegroups.com
Subject: Re: FK relationships

[Lukas Eder]

Hi Joachim,

I'll keep my responses short, as this thread is getting hard to track :-)

> Now I have to show how little of Jooq I've seen yet ;-) , but anyway:
> Is there a workaround for code that uses these fetch{Entity} and
> fetch{Entity}List calls?

Yes, you will be able to navigate the generated ForeignKey objects in
both directions. You can check out the API code here:
https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/ForeignKey.java

This was introduced with #2044
https://github.com/jOOQ/jOOQ/issues/2044

> I'm seeing the laziness issue.

Not sure what you mean. This discussion went on in another thread.
Here's a summary:
- jOOQ will NEVER fetch anything other than what you explicitly tell
it to fetch through the DSL API.
- jOOQ will ALWAYS fetch records first (in other words: tables)
- jOOQ will ONLY THEN optionally transform records into POJOs
according to rules that may be enhanced in the future. These rules are
not yet defined.

So I don't see any laziness problems. Client code is responsible to
spell out all the necessary JOIN clauses, predicates, etc. to get the
tables they need. Going farther than that wouldn't be within jOOQ's
mission anymore.

> I'd not recommend using the same annotations as JPA.
> Aspect 1: Principle of least surprise. People with a JPA background
> might expect Jooq to do lazy loading if they see the same
> annotations being used.

I haven't fully evaluated that yet.

> Aspect 2: Coexistence. It would be desirable to allow the same
> Pojo class to work in JPA and with Jooq. If there's a case where
> Jooq usage would need different annotation values than JPA usage,
> you can't reuse a Jooq class in a JPA context or vice versa. I
> don't know whether such a case exists, but it might come up as
> JPA evolves, so I'd want to avoid that.

That might be true.

>> I think that - if jOOQ were to go down that road - the POJO
>> should be considered part of the "Java domain", whereas the
>> org.jooq.Record should be considered part of the "RDBMS domain".
>
> I'm not sure whether that's a good idea.

Anyway, that ship has sailed.

> Jokes aside, the more interesting question (for me) is whether
> the relational links between Records should really be handled
> differently than those between pojos.

Records: 1-1 mapping between SQL and Java (like ActiveRecords)
POJOs: Arbitrary user code

It's the old story of the ORM impedance mismatch, to try to make them
work the same way.

>> two consecutive executions of the same / a similar query
>> would have to produce identical records.
>
> Only if you have a guarantee that identity inside the
> RDBMS maps to identity inside the JVM.
> Which, of course, it doesn't. Not even with a fully
> normalized schema where every table has a PK. Does Jooq
> even want to guarantee this kind of equivalence, across
> transactions? DB connections/sessions? JVMs?

jOOQ does not care about what you called object identity. jOOQ will
always treat record equality according to what SQL understands by row
value expression equality. The concept of identity is not involved in
this discussion.

Summary:
- Laziness is not a relevant topic for jOOQ as jOOQ users fully
control their SQL.
- Adding improved mapping from records "into" POJOs needs more
thinking. This is a low prio to me, as it is hard to get this right.
I'm waiting for user contributions / concrete suggestions in that
area, for now.
- Record equality is well-defined in jOOQ through row value expression
equality, not through identity

Cheers
Lukas

[Durchholz, Joachim]

>> I'm seeing the laziness issue.
>
> Not sure what you mean.

Me neither anymore :-(
(too much snippage)

>> Jokes aside, the more interesting question (for me) is whether the
>> relational links between Records should really be handled differently
>> than those between pojos.
>
> Records: 1-1 mapping between SQL and Java (like ActiveRecords)
> POJOs: Arbitrary user code
>
> It's the old story of the ORM impedance mismatch, to try to make them work the same way.

Thinking on a tangent, not really related to Jooq:
I think the mismatch should be addressed by setting up maps from keys to records, rebuilding the DB's concept of an index on the Java side. Getters and setters would go through the maps and the mismatch would go away (at least the link-vs-join mismatch, there are others).

>>> two consecutive executions of the same / a similar query would have
>>> to produce identical records.
>>
>> Only if you have a guarantee that identity inside the RDBMS maps to
>> identity inside the JVM.
>> Which, of course, it doesn't. Not even with a fully normalized schema
>> where every table has a PK. Does Jooq even want to guarantee this kind
>> of equivalence, across transactions? DB connections/sessions? JVMs?
>
> jOOQ does not care about what you called object identity.

1) Object identity is a well-established concept, not my personal definition. In Java, it is defined by ==.
2) Object identity played a role in some design decisions you were considering, namely how to find out whether a record is already represented on the Java side (namely in situations where there's no unique key available). I'm a bit fuzzy about the details, but yes object identity can be a useful fallback, though in very limited circumstances. (Also, Oracle's ROWID does map cleanly to object identity. Many but not all databases have an equivalent concept, relational theory be damned.)

> jOOQ will always treat record equality according to what SQL
> understands by row value expression equality. The concept of
> identity is not involved in this discussion.

If that's Jooq's design choice - okay.

Regards,
Jo