On Oct 26, 2011, at 1:48 PM, Durchholz, Joachim wrote: > 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. Cayenne can actually guess optionality of some relationships (e.g. to-one based on non-NULL FK). Those that can't be guessed, are assumed to be optional. And it does use the optionality information in many places (fault resolution strategies come to mind, see below). So the question here whether we should bother with an extra mapping property for those few remaining cases (that are handled correctly, but perhaps can be optimized a bit further). > 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. But *prefetching* them in a query that selects relationship root is using OUTER. So this may be about optimizing prefetching (in Cayenne terms anyways). > 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. 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). > The other point for optionality information is that Cayenne could do > consistency checks before writing to the database. > (I don't know whether Cayenne does.) > If Cayenne knows, it can avoid getting hit by SQLExceptions from constraint > violations. This could help Cayenne if it does exception analysis, such as > would be needed to activate the various workarounds needed as Oracle reports > rollback segment problems. > On the con side, for the application, it doesn't matter much to the > application whether it gets hit by an SQLException or a Cayenne-generated > consistency exception. 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. Cheers, Andrus
