Liquibase - envers inserts data in audit table

Question

I am developing a spring-boot (2.4.5) application with envers to audit data. When I am using the app from scratch, all is fine. From app, I can save data in database tables, and envers is storing audit data in the audit tables.

To database CI/CD, I have configured Liquibase, executing a changeset. The database is created without any issue.

But my problem is when I want to add inserts sentences in a changeset to create initial data. How can I add these initial data to audit tables?

My change set:

-- changeset puser:1659003040757-1

CREATE SEQUENCE HIBERNATE_SEQUENCE START WITH 1 MAXVALUE 9999999999999999999999999999;

-- changeset puser:1659003040757-2
--preconditions onFail:WARN onError:WARN
--precondition-sql-check expectedResult:0 SELECT count(*) FROM USER_TABLES WHERE TABLE_NAME = 'AUDIT_REVISION_ENTITY';

CREATE TABLE AUDIT_REVISION_ENTITY
(
    ID         NUMBER(10, 0) NOT NULL,
    TIMESTAMP  NUMBER(19, 0) NOT NULL,
    IP_ADDRESS VARCHAR2(255 CHAR),
    USERNAME   VARCHAR2(255 CHAR),
    CONSTRAINT SYS_C0017210 PRIMARY KEY (ID)
) TABLESPACE DATAAPP;

-- changeset puser:1659003040757-3

CREATE TABLE USERS
(
    ID            RAW(16)            NOT NULL,
    CREATED_BY    VARCHAR2(255 CHAR),
    CREATED_DATE  TIMESTAMP(6)       NOT NULL,
    MODIFIED_BY   VARCHAR2(255 CHAR),
    MODIFIED_DATE TIMESTAMP(6),
    ACTIVE        NUMBER(1, 0),
    EMAIL         VARCHAR2(200 CHAR) NOT NULL,
    USER_LOGIN    VARCHAR2(20 CHAR)  NOT NULL,
    USER_NAME     VARCHAR2(200 CHAR) NOT NULL,
    USER_PASSWORD VARCHAR2(256 CHAR) NOT NULL,
    USER_ROLE     VARCHAR2(50 CHAR)  NOT NULL,
    USER_SURNAME  VARCHAR2(200 CHAR) NOT NULL,
    CONSTRAINT SYS_C0017439 PRIMARY KEY (ID)
) TABLESPACE DATAAPP;

-- changeset puser:1659003040757-4
CREATE TABLE USERS_AUD
(
    ID            RAW(16)       NOT NULL,
    REV           NUMBER(10, 0) NOT NULL,
    REVTYPE       NUMBER(3, 0),
    CREATED_BY    VARCHAR2(255 CHAR),
    CREATED_DATE  TIMESTAMP(6),
    MODIFIED_BY   VARCHAR2(255 CHAR),
    MODIFIED_DATE TIMESTAMP(6),
    ACTIVE        NUMBER(1, 0),
    EMAIL         VARCHAR2(200 CHAR),
    USER_LOGIN    VARCHAR2(20 CHAR),
    USER_NAME     VARCHAR2(200 CHAR),
    USER_PASSWORD VARCHAR2(256 CHAR),
    USER_ROLE     VARCHAR2(50 CHAR),
    USER_SURNAME  VARCHAR2(200 CHAR),
    CONSTRAINT SYS_C0017442 PRIMARY KEY (ID, REV)
) TABLESPACE DATAAPP;

-- changeset puser:1659003040757-5
ALTER TABLE USERS_AUD
    ADD CONSTRAINT FKLD7CDNHID45YC6535CECSHYOP FOREIGN KEY (REV) REFERENCES AUDIT_REVISION_ENTITY (ID);

-- changeset puser:1659003040757-6
INSERT INTO users (id, user_surname, user_name, user_login, user_password, user_role, email, created_by, created_date, active)
VALUES ('cca76b85f24d490b8df7fd8d91743835', 'APPUSER', 'APPUSER', 'APPUSER', '$2a$10$9wXu9hshOrtZ7RopythgF.XP93XbKtISBzv6QjTGBzq', 'ADMIN', 'app@appsuser.com', 'System', current_date, 1);

My question is how do the changeset to insert data in envers audit table?

INSERT INTO users_aud ???????????????????????????????```

Hibernate envers, will insert into audit table if you use hibernate. When you execute a changeset with sql, this happens outside of hibernate involvement and as you have observed no audit data is stored. If you want to insert data into audit table through hibernate, you can use Liquibase custom changeset with java as the change type. It might need more steps depending on how your project is set up. We have a similar need, we had a handler on tomcat startup, where we injected all the autowired dependencies (repositories etc), had a custom changeset defined and executed them. — Neeraj, Aug 01 '22 at 16:53

score 0 · Answer 1 · edited Aug 09 '22 at 06:06

Thanks to @Neeraj's comment, I could finally fix with three inserts.

INSERT INTO users (id, user_surname, user_name, user_login, user_password, user_role, email, created_by, created_date, active)
VALUES ('cca76b85f24d490b8df7fd8d91743835', 'user', 'user', 'user', '$TGBzq', 'ADMIN', 'user@company.com', 'system', current_date, 1);

INSERT INTO AUDIT_REVISION_ENTITY (TIMESTAMP, IP_ADDRESS, USERNAME, ID)
VALUES (1659347410125, '0.0.0.0', 'System', HIBERNATE_SEQUENCE.NEXTVAL);

INSERT INTO USERS_AUD (ID, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, ACTIVE, USER_SURNAME, USER_NAME, USER_LOGIN, USER_PASSWORD, USER_ROLE, EMAIL, REV, REVTYPE)
SELECT 'cca76b85f24d490b8df7fd8d91743835',
       'user',
       current_date,
       'user',
       current_date,
       1,
       'user',
       'user',
       'user',
       '$2jTGBzq',
       'ADMIN',
       'user@company.com',
       rev,
       0
FROM (SELECT MAX(ID) AS REV FROM AUDIT_REVISION_ENTITY);

For a few number of inserts, this is a quick solution; for a large amount of data, I think that using a custom changeset is better solution.

Liquibase - envers inserts data in audit table

1 Answers1