You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by Greg Monroe <Gr...@DukeCE.com> on 2008/02/11 21:19:36 UTC
Include new Summary Query Helper and FunctionFactory features in 3.3 or not?
I'm just about done with a summary query "helper"
class and a SQL FunctionFactory methodology that I
think will fill a long time "hole" in Torque's
base functionality.
But before I just check it in, I thought I'd better
see if folks would be against adding this "new"
capability to 3.3.
Best practices says NO... but please note that this is
99% new runtime classes that don't change any of the
current tested class methods. The only change to
existing classes are a new method being added to the
DB interface and a default implementation being added
to the DBAbstract class.
IMHO, since it doesn't change or break any tested
existing feature, the benefits of getting this
into a formal release is worth it.
Here's a description of this new capability.
The biggest "user" change is the addition of a
SummaryHelper class that makes it very easy to create
queries that include "Aggregate" functions. E.g.
queries like:
Select Employee Sum(hours),avg(hours),min(hours),max(hours)
From time_sheet
Where Type = 1
Group By Employee
Order By Employee ASC
Here's how you would do that with the SummaryHelper class:
SummaryHelper sHelp = new SummaryHelper();
Criteria c = new Criteria();
c.add(TimeSheetPeer.TYPE, 1);
c.addAscendingOrderBy(TimeSheetPeer.EMPLOYEE);
sHelper.addGroupBy(TimeSheetPeer.EMPLOYEE);
sHelper.addAggregate(FunctionFactory.Sum(TimeSheetPeer.HOURS),"Hours");
sHelper.addAggregate(FunctionFactory.Avg(TimeSheetPeer.HOURS),"Avg_Hrs")
;
sHelper.addAggregate(FunctionFactory.Min(TimeSheetPeer.HOURS),"Min_Hrs")
;
sHelper.addAggregate(FunctionFactory.Max(TimeSheetPeer.HOURS),"Max_Hrs")
;
List results = sHelper.summarize( c );
The results list will be an OrderedMap with a key of either
the group by column name or the name specified for the aggregate
function (e.g. EMPLOYEE or Hours). The value will be a Village
Value Class. Below is the basic way to do this.
String emp = results.get("EMPLOYEE").asString();
int hours = results.get("Hours").asInt();
In order to support this in a DB server specific manner,
I've created a new org.apache.torque.util.functions
package. This has a generic SQLFunction interface, a
FunctionFactory class, and other supporting classes.
The FunctionFactory class is responsible for creating
DB Adaptor specific SQLFunction implementations. This is
Done by calling a new DB interface method with the
signature of:
public Class getFunctionClass( FunctionEnum type )
Note that FunctionEnum is like SQLEnum, just a list
of supported function type. This is used as a key
to look up the SQLFunction implementation class that
supports the (potentially DB specific) function.
Currently, I have SQL99 standard implementation for
All the "Aggregate" functions, e.g. AVG, COUNT, MAX,
MIN, and SUM. The DBAbstract class will be modified
to use these by default. These are pretty standard
across all the DB server types Torque supports so
this should be a good starting point.
However, if there is need for DB specific functions,
it is easy for the specific DB adaptor to replace
the SQL 99 standard with its own function.
Also, the SQLFunction interface is designed with an
eye towards expanding FunctionFactory's support into
other SQL standard function categories (like Date
and String).
Questions? Comments?
If people think it's needed, I'll make the JavaDocs and
code available via the web.
Greg
Greg Monroe <Mo...@DukeCE.com> (919)680-5050
C&IS Solutions Team Lead
Duke Corporate Education, Inc.
330 Blackwell St.
Durham, NC 27701
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
Re: Include new Summary Query Helper and FunctionFactory features in 3.3 or not?
Posted by Brendan Miller <bm...@dotster.com>.
> Ultimately, it boils down to the fact that we've got
> such long release cycles that if it doesn't get into 3.3
> then it will be sitting in the CVS version for a long
> time. Maybe the year or more that 4.0 might take.
As I've mentioned before for some other [pet] issues, I suggest we
consider a 3.4 release cycle for this and other possible inclusions.
I know the "4.0 wishlist" has been around for years, but is it possible
to start 4.0 work whilst letting 3.4 things trickle in?
If there was a "we want to include as many nagging issues in a 3.4
release by <this date>", it my propel people to get with the program
on fixing some of the leftover JIRAs.
Just my thoughts.
Brendan
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
RE: Include new Summary Query Helper and FunctionFactory features in 3.3 or not?
Posted by Greg Monroe <Gr...@DukeCE.com>.
Yeah, you're right... I just figured I'd give the 1%
chance that people would "go yeah, that's a feature I
really wanted... lets go for it" a shot. It is really
a total new set of code with no big changes to the
tested code.
Ultimately, it boils down to the fact that we've got
such long release cycles that if it doesn't get into 3.3
then it will be sitting in the CVS version for a long
time. Maybe the year or more that 4.0 might take.
FWIW, it's ready for check-in with all the JavaDoc info,
licensing, code style, and a test project class that
tests if it works (tests fine with MS SQL and MySQL 4.0 so
far) The code can now be found at:
http://people.apache.org/~gmonroe/torque/TorqueSummaryHelper.zip
> -----Original Message-----
> From: Thomas Vandahl [mailto:tv@apache.org]
> Sent: Wednesday, February 13, 2008 6:50 AM
> To: Apache Torque Developers List
> Subject: Re: Include new Summary Query Helper and FunctionFactory
> features in 3.3 or not?
>
> Greg Monroe wrote:
> > But before I just check it in, I thought I'd better
> >
> > see if folks would be against adding this "new"
> >
> > capability to 3.3.
> Strongly -1. We are voting on the 3.3 release already.
>
> Bye, Thomas.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-dev-help@db.apache.org
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: Include new Summary Query Helper and FunctionFactory features
in 3.3 or not?
Posted by Thomas Vandahl <tv...@apache.org>.
Greg Monroe wrote:
> But before I just check it in, I thought I'd better
>
> see if folks would be against adding this "new"
>
> capability to 3.3.
Strongly -1. We are voting on the 3.3 release already.
Bye, Thomas.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
New Summary Query Helper and FunctionFactory features
Posted by Greg Monroe <Gr...@DukeCE.com>.
Below is the thread that was part of the 3.3 release
Discussions. Now that 3.3. is released, I'm going to
check in on Monday unless anyone has problems with
the way I've started implementing function support.
This has all the JavaDoc info, licensing, code style
checks, and a test project class that tests if it
works (tests fine with MS SQL and MySQL 4.0 so far).
The code can now be found at:
http://people.apache.org/~gmonroe/torque/TorqueSummaryHelper.zip
> -----Original Message-----
> From: Greg Monroe [mailto:Greg.Monroe@DukeCE.com]
> Sent: Monday, February 11, 2008 3:20 PM
> To: Apache Torque Developers List
> Subject: Include new Summary Query Helper and FunctionFactory features
> in 3.3 or not?
>
> I'm just about done with a summary query "helper"
> class and a SQL FunctionFactory methodology that I
> think will fill a long time "hole" in Torque's
> base functionality.
....
> Here's a description of this new capability.
>
> The biggest "user" change is the addition of a
> SummaryHelper class that makes it very easy to create
> queries that include "Aggregate" functions. E.g.
> queries like:
>
> Select Employee Sum(hours),avg(hours),min(hours),max(hours)
> From time_sheet
> Where Type = 1
> Group By Employee
> Order By Employee ASC
>
> Here's how you would do that with the SummaryHelper class:
>
> SummaryHelper sHelp = new SummaryHelper();
> Criteria c = new Criteria();
> c.add(TimeSheetPeer.TYPE, 1);
> c.addAscendingOrderBy(TimeSheetPeer.EMPLOYEE);
> sHelper.addGroupBy(TimeSheetPeer.EMPLOYEE);
> sHelper.addAggregate(
> FunctionFactory.Sum(TimeSheetPeer.HOURS),"Hours");
> sHelper.addAggregate(
> FunctionFactory.Avg(TimeSheetPeer.HOURS),"Avg_Hrs");
> sHelper.addAggregate(
> FunctionFactory.Min(TimeSheetPeer.HOURS),"Min_Hrs");
> sHelper.addAggregate(
> FunctionFactory.Max(TimeSheetPeer.HOURS),"Max_Hrs");
> List results = sHelper.summarize( c );
>
> The results list will be an OrderedMap with a key of either
> the group by column name or the name specified for the aggregate
> function (e.g. EMPLOYEE or Hours). The value will be a Village
> Value Class. Below is the basic way to do this.
>
> String emp = results.get("EMPLOYEE").asString();
> int hours = results.get("Hours").asInt();
>
> In order to support this in a DB server specific manner,
> I've created a new org.apache.torque.util.functions
> package. This has a generic SQLFunction interface, a
> FunctionFactory class, and other supporting classes.
>
> The FunctionFactory class is responsible for creating
> DB Adaptor specific SQLFunction implementations. This is
> Done by calling a new DB interface method with the
> signature of:
>
> public Class getFunctionClass( FunctionEnum type )
>
> Note that FunctionEnum is like SQLEnum, just a list
> of supported function type. This is used as a key
> to look up the SQLFunction implementation class that
> supports the (potentially DB specific) function.
>
> Currently, I have SQL99 standard implementation for
> All the "Aggregate" functions, e.g. AVG, COUNT, MAX,
> MIN, and SUM. The DBAbstract class will be modified
> to use these by default. These are pretty standard
> across all the DB server types Torque supports so
> this should be a good starting point.
>
> However, if there is need for DB specific functions,
> it is easy for the specific DB adaptor to replace
> the SQL 99 standard with its own function.
>
> Also, the SQLFunction interface is designed with an
> eye towards expanding FunctionFactory's support into
> other SQL standard function categories (like Date
> and String functions).
>
> Questions? Comments?
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org