[Opensim-dev] introducing, database migrations

[Justin Clark-Casey]

+1, excellent stuff Sean.

Sean Dague wrote:
> 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
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Opensim-dev mailing list
> Opensim-dev at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-dev


-- 
justincc
Justin Clark-Casey
http://justincc.wordpress.com