4 Replies Latest reply on Jun 14, 2011 11:07 AM by Sri Harsha Yenuganti

    How to generate REVISION_ID/REV_TYPE manually?

    Sri Harsha Yenuganti Newbie

      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 !!

        • 1. Re: How to generate REVISION_ID/REV_TYPE manually?
          Ernst-Udo Wallenborn Newbie

          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.

          1 of 1 people found this helpful
          • 2. Re: How to generate REVISION_ID/REV_TYPE manually?
            Sri Harsha Yenuganti Newbie

            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 ?

            • 3. Re: How to generate REVISION_ID/REV_TYPE manually?
              Ernst-Udo Wallenborn Newbie

              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?

              • 4. Re: How to generate REVISION_ID/REV_TYPE manually?
                Sri Harsha Yenuganti Newbie

                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.

                1 of 1 people found this helpful