Mailing-List: contact jdo-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: jdo-dev@db.apache.org
Received-SPF: pass (idunn.apache.osuosl.org: domain tralfamadore.com
 designates 216.154.211.217 as permitted sender)
Message-ID: <4517F80A.7020408@tralfamadore.com>
Date: Mon, 25 Sep 2006 16:38:50 +0100
From: Wes Biggs <wes@tralfamadore.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
 rv:1.7.13) Gecko/20060414
MIME-Version: 1.0
To: Michael Bouschen <mbo.tech@spree.de>
CC: jdo-dev@db.apache.org, jdo-experts-ext@sun.com
Subject: Re: JDOQL Subquery proposals
References: <007101c68c16$c6c66de0$6963a8c0@DELLZILLA>
 <449B1735.2010400@spree.de> <043D9D30-827A-440C-8CF0-8B98EE723773@SUN.com>
 <44C7E148.2050805@spree.de> <450DA1EF.4020903@tralfamadore.com>
 <4516EB87.8050001@spree.de>
In-Reply-To: <4516EB87.8050001@spree.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: softfail (cacas.org: domain of transitioning
 wes@tralfamadore.com does not designate 62.189.209.116 as permitted sender)
 receiver=cacas.org; client-ip=62.189.209.116; helo=[10.51.14.78];
 envelope-from=wes@tralfamadore.com;
 x-software=spfmilter 0.97 http://www.acme.com/software/spfmilter/ with
 libspf2-1.0.0;

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)

Some minor points:
addSubquery() in all its forms should throw an exception if ...
-- sub.getPersistenceManager() != this.getPersistenceManager()
-- sub has been closed
-- "this" has been closed (duh)
-- "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

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.

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

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]