Disclaimer: A possible 'enhancement request' for Hibernate came up in a discussion the other day, but I have not had time to think through all the issues to decide if it made sense or not. So, rather than clutter JIRA with a nebulous idea, I thought it woud be more appropriate to post it to a forum for discussion. Let me know if this is the wrong way to go about it.

-------------------------------

In the December issue of Dr. Dobb's Journal, Todd Schraml has an article entitled Table Patterns and Changing Data: Dealing with History in Relational Databases. At first glance, he seems to have captured each of the basic database 'patterns' used to provide history/audit trails within business applications, along with some useful heuristics for when to use what. For example, he compares and contrasts the common 'functional history' table with 'row-level audit' and 'column-level audit' patterns.

My experience is that this is an area of database design that is opaque to most Java developers (unless, of course, they come from a DBA background). Heck, even most BA's I've worked with seldom think to ask about auditability requirements unless you point them in that direction. That's a problem, especially in the US where SOX compliance is becoming a huge issue. That got me thinking...

Automating the application of these 'database design patterns' via Hibernate would go a long way to solving the problem. It would raise awareness among developers of the issues and solutions. It would also ease resistence to discussing or implementing audit requirements because automated application of the most common patterns by Hibernate would make the implementation trivial for the application dev.

As a straw man, let's assume you are going the annotations route. Imagine applying an attribute something like the following:

@Auditable(type = "history table")
public class Foo {
...
}

And this results in Hibernate creating an associated history table for the table structure it creates to store 'Foo'.

Now, I'm aware that Hibernate will allow you to implement audit logging using interceptors (see Hibernate in Action pp. 340-347 for details) but 'audit logging' is not the same as an 'auditable table structure', although they are used for related purposes. 'Hard' auditability requirements (e.g. those imposed by SOX) are likely to drive databse design toward the patterns described by Todd Schraml rather than the use of the (simpler to create, but harder to report against) audit logs. Or so I would argue.

If true, my (perhaps naive) perception is that these designs woud be labourious to create/use with 'standard' Hibernate usage patterns. Is this true, or is there some dirt simple implementation approach I'm just not aware of yet? If it is true, this seems to me to be an area that could be effectively automated by Hibernate, shielding the developer from the details of the necessary changes to the DDL and the insert and update semantics.

Thoughts?

As it happens, I'm a Java developer fitting the description. I too read the Dr. Dobbs article and put it in the back of my head for a "to-do" on my current hibernate-enabled project.

I'm at the point now where the history/audit functiionality needs to be implemented and came across this post while searching for a solution. I'd love it if Hibernate "just worked" for auditing, but I suspect that's a long way off...

So, has anyone implemented history/audit tables w/ Hibernate? Any recommendations as to how to procede?

thanks,

-don.

The Wiki has examples as does HIA. Then again you could just let the database do it, eg, use triggers .

David,

I assume you're referring to:
Audit Logging -- http://www.hibernate.org/318.html and
History Interceptor -- http://www.hibernate.org/195.html

which discuss how to create an audit log, but I've found nothing that speaks to how to use the log. I understand how to use triggers or Hibernate's Interceptor to stuff the needed data into the DB, but writing a Hibernate-based app to traverse it isn't so clear.

Also, they both refer to a single pattern (that of the audit log), wheras the article in question (and a decent overview by Martin Fowler at http://martinfowler.com/ap2/timeNarrative.html), give other "audit-like" patterns. I'm specifically interested in Fowler's "Snapshot" pattern. (though I'm sure other folks need the other patterns here) I'm curious if anyone has been able to find a slick way of getting Hibernate to help.

thanks all,

-don.

We also need good history tables: to store all changes in transaction, and whats more diffucult to retrieve the changes done during transaction and in proper order - and it could be hundrets of objects, main-collection-collection, references and inheritance together.
Basicaly any DB depended stuff is rejected due to our design. So we try to do it purely in Hibernate using events. Seems to be hard stuff to write, but now looks like we are close to succeed.
btw we had 5 years ago, a c++ db persistance which had history implemented in the box, and we were slightly dispointed, that such feature is not in Java persistence frameworks.

ChristopherRM,
I agree that a user should be able to mark an object Auditable, and I have a suspicion that this feature could be implemented with a cleverly written custom persister, and therefore not requiring much in the way of changes to the Hibernate core.

Let me just go on the record throwing my vote in for an implementation of a simple row-level audit.