You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jdo-dev@db.apache.org by Craig L Russell <Cr...@Sun.COM> on 2007/10/22 22:23:45 UTC
Re: JDOQL Subquery proposals
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!
Re: JDOQL Subquery proposals
Posted by Craig L Russell <Cr...@Sun.COM>.
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!
Re: JDOQL Subquery proposals
Posted by Michael Bouschen <mb...@spree.de>.
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