0 Replies Latest reply on Dec 9, 2010 2:02 PM by ajevans85

    Extending AuditEventListener, onPostUpdate custom SQL

    ajevans85

      I have a issue where I need to run some custom SQL after a audit has been inserted.

       

      I've done this by overiding onPostUpdate method of AuditEventListener. From there I get the session and call doWork, quick example below

       

       

      {code}

      @Override
      public void onPostUpdate(PostUpdateEvent event) {
             
              super.onPostUpdate(event);
             
              if(event.getEntity() instanceof Location){
                  event.getSession().doWork(new Work() {
                      public void execute(Connection connection) throws SQLException {
                          connection.prepareStatement("SELECT now()").execute();
                      }
                  });
              }
             
      }

      {code}

       

      The problem I'm having is "SELECT now()" is run before the audit data is committed when looking at the MySQL query log, i need to be able to execute queries once the revision data has been committed.

       

      Looking through the Envers source I found the AuditProcess task which looks to execute tasks in the order added. So I changed onPostUpdate to the below and created a sample WorkUnit to see if this solved the issue

       

       

      {code}

      @Override
      public void onPostUpdate(PostUpdateEvent event) {
             
              super.onPostUpdate(event);
             
              if(event.getEntity() instanceof Location){
                  AuditProcess auditProcess = getVerCfg().getSyncManager().get(event.getSession());
                 
                  auditProcess.addWorkUnit(new JdbcWorkUnit());
              }
             
      }

      {code}

       

       

      {code}

      package bbc.forge.thingstodo.dao;

      import java.sql.Connection;
      import java.sql.SQLException;
      import java.util.Map;

      import org.hibernate.Session;
      import org.hibernate.envers.synchronization.work.AddWorkUnit;
      import org.hibernate.envers.synchronization.work.AuditWorkUnit;
      import org.hibernate.envers.synchronization.work.CollectionChangeWorkUnit;
      import org.hibernate.envers.synchronization.work.DelWorkUnit;
      import org.hibernate.envers.synchronization.work.FakeBidirectionalRelationWorkUnit;
      import org.hibernate.envers.synchronization.work.ModWorkUnit;
      import org.hibernate.envers.synchronization.work.WorkUnitMergeVisitor;
      import org.hibernate.jdbc.Work;

      public class JdbcWorkUnit implements AuditWorkUnit{
         
          private boolean performed = false;

          public AuditWorkUnit merge(AddWorkUnit second) {
              // TODO Auto-generated method stub
              return null;
          }

          public AuditWorkUnit merge(ModWorkUnit second) {
              // TODO Auto-generated method stub
              return null;
          }

          public AuditWorkUnit merge(DelWorkUnit second) {
              // TODO Auto-generated method stub
              return null;
          }

          public AuditWorkUnit merge(CollectionChangeWorkUnit second) {
              // TODO Auto-generated method stub
              return null;
          }

          public AuditWorkUnit merge(FakeBidirectionalRelationWorkUnit second) {
              // TODO Auto-generated method stub
              return null;
          }

          public AuditWorkUnit dispatch(WorkUnitMergeVisitor first) {
              // TODO Auto-generated method stub
              return null;
          }

          public Object getEntityId() {
              // TODO Auto-generated method stub
              return null;
          }

          public String getEntityName() {
              // TODO Auto-generated method stub
              return null;
          }

          public boolean containsWork() {
              return true;
          }

          public boolean isPerformed() {
              return performed;
          }

          public void perform(Session session, Object revisionData) {
              session.doWork(new Work() {
                  public void execute(Connection connection) throws SQLException {
                      connection.prepareStatement("SELECT now()").execute();
                      performed = true;
                  }
              });
          }

          public void undo(Session session) {
              // TODO Auto-generated method stub
             
          }

          public Map<String, Object> generateData(Object revisionData) {
              // TODO Auto-generated method stub
              return null;
          }

      }

      {code}

       

      Taking this approach I get the same result, the custom SQL seems to be run before the insert into the audit tables although looking at AuditProcess.executeInSession() it shouldn't?

       

      Is it possible to do what I want to do?

       

      Essentialy I am trying to create a versioning system so the main table only contains LIVE enetities. So on postUpdate if status != LIVE I want to run the following outside of Hibernate so not to fire anymore envents:

       

      1) Select the last LIVE entity from the audit table

      2) Update the main table with result of last LIVE revision

       

      I need to do the swap for performance reasons as queries where taking to long on the audit tables. Now for all live entities I just query on the main tables as I know these are live. I have the template all hooked up to do this, sql working etc it's just a case of getting the sql to run after the Revision insert, below is what we see in the query log based on the postUpdate example given.

       

      929 Query       SELECT now()
      929 Query       update location (DATA REMOVED)
      929 Query       insert into REVINFO (REVTSTMP) values (1291921029059)
      929 Query       insert into location_AUD (DATA REMOVED)
      929 Query       commi

       

      Any help / pointers appreciated