Function to revert sql statement

Question

How can I implement a undo changes function to mysql database, just like Gmail when you delete/move/tag an email.

So far I have a system log table that holds the exact sql statements executed by the user.

For example, I'm trying to transform:

INSERT INTO table (id, column1, column2) VALUES (1,'value1', 'value2')

into:

DELETE FROM table WHERE id=1, column1='value1', column2='value2'

is there a built in function to do this like the cisco routers commands, something like

(NO|UNDO|REVERT) INSERT INTO table (id, column1, column2) VALUES (1,'value1', 'value2')

Maybe my approach is incorrect, should i save the current state of my row and the changed row to get back to it's original state?.

something like:

original_query = INSERT INTO table (id, column1, column2) VALUES (1,'value1', 'value2')

executed_query = INSERT INTO table (id, column1, column2) VALUES (1,'change1', 'change2')

to later transform into:

INSERT INTO table (id, column1, column2) VALUES (1,'value1', 'value2') ON DUPLICATE KEY UPDATE
 column1=VALUES(column1), column2=VALUES(column2)

But maybe it won't work with newly inserted rows or can cause troubles if i modify the primary key so i will rather let them unchanged.

This is my log table:

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

The objective is like i said, undo changes from certain period of time based on the date of the statement execution, for example (can be in php)

function_undo(startdate, enddate)
{
    RESULT = SELECT query FROM log WHERE date BETWEEN startdate AND endate
    FOR EACH RESULT AS KEY - query
       REVERT query
}

or a undo button to revert one single action (single logged query ).

It's my concept of this 'incremental backup changes' correct or am I overcomplicating everything? Considering the obvious fact that the size of my database will be double or maybe tripple if I store the full queries. Should I store it in a different database ? or simply erase the log table once I make a programed full backup to only keep recent changes?

Any advices are welcome...

Answer 1

It was always problematic, SQL 2012 addresses this issue. Temporal model is simple: add interval columns (valid_from, valid_to ) but it is very complicated to implement constraints. Model manipulation is also simple:

1. insert -  new version valid_from=now, valit_to=null
2. update -  new version valid_from=now, valit_to=null, update previous version valit_to=now
3. delete -  update current version valit_to=now
4. undo delete - update last version valit_to=null 
5. undo update/insert - delete current version if you do not need redo and update valit_to=null if previous version exits 

It is more complicated with redo but it is similar, typically this model is used in data warehouse to track changes instead of redo function but it should be fine for redo too. It is also know as slowly changing dimension in data warehouse.

Answer 2

I think you need to record the reverse of each insert / update / delete queries and then perform them to do the undo. Here is a solution for you but this does not take foreign key relationships (cascade operations) into account. It is just a simple solution concept. Hopefully it will give you more ideas. Here it goes:

assume u have a table like this that you want to undo

create table if not exists table1 
(id int auto_increment primary key, mydata varchar(15));

here is the table that records reverse queries

create table if not exists undoer(id int auto_increment primary key,
 undoquery text ,  created datetime );

create triggers for insert update and delete operations that saves the reverse/rescue query

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());

to undo, you execute the reverse queries from undoer table between your dates sorted by date in desc order