O/R mappers that can update the database schema automatically?

Question

Are there any O/R mappers out there that will automatically create or modify the database schema when you update the business objects? After looking around it seems that most libraries work the other way by creating business object from the database schema.

The reason I'd like to have that capability is that I am planning a product that stores its data in a database on the customer's machine. So I may have to update the database schema when a new version comes out.

Another requirement is that the mapper supports a file based database like SQLite or JET, not only SQL server.

I know XPO from Developer Express has that capability but I was wondering if there are any alternatives out there.

Thanks

Answer 1

SubSonic does what you need. It has a migration class to be use with sonic commander. I modified the code and put it in the startup of my apps, and have it check for version difference and upgrade automatically. This is how you need to setup it up:

1. You need to add the versioned migration class. You might want to take a look at this tutorial if you never used SubSonic before. The following class is saved as \Migrations\001_initial.cs, if you called migrate.exe (inside VS) from subsonic, it will update the current db to the latest version available. You need to keep the 3 digit version prefix as your filename for migrate.exe to work properly as mentioned in the tutorial. As for the class name, the underscore and 3 digit version prefix is needed for the migration class written by myself to detect the version properly.

//*Not sure why I need this line to make the code block working below

namespace MyApps.Migrations
{
    public class _001_Initial : Migration
    {
        public override void Up()
        {
            //Execute your upgrade query here
        }
        public override void Down()
        {
            //Execute your downgrade query here
        }
    }
}

2. Called CheckForMigration() during the startup of your application. IsUpdateAvailable will indicate that you need to update the db. You just need to call Migrate(). IsAppVersionOlder indicate that your application is made with older version db schema (maybe another copy of the newer apps has updated the db). With, you can prevent the older apps from running and corrupting your updated db.

//

using System;
using System.Collections.Generic;
using SubSonic;
using SubSonic.Migrations;

namespace MyApps.Migrations
{
    internal static class MigrationHelper
    {
        const string NameSpace = "MyApps.Migrations";
        private const string SCHEMA_INFO = "SubSonicSchemaInfo";
        public static int CurrentVersion { get { return currentVersion; } }
        public static int AppVersion { get { return latestVersion; } }
        public static bool IsUpdateAvailable { get { return (updateVersion.Count > 0); } }
        public static bool IsAppVersionOlder { get; private set; }
        public static bool Checked { get; internal set; }
        private static int currentVersion;
        private static int latestVersion;
        private static List<string> updateVersion;
        private static List<string> availableVersion;

        static MigrationHelper()
        {
            Checked = false;
        }

        /// <summary>
        /// Migrates the specified migration directory.
        /// </summary>
        public static void CheckForMigration()
        {
            currentVersion = Migrator.GetCurrentVersion("YourProviderName");
            Type[] allTypes =
                        System.Reflection.Assembly.GetExecutingAssembly().GetTypes();
            availableVersion = new List<string>();
            foreach (Type type in allTypes)
            {
                if (type.Namespace == NameSpace)
                    if (type.Name.Substring(0, 1) == "_")
                        availableVersion.Add(type.Name);
            }

            availableVersion.Sort();
            updateVersion = new List<string>();
            foreach (string s in availableVersion)
            {
                int version = 0;
                if (int.TryParse(s.Substring(1,3), out version))
                {
                    if (version > currentVersion)
                    {
                        updateVersion.Add(s);
                    }
                    latestVersion = version;
                }
            }
            IsAppVersionOlder = (latestVersion < currentVersion);
            //log.WriteLine(string.Format(
            ///"CheckForMigration: DbVer = {0}, AppVer = {1}, UpdateAvailable = {2}, IsAppOlder = {3}",
                //currentVersion, latestVersion, updateVersion.Count, IsAppVersionOlder));
            Checked = true;
        }

        internal static void Migrate()
        {
            foreach (string s in updateVersion)
            {
                Migration _migration = (Migration)Activator.CreateInstance(
                    System.Reflection.Assembly.GetExecutingAssembly().GetType(
                    "MyApps.Migrations." + s));
                _migration.Migrate("YourProviderName", Migration.MigrationDirection.Up);
                IncrementVersion();
            }
        }

        private static void IncrementVersion()
        {
            new Update(SCHEMA_INFO, 
            "YourProviderName").SetExpression("version").EqualTo("version+1").Execute();
        }

    }
}

A table named SubSonicSchemaInfo will be added automatically by SubSonic to your db to help keep track of the db version.

It's a long post, hope I didn't missed anything

Answer 2

Can NHibernate update the schema or does it drop and recreate it?

Answer 3

NHibernate can generate the database schema from the business objects for you.