Automatic Schema Validation using NHibernate/ActiveRecord

Question

Hi,
let's assume I have a table of Products with columns: Id, Name, Price and using NHibernate (or ActiveRecord) I map the table to the POCO:

public class Product
{
public virtual long Id { get; set; }
public virtual string Name { get; set; }
public virtual double Price { get; set; }
}

Now if someday a new column named ShipmentPrice (let's assume it's double too) will be added to the Products table, is there any way I can automatically know that. For saying automatically I mean adding code to do that or getting an exception? (I assume I don't have control on the columns of the table or a way to know of any changes to the table's schema in advance)

Answer 1

You could use NHibernate's SchemaValidator, but IIRC it only checks that your mapped entities are valid so it doesn't check if there are more columns than mapped properties since that wouldn't really break your app.

Answer 2

You do recall correctly, Mauricio. The following code shows how you can create or update a schema. The update will run when Validate() raises an exception. No exception will be thrown when a field is available in the database but not in the configuration. It is perfectly legal to have extra fields: you don't want them to be deleted, I hope? That could cause tremendous damage...

The following code shows Test, Create, Validate and Update, each step with the proper exception handling. The code is simplified, but it should give you a handle on how to do a validation.

This code helps with Entity-centric (POCO) ORM configurations, where you can add a field to your class and it will automatically be updated in the database. Not with table-centric, where fields are leading.

// executes schema script against database
private static void CreateOrUpdateSchema(Configuration config)
{
    // replace this with your test for existence of schema
    // (i.e., with SQLite, you can just test for the DB file)
    if (!File.Exists(DB_FILE_NAME))
    {
        try
        {
            SchemaExport export = new SchemaExport(config);
            export.Create(false, true);
        }
        catch (HibernateException e)
        {
            // create was not successful
            // you problably want to break out your application here
            MessageBox.Show(
                String.Format("Problem while creating database: {0}", e),
                "Problem");
        }
    }
    else
    {

        // already something: validate
        SchemaValidator validator = new SchemaValidator(config);
        try
        {
            validator.Validate();
        }
        catch (HibernateException)
        {
            // not valid, try to update
            try
            {
                SchemaUpdate update = new SchemaUpdate(config);
                update.Execute(false, true);
            }
            catch (HibernateException e)
            {
                // update was not successful
                // you problably want to break out your application here
                MessageBox.Show(
                    String.Format("Problem while updating database: {0}", e),
                    "Problem");
            }
        }
    }
}

-- Abel --