JasonDentler.com

In my December 23rd post, I showed you how to generate audit tables and triggers from your NHibernate mappings. Since then, I had an amazing conversation with Fabio Maulo, NHibernate project owner, where among other things, he showed me a much better way to accomplish the same task.

In my last post, we used the NHibernate mapping as source data to create our script by hand. With this version, we’re going to take it a step further. When you add an IAuxilaryDatabaseObject to the mappings, it will be included in the ddl output of SchemaExport. These have no effect outside of SchemaExport. It’s a simple way to include additional items in your database setup scripts.

In our main program, we set up some additional columns to hold our audit data – user, timestamp, and action. These columns will be added to each audit table. Then, we use TriggerStrategy to add auxiliary database objects to our configuration.

        static void AddAuditing(Configuration cfg)
        {
            var columns = new List<auditcolumn>();
            columns.Add(new AuditColumn() {
                Name = "AuditUser",
                SqlType = "sysname", 
                IsNullable = false, 
                ValueFunction = delegate(TriggerActions actions) 
                {
                    return "system_user";
                }
            });

            columns.Add(new AuditColumn()
            {
                Name = "AuditTimestamp",
                Value = new SimpleValue() { TypeName = NHibernate.NHibernateUtil.DateTime.Name },
                IsNullable = false,
                IncludeInPrimaryKey = true,
                ValueFunction = delegate(TriggerActions actions)
                {
                    return "getdate()";
                }
            });

            columns.Add(new AuditColumn()
            {
                Name = "AuditOperation",
                Value = new SimpleValue() { TypeName = NHibernate.NHibernateUtil.AnsiChar.Name },
                Length = 1,
                IsNullable = false,
                ValueFunction = delegate(TriggerActions actions)
                {
                    switch (actions)
                    {
                        case TriggerActions.Insert:
                            return "'I'";
                        case TriggerActions.Update:
                            return "'U'";
                        case TriggerActions.Delete:
                            return "'D'";
                        default:
                            throw new ApplicationException("Triggers handling multiple event types (INSERT, UPDATE, DELETE) are unsupported. ");
                    }
                }
            });
                

            new NHibernate.Audit.TriggerStrategy(
                new NHibernate.Audit.AuditTableBuilder(),
                new NHibernate.Audit.MsSqlTriggerBuilder(), 
                columns).Configure(cfg);
	}

Trigger strategy simply hands off the work of audit table builder and trigger builder.

Audit table builder creates an audit table object for each table already in your mapping. It is also responsible for calculating the name of each audit table. Yes, the naming should probably be pulled in to a separate class, but oh well.

Audit table uses NHibernate's table object internally to create a duplicate of each table, plus the additional audit columns, then exposes the drop/create scripts for this table object as an IAuxilaryDatabaseObject. I've ommitted some of the name-related static below.

using System.Collections.Generic;
using NHibernate.Mapping;

namespace NHibernate.Mapping
{
    
    public class AuditTable : AbstractAuxiliaryDatabaseObject 
    {

        public AuditTable(string Name, Table DataTable, IEnumerable<AuditColumn> AuditColumns)
        {
            dataTable = DataTable;
            auditTable = new Table();
            auditColumns = AuditColumns;
            BuildAuditTable(Name);
        }

        protected readonly Table dataTable;
        protected readonly Table auditTable;
        protected readonly IEnumerable<AuditColumn> auditColumns;

        protected virtual void BuildAuditTable(string Name)
        {
            auditTable.Catalog = dataTable.Catalog;
            auditTable.Schema = dataTable.GetQuotedSchema();
            auditTable.Name = Name;

            foreach (Column column in dataTable.ColumnIterator)
                CopyColumn(column);

            CopyPrimaryKey(dataTable);

            AddAuditColumns();
        }

        protected virtual void CopyColumn(Column column)
        {
            auditTable.AddColumn((Column)column.Clone());
        }

        protected virtual void CopyPrimaryKey(Table dataTable)
        {
            if (dataTable.PrimaryKey != null)
            {
                var pk = new PrimaryKey();
                auditTable.PrimaryKey = pk;
                foreach (Column c in dataTable.PrimaryKey.ColumnIterator)
                {
                    Column auditTableColumn = auditTable.ColumnIterator.WithName(c.Name);
                    pk.AddColumn(auditTableColumn);
                }
            }
            else
            {
                throw new System.NotSupportedException("Tables without primary keys are not supported.");
            }
        }

        protected virtual void AddAuditColumns()
        {
            foreach (AuditColumn source in auditColumns)
            {
                Column dest = (Column)source.Clone();
                auditTable.AddColumn(dest);
                if (source.IncludeInPrimaryKey)
                    auditTable.PrimaryKey.AddColumn(dest);
            }
        }


        public override string SqlCreateString(NHibernate.Dialect.Dialect dialect, NHibernate.Engine.IMapping p, string defaultCatalog, string defaultSchema)
        {
            return auditTable.SqlCreateString(dialect, p, defaultCatalog, defaultSchema);
        }

        public override string SqlDropString(NHibernate.Dialect.Dialect dialect, string defaultCatalog, string defaultSchema)
        {
            return auditTable.SqlDropString(dialect, defaultCatalog, defaultSchema);
        }
    }

}

That handles our audit tables. Now let’s look at the triggers.

Abstract trigger builder builds insert, update, and delete triggers for each table. You should override BuildTriggerBody to customize the body of your trigger. I’ve created MsSqlTriggerBuilder to output my preferred trigger code.

NHibernate dialects have no concept of triggers and lack the required strings to build a DBMS-specific CREATE TRIGGER statements. Instead, you specify a trigger type inherited from AbstractTrigger specific to your database to build the CREATE TRIGGER statement. I’ve included MsSqlTrigger.

That’s all folks. You can grab the code from my SVN repository at http://basiclyeverything.googlecode.com/svn/trunk/AuditLogExample/

Since I’m still using the system_user function to get the current user, you will still need to impersonate the user all the way down to the database level. I’ve found a way around this, and I will show it to you next time. Until then, Happy New Year.