1 2 Previous Next 17 Replies Latest reply on Mar 24, 2012 11:20 PM by adamw Go to original post
      • 15. Re: Auditing with hierarchies #3 :)
        adamw

        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

          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

           

          FieldValueDate/Rev
          User
          First Name(borrower#1)John12-31-2010 09:00:00/5user1
          Amount(fee#1)100.0012-31-2010 09:00:00/5user1
          First Name(borrower#2)Mary12-30-2010 10:00:00/4user2
          Amount(fee#2)800.0012-30-2010 10:00:00/4user2




           

           

           

          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

            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

            1 2 Previous Next