Hi,
I’m looking for some pointers and advice on creating an audit trail of CUD (Create/Update/Delete) database operations.
I’ve looked at LogBox and have heard that WireBox can use LogBox but haven’t looked into that yet and of course there are the ORM events that I could tap into.
So the question is, which way to go and are there any resources out there that could guide me?
All thoughts welcome…
Richard
Hi Richard,
I have a similar scenario here - used as a versioning system on a given object as well as an audit. It’s based on Transfer ORM rather than native CF stuff though.
Principle is that is uses the database events, postUpdate etc and a single table that holds audit records for all objects.
After the object is saved, a snapshot of it’s properties is taken as a JSON momento, this along with the objects class, primary key, audit date and user are all saved into the audit table.
The objects that I want included into the audit extend a base class, this base class contains a bunch of methods which allow me to access details on previous versions, rollback and things like that.
Thanks,
Robert
Perfect candidate to use AOP as well
Luis Majano
CEO
Ortus Solutions, Corp
www.ortussolutions.com
ColdBox Platform: http://www.coldbox.org
Linked In: http://www.linkedin.com/pub/3/731/483
Social: twitter.com/ortussolutions | twitter.com/coldbox | twitter.com/lmajano
Hi Robert,
Thanks for that.
The last project for this client was written in CFWheels and I managed to implement db audit trail functionality pretty easily. This new project is being written in ColdBox but I have to use the existing audit db schema so I’m bound to that in terms of the info to gather.
Luis, I’ve just finished watching your LogBox and WireBox Connection recordings (I was in the original audience but had forgotten everything!) and saw you explain AOP. I might need a bucket of wet sand for my fizzing brain but I think I understand how to approach this.
We shall see!
Thanks for your thoughts guys…
After some other distractions I’m now coming back on this requirement and have made some progress which I’d like to share plus I’ve hit an issue for which I’d like some help and guidance.
As I’m using ORM I have the following settings in my Application.cfc…
this.ormSettings.eventhandling = true; this.ormSettings.eventhandler = "model.ORMEventHandler";
…so, taking record updating as an example, in my model.ORMEventHandler.cfc I have a preUpdate(any submittedEntity, any persistedStruct) method. Within this method I determine what in the submitted entity has changed from the persisted entity (I can share this if anyone is interested) and assemble a struct of data that I want to record in my audit trail table.
Next I have created a custom LogBox AsyncDBAppender, referencing it in my Coldbox.cfc config, which accepts my audit data and inserts it into my audit trail table.
And all is good in the garden!
Except…
Getting LogBox injected into my model.ORMEventHandler component.
I tried…
property name="objLogbox" inject="logbox:logger:auditTrailLogger";
…but it didn’t work and I guess I get that as ColdBox can’t inject it as ColdBox hasn’t been instantiated when the EventHandler is called for the first time by the Application.cfc pseudo constructor.
I decided to take a look at the variables scope in the pseudo constructor region of the component and it said getController() existed but then I tried…
variables.objLogbox = variables.getController().getLogbox().getLogger("auditTrailLogger");
…that failed with…
Message: ColdBox Controller Not Found Detail: The coldbox main controller has not been initialized
…so I guess the pseudo constructor was lying.
The only way I can see that I can get LogBox is by calling it from within preUpdate() and any other method in the component like this…
var objLogbox = getLogger("auditTrailLogger");
If this is right, my only concern is how “expensive” calling getLogger() is in every method rather than referencing it once from the variables scope as I’m used to with injected properties.
Any opinions or differing approaches welcome.
Since the vent handler is called by ColdFusion we cannot do injection at startup. I suggest your approach. Getting a logger instance should be extremely fast. Since loggers are reused by category.
Also if you store locally in the cfc I am not sure ColdFusion persists that or recreates the event handler on a per request basis
Thanks, that’s what I thought about the order at startup.
Yes, getting the logger seems pretty swift. I’m guessing it come from memory?
It appears that ColdFusion does persist the event handler component at startup but ColdBox re-instantiates it on a fwreinit=1 so I’m faced with testing for it’s existence on every method call and then calling it if not found. Maybe easier/quicker just to call the logger from memory every time.