Very often, one of the requirements that an application I'm working on has is that it needs an audit trail. In this post I'm going to go through a technique that will build the foundation which will allow you to add record-based audit trails to your application.
We'll look at adding application user information and time stamps to the data using inheritance, as well as overriding some methods in the ActiveRecord base classes to automatically populate these values.
Download - Darkside.AuditTrail.Part1.zip
First off, let's define a mini-spec on what our "system" should do with respect to the auditing:
- All records added and updated should be recorded, including who edited the record and when
- Records in the database should never be deleted, only flagged for deletion
Based on the requirements above, I can immediately see that our domain classes are going to have common fields; This makes class inheritance a prime candidate for solving this problem. Our common fields are:
- IsDeleted (bool) - This will be our flag for deleted records
- EditedById (Guid) - A FK to our user table, indicating the person that edited this record
- EditedDateTime (DateTime) - The date and time the record was last edited
Its worth noting that if your decide to do record locking in your application, you should consider adding an additional field that has the [Version] attribute to this base class as well
I've outlined the beginning of base class in the code below. Something worth pointing out is the fact that I've decided to add the property Id to the class and make it abstract; This is a personal preference only - I like to have the primary key of my objects called Id.
Expand Code
public abstract partial class ObjectBase<T> : ActiveRecordBase <T> where T: ObjectBase<T>
{
#region Field Name Constants
public const string IsDeletedFieldName = "IsDeleted";
public const string EditedByIdFieldName = "EditedById";
public const string EditedDateTimeFieldName = "EditedDateTime";
#endregion
public abstract Guid Id { get; set; }
[Property(Column = EditedByIdFieldName)]
public bool IsDeleted { get; set; }
[Property(Column = EditedByIdFieldName)]
public Guid EditedById { get; set; }
[Property(Column=EditedDateTimeFieldName, ColumnType = "TimeStamp")]
public DateTime EditedDateTime { get; set; }
}
Now, we can continue defining our domain objects, but inherit from our own base class.
For this sample project, I'm going to have 3 domain objects. They will be:
- ApplicationUser - The user that will log on to the application and that we need to keep track of to allow the EditedId field to be updated.
- Customer - A customer detail class
- Address - An address class, of which will have a many-to-one relationship to Customer
Lets start with the AcitveRecord attribute on the ApplicationUser class.
[ActiveRecord(Table = "ApplicationUser", Where="IsDeleted=0")]
Notice that I've include a value for the property Where, which allows you to add a SQL condition which is applied when you fetch objects. This helps our classes conform to the "Records in the database should never be deleted..." requirement. This will ensure that records returned always have the IsDeleted property equal to 0.
In addition to the mapping in this class, I've added another file (partial class) for ApplicationUser that implements IPrincipal and IIdentity.
Expand Code
public partial class ApplicationUser :IPrincipal , IIdentity
{
#region IPrincipal Members
public IIdentity Identity
{
get { return (this); }
}
public bool IsInRole(string role)
{
return (true);
}
#endregion
#region IIdentity Members
public string AuthenticationType
{
get { return ("ApplicationAuthentication"); }
}
public bool IsAuthenticated { get; set; }
public string Name
{
get { return (UserName); }
}
#endregion
}
I prefer using a class that implements these two interfaces for logging in. I digress slightly from the point of this post, but a simple implementation of an Authenticate method could look something like this:
Expand Code
public static void AuthenticateUser(string userName, string password)
{
var userNameCriterion = Restrictions.Eq("UserName", userName);
var passwordCriterion = Restrictions.Eq("Password", password);
var user = FindOne(userNameCriterion, passwordCriterion);
if (user != null)
{
user.IsAuthenticated = true;
}
else
{
user =new ApplicationUser();
}
Thread.CurrentPrincipal = user;
}
This way, you have access to the currently logged in user throughout your application, and is accessible from Thread.CurrentPrincipal. And it fits in well with how I would like to automatically update certain fields in our base class :)
Now that we have that out of the way, lets revisit some additional code in the base class. The code below overrides some ActiveRecord base methods, making sure the necessary fields we require are updated, as well as making sure we don't allow for data to be deleted.
Expand Code
public abstract partial class ObjectBase<T>: ActiveRecordBase<T> where T : ObjectBase<T>
{
//Update the EditedById to the currently logged on user, and the EditedDateTime field to DateTime.Now
protected override bool BeforeSave(IDictionary state)
{
base.BeforeSave(state);
UpdateDetails(state);
return (true);
}
protected override bool OnFlushDirty(object id, IDictionary previousState, IDictionary currentState, NHibernate.Type.IType[] types)
{
base.OnFlushDirty(id, previousState, currentState, types);
UpdateDetails(currentState);
return (true);
}
//Update the EditedById to the currently logged on user, and the EditedDateTime field to DateTime.Now
private static bool UpdateDetails(IDictionary state)
{
state[EditedDateTimeFieldName] = DateTime.Now;
if (Thread.CurrentPrincipal is ApplicationUser)
state[EditedByIdFieldName] = (Thread.CurrentPrincipal as ApplicationUser).Id;
else
state[EditedByIdFieldName] = Guid.Empty;
return (true);
}
//Make sure that we actually do a Save()
public override void Delete()
{
IsDeleted = true;
Save();
}
//Make sure that we actually do a SaveAndFlush()
public override void DeleteAndFlush()
{
IsDeleted = true;
SaveAndFlush();
}
}
Having a quick look at our customer class, you'll notice that you no longer inherit from ActiveRecordBase or ActiveRecordBase<T>, but our new ObjectBase<T> class. You'll also notice the [HasMany] attribute also has a Where property set. This behaves the same way as mentioned above - it adds a SQL condition when data is fetched.
Expand Code
[ActiveRecord(Table = "Customer", Where="IsDeleted=0")]
public partial class Customer : ObjectBase<Customer>
{
[PrimaryKey(Column = "CustomerId", Generator = PrimaryKeyType.GuidComb)]
public override Guid Id { get; set; }
[Property(Column = "FirstName", Length = 100, NotNull = true)]
public string FirstName { get; set; }
[Property(Column = "LastName", Length = 100, NotNull = true)]
public string LastName { get; set; }
[Property(Column = "Age", NotNull = true)]
public int Age { get; set; }
[HasMany(MapType = typeof(Address), Where = "IsDeleted=0")]
public IList<Address> Addresses { get; set; }
}
The sample project includes a console app that you can run. You'll need to modify the connection string as well as create yourself an empty database. The schema is created by the demo.
You'll notice that you can use the classes without having to manually set the EditedBy and EditedDateTime properties whenver you Save/Update/Flush.
The only code in the sample app that I think may require more explanation is the TestDelete() method.
Expand Code
private static void TestDelete()
{
Console.WriteLine("Testing delete on a fetched object...");
Customer customer1 = ActiveRecordBase<Customer>.FindFirst(Restrictions.Eq("FirstName", "Marge"));
customer1.Delete();
Customer customer2 = ActiveRecordBase<Customer>.TryFind(customer1.Id);
if (customer2 == null) //Successfully deleted, Find will only bring back entries where IsDeleted=0
Console.WriteLine("Object succesfully \"deleted\".");
else
Console.WriteLine("Delete failed.");
}
You'll need to dive into the database and check the table directly to see that the IsDeleted field is set to 1 to confirm that it wasn't deleted if you're not keen on believing the test :).
In the next post, I'll demonstrate using SQL triggers to take advantage of the information we've added to the new base class, allow you to implement a really effective row-based audit trail.