From torque-dev-return-8357-apmail-db-torque-dev-archive=db.apache.org@db.apache.org Fri Mar 28 17:12:04 2008 Return-Path: Delivered-To: apmail-db-torque-dev-archive@www.apache.org Received: (qmail 70456 invoked from network); 28 Mar 2008 17:12:04 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 28 Mar 2008 17:12:04 -0000 Received: (qmail 56961 invoked by uid 500); 28 Mar 2008 17:12:03 -0000 Delivered-To: apmail-db-torque-dev-archive@db.apache.org Received: (qmail 56942 invoked by uid 500); 28 Mar 2008 17:12:03 -0000 Mailing-List: contact torque-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Unsubscribe: List-Help: List-Post: List-Id: "Apache Torque Developers List" Reply-To: "Apache Torque Developers List" Delivered-To: mailing list torque-dev@db.apache.org Received: (qmail 56931 invoked by uid 99); 28 Mar 2008 17:12:03 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 28 Mar 2008 10:12:03 -0700 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_HELO_PASS,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: domain of greg.monroe@dukece.com designates 152.3.166.24 as permitted sender) Received: from [152.3.166.24] (HELO dukece.com) (152.3.166.24) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 28 Mar 2008 17:11:13 +0000 Received: from ([10.123.20.204]) by smtpgw1.dukece.com with ESMTP id 4440076.3815135; Fri, 28 Mar 2008 13:11:18 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: New Summary Query Helper and FunctionFactory features Date: Fri, 28 Mar 2008 13:11:18 -0400 Message-ID: <8F5843B903F59D4C8C6806BB49A3911906016692@dukece-mail3.dukece.com> In-Reply-To: <8F5843B903F59D4C8C6806BB49A39119057D7A23@dukece-mail3.dukece.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: New Summary Query Helper and FunctionFactory features Thread-Index: Achs62w09huG7VVZQ8iBN25L5ZBUkwkCbwmQ References: <8F5843B903F59D4C8C6806BB49A39119057D7A23@dukece-mail3.dukece.com> From: "Greg Monroe" To: "Apache Torque Developers List" Content-Type: text/plain; charset="us-ascii" X-Virus-Checked: Checked by ClamAV on apache.org Below is the thread that was part of the 3=2E3 release=0D=0ADiscussions=2E = Now that 3=2E3=2E is released, I'm going to=0D=0Acheck in on Monday unless= anyone has problems with=0D=0Athe way I've started implementing function s= upport=2E=0D=0A=0D=0AThis has all the JavaDoc info, licensing, code style= =0D=0Achecks, and a test project class that tests if it=0D=0Aworks (tests f= ine with MS SQL and MySQL 4=2E0 so far)=2E=0D=0AThe code can now be found a= t:=0D=0A =0D=0Ahttp://people=2Eapache=2Eorg/~gmonroe/torque/TorqueSummaryHe= lper=2Ezip=0D=0A=0D=0A> -----Original Message-----=0D=0A> From: Greg Monroe= [mailto:Greg=2EMonroe@DukeCE=2Ecom]=0D=0A> Sent: Monday, February 11, 2008= 3:20 PM=0D=0A> To: Apache Torque Developers List=0D=0A> Subject: Include n= ew Summary Query Helper and FunctionFactory features=0D=0A> in 3=2E3 or not= ?=0D=0A> =0D=0A> I'm just about done with a summary query "helper" =0D=0A> = class and a SQL FunctionFactory methodology that I =0D=0A> think will fill = a long time "hole" in Torque's =0D=0A> base functionality=2E=0D=0A =0D=0A= =2E=2E=2E=2E=0D=0A =0D=0A> Here's a description of this new capability=2E= =0D=0A> =0D=0A> The biggest "user" change is the addition of a =0D=0A> Summ= aryHelper class that makes it very easy to create =0D=0A> queries that incl= ude "Aggregate" functions=2E E=2Eg=2E =0D=0A> queries like: =0D=0A> =0D=0A= > Select Employee Sum(hours),avg(hours),min(hours),max(hours) =0D=0A> Fro= m time_sheet =0D=0A> Where Type =3D 1 =0D=0A> Group By Employee =0D=0A>= Order By Employee ASC =0D=0A> =0D=0A> Here's how you would do that with = the SummaryHelper class:=0D=0A> =0D=0A> SummaryHelper sHelp =3D new Summ= aryHelper();=0D=0A> Criteria c =3D new Criteria();=0D=0A> c=2Eadd(Tim= eSheetPeer=2ETYPE, 1);=0D=0A> c=2EaddAscendingOrderBy(TimeSheetPeer=2EEM= PLOYEE);=0D=0A> sHelper=2EaddGroupBy(TimeSheetPeer=2EEMPLOYEE);=0D=0A> = sHelper=2EaddAggregate(=0D=0A> FunctionFactory=2ESum(TimeSheetPe= er=2EHOURS),"Hours");=0D=0A> sHelper=2EaddAggregate(=0D=0A> Funct= ionFactory=2EAvg(TimeSheetPeer=2EHOURS),"Avg_Hrs");=0D=0A> sHelper=2Eadd= Aggregate(=0D=0A> FunctionFactory=2EMin(TimeSheetPeer=2EHOURS),"Min_= Hrs");=0D=0A> sHelper=2EaddAggregate(=0D=0A> FunctionFactory=2EMa= x(TimeSheetPeer=2EHOURS),"Max_Hrs");=0D=0A> List results =3D sHelper=2Es= ummarize( c );=0D=0A> =0D=0A> The results list will be an OrderedMap with a= key of either=0D=0A> the group by column name or the name specified for th= e aggregate=0D=0A> function (e=2Eg=2E EMPLOYEE or Hours)=2E The value will= be a Village=0D=0A> Value Class=2E Below is the basic way to do this=2E= =0D=0A> =0D=0A> String emp =3D results=2Eget("EMPLOYEE")=2EasString();= =0D=0A> int hours =3D results=2Eget("Hours")=2EasInt();=0D=0A> =0D=0A> I= n order to support this in a DB server specific manner,=0D=0A> I've created= a new org=2Eapache=2Etorque=2Eutil=2Efunctions=0D=0A> package=2E This has= a generic SQLFunction interface, a=0D=0A> FunctionFactory class, and other= supporting classes=2E=0D=0A> =0D=0A> The FunctionFactory class is responsi= ble for creating=0D=0A> DB Adaptor specific SQLFunction implementations=2E = This is=0D=0A> Done by calling a new DB interface method with the=0D=0A> si= gnature of:=0D=0A> =0D=0A> public Class getFunctionClass( FunctionEnum ty= pe )=0D=0A> =0D=0A> Note that FunctionEnum is like SQLEnum, just a list=0D= =0A> of supported function type=2E This is used as a key=0D=0A> to look up= the SQLFunction implementation class that=0D=0A> supports the (potentially= DB specific) function=2E=0D=0A> =0D=0A> Currently, I have SQL99 standard i= mplementation for=0D=0A> All the "Aggregate" functions, e=2Eg=2E AVG, COUNT= , MAX,=0D=0A> MIN, and SUM=2E The DBAbstract class will be modified=0D=0A>= to use these by default=2E These are pretty standard=0D=0A> across all th= e DB server types Torque supports so=0D=0A> this should be a good starting = point=2E=0D=0A> =0D=0A> However, if there is need for DB specific functions= ,=0D=0A> it is easy for the specific DB adaptor to replace=0D=0A> the SQL 9= 9 standard with its own function=2E=0D=0A> =0D=0A> Also, the SQLFunction in= terface is designed with an=0D=0A> eye towards expanding FunctionFactory's = support into=0D=0A> other SQL standard function categories (like Date=0D=0A= > and String functions)=2E=0D=0A> =0D=0A> Questions? Comments?=0D=0ADukeCE = Privacy Statement:=0D=0APlease be advised that this e-mail and any files tr= ansmitted with=0Ait are confidential communication or may otherwise be priv= ileged or=0Aconfidential and are intended solely for the individual or enti= ty=0Ato whom they are addressed=2E If you are not the intended recipient=0A= you may not rely on the contents of this email or any attachments,=0Aand we= ask that you please not read, copy or retransmit this=0Acommunication, but= reply to the sender and destroy the email, its=0Acontents, and all copies = thereof immediately=2E Any unauthorized=0Adissemination, distribution or co= pying of this communication is=0Astrictly prohibited=2E --------------------------------------------------------------------- To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org For additional commands, e-mail: torque-dev-help@db.apache.org