cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Kienenberger <mkien...@gmail.com>
Subject Qualifier support for prefetch queries
Date Fri, 27 Sep 2013 00:29:10 GMT
I have something that I believe is correctly adding the right prefetch
and the test I wrote passes, but some of my prefetches are putting the
qualifier on twice.  This does not happen for every prefetch in the
same operation, only 2 of the 4 for the example query below:

SELECT DISTINCT t0.*
 FROM ACCOUNT t0
 JOIN USER_ACCOUNT_RELATIONSHIP t1 ON (t0.ACCOUNT_NUMBER = t1.ACCOUNT_NUMBER)
 WHERE (t1.USER_ID = ?) AND (t1.INVALIDATED = ?)
 [bind: 1:56, 2:'N']


SELECT DISTINCT t0.*
 FROM SCHEDULED_PAYMENT t0
 JOIN ACCOUNT t1 ON (t0.ACCOUNT_NUMBER = t1.ACCOUNT_NUMBER)
 JOIN USER_ACCOUNT_RELATIONSHIP t2 ON (t1.ACCOUNT_NUMBER = t2.ACCOUNT_NUMBER)
 WHERE (t2.USER_ID = ?) AND (t2.INVALIDATED = ?) AND (t0.INVALIDATED =
?) AND (t0.INVALIDATED = ?)
 [bind: 1:56, 2:'N', 3:'N', 4:'N']


SELECT DISTINCT t0.*
 FROM USER_ACCOUNT_RELATIONSHIP t0
 JOIN ACCOUNT t1 ON (t0.ACCOUNT_NUMBER = t1.ACCOUNT_NUMBER)
 JOIN USER_ACCOUNT_RELATIONSHIP t2 ON (t1.ACCOUNT_NUMBER = t2.ACCOUNT_NUMBER)
 WHERE (t2.USER_ID = ?) AND (t2.INVALIDATED = ?) AND (t0.INVALIDATED =
?) AND (t0.INVALIDATED = ?)
 [bind: 1:56, 2:'N', 3:'N', 4:'N']


SELECT DISTINCT t0.*
 FROM PAPER_BILL_WARNING t0
 JOIN ACCOUNT t1 ON (t0.ACCOUNT_NUMBER = t1.ACCOUNT_NUMBER)
 JOIN USER_ACCOUNT_RELATIONSHIP t2 ON (t1.ACCOUNT_NUMBER = t2.ACCOUNT_NUMBER)
 WHERE (t2.USER_ID = ?) AND (t2.INVALIDATED = ?)
 [bind: 1:56, 2:'N']


SELECT DISTINCT t0.*
 FROM SEND_PAPER_BILL_CHANGES t0
 JOIN ACCOUNT t1 ON (t0.ACCOUNT_NUMBER = t1.ACCOUNT_NUMBER)
 JOIN USER_ACCOUNT_RELATIONSHIP t2 ON (t1.ACCOUNT_NUMBER = t2.ACCOUNT_NUMBER)
 WHERE (t2.USER_ID = ?) AND (t2.INVALIDATED = ?)
 [bind: 1:56, 2:'N']




Index: framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/query/SelectQueryPrefetchRouterAction.java
===================================================================
--- framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/query/SelectQueryPrefetchRouterAction.java
   (revision 1524993)
+++ framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/query/SelectQueryPrefetchRouterAction.java
   (working copy)
@@ -23,7 +23,10 @@

 import org.apache.cayenne.CayenneRuntimeException;
 import org.apache.cayenne.exp.Expression;
+import org.apache.cayenne.map.Entity;
+import org.apache.cayenne.map.EntityInheritanceTree;
 import org.apache.cayenne.map.EntityResolver;
+import org.apache.cayenne.map.ObjEntity;
 import org.apache.cayenne.map.ObjRelationship;
 import org.apache.cayenne.reflect.ClassDescriptor;
 import org.apache.cayenne.util.CayenneMapEntry;
@@ -95,15 +98,30 @@
                     .andExp(entityQualifier) : entityQualifier;
         }

