Envers: What is the recommended way to save the current state of all entities in an existing database?

Hibernate ORM
1

We are introducing Hibernate Envers in our application.

Now, we want to store an initial revision of all out audited entities in the audit-tables. I thought I just could save the entities once via JPA, but Envers checks for modifications and does not save a new version.

I don’t want to make any arbitrary changes.

Is there a recommended way to create an initial version of all audited entities? Or force Envers to save a version even if there are no changes? Or something similar?

Or is SQL the only (and recommended?) way to do this?

1 Like
2

Why would you want to save the initial version if it is the same as the previous one? There should be no need for creating that, but if you want that, I think your only option is to write that with SQL directly or change something arbitrary.

3

There is no previous one. It is the “current” one. Data was imported with SQL loading and before we had Envers working.

So now, if I change something, the new version is saved in the audit-tables. But I have no way to find out the version before.

There is the need, definitely. And I am not the only one. But I did not find a nice solution for now.

We are using MySQL, what I came up with now, is, for each audited table (we save the user which changed the data, too, so we have a revinfo table):

set @i=(select max(id) from revinfo);

insert into table_aud (id, rev, revtype, ...all other columns)
select id,
	   @i + (ROW_NUMBER() over (ORDER BY id)) as rev,
	   0 as revtype,
	   ...all other columns
from table;

insert into revinfo
select rev, NOW() as timestamp, 'user' as username from table_aud
where rev > @i;

Not very nice, and it has to be done for any audited table, but works.

I had to increase the value in table hibernate_sequence, too.

update hibernate_sequence set next_val = (select max(id)+1 from revinfo);
4

False information removed.

5

Yes, I am sure. Envers stores the new, changed version of a row.

1 Like
6

You are definitely not the only one. Many projects start saving history after being in production and having the database populated.

That means that the RevisionType “ADD” never exists. When I first update an entry, the current state gets lost.

Would be nice if hibernate had a way to baseline the current db state

2 Likes
7

Do we still not have a solution for this?

8

Apparently nobody cares enough to develop this, so yeah, still no solution.

9

I did this in two steps: 1) Wrote some scratch code in the app that loaded all entities, type by type, appended “_aud” to updatedBy, cleared the versioning tables, then did another run to remove the “_aud” suffix from updatedBy (and thus again baselined the versioning tables). I was able to easily do this with the data framework we’ve developed; 2) Wrote a raw PostgreSQL script to populate the many-to-many join versioning tables, which of course did not get touched by 1).

Clunky and a bit fugly but it took me a couple of hours and it worked.

IMO, this should be essential functionality if you want a comprehensive ability to run both vertical and horizontal queries that will be valid starting on the date of baselining.