From: Tim Bates <tim@...>
Date: 2004-01-09T08:02:59+09:00
Subject: Re: Database applications and OOness

On Fri, Jan 09, 2004 at 12:46:44AM +0900, Francis Hwang wrote:
> 1. Regarding subtle differences in query syntax: If I had a
> comprehensive list of things to look out for I could write tests
> against them. I imagine most of them would be easy to fix.

Okay. I don't have a comprehensive list, I'd imagine most of them would
be found through testing. The ones I know of off the top of my head are:

MySQL's "RLIKE" and "NOT RLIKE" correspond to PostgreSQL's ~ and !~
operators.

PostgreSQL doesn't like "OFFSET x,y" syntax, it insists upon
"OFFSET x LIMIT y".

I don't know what the SQL standard says in either of these cases.

> 2. Regarding transactions: I haven't done this, but I don't imagine it
> would be difficult. A Ruby-like style for it might be something like:
> 
> objectStore.runTransaction {
>   account1743.balance -= 100
>   account1743.commit
>   account329.balance += 100
>   account329.commit
> }

That's exactly how DBI and Vapor deal with it. That would be ideal.

> .... and then I'd just write an ObjectStore#runTransaction method like:
> 
> def runTransaction
>   beginTransaction
>   yield
>   commitTransaction
> end

def runTransaction
  @dbhandle.transaction do |dbh|
    yield
  end
end

Assuming that your DBI::DatabaseHandle is called @dbhandle.

> right? One question: When using DBI/Postgres, do you issue begin and
> commit commands as separate lines, or do they need to be mixed in with
> your SQL strings?

You don't need to worry about that, DBI handles it for you. ;) But just
FYI, there's a "BEGIN;" SQL statement, a "COMMIT;" SQL statement and a
"ROLLBACK;" SQL statement.

> The more I think about it, the more this sort of interface makes me
> nervous. Maybe Criteria users can pipe in here with their perspective,
> but it seems to me that once you tell the programmer that she can
> write code like
> 
> (tbl.bday == "2003/10/04").update(:age => tbl.age + 1)
> 
> then it's natural for her to assume she can write the opposite:
> 
> (tbl.bday != "2003/10/04").update(:age => tbl.age + 1)
> 
> .... but the second case will fail, and probably it will fail quietly
> since (I think) there's no way for a Table object to detect negation
> like that.

It does fail, and it doesn't do it quietly either; watch this:

irb(main):001:0> require 'criteria/sql'
=> true
irb(main):002:0> include Criteria
=> Object
irb(main):003:0> tbl = SQLTable.new('people')
=> #<Criteria::SQLTable:0x400ea780 @criterion_type=Criteria::SQLCriterion,
@_typemap=nil, @table="people">
irb(main):004:0> (tbl.bday == "2003/10/04")
=> (== :bday "2003/10/04")
irb(main):005:0> (tbl.bday == "2003/10/04").update(:age => tbl.age + 1)
=> "UPDATE people SET age = (people.age + 1) WHERE (people.bday = '2003/10/04')"
irb(main):006:0> (tbl.bday != "2003/10/04")
=> false
irb(main):007:0> (tbl.bday != "2003/10/04").update(:age => tbl.age + 1)
NoMethodError: undefined method `update' for false:FalseClass
        from (irb):7
        from :0

So basically it just gives up. :) This is because Ruby translates it to
  !(tbl.bday == "2003/10/04")
and (tbl.bday == "2003/10/04") is true, therefore the expression
evaluates to false. There is really no way around this other than
altering the query syntax, as you suggest next.

> 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.

> One more point of disclosure in the interest of not wasting your time,
> Tim: Lafcadio does almost no handling of group functions yet. I
> suspect it wouldn't be too difficult to add on, but I haven't thought
> about the problem much yet so I can't really be certain of it.

Again, the trick would be to make it Ruby, rather than just tacking SQL
group functions onto it like Criteria does by accepting literal SQL
strings at the moment.

I very much agree with your earlier comment that those libraries which
continue to exist in this problem domain are going to have to deal with
all these issues sooner or later. I think Lafcadio and Vapor have many
of the answers to one aspect of the problem, and Criteria has answers to
another; a marriage or cross-pollination of the two would make a lot of
sense.

Tim Bates
-- 
tim@bates.id.au