>> I had thought that OF COURSE you need to specify optionality on >> relationships, but it seems to be far less a case than I thought. > > Actually if we talk about "optionality" flag, it is something different from > the relationship join type per Hugi's original email. Not sure he was talking > about the same thing.
You're right. I was just making the connection because that's the one situation where the difference between outer and inner joins makes sense at the relationship level. For fetch optimization, specifying a join type does not make that much sense. Hibernate does have it, on the grounds that the application programmer can specify the default strategy when there's no more specific reason to use the other fetch type. I'm not sure that that is a good idea though; flipping that switch might change the application's performance in unexpected ways. >> For automatic SELECTs by Cayenne, I see one use case: if the >> application holds a record that's a grandchild, and you access the >> grandparent, Cayenne could optimize the outer join between parent and >> grandparent table into an inner join if it knew that the to-1 >> relationship from parent to grandparent is mandatory. (Inner joins >> give the query planner of the database more options. More options >> means better plans, but maybe also more fruitless optimization >> planning, so it's a mixed blessing - also, grandchild-to-grandparent >> selects tend to not be bulk transfers, so this use case is a rather >> weak argument.) > > Strictly reading the relationships is always done via an INNER join. Hmm... that means when the parent exists but not the grandparent, accessing the grandparent does not get the parent into the object cache. I'd have expected Cayenne to act as if it first loaded the parent, then the grandparent, so the parent would be in memory anyway. I'm not sure how that difference would be actually noticeable. And it can always be fixed by a per-query option, so it's not a too big deal anyway. >> Then there's 1:1 relationships. If one side is optional, then it's >> just a case of 1:N with the additional constraint N<=1. If both >> sides are mandatory, we're in trouble - Cayenne does not know in >> which order to do the updates (and most databases will complain, >> since they do their consistency checks immediately instead of at >> end-of-transaction, so you can't INSERT into the left table because >> there is no record in the right table yet, and vice versa). > > we do have a checkbox "To Dep PK" in the join that hints Cayenne which 1:1 > side is "primary" and which is "dependent". So this situation is handled > correctly. I wouldn't have made that connection if you hadn't mentioned that. "To Dep PK" says "related to primary keys, and directions", but I wouldn't know what it actually does. Nor would I know to look there if I needed to establish a dependency direction. > But yeah 1:1, and more generally PK:PK relationships, even if this is a > relationship between parts of a compound PK, are a case when we don't know > the optionality, and have to assume the worst case. E.g. lazy faulting uses > optionality information to decide whether to return a HOLLOW object from > unresolved relationship without a query, or whether the fetch is required to > determine if the target is not null. Somewhat of an edge case (you are > reading the relationship, but don't care to resolve the resulting HOLLOW > object... but may be good for NULL checking or indirect access to an FK via > related object PK). Resolving that is actually relatively easy - retrieve a PK field via an outer join, and assume the target isn't there if that field is NULL. Well, for databases that actually allow NULLs in PKs, you'd probably want to use a SELECT COUNT(*) or IF EXISTS subquery instead. Anyway, what I mean to say is that you don't need to actually resolve the object to know whether it exists (and if the related table has non-PK fields, this check will be faster than simply retrieving the object). > We don't do much (anything at all) in the area of analyzing native > exceptions. So I can't comment on specifics, but it would be cool to have > such ability. I'm not sure that it's worth the effort. You'd be hunting a moving target, since exception behaviour of databases can (and sometime does) change depending on database release, mode of operation, isolation level, and probably phase of moon. Regards, Jo
