How to delete entries from my audit table?

Question

I am currently working with Hibernate Envers.

How to delete entries in the audit table related to the entity I want to delete? My entity has no relation with other entities.

I figured out that I have to do that in onPostDelete method of my custom listener:

import org.hibernate.envers.event.AuditEventListener;
import org.hibernate.event.PostCollectionRecreateEvent;
import org.hibernate.event.PostDeleteEvent;
import org.hibernate.event.PostInsertEvent;
import org.hibernate.event.PostUpdateEvent;
import org.hibernate.event.PreCollectionRemoveEvent;
import org.hibernate.event.PreCollectionUpdateEvent;

public class MyListener extends AuditEventListener {

  ...
  @Override
  public void onPostDelete(PostDeleteEvent arg0) {
    // TODO Auto-generated method stub
    super.onPostDelete(arg0);
  }
  ...

}

I've read the documentation, forums, many things but I can't figure it out. Maybe it's not possible, I don't know.

Has someone ever done this before?

Answer 1

Ok I'm 50% done with this for those who want to know.

Thanks to the creator of Hibernate Envers, Adam Warski, I quote:

"id" is a hibernate keyword for the id of an entity, whatever the names is; in case of audit entities, the id is composite and is called "originalId". Try:

"delete from full.package.name.User_AUD u where u.originalId.id = :userid" 

But now, I also would like to delete entries related to audit table in my revinfo table.

If someone has a clue, let me know.

Answer 2

If you want to wipe out a revision by ID, you can access the envers table directly using a native query. There are 2 tables that contain references to the revision. Assuming your audit table uses the conventional _AUD suffix, you can find the entity table name programmatically.

Here are some snippets written in Kotlin:

fun getAuditTableName(em: EntityManager, aClass: Class<*>): String {
    return getAuditTableName(em, aClass.name) + "_AUD"
}

fun getEntityTableName(em: EntityManager, aClass: Class<*>): String {
    val session = em.unwrap(Session::class.java) as Session
    val sessionFactory = session.sessionFactory
    val hibernateMetadata = sessionFactory.getClassMetadata(className)
    val persister = hibernateMetadata as AbstractEntityPersister
    return persister.tableName
}

Now that we have the table name, we can remove the rows in the tables. (Put this in your JPA transaction block, replace the content as needed, and adjust the SQL for your provider). So given MyEntityClass and myRevisionId, we can do something like this:

    val em:EntityManager = getEntityManager()
    val auditTableName = getAuditTableName(MyEntityClass::class.java)

    em.createNativeQuery("delete from `$auditTableName` where REV=${myRevisionId}").executeUpdate()
    em.createNativeQuery("delete from REVINFO where REV=${myRevisionId}").executeUpdate()

If you want to delete by a parameter other than the revisionID, simply query for the the revisionIds in the entity_AUD table, and then delete the found rows in the mentioned way.

Keep in mind that a revisionId may be associated with more than 1 entity, and all of the entries will be removed in the previous method. To delete the revision for a single entity, you will need the entity's ID and entity's key field name(s).

Here is code for dynamically getting the field name:

fun getEntityKeyNames(em: EntityManager, entityClass: Class<*>): List<String> {
    val session = em.unwrap(Session::class.java) as Session
    val sessionFactory = session.sessionFactory
    val hibernateMetadata = sessionFactory.getClassMetadata(entityClass.name)
    val persister = hibernateMetadata as AbstractEntityPersister
    return persister.keyColumnNames.toList()
}

Answer 3

This is fully working to me, and no native query required

AuditQuery aq = auditReader.createQuery()
                   .forRevisionsOfEntity( ErpEmploye.class, true, false);       
 aq.add( AuditEntity.id().eq( employe.getCodeId() ) );
 aq.add( AuditEntity.relatedId("period").eq( erpPeriod.getCodeId() ) );
 List result =  aq.getResultList();//parameters must be added, this call is required
 if (result.size()>0){
    Query query = (Query) PrivateAccessor.invokePrivateMethod( aq, "buildQuery", new Object[0]);
    String queryString = (String) PrivateAccessor.getPrivateField( query, "queryString", true );
    PrivateAccessor.setPrivateField( query, "queryString", queryString.replace("select e__ from", "delete from"), true );
    getDAO().executeQuery(query);//transaction required             
}

Answer 4

Audit entries are typically only added, not deleted, even when the related entity is deleted so I don't think that the Envers API provide support for that.

Now, if really you want to remove entries for deleted entities (this kinda defeats the purpose of auditing), you can maybe delay this a bit and instead of removing entries at deletion time, run a daily native query, for example every night.