Database schema upgrade

Database schema upgrade overview

Initial database schema creation

As you already know DataObjects.Net automatically generates database schema when you build a domain. You don’t need to create tables and columns manually, everything you need to do is just to create empty database and specify appropriate connection string in domain configuration.

Let us suppose we have created our model consisting of one class:

[HierarchyRoot]
public class Order : Entity
{
  [Key, Field]
  public int Id { get; private set;}

  [Field]
  public string ProductName { get; set; }

  [Field]
  public int Quantity { get; set; }
}

Then build domain:

var domainConfig = new DomainConfiguration("sqlserver://localhost/myDatabase");
domainConfig.Types.Register(myAssembly, "MyProduct.Model");
domainConfig.UpgradeMode = DomainUpgradeMode.Recreate;
var domain = Domain.Build(domainConfig);

When domain is built, database structure will look like this:

You can see that there is Order table corresponding to Order entity in our model and several additional tables.

Existing database schema upgrade

You can also notice that in previous example we specified DomainUpgradeMode.Recreate as upgrade mode in domain configuration, it means that existing database schema and data should be completely cleared before new schema is generated. This mode is very useful when you want to create new database or perform unit tests, but when our application is in use we need to upgrade database schema from one version to another without loosing existing data, moreover sometimes existing data should be also modified, e.g. we may want to change some column type or move some data from one class to another.

There are several available values of DomainUpgradeMode :

• Validate – Domain will check whether database schema is compatible with persistent model, anyway it will not be modified. If schema is incompatible, exception will be thrown.
• Recreate – Database schema will be completely recreated, all existing data will be removed.
• Perform – Storage upgrade will be performed. All missing database structures (columns, tables) will be added, excess ones will be removed.
• PerformSafely (default value) – The same as Perform mode, but any actions leading to data or precision lost are never performed to the database unless all of them are explicitly enabled.

Automatic upgrade

In order to upgrade exiting database schema and save stored data we can use Perform or PerformSafely mode. In simple cases such upgrade can be performed fully automatically.

For example let’s create several orders and then change our model: Add Customer class and Customer property to Order class:

[HierarchyRoot]
public class Order : Entity
{
  // ...

  [Field]
  public Customer Customer { get; set;}
}

[HierarchyRoot]
public class Customer : Entity
{
  [Key, Field]
  public int Id { get; private set; }

  [Field]
  public string Name { get; set; }
}

If we build our domain with Validate mode, we’ll get SchemaSynchronizationException in Build() method. If we build it with Recreate mode, it will create new database schema, but data will be lost. When we have our domain built with Perform or PerformSafely upgrade mode, we’ll see following database structure:

DataObjects.Net automatically added Customer table and Customer.Id column to Order table. New table Customer is empty and value of new field is null, but all existing orders are saved.

Upgrade handlers and hints

Automatic upgrade works perfectly when we add new classes and properties to our model, but sometimes we need to perform some specific schema modifications, for example we can rename some properties or classes.

Rename hints

Let’s rename Customer class to Person and, for example, Name property to FullName.

[HierarchyRoot]
public class Order : Entity
{
  // ...

  [Field]
  public Person Customer { get; set;}
}

[HierarchyRoot]
public class Person : Entity
{
  [Key, Field]
  public int Id { get; private set; }

  [Field]
  public string FullName { get; set; }
}

To upgrade schema correctly, we should inform domain that Person class is renamed Customer, otherwise it will remove Customer table with all records and create new empty Person table. To do this we use so called upgrade handlers and upgrade hints.

Upgrade handler is class responsible for upgrade of persistent classes from one assembly. It must be inherited from UpgradeHandler or implement IUpgradeHandler interface, it also must have a parameterless constructor. Each assembly with persistent model should contain exactly one upgrade handler class, it will be automatically found when schema is being upgraded. UpgradeHandler class contains a set of methods and properties that can be overridden for customizing schema upgrade process.

public class Upgrader : UpgradeHandler
{
  protected override void AddUpgradeHints(Xtensive.Collections.ISet<UpgradeHint> hints)
  {
    hints.Add(new RenameTypeHint("MyProduct.Model.Customer", typeof (Person)));
    hints.Add(new RenameFieldHint(typeof (Person), "Name", "FullName"));
  }
}

In our example we have overriden AddUpgradeHints method to add two hints. If we build domain with new model it will find our upgrade handler and will rename columns and tables instead of removing and creating new ones, so their data will be saved.

• RenameFieldHint describes a renaming of a persistent field. You should create this hint when you rename a persistent field.
• RenameTypeHint describes a renaming of a persistent type. You should create this hint when you rename entities or structures.

Remove hints

In order to understand meaning of remove hints let’s remember difference between Perform and PerformSafely upgrade modes. Second one requires all modifications leading to losing data to be explicitly permitted. For example, if we remove some persistent field or persistent class from our model, domain builder will remove appropriate database structure in Perform mode and will throw exception in PerformSafely mode. To perform such operations in safe mode we should use following hints:

• RemoveFieldHint describes a renaming of a persistent field.
• RemoveTypeHint describes a renaming of a persistent type.

For example, if you want to remove Quantity field from Order in PerformSafely mode, you would remove appropriate property from Order class and add RemoveFieldHint to hints collection in AddUpgradeHints method.

Change type hint

ChangeFieldTypeHint is used when you want to change type of some persistent column. For example you have Order class with Number property of int type, but new version of application should allow to store numbers with literal prefix, and we definitely want to change column type to string and convert all stored numbers. To do this we change type of property to string:

[Field(Length = 20)]
public string Number { get; set; }

