Modelling a temporal-style relational table in Hibernate

Question

Hi all,

I'm trying to wrap Hibernate around an existing data model, which as expected has its ups and downs.

My current sticking point is that one of the entity tables has a quasi-temporal model; no rows are ever deleted or updated; instead, the "is_current" column is set to false (and a new row is created with the new fields on an update, with a different primary key).

Being relatively new to Hibernate I'm struggling on how to model this, or even ascertain whether it's possible at all. Well, the deletes are simple enough with a custom @SQLDelete annotation, but it's the updates that seem tricky. Theoretically this seems like it could be quite a simple thing to do (in meta-code, @SQLUpdate(sql = @SQLDelete + "; " + @SQLInsert)) but there are obvious complications (asides from the fact that this syntax doesn't exist), partly around the fact that the primary key would need to be nullified in between the two statements and updated in the second, and I'm sure there are other data consistency issues that I haven't considered yet.

Is there a realistic way to manage this, i.e. model updates as delete + insert in Hibernate?

EDIT: Just to clarify, I'm aware that I could get this to work in a brute-force way, by explicitly specifying the insert SQL, and then using the above snippet (with legal references to SQL constants) to do the update. However, I don't really want to do this as I'm happy with the SQL Hibernate chooses to generate by default for the inserts, and writing this out by hand would be very brittle should the class change at all. It's a last-ditch solution to force this through, but it would seem to undermine the point of an ORM mapper somewhat if I have to write the SQL by hand...

DOUBLE EDIT: Even the above presumably won't work as I will need to specify the primary key bind parameter twice in the SQL (not possible with question marks), and I can't persuade Hibernate to go assign me a new one from the SequenceGenerator. So it looks like I'll need a programmatic approach here, rather than a configurational one - unless there's some particularly relevant config elements I've missed.

Answer 1

You say no row is updated, but it sounds like you are updating the is_current column on that row. I would just do the same thing using Hibernate (update the row's isCurrent field, then create a new row), instead of trying to write a custom delete function that is actually an update.

row1.setCurrent(false);
session.saveOrUpdate(row1);
row2 = new Row(row1); //Copy constructor; use a different primary key (or your default primary key for a new object)
row2.setCurrent(true);
row2.setFoo("bar"); //whatever data updates you are doing for the new row
session.saveOrUpdate(row2);

You can wrap this procedure in some middle tier/business logic code, to ensure that it no other fields get updated.

Answer 2

We have a fully temporal star schema that we use with Hibernate. However Hibernate never knows about the audit or fact tables but only views to the tables. This allows us to work with Hibernate as usual so when an insert,delete or update is issued by Hibernate on the view in the db "instead of" triggers are fired for these respective tasks.

So in your case create a view (Hibernate doesn't know any better and is totally transparent) and the triggers as appropriate to set your flags and whatever else. Actually I'm surprised how we got away with this with relatively little pain -- and our design really could be better as using Hibernate was an afterthought/add on.

Answer 3

Having looked into this further, I believe that there isn't going to be an easy way to do it. One approach I'm intrigued to try is to hide the raw table behind an updateable view, so that Hibernate can be presented with something that looks sane while the triggers on the view map simple CRUD operations to the sequence of manipulations required in the underlying table.

However, I am suspicious that this might not work too well, because I believe Hibernate maintains its own concept of the contents of the database at any point in time; and e.g. if I delete an entity, Hibernate's cache will think that physical row no longer exists in the table (while it does but with is_current now false). I would not be surprised if this mismatch could lead to either data consistency problems in the worst case, or perhaps inefficient batch optimisation.

The existing tables are horrible anyway, so making them saner in the context of this change is the approach I'm going to take. At least this evaluation stage has made some of the deficiencies of the existing model clearer.