-
15. Re: Auditing with hierarchies #3 :)
adamw Feb 24, 2012 2:30 PM (in response to marx3)For your second problem, maybe you can add a field to a custom revision entity to later group revisions logically.
Adam
-
16. Re: Auditing with hierarchies #3 :)
mark_v_torres Mar 15, 2012 5:32 PM (in response to ahoehma)Hello,
First off, I think this will be a great feature to have. We currently are trying to implement audit functionality and one of the features we've identified that would be useful is to have a historical view of all changes related to the root entity.
So say for the following model(greatly simplified, as its the object graph is deeper than this), loan being the root entity
@Entity public class Loan { @Id @GeneratedValue private Long id; @OneToMany(mappedBy="loan") private Set<Borrower> borrowers; @OneToMany(mappedBy="loan") private Set<Fee> fees; } @Entity public class Borrower { @Id @GeneratedValue private Long id; @ManyToOne private Loan loan; private String firstName; private String lastName; } public class Fee { @Id @GeneratedValue private Long id; private String type; private BigDecimal amount; @ManyToOne private Loan loan; }
We'd like to build something like
Loan History Loan#1
Field Value Date/Rev User First Name(borrower#1) John 12-31-2010 09:00:00/5 user1 Amount(fee#1) 100.00 12-31-2010 09:00:00/5 user1 First Name(borrower#2) Mary 12-30-2010 10:00:00/4 user2 Amount(fee#2) 800.00 12-30-2010 10:00:00/4 user2 I'm just beginning to scratch the surface of what envers(we're doing the proof of concept on 3.6) does, but as far as I've explored, its not doable without doing something custom.
The issues are, how do we find the root entity, and how do we save the roots, and the subentities in the audit tables during a change.
Again, I'm a newbie to envers so please bear with me if the following ideas overlook a bunch of stuff, but I just wanted to share and explore them...
1. To find the root entity, and possibly save audit trails of the intermediate path(s) to the root entity, maybe we can define some new cascade type. So for my example, it will be something like
public class Fee { ... @ManyToOne @Cascade(value=CascadeType.AUDIT) private Loan loan; @Entity @Audited(oncascade=SAVE_AS_ROOT) public class Loan { ...
oncascade can be SAVE_AS_ROOT (save as root entity), SAVE (save an audit record), NOSAVE (continues to cascade to its relations, but does not save an audit record, maybe the default behavior).
2. For saving the data, what about a schema similar to the following
ENTITY_REVISION table
ID(int/long pk)
REV
ENTITY_NAME
DATA_ID(int/long pseudo fk to Entity_AUD.ID(eg for Loan, points to Loan_AUD.id))
REV_TYPE
Loan_AUD table
ID(int/long pk)
ORIG_ID(domain original pk)
...data columns...
ENTITY_ROOT_RELATION table
ROOT_ID (fk to ENTITY_REVISION.ID)
DESCENDANT_ID (fk to ENTITY_REVISION.ID)
1. A "regular" revision for a Loan instance is done by creating a new record in ENTITY_REVISION, and Loan_AUD.
2. A "virtual" revision for a Loan instance is done by creating a new record in ENTITY_REVISION, which points to the "latest" Loan_AUD record whose ORIG_ID=Loan.id.
3. A "regular" revision for a Borrrower is done by creating a new record in ENTITY_REVISION, and Borrower_AUD, a data for a "virtual" revision for Loan(2.). In addition a record in ENTITY_ROOT_RELATION is created to relate Borrower and Loan.
To query the change history for a loan...
select er.*,erc.* Loan_AUD la
inner join ENTITY_REVISION er
on er.ENTITY_NAME='Loan'
and er.DATA_ID=la.ID
left join ENTITY_ROOT_RELATION err
on err.ROOT_ID=er.ID
left join ENTITY_REVISION erc
on erc.ID=err.DESCENDANT_ID
where la.ORIG_ID = <id>;
and iterate over audit records related to er.*, and erc.*.
Im concerned about this approach of breaking out the revision info out of the the Entity_AUD tables into ENTITY_REVISION. Its a pretty big change and Im not sure how big of an impact it will be on the existing envers functionality. The number of inserts also doubles, although maybe the insert into ENTITY_REVISION can be "batched" more efficiently.
-
17. Re: Auditing with hierarchies #3 :)
adamw Mar 24, 2012 11:20 PM (in response to mark_v_torres)Well that's certainly one path to take, but it would probably take some time to implement
Btw. I would recommend doing any POC on Hibernate4, as 3.6 will no longer be maintained.
Adam