+        Expression translatedQueryQualifierExpression =
classDescriptor.getEntity().translateToRelatedEntity(
+                queryQualifier,
+                prefetchPath);
+
+
+        Entity targetEntity = relationship.getTargetEntity();
+        ObjEntity targetObjEntity = (ObjEntity)targetEntity;
+        EntityInheritanceTree prefetchEntityInheritanceTree =
resolver.lookupInheritanceTree(targetEntity.getName());
+        Expression prefetchEntityQualifier =
prefetchEntityInheritanceTree.qualifierForEntityAndSubclasses();
+
+        if (prefetchEntityQualifier != null) {
+            Expression translatedPrefetchEntityQualifierExpression =
targetObjEntity.translateToDbPath(prefetchEntityQualifier);
+
+            translatedQueryQualifierExpression =
(translatedQueryQualifierExpression != null) ?
translatedQueryQualifierExpression
+
.andExp(translatedPrefetchEntityQualifierExpression) :
translatedPrefetchEntityQualifierExpression;
+        }
+
         // create and configure PrefetchSelectQuery
         PrefetchSelectQuery prefetchQuery = new PrefetchSelectQuery(
                 prefetchPath,
                 relationship);
         prefetchQuery.setStatementFetchSize(query.getStatementFetchSize());

-        prefetchQuery.setQualifier(classDescriptor.getEntity().translateToRelatedEntity(
-                queryQualifier,
-                prefetchPath));
+        prefetchQuery.setQualifier(translatedQueryQualifierExpression);

         if (relationship.isSourceIndependentFromTargetChange()) {
             // setup extra result columns to be able to relate result
rows to the parent
Index: framework/cayenne-jdk1.5-unpublished/src/test/java/org/apache/cayenne/query/SelectQueryPrefetchRouterActionQualifiedEntityTest.java
===================================================================
--- framework/cayenne-jdk1.5-unpublished/src/test/java/org/apache/cayenne/query/SelectQueryPrefetchRouterActionQualifiedEntityTest.java
   (revision 1524993)
+++ framework/cayenne-jdk1.5-unpublished/src/test/java/org/apache/cayenne/query/SelectQueryPrefetchRouterActionQualifiedEntityTest.java
   (working copy)
@@ -24,6 +24,7 @@
 import org.apache.cayenne.exp.ExpressionFactory;
 import org.apache.cayenne.map.EntityResolver;
 import org.apache.cayenne.map.ObjEntity;
+import org.apache.cayenne.testdo.inherit.Address;
 import org.apache.cayenne.testdo.inherit.Department;
 import org.apache.cayenne.testdo.inherit.Employee;
 import org.apache.cayenne.testdo.inherit.Manager;
@@ -58,6 +59,29 @@
                 + "or db:employees.PERSON_TYPE = 'EM')"),
prefetch.getQualifier());
     }

