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  |
 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 #713537 is a reply to message #713525] |
Mon, 08 August 2011 15:23  |
Stefan Palme
Messages: 8
Registered: August 2011 |
Junior Member |
|
|
James wrote on Mon, 08 August 2011 11:08WHERE (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 |
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 #714825 is a reply to message #713596] |
Thu, 11 August 2011 15:04 |
|
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
|
|
|
Goto Forum:
Current Time: Fri Apr 19 05:03:45 GMT 2024
Powered by FUDForum. Page generated in 0.03019 seconds |
] 
Search
Help
Register
Login
Home
