[Opensim-dev] introducing, database migrations

[Sean Dague]

After far too much frustration with our current adhoc database migration
model, I've implemented a more generic approach, inspired by the way
ruby on rails does this.  The implementation is in
OpenSim/Data/Migration.cs for those interested.

From the documentation in that file:

   /// The Migration theory is based on the ruby on rails concept.
    /// Each database driver is going to be allowed to have files in
    /// Resources that specify the database migrations.  They will be
    /// of the form:
    ///
    ///    001_Users.sql
    ///    002_Users.sql
    ///    003_Users.sql
    ///    001_Prims.sql
    ///    002_Prims.sql
    ///    ...etc...
    ///
    /// When a database driver starts up, it specifies a resource that
    /// needs to be brought up to the current revision.  For instance:
    ///
    ///    Migration um = new Migration(Assembly, DbConnection,
    "Users");
    ///    um.Upgrade();
    ///
    /// This works out which version Users is at, and applies all the
    /// revisions past it to it.  If there is no users table, all
    /// revisions are applied in order.  Consider each future
    /// migration to be an incremental roll forward of the tables in
    /// question.
    ///
    /// Assembly must be specifically passed in because otherwise you
    /// get the assembly that Migration.cs is part of, and what you
    /// really want is the assembly of your database class.
    ///

SQLite driver has been converted to this model.  When making a change to
the data structure for SQLite for any of the stores, create it as
002_xxx.sql and make it alter table calls, or creation of new tables
(unrelated to the existing ones).  Think of it as a series of database
patches that roll forward.

I'll do MySQL tomorrow (or at least some of it).  While we move from our
current adhoc approach to migrations, there is going to be some legacy
code.  I'm declaring changeset 6000 as the cuttoff point where we can
remove the legacy.  So trying to jump from 5050 -> 6000 without doing any
intermediary revisions isn't going to work.  I suspect there will be < 3
people in the world that are trying to do that anyway.

This whole approach makes dealing with database changes much easier,
much more consistant, and should reduce hesitation to doing database
schema changes.

     -Sean

-- 
__________________________________________________________________

Sean Dague                                       Mid-Hudson Valley
sean at dague dot net                            Linux Users Group
http://dague.net                                 http://mhvlug.org

There is no silver bullet.  Plus, werewolves make better neighbors
than zombies, and they tend to keep the vampire population down.
__________________________________________________________________
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
URL: <http://opensimulator.org/pipermail/opensim-dev/attachments/20080611/1e11860b/attachment-0001.pgp>