db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fox <Thomas....@seitenbau.net>
Subject Criterions which do not reference a column
Date Tue, 03 Apr 2012 03:29:14 GMT

In TORQUE-185 (https://issues.apache.org/jira/browse/TORQUE-185) I'd like
to implement criterions which do not reference a column. A common case for
such queries is a Criterion with a subselect on one side and a plain value
on the other side, e.g.
select * from TABLE_1 where 0=(select count(*) from TABLE2 where
TABLE2.COLUMN1 = TABLE1.COLUMN1 AND TABLE2.COLUMN2=?);

On sql rendering side, one would like to represent the plain value as
prepared statement replacement (because it can also be a String which
otherwise needs to be escaped), i. e.
select * from TABLE_1 where ?=(select count(*) from TABLE2 where
TABLE2.COLUMN1 = TABLE1.COLUMN1 AND TABLE2.COLUMN2=?);
preparedStatementReplacements = [0]

We can already do the subselect on the right hand side of the criterion.
What we cannot do currently is the plain value on the LHS rendered as
prepared statement replacement (it is also not possible using the
Criteria.Custom comparison).

I have the following ideas to implement this:

1)  Change the criteria.Criteria interface from
  and(String column, Object value)
  and(Column column, Object value)
  and(String column, Object value, SqlEnum comparison)
  and(Column column, Object value, SqlEnum comparison)
to
  and (Object lhs, Object rhs)
  and (Object lhs, Object rhs, SqlEnum comparison)
(same for or, where, andIn, ..., the util.Criteria interface would stay as
is). The objects would be interpreted as column if they implement the
Column interface, as subselects if they implement the Criteria
interface, ... (there are some more special cases which are already built
in), and as plain objects otherwise.
So for the above query, one would write
  criteria.and(0, subselectCriteria);

2)  Leave the Criteria interface as is. Implement an Object
ValuePseudocolumn which implements the Column interface and stores an
Object internally, and which is rendered as prepared statement replacement
in SQL.
For the above query, one would write
  criteria.and(new ValuePseudocolumn(0), subselectCriteria);

3) as 2), but let not implement the ValuePseudocolumn object implement the
column interface (and consequently use another name, e.g. ValueHolder).
This changes the criteria.Criteria interface to
  and(String column, Object value)
  and(Column column, Object value)
  and(ValueHolder lhs, Object rhs)
  and(String column, Object value, SqlEnum comparison)
  and(Column column, Object value, SqlEnum comparison)
  and(ValueHolder lhs, Object rhs, SqlEnum comparison)
For the above query, one would write
  criteria.and(new ValueHolder(0), subselectCriteria);

1) has the advantage that the Criteria interface gets more simple and that
it is an intuitive solution (at least in my eyes). It has the disadvantage
that the call ("string1", someOtherObject) has changed its semantics from
   ...WHERE someTable.string1=? , preparedStatementReplacements=
[someOtherObject]
to
   ...WHERE ?=? , preparedStatementReplacements=["string1",
someOtherObject]
A call where the generated peer class column constants are used does not
change its semantics.

2) has the advantage that the semantics of the Criteria calls stays the
same. It has the disadvantage that the wrapper around the object is not
intuitive (why should a value be disguised as a column),  (why is the
wrapper needed in the first argument but not on the second argument of
criteria.and(..., ...)).

3) has the advantage that it does not have the disadvantages of 2), but has
the disadvantage that it further bloats the criteria interface.

My personal favourite is 1). We already have the semantics change in the
criteria.or() methods, which was mediated by having an old-style and a
new-style criteria object in the util and criteria packages, so another
semantics change would not hurt much IMO. Also using strings as table names
is a bad practice anyway :-), and if one still wants to do it one cane use
new ColumnImpl("table1") instead for the same effect.

What do you think ?

    Thomas

P.S. to care for other such cases which are not covered by the above, I
intend to add the methods
  andCustom(String query, Object[] preparedStatementReplacements)
and
  orCustom(String query, Object[] preparedStatementReplacements)
so one can also create custom criterions which use prepared statement
replacement. Any objections on this ?


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


Mime
View raw message