[Buildbot-devel] Database Upgrade thoughts

[Steve 'Ashcrow' Milner]

On 22/02/10 22:17 -0800, Bill Deegan wrote:
>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
>
>------------------------------------------------------------------------------
>Download Intel® Parallel Studio Eval
>Try the new software tools for yourself. Speed compiling, find bugs
>proactively, and fine-tune applications for parallel performance.
>See why Intel Parallel Studio got high marks during beta.
>http://p.sf.net/sfu/intel-sw-dev
>_______________________________________________
>Buildbot-devel mailing list
>Buildbot-devel at lists.sourceforge.net
>https://lists.sourceforge.net/lists/listinfo/buildbot-devel

Another similar way which is in python is django-south.

-- 
kthxbye!
Steve 'Ashcrow' Milner
Agent of Infosec
RHCE: https://www.redhat.com/training/certification/verify/?certno=805009277242449
ITIL Foundation: c.721843
IRC: ashcrow
GnuPG ID: 28DFD4BE

-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCS/IT/MU/O d-- s:+> a- C+++$ UBL+++$ P++@ L+++$>++++ !E--> W+++$ !N-
!o K--? !w-- !O- M- !V- PS PE+ Y+ PGP+++ t+ !5 !X R tv+ b+>++ DI+ !D-
G e h !r>+++ y?
------END GEEK CODE BLOCK------

"In the heat of conversation I may have said certain things I believe 
to be untrue. The alleged lie that you might have heard me saying 
allegedly moments ago ... that's a parasite that lives in my neck." 
     -- Tad Ghostal
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 197 bytes
Desc: not available
URL: <http://buildbot.net/pipermail/devel/attachments/20100223/0a8d0cb2/attachment.bin>