As a former owner and CTO of a webhosting company our support staff and our customers often made changes to DNS and email settings in our CFML customer portal, but this can be a dangerous affair if you don’t know what your are doing . Of course auditing can be done in the database systems, but in that case you’ll often miss specific information such as user ID’s, company names, ip numbers and so on. In other projects a detailed log of our database changes could also be very useful, so in this post I’ll discuss how you can log your changes very easily using Logbox and interceptors in a coldbox application.

So what do we want? A log entry on every single database insert, update and delete. There are many different ways to interact with your database, and we will discuss logging solutions for several scenario’s like

  • quick ORM applications
  • cborm based applications
  • custom applications using base services and a base entity
  • applications using qb
  • applications using cfquery and queryExecute

Quick ORM applications

I’ll start with an application based on quick ORM, because this is the simplest way to log your changes (although cborm is almost as easy). Quick orm has some nice interception points such as

  • quickPreInsert and quickPostInsert.
  • quickPreUpdate and quickPostUpdate
  • quickPreDelete and quickPostDelete
  • quickPreSave and quickPostSave

The pre- and postSave interception points were less interesting for us, because they will be triggered on both inserts and updates. We are especially interested in the values AFTER inserts and updates, and you could argue if you need the values before or after a delete. We choose before, because we think there shouldn’t be any data left after a delete, but quick just shows the same data as before the delete. A simular warning for the preUpdate: this shows the data of a populated but not yet saved object, so if you validated your objects correctly data for preUpdate and postUpdate are the same, but we miss the old data.
So we need postInsert, postUpdate and preDelete. We create an interceptor QuickOrmInterceptor which we register in coldbox.cfc

interceptors = [
//....	
{
		class      : "interceptors.QuickOrmInterceptor",
		properties : {}
 },

We also need an audit logger, so we create a named category in logbox:

logbox: {
//.. appenders	
  categories : {
     "audit" : {
		levelMax  : "INFO",
		appenders : "extendedDBAppender"
     },
// ..etc

As you can see I used an extendedDBappender, which can log some extra info automatically. You don’t have to do that, you could also use some standard appender, but if you need some filtering on usernames, ipnumbers, company or other fields, a modified appender is your friend.

This brings us to the interceptor:

component{
  // an injected security proxy component which retrieves username and id
  property name="psec" inject="provider:SecurityProxy";
  // the logbox auditLogger
  property name="auditLogger" inject="logbox:logger:audit";

  void function configure(){
    // no special configuration here
  }
	
  void function quickPostInsert( event, data, buffer, rc, prc ){
    logAction("PostInsert", arguments.data,arguments.rc, arguments.prc);
  }
  void function quickPostUpdate( event, data, buffer, rc, prc ){
    logAction("PostUpdate", arguments.data,arguments.rc, arguments.prc);
  }
  void function quickPreDelete( event, data, buffer, rc, prc ){
    logAction("PreDelete", arguments.data,arguments.rc, arguments.prc);
  }

  private function logAction(required string Action, data, rc, prc ){
    try {
      var myData = { "entity": data.Entity.getMemento() };
	  var entityName = data.Entity.entityName();
	  var entityId = data.Entity.getId() ?: "- no Id -";
	  mydata["rc"] = rc;
    } catch(any e){
       // ignore error for getMemenot
      var myData={ "error catched": e};
	  var entityName = "unknown";
	  var entityId = "unknown";
    }
	auditLogger.info( "#arguments.action#  #entityName# with id #entityId#. User: #psec.getUsername()# (Id:#psec.getUserIdAsString()#)",  myData);
	}
}

So what’s happening here in this interceptor? We need to inject some logger, and we define the quickPostInsert, quickPostUpdate and quickPreDelete interceptions, which all do nothing more but calling some logAction. We inject some security proxy component which can retrieve a username and userId from the authenticated user. In this example I injected this in my interceptor and used the info directly in the log message. If I had used my extendedDBappender this information was already available in the appender in separate fields, this is just another strategy for logging. I can also add the rc to the data which I want to log. Just make sure your rc can be serialized.
Quick also has some interceptors for loading your data so for stricter auditing you could follow that process as well. The possibilities are endless. Every piece of data which can be retrieved in coldbox can be inserted in the data part of your log entry, or you can do that in a customized appender if you want to separate this information into distinct log entry fields.

cbOrm applications

CbOrm applications are quite simular to quick with respect to the interceptors. You only have to take an extra step and enable eventhandling in your ormsettings in Application.cfc and point the eventhandler to cborm.models.EventHandler which acts as a bridge between hibernate events and the corresponding coldbox interceptors .
Yesterday I was creating some cborm demo application for orm events, but unfortunately there are some issues in recent coldbox version when loading the cborm event handler. This brings me to the conclusion that not many people are using Orm events… although they are very powerful. I ‘ll leave the cbOrm demo for a future post now.

Conclusion

Logbox combined with quick events are a powerful combination to follow your database changes. cborm could do the same, once coldbox has been fixed. Does this mean that we have to use cborm or quick for this?
The answer is no, but it takes a little more work. In one of my applications I create my own data access objects, and create my own interception point on inserts, updates and deletes. If you are just using qb or cfquery/queryExcute your options are more limited, but with some creativity you can still see your changes. Both topics will be addressed in the next posts on logging your db changes.