Problem retrieving deleted archived entities: RevisionsEntity settings are ignored

[kniffte]

Hello,

we're having an @Audited table here, whose entries get inserted and modified several times, before they get deleted.

Finally, I would like to retrieve the deleted entity from the archive table (the last modified dataset is fine as well).

I have therefore created the following statements:

AuditQuery auditQuery = AuditReaderFactory.get(this.em).createQuery().forRevisionsOfEntity(FraudRequestDOM.class, true, true);

auditQuery.add(AuditEntity.id().eq(requestId));

auditQuery.add(AuditEntity.revisionType().eq(RevisionType.DEL));

FraudRequestDOM is the @Audited annotated class and requestId is its primary key (in the working table).

The FraudRequestDOM includes yet another object (FraudCustomerDOM customerDOM) as attribute, which referenced by the following relation: FraudRequestDOM.fraud_customer_id -> FraudCustomerDOM.id. The FraudCustomerDOM on the other hand references another object (List<FraudCustomerAddressDOM> addresses) with the following settings:

FraudCustomerDOM:

     @OneToMany(mappedBy = "customer", cascade =

    { CascadeType.ALL }, fetch = FetchType.EAGER)

    private List<FraudCustomerAddressDOM> addresses = new ArrayList<FraudCustomerAddressDOM>();

FraudCustomerAddressDOM:

    @ManyToOne

    @JoinColumn(name = "fraud_customer_id")

    private FraudCustomerDOM customer = null;

relation: FraudCustomerDOM.fraud_customer_id => FraudCustomerAddressDOM.id

Using craftsman:spy:1.0.5, I have taken a look into the generated select statement, here's the first part:

04 Jun 2012 12:58:17 [main] INFO  craftsman.spy.SpyConnection  - 18206828:autocommit(false) succeed (13 ms)

04 Jun 2012 12:58:17 [main] INFO  craftsman.spy.SpyPreparedStatement  - 18206828:select fraudreque0_.request_id as request1_6_, fraudreque0_._revision as column2_6_, fraudreque0_._revision_type as column3_6_, fraudreque0_.ps_id as ps5_6_, fraudreque0_.fraud_customer_id as fraud6_6_ from archiv.dbo.fraud_request fraudreque0_ where fraudreque0_.request_id=21104183 and fraudreque0_._revision_type=2 order by fraudreque0_._revision asc => ... (13 ms)

04 Jun 2012 12:58:17 [main] INFO  craftsman.spy.SpyResultSet  - 18206828:request_id=21104183,_revision=32485192,_revision_type=2,ps_id=20000,fraud_customer_id=2198495 (row 1)

04 Jun 2012 12:58:17 [main] INFO  craftsman.spy.SpyResultSet  - 18206828:total rowcount is 1

... in short: this one is correct:

1. it retrieves the deleted entries as well (since I use .forRevisionsOfEntity(FraudRequestDOM.class, true, true))

2. it retieves the deleted entry only (since I defined restriction: auditQuery.add(AuditEntity.revisionType().eq(RevisionType.DEL)))

=> this is archived by the where clause: fraudreque0_._revision_type=2

2. this way, it should continue using _revision=32485192

Now it tries to select the CustomerDOM:

04 Jun 2012 12:58:18 [main] INFO  craftsman.spy.SpyPreparedStatement  - 18206828:select fraudcusto0_.id as id4_, fraudcusto0_._revision as column2_4_, fraudcusto0_._revision_type as column3_4_, fraudcusto0_.customer_id as customer4_4_, fraudcusto0_.firstname as firstname4_, fraudcusto0_.lastname as lastname4_ from archiv.dbo.fraud_customer fraudcusto0_ where fraudcusto0_._revision=(select max(fraudcusto1_._revision) from archiv.dbo.fraud_customer fraudcusto1_ where fraudcusto1_._revision<=32485192 and fraudcusto0_.id=fraudcusto1_.id) and fraudcusto0_._revision_type<>2 and fraudcusto0_.id=2198495 => ... (10 ms)

... in short, this one is incorrect:

1. it tried to use the correct fraudcusto1_._revision<=32485192

2. it fails, because the following _revision_type restriction is applied: fraudcusto0_._revision_type<>2 (this is Sybase for "not 2")

