[prev in list] [next in list] [prev in thread] [next in thread] 

List:       turbine-torque-dev
Subject:    SQL Function support (TRQS317)
From:       "Greg Monroe" <Greg.Monroe () DukeCE ! com>
Date:       2005-08-26 19:24:27
Message-ID: 0EBA2D5D58A98C4AAFBF2ACB6E625A260C18E4 () dukece-mail2 ! dukece ! com
[Download RAW message or body]


Thomas and I have been having some discussions relating to quick and
dirty 
enhancement patch I submitted to add DB independent concatenation to
Torque ( 
http://issues.apache.org/scarab/issues/id/TRQS317 ).  Rather than clog
up Scarab 
with multiple comments, it seems like it's time to move this to a
Torque-dev thread.

IMHO, The main infrastructure issues that seems to be coming out of this
are:

- How to support general DB independent common functions, e.g. concat
and the 
like.

- How to integerate functions into the Criteria model.

- How to reconcile current confusion of columns as strings and
functions.

Some of the design points to concider that have been made are:

- DB specific information should be in the DB adapter class(es).

- KISS (Keep it simple stupid) because that's one of the beauties of
Torque

- Downward compatiblity, e.g. thou shalt not remove methods (but
deprecation may 
be OK).

- Minimize the number of methods added to classes that already have too
many.

That said, let me attempt to layout my current thoughts on how to
achieve this...

First, how about creating a Column interface.  This would be a
definition of the 
common methods that the Criteria model needs in order to eventually
create the 
SQL statement string.  This would be based on a few of the ColumnMap
class 
methods (so ColumnMap already impliments it) and maybe one or two others
(like 
equals/hashCode maybe?).  Having an interface would allow for function
classes 
that implement the Column interface to be used in Criteria methods just
like 
columns.

A quick first pass at what methods the column interface would have is:

/**
 * Real Columns would return table.column 
 * Functions would return appropriate string like 
 *            users.first_name || ' ' || users.last_name 
 */ 
getFullyQualifiedName() 

/**
 * Real columns = column name
 * Functions = fullyQualified name 
 */
 getColumnName() 

/**
 * Real columns would return at least Object(); 
 * Functions would return a subclass of something like SQLFunction so
typeof can be 
 *    used to determine actions. 
 */ 
 getType()

/**
 * Real columns = table name 
 * Functions = null?... Maybe functions need to have a "main" table
specified when created? 
 */ 
 getTableName()


How would the ColumnInterface be used:

With a Column interface, you could add a small set of methods to the
Criteria 
function like:

add( ColumnInterface column, Object comparitor, SqlEnum enum );

Or alternatively, have a ColumnCriteria (or some such name) class that
extended 
Criteria with a more "complete" set of Column based methods. (The Java
doc will 
be cleaner this way,  but IDE code helpers, e g. Eclipse, will still
show lots 
of methods...)

V3.3 idea... create an Interface and/or base Criteria object with the
methods 
needed for SQL statement creation, make this the type for Peer calls.
Then you 
could have more Criteria (Criterion?) objects with fewer methods, e.g. 
JoinCriteria.add...  or OrderCriteria.. that get combined to make the
end 
statement criteria.


How do you get a real column object

ColumnInterface c = (ColumnInterface)
TablePeer.getTableMap().getColumn(name);

works now. but adding a ColumnInterface getColumn(name) function to
BasePeer 
would be nice.


The SQL Functions layout

Taking the previous conversations into mind, how about this:

For each function that Torque supports there would be class that
implement the 
column interface.  These would supply function specific methods for
general use. 
For example, with concat, there could methods like concate(
ColumnInterface, String)
or concate(String, String).  Specifying ColumnInterface and String as
needed would 
allow for the appropriate handling of parameters that need quoting, etc.
This also
makes the functions more "user friendly" because the functions method
format is 
more like the underlying SQL being created.

These function classes will have to be constructed with information that
allows it 
to find the DB adapter being used.  E.g., new SQLConcat(Criteria c) or 
new SQLConcat(DB adapter) and probably need to be built on a base
abstract class (so
getType() returns a constant typeof).

The responsiblity of these function classes is simply to process the
various method
arguments and convert them to a common SQLFunction Interface format.
The implimented
ColumnInterface methods like getQualifiedName, would get a DB Specific
SQLFunction 
implimentation from an Adapter factory method.

Here's some psuedo code to illustrate this:

class SQLConcat impliments ColumnInterface {

	SQLConcat( Criteria c ) {
	   setDBAdapter( Torque.getDB(c.getDbName()));
	}
	
	void concat( column, string ) {
	  clearArgs();
	  addArg(column.getQualifiedName());
	  addArg(SQLExpression.quoteAndEscapeText(string),getDB());
	}
	
	String getQualifiedName() {
		SQLFunction func = getDB().makeFunction(DB.CONCAT);
		return func.getSQLString(getArgs());
	}
}

I'd suggest that the DB base class could have the SQL standard functions
implimented 
as inner classes so that only adapters that need different
implimentations would have
to override them.  Here's some psuedo code:

class DB {

	static String CONCAT = "concat";

	Class Concat impliments SQLFunction {
		getSQLString( String[] args ) {
			StringBuf result = new StringBuf();
			result.append(arg1);
			for other args {
result.append("||").append(arg#) }
			return result.toString();
		}
	}
	
	makeFunction(String type ) throws IllegalArgumentException {
		if ( type.equals(CONCAT) ) { return new DB.Concat() };
	}
}


Some Psuedo End User code

    Criteria c = new Criteria();
    ColumnInterface last = UsersPeer.getColumn(UsersPeer.LASTNAME);
    ColumnInterface first = UsersPeer.getColumn(UsersPeer.FIRSTNAME);
    ColumnInterface ou = UsersPeer.getColumn(UsersPeer.OU); 
    SQLConcat fullname = new SQLConcat(c);
    fullname.concat(first," ",last);  // Maybe should use StringBuffer
format?    
    c.add(fullname,"%ney Rub%", SqlEnum.LIKE);
    c.add(ou,"o=Slate.com".SqlEnum.EQUALS);

Thus endth the design acording to me....Thoughts?..confusion?...

Greg 


Duke CE 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.



[prev in list] [next in list] [prev in thread] [next in thread]