Design notes to support querying audit data using entity associations

Version 4

    Overview

     

    The intent for this feature is to add support for audit queries with constraints, projections, and sort operations based on joined entity relations.

     

    Here is an example to illustrate:

     

    @Entity

    public class Car {

        @Id

        long id;

        @ManyToOne

        private Manufacturer manufacturer

        private String make;

        @ManyToOne

        private Person owner;

        ...

    }

    @Entity

    public class Manufacturer {

        @Id

        long id;

        String name;

        ....

    }

    @Entity

    public class Person {

        @Id

        long id;

        @ManyToOne

        private Address address;

        private String name;

        private int age;

        ...

    }

    @Entity

    public class Address {

        @Id

        long id;

        private String streetNumber;

        ...

    }

     

    Example use cases using an associated entity's property (Car.owner.name):

    • find all Car entities at revision 1 with an owner named "John Doe" (inner join and constraint);
    • find all Car entities at revision 1 that have no owner, or have an owner named "Jane Doe" (outer join and constrant);
    • find the Car ID and its owner's name of each Car at revision 1 (inner join and projections);
    • find all Car entities at revision 1, with or without an owner, ordered by owner name (outer join and sort).

     

    Background:

     

    Note: this feature has already been implemented by [HHH-11025] for EAP 7.1.0.

     

    A new feature has been proposed that allows Envers audit data to be queried with specific where clauses on associated object.  While HQL-like queries are not supported for auditing Envers audit data, here is an example using Hibernate HQL to illustrate outer join, constraint, and sort functionality that would need to be added to the org.hibernate.envers.query.AuditQuery API:

     

    "select c from Car c left join c.manufacturer m left outer join c.owner where m.name = :manufacturerName order by c.owner"

     

    Envers requires the following functionality to be added to org.hibernate.envers.query.AuditQuery to support applying constraints. projections, and sort operations based on entity relations:

    • ability to specify an inner or outer join on *-to-one entity associations;
    • ability to traverse to a *-to-one associated entity and use its properties in audit query expressions;
    • ability to use entity aliases for specifying projections, constraints and sort operations.

     

    Issue Metadata

     

    EAP ISSUE: https://issues.jboss.org/browse/EAP7-293

     

    RELATED ISSUES:

     

    The following have already been implemented upstream in Hibernate ORM 5.1.0 and 5.2.0 (Hibernate Issue Tracker):

    [HHH-3555] Extend the Envers query system with the ability to traverse associations (limited to inner joins)

    [HHH-16] Explicit joins on unrelated classes - Hibernate Issue Tracker

     

    The following have already been released upstream in Hibernate ORM 5.2.0 (Hibernate Issue Tracker):

    [HHH-10762] Implement left-joins for relation traversion in audit queries

    [HHH-9178] Querying audited entities with embeddables fails with 'org.hibernate.QueryException: could not resolve property

    [HHH-8070] Support "in" expression in AuditRelatedId - Hibernate Issue Tracker

     

    The following backports HHH-10762, HHH-9178, HHH-8070 to Hibernate ORM 5.1 branch and was released in Hibernate ORM 5.1.1:

    [HHH-11025] Backport: Implement left-joins for relation traversal in audit queries

    Commit: https://github.com/hibernate/hibernate-orm/commit/8206ab3a50adcd3f468086f6ed0e6304551230ce

    Documentation: https://docs.jboss.org/hibernate/orm/5.1/userguide/html_single/Hibernate_User_Guide.html#envers-querying-entity-relation-jobs

     

    DEV CONTACTS: Gail Badner (primary), Chris Cranford (developer)

     

    QE CONTACTS: Jan Martiška

     

    AFFECTED PROJECTS OR COMPONENTS: Hibernate Envers 5.1.x (in Hibernate ORM component)

     

    OTHER INTERESTED PARTIES: TBD

     

    Requirements

     

    Hard Requirements

     

    These items must be satisfied in order to have a satisfactory feature.

     

    • Traverse *-to-one associations
      • Traverse a *-to-one association using an INNER JOIN:

    Example: AuditQuery query = getAuditReader().createQuery()

    .forEntitiesAtRevision( Car.class, 1 )

    .traverseRelation( "owner", JoinType.INNER );

      • Traverse a *-to-one association using a LEFT JOIN:

    Example: AuditQuery query = getAuditReader().createQuery()

    .forEntitiesAtRevision( Car.class, 1 )

    .traverseRelation( "owner", JoinType.LEFT );

      • Traverse nested associations;

    Example: AuditQuery query = getAuditReader().createQuery()

    .forEntitiesAtRevision( Car.class, 1 )

    .traverseRelation( "owner", JoinType.INNER )

    .traverseRelation( "address", JoinType.INNER );

      • Traverse associations and navigate back up an entity graph to traverse other entity associations

    (up method is used to navigate back up the entity graph)

    Example: AuditQuery query = getAuditReader().createQuery()

    .forEntitiesAtRevision( Car.class, 1 )

    .traverseRelation( "owner", JoinType.INNER )

    .up()

    .traverseRelation( "manufacturer", JoinType.INNER );

    • Constraints based on an associated entity properties
      • Constraint on an associated entity's property

    Example: AuditQuery query = getAuditReader().createQuery()

    .forEntitiesAtRevision( Car.class, 1 )

    .traverseRelation( "owner", JoinType.INNER )

    .add( AuditEntity.property( "name" ).like( "Joe%" ) );

      • Constraints using entity aliases

    Example: AuditQuery query = getAuditReader().createQuery()

    .forEntitiesAtRevision( Car.class, 1 )

    .traverseRelation( "owner", JoinType.INNER, "p" )

    .traverseRelation( "address", JoinType.INNER, "a" )

    .up()

    .up()

    .traverseRelation( "manufacturer", JoinType.INNER, "m" )

    .add(

    AuditEntity.disjunction()

    .add( AuditEntity.property( "p", "age" ).eq( 20 ) )

    .add( AuditEntity.property( "a", "streetNumber" ).eq( 1234 ) )

    .add( AuditEntity.property( "m", "name" ).eq( "Chevrolet" ) )

    );

    • Sort operations based on associated entity properties

    Example: AuditQuery query = getAuditReader().createQuery()

    .forEntitiesAtRevision( Car.class, 1 )

    .traverseRelation( "owner", JoinType.INNER, "o" )

    .up()

    .traverseRelation( "manufacturer", JoinType.INNER, "m" )

    .addOrder( AuditEntity.property( "o", "name" ).asc() )

    .addOrder( AuditEntity.property( "m", "name" ).asc() );

     

    Nice-to-Have Requirements

     

    Support for audit queries with constraints, projections, and sort operations based on *-to-many entity relations.