[Buildbot-devel] Database Upgrade thoughts

[Bill Deegan]

Dustin,

On Mon, Feb 22, 2010 at 10:05 PM, Dustin J. Mitchell <dustin at zmanda.com> wrote:
> I've been thinking about how to handle schema upgrades.  I'd like to
> get this solved early because:
>
> a) people will likely run buildbot between releases, so any schema
> change will need to be committed with the necessary upgrade suppport
>
> b) We may already have some potential schema changes (for
> multi-project/multi-repo support - Dan Locks and Benoit Allard)
>
> c) If we design it right, the upgrade procedures can be tested
> automatically by starting with a version-0 database (the old on-disk
> pickles) and migrating it up to the current version and then verifying
> that the appropriate information is still present, and that the schema
> is correct.  I'd like to get this designed right before we have
> several versions on hand.
>
> d) rather than writing a separate upgrade-to-scheduler-db system and
> upgrade-db-schema-version system, they should be one and the same.
>
> To that end, I propose the following:
>
> 1. We split out the schema declaration into a text file in
> buildbot/db/schema.sql, with some simple pattern that will help split
> it into SQL statements. The schema always represents the latest
> version.  Since this is rarely needed, there's no sense keeping it in
> RAM.  It should indicate the current DB version in some easily-parsed
> fashion.
>
> 2. Upgrade scripts are stored in functions named "upgradeXtoY" in
> modules named buildbot.db.upgrade.toY; these modules will only be
> imported when required for an upgrade operation, saving some memory
> load and startup time.  Upgrades should be performed in transactions.
>
> 3. As is the case in 'master' now, Buildbot should refuse to upgrade
> the database (whether from version 0, the on-disk pickles format, or
> from a later db-related version) unless buildbot upgrade-master is
> run.  That is, Buildbot won't start with an old/missing database.
>
> Open questions:
>
> I. Should a fresh install start at an empty version-0 database (like
> buildbot-0.7.*) and run through the upgrades to get to the current
> version?  Or should it install from the schema file?  The latter has
> the potential to allow new installs' schemas to diverge from old,
> upgraded installs -- but we should be able to prevent that with proper
> testing.
>
> Thoughts?  I hope to implement this tomorrow (Tuesday).

I'd say take a look at Rail's migrations.
It handles db upgrades/downgrades/initializations.
Of course you can't use the code.
But the concepts pretty clean.
Basically a table which tells which "migrations" have been applied.
Each migration is a class which has methods for clean/upgrade/downgrade,etc.
When you run the migration you can specify to which one, and it
figures out if it's an up or a down from where you are, and then
applies each migration class in sequence.
It's pretty elegant. (I think)

-Bill