Hi All.
I think I'm finally getting somewhere interesting.
I have looked at this problem from various angles. I'll include
a little discussion about that at the end of this note for those
who are especially interested. For those who aren't, here's the
story about the solution I'm now proposing.
I've attached a starting point to this message if you want to
look at code.
First, the exiting Criteria and discussion up to now constructs
an abstract tree representation of a SQL statement and then walks
the tree later to generate a SQL string. The code I offer now
generates SQL fragments directly and returns them as Strings.
That simplification has made the code and the concepts behind
them drastically more clear. The code attached (Sql.java) is a
starting point. It's a flyweight with methods like the
following:
public String getAnd(Object left, Object right)
public String getEqual(Object left, Object right)
public String getGreaterThan(Object left, Object right)
public String getGreaterEqual(Object left, Object right)
public String getLessThan(Object left, Object right)
public String getLessEqual(Object left, Object right)
and also methods like these:
public String getSelect(List list)
public String getFrom(List list)
and:
public String getWhere(Object foo)
All of the above are essentially convenience methods that wrap
three or four workhorse methods. So the implementation is
shared. And additional methods like these are fairly easy to
add.
Here's an example of how you would use the api:
Sql s = new Sql();
String sql = s.getWhere(
s.getOr(
s.getAnd(
s.getGreaterEqual("columnA","25"),
s.getLessEqual("columnA","50")
),
s.getAnd(
s.getGreaterEqual("columnB","10"),
s.getLessEqual("columnB","20")
)
)
);
Which would produce this SQL: (line-wrapped for readability)
" WHERE ( ((columnA>=25) AND (columnA<=50))"
+ " OR ((columnB>=10) AND (columnB<=20)))";
See the attached SqlTest.java for other examples.
Second, I propose that in place of the present database adaptors,
we extend the attached Sql.java with database specific subclasses
that override methods where their syntaxes differ. So the new
database adaptors would inherit all of the methods that are
common, and would only have to implement the differences.
Also, if you look at the code, I think you will agree that it
would be fairly easy for someone to modify the adaptor for their
database (or subclass it, as the case may be) and then add
methods for their stored procedures, or unique functionalities
(like Oracle's "connect by" clause).
Third, I propose that we create a factory for these Sql objects.
SqlFactory.getInstance() would return the default database
adaptor as configured in TR.props. For a non-default adaptor,
you would specify the one you want like this:
SqlFactory.getInstance(ORACLE). Moreover, we could use the
FactoryService to get the SqlFactory in the first place.
Configuration options in TR.props would define the map between
ORACLE and o.a.t.util.db.statement.OracleSql, etc.
Forth, about backward compatibility... I think we should develop
this separately from the existing Criteria, SqlExpression, etc.
Once we have a functional base to work from, and some adaptors
for specific databases, we start to change the implementation of
the existing code to utilize these methods. In parallel with
that I would like to overload methods in BasePeer that would
accept sql fragments in place of the Criteria object where
appropriate, eventually deprecating the use of Criteria.
Issues:
If an installation is using more than one database, then the
application code would have to know when to use which database
adaptor. Less than ideal. I haven't given a lot of thought to
this problem, but one idea is to try to handle the mapping in the
factory -- rather than asking the factory specifically for
ORACLE, you might pass the factory a token which tells the
factory something about the context in which the SQL will be
used, and let it chose the appropriate adaptor based on the
context. I'm wide open to suggestions.
I'm working with Strings, a lot, which could present a
performance problem. I've tried to follow the example in
StringStackBuffer for pre-sizing StringBuffers in the worker
methods, but it's hard to get around the fact that Object has a
toString and does not have a toStringBuffer.
I'm not exactly happy with the names of the workhorse methods in
this code. Any suggestions?
Should this project be in commons?
Obviously, it's still not complete. 8^)
Other options considered (this is the special interest part 8^):
After the first round of discussion with Fedor, I tried looking
at the Object Query Language (OQL) in Castor, 'cos I thought we
might be reinventing a wheel. (http://www.castor.org/oql.html)
Along with that I also tried to find more info about parse trees
and the Interpreter pattern. Our original discussion sounds a
lot like we're asking developers to create a parse tree by hand.
All of that seemed too complex for the problem at hand.
I looked at Velocity and especially at the scripts that generate
the SQL statements for Turbine. That just felt like the wrong
way to go. I don't have a good reason, just a gut feeling. But
my intuition is usually pretty good, so I looked elsewhere.
I tried to apply a flyweight pattern to Fedor's model of breaking
the SQL down to very granular objects. Sounded good at first:
convenience of objects without the overhead of instantiating many
objects for every SQL statement. As I tried to externalize the
state from those objects, I found myself writing into those
objects exactly the methods I proposed in my last reply to Fedor,
but inside flyweight objects. That seemed like a nice
compromise, but basically just added overhead. I had to make an
extra method call to get the flyweight object, and then call the
method inside the flyweight.
So I rewrote it into it's present form. And then I realized that
this code and the database adaptors could be treated as
flyweights, 'cos they're not really storing any state. That
brought in the idea of using a factory and the factory service.
I mention all of this mostly to enrich discussion on the
proposal. I'm still open to other ideas, but I think the
solution I'm presenting is better than the other options I've
considered.
I look forward to your comments.
-Eric
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
