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: [email protected] [mailto:[email protected]] On Behalf Of Lukas Eder Sent: Tuesday, January 15, 2013 3:11 AM To: [email protected] Subject: Re: FK relationships 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 Am Montag, 14. Januar 2013 schrieb Durchholz, Joachim <[email protected]>: > 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... >
