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

[Erik-Berndt Scheper (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3709?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=38135#action_38135 ] 

Erik-Berndt Scheper commented on HHH-3709:
------------------------------------------

This issue duplicates     
* HHH-3763 An optional "end revision" column
* HHH-3766 Modify the queries executed to use the "end-revision" column, when available

> 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.5.0.Beta-1
>            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