Therefore it tries to select the _revision=32485192, but since this is a _revision_type=2 it fails as this _revision_type is excluded.

I tried to do it the other way round, specifying the use modifications only, but that one failed again:

AuditQuery auditQuery = AuditReaderFactory.get(this.em).createQuery().forRevisionsOfEntity(FraudRequestDOM.class, true, false);

auditQuery.add(AuditEntity.id().eq(requestId));

auditQuery.add(AuditEntity.revisionType().eq(RevisionType.MOD));

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:request_id=21104183,_revision=32484609,_revision_type=1,client_request_id='1184544',ps_id=10002,fraud_customer_id=2198495 (row 1)

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:request_id=21104183,_revision=32484610,_revision_type=1,client_request_id='1184544',ps_id=10001,fraud_customer_id=2198495 (row 2)

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:request_id=21104183,_revision=32484676,_revision_type=1,client_request_id='1184544',ps_id=10001,fraud_customer_id=2198495 (row 3)

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:request_id=21104183,_revision=32484811,_revision_type=1,client_request_id='1184544',ps_id=14501,fraud_customer_id=2198495 (row 4)

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:request_id=21104183,_revision=32485010,_revision_type=1,client_request_id='1184544',ps_id=14502,fraud_customer_id=2198495 (row 5)

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:request_id=21104183,_revision=32485032,_revision_type=1,client_request_id='1184544',ps_id=15000,fraud_customer_id=2198495 (row 6)

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:request_id=21104183,_revision=32485184,_revision_type=1,client_request_id='1184544',ps_id=20000,fraud_customer_id=2198495 (row 7)

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:total rowcount is 7

FraudRequestDOM: Found 7 results

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyPreparedStatement  - 856873:select fraudcusto0_.id as id4_, fraudcusto0_._revision as column2_4_, fraudcusto0_._revision_type as column3_4_, fraudcusto0_.customer_id as customer4_4_, fraudcusto0_.firstname as firstname4_, fraudcusto0_.lastname as lastname4_ from archiv.dbo.fraud_customer fraudcusto0_ where fraudcusto0_._revision=(select max(fraudcusto1_._revision) from archiv.dbo.fraud_customer fraudcusto1_ where fraudcusto1_._revision<=32484609 and fraudcusto0_.id=fraudcusto1_.id) and fraudcusto0_._revision_type<>2 and fraudcusto0_.id=2198495 => ... (13 ms)

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:id=2198495,_revision=32484608,_revision_type=0,customer_id=126272062,firstname='Lastname',lastname='Firstname' (row 1)

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:total rowcount is 1

FraudRequestDOM: Entry contained customerId: 2198495, Mr./Mrs. Lastname Firstname

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyPreparedStatement  - 856873:select fraudcusto0_.id as id5_, fraudcusto0_._revision as column2_5_, fraudcusto0_._revision_type as column3_5_, fraudcusto0_.address_type as address4_5_, fraudcusto0_.city as city5_, fraudcusto0_.countrycode as countryc6_5_, fraudcusto0_.firstname as firstname5_, fraudcusto0_.lastname as lastname5_, fraudcusto0_.postbox as postbox5_, fraudcusto0_.street as street5_, fraudcusto0_.streetnumber as streetn11_5_, fraudcusto0_.title as title5_, fraudcusto0_.zipcode as zipcode5_, fraudcusto0_.fraud_customer_id as fraud14_5_ from archiv.dbo.fraud_customeraddress fraudcusto0_ where fraudcusto0_.fraud_customer_id=2198495 and fraudcusto0_._revision=(select max(fraudcusto1_._revision) from archiv.dbo.fraud_customeraddress fraudcusto1_ where fraudcusto1_._revision<=32484609 and fraudcusto0_.id=fraudcusto1_.id) and fraudcusto0_._revision_type<>2 => ... (40 ms)

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:id=2358244,_revision=32484608,_revision_type=0,address_type='CONTRACT_CONTACT',city='City',countrycode='D',firstname='Lastname',lastname='Firstname',postbox=NULL,street='Street.',streetnumber='36',title=NULL,zipcode='99551',fraud_customer_id=2198495 (row 1)

04 Jun 2012 13:18:55 [main] INFO  craftsman.spy.SpyResultSet  - 856873:total rowcount is 1

