Adding Envers to an existing database
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?
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!
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 az here to make it shorter
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 :
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
上一篇: 用于聚合异构表格数据的设计模式
下一篇: 将Envers添加到现有数据库