[hibernate-issues] [Hibernate-JIRA] Created: (HHH-3709) Add StartRevision/EndRevison fileds to audit tables

[jason shi (JIRA)]

Add StartRevision/EndRevison fileds to audit tables
---------------------------------------------------

                 Key: HHH-3709
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3709
             Project: Hibernate Core
          Issue Type: Improvement
          Components: envers
    Affects Versions: 3.4
            Reporter: jason shi


In Envers audit tables(eg:Person_Aud), two fields added:REV,REVTYPE

When retrieve data at special REV, a sql with subselect executed:
 select a.id, a.REV, a.REVTYPE, a.name, a.surname, a.address_id
      from Person_AUD a
      where a.REVTYPE <> ? and a.id = ?
          and a.REV = (select max(b.REV)   from Person_AUD b   where b.REV <= ?       and a.id = b.id)

The sql performance is poor.

I suggest adding  StartRevision/EndRevison fileds to the audit tables,replace the REV field.
The StartRevision equals the original REV field,EndRevision will be filledd when this record changed in next Revision.

The new query sql will like this:
 select a.id, a.REV, a.REVTYPE, a.name, a.surname, a.address_id
      from Person_AUD a
      where a.REVTYPE <> ? and a.id = ?
            and a.StartRevision<=? and a.EndRevision>?



-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira