Sterling Silverlight and Windows Phone 7 Database Triggers and Auto-Identity

Sterling changeset 72063 [browse the source code here] introduces triggers. The trigger is a powerful feature that allows you to universally intercept database operations. Their application in Sterling resolves several concerns, including:

• Validation — prevent a save operation from succeeding if data integrity is compromised, or prevent a delete operation when prerequisites are not met
• Data-specific concerns — should the business layer be concerned with a “last modified date” that relates to the database? Use a trigger to set the date consistently without involving other pieces of your application
• Post-save processing (for example, clear a “dirty flag” once the item is persisted)

Declaring the Trigger

Declaring the trigger in Sterling is straightforward. Every entity that is persisted by sterling is defined by a combination of the type and the key type. The trigger is no different. Internal to Sterling, a basic interface allows the database engine to manage triggers without having to close the generic type:

internal interface ISterlingTrigger
{
    bool BeforeSave(Type type, object instance);
    void AfterSave(Type type, object instance);
    bool BeforeDelete(Type type, object key);
}

Next, we want a more strongly typed version for definitions and for external sources that are aware of the type. This allows those interfaces to close the generic and work with a strongly typed interface:

internal interface ISterlingTrigger<T, TKey> : ISterlingTrigger where T: class, new() 
{
    bool BeforeSave(T instance);
    void AfterSave(T instance);
    bool BeforeDelete(TKey key);
}

Side Note: Covariance and Contravariance

Wikipedia has a good article explaining covariance and contravariance. For their application in C#, read the related Microsoft article. In C# you can declare types in generics as co- and contra- variant using the “in” and “out” keywords. The original interface definition used this until I realized they are no-go on the phone. Basically, the IDE and compiler will allow you to define them, but at runtime they fail with a type load exception.

Defining a Trigger

You may have noticed that the interfaces are internal to Sterling. That’s because the way to define a trigger is through the base class that looks like this:

public abstract class BaseSterlingTrigger<T,TKey> : ISterlingTrigger<T,TKey> where T: class, new()
{
    public bool BeforeSave(Type type, object instance)
    {
        return BeforeSave((T) instance);
    }

    public void AfterSave(Type type, object instance)
    {
        AfterSave((T) instance);
    }
       
    public bool BeforeDelete(Type type, object key)
    {
        return BeforeDelete((TKey) key);
    }

    public abstract bool BeforeSave(T instance);

    public abstract void AfterSave(T instance);

    public abstract bool BeforeDelete(TKey key);
        
}

This is a common pattern I use when working with generics. The internal engine wants to deal with the object and the type, while the externals want to close the generic. In order to provide a contract to deal with a typed entity and keep the developer from worrying about any conversion, I can use the abstract class to overload from the non-typed to the typed version. The casting is very inexpensive compared to the reflection that would have to happen to manually invoke methods and close the generics in the core database engine.

The TriggerTest included with the Sterling project demonstrates the definition of a trigger:

public class TriggerClassTestTrigger : BaseSterlingTrigger<TriggerClass, int>
{
    public const int BADSAVE = 5;
    public const int BADDELETE = 99;

    private int _nextKey;
        
    public TriggerClassTestTrigger(int nextKey)
    {
        _nextKey = nextKey;
    }

    public override bool BeforeSave(TriggerClass instance)
    {
        if (instance.Id == BADSAVE) return false;
            
        if (instance.Id > 0) return true;

        instance.Id = _nextKey++;                       
        return true;
    }

    public override void AfterSave(TriggerClass instance)
    {
        instance.IsDirty = false;
    }

    public override bool BeforeDelete(int key)
    {
        return key != BADDELETE;
    }
}

This trigger does a few things. It takes in a key and stores that value. In the case of integer identity fields, for example, the portion of your code that initializes the database can perform a query to find the maximum key that exists. You can then increment the value and pass it to the trigger class, which will auto-set the identity for new entities (assuming anything without a positive non-zero id is new) and keep track of the next key.

There is an arbitrary validation that returns false if the id is an explicit value. The Sterling database engine will throw a SterlingTriggerException if the BeforeSave or BeforeDelete methods return false, preventing the class from being persisted. This is meant as a last resort, as exceptions are expensive and your code should validate these conditions and prevent them prior to saving.

In the AfterSave you can see the example of automatically clearing the dirty flag.

Registering the Trigger

Registering the trigger is straightforward. Triggers can be registered anytime after the database is activated (this allows you to query the database and preset and trigger conditions prior to activating them). They can also be unregistered. Triggers in Sterling are more like inceptors and may be transient, rather than traditional relational database triggers which can be thought of as part of the table definition itself.

The following code demonstrates a pattern for handling auto-identity fields. It activates the Sterling engine, registers and activates the database, queries for the highest key value and then registers the trigger using the last known key:

_engine = new SterlingEngine();
_engine.Activate();
_databaseInstance = _engine.SterlingDatabase.RegisterDatabase<TriggerDatabase>();

var nextKey = _databaseInstance.Query<TriggerClass, int>().Any() ?
    (from keys in _databaseInstance.Query<TriggerClass, int>()
        select keys.Key).Max() + 1 : 1;

_databaseInstance.RegisterTrigger(new TriggerClassTestTrigger(nextKey));

Afterword: Windows Phone 7 Tests

As of this blog date an interesting issue exists that the Sterling team is investigating. Triggers appear to work perfectly fine on the Windows Phone 7 and the sample project has been updated to use a trigger. However, when the trigger is included in the unit tests for the phone, the unit test harness initializes but fails to run any tests. This is true when simply the definition of the trigger class is provided.

I will follow up once we determine the cause. It appears to be an issue with the unit test framework and I suspect something happens when it scans the types in the assembly to find tests. For now we have the unit test disabled on the phone but will update you once the cause is found and rectified.

Sterling is getting closer to versoin 1.0 RTM. The remaining pieces include a similar trigger-like architecture for intercepting the byte streams to enable encryption and compression or other manipulation, and streams to expose a backup and restore mechanism.

Visit Sterling online at http://sterling.codeplex.com/.