Changelog for Domain Objects

In this post, I would like to present you a simple way to implement changelog system for database objects using NHibernate session interceptor and database triggers. This system will inform which rows were created, deleted and which columns were updated and what were their previous values.

Let's imagine we have the UNIVERSE database that is used by astronomers all around the world to store discovered planets' records. Let's then think that they want to know what changes have been made to the planets' descriptions and who made them (to eventually ask him for proofs). Our simple database will have only two tables:

Planet table stores basic information about discovered planets and PlanetChangeLog table stores all changes that has been made to the Planet table. The easiest way to fill PlanetChangeLog is to use triggers. A problem that arises is how to fill the Modifier column - in the database, we often don't know who is logged to the web application (the database logged user is usually the application itself). So in the trigger code, we need to use a column ModifiedBy from the modified row:

create trigger planet_insupdttrg on Planet 
for update
as
begin
 set nocount on
 
 if (update(PlanetName))
  insert into PlanetChangeLog
       (PlanetId
       ,ChangedColumn
       ,OldVal
       ,NewVal
       ,Modifier
       ,ModificationDate)
    select ins.PlanetId
     ,'PlanetName'
     ,del.PlanetName
     ,ins.PlanetName
     ,ins.ModifiedBy
     ,ins.ModifyDate
    from inserted ins
    inner join deleted del on ins.PlanetId = del.PlanetId
 if (update(MeanRadius))
  insert into PlanetChangeLog
       (PlanetId
       ,ChangedColumn
       ,OldVal
       ,NewVal
       ,Modifier
       ,ModificationDate)
    select ins.PlanetId
     ,'MeanRadius'
     ,del.MeanRadius
     ,ins.MeanRadius
     ,ins.ModifiedBy
     ,ins.ModifyDate
    from inserted ins
    inner join deleted del on ins.PlanetId = del.PlanetId
 if (update(Volume))
  insert into PlanetChangeLog
       (PlanetId
       ,ChangedColumn
       ,OldVal
       ,NewVal
       ,Modifier
       ,ModificationDate)
    select ins.PlanetId
     ,'Volume'
     ,del.Volume
     ,ins.Volume
     ,ins.ModifiedBy
     ,ins.ModifyDate
    from inserted ins
    inner join deleted del on ins.PlanetId = del.PlanetId
 if (update(Mass))
  insert into PlanetChangeLog
       (PlanetId
       ,ChangedColumn
       ,OldVal
       ,NewVal
       ,Modifier
       ,ModificationDate)
    select ins.PlanetId
     ,'Mass'
     ,del.Mass
     ,ins.Mass
     ,ins.ModifiedBy
     ,ins.ModifyDate
    from inserted ins
    inner join deleted del on ins.PlanetId = del.PlanetId
 set nocount off
end

Now, in the application code, we must fill correctly ModifiedBy and CreatedBy column. Let's first look at the object definition:

public class Planet : IAuditable
{
    public virtual Int32 Id { get; protected set; }

    public virtual String Name { get; set; }
    public virtual Double MeanRadius { get; set; }
    public virtual Double Volume { get; set; }
    public virtual Double Mass { get; set; }

    public virtual String CreatedBy { get; set; }
    public virtual DateTime CreateDate { get; set; }
    public virtual String ModifiedBy { get; set; }
    public virtual DateTime ModifyDate { get; set; }
}

and its mapping file:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="AuditWithNH"
                   namespace="AuditWithNH">
  <class name="Planet" table="Planet" 
  mutable="true" dynamic-update="true">
    <id name="Id" column="PlanetId" 
    type="Int32" unsaved-value="0">
      <generator class="native" />
    </id>

    <!-- Planet characteristics -->
    <property name="Name" 
    column="PlanetName" type="String" 
		length="256" not-null="true" />
    <property name="MeanRadius" 
    column="MeanRadius" type="Double" not-null="true" />
    <property name="Volume" 
    column="Volume" type="Double" not-null="true" />
    <property name="Mass" 
    column="Mass" type="Double" not-null="true" />

    <!--<span class="code-comment"> Changelog -->
    <property name="ModifiedBy" 
    column="ModifiedBy" type="String" 
		length="30" not-null="true" />
    <property name="ModifyDate" 
    column="ModifyDate" type="DateTime" not-null="true" />
    <property name="CreatedBy" 
    column="CreatedBy" type="String" 
		length="30" not-null="true" />
    <property name="CreateDate" 
    column="CreateDate" type="DateTime" not-null="true" />
  </class>
