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 Include new Summary Query Helper and FunctionFactory features in 3.3 or not?
Date Mon, 11 Feb 2008 20:19:36 GMT
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 <Monroe@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.
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message