Hi!
I am replying to this very old message cause I was not on this issue
lately...
I believe I understand where you are going and I think you are missing the
point.
This route you are completely loosing one piece of Criteria functionality
which is critical. This piece is holding query's *structure*.
You code does the job of creating the SQL. This is good, but it is not
really required. I can write SQL myself just as good or better. It's good to
abstract it and stuff, but not essential. What is really important about
Criteria is that it knows the structure of the query. Without it Peers can
not function. If instead of passing Criteria to the Peer you would pass a
sql string how would it know the list of selected columns and some other
info it may need?
I also do not like extensive string concatenation, I would much prefer use
of StringStackBuffer...
see more comments below...
Eric Dobbs wrote quite a while back:
> 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.
I agree with Daniel that db specifics should be kept in db adapters.
>
> 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 am not sure where we are asking to create a parse tree... could you
explain?
>
> 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.
could you tell me what "flyweight" exactly is? (excuse my luck of education
& knowledge of English)
My objects are completely stateless, more than that - they are immutable. I
believe it is a big benefit and allows, for example, create queries once and
put them into static vars for reuse whereas with current Criteria you have
to recreate them for each query... You can also share same expression in two
different queries...
>
> 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
>
always late,
Fedor.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
