query to obtain last time an entity has changed

[aryankids]

Dear Team,

Due to nature of the project I am working on at the moment, our audit tables are huge.  Although certain column got indexed properly but still querying the audit tables considered as expensive process and should be avoided if it is possible.

Taking above fact into account I would like to know,  when was the last time an entity with a given id has been changed before load that entity using envers . The only efficient way I came across is to join audit table and master revision table as follow : (assume my ent.id = 409 )

select globerevis1_.creation_date from myEntity_AUD  ent_a0_, Global_Revision  globerevis1_ where

ent_a0_.rev_id=globerevis1_.id and ent_a0_.rev_type<>2 and ent_a0_.id=409 and ent_a0_.revend_date_time is null       

The above query returns a date which indicates the last time that specific entity with id=409 has changed. I could use this date to check whether it is appropriate to load the entity or not. So far so good but I could not find a way to access revend_date_time using audit reader. So the following is invalid :

final Date lastModifiedDate = (Date) getAuditReader().createQuery().forRevisionsOfEntity(getType(), true, false)

.addProjection(AuditEntity.revisionProperty("timeStamp").max()).add(AuditEntity.property("revend_timestamp").eq(null)).getSingleResult();

So given the above requirement, is there any way that I could access columns which has been created and used for validity audit strategy using auditReader? Is there any other way to check the time  an entity has changed before using envers to load the entire object ?  

here is part of envers config file :

			<property name="org.hibernate.envers.audit_strategy" value="org.hibernate.envers.strategy.ValidityAuditStrategy"/>
			<property name="org.hibernate.envers.audit_strategy_validity_end_rev_field_name" value="REV_ID_END"/>
			<property name="org.hibernate.envers.audit_strategy_validity_store_revend_timestamp" value="true"/>
			<property name="org.hibernate.envers.audit_strategy_validity_revend_timestamp_field_name" value="REVEND_DATE_TIME"/>

Any help or suggestion is appreciated in advance.

kind regards

[adamw]

Hello,

I think a better way would be to query for revision numbers, instead of using dates. So you could do: AuditEntity.revisionNumber().max() instead of

AuditEntity.revisionProperty("timeStamp").max(). Then you could easily check if the entity should be loaded or not.

Adam

[aryankids]

Hi Adam,

Many thanks for reply.

You are absolutely right, max revision would do the job however due to nature of our project "time" constraint plays a vital role in almost all queries hence choosing alternative query sometime ends up with a complicated select command so I thought it would be great if I could use rev_end_time_stamp column which is already available in the audit table.

I was wondering if you could point me to right direction to be able to expose the timestamp column which is used by validity audit strategy to outside world. Any help or even pointer to any guidance or discussion would be much appreciated. 

Regards

[adamw]

But the validity audit strategy uses revisions, not timestamps. You could join to the revision info table though, if needed, but that would require a hand-crafter query, it's not (yet) unfortunately possible using the API.

Adam