From: Brian Candler <B.Candler@...>
Date: 2003-05-30T05:58:39+09:00
Subject: Re: Metakit for Ruby - Would you want it?

On Fri, May 30, 2003 at 04:34:18AM +0900, Hal E. Fulton wrote:
> > You mean like sqlite? http://www.sqlite.com/
> >
> > There are ruby bindings which work more or less (get the latest
> ruby-dbi-all
> > from CVS)
> 
> Hmm. Can it do record-level locking? E.g., suppose multiple
> threads or processes are accessing the database?

You can run it safely with multiple threads/processes, but it locks the
whole file (i.e. the whole database) when it needs to.

This leads to a nasty behaviour with DBI and AutoCommit. If you have
AutoCommit set to false (which is the default), then the sqlite DBD handles
this by immediately issuing a "begin transaction" as soon as you open the
database. When you do a commit it issues "commit" followed immediately by
another "begin transaction". This has the very nasty side-effect of keeping
the whole database locked, so it can only be accessed from a single thread.

You can turn AutoCommit on, but many databases don't handle transactions
properly in that case, because there is no 'begin' method in the DBD
interface - only 'commit' and 'rollback'. In fact, ruby-dbi is (in my
opinion) seriously broken when it comes to transactions. It's a shame,
because this is one of the absolutely key pieces of infrastructure for many
applications.

Ruby-dbi is just a wrapper around the 'native' APIs for each of the
databases of course, so there's nothing to stop you using say the mysql or
postgresql APIs directly, if you don't mind hard-wiring your code to that
API. But sqlite has only a DBD as far as I know.

> I've toyed with the idea of "RQL" (Ruby Query Language) which would be
> similar in spirit to SQL but allow things like "MATCHES regex" and
> so on.

Yeah, but how would you implement it as a layer on top of an SQL database?
This is just one of the problems I came across:

- In Oracle, "like" is case-sensitive (only)
- In Sqlite, "like" is case-insensitive (only)
- In Mysql, you have the choice of "like" or "ilike" operators

It's pretty much impossible to layer consistent semantics on top of all
three databases. I have had to approximate. In the case of Oracle, I have to
issue queries of the form

     "foo like ? or foo like ? or foo like ? or foo like ?",
         var, var.downcase, var.upcase, var.capitalize

It's awful. Even worse is date and time handling.

Cheers,

Brian.