Populate envers revision tables with existing data from Hibernate Entities

Question

I'm adding envers to an existing hibernate entities. Everything is working smoothly so far as far as auditing, however querying is a different issue because the revision tables aren’t populated with the existing data. Has anyone else already solved this issue? Maybe you’ve found some way to populate the revision tables with the existing table? Just thought I’d ask, I'm sure others would find it useful.

Answer 1

We populated the initial data by running a series of raw SQL queries to simulate "inserting" all the existing entities as if they had just been created at the same time. For example:

insert into REVINFO(REV,REVTSTMP) values (1,1322687394907); 
-- this is the initial revision, with an arbitrary timestamp

insert into item_AUD(REV,REVTYPE,id,col1,col1) select 1,0,id,col1,col2 from item; 
-- this copies the relevant row data from the entity table to the audit table

Note that the REVTYPE value is 0 to indicate an insert (as opposed to a modification).

Answer 2

You'll have a problem in this category if you are using Envers ValidityAuditStrategy and have data which has been created other than with Envers enabled.

In our case (Hibernate 4.2.8.Final) a basic object update throws "Cannot update previous revision for entity and " (logged as [org.hibernate.AssertionFailure] HHH000099).

Took me a while to find this discussion/explanation so cross-posting:

ValidityAuditStrategy with no audit record

Answer 3

You don't need to.
AuditQuery allows you to get both RevisionEntity and data revision by :

AuditQuery query = getAuditReader().createQuery()
                .forRevisionsOfEntity(YourAuditedEntity.class, false, false);

This will construct a query which returns a list of Object [3]. Fisrt element is your data, the second is the revision entity and the third is the type of revision.

Answer 4

We have solved the issue of populating the audit logs with the existing data as follows:

SessionFactory defaultSessionFactory;

// special configured sessionfactory with envers audit listener + an interceptor 
// which flags all properties as dirty, even if they are not.
SessionFactory replicationSessionFactory;

// Entities must be retrieved with a different session factory, otherwise the 
// auditing tables are not updated. ( this might be because I did something 
// wrong, I don't know, but I know it works if you do it as described above. Feel
// free to improve )

FooDao fooDao = new FooDao();
fooDao.setSessionFactory( defaultSessionFactory );
List<Foo> all = fooDao.findAll();

// cleanup and close connection for fooDao here.
..

// Obtain a session from the replicationSessionFactory here eg.
Session session = replicationSessionFactory.getCurrentSession();

// replicate all data, overwrite data if en entry for that id already exists
// the trick is to let both session factories point to the SAME database.
// By updating the data in the existing db, the audit listener gets triggered,
// and inserts your "initial" data in the audit tables.
for( Foo foo: all ) {
    session.replicate( foo, ReplicationMode.OVERWRITE ); 
}     

The configuration of my data sources (via Spring):

<bean id="replicationDataSource" 
      class="org.apache.commons.dbcp.BasicDataSource" 
      destroy-method="close">
  <property name="driverClassName" value="org.postgresql.Driver"/>
  <property name="url" value=".."/>
  <property name="username" value=".."/>
  <property name="password" value=".."/>
  <aop:scoped-proxy proxy-target-class="true"/>
</bean>

<bean id="auditEventListener" 
      class="org.hibernate.envers.event.AuditEventListener"/>

<bean id="replicationSessionFactory"
      class="o.s.orm.hibernate3.annotation.AnnotationSessionFactoryBean">

  <property name="entityInterceptor">
    <bean class="com.foo.DirtyCheckByPassInterceptor"/>
  </property>

  <property name="dataSource" ref="replicationDataSource"/>
  <property name="packagesToScan">
    <list>
      <value>com.foo.**</value>
    </list>
  </property>

  <property name="hibernateProperties">
    <props>
      ..
      <prop key="org.hibernate.envers.audit_table_prefix">AUDIT_</prop>
      <prop key="org.hibernate.envers.audit_table_suffix"></prop>
    </props>
  </property>
  <property name="eventListeners">
    <map>
      <entry key="post-insert" value-ref="auditEventListener"/>
      <entry key="post-update" value-ref="auditEventListener"/>
      <entry key="post-delete" value-ref="auditEventListener"/>
      <entry key="pre-collection-update" value-ref="auditEventListener"/>
      <entry key="pre-collection-remove" value-ref="auditEventListener"/>
      <entry key="post-collection-recreate" value-ref="auditEventListener"/>
    </map>
  </property>
