cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <>
Subject Re: Can I specify join type for relationships in the model?
Date Wed, 26 Oct 2011 19:22:31 GMT
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.


View raw message