带有审计表的数据更改历史记录:分组更改 [英] Data change history with audit tables: Grouping changes
问题描述
让我们说我想将用户和组存储在MySQL数据库中.它们之间的关系为n:m.为了跟踪所有更改，每个表都有一个审核表user_journal，group_journal和user_group_journal. MySQL触发器会在每个INSERT或UPDATE上将当前记录复制到日记表(不支持DELETES，因为我需要哪个应用程序用户已删除记录的信息-因此有一个标记active将被设置为0而不是删除).
Lets say I want to store users and groups in a MySQL database. They have a relation n:m. To keep track of all changes each table has an audit table user_journal, group_journal and user_group_journal. MySQL triggers copy the current record to the journal table on each INSERT or UPDATE (DELETES are not supported, because I would need the information which application user has deleted the record--so there is a flag active that will be set to 0 instead of a deletion).
我的问题/问题是:假设我一次将10个用户添加到一个组中.当我稍后在应用程序的用户界面中单击该组的历史记录时，我希望将这10个用户的添加作为一个步骤，而不是作为10个独立的步骤. 是否有很好的解决方案将这些更改组合在一起?也许每次触发都可以增加一个计数器?我从未使用过触发器.
My question/problem is: Assuming I am adding 10 users into a group at once. When I'm later clicking through the history of that group in the user interface of the application I want to see the adding of those 10 users as one step and not as 10 independent steps. Is there a good solution to group such changes together? Maybe it is possible to have a counter that is incremented each time the trigger is ... triggered? I have never worked with triggers.
最好的解决方案是将交易中进行的所有更改汇总在一起.因此，当用户更新组的名称并一步(一个表单控制器调用)添加10个用户时，这将是历史记录中的一步.也许每次事务启动并在触发器中访问该值时，都可以定义随机哈希或增加全局计数器?
The best solution would be to put together all changes made within a transaction. So when the user updates the name of the group and adds 10 users in one step (one form controller call) this would be one step in the history. Maybe it is possible to define a random hash or increment a global counter each time a transaction is started and access this value in the trigger?
我不想让表设计比每个真实"表都有一个日记表更复杂.我不想在每个数据库表中添加事务哈希(意味着真实"表，而不是审计表，当然可以).我也想在数据库中找到解决方案，而不是在应用程序中.
I don't want to make the table design more complex than having one journal table for each "real" table. I don't want to add a transaction hash into each database table (meaning the "real" tables, not the audit tables--there it would be okay of course). Also I would like to have a solution in the database--not in the application.
推荐答案
我玩了一段时间，现在我找到了一个很好的解决方案:
I played a bit around and now I found a very good solution:
# First of all I create the database and the basic table:
DROP DATABASE `mytest`;
CREATE DATABASE `mytest`;
USE `mytest`;
CREATE TABLE `test` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`something` VARCHAR(255) NOT NULL
);
# Then I add an audit table to the database:
CREATE TABLE `audit_trail_test` (
`_id` INT PRIMARY KEY AUTO_INCREMENT,
`_revision_id` VARCHAR(255) NOT NULL,
`id` INT NOT NULL,
`something` VARCHAR(255) NOT NULL
);
# I added a field _revision_id to it. This is
# the ID that groups together all changes a
# user made within a request of that web
# application (written in PHP). So we need a
# third table to store the time and the user
# that made the changes of that revision:
CREATE TABLE `audit_trail_revisions` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`user_id` INT NOT NULL,
`time` DATETIME NOT NULL
);
# Now we need a procedure that creates a
# record in the revisions table each time an
# insert or update trigger will be called.
DELIMITER $$
CREATE PROCEDURE create_revision_record()
BEGIN
IF @revision_id IS NULL THEN
INSERT INTO `audit_trail_revisions`
(user_id, `time`)
VALUES
(@user_id, @time);
SET @revision_id = LAST_INSERT_ID();
END IF;
END;
# It checks if a user defined variable
# @revision_id is set and if not it creates
# the row and stores the generated ID (auto
# increment) into that variable.
#
# Next I wrote the two triggers:
CREATE TRIGGER `test_insert` AFTER INSERT ON `test`
FOR EACH ROW BEGIN
CALL create_revision_record();
INSERT INTO `audit_trail_test`
(
id,
something,
_revision_id
)
VALUES
(
NEW.id,
NEW.something,
@revision_id
);
END;
$$
CREATE TRIGGER `test_update` AFTER UPDATE ON `test`
FOR EACH ROW BEGIN
CALL create_revision_record();
INSERT INTO `audit_trail_test`
(
id,
something,
_revision_id
)
VALUES
(
NEW.id,
NEW.something,
@revision_id
);
END;
$$
应用程序代码(PHP)
$iUserId = 42;
$Database = new \mysqli('localhost', 'root', 'root', 'mytest');
if (!$Database->query('SET @user_id = ' . $iUserId . ', @time = NOW()'))
die($Database->error);
if (!$Database->query('INSERT INTO `test` VALUES (NULL, "foo")'))
die($Database->error);
if (!$Database->query('UPDATE `test` SET `something` = "bar"'))
die($Database->error);
// To simulate a second request we close the connection,
// sleep 2 seconds and create a second connection.
$Database->close();
sleep(2);
$Database = new \mysqli('localhost', 'root', 'root', 'mytest');
if (!$Database->query('SET @user_id = ' . $iUserId . ', @time = NOW()'))
die($Database->error);
if (!$Database->query('UPDATE `test` SET `something` = "baz"'))
die($Database->error);
然后…结果
mysql> select * from test;
+----+-----------+
| id | something |
+----+-----------+
| 1 | baz |
+----+-----------+
1 row in set (0.00 sec)
mysql> select * from audit_trail_test;
+-----+--------------+----+-----------+
| _id | _revision_id | id | something |
+-----+--------------+----+-----------+
| 1 | 1 | 1 | foo |
| 2 | 1 | 1 | bar |
| 3 | 2 | 1 | baz |
+-----+--------------+----+-----------+
3 rows in set (0.00 sec)
mysql> select * from audit_trail_revisions;
+----+---------+---------------------+
| id | user_id | time |
+----+---------+---------------------+
| 1 | 42 | 2013-02-03 17:13:20 |
| 2 | 42 | 2013-02-03 17:13:22 |
+----+---------+---------------------+
2 rows in set (0.00 sec)
请让我知道我是否错过了一点.我必须在审核表中添加action列，才能记录删除.
Please let me know if there is a point I missed. I will have to add an action column to the audit tables to be able to record deletions.
这篇关于带有审计表的数据更改历史记录:分组更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
