How do you clone and compare tables in NHibernate?

Question

I have an application where I want to take a snapshot of all entities, creating cloned tables that represent a particular point in time. I then want to be able to compare the differences between these snapshots to see how the data evolves over time.

How would you accomplish this in NHibernate? It seems like NH isn't design for this type of data manipulation and I'm unsure if I'm abusing my database, NH, or both.

(P.S. Due to database engine restrictions I am unable to use views or stored procs.)

Answer 1

Do you really need to save the entirety of each entity in this snapshot? If so, maybe a collection of tables with names like type_snapshot would help. You could save your entities to this table (only inserting, never updating). You could store the original item's identifier, and generate a new identifier for the snapshot itself. And you could save the timestamp with each snapshot. Your item_snapshot table would look something like:

id | snapshot_date | item_id | item_prop1 | item_prop2 ...
123 | 7/16/10 | 15 | "item desc" | "item name" ...

Within your domain, maybe you could work with Snapshot instances (snapshot containing the id and the snapshot date, along with an instance of T)

It may not be ideal, as it'll introduce a second set of mappings, but it is a way to get where you're going. It seems like you might be better off doing something closer to the database engine, but without knowing what you have in mind for these snapshots (from application perspective) its its hard to say.

Answer 2

I wound up augmenting my entities with a snapshot id column and copying the entries in place in the table. Combined with a filter, I can select from any given snapshot. Had to make some patches to legacy code, but it basically works.

Answer 3

We wound up, creating duplicate tables with an extra column of type timestamp for snapshots. Made indexes on main table smaller, as we had 10Million + rows, so adding versions in same table would create many more records. Also version tables in different tablespace ( db file on mssql)