db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Greg Monroe" <Greg.Mon...@DukeCE.com>
Subject New Summary Query Helper and FunctionFactory features
Date Fri, 28 Mar 2008 17:11:18 GMT
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


Mime
View raw message