How to generate REVISION_ID/REV_TYPE manually?

[shyenuganti]

I am working on WPS part of the a large application. The whole application uses Envers for auditing. But I found that I cant use Envers for auditing in WPS environment because of the design of our environment. So I am planning to do the audits manually in our environment. But I have to keep pace with the REVISION_ID's generated by Envers in the other parts of the application.

I should insert the REV_TYPE and REVISION_ID into the audit tables.

What is the algorithm/process used by Envers to generate the Revision_ID? If I have to generate the Revision_ID manually how can i do it? How can I notify the Envers about the REVISION_ID that I have used so that ENVERS will not use that ID for the next insert on the same batch from another application? What does ENVERS do if there is a conflict on REVISION_ID while inserting in the audit tables?

If I am not wrong, for the REVISION_TYPE field, the values are 0 for insert/add and 1 for modify. Correct me if I am wrong !!

Waiting for some help on this issue !!

[wallenborn]

REVISION TYPE is 0:insert, 1: update, 2: delete afaik. As to your other question: it depends on your database and the particular RevisionEntity setup your application uses. The RevisionEntity id is by default

    @Id

    @GeneratedValue

    @RevisionNumber

    private int id;

where the default for @GeneratedValue is GenerationType.AUTO, which means you get whatever identity mechanism your underlying database has. If that's PostgreSQL you'd have

create table revinfo (

  id serial not null primary key,

  ..);

and in DB2 you'd have something like

create table revinfo (

  id bigint not null generated always as identity primary key,

..);

If you want to manually add audit entries to the history table you have to create an entry manually (don't forget that you have to set a timestamp), retrieve its ID, and add entries to the appropriate history tables. In DB2 this would look like

update foo set bar='baz' where id=42;

insert into revinfo (timestamp) values (sysdate);

then retrieve the revinfo_id with 'values identity_val_local();' and then something like:

insert into foo_rev select $revinfo_id, '1', * from foo;

Again, the particular implementation would depend on the details of your database setup, but that's the general line.

[shyenuganti]

Thank You Ernst. But I have another question.

But most of the times for the insert audits, the audit tables are populated with a REVISION_ID = 1. But not always ! Is there anything like the revision_id=1 for insert audits wherever possible ?

[wallenborn]

REVISION_ID is supposed to be a foreign key pointing to a transaction entry in the REVINFO table, so for each entity, you can answer questions like: what value did this entity have at a given point in time? Who changed this entity yesterday? What other entities were changed in the same transaction? If you set REVISION_ID to 1 for a lot of inserts, your transaction history won't make much sense anymore.

Why do your audit tables have a REVISION_ID=1? Was the database populated in one fell swoop and hence, one transaction? (that would make sense) or is that just some sort of default value?

[shyenuganti]

I think now I got the problem. Probably the database should have been populated all at once using some script. Because this is a new application and we dont have any data in the DB earlier. This was created overnight for our testing purposes.

Thank You. That helps.