Implement Envers querying on top of SQL2011 versioned tables

[beikov]

The idea to support SQL2011 versioned tables, which came up on the JPA spec project in the past and the idea is interesting, because it could be a nice abstraction for Hibernate Envers to use.

I didn't give this too much thought yet, but I imagine the following:

• New annotation to mark versioned entities
• New HQL syntax to allow querying history data e.g. ('as' 'of' ('system time'|timestampExpression)|'versions' betweenPredicate)

The added benefit of Envers would be the possibility to fine tune columns/properties that should be audited as system versioning is an all or nothing thing. The AuditQuery API should be buildable on top of the new HQL syntax and there should be an SPI that Envers can plug into, to translate queries to the proper SQL.

[gavinking]

New HQL syntax to allow querying history data

Is that the right way to do it, or should it be more of a session-level setting like with filters?

[beikov]

Using filters instead would require that the user specifies for which from-node alias to enable a filter. This means that aliases would have to be globally unique. Also, what would be the solution for criteria queries since the nodes of criteria queries don't have aliases?

Not relying on aliases would be very limiting and would i.e. make it impossible to join history data against current data.

[Felk]

Coming from https://discourse.hibernate.org/t/predeleteeventlistener-veto-deletion-of-entity-with-relation/11649/9 I'd like to give you my perspective on the envers vs system-versioned tables discussion, because we were at the point of choosing between sql:2011-style versioning and envers a couple of years back.

First, because we struggled a lot to find the authorative pieces of documentation for Oracle, some Oracle-specific information dump:

• Oracle does implement SQL:2011 system-versioned tables. They mention that on the page "Oracle Support for Optional Features of SQL/Foundation" under T180:

  Oracle's Flashback capability is substantially the same as the standard's system-versioned tables. Some key differences are:

  • In Oracle you do not need to designate particular tables for journaling; all tables are journaled.
  • In Oracle, LOB columns need to be individually designated for journaling, because of the potential for large amounts of data. The standard has no analogous provision.
  • In Oracle you need a privilege in order to read historical data.
  • In the standard, journaled tables have columns to record the start and end timestamps for the row. In Oracle, this is provided through pseudocolumns.

• They call their technology used to implement system-versioned tables flashback data archive (FDA), or sometimes just flashback archive (FBA).
• They also put it under the label flashback time travel, for example: Using Flashback Time Travel

I think MySQL/MariaDB and MSSQL support it fully out-of-the-box, and postgres supports it with an extension, though I have no experience with any of those.

Now, back to topic.
The implementation options we considered back then were:

• Use envers as-is,
• Use Oracle's system-versioned tables as-is, but be forced to use native queries a lot as jpa/hibernate/jpql have no first-class support for system-versioned tables,
• Succumb to "Not-Invented-Here"-Syndrome and implement a SQL:2011-style wrapper in application code (mostly using hibernate event listeners).

Our (partially self-imposed) restrictions were:

1. Versioning is all-or-nothing, enabled or disabled per entity (we don't need fine-granularity)
2. Versioning must be automatic: application code inserting/updating/deleting data must not know about versioning.
3. Millisecond-precision for version timestamping.
4. Prefer JDBC. Keep it simple, reliable and readable by avoiding native queries or Java DSLs (like criteria API or AuditReaderFactory).
   • Also use batching wherever possible, avoiding single-entity APIs (like EntityManager#find).
5. Be able to efficiently search for current and previous versions by timestamp (fixed or range) using regular JPQL, and return the versions' timestamps.
6. No query duplication for querying versioned data. Must be able to just parameterize any "normal" query with an (optional) sys-date filter.
   • This requires either having all data be in one physical table (as we chose, performance is good enough), a logical table (which may be physically partitioned, as oracle does), or a view combining current and historic data.
7. Old versions must be able to be retained indefinetely.

We didn't go with Oracle, partially because of (4), in retrospect a bit because of (3), but also for bad reasons: Failing to investigate into the state of oracle's support, reading some documentation for Oracle 10 and concluding their flashback feature was not fit for long-term versioning (7).

We didn't go with envers because we feared we had to make large compromises on (4), (5) and (6).

If hibernate added JDBC-support for system-versioned tables, that would imo be a huge functionality win and might allow us to ditch our custom stuff entirely. I believe all we'd need are the features mentioned in this thread already:

• Extend JDBC to allow for the as of ...-syntax, for fixed times and ranges.
• Allow for a session-wide setting (dbms_flashback_archive.enable_at_valid_time for oracle).

Though by my judgmenet the latter is of lesser importance because the entire functionality can technically be built using the first feature only, with the session-level stuff just being QoL.
One challenge might be teaching hibernate to tolerate repeated primary keys, and other unexpected data characteristics arising from suddenly getting multiple versions per entity?