From: Kirk Haines <khaines@...>
Date: 2004-04-21T08:54:49+09:00
Subject: Re: The quest for opensource database...

On Wed, 21 Apr 2004, Ryan Pavlik wrote:

> There are a couple problems here, and a few solutions to them, as
> well.  I'll shamelessly plug Criteria here.  Make sure you use the SVN
> version:
> 
>   http://svn.mephle.org/svn/mephle/criteria/
> 
> That will help you combine your queries; I do this regularly:
> 
>   class Customer
>       # This is the table, you probably want to use a DBITable though.
>       IDX = SQLTable.new("my_table", ...)
>   
>       CUSTOMER_NAME   = (IDX.name == IDX.x0?)   # .x0? is a placeholder
>       CUSTOMER_BEFORE = (IDX.date <  IDX.x0?)
>       # :
>       # :     more queries
>       # :
>   end
>   
> Later on we can combine them:
> 
>   query = CUSTOMER_NAME["Bob"] & CUSTOMER_BEFORE[1975]

Criteria is pretty slick.  As an exercise in comparison, here's how I'd do 
it using Kansas:

ksdbh.table('Customer','customer') # args are classname, tablename

Then to do the query:

customers = ksdbh.select(:Customer) do |customer|
  (customer.name == 'Bob') && (customer.date < 1975)
end

> You get the idea.  However this is only one of your problems; the
> other is the fact it's very slow to do a query everytime you want an
> attribute, either getting or setting.  For this I would recommend
> "caching" things; make an attr_sql_accessor or the like which (perhaps
> using Criteria... I do this too) makes note of when the value changes,
> but only updates the database on a flush, and/or tie in transactions
> (especially nice with PgSQL's MVCC).  This should keep you from having
> 3 lines like this:
> 
>   customer.name    = "Larry"
>   customer.date    = 1998
>   customer.address = "..."
> 
> ...generate 3 UPDATE statements, but only one instead.  The only thing
> you need to watch out for is making sure someone doesn't update the
> database behind you; for this I'm not sure what you can do.  (Maybe
> some magic with stored ruby procedures and triggers... dunno.)

This is trickier.  The direction that I am taking with Kansas is to have a 
level of transaction support.

ksdbh.transaction do
  customer.name = 'Larry'
  customer.date = 1998
  customer.address = '123 Trelona Circle'
  # blah blah some other timeconsuming stuff
  ksdbh.commit
end

At the moment this is only partialy implemented, but what should be 
happening is that Kansas takes care of initiating a transaction at the 
database level to keep things from changing underneath us at that level.  
It applies the attribute changes to the object, but wait until the commit 
to roll them up and apply them all at once down at the database level 
before issuing an echoing commit() on the database.  For DBI backends that 
don't support transactions, one will still have the transactional support, 
including the ability to rollback changes to the objects, at the Ruby 
level, and one would still have the rolling up of multiple updates into a 
single update.

This doesn't address the problems of the database data changing underneath 
the object and the object knowing nothing of it.  The only alternatives 
that I see are doing something with triggers or making each object so that 
it can refresh itself to make sure it is in sync with the db.

customer.refresh

or something like that.  And if there is a high likelyhood of the db 
changing under and object, it might be worth the performance cost to have 
something like:

customer.autorefresh

which would tell the object to check itself against the db before any 
attribute is accessed.  That could be a huge performance hit, though, and 
could itself introduce problems.  Tricky.


Kirk Haines