>>> 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.
