News, examples, tips, ideas and plans.
Thoughts around ORM, .NET and SQL databases.

Saturday, July 16, 2011

More flexibility to database schema upgrade

In this post I'm going to describe one of the most advanced and appreciated features of DataObjects.Net — automatic database schema upgrade.

You know, automatic database schema generation for Code-First & Model-First ORMs is a vital requirement, however while the task is not trivial by itself, it is not so difficult in comparison with continuous domain model & database schema synchronization. One can imagine all kind of operations on domain model: creating, renaming, moving, splitting, merging, deleting types, fields, associations, field options, index options, validation constraints, etc. All these types of changes must be propagated by ORM to database schema more or less transparently and without any possible loss of data.

In DataObjects.Net this goal is achieved with the help of mature upgrading framework that had been polished for years in version 3.x branch (starting from 2003 year) and then was migrated to version 4.x one with numerous core updates and extensions.

This could be surprising, but the core component of the upgrade framework is not translation of upgrade actions to SQL or making changes to a database schema, but the effective comparison of 2 abstract models. I'm talking about Xtensive.Modelling that was invented for describing and comparing models of any thing. In case of DataObjects.Net it is being used to compare 2 models of a storage.

Here is how the whole process is constructed. We build domain model and convert it to a unified model of storage. In parallel, we extracting metadata about database schema and converting it to the model of existing storage. After that we compare these two and additionally pass to the comparison procedure a set of upgrade hints that are provided by a user. As a result, we get a difference between 2 models and a sequence of upgrade actions that must be executed in order to convert the existing storage to the new one.

Upgrade actions are taken place only when a domain is being built in DomainUpgradeMode.Perform or PerformSafely. These two have one principal distinction: while Perform silently alters whatever is required even with data loss, PerformSafely guarantees that nothing will be removed unless is explicitly declared by a user by using special upgrade hints and attributes. The hints are nonetheless important and useful in both scenarios as they provide additional information for the comparison routine on what changes are made to domain model.

What are these hints?
  • RenameTypeHint — for renaming a persistent type
  • RenameFieldHint — for renaming a persisting field
  • RemoveTypeHint — for removing a persistent type
  • RemoveFieldHint — for removing a persistent field
  • ChangeFieldTypeHint — for a situation when type of a persistent field is changed
  • MoveFieldHint — for moving a persistent field from one type to another
  • CopyFieldHint — for copying a persistent field from one type to another
The hints mechanism is intended to be used in custom implementation of UpgradeHandler class. This can be included into an assembly that is going to be upgraded from version "" to another one and can look like this:

public class MyUpgradeHandler : UpgradeHandler
  public override bool CanUpgradeFrom(string oldVersion)
    return oldVersion == "";

  protected override void AddUpgradeHints()
    var hintSet = UpgradeContext.Hints;
      new RenameTypeHint("MyProduct.Model.Customer", typeof (Person)));
      new RenameFieldHint(typeof (Person), "Name", "FullName"));
More about the hints and usage examples in our manual.

The upgrade hints infrastructure serves well for the overwhelming majority of scenarios but there are relatively rare situations where a change in domain model can't be described with these hints. We were gathering information and analyzing such cases and afterall, decided to extend the interface of UpgradeHandler class to provide an additional point where the upgrade routine can be corrected by user. On the picture above there is the last point, where upgrade actions are generated based on the storage models comparison result. Until now, these actions were automatically applied to a database schema and there was no way to control this process.

We are adding a method to UpgradeHandler class that exposes a sequence of upgrade actions before thay are applied to database scheme:

public class UpgradeHandler : IUpgradeHandler

    // ...

    public virtual void OnBeforeExecuteActions(UpgradeActionSequence actions)
      // In overridden method actions can be added, edited, removed, etc.

UpgradeActionSequence contains all actions that will be played against a database scheme, split in groups. An action is a regular SQL command. In this method any action can be removed, edited, moved from one group to another, new actions can be added to the sequence and so on.

The actions are executed in the following order:

01. NonTransactionalEpilogueCommands
02. Open transaction
03. CleanupDataCommands
04. PreUpgradeCommands
05. UpgradeCommands
06. CopyDataCommands
07. PostCopyDataCommands
08. CleanupCommands
09. Commit transaction
10. NonTransactionalEpilogueCommands

Note the transaction boundaries: all commands except those that don't support transactional execution are run in one transaction so on any error the scheme will stay valid and integral.

The new bits are being tested now and will be available soon as nightly builds. After the thorough testing, the updated DataObjects.Net will be available as usual on our website.

Thanks for your attention.


  1. Great post partially closing really disappointing gap in documentation, Dmitri.

  2. There is no need to suffer academic pressure alone, get comprehensive help from the best online term paper writing company and score top-of-the-class grades.