From: sera@... (Francis Hwang)
Date: 2004-01-09T00:46:44+09:00
Subject: Re: Database applications and OOness

Tim Bates <tim@bates.id.au> wrote in message news:<20040107232653.GA32403@bates.id.au>...
> > In the short-term, I can tell you what you'd have to do for Lafcadio
> > to get it do what you want. First, Lafcadio was written first for
> > MySQL, and although it should support Postgres with minimal changes, I
> > have yet to implement or test such a change.
> 
> Vapor, to give an example, is very targeted at Postgres and uses a lot of its
> more advanced features (in particular it integrates Postgres's
> transaction model quite closely into its behaviour). Criteria, to give
> another example, is running into problems because of subtly different
> query syntaxes between different databases (MySQL's "RLIKE" vs
> Postgres's "~", for example). Does Lafcadio suffer from either of these
> constraints? In particular I'd like to have a fair bit of control over
> when and where transactions are used - it's particularly critical in
> cases like
> 
> "BEGIN;
>    UPDATE accounts SET balance = balance - 100 WHERE accnum = 1743;
>    UPDATE accounts SET balance = balance + 100 WHERE accnum = 329;
>  COMMIT;"
> 
> If I were to do a similar thing in Ruby code, I'd need to be sure that
> the account balance that I was using in the calculation was up-to-date
> (otherwise the effects of someone else's transaction might get wiped)
> and also that if the second UPDATE in the example above failed for some
> reason then the first one would get rolled back. Can Lafcadio do, or be
> made to do this?

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.

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
}

.... and then I'd just write an ObjectStore#runTransaction method like:

def runTransaction
  beginTransaction
  yield
  commitTransaction
end

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?

> > Second, Lafcadio's support for querying works, but I'll be the first
> > to admit that it's verbose and ugly as hell. Query inference is very
> > high on my list, but then, I'm busy, too.  So Vapor or Criteria might
> > be a better choice for you, depending.
> 
> I wouldn't consider Criteria to be in quite the same basket as Lafcadio
> and Vapor. It doesn't have any OO abstraction at all (on the data side -
> the project is all about OO abstraction of queries) I think Lafcadio (or
> Vapor) could take a lot of ideas (and/or code) from Criteria for their
> own query interfaces. I had independently thought of some of the
> concepts behind Criteria when I discovered that somebody had already
> written it, and in a much cleverer way than I would have thought of, but
> it's not primarily a database interface library, it's a query
> construction library. A project that combined the best aspects of the OO
> data interfaces and the OO query interfaces that we already have would
> be exactly what I was looking for.

There's an extremely alpha version of query inference in the last dev
release, 0.3.4. I wouldn't suggest trying to use it, though, 'cause
the interface is likely to change. I tried doing something similar to
Ryan did with Criteria, but I found that as soon I started seeing
pseudo-Ruby syntactical symbols like &, |, <, >, etc., I instinctively
wanted the entire range of Ruby primitives to play with. Hence my
wierd question here earlier about whether it's possible to override
unary negation.

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

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.

Francis