Migrations and Backup+Restore

Hello:

I have an existing SQLite-based application with Lightspeed5. This application has a backup and restore facility which backs up by copying the SQLite database file somewhere else and the restore reverses the process.

One of the problems I have is if the database schema changes between the backup and subsequent restore, i.e.

Monday: Backup with old version of application. Tuesday: Update application, including a schema change. Wednesday: Dog eats laptop, get new laptop, install current version of application (and model) Thursday: Restore data.

My current mechanism is a cumbersome body of dumb code containing alter statements that always execute whenever the application starts, for example this:

        try
        {
            using (IDbCommand cmd = work.Context.DataProviderObjectFactory.CreateCommand())
            {
                cmd.CommandText = "ALTER TABLE [MyTable]....";
                cmd.CommandType = CommandType.Text;
                work.PrepareCommand(cmd);
                cmd.ExecuteScalar();
            }
        }
        catch
        {
            ;   // Ignore all
        }

What can I say, it wasn't my best work back when I first started with Lightspeed 2 :)

I am reading the Migrations chapter in the manual and thought I could use this to auto-magically migrate a restored file up to the current version of the data model.

I created my Migrations project which auto-generates the migration to create the database based on the current model. This works great for creating a NEW database from the current model!

The automatically created class that extends Mindscape.Lightspeed.Migrations.Migration works great when the database file doesn't exist, but when it does it of course throws exceptions because the tables already exist.

My question is, has anyone used Migration for a purpose like this and, if so, are there recommended techniques for doing so?

Migration (the class) has a Version property and my thought would be to store that version in the database itself and with before every Up() execution, check to see if it is necessary to actually execute that Up().

Of course, if this idea is a dumb one, I'd like to hear about that too :)

Thank you for any suggestions and comments !!!

Lionel

Yes you can certainly do this as long as you can determine what version in your migration chain the current database schema is at. Generally its easiest to just store a version number somewhere in the database that you can inspect to determine this and have this updated as part of each migration up or down.

JD wrote a blog post about how you can handle this which I suspect will be of use here: http://www.mindscapehq.com/blog/index.php/2010/12/01/upgrading-database-structures-with-lightspeed-migrations/

Let us know if you have any more questions! :)

Jeremy

Thanks Jeremy!

I was able to successfully apply the techniques in JD's post.

I just had one little gotcha to deal with. On an existing database out in the wild, there is no DBVERSION table to work from. Nothing to be concerned about for a new database, the Migration just works creating anew. But on an existing one, it's apparently required. So I just created that table using the code in my previous post (for existing databases only) and then Migrations just flow naturally downstream.

Exactly does what I was aiming for!

Once again, thank you for your time; I appreciate it

Best regards,

Lionel