And add ChangeFieldTypeHint in our custom upgrade handler:

hintSet.Add(new ChangeFieldTypeHint(typeof(Order), "Number"));

If hint is not added domain builder will create new column without saving data. In safe mode an exception will be thrown.

You should also use ChangeFieldTypeHint hint if you use PerformSafely mode change length of string field or precision of decimal field.

When column type is changed domain builder uses standard functions of RDBMS to convert values. In our example we changed int type to string and values were successfully converted. But if types are incompatible in particular RDBMS, we’ll get appropriate exception.

Move and copy field hints

Imagine we have Order type inherited from Document, Order class has Number property. We want number to be a property of Document class. We can declare such property in Document and remove it from Order, but we also have to copy data from Order table to Document table before order’s column will be removed.

To do this we can use MoveFieldHint, that moves some field data to another class within single hierarchy:

hintSet.Add(new MoveFieldHint("MyProduct.Model.Order", "Number", typeof(Document)));

Whereas MoveFieldHint implies that original field is removed, CopyFieldHint don’t, it just copies. To implement our upgrade handler using CopyFieldHint we should add RemoveFieldHint.

hintSet.Add(new CopyFieldHint("Order", "Number", typeof(Order)));
hintSet.Add(new RemoveFieldHint("Order", "Number"));

Recycled types and fields

There are some cases where automatic upgrade with upgrade hints is not enough to describe changes in our model. For example suppose we decided to extract product information from Order class to separate Product class. In original model we have Order.ProductName property of string type. In result model we have Order.Product property of Product type, which has Name property.

So we should enumerate all orders during upgrade process, create new product for each unique product name, and assign newly created products to Product property of each order. We can not do this when domain already built because column ProductName should be removed from Order table as a result of schema upgrade. Since it is rather complex operation, we will perform it manually.

First of all we should make necessary changes to our model – add new persistent class and persistent field. Then we mark old field ProductName as recycled, it is also recommended to mark it as obsolete:

[HierarchyRoot]
public class Order : Entity
{
  // ...

  [Field]
  public Product Product { get; set; }

  [Field, Obsolete, Recycled]
  public string ProductName { get; set; }
}

[HierarchyRoot]
public class Product : Entity
{
  [Key, Field]
  public int Id { get; private set; }

  [Field]
  public string Name { get; set; }
}

Next thing we should do is to implement our custom upgrade handler for this upgrade:

public class Upgrader : UpgradeHandler
{
  public override void OnUpgrade()
  {
    var session = Session.Demand();
    foreach (var order in session.Query.All<Order>()) {
      var product = session.Query.All<Product>()
         .SingleOrDefault(p => p.Name==order.ProductName);
      if (product==null)
        product = new Product { Name = order.ProductName };
      order.Product = product;
    }
  }
}

As you can see, we have overridden OnUpgrade() method of upgrade handler and placed our custom logic there. We manually query the orders and create new products. When this method is executed, both old and new fields are accessible. But when domain is already built, you can’t use recycled fields; moreover, their underlying tables and columns are removed at that moment.

In our example we used recycled fields, but it is also possible to use recycled persistent classes in exactly the same way. You need just mark types to be removed by [Recycled] attribute and recycle their data in OnUpgrade() method.

Legacy database schema support

DataObjects.Net can be used for mapping of entities to existing database. There are two special upgrade modes for this:

• DomainUpgradeMode.LegacySkip skips any schema checks.
• DomainUpgradeMode.LegacyValidate ensures all mapped structures (tables and columns) are available in database; fails with exception if something is absent.

Currently legacy mode has a set of limitations:

• IDENTITY columns are not yet supported - you have to use generators or write your own ones.
• Mapping to multiple schemas is not supported.
• Mapping to views and stored procedures isn’t supported.
• T4-based model code generation (ActiveRecord pattern) is not available, i.e. you should write the classes by your own.
• If you’re going to use non-paired EntitySets, you can’t define table and column mappings for runtime-generated type that is used to represent its items. Usage of such non-paired EntitySets is optional, but this is pretty convenient to use them in many cases.
• Also, you can’t fully control mappings for composite foreign keys.

Rules to follow:

• Use DomainUpgradeMode.LegacySkip or DomainUpgradeMode.LegacyValidate to to turn this feature on.
• If mapped table and column names differ from derived by default mapping rules, use [TableMapping] and [FieldMapping] attributes.
• You must explicitly specify type discriminators (using [TypeDiscriminator] and [TypeDiscriminatorValue(...)] attributes), if you’re going to map hierarchies with inheritance. Note that inheritance hierarchies can also be represented as non-inherited types containing reference as key of “descendant” type.
• Take all the above-mentioned limitations into account.

Here is an example of Legacy model mapping. Note that we are mapping 3 classes to the single table “Animals” and use ElementType property as type discriminator.

[HierarchyRoot(InheritanceSchema = InheritanceSchema.SingleTable)]
[TypeDiscriminatorValue("Animal", Default = true)]
[TableMapping("Animals")]
public class Animal : Entity
{
  [Field, Key]
  public Guid Id { get; private set; }

  [Field(Length = 50), TypeDiscriminator]
  [FieldMapping("Type")]
  public string ElementType { get; private set; }

  [Field(Length = 50)]
  public string Name { get; set; }

  [Field]
  public int Age { get; set; }

  [Field]
  [FieldMapping("Owner")]
  public Person Owner { get; set; }
}

[TypeDiscriminatorValue("Dog")]
public class Dog : Animal
{
}

[TypeDiscriminatorValue("Cat")]
public class Cat : Animal
{
}