</hibernate-mapping></span>

You may notice that the Planet class implements IAuditable interface. This interface informs NHibernate session interceptor that this object has changelog fields that need to be filled:

using System;

namespace TnE.Shared.Audit
{
    /// <summary>
    /// This is a base interface for types
    /// that should be monitored by NHibernate.
    /// 
    /// If mapped type is saved to the database
    /// NHibernate Session Interceptor will check
    /// whether it implements IAuditable. And if it 
    /// does it will fill all interface fields with 
    /// correct values. 
    /// </summary>
    public interface IAuditable
    {
        /// <summary>
        /// Gets or sets the information about
        /// who created the record (it's set
        /// only when the object is saved to the
        /// database).
        /// </summary>
        /// <value>The created by.</value>
        String CreatedBy { get; set; }

        /// <summary>
        /// Gets or sets the creation date (set 
        /// only when the object is saved to the
        /// database for the first time).
        /// </summary>
        /// <value>The create date.</value>
        DateTime CreateDate { get; set; }

        /// <summary>
        /// Gets or sets the user name of the 
        /// currently logged user which modifies
        /// a given record.
        /// </summary>
        /// <value>The modified by.</value>
        String ModifiedBy { get; set; }

        /// <summary>
        /// Gets or sets the date of the modification.
        /// </summary>
        /// <value>The modify date.</value>
        DateTime ModifyDate { get; set; }
    }
}

Finally, let's define the AuditSessionInterceptor. As stated before, this interceptor will check whether object (being persisted) implements IAuditable interface and if it does, the interceptor will fill the changelog fields. In the code below, I assume that LoggedUser property of the interceptor will be set after successful user authentication by authentication module. Usually, NHibernate session (so also the session interceptor) has the same lifetime as the HTTP request so each user will have its own session interceptor - so the LoggedProperty may be set once and will be valid till the finalization of the interceptor. However, if you implement different NHibernate session management, you may want to retrieve the logged user name in each of the interceptor methods.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using NHibernate;

namespace AuditWithNH
{
    /// <summary>
    /// Session interceptor used to update audit fields 
    /// in monitored database objects. 
    /// </summary>
    public class AuditSessionInterceptor : EmptyInterceptor
    {
        private String loggedUser;

        /// <summary>
        /// Gets or sets the logged user.
        /// </summary>
        /// <value>The logged user.</value>
        public String LoggedUser
        {
            get { return loggedUser; }
            set { this.loggedUser = value; }
        }

        private void FillUpdateFields(Object[] state, String[] propertyNames, 
                                      Dictionary<String, Object> fieldVals)
        {
            for (Int32 i = 0; i < propertyNames.Length; i++)
            {
                if (fieldVals.ContainsKey(propertyNames[i]))
                {
                    state[i] = fieldVals[propertyNames[i]];
                }
            }
        }

        /// <summary cref="NHibernate.IInterceptor.OnSave">
        /// </summary>
        public override bool OnSave(object entity, object id, object[] state, 
                                    string[] propertyNames, NHibernate.Type.IType[] types)
        {
            Debug.Assert(loggedUser != null, "loggedUser != null");
            if (loggedUser != null)
            {
                if (entity is IAuditable)
                {
                    var fv = new Dictionary<String, Object>() 
                { 
                    { "CreatedBy" , loggedUser },
                    { "CreateDate", DateTime.Now },
                    { "ModifiedBy", loggedUser }, 
                    { "ModifyDate", DateTime.Now } 
                };
                    FillUpdateFields(state, propertyNames, fv);
                }
            }
            return true;
        }

        /// <summary cref="NHibernate.IInterceptor.OnFlushDirty">
        /// </summary>
        public override bool OnFlushDirty
			(object entity, object id, object[] currentState, 
                             object[] previousState, string[] propertyNames, 
                             NHibernate.Type.IType[] types)
        {
            Debug.Assert(loggedUser != null, "loggedUser != null");
            if (loggedUser != null)
            {
                if (entity is IAuditable)
                {
                    var fv = new Dictionary<String, Object>() 
                { 
                    { "ModifiedBy", loggedUser }, 
                    { "ModifyDate", DateTime.Now } 
                };
                    FillUpdateFields(currentState, propertyNames, fv);
                }
            }
            return true;
        }
    }
}