text: 15. Re: Auditing with hierarchies #3 :)Adam Warski 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 Torres Mar 15, 2012 5:32 PM (in response to Andreas Hoehmann)
  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
  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 :)Adam Warski Mar 24, 2012 11:20 PM (in response to Mark 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