[Buildbot-devel] Database Upgrade thoughts

[Dustin J. Mitchell]

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).

Dustin

-- 
Open Source Storage Engineer
http://www.zmanda.com