Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Changelog for domain objects

, 3 May 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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>

    <!--<span class="code-comment"> Planet characteristics --></span>
    <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 --></span>
    <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>

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
{
    /// <span class="code-SummaryComment"><summary>
</span>    /// 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. 
    /// <span class="code-SummaryComment"></summary>
</span>    public interface IAuditable
    {
        /// <span class="code-SummaryComment"><summary>
</span>        /// Gets or sets the information about
        /// who created the record (it's set
        /// only when the object is saved to the
        /// database).
        /// <span class="code-SummaryComment"></summary>
</span>        /// <span class="code-SummaryComment"><value>The created by.</value>
</span>        String CreatedBy { get; set; }

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

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

        /// <span class="code-SummaryComment"><summary>
</span>        /// Gets or sets the date of the modification.
        /// <span class="code-SummaryComment"></summary>
</span>        /// <span class="code-SummaryComment"><value>The modify date.</value>
</span>        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
{
    /// <span class="code-SummaryComment"><summary>
</span>    /// Session interceptor used to update audit fields 
    /// in monitored database objects. 
    /// <span class="code-SummaryComment"></summary>
</span>    public class AuditSessionInterceptor : EmptyInterceptor
    {
        private String loggedUser;

        /// <span class="code-SummaryComment"><summary>
</span>        /// Gets or sets the logged user.
        /// <span class="code-SummaryComment"></summary>
</span>        /// <span class="code-SummaryComment"><value>The logged user.</value>
</span>        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]];
                }
            }
        }

        /// <span class="code-SummaryComment"><summary cref="NHibernate.IInterceptor.OnSave">
</span>        /// <span class="code-SummaryComment"></summary>
</span>        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;
        }

        /// <span class="code-SummaryComment"><summary cref="NHibernate.IInterceptor.OnFlushDirty">
</span>        /// <span class="code-SummaryComment"></summary>
</span>        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;
        }
    }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sebastian Solnica
Software Developer (Senior)
Poland Poland
Interested in tracing, debugging and performance tuning of the .NET applications (especially ASP.NET).
 
If you find this article interesting, maybe you would like to pay me a visit: http://lowleveldesign.wordpress.com? Smile | :)

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 3 May 2010
Article Copyright 2010 by Sebastian Solnica
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid