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

    Postgres db script for manually creating revision table and audit table

    Nick . Apprentice

      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.

          • 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.

            1 of 1 people found this helpful
            • 6. Re: Postgres db script for manually creating revision table and audit table
              Nick . Apprentice

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