Query syntax in O-R mapping (was: Database applications and OOness)

Tim Bates <[e-mail]> wrote in message news:<[e-mail]>...
< much Lafcadio-specific discussion snipped >

Tim, everything we're talking about here makes sense to me. And the
only reason it's not in Lafcadio now is only because I haven't had the
time to do it, not 'cause I object on principle to including
transactions or grouping functions or whatever. So if you feel like
helping to add this to Lafcadio I'd love to help you help the code.
(Or, you could choose to roll your own, which I wouldn't at all take
personally given the idiosyncratic nature of Lafcadio at parts.) I'd
promise you something more substantive than that, but, again, there's
that chronic busy-ness to tend to ... Maybe we can talk more specifics
over email if you want.

The discussion below, though, I think would benefit from having as
many voices as possible. At RubyConf, I framed the problem like this:
We may bitch about SQL, but it's actually an extremely robust,
powerful, and concise syntax for dealing with RDBMS ... if we want to
mimic it all in Ruby, what is the best way to do it?

> > I'm starting to think I'd want a query syntax that trades
> > some cleverness for clarity, something like maybe
> > 
> > (tbl.bday.equals( "2003/10/04" ) ).update(:age => tbl.age + 1)
> > (tbl.bday.equals( "2003/10/04" ).not! ).update(:age => tbl.age + 1)
> > 
> > Or maybe that's trading one sort of ugliness for another. I'm not sure
> > yet.
> 
> It'd need some more thought. Once you start doing this sort of thing, I
> start wondering why I shouldn't just write raw SQL, because this is
> looking a lot like SQL crammed into Ruby syntax, rather than an abstract
> Ruby query-specification syntax which happens to translate to SQL rather
> nicely, which is what Criteria was in the first place. I'm of the
> opinion that if I'm going to write something that close to SQL, I may as
> well write SQL itself. If I'm going to write Ruby, I want to think in
> Ruby, not SQL. I want to write it without stopping to think how this
> translates to SQL and whether that's really what I want.

Only Criteria query inference isn't really Ruby. You can see the
relation to Ruby, but it's barely the same thing. It's like the
difference between English and Creole. Let's take "&", which you use
in Criteria because you can't override "&&". Only it has a different
operator precedence and

t.foo > t.bar & t.baz < t.quux

will parse differently from what you might expect so you have to
remember to use

(t.foo > t.bar) & (t.baz < t.quux)

instead. And it's significant that in every other context, nobody
types "&" when they want "boolean AND". They type "&&" instead,
perhaps because "&&" always means "boolean AND" and "&" can mean set
intersection (for Arrays) and bitwise AND (for Fixnum).

I don't mean to beat up on Criteria. It seems to me that given the
sort of design Ryan's pursuing, and the way Ruby introspection works,
he's done as well as anybody could do. Criteria seems like it's very
well-done, and it's obviously useful since there are a lot of people
using it. Since I haven't had the chance to use it for anything
serious, I guess I don't have a clear sense of what it's like to use
Criteria. So I hope everybody reads what I'm posting not as a
criticism of Criteria but an attempt to understand how it works in
your head when you use it.

Here's my concern: Seems to me than when you have a syntax that looks
_sort of_ like normal Ruby, but is different in all these tiny subtle
ways, using it might involve a lot of "mental housekeeping" about why
things work and don't work. In particular, the idea of teaching a
moderately skilled programmer how to use Criteria is pretty daunting.

Is this not a problem for Criteria users? Am I just worrying too much?

Francis