From: Brian Candler <B.Candler@...>
Date: 2003-07-03T19:09:49+09:00
Subject: Re: A Quick Guide to SQLite and Ruby

On Thu, Jul 03, 2003 at 08:55:45AM +0900, why the lucky stiff wrote:
> I've been truly impressed by SQLite, a small database with support for much of 
> SQL-92.  This is a swift, ACIDic, customizable database that could be useful 
> to a great many Ruby developers.

Biggest problem I have with ruby-dbi and Sqlite: if you set db['AutoCommit']
= false, which is actually the default, then ruby-dbi keeps a transaction
open at all times. That is, it issues "begin transaction" as soon as the
database is opened, and then after a "commit" it issues another "begin
transaction" immediately.

SQLite locks the entire database when a transaction is in progress, and
therefore the database can only ever be seen by a single connection /
thread / process.

$ cat x.rb
#!/usr/local/bin/ruby
require 'dbi'

a = DBI.connect('dbi:Sqlite:foobar')
b = DBI.connect('dbi:Sqlite:foobar')

$ ruby x.rb
/usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:567:in `Connect': database is locked(database is locked) (DBI::DatabaseError)
        from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:567:in `Connect'
        from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:367:in `Connect'
        from x.rb:5

Unfortunately it seems that ruby-dbi's transaction support is broken (it has
a concept of 'commit' but not of 'begin') so it's hard to fix this without
major surgery to ruby-dbi itself.

Regards,

Brian.