-
1. Re: Postgres db script for manually creating revision table and audit table
vyacheslav86 Jan 30, 2012 6:33 AM (in response to nick.sree)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.sree Feb 13, 2012 2:48 AM (in response to vyacheslav86)Can you please share that sql file.
-
3. Re: Postgres db script for manually creating revision table and audit table
vyacheslav86 Feb 13, 2012 3:49 AM (in response to nick.sree)1 of 1 people found this helpfulALTER 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.
-
4. Re: Postgres db script for manually creating revision table and audit table
nick.sree Feb 21, 2012 4:41 AM (in response to vyacheslav86)Is it a postgres sql script?
-
5. Re: Postgres db script for manually creating revision table and audit table
vyacheslav86 Feb 21, 2012 6:41 AM (in response to nick.sree)No. Oracle one.
-
6. Re: Postgres db script for manually creating revision table and audit table
nick.sree May 4, 2012 4:15 AM (in response to nick.sree)Can any one please give me fresh postgres script, the above one n't working for me.