Сохранение старого и нового состояния строки в JSON
Лучший способ сохранить старое и новое состояние строки — использовать столбцы JSON. Итак, для каждой таблицы, для которой вы хотите включить ведение журнала аудита, вы можете создать таблицу журнала аудита, например эту:
CREATE TABLE book_audit_log (
book_id BIGINT NOT NULL,
old_row_data JSON,
new_row_data JSON,
dml_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
dml_timestamp TIMESTAMP NOT NULL,
dml_created_by VARCHAR(255) NOT NULL,
PRIMARY KEY (book_id, dml_type, dml_timestamp)
)
- В столбце
book_id хранится идентификатор строки book, которая была создана, обновлена или удалена.
old_row_data — это столбец JSON, в котором фиксируется состояние записи book перед выполнением инструкции INSERT, UPDATE или DELETE.new_row_data — это столбец JSON, в котором фиксируется состояние записи book после выполнения инструкции INSERT, UPDATE или DELETE.dml_type — это столбец перечисления, в котором хранится тип оператора DML, который создал, обновил или удалил данную запись book.dml_timestamp хранит метку времени выполнения инструкции DML.- В
dml_created_by хранится информация о пользователе приложения, выполнившем инструкцию INSERT, UPDATE или DELETE DML.
Перехват инструкций INSERT, UPDATE и DELETE DML с использованием триггеров
Теперь, чтобы заполнить таблицы журнала аудита, вам нужно создать следующие 3 триггера:
CREATE TRIGGER book_insert_audit_trigger
AFTER INSERT ON book FOR EACH ROW
BEGIN
INSERT INTO book_audit_log (
book_id,
old_row_data,
new_row_data,
dml_type,
dml_timestamp,
dml_created_by
)
VALUES(
NEW.id,
null,
JSON_OBJECT(
"title", NEW.title,
"author", NEW.author,
"price_in_cents", NEW.price_in_cents,
"publisher", NEW.publisher
),
'INSERT',
CURRENT_TIMESTAMP,
@logged_user
);
END
CREATE TRIGGER book_update_audit_trigger
AFTER UPDATE ON book FOR EACH ROW
BEGIN
INSERT INTO book_audit_log (
book_id,
old_row_data,
new_row_data,
dml_type,
dml_timestamp,
dml_created_by
)
VALUES(
NEW.id,
JSON_OBJECT(
"title", OLD.title,
"author", OLD.author,
"price_in_cents", OLD.price_in_cents,
"publisher", OLD.publisher
),
JSON_OBJECT(
"title", NEW.title,
"author", NEW.author,
"price_in_cents", NEW.price_in_cents,
"publisher", NEW.publisher
),
'UPDATE',
CURRENT_TIMESTAMP,
@logged_user
);
END
CREATE TRIGGER book_delete_audit_trigger
AFTER DELETE ON book FOR EACH ROW
BEGIN
INSERT INTO book_audit_log (
book_id,
old_row_data,
new_row_data,
dml_type,
dml_timestamp,
dml_created_by
)
VALUES(
OLD.id,
JSON_OBJECT(
"title", OLD.title,
"author", OLD.author,
"price_in_cents", OLD.price_in_cents,
"publisher", OLD.publisher
),
null,
'DELETE',
CURRENT_TIMESTAMP,
@logged_user
);
END
Функция JSON_OBJECT MySQL позволяет нам создать объект JSON, который принимает предоставленные пары ключ-значение.
В столбце dml_type устанавливается значение INSERT, UPDATE или DELETE, а в столбце dml_timestamp устанавливается значение CURRENT_TIMESTAMP.
Столбец dml_created_by устанавливается на значение переменной сеанса @logged_user MySQL, которая ранее была установлена приложением с текущим зарегистрированным пользователем:
Session session = entityManager.unwrap(Session.class);
Dialect dialect = session.getSessionFactory()
.unwrap(SessionFactoryImplementor.class)
.getJdbcServices()
.getDialect();
session.doWork(connection -> {
update(
connection,
String.format(
"SET @logged_user = '%s'",
ReflectionUtils.invokeMethod(
dialect,
"escapeLiteral",
LoggedUser.get()
)
)
);
});
Время тестирования
При выполнении оператора INSERT для таблицы book:
INSERT INTO book (
id,
author,
price_in_cents,
publisher,
title
)
VALUES (
1,
'Vlad Mihalcea',
3990,
'Amazon',
'High-Performance Java Persistence 1st edition'
)
Мы видим, что в таблицу book_audit_log вставлена запись, которая фиксирует оператор INSERT, только что выполненный для таблицы book:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by |
|---------|--------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1 | | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-07-29 13:40:15 | Vlad Mihalcea |
При обновлении строки таблицы book:
UPDATE book
SET price_in_cents = 4499
WHERE id = 1
Мы видим, что новая запись будет добавлена в book_audit_log с помощью триггера AFTER UPDATE в таблице book:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by |
|---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1 | | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-07-29 13:40:15 | Vlad Mihalcea |
| 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE | 2020-07-29 13:50:48 | Vlad Mihalcea |
При удалении строки таблицы book:
DELETE FROM book
WHERE id = 1
Новая запись добавляется в book_audit_log триггером AFTER DELETE в таблице book:
| book_id | old_row_data | new_row_data | dml_type | dml_timestamp | dml_created_by |
|---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1 | | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT | 2020-07-29 13:40:15 | Vlad Mihalcea |
| 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE | 2020-07-29 13:50:48 | Vlad Mihalcea |
| 1 | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | | DELETE | 2020-07-29 14:05:33 | Vlad Mihalcea |
Вот и все!
person
Vlad Mihalcea
schedule
31.07.2020