... in short: it selects the maximum _revision, but restricts it immediately to the smallest one it knows about (_revision=32485184 vs _revision=3248469).

Now, how can I get Envers to select

a) the deleted entity

b) the last modified entity

for all entities loaded during the select?

Please note: Since I'm bound to use JBoss 4.2.3.GA, which provides JPA 1.0 only, I'm currently still using org.jboss.envers:jboss-envers:1.1.0.GA. I know, this is very old, but I cannot switch to a JPA 2.0 hibernate. That's why I'm actually using VersionsQuery etc., but I didn't want to shy you off from the start. The test (and its results) has been run using org.hibernate:hibernate-envers:4.1.4.Final in a Spring-based stand-alone testing client.

Please do not hesitate to contact me for any further details required.

Thanks a lot in advance.

kniffte

[adamw]

It is a known bug that traversing a historic entity retrieved with rev_type == DEL doesn't work.

What should work, though, is getting the entity at the revision before the deletion. So if you know the revision number at which the entity is deleted, try getting it at that revision-1. Alternatively, use the maximize() method on the query to maximize the revision number with MOD revision type.

Note, though, that the entity tree when retrieved at (delete revision - 1) and at (revision at which the root was last modified) may be different, as the child entities may have changed.

Adam

[kniffte]

Hello Adam,

thanks for your support.

No, I don't know the exact revision number; I only know the generic setting "it has been deleted and therefore the deleted entry or the one before is up to date".

I would like to use something as follows:

VersionsQuery versionsQuery = getVersionsReader().createQuery().forRevisionsOfEntity(FraudRequestDOM.class, false, false);

versionsQuery.add(VersionsRestrictions.idEq(requestId));

versionsQuery.add(VersionsRestrictions.maximizeProperty("revision"));

versionsQuery.add(VersionsRestrictions.eq("revisionType", RevisionType.MOD));

but that fails, since my target object does not include the Envers-specific attributes "revision" and "revisionType" (I tried it using the column names as well ... no luck here):

Exception in thread "main" org.hibernate.QueryException: could not resolve property: revision of: FraudRequestDOM_versions [select e, r from FraudRequestDOM_versions e, Revisions r where e._revision_type <> :_p0 and e.originalId.requestId = :_p1 and e.revision = (select max(_e0.revision) from FraudRequestDOM_versions _e0) and e.revisionType = :_p2 and e.originalId._revision.id = r.id order by e.originalId._revision.id asc]

at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:44)

at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:38)

at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1362)

at org.hibernate.hql.ast.tree.FromElementType.getPropertyType(FromElementType.java:279)

at org.hibernate.hql.ast.tree.FromElement.getPropertyType(FromElement.java:386)

at org.hibernate.hql.ast.tree.DotNode.getDataType(DotNode.java:567)

at org.hibernate.hql.ast.tree.DotNode.prepareLhs(DotNode.java:241)

at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:188)

at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:94)

at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:90)

at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:728)

at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1216)

at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4041)

at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3525)

at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1762)

at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1690)

at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1687)

at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1687)

at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:776)

at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:577)

at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)

at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)

at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)

at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)

at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)

at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)

at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)

at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)

at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)

at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)

at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)

at org.jboss.envers.query.impl.AbstractVersionsQuery.buildAndExecuteQuery(AbstractVersionsQuery.java:81)

at org.jboss.envers.query.impl.RevisionsOfEntityQuery.list(RevisionsOfEntityQuery.java:101)

at org.jboss.envers.query.impl.AbstractVersionsQuery.getResultList(AbstractVersionsQuery.java:94)

Is there any other option?

Of course, I can have Envers to just return me all entries and search for the maximum MOD revision myself - but thats not really my intention (and is slow by DB standards).

Thanks again, hope you can help me out.

Stefan

PS: is there any chance you can upload the Source and JavaDoc of (the very outdated) org.jboss.envers:jboss-envers:1.1.0.GA? Is there documentation / example available regarding that version? I found 3.x and 4.x documentation only.

[adamw]

In the newer version you can get AuditEntity.revisionNumber() and .revisionType() to get the appropriate property names.

Or as you can see in the query they are named _revision_type and originalId._revision.id.

As for sources, see here: http://anonsvn.jboss.org/repos/envers/tags/1.1.0-ga/

Adam