6 Replies Latest reply: May 4, 2012 4:15 AM by Nick . RSS

    Postgres db script for manually creating revision table and audit table

    Nick . Novice

      Can any of you please tell me, what will be postgres db script for manually creating revinfo and xyz_aud tables. And what are the things to be remembered if we are not using automatic table creation which is done by envers.

        • 1. Re: Postgres db script for manually creating revision table and audit table
          Vyacheslav Sakhno Newbie

          on each alter of data table(adding removing columns) you must alter you aud table. I've created an sql file for manual creating audit tables and revinfo table by requesting ddl script from database after hbm2ddl.auto script run.

          • 2. Re: Postgres db script for manually creating revision table and audit table
            Nick . Novice

            Can you please share that sql file.

            • 3. Re: Postgres db script for manually creating revision table and audit table
              Vyacheslav Sakhno Newbie

              ALTER TABLE REVISIONS

              DROP PRIMARY KEY CASCADE;

               

               

              DROP TABLE REVISIONS CASCADE CONSTRAINTS;

               

               

              CREATE TABLE REVISIONS

              (

                ID         NUMBER(10)                         NOT NULL,

                TIMESTAMP  TIMESTAMP(6),

                USERNAME   VARCHAR2(255 CHAR)

              )

              TABLESPACE USERS

              PCTUSED    0

              PCTFREE    10

              INITRANS   1

              MAXTRANS   255

              STORAGE    (

                          INITIAL          64K

                          NEXT             1M

                          MINEXTENTS       1

                          MAXEXTENTS       UNLIMITED

                          PCTINCREASE      0

                          BUFFER_POOL      DEFAULT

                         )

              LOGGING

              NOCOMPRESS

              NOCACHE

              NOPARALLEL

              MONITORING;

               

               

               

               

              --  There is no statement for index SYS_C0036213.

              --  The object is created automatically by Oracle when the parent object is created.

               

               

              ALTER TABLE REVISIONS ADD (

                PRIMARY KEY

                (ID)

                USING INDEX

                  TABLESPACE USERS

                  PCTFREE    10

                  INITRANS   2

                  MAXTRANS   255

                  STORAGE    (

                              INITIAL          64K

                              NEXT             1M

                              MINEXTENTS       1

                              MAXEXTENTS       UNLIMITED

                              PCTINCREASE      0

                             ));

               

              DROP SEQUENCE REVISIONS_SEQ;   

               

               

              CREATE SEQUENCE REVISIONS_SEQ

                START WITH 261

                MAXVALUE 999999999999999999999999999

                MINVALUE 1

                NOCYCLE

                CACHE 20

                NOORDER;

               

              .... example of domain table aud table.

               

               

               

              ALTER TABLE PROPERTY_USER_AUD

              DROP PRIMARY KEY CASCADE;

               

               

              DROP TABLE PROPERTY_USER_AUD CASCADE CONSTRAINTS;

               

               

              CREATE TABLE PROPERTY_USER_AUD

              (

                PROPERTY_ID  NUMBER(19)                       NOT NULL,

                USER_ID      NUMBER(19)                       NOT NULL,

                REV          NUMBER(19)                       NOT NULL,

                REVTYPE      NUMBER(3)

              )

              TABLESPACE USERS

              PCTUSED    0

              PCTFREE    10

              INITRANS   1

              MAXTRANS   255

              STORAGE    (

                          INITIAL          64K

                          NEXT             1M

                          MINEXTENTS       1

                          MAXEXTENTS       UNLIMITED

                          PCTINCREASE      0

                          BUFFER_POOL      DEFAULT

                         )

              LOGGING

              NOCOMPRESS

              NOCACHE

              NOPARALLEL

              MONITORING;

               

               

               

               

              --  There is no statement for index SYS_C0043853.

              --  The object is created automatically by Oracle when the parent object is created.

               

               

              ALTER TABLE PROPERTY_USER_AUD ADD (

                PRIMARY KEY

                (PROPERTY_ID, USER_ID, REV)

                USING INDEX

                  TABLESPACE USERS

                  PCTFREE    10

                  INITRANS   2

                  MAXTRANS   255

                  STORAGE    (

                              INITIAL          64K

                              NEXT             1M

                              MINEXTENTS       1

                              MAXEXTENTS       UNLIMITED

                              PCTINCREASE      0

                             ));

               

               

              ALTER TABLE PROPERTY_USER_AUD ADD (

                CONSTRAINT FK72DA5746749A9E62

                FOREIGN KEY (REV)

                REFERENCES REVISIONS (ID));

               

               

               

              That's it. Actually i just read db scheme by some sql ide, for example toad, generated by hbm2ddl script.

              • 6. Re: Postgres db script for manually creating revision table and audit table
                Nick . Novice

                Can any one please give me fresh postgres script, the above one n't working for me.