If you are looking for a framework for auditing the versions and changes of your hibernate objects, you might probably be satisfied with Envers (http://www.jboss.org/envers/), a solution based on hibernate.
In this blog entry I want to give you an impression of an alternative solution by agimatec, that is slightly different. It is the solution we are using in our products for more than two years and with which we are satisfied. It would be interesting to hear your options on this.
The concept
Database:
We generate history tables for each table for which we need auditing. We generate database triggers (oracle or postgres) to automatically insert data in the history tables when entities are stored/deleted.
We do not store duplicate data, which means that our history tables contain only OLD data. All current data is ONLY stored in the application’s tables (usually mapped by hibernate/Ejb3).
Configuration:
We have a XML configuration, that contains the table names for which we want to enable our history solution. This configuration lets you specify:
- name of history table (or a default name will be used)
- which columns to exclude from history (default is that all columns are historised)
- optionally you can turn off history for insert and/or update, change trigger names etc.
The XML configuration controls the tables and triggers generated by freemarker templates.
Features:
It is not only important to version the changes, but also to store, the timestamp of a change and some context information about the change (e.g. who is the actor, which application, from which sessionID etc.). Any context information can be associated with a version record by storing a contextID in the history table’s rows.
This is also a way to separate changes from user-specific data, which might be a judical requirement. An extended Hibernate event handler stores the contextID into the database session by calling a stored procedure, so that the history triggers and access the contextID to store it together with the history data.
Each history table has additional columns:
- HIST_TIME (timestamp of change)
- HIST_CONTEXTID (ID of context providing additional information)
- HIST_TYPE (to distinguish INSERT, UPDATE and DELETION)
A simple java API exists to query historical data. It returns the same classes as you use for your hibernate entities, but it materializes the objects itself. Hibernate does not know anything about the history tables or mappings.
The benefits
There are some differences between the solution provided by Envers and agimatec:
- Envers needs a global _revision table, agimatec’s history rows are identified by the primary key and a version number (@Version) incremented from 1 for each entity/no global _revision
- You can change data with any SQL tool.Historisation does not rely on hibernate as the only API to change the database.
- Whenever you increment a row’s version, the history triggers will automatically write historical data. By this way, you can use migration scripts that can control whether history data will be stored.
- Envers stores current data in history tables, which makes it a bit easier to query the data, but with means redundancy (and potential data inconsistencies).
- Both framework are integrated with Hibernate by extending the Hibernate Events (to provide context information).
Examples
History configuration
<historyConfig>
<tableConfig>
<tableName>booking</tableName>
</tableConfig>
<tableConfig>
<tableName>customer</tableName>
<historyTable>h_cust</historyTable>
<insertTrigger>TR_I_cust</insertTrigger>
<updateTrigger>TR_U_cust</updateTrigger>
</tableConfig>
Generated tables
CREATE TABLE H_JOURNAL (
ID INTEGER NOT NULL,
HIST_TABLE VARCHAR(50) NOT NULL,
HIST_TIME TIMESTAMP NOT NULL,
HIST_CONTEXTID VARCHAR(40),
CONSTRAINT H_JOURNAL_PK PRIMARY KEY (ID, HIST_TABLE)
);
CREATE TABLE H_booking (
VERSION INTEGER NOT NULL,
booking_id INTEGER NOT NULL,
price NUMBER,
HIST_TIME TIMESTAMP NOT NULL,
HIST_CONTEXTID VARCHAR(40),
HIST_TYPE CHAR(1) NOT NULL,
CONSTRAINT H_booking_PK PRIMARY KEY (VERSION, booking_id)
);
CREATE TABLE H_cust (
VERSION INTEGER NOT NULL,
cust_id INTEGER NOT NULL,
first_name VARCHAR2(40),
last_name VARCHAR2(40),
HIST_TIME TIMESTAMP NOT NULL,
HIST_CONTEXTID VARCHAR(40),
HIST_TYPE CHAR(1) NOT NULL,
CONSTRAINT H_cust_PK PRIMARY KEY (VERSION, card_id)
);
Generated triggers
CREATE OR REPLACE TRIGGER TR_I_cust
AFTER INSERT ON customer
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
setTATime();
INSERT INTO H_JOURNAL (ID, HIST_TIME, HIST_CONTEXTID, HIST_TABLE)
select :NEW.card_id, h.ts, h_session.getContextId, 'customer' from h_tatime h;
END;
/
CREATE OR REPLACE TRIGGER TR_U_cust
AFTER DELETE OR UPDATE
ON customer
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF(DELETING) THEN
setTATime();
INSERT INTO H_cust (
cust_id,
version,
first_name,
last_name,
HIST_TIME, HIST_CONTEXTID, HIST_TYPE)
select
:OLD.cust_id,
:OLD.version,
:OLD.first_name,
:OLD.last_name,
h.ts, h_session.getContextId, 'D' from h_tatime h;
ELSIF (UPDATING AND :OLD.VERSION != :NEW.VERSION) THEN
setTATime();
INSERT INTO H_cust (
cust_id,
version,
first_name,
last_name,
HIST_TIME, HIST_CONTEXTID, HIST_TYPE)
select
:OLD.cust_id,
:OLD.version,
:OLD.first_name,
:OLD.last_name,
h.ts, h_session.getContextId, 'U' from h_tatime h;
END IF;
END;
/
Java API
The implementation of class HibernateHistoryReader was rather complex, because of the need to resolve the relationships through the current tables and the history tables. All changes done inside a single transaction are joined together by using a timestamp in HIST_TIME that is unique inside the running database transaction.
Here is interface HistoryReader:
interface HistoryReader {
void open(EntityManager entityManager);
void close();
List<EntityRevision> getRevisions(Class entityClass, Object primaryKey, Timeframe timeframe);
EntityRevision getRevision(Class entityClass, Object primaryKey, int version);
Map<Object, List<EntityRevision>> getChildrenRevisions(Class parentEntityClass,EntityRevision parentRevision,String relationship);
<E> HistoryEntity<E> loadEntity(Class<E> entityClass, Object primaryKey,Timestamp time);
<E> List<HistoryEntity<E>> loadChildren(Class parentEntityClass, String relationship,
Object parentPrimaryKey, Timestamp time);
}
Where class HistoryEntity contains the entity instance for a given point of time and some additional information, such as the contextID and the foreignKey values to load some to-one-references.
If you are interested in more details contact us.
Using portlets, you are able to develop tiny little applications that are suitable for a small subset of the actual functionality your customer wants. By combining them, they make it possible to create powerful but still modular applications that meet exactly the use cases your customer needs help with.