Preventing Accidental Deletes

Murphy’s Law states whatever can go wrong will go wrong. So even with the appropriate logging and monitoring measures in place accidents are bound to happen. Even with warning and confirmation screens a legitimate user can still delete information they didn’t really intend to. The problem with DELETE statements is that they are irrecoverable.

One suggestion to prevent the unintentional deletion of data stored in a database is to add a new field to the records named IS_DELETED . The field is a TINYINT(1) which contains either a 0 or 1 to denote if the record is considered deleted . Your application would not issue any actual DELETE queries, rather it would set the field value to 1. It’s trivial to change the value of the field to restore the record in case of an accident.

Depending on the type of application you are developing, you may not want to have stale data in the database. To prevent deleted records from accumulating in the table you can write an administrative script that can run weekly (or even nightly) from cron or Scheduled Tasks to actually delete the records.The code below shows a script that I use.

A SHOW TABLES query is issued to retrieve a list of all tables in the database. For each table name that is returned, the column names are retrieved with a SHOW COLUMNS query and scanned to find any names that are named IS_DELETED . If so then a true DELETE query is issued otherwise the script moves on to analyze the next table.

#! /usr/bin/php
<?php
include ‘../lib/common.php’;
include ‘../lib/db.php’;
// retrieve list of tables
$table_result = mysql_query(‘SHOW TABLES’, $GLOBALS[‘DB’]);
while ($table_row = mysql_fetch_array($table_result))
{
    // retrieve list of column names in table
    $column_result = mysql_query(‘SHOW COLUMNS FROM ‘ . table_row[0], $GLOBALS[‘DB’]);
    while ($column_row = mysql_fetch_assoc($column_result))
    {
        // if the table has an IS_DELETED field then delete old records
        if ($column_row[‘Field’] == ‘IS_DELETED’)
        {
          mysql_query(‘DELETE FROM ‘ . $table_row[0] . ‘ WHERE ‘ . ‘IS_DELETED = 1’ , $GLOBALS[‘DB’]);
          // break out to process next table
          mysql_free_result($column_result);
          break;
        }
    }
    mysql_free_result($column_result);
}
mysql_free_result($table_result);
mysql_close($GLOBALS[‘DB’]);
?>