</bean>

The interceptor:

import org.hibernate.EmptyInterceptor;
import org.hibernate.type.Type;
..

public class DirtyCheckByPassInterceptor extends EmptyInterceptor {

  public DirtyCheckByPassInterceptor() {
    super();
  }


  /**
   * Flags ALL properties as dirty, even if nothing has changed. 
   */
  @Override
  public int[] findDirty( Object entity,
                      Serializable id,
                      Object[] currentState,
                      Object[] previousState,
                      String[] propertyNames,
                      Type[] types ) {
    int[] result = new int[ propertyNames.length ];
    for ( int i = 0; i < propertyNames.length; i++ ) {
      result[ i ] = i;
    }
    return result;
  }
}

ps: keep in mind that this is a simplified example. It will not work out of the box but it will guide you towards a working solution.

Answer 5

You could extend the AuditReaderImpl with a fallback option for the find method, like:

    public class AuditReaderWithFallback extends AuditReaderImpl {

    public AuditReaderWithFallback(
            EnversService enversService,
            Session session,
            SessionImplementor sessionImplementor) {
        super(enversService, session, sessionImplementor);
    }

    @Override
    @SuppressWarnings({"unchecked"})
    public <T> T find(
            Class<T> cls,
            String entityName,
            Object primaryKey,
            Number revision,
            boolean includeDeletions) throws IllegalArgumentException, NotAuditedException, IllegalStateException {
        T result = super.find(cls, entityName, primaryKey, revision, includeDeletions);
        if (result == null)
            result = (T) super.getSession().get(entityName, (Serializable) primaryKey);
        return result;
    }
}

You could add a few more checks in terms of returning null in some cases. You might want to use your own factory as well:

    public class AuditReaderFactoryWithFallback {


    /**
     * Create an audit reader associated with an open session.
     *
     * @param session An open session.
     * @return An audit reader associated with the given sesison. It shouldn't be used
     * after the session is closed.
     * @throws AuditException When the given required listeners aren't installed.
     */
    public static AuditReader get(Session session) throws AuditException {
        SessionImplementor sessionImpl;
        if (!(session instanceof SessionImplementor)) {
            sessionImpl = (SessionImplementor) session.getSessionFactory().getCurrentSession();
        } else {
            sessionImpl = (SessionImplementor) session;
        }

        final ServiceRegistry serviceRegistry = sessionImpl.getFactory().getServiceRegistry();
        final EnversService enversService = serviceRegistry.getService(EnversService.class);

        return new AuditReaderWithFallback(enversService, session, sessionImpl);
    }

}

Answer 6

Take a look at http://www.jboss.org/files/envers/docs/index.html#revisionlog

Basically you can define your own 'revision type' using @RevisionEntity annotation, and then implement a RevisionListener interface to insert your additional audit data, like current user and high level operation. Usually those are pulled from ThreadLocal context.

Answer 7

I've checked many ways, but the best way for me is to write a PL/SQL script as below.

The below script is written for PostgreSQL. Didn't check other vendors, but they must have the same feature.

CREATE SEQUENCE hibernate_sequence START 1;

DO
$$
    DECLARE
        u       RECORD;
        next_id BIGINT;

    BEGIN
        FOR u IN SELECT * FROM user
            LOOP
                SELECT NEXTVAL('hibernate_sequence')
                INTO next_id;
                INSERT INTO revision (rev, user_id, timestamp)
                VALUES (next_id,
                        '00000000-0000-0000-0000-000000000000',
                        (SELECT EXTRACT(EPOCH FROM NOW() AT TIME ZONE 'utc')) * 1000);
                INSERT INTO user_aud(rev,
                                     revend,
                                     revtype,
                                     id,
                                     created_at,
                                     created_by,
                                     last_modified_at,
                                     last_modified_by,
                                     name)
                VALUES (next_id,
                        NULL,
                        0,
                        f.id,
                        f.created_at,
                        f.created_by,
                        f.last_modified_at,
                        f.last_modified_by,
                        f.name);
            END LOOP;
    END;
$$;