Re: SQL Function support (TRQS317)

[Thomas Fischer <tf...@apache.org>]

Hi,

sorry it took me so long replying to this, but it needed a certain amount 
of thought. There might be some inconsistencies in what I have added, but 
I wanted this out now.

Personally, I do not see this in the 3.2 release, it touches too many 
internals.

More comments inserted below.

    Thomas

On Fri, 26 Aug 2005, Greg Monroe wrote:

> 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()
>

I do not think that anything used in the TableMap would be a good start. 
The reason is that the TableMap cares about structure (e.g. "column X 
contains a String and it is a foreign key to another Table and it has a 
size of 255"), but this is not what we need here. We need the Column 
object for SQL generation. Up to now, we have used strings there, like 
e.g. TableName.ColumnName. (or even SchemaName.TableName.ColumnName) so as 
first guess, the column interface will contain at least getters for 
schemaName, tableName, and columnName.

For the getters, a real column would have at least return a valid 
tableName and a valid columnName. To see what a "composed" column might 
return, we need to consider what is happening with the column. It is used 
for generation of the select clause and of the from clause of a sql 
statement.

In my opinion, neither clause should be generated from the column object 
itself, but rather from a SQL builder (as it is done now for similar 
structures). This is the classical MVC pattern: Separate (SQL) view from 
(Column) model, with all its advantages.

For building the select clause, the column would need to tell the SQL 
generation classes what kind of column it is. This can be decided by the 
class of the implementing object or by some function like getType() 
Probably there will be different implementations for normal columns, 
function columns etc, so the sql generator would have to use switches by 
instanceof anyway, so there is no need for the getType() in the interface.

For building the from clause, we need to know which tables are used in the 
column. For normal columns, this is easy. For functions, there might be 
several "normal" columns involved. So the function column will probably 
keep a List of column "arguments". For building the from clause, all the 
contained columns should be returned by the column object, so an 
additional method in the interface is needed:
public List getContainedColumns();
which returns a list of columns and is called in building the from clause 
if getTableName() returns null.

Perhaps the getColumnName() should be renamed toString(). As explained 
above, in my opinion it sould only return valid SQL for simple columns,
and a string with debugging info for function columns. 
>
> 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.
>

The first way of using the column object obviously stuffs the criteria 
object with more methods.
The second way has the disadvantage that people will tend to use the 
more powerful object anyway. What use is there for the less powerful 
object ?
For the third way, this would lead to a totally different way of building
criteria. You would not use criteria.add(someColumn, someValue) and 
criteria.addOrderBy(someColumn) but 
criteria.add(new Condition(someColumn,someValue)) or criteria.add(new 
OrderBy(someColumn)), if I understood you correctly. It is certainly an 
interesting idea. Perhaps it could be used as an alternative to criteria,
but it is a lot of work and I am not sure if it has other advantages 
besides from the (new) Criteria object being less stuffed.

Still another idea : Why should columns represented by strings at all ? 
Constants like SomePeer.SOME_COLUMN could also be column objects instead 
of Strings. Then, one could deprecate all the methods in criteria using 
strings as column namens, and replace them with methods using the column 
interface. This will be source compatible for people using columns the 
usual way, like criteria.add(SomePeer.SOME_COLUMN, someValue). To retain 
the flexibility by using strings, one could add a method which parses 
Strings into column objects.

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

Hm, having a separate class for each function will add a lot of
if (xxx instanceof yyy)
in the sql creation. But perhaps one can have an underlying base class 
which has a function name, and a List of arguments.

> 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).
>

If you do not generate SQL in the functions themselves, (see above, 
separate model from view), you would not need this. The criteria knows 
which DB should be used, no need for the function to know it.

> 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() };
> 	}
> }
>

Adding the functions to the db adapter is a good idea. If one separates 
sql generation from the column model, the above will not work. But why not 
add a method like
public String getFunction(SQLEnum functionName, List functionArguments)
which has a default implementation and can be overridden in each Adapter.

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

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org