Re: JDOQL Subquery proposals

[Craig L Russell]

It looks like the spec update I just completed forgot to include some of the APIs that we discussed here:

In the api2 this becomes








Craig

On Oct 3, 2006, at 9:49 AM, Craig L Russell wrote:

addSubquery(Query subquery, String variableDeclaration, String candidateCollectionExpr, String parameter); addSubqueryWithArray(Query subquery, String variableDeclaration, String candidateCollectionExpr, String[] parameters); 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.

Hi Wes and Michael,

I'm just now reviewing this. Very nice indeed.



<spec>

tenceManager.
</spec>

I'd suggest that we adapt this for subqueries as follows:


class, limits on size, and number of skipped instances are not used.

The implications are:



the subquery can be executed without affecting the outer query





serialized/restored queries can be used as parameters for addSubquery





A few more comments below.

On Sep 26, 2006, at 1:30 PM, Michael Bouschen wrote:

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. 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- 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 changes made to the subquery after addSubquery are not reflected in 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 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)

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.

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();



// Invoke as subquery -- someEmployees collection is ignored
Collection employees = (Collection) aboveDeptAvg.execute();

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 aboveDeptAvg = pm.newQuery(Employee.class, "this.salary > avgSalary"); aboveDeptAvg.addSubquery(avgSalary, "float avgSalary", "this.department.employees"); // 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.

Craig

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.

Thanks again for the feedback.

Regards Michael

Wes


Michael Bouschen wrote:

Hi Wes,

thanks for the feedback, it's definitely not too late.








  clearSubqueries();

What do you think?



Regards Michael

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: 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.

I'm ridiculously late in responding to this thread but if I may be so bold, I'll make a further suggestion.

void declareVariables(String variableList, Query... subquery)



To touch up Michael's examples:



Single-string JDOQL:

Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.salary > averageSalary");


Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(salary)");

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. 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. Select employees who make more than the average of their department employees? SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM this.department.employees e) // Subquery definition is generic: for a given set of Employees, return the average 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 >


Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.salary > averageSalary");


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.

(SELECT AVG(e.salary) FROM this.department.employees e WHERE e.payScale == this.payScale) // This subquery generically defines the average salary of a set of Employees at a given PayScale q.declareVariables("float averageSalary (this.department.employees, this.payScale)", sub);

Select employees who make more than the average of all employees?

Single-string JDOQL:


Query API:
Query q = pm.newQuery(Employee.class);
q.setFilter("this.salary > averageSalary");
Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(salary)");

// and thus it uses the entire extent of Employee
q.declareVariables("float averageSalary(null)", sub);

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) // The null value indicates that we're not overriding the candidates for the subquery

Select employees named Joe who make more than the average of all employees?

Single-string JDOQL:


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


Query sub = pm.newQuery(Employee.class);
sub.setResult("avg(salary)");


q.declareVariables("float averageSalary(null)", sub);

SELECT FROM Employee WHERE this.name == 'Joe' && this.salary > (SELECT AVG(e.salary) FROM Employee e) // This subquery generically defines "the average of all employeees" // Note we could have reused the query instance from the previous example.

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 >


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


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 AVG(e.salary) FROM Employee e WHERE e.payScale == this.payScale) // Note that this is the same subquery instance as the previous pay scale example

Select employees who make more than the average of all employees at the same pay scale?

Single-string JDOQL:


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");





Wes

SELECT FROM Employee WHERE this.salary > (SELECT AVG(e.salary) FROM Employee e WHERE e.payScale == this.payScale) 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?

[chop]

--
Michael Bouschen                [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED]        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:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!

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

smime.p7s
Description: S/MIME cryptographic signature