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.