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 Mon, 22 Oct 2007 20:23:45 GMT
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!


Mime
View raw message