Function to revert sql statement
database, mysql, php, sql, undo
Solution
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.
Problem
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...