Postgres db script for manually creating revision table and audit table

[nick.sree]

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.

[vyacheslav86]

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.

[nick.sree]

Can you please share that sql file.

[vyacheslav86]

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.

[nick.sree]

Is it a postgres sql script?

[vyacheslav86]

No. Oracle one.

[nick.sree]

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