Problem retrieving deleted archived entities: RevisionsEntity settings are ignored
kniffte Jun 4, 2012 7:36 AMHello,
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