>> 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 :-)
