Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » (no subject)
(no subject) [message #713519] Mon, 08 August 2011 10:37
Stefan Palme is currently offline Stefan Palme
Messages: 8
Registered: August 2011
Junior Member
Hi all,

I have the following problem, sorry that I have to explain a little bit more for the background, I will reduce it to the most simple case, I promise:

I use EclipseLink in a JPA environment. I have an entity with a @Version annotation to mark a specific attribute resp. column ("VERSION") to hold a version counter for optimistic locking. Furthermore I have configured EclipseLink to manage historical data using additional tables.

My "regular" entity data is stored in table ENTITY, the table with historical data is ENTITY_HIST with two additional columns ROW_START and ROW_END to mark the time range where each record has been active. I have used the EclipseLink SessionCustomizer to configure the appropriate HistoryPolicy for my entity class.

Please note, that the VERSION column obviously appears in ENTITY and in ENTITY_HIST.

Furthermore, my entity class contains an attribute resp. column "EDITOR", which should track which user made the a change to the database record. I have a JPA @PreCommit hook which is responsible to update this value each time a database record is modified.

Now let's assume, the currently "active" database record from the ENTITY_HIST table is somethink like this (I have reduced the timestamps to be times only for simplicity):
id=123, version=3, editor=XYZ, row_start=12:00:00, row_end=NULL

Now (at 12:15:00) the person "ABC" deletes the record from the ENTITY table. This leads to the following new content of the ENTITY_HIST table:
id=123, version=3, editor=XYZ, row_start=12:00:00, row_end=12:15:00
id=123, version=4, editor=ABC, row_start=12:15:00, row_end=12:15:00

A new history record is created, because we have changed some value (the EDITOR). At the same time, this record is deleted from database, so ROW_START and ROW_END of the last history record will be exactly the same.

Yes, I know, it seems(!) silly to modify a database record and to delete it in the same transaction, but we need this approach because of auditing reasons (we have to know WHO deleted the database record).

The problem with this: I can not fetch the very last historical record in a historical session. If I use "12:15:00" as the time for the AsOfClause, EclipseLink creates a database query with the following WHERE clause:

WHERE (row_start>='12:15:00' AND (row_end IS NULL OR row_end<'12:15:00'))

Since row_end is neither NULL, nor is it less than '12:15:00' (it is *exactly* '12:15:00'), the historical session will return nothing in this case, which is not what I want :-)

So my idea was to add an expression to the created query, so that the resulting WHERE clause will be something like this:

WHERE (row_start>='12:15:00' AND (row_end IS NULL OR row_end<'12:15:00'))
OR (row_start='12:15:00' AND row_end=row_start)

But I did not manage it, I don't know how to access the column "row_start" and "row_end" in a HistoryPolicy.additionalHistoryExpression(), and I do not know how to access the requested AsOfClause there.

Some other ideas included using native SQL queries to fetch the most recent history record and then create an entity object from it, but in this case, access to all the related entites (using foreign key constraints respective ManyToOne mappings etc.) did not work (because when using native SQL queries I do not really have a historical session, of course).

Any ideas of how to solve this problem? Currently I am thinking about modifying the EclipseLink source code to add my additional expression to the automatically created WHERE clause, but this I see as the very last resort.

Currently used EclipseLink version is 2.2.0, but the same problem also exists with 2.3.0.

Any help really appreciated
Thanks in advance

-stefan-
Previous Topic:Accessing outer query in Eclipselink subquery
Next Topic:uses a non-entity as target entity in the relationship attribute
Goto Forum:
  


Current Time: Mon Sep 01 22:02:10 EDT 2014

Powered by FUDForum. Page generated in 0.03594 seconds