10

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?

Daniel Serodio
  • 4,229
  • 5
  • 37
  • 33

3 Answers3

3

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!

adamw
  • 8,038
  • 4
  • 28
  • 32
  • Since revisions are global, and not local to a specific entity, I'm afraid that adding a "0" revision that affects all entities would break querying for previous revisions, since when I query for the "0" revision it would try to load the whole database, wouldn't it be a problem? – Daniel Serodio Mar 21 '13 at 16:36
  • @DanielSerodio Adamw is correct. You have to add revision 0 and start at that for your existing database. How can you find out previous revisions if there is no auditing currently in place? – RNJ Mar 21 '13 at 16:38
  • @RNJ since you replied quickly after my comment above, I'm not sure you saw that comment (about global revision). Do you think that would be a problem? – Daniel Serodio Mar 21 '13 at 16:40
  • @DanielSerodio I think you are right. But would you do this? Or would you query for a particular table at a given revision? That;s is how I am using it currently. – RNJ Mar 21 '13 at 20:15
  • @RNJ Sure, I won't query for "everything in rev. 0", but I'm afraid querying for "the previous revision of entity E" would try to fetch other entities too. I guess I'll have to give it a try in a test environment. – Daniel Serodio Mar 21 '13 at 22:25
  • Well, if you query for the previous revision of E, then you most likely will add an id-constraint, which will load only this one entity. You'll get the whole table (there's no option to load the whole database) if you query for the whole table ;) – adamw Mar 22 '13 at 17:03
  • I'm using spring with envers and I have the same problem. I think that a solution could be to create an Audited temp entity and then copy data from source table to audited entity. In this way envers recreate all revisions. Then the temp table could be deleted and we have a table with initial revisions. Could it be a workaround or I missing something? Thank you – gipinani Aug 30 '13 at 05:46
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

RNJ
  • 15,272
  • 18
  • 86
  • 131
0

As far as envers is concerned, the basic use case is to record the complete audit of an entity(the parameters we wish to via @Audited annotation). For the case which you are mentioning, new entities might be added properly but for existing it would give issue as there is no revision present in the audit table.

Let's solve the case with the help of a scenario :

Let's say the entity we have taken into consideration is users. The table which gets created now to observe the history, let's say, is users_audit. In addition to it revinfo also would be in place to observe and record all the changes wrt to a given record.

The issue in the first place comes, because whenever there is an update, the persistence layer is unable to find a revision record. So to fix it all the existing entries need to be present in the table and the foreign key mapping with the revinfo table should not break. Hence, two things are required to be done :

  1. Insert the temp values in the revinfo table so that the rev can act as a foreign key
  2. Copy the data from the users table to the users_audit table.

Sample Liquibase file can be like this :

    CREATE TABLE `revinfo` (
      `rev` int(11) NOT NULL AUTO_INCREMENT,
      `revtstmp` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`rev`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    INSERT INTO `revinfo` (`revtstmp`) select updated_at from users u;

    SET @position := 0;

    insert into users_audit (
    rev,
    revtype,
    id,
    name,
    type,
    mobile_number,
    password,
    parent_id,
    profile_image_uri,
    is_active,
    created_at,
    updated_at
    ) select @position := @position +1, 0,
    id,
    name,
    type,
    mobile_number,
    password,
    parent_id,
    profile_image_uri,
    is_active,
    created_at,
    updated_at from us
smutneja03
  • 51
  • 1
  • 4