Adding Envers to an existing database

Question

I have a Hibernate-based app in production, with a large database. I need to add auditing to two entities (two tables) in this application, and I've decided to go with Envers.

For every INSERT, UPDATE or DELETE, Envers adds a new record to the entity's audit table.

If I had Envers support from the application's start, the audit tables would be populated at the time of the entities' creation (INSERT).

The Envers documentation is very thin, and doesn't mention anything about adding Envers to an existing application.

If I simply add Envers support and create the respective audit tables, they will start empty, so when I UPDATE an existing entity, Envers will add a record to the audit table recording the new values, but I'll lose the previous values.

How should I add Envers support to an application with an existing database?

Answer 1

There's no built-in solution for that currently.

The "correct" way would be to write an SQL script (or create manually) a "0" revision, together with insert audit records bound to that revision for each existing entity.

Actually, it's a quite commonly requested feature, so if you'd like to contribute, it would be most welcome!

Answer 2

You will need to do manual inserts. Something like

INSERT INTO z_envers_revisions (ID, timestamp, user_id, user_name) values (1, round((sysdate - to_date('19700101','YYYYMMDD')) * 86400000) , 42, 'UserName');

INSERT INTO z_Table1(rev, revtype, id, description, name) select 1 as rev, 0 as revtype, id, description, name from Table1;
INSERT INTO z_Table2(rev, revtype, id, description, name) select 1 as rev, 0 as revtype, id, description, name from Table2;

I have prefixed my audit tables with a z here to make it shorter