Safe DML Options

Permalink: http://bit.ly/VNLTQe



The Safe DML project provides automatic creation of backups and an undo functionality for MySQL. These two abilities do not rely on the command line shell and can simply be executed by queries.

$DML() Options

Inside the stored procedure, $DML(), you can find the following options that can be set:

-- Switches logging on/off
DECLARE logging BOOLEAN DEFAULT FALSE;
-- Clears the logs per call
DECLARE clearLogs BOOLEAN DEFAULT TRUE;
-- Set to FALSE to backup only the current db in use
DECLARE backupAllDB BOOLEAN DEFAULT TRUE;
-- Disables filtering out of unsupported statements
DECLARE dmlFilter BOOLEAN DEFAULT TRUE;

• The logging option enables/disables logs written by Safe DML into the `debug` table in the `$backup` database. Logging is useful for development work when adding new features or updating Safe DML. Disable for production use to improve performance.
• Additionally, you can set logs to clear for each time $DML() is used. Enable this option only when needed and only during development work since keeping this BOOLEAN set to TRUE can cause the `debug` table to consume too much disk space over time especially when used in a production environment.
• $DML() can be used for just the current database by setting this option to FALSE. This is also useful for keeping disk space consumption of backup tables low if only the current database needs an UNDO functionality.
• Statements not supported by Safe DML has an option to be filtered out from being passed. Only INSERT, UPDATE, DELETE, and REPLACE statements are currently supported. Since SELECT and SHOW statements do not modify data, you can pass these statements into $DML() if so desired but these will not be recorded in the `history` table inside the `$backup` database. 

Other statements that are allowed to be used inside transactions will be supported in a future version. Options on the size restriction on the maximum number of `history` table records and flushing schedule of table backups are under development.

See the change logs for updates on new features.

$UNDO() Options

For the $UNDO() stored procedure, you can also set its options for logging and clearLogs. To invoke an undo command, you can either use the UNDO keyword by calling the $DML() stored procedure or call the $UNDO() stored procedure directly.

To UNDO the last statement:

CALL $DML("UNDO");
-- or
CALL $DML("UNDO LAST STATEMENT");
-- or
CALL $UNDO('');
-- or
CALL $UNDO('LAST STATEMENT');

To UNDO all changes:

CALL $DML("UNDO ALL")
-- or
CALL $UNDO('ALL');

To UNDO multiple statements or to a specific point in time, first determine the commands you want to revert by parsing the `history` table for the id of the final command to be reverted:

SELECT * FROM `$backup`.`history`;
-- take note of the dmlStringId

Then call the desired stored procedures, where n is the dmlStringId of the final command to be reverted.

CALL $DML("UNDO n");
-- or
CALL $UNDO(n);

For example, there are 3 records in the `history` table with dmlStringIds 1, 2, and 3. The following call will have the effect of undoing the 3rd command in the record, then the 2nd command, in sequence, while keeping the 1st command with dmlStringId = 1, untouched:

CALL $DML("UNDO 2");

To be continued.