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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
