我如何将 undo更改函数到mysql数据库,就像删除/移动/标记电子邮件时的gmail一样。
到目前为止,我有一个系统日志表,该表保留了用户执行的确切的SQL语句。
例如,我正在尝试转换:
INSERT INTO table (id, column1, column2) VALUES (1,'value1', 'value2')
进入:
DELETE FROM table WHERE id=1, column1='value1', column2='value2'
是否有内置函数可以像Cisco Routers命令一样执行此操作,例如
(NO|UNDO|REVERT) INSERT INTO table (id, column1, column2) VALUES (1,'value1', 'value2')
也许我的方法不正确,我应该保存行的当前状态和更改的行以返回其原始状态吗?
类似:
original_query = INSERT INTO table (id, column1, column2) VALUES (1,'value1', 'value2')
executed_query = INSERT INTO table (id, column1, column2) VALUES (1,'change1', 'change2')
以后转换为:
INSERT INTO table (id, column1, column2) VALUES (1,'value1', 'value2') ON DUPLICATE KEY UPDATE
column1=VALUES(column1), column2=VALUES(column2)
,但也许它不会与新插入的行一起使用,或者如果我修改主键,可能会引起麻烦,这样我宁愿让它们不变。
这是我的日志表:
CREATE TABLE `log` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT ,
`date` datetime NOT NULL ,
`user` int(11) NOT NULL,
`client` text COMMENT ,
`module` int(11) unsigned NOT NULL ,
`query` text NOT NULL ,
`result` tinyint(1) NOT NULL ,
`comment` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
目的就像我说的那样,根据声明执行日期,撤消从某些时期变化,例如(可以在PHP中)
function_undo(startdate, enddate)
{
RESULT = SELECT query FROM log WHERE date BETWEEN startdate AND endate
FOR EACH RESULT AS KEY - query
REVERT query
}
或一个撤消按钮来恢复一个单个动作(单登录查询)。
这是我对这种"增量备份更改"正确的概念,还是我过度复杂化了一切?考虑到明显的事实,即如果我存储完整的查询,我的数据库的大小将是双重或可能是tripple。我应该将其存储在其他数据库中吗?或者,一旦我进行完整的备份以保持最新更改,请简单地删除日志表?
欢迎任何建议...
这总是有问题的,SQL 2012解决了这个问题。时间模型很简单:添加间隔列(有效_from,有效_TO),但是实现约束非常复杂。模型操作也很简单:
1.插入 - 新版本有效_from =现在,valit_to = null2.更新 - 新版本有效_from =现在,valit_to = null,更新上一个版本valit_to =现在3.删除 - 更新当前版本valit_to =现在4.撤消删除 - 更新最后版本valit_to = null5.撤消更新/插入 - 删除当前版本,如果您不需要重做并更新valit_to = null如果以前的版本退出
重做更为复杂,但它相似,通常此模型在数据仓库中用于跟踪更改而不是重做功能,但对于重做也应该很好。它也被称为数据仓库中的尺寸缓慢。
我认为您需要记录每个插入/update/delete查询的反面,然后执行它们进行撤消。这是您的解决方案,但这并不考虑外国关键关系(级联操作)。这只是一个简单的解决方案概念。希望它能给您更多的想法。在这里:
假设您有这样的表格,您想撤消
create table if not exists table1
(id int auto_increment primary key, mydata varchar(15));
这是记录反向查询的表
create table if not exists undoer(id int auto_increment primary key,
undoquery text , created datetime );
创建用于插入更新和删除操作的触发器,以保存反向/救援查询
create trigger after_insert after insert on table1 for each row
insert into undoer(undoquery,created) values
(concat('delete from table1 where id = ', cast(new.id as char)), now());
create trigger after_update after update on table1 for each row
insert into undoer(undoquery,created) values
(concat('update table1 set mydata = '',old.mydata,
'' where id = ', cast(new.id as char)), now());
create trigger after_delete after delete on table1 for each row
insert into undoer(undoquery,created) values
(concat('insert into table1(id,mydata)
values(',cast(old.id as char), ', '',old.mydata,'') '), now());
要撤消,您可以从desc顺序按日期排序的日期之间执行反向查询
最好的解决方案是在数据库表,通常是一个名为" IS_DEATED"的列和" dateTime_deleted",当用户删除时填充自动填充。
删除完成后,响应包括记录的ID-填充链接调用撤消方法的链接,用户可以单击,该方法只需通过再次更新数据库即可解开记录。
然后,您可以操作用户执行的作业,或者在计划的任务上执行的作业,以在一段时间内清理所有标记为" is_deleted = 1"的数据。
我认为这里需要采用多种技术...
您可以在一定时间后实施一个队列系统,该系统在一定时间后执行作业(发送电子邮件等)。
例如,如果用户删除对象,则将其发送给队列30秒左右,只需将用户单击撤消即可。如果用户确实单击撤消,则可以简单地从队列中删除作业。
这种结合使用软删除可能是一个很好的选择。
我已经使用了laravels队列类,这真的很好。
我不确定是否有正确的答案,因为没有正确的方法。好运:)
我建议您使用下表类似的内容来记录数据库的更改。
TABLE audit_entry_log
-- This is an audit entry log table where you can track changes and log them here.
( audit_entry_log_id INTEGER PRIMARY KEY
, audit_entry_type VARCHAR2(10) NOT NULL
-- Stores the entry type or DML event - INSERT, UPDATE or DELETE.
, table_name VARCHAR2(30)
-- Stores the name of the table which got changed
, column_name VARCHAR2(30)
-- Stores the name of the column which was changed
, primary_key INTEGER
-- Stores the PK column value of the row which was changed.
-- This is to uniquely identify the row which has been changed.
, ts TIMESTAMP
-- Timestamp when the change was made.
, old_number NUMBER(36, 2)
-- If the changed field was a number, the old value should be stored here.
-- If it's an INSERT event, this would be null.
, new_number NUMBER(36,2)
-- If the changed field was a number, the new value in it should be stored here.
-- If it's a DELETE statement, this would be null.
, old_text VARCHAR2(2000)
-- Similar to old_number but for a text/varchar field.
, new_text VARCHAR2(2000)
-- Similar to new_number but for a text/varchar field.
, old_date VARCHAR2(2000)
-- Similar to old_date but for a date field.
, new_date VARCHAR2(2000)
-- Similar to new_number but for a date field.
, ...
, ... -- Any other data types you wish to include.
, ...
);
现在,假设您有这样的桌子:
TABLE user
( user_id INTEGER PRIMARY KEY
, user_name VARCHAR2(50)
, birth_date DATE
, address VARCHAR2(50)
)
在此表上,我有一个触发器,可以填充audit_entry_log
跟踪该表的更改。我为Oracle提供了此代码示例,您肯定可以对Mysql进行一些调整:
CREATE OR REPLACE TRIGGER user_id_trg
BEFORE INSERT OR UPDATE OR DELETE ON user
REFERENCING new AS new old AS old
FOR EACH ROW
BEGIN
IF INSERTING THEN
IF :new.user_name IS NOT NULL THEN
INSERT INTO audit_entry_log (audit_entry_type,
table_name,
column_name,
primary_key,
ts,
new_text)
VALUES ('INSERT',
'USER',
'USER_NAME',
:new.user_id,
current_timestamp(),
:new.user_name);
END IF;
--
-- Similar code would go for birth_date and address columns.
--
ELSIF UPDATING THEN
IF :new.user_name != :old.user_name THEN
INSERT INTO audit_entry_log (audit_entry_type,
table_name,
column_name,
primary_key,
ts,
old_text,
new_text)
VALUES ('INSERT',
'USER',
'USER_NAME',
:new.user_id,
current_timestamp(),
:old.user_name,
:new.user_name);
END IF;
--
-- Similar code would go for birth_date and address columns
--
ELSIF DELETING THEN
IF :old.user_name IS NOT NULL THEN
INSERT INTO audit_entry_log (audit_entry_type,
table_name,
column_name,
primary_key,
ts,
old_text)
VALUES ('INSERT',
'USER',
'USER_NAME',
:new.user_id,
current_timestamp(),
:old.user_name);
END IF;
--
-- Similar code would go for birth_date and address columns
--
END IF;
END;
/
现在,考虑一个简单的例子,您可以在时间戳31-JAN-2014 14:15:30
上运行此查询:
INSERT INTO user (user_id, user_name, birth_date, address)
VALUES (100, 'Foo', '04-JUL-1995', 'Somewhere in New York');
接下来,您在时间戳31-JAN-2014 15:00:00
上运行UPDATE
查询:
UPDATE user
SET username = 'Bar',
address = 'Somewhere in Los Angeles'
WHERE user_id = 100;
因此,您的user
表将具有数据:
user_id user_name birth_date address
------- --------- ----------- --------------------------
100 Bar 04-JUL-1995 Somewhere in Los Angeles
这将在AUDIT_ENTRY_LOG表中产生以下数据:
audit_entry_type table_name column_name primary_key ts old_text new_text old_date new_date
---------------- ---------- ----------- ----------- -------------------- --------------------- ------------------------ -------- -----------
INSERT USER USER_NAME 100 31-JAN-2014 14:15:30 FOO
INSERT USER BIRTH_DATE 100 31-JAN-2014 14:15:30 04-JUL-1992
INSERT USER ADDRESS 100 31-JAN-2014 14:15:30 SOMEWHERE IN NEW YORK
UPDATE USER USER_NAME 100 31-JAN-2014 15:00:00 FOO BAR
UPDATE USER ADDRESS 100 31-JAN-2014 15:00:00 SOMEWHERE IN NEW YORK SOMEWHERE IN LOS ANGELES
创建一个类似以下过程的过程,该过程将接受表名和时间戳,我们必须在其中还原特定的表名称。该桌子只能恢复到时间戳。不会有时间戳。
CREATE OR REPLACE PROCEDURE restore_db (p_table_name varchar, p_to_timestamp timestamp)
AS
CURSOR cur_log IS
SELECT *
FROM audit_entry_log
WHERE table_name = p_table_name
AND ts > p_to_timestamp;
BEGIN
FOR i IN cur_log LOOP
IF i.audit_entry_type = 'INSERT' THEN
-- Delete the row that was inserted.
EXEC ('DELETE FROM '||p_table_name||' WHERE '||p_table_name||'_id = '||i.primary_key);
ELSIF i.audit_entry_type = 'UPDATE' THEN
-- Put all the old data back into the table.
IF i.old_number IS NOT NULL THEN
EXEC ('UPDATE '||p_table_name||' SET '||i.column_name||' = '||i.old_number
||' WHERE '||p_table_name||'_id = '||i.primary_key);
ELSIF i.old_text IS NOT NULL THEN
-- Similar statement as above EXEC for i.old_text
ELSE
-- Similar statement as above EXEC for i.old_text
END IF;
ELSIF i.audit_entry_type = 'DELETE' THEN
-- Write an INSERT statement for the row that has been deleted.
END IF;
END LOOP;
END;
/
现在,如果您想将user
表恢复到31-JAN-2014 14:30:00
处的状态 - 当发射INSERT
并且未触发UPDATE
时,这样的程序调用将做一个很好的joib:
restore_db ('USER', '31-JAN-2014 14:30:00');
我再次对此进行迭代 - 将上述所有代码视为伪代码,并在尝试运行它们时进行必要的更改。这是我在手动查询闪回中看到的最故障设计。
您是否考虑过将旧值传递到单独的表中,为XML值?然后,如果您需要还原它们,则可以从表中检索XML值。
对于这种系统,日志表是要走的方式。是的,桌子很可能很大,但这完全取决于您想要走多远。如您所说,您可以使用时间限制,并在6个月前删除所有日志。您还可以创建某种回收箱,并且不允许用户拥有的更多,而不是其中100个"项目" - 始终保留每个用户的最新日志条目。
关于要保存在日志表中的问题的问题,没有内置的功能可以让您完成想要的工作。但是,由于您仅记录更新和删除(不需要记录插入物,因为用户通常可以选择删除其内容),因此您可以轻松地构建自己的功能。
在任何更新或删除语句之前,您可以从数据库中获取整个行,并为其创建一个替换语句 - 它既可以作为更新和插入。唯一要记住的是,您需要所有桌子的主键或唯一索引。
这是有关该功能的外观的iDeea:
function translateStatement($table, $primaryKey, $id)
{
$sql = "SELECT * FROM `$table` WHERE `$primaryKey` = '$id'"; //should always return one row
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$columns = implode(',', array_map( function($item){ return '`'.$item.'`'; }, array_keys($row)) ); //get column names
$values = implode(',', array_map( function($item){ return '"'.mysql_real_escape_string($item).'"'; }, $row) ); //get escaped column values
return 'REPLACE INTO `$table` ('.$columns.') VALUES ('.$values.')';
}