Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » problems with historical sessions combined with @Version
problems with historical sessions combined with @Version [message #713492] Mon, 08 August 2011 14:37 Go to next message
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-
Re: problems with historical sessions combined with @Version [message #713525 is a reply to message #713492] Mon, 08 August 2011 15:08 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

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

Is this the SQL that is generated? Seems backward, should be start <= 12:15 and end > 12:15.

But the SQL should probably have <= and >=, please log a bug for this.



James : Wiki : Book : Blog : Twitter
Re: problems with historical sessions combined with @Version [message #713537 is a reply to message #713525] Mon, 08 August 2011 15:23 Go to previous messageGo to next message
Stefan Palme is currently offline Stefan Palme
Messages: 8
Registered: August 2011
Junior Member
James wrote on Mon, 08 August 2011 11:08
WHERE (row_start>='12:15:00' AND (row_end IS NULL OR row_end<'12:15:00'))

Is this the SQL that is generated? Seems backward, should be start <= 12:15 and end > 12:15.


You are right, sorry, this was a typo. Of course the query is "WHERE row_start<='12:15' AND row_end>'12:15'.

Quote:

But the SQL should probably have <= and >=, please log a bug for this.


No, this is not the bug. The ">" for the row_end is *correct*, so that is will not overlap with the row_start of the following history record:

version=1, row_start=12:15, row_end=12:20
version=2, row_start=12:20, row_end=12:30
version=3, row_start=12:30, row_end=NULL

getHistoricalSession(12:20) will exactly know that version=2 is the correct record to be retrieved. If the query was (row_start<=12:20 and row_end>=12:20) than both version=1 and version=2 would match, which is not correct.

So the currently created query using "<=" and ">" is OK, only the special case where row_start==row_end is not handled correctly.

-stefan-
(no subject) [message #713573 is a reply to message #713525] Mon, 08 August 2011 15:23 Go to previous messageGo to next message
Stefan Palme is currently offline Stefan Palme
Messages: 8
Registered: August 2011
Junior Member
James wrote on Mon, 08 August 2011 11:08
> WHERE (row_start>='12:15:00' AND (row_end IS NULL OR row_end<'12:15:00'))
>
> Is this the SQL that is generated? Seems backward, should be start <= 12:15 and end > 12:15.


You are right, sorry, this was a typo. Of course the query is "WHERE row_start<='12:15' AND row_end>'12:15'.

Quote:
> But the SQL should probably have <= and >=, please log a bug for this.


No, this is not the bug. The ">" for the row_end is *correct*, so that is will not overlap with the row_start of the following history record:

version=1, row_start=12:15, row_end=12:20
version=2, row_start=12:20, row_end=12:30
version=3, row_start=12:30, row_end=NULL

getHistoricalSession(12:20) will exactly know that version=2 is the correct record to be retrieved. If the query was (row_start<=12:20 and row_end>=12:20) than both version=1 and version=2 would match, which is not correct.

So the currently created query using "<=" and ">" is OK, only the special case where row_start==row_end is not handled correctly.

-stefan-
Re: (no subject) [message #713596 is a reply to message #713537] Mon, 08 August 2011 16:21 Go to previous messageGo to next message
Stefan Palme is currently offline Stefan Palme
Messages: 8
Registered: August 2011
Junior Member
Sorry guys, I don't know why some of my messages get duplicated. Especially since the duplicate is not exactly equal to my original post...
-stefan-
Re: (no subject) [message #714825 is a reply to message #713596] Thu, 11 August 2011 15:04 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Yes, you are right.

I suppose we should ensure that start never equals end. So when we are setting the end if it equals the start we should increment it by 1 millisecond. I think we already do this in one case, so should be possible to extend it to this case as well. Please log a bug.

Either that, or we change the expression to "row_start<='12:15' AND (row_end>'12:15 || row_start=row_end)"


James : Wiki : Book : Blog : Twitter
Previous Topic:(no subject)
Next Topic:H2 Table Creation on glassfish startup
Goto Forum:
  


Current Time: Sat Sep 20 12:10:36 GMT 2014

Powered by FUDForum. Page generated in 0.01871 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software