+    /**
+     * This method tests that the prefetch qualifier is property attached
+     * @throws Exception
+     */
+    public void testPrefetchEmployeeFromAddress() throws Exception {
+        ObjEntity employee = resolver.lookupObjEntity(Employee.class);
+        SelectQuery q = new SelectQuery(Address.class);
+
+        q.addPrefetch(Address.TO_EMPLOYEE_PROPERTY);
+
+        SelectQueryPrefetchRouterAction action = new
SelectQueryPrefetchRouterAction();
+
+        MockQueryRouter router = new MockQueryRouter();
+        action.route(q, router, resolver);
+        assertEquals(1, router.getQueryCount());
+
+        PrefetchSelectQuery prefetch = (PrefetchSelectQuery)
router.getQueries().get(0);
+
+        assertSame(employee, prefetch.getRoot());
+        assertEquals(Expression.fromString("(db:PERSON_TYPE = 'EE' "
+                + "or db:PERSON_TYPE = 'EM')"), prefetch.getQualifier());
+    }
+
     public void testPrefetchManager() throws Exception {
         ObjEntity departmentEntity =
resolver.lookupObjEntity(Department.class);
         SelectQuery q = new SelectQuery(Manager.class,
ExpressionFactory.matchExp(

On Thu, Sep 26, 2013 at 4:45 PM, Mike Kienenberger <mkienenb@gmail.com> wrote:
> I think the reason this is working for me is because almost all of my
> qualifiers are "invalidated = 'N'" but this isn't the case in the
> tests, and it's grabbing the qualifier from the wrong entity.
>
> On Thu, Sep 26, 2013 at 4:08 PM, Mike Kienenberger <mkienenb@gmail.com> wrote:
>> So I'm investigating this situation.
>>
>>         PrefetchSelectQuery prefetchQuery = new PrefetchSelectQuery(
>>                 prefetchPath,
>>                 relationship);
>>
>> I replaced
>>
>>         prefetchQuery.setQualifier(classDescriptor.getEntity().translateToRelatedEntity(
>>                 queryQualifier,
>>                 prefetchPath));
>>
>> with this instead:
>>
>>         prefetchQuery.setQualifier(relationship.getTargetEntity().translateToRelatedEntity(
>>                 queryQualifier,
>>                 prefetchPath));
>>
>> which caused a number of cayenne tests to fail during build, but seems
>> to be generating correct prefetching query qualifiers.
>>
>> On Tue, Sep 24, 2013 at 4:16 PM, Mike Kienenberger <mkienenb@gmail.com> wrote:
>>> Added as Issue CAY-1875 - PrefetchSelectQuery incorrectly applies
>>> entity qualifiers
>>>
>>> On Tue, Sep 24, 2013 at 1:48 PM, Andrus Adamchik <andrus@objectstyle.org>
wrote:
>>>>
>>>>> From what testing I've done so far, the qualifier isn't put on for
>>>>> prefetch queries, which leaves me at the same situation as when using
>>>>> my datacontext delegate.
>>>>
>>>> This is bad and is not supposed to happen. Appears to be a bug. I am checking
SelectQueryPrefetchRouterAction, and it applies *root* entity qualifier to prefetch query
instead of prefetched entity. Should be a relatively easy fix for "disjoint" prefetches at
least.
>>>>
>>>>> What do you think of my general idea of exposing PrefetchSelectQueries
to the DataContextDelegate?
>>>>
>>>> FWIW, I was hoping DataContextDelegate itself is on the way out.
>>>>
>>>> So I'd start with entity qualifier, as it is intended exactly for what you
are trying to do - filtering selects (presuming we fix the bug above). The "special DataContext"
case where the qualifier should be ignored can probably be handled by starting a separate
ServerRuntime, where you can strip off the qualifiers. For whatever overhead it creates (ideally
not much), this has an advantage of cleanly separating "spaces" with different ORM rules.
>>>>
>>>> Andrus
>>>>
>>>>
>>>> On Sep 24, 2013, at 8:03 PM, Mike Kienenberger <mkienenb@gmail.com>
wrote:
>>>>> From what testing I've done so far, the qualifier isn't put on for
>>>>> prefetch queries, which leaves me at the same situation as when using
>>>>> my datacontext delegate.
>>>>>
>>>>> And I also have one case where I need to be able to disable the
>>>>> qualifier on a specific entity for a specific datacontext.
>>>>> I don't think this is possible even if the modeler qualifier was
>>>>> working in all other cases.   I consider doing something else, like an
>>>>> SQL template, but I'd still have entities being fetched in this
>>>>> situation where foreign keys from that entity wouldn't find a matching
>>>>> foreign entity due to qualifier restriction.
>>>>>
>>>>>
>>>>>
>>>>> On Tue, Sep 24, 2013 at 12:07 PM, Andrus Adamchik
>>>>> <andrus@objectstyle.org> wrote:
>>>>>>>> I still need a way to filter all query
>>>>>>>> types for specific entities to filter out certain entities
(appending
>>>>>>>> "where INVALIDATED = 'N'").
>>>>>>
>>>>>> Is this a global rule, or does it depend on some context (like user
role)? If it's the former, you can add a qualifier to affected entities in the Modeler.
>>>>>>
>>>>>> Andrus
>>>>>>
>>>>>> On Sep 24, 2013, at 7:00 PM, Mike Kienenberger <mkienenb@gmail.com>
wrote:
>>>>>>
>>>>>>> Here's one possible way to add support for intercepting prefetch
>>>>>>> queries.   I'm not entirely certain it's the best way, but I
didn't
>>>>>>> see another obvious point.
>>>>>>>
>>>>>>> What I did was to call
>>>>>>> QueryRouter.willPerformQuery(PrefetchSelectQuery query) before
routing
>>>>>>> the newly-created prefetch select query.
>>>>>>>
>>>>>>> For DataDomainQueryAction, this will call context.willPerformQuery()
>>>>>>> if there's a non-null context.
>>>>>>> For anything else (DataDomainLegacyQueryAction, MockQueryRouter),
it's a noop.
>>>>>>>
>>>>>>> If the returned query is null, then we skip routing the query
and
>>>>>>> return either true or false.   I picked true since it might be
useful
>>>>>>> to process children of the prefetch even if the prefetch is not
>>>>>>> skipped.   My own use case is never going to return null, so
I'm fine
>>>>>>> with false.
>>>>>>>
>>>>>>> There's also no reason why I picked
>>>>>>> QueryRouter.willPerformQuery(PrefetchSelectQuery query) instead
of
>>>>>>> QueryRouter.willPerformQuery(Query query) other than it made
it more
>>>>>>> obvious that this method was only being used for
>>>>>>> PrefetchSelectQueries.   But there may be other kinds of queries
which
>>>>>>> should also be going through this method.   The more I think
about
>>>>>>> this, the more reasonable it seems have it be Query since developers
>>>>>>> might be writing their own Query types, and any Queries being
created
>>>>>>> internally should be exposed through
>>>>>>> DataContextDelegate.willPerformQuery, and the QueryRouter is
the most
>>>>>>> likely place to be able to forward such new queries.
>>>>>>>
>>>>>>> This has solved my issues with prefetching under 3.1.   I'm still
open
>>>>>>> to suggestions for solving my specific problem another way in
the
>>>>>>> application code (adding database table views isn't an option),
but I
>>>>>>> think exposing prefetch queries (as well as others) is something
we
>>>>>>> should be supporting in Cayenne.
>>>>>>>
>>>>>>>
>>>>>>> Index: framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/DataDomainLegacyQueryAction.java
>>>>>>> ===================================================================
>>>>>>> --- framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/DataDomainLegacyQueryAction.java
>>>>>>>  (revision 1524993)
>>>>>>> +++ framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/DataDomainLegacyQueryAction.java
>>>>>>>  (working copy)
>>>>>>> @@ -27,6 +27,7 @@
>>>>>>>
>>>>>>> import org.apache.cayenne.CayenneRuntimeException;
>>>>>>> import org.apache.cayenne.map.DataMap;
>>>>>>> +import org.apache.cayenne.query.PrefetchSelectQuery;
>>>>>>> import org.apache.cayenne.query.Query;
>>>>>>> import org.apache.cayenne.query.QueryMetadata;
>>>>>>> import org.apache.cayenne.query.QueryRouter;
>>>>>>> @@ -163,4 +164,8 @@
>>>>>>>
>>>>>>>        return q != null ? q : executedQuery;
>>>>>>>    }
>>>>>>> +
>>>>>>> +    public Query willPerformQuery(PrefetchSelectQuery prefetchQuery)
{
>>>>>>> +        return prefetchQuery;
>>>>>>> +    }
>>>>>>> }
>>>>>>> Index: framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/DataDomainQueryAction.java
>>>>>>> ===================================================================
>>>>>>> --- framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/DataDomainQueryAction.java
>>>>>>>  (revision 1524993)
>>>>>>> +++ framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/DataDomainQueryAction.java
>>>>>>>  (working copy)
>>>>>>> @@ -772,4 +772,14 @@
>>>>>>>            }
>>>>>>>        }
>>>>>>>    }
>>>>>>> +
>>>>>>> +    public Query willPerformQuery(PrefetchSelectQuery prefetchQuery)
{
>>>>>>> +        // Notify DataContextDelegate that we have created a
new
>>>>>>> PrefetchSelectQuery
>>>>>>> +        if (null != context) {
>>>>>>> +            Query transformedQuery =
>>>>>>> context.nonNullDelegate().willPerformQuery(context, prefetchQuery);
>>>>>>> +            return transformedQuery;
>>>>>>> +        } else {
>>>>>>> +            return prefetchQuery;
>>>>>>> +        }
>>>>>>> +    }
>>>>>>> }
>>>>>>> Index: framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/query/QueryRouter.java
>>>>>>> ===================================================================
>>>>>>> --- framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/query/QueryRouter.java
>>>>>>>  (revision 1524993)
>>>>>>> +++ framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/query/QueryRouter.java
>>>>>>>  (working copy)
>>>>>>> @@ -49,4 +49,6 @@
>>>>>>>     * @throws NullPointerException if a map parameter is null.
>>>>>>>     */
>>>>>>>    QueryEngine engineForDataMap(DataMap map);
>>>>>>> +
>>>>>>> +    Query willPerformQuery(PrefetchSelectQuery prefetchQuery);
>>>>>>> }
>>>>>>> Index: framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/query/SelectQueryPrefetchRouterAction.java
>>>>>>> ===================================================================
>>>>>>> --- framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/query/SelectQueryPrefetchRouterAction.java
>>>>>>>  (revision 1524993)
>>>>>>> +++ framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/query/SelectQueryPrefetchRouterAction.java
>>>>>>>  (working copy)
>>>>>>> @@ -114,9 +114,15 @@
>>>>>>>
>>>>>>>        // pass prefetch subtree to enable joint prefetches...
>>>>>>>        prefetchQuery.setPrefetchTree(node);
>>>>>>> -
>>>>>>> +
>>>>>>> +        Query transformedQuery = router.willPerformQuery(prefetchQuery);
>>>>>>> +        if (null == transformedQuery) {
>>>>>>> +            // Not sure if we want to return false instead.
>>>>>>> Returning true seems safer.
>>>>>>> +            return true;
>>>>>>> +        }
>>>>>>> +
>>>>>>>        // route...
>>>>>>> -        prefetchQuery.route(router, resolver, null);
>>>>>>> +        transformedQuery.route(router, resolver, null);
>>>>>>>        return true;
>>>>>>>    }
>>>>>>>
>>>>>>> Index: framework/cayenne-jdk1.5-unpublished/src/test/java/org/apache/cayenne/query/MockQueryRouter.java
>>>>>>> ===================================================================
>>>>>>> --- framework/cayenne-jdk1.5-unpublished/src/test/java/org/apache/cayenne/query/MockQueryRouter.java
>>>>>>>  (revision 1524993)
>>>>>>> +++ framework/cayenne-jdk1.5-unpublished/src/test/java/org/apache/cayenne/query/MockQueryRouter.java
>>>>>>>  (working copy)
>>>>>>> @@ -50,4 +50,8 @@
>>>>>>>    public QueryEngine engineForDataMap(DataMap map) {
>>>>>>>        return new MockQueryEngine();
>>>>>>>    }
>>>>>>> +
>>>>>>> +    public Query willPerformQuery(PrefetchSelectQuery prefetchQuery)
{
>>>>>>> +        return prefetchQuery;
>>>>>>> +    }
>>>>>>> }
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Mon, Sep 23, 2013 at 7:04 PM, Mike Kienenberger <mkienenb@gmail.com>
wrote:
>>>>>>>> All of my tests pass now, but I'm still hitting a few issues
for 3.1
>>>>>>>> that the tests didn't reveal.
>>>>>>>>
>>>>>>>> In previous versions (not sure when it changed), there existed
the
>>>>>>>> ability to intercept prefetch queries using
>>>>>>>> DataContextDelegate.willPerformQuery() or willPerformGenericQuery().
>>>>>>>> Those queries are no longer available -- only the original
query with
>>>>>>>> the prefetchTree goes through those methods.
>>>>>>>>
>>>>>>>> It's the end of the day here, so I haven't traced through
the code yet
>>>>>>>> to see what's going on, but I still need a way to filter
all query
>>>>>>>> types for specific entities to filter out certain entities
(appending
>>>>>>>> "where INVALIDATED = 'N'").   I've got this working for select
>>>>>>>> queries, relationship queries, objectIdQueries, but not prefetch
>>>>>>>> queries.
>>>>>>>>
>>>>>>>> And I'm still wondering what the difference between
>>>>>>>> willPerformGenericQuery and willPerformQuery might be.  
So far, I
>>>>>>>> just forward willPerformGenericQuery requests through my
>>>>>>>> willPerformQuery code.
>>>>>>>
>>>>>>
>>>>>
>>>>

Mime
View raw message