db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: JDOQL Subquery proposals
Date Tue, 23 Oct 2007 18:40:23 GMT
Hi Michael,

Looks good.

Craig

On Oct 23, 2007, at 2:35 AM, Michael Bouschen wrote:

> Hi,
>
> I looked at the sample queries we used when discussion the subquery  
> proposal. I modified them such that they work with the company  
> model used in the TCK and adapted them to the proposed API. I  
> propose to add these queries to a new TCK query test class testing  
> JDOQL subqueries.
>
> (1) Select employees who work more than the average of their  
> department employees
>
> Single-string JDOQL:
>  SELECT FROM Employee WHERE this.weeklyhours > (SELECT AVG 
> (e.weeklyhours) FROM this.department.employees e)
>
> Query API:
>  Query sub = pm.newQuery(Employee.class);
>  sub.setResult("avg(this.weeklyhours)");
>  Query q = pm.newQuery(Employee.class);
>  q.setFilter("this.weeklyhours> averageWeeklyhours");
>  q.addSubquery(sub, "double averageWeeklyhours",  
> "this.department.employees");
>
> (2) Select employees who work more than the average of the  
> employees in their department having the same manager?
>
> Single-string JDOQL:
>  SELECT FROM Employee WHERE this.weeklyhours >
>    (SELECT AVG(e.weeklyhours) FROM this.department.employees e  
> WHERE e.manager == this.manager)
>
> Query API:
>  Query sub = pm.newQuery(Employee.class);
>  sub.setResult("avg(this.weeklyhours)");
>  sub.setFilter("this.manager == :manager");
>  Query q = pm.newQuery(Employee.class);
>  q.setFilter("this.weeklyHours > averageWeeklyhours");
>  q.addSubquery(sub, "double averageWeeklyhours",  
> "this.department.employees", "this.manager");
>
> (3) select employees who work more than the average of all employees?
>
> Single-string JDOQL:
>  SELECT FROM Employee WHERE this.weeklyhours > (SELECT AVG 
> (e.weeklyhours) FROM Employee e)
>
> Query API:
>  Query sub = pm.newQuery(Employee.class);
>  sub.setResult("avg(this.weeklyhours)");
>  Query q = pm.newQuery(Employee.class);
>  q.setFilter("this.weeklyHours > averageWeeklyhours");
>  q.addSubquery(sub, "double averageWeeklyhours", null);
> The null value passed as the candidateCollection expression  
> indicates that we're not overriding the candidates for the subquery  
> and thus it uses the entire extent of Employee.
>
> (4) Select employees hired after a particular date who work more  
> than the average of all employees?
>
> Single-string JDOQL:
>  SELECT FROM Employee WHERE this.hiredate > :hired &&  
> this.weeklyhours> (SELECT AVG(e.weeklyhours) FROM Employee e)
>
> Query API:
>  Query sub = pm.newQuery(Employee.class);
>  sub.setResult("avg(this.weeklyhours)");
>  Query q = pm.newQuery(Employee.class);
>  q.setFilter("this.hiredate > :hired && this.weeklyhours >  
> averageWeeklyhours");
>  q.addSubquery(sub, "double averageWeeklyhours", null);
>
> (5) Select employees hired after a particular date who work more  
> than the average of all employees of the same manager?
>
> Single-string JDOQL:
>  SELECT FROM Employee WHERE this.hiredate > :hired &&  
> this.weeklyhours >
>    (SELECT AVG(e.weeklyhours) FROM Employee e WHERE e.manager ==  
> this.manager)
>
> Query API:
>  Query sub = pm.newQuery(Employee.class);
>  sub.setResult("avg(this.weeklyhours)");
>  sub.setFilter("this.manager == :manager");
>  Query q = pm.newQuery(Employee.class);
>  q.setFilter("this.hiredate > :hired && this.weeklyhours>  
> averageWeeklyhours");
>  q.addSubquery(sub, "double averageWeeklyhours", null,  
> "this.manager");
>
> (6) Select employees who work more than the average of all  
> employees of the same manager?
>
> Single-string JDOQL:
>  SELECT FROM Employee WHERE this.weeklyhours >
>    (SELECT AVG(e.weeklyhours) FROM Employee e WHERE e.manager ==  
> this.manager)
>
> Query API:
>  Query sub = pm.newQuery(Employee.class);
>  sub.setResult("avg(this.weeklyhours)");
>  sub.setFilter("this.manager == :manager");
>  Query q = pm.newQuery(Employee.class);
>  q.setFilter("this.weeklyHours > averageWeeklyhours");
>  q.addSubquery(sub, "double averageWeeklyhours", null,  
> "this.manager");
>
> Regards Michael
>
>> It looks like the spec update I just completed forgot to include  
>> some of the APIs that we discussed here:
>>
>>  addSubquery(Query subquery, String variableDeclaration, String  
>> candidateCollectionExpr, String parameter);
>>  addSubqueryWithArray(Query subquery, String variableDeclaration,  
>> String candidateCollectionExpr, String[] parameters);
>>
>> In the api2 this becomes
>>  addSubquery(Query subquery, String variableDeclaration, String  
>> candidateCollectionExpr, String... parameters);
>>
>>  addSubquery(Query subquery, String variableDeclaration, String  
>> candidateCollectionExpr, Map parameters);
>>
>> We also need to describe what happens if the same  
>> variableDeclaration is re-used. I think it makes sense that the  
>> variableDeclaration is the key to a map of subqueries in the outer  
>> query, and if addSubquery is used with the same  
>> variableDeclaration then it replaces the previous query. And if  
>> subquery is null, then that variableDeclaration is reset.
>>
>> We can explore some of these side issues when we see Michael's  
>> test cases.
>>
>> Craig
>>
>> On Oct 3, 2006, at 9:49 AM, Craig L Russell wrote:
>>
>>> Hi Wes and Michael,
>>>
>>> I'm just now reviewing this. Very nice indeed.
>>>
>>> We need to consider what happens if a subquery is modified after  
>>> being added to an outer query. I'd like to propose that we treat  
>>> the subquery as supplying certain pieces and excluding others. We  
>>> have a similar situation with serialization of a Query instance,  
>>> that I think we can model for this purpose.
>>>
>>> <spec>
>>> The class implementing the Query interface must be serializable.  
>>> The serialized fields in-
>>> clude the candidate class, the filter, parameter declarations,  
>>> variable declarations, imports,
>>> ordering specification, uniqueness, result specification,  
>>> grouping specification, and result
>>> class. The candidate collection, limits on size, and number of  
>>> skipped instances are not se-
>>> rialized. If a serialized instance is restored, it loses its  
>>> association with its former Persis-
>>> tenceManager.
>>> </spec>
>>>
>>> I'd suggest that we adapt this for subqueries as follows:
>>>
>>> The query parameter is unmodified as a result of the addSubquery  
>>> or subsequent execution
>>> of the outer query. Only some of the query parts are copied for  
>>> use as the subquery. The parts in-
>>> clude the candidate class, filter, parameter declarations,  
>>> variable declarations, imports,
>>> ordering specification, uniqueness, result specification, and  
>>> grouping specification.
>>> The association with a PersistenceManager, the candidate  
>>> collection, result
>>> class, limits on size, and number of skipped instances are not used.
>>>
>>> The implications are:
>>>
>>> changes made to the subquery after addSubquery are not reflected  
>>> in the outer query
>>>
>>> the subquery can be executed without affecting the outer query
>>>
>>> the same subquery can be used multiple times in the same or other  
>>> outer queries
>>>
>>> the candidate instances of the subquery itself are not  
>>> considered; either the candidates are identified in the  
>>> addSubquery method or the extent of the candidate class is used
>>>
>>> serialized/restored queries can be used as parameters for  
>>> addSubquery
>>>
>>> there is no limitation on the use of a query that is bound to a  
>>> different PersistenceManager
>>>
>>> the range feature is not usable (this is probably a good thing  
>>> for subqueries)
>>>
>>> A few more comments below.
>>>
>>> On Sep 26, 2006, at 1:30 PM, Michael Bouschen wrote:
>>>
>>>> Hi Wes,
>>>>
>>>> I agree with what you are proposing below. Some more comments  
>>>> inline ...
>>>>> Hi Michael, thanks for reviewing.
>>>>>
>>>>> I agree, it is too much to overload the existing methods.  I  
>>>>> like your suggestion -- the only modification I would add is  
>>>>> that the Query parameter come first as I think that would be  
>>>>> slightly more readable/self-documenting.
>>>>>
>>>>> e.g. addSubquery(Query sub, String variableDeclaration, String  
>>>>> candidateCollectionExpression)
>>>> Sounds good.
>>>>>
>>>>> Some minor points:
>>>>> addSubquery() in all its forms should throw an exception if ...
>>>>> -- sub.getPersistenceManager() != this.getPersistenceManager()
>>>
>>> no; see above
>>>
>>>>> -- sub has been closed
>>>
>>> queries are not closed; results are closed
>>>
>>>>> -- "this" has been closed (duh)
>>>
>>> queries are not closed; results are closed
>>>
>>>>> -- "this" has been made unmodifiable
>>>>> -- the derived type of the candidateCollectionExpression does  
>>>>> not match the declared extent type of sub
>>>>> -- the variableDeclaration type does not match the declared  
>>>>> return type of sub
>>>> I agree. Just a minor change: the element type of the  
>>>> candidateCollectionExpression must be compatible with the  
>>>> candidate class of the subquery.
>>>>>
>>>>> It would make things simpler if the IgnoreCache setting had to  
>>>>> be the same for both as well.  Or we might say the IgnoreCache  
>>>>> value for the outer query overrides any subqueries.
>>>> Yes, the IgnoreCache value of the outer query should win.
>>>>>
>>>>> Also, while the candidates for the subquery will be overridden  
>>>>> at the time when execute() is invoked on the outer query, it  
>>>>> would be nice if that was non-mutating, though we should take  
>>>>> some advice from implementers on what the following use case  
>>>>> should do:
>>>>>
>>>>> Query avgSalary = pm.newQuery(Employee.class);
>>>>> avgSalary.setResult("avg(this.salary)");
>>>>> avgSalary.setCandidates(someEmployees); // for argument's sake
>>>>>
>>>>> // Invoke directly -- against someEmployees collection
>>>>> Float result1 = (Float) avgSalary.execute();
>>>>>
>>>>> Query aboveDeptAvg = pm.newQuery(Employee.class, "this.salary >  
>>>>> avgSalary");
>>>>> aboveDeptAvg.addSubquery(avgSalary, "float avgSalary",  
>>>>> "this.department.employees");
>>>>>
>>>>> // Invoke as subquery -- someEmployees collection is ignored
>>>>> Collection employees = (Collection) aboveDeptAvg.execute();
>>>>>
>>>>> // Now invoke the subquery directly again -- does this use  
>>>>> someEmployees?
>>>
>>> Yes. When avgSalary was used as a subquery, the relevant parts of  
>>> the query, not including the candidates, were used and avgSalary  
>>> itself was not modified.
>>>
>>>>> Float result2 = (Float) avgSalary.execute();
>>>>>
>>>>> I would prefer that result1.equals(result2) -- this implies  
>>>>> that the implementation must internally revert the candidate  
>>>>> extent or collection for avgSalary after it is used as a  
>>>>> subquery; for the spec it just means that using a Query as a  
>>>>> subquery does not modify any of its own candidate settings.
>>>> Yes, I would prefer this, too. So let's see whether there are  
>>>> any issues with this from the implementation point of view.
>>>
>>> I think it's easier for everyone to consider addSubquery to  
>>> simply copy the parts of the query of interest and leave the  
>>> query intact.
>>>
>>> Craig
>>>>
>>>> Thanks again for the feedback.
>>>>
>>>> Regards Michael
>>>>>
>>>>> Wes
>>>>>
>>>>>
>>>>> Michael Bouschen wrote:
>>>>>
>>>>>> Hi Wes,
>>>>>>
>>>>>> thanks for the feedback, it's definitely not too late.
>>>>>>
>>>>>> I like your proposal. It allows subqueries being supported in  
>>>>>> both the SSJDOQL and the query API. I like the the idea of  
>>>>>> explicitly setting subquery's candidate collection by passing  
>>>>>> an expression of the outer query (and use the same mechanism  
>>>>>> for the parameters of the subquery). This solves the biggest  
>>>>>> problem I had with using a separate Query instance for the  
>>>>>> subquery: now the subquery instance is self-contained and  
>>>>>> compiles, because it does not explicitly use an expression  
>>>>>> from the outer query.
>>>>>>
>>>>>> I'm just wondering whether we could find a more intuitive  
>>>>>> syntax, because name(candidateExpression 
>>>>>> [,parameterExpression...]) looks more like a method than a  
>>>>>> variable. Furthermore, as a corner case, it might be possible  
>>>>>> that a query uses more than one subquery. All the subqueries  
>>>>>> would have to be defined in a single call of  
>>>>>> outer.declareVariables. So how about, if we introduce a new  
>>>>>> method called addSubquery to bind a single subquery to the  
>>>>>> outer query. The method takes separate arguments for the  
>>>>>> candidate collection expression and the parameters (if any).  
>>>>>> Actually the parameter handling could be very similar to the  
>>>>>> parameters of the execute call:
>>>>>>  addSubquery(String variableDeclaration, Query subquery,  
>>>>>> String candidateCollectionExpr);
>>>>>>  addSubquery(String variableDeclaration, Query subquery,  
>>>>>> String candidateCollectionExpr, String parameter);
>>>>>>  addSubqueryWithArray(String variableDeclaration, Query  
>>>>>> subquery, String candidateCollectionExpr, String[] parameters);
>>>>>>  addSubqueryWithMap(String variableDeclaration, Query  
>>>>>> subquery, String candidateCollectionExpr, Map parameters);
>>>>>>
>>>>>> Looking at the first example from below, the definition of the  
>>>>>> subquery would be the same. The only line that changes is the  
>>>>>> declareVariable call. It is replaced by:
>>>>>>  q.addSubquery(""float averageSalary", sub,  
>>>>>> "this.department.employees");
>>>>>>
>>>>>> Just for completeness we should add a method to clear the  
>>>>>> subqueries, such that you can reuse the outer query and bind  
>>>>>> new subqueries for another execute call:
>>>>>>   clearSubqueries();
>>>>>>
>>>>>> What do you think?
>>>>>>
>>>>>> If we think the above is option I would come up with an  
>>>>>> updated summary of JDOQL changes to support subqueries and  
>>>>>> updated version of the sample queries.
>>>>>>
>>>>>> Regards Michael
>>>>>>
>>>>>>> I'm ridiculously late in responding to this thread but if I 

>>>>>>> may be so bold, I'll make a further suggestion.
>>>>>>>
>>>>>>> I like everything about the proposed approach except the  
>>>>>>> requirement that subquery definitions must resort to single-

>>>>>>> string JDOQL syntax, even when using the API-based methods. 
 
>>>>>>> I think this introduces asymmetry and discourages reuse and 

>>>>>>> modularity.
>>>>>>>
>>>>>>> I would really like to see the ability to map variables to  
>>>>>>> (sub)Query objects.  There are two new capabilities  
>>>>>>> introduced in the SSJDOQL version, and my opinion is that the
 
>>>>>>> API should match these feature by feature.  The two features
 
>>>>>>> are:
>>>>>>> (1) The ability for a subquery to use an expression defined 

>>>>>>> on the outer query as its candidate set.
>>>>>>> (2) The ability for a subquery to use expressions defined on
 
>>>>>>> the outer query as parameters.
>>>>>>>
>>>>>>> Therefore, for parity, we need an API-based way to declare  
>>>>>>> these mappings, so that subqueries can be assigned both their
 
>>>>>>> candidate collections and their parameters dynamically.
>>>>>>>
>>>>>>> I propose an overloaded version of declareVariables that  
>>>>>>> allows mapping variable names used in the outer query to (sub)

>>>>>>> Query instances that are correlated with candidates and  
>>>>>>> parameters.
>>>>>>>
>>>>>>> void declareVariables(String variableList, Query... subquery)
>>>>>>>
>>>>>>> The variable declaration syntax should be extended to allow 

>>>>>>> parameterized variables of the form "name(candidateExpression

>>>>>>> [,parameterExpression...])".  "name" defines a variable name
 
>>>>>>> in the query; "candidateExpression" defines an expression  
>>>>>>> (rooted in the namespace of the outer query) for the  
>>>>>>> candidate extent to be bound to the subquery, where "null"  
>>>>>>> signifies that the subquery candidate set is not being  
>>>>>>> limited.  "parameterExpression" identifies dynamic values for
 
>>>>>>> parameters declared by the subquery, again rooted in the  
>>>>>>> namespace of the outer query doing the binding.
>>>>>>>
>>>>>>> To touch up Michael's examples:
>>>>>>>
>>>>>>> Select employees who make more than the average of their  
>>>>>>> department employees?
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG 
>>>>>>> (e.salary) FROM this.department.employees e)
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>>>
>>>>>>> // Subquery definition is generic: for a given set of  
>>>>>>> Employees, return the average salary
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setResult("avg(salary)");
>>>>>>>
>>>>>>> // Bind the subquery to the master query by identifying the 

>>>>>>> candidate set
>>>>>>> q.declareVariables("float averageSalary 
>>>>>>> (this.department.employees)", sub);
>>>>>>>
>>>>>>>> Select employees who make more than the average of the  
>>>>>>>> employees in their department at the same pay scale?
>>>>>>>
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>> SELECT FROM Employee WHERE this.salary >
>>>>>>>   (SELECT AVG(e.salary) FROM this.department.employees e  
>>>>>>> WHERE e.payScale == this.payScale)
>>>>>>>
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>>>
>>>>>>> // This subquery generically defines the average salary of a
 
>>>>>>> set of Employees at a given PayScale
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setFilter("this.payScale == ps");
>>>>>>> sub.declareParameters("PayScale ps");
>>>>>>> sub.setResult("avg(salary)");
>>>>>>>
>>>>>>> // Bind both a candidate set and the payScale parameter.
>>>>>>> q.declareVariables("float averageSalary 
>>>>>>> (this.department.employees, this.payScale)", sub);
>>>>>>>
>>>>>>>> Select employees who make more than the average of all  
>>>>>>>> employees?
>>>>>>>
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>>  SELECT FROM Employee WHERE this.salary > (SELECT AVG 
>>>>>>> (e.salary) FROM Employee e)
>>>>>>>  SELECT FROM Employee WHERE this.salary > (SELECT AVG 
>>>>>>> (this.salary) FROM Employee)
>>>>>>>
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setResult("avg(salary)");
>>>>>>> // The null value indicates that we're not overriding the  
>>>>>>> candidates for the subquery
>>>>>>> // and thus it uses the entire extent of Employee
>>>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>>>
>>>>>>>
>>>>>>>> Select employees named Joe who make more than the average
of  
>>>>>>>> all employees?
>>>>>>>
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary
 
>>>>>>> > (SELECT AVG(e.salary) FROM Employee e)
>>>>>>>
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.name == 'Joe' && this.salary > 

>>>>>>> averageSalary");
>>>>>>>
>>>>>>> // This subquery generically defines "the average of all  
>>>>>>> employeees"
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setResult("avg(salary)");
>>>>>>>
>>>>>>> // Note we could have reused the query instance from the  
>>>>>>> previous example.
>>>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>>>
>>>>>>>>
>>>>>>>> Select employees named Joe who make more than the average
of  
>>>>>>>> all employees at the same pay scale?
>>>>>>>
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>> SELECT FROM Employee WHERE this.name == 'Joe' && this.salary
>
>>>>>>>   (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale == 

>>>>>>> this.payScale)
>>>>>>>
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.name == 'Joe' && this.salary > 

>>>>>>> averageSalary");
>>>>>>>
>>>>>>> // Note that this is the same subquery instance as the  
>>>>>>> previous pay scale example
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setFilter("payScale == ps");
>>>>>>> sub.declareParameters("PayScale ps");
>>>>>>> sub.setResult("avg(salary)");
>>>>>>>
>>>>>>> q.declareVariables("float averageSalary(null)", sub);
>>>>>>>
>>>>>>>> Select employees who make more than the average of all  
>>>>>>>> employees at the same pay scale?
>>>>>>>
>>>>>>>
>>>>>>> Single-string JDOQL:
>>>>>>> SELECT FROM Employee WHERE this.salary > (SELECT AVG 
>>>>>>> (e.salary) FROM Employee e WHERE e.payScale == this.payScale)
>>>>>>>
>>>>>>> Query API:
>>>>>>> Query q = pm.newQuery(Employee.class);
>>>>>>> q.setFilter("this.salary > averageSalary");
>>>>>>>
>>>>>>> // Same again
>>>>>>> Query sub = pm.newQuery(Employee.class);
>>>>>>> sub.setFilter("ps == this.payScale");
>>>>>>> sub.declareParameters("PayScale ps");
>>>>>>>
>>>>>>> q.declareVariables("float averageSalary(null,  
>>>>>>> this.payScale)", sub);
>>>>>>>
>>>>>>> I'd like to hear other ideas for the exact syntax, but what 

>>>>>>> do you think of the general concept?
>>>>>>>
>>>>>>> Wes
>>>>>>>
>>>>>>>
>>>>> [chop]
>>>>
>>>>
>>>> -- 
>>>> Michael Bouschen        Tech@Spree Engineering GmbH
>>>> mailto:mbo.tech@spree.de    http://www.tech.spree.de/
>>>> Tel.:++49/30/235 520-33        Buelowstr. 66           Fax.:+ 
>>>> +49/30/2175 2012        D-10783 Berlin
>>>
>>> Craig Russell
>>> Architect, Sun Java Enterprise System http://java.sun.com/ 
>>> products/jdo
>>> 408 276-5638 mailto:Craig.Russell@sun.com
>>> P.S. A good JDO? O, Gasp!
>>>
>>
>> Craig Russell
>> Architect, Sun Java Enterprise System http://java.sun.com/products/ 
>> jdo
>> 408 276-5638 mailto:Craig.Russell@sun.com
>> P.S. A good JDO? O, Gasp!
>>
>
>
> -- 
> Tech@Spree Engineering GmbH  Tel.: +49/(0)30/235 520-33
> Buelowstr. 66                Fax.: +49/(0)30/217 520-12
> 10783 Berlin                 mailto:mbo.tech@spree.de  
> Geschaeftsfuehrung: Anna-Kristin Proefrock
> Sitz Berlin, Amtsgericht Charlottenburg, HRB 564 52
>

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


Mime
View raw message