From: Alex Fenton <alex@...>
Date: 2005-10-24T18:42:02+09:00
Subject: Re: Madeleine, SQLite and multi-platform issues (in ruby :-)

Assaph Mehr wrote:

 > Would the right
 > approach be to simply upon start-up read the affected tables, drop the
 > old one, recreate the new ones and then write the (massaged) data
 > back?

Yes, or it's probably safer to start a transaction, rename the old table 
to a
temporary name

ALTER TABLE foo RENAME TO foo_temp;

then create the updated table definition and copy into it.

CREATE TABLE foo (...)
INSERT INTO foo SELECT * FROM foo_temp;

> I understand that Rails' ActiveRecord does something similar for its
> migrations. Have you had occasion to use it (with and without
> migrations) over SQLite?

The Migration API in AR does look useful, but I haven't had cause to try
it (yet).

>>Code will break if you
>>SELECT columns that don't exist or if you assume that you're getting a
>>string when you're fetching a NULL cell.
> 
> 
> I guess that can be managed with a 'version' fields plus a set of
> migrations for db upgrades, right?

Yep. That's just how I do it (though my data model is fairly stable).

http://rubyforge.org/cgi-bin/viewcvs.cgi/weft-qda/lib/weft/backend/sqlite/upgradeable.rb?rev=1.4&cvsroot=weft-qda&content-type=text/vnd.viewcvs-markup


> As for programming defensively - I think I'd rather program paranoidally :-)
> One of the problems I experienced with madeleine is indeed in changes
> between revisions of the software. That's why I'm trying to find out
> as much as I can before committing to a backend change that'll prove
> inadequate.

Sounds good. SQLite's maturity has been a plus.

hth
a