source: branches/1.1dev/lib/RecordVersion.inc.php

<?php
/**
 * The RecordVersion class provides a system for saving, reviewing, and 
 * restoring versions of a record of any DB table. All the data in the record is
 * serialized, compressed, and saved in a blob in the version_tbl. Restoring a
 * version simply does a REPLACE INTO of the data. It is very simple, and works
 * with multiple database tables, but the drawback is that relationships for
 * a record cannot be retained. For example, an article from an article_tbl can 
 * be saved, but not categories associated to the record in a category_article_tbl.
 * The restored article will simple retain the relationships that the previous
 * current article had.
 *
 * @author  Quinn Comendant <quinn@strangecode.com>
 * @requires App.inc.php
 * @version 1.0
 */

require_once dirname(__FILE__) . '/App.inc.php';

class RecordVersion {

    var $record_version_max_qty = 100; // Never have more than this many versions of each record.
    var $record_version_min_qty = 25; // Keep at least this many versions of each record.
    var $record_version_min_days = 7; // Keep ALL versions within this many days, even if MORE than record_version_min_qty.

    /**
     * Saves a version of the current record into the version table.
     *
     * @param string $record_table  The table containing the record.
     * @param string $record_key    The key column for the record.
     * @param string $record_val    The value of the key column for the record.
     * @param string $title         The title of this record. Only used for human presentation.
     *
     * @return int                  The id for the version (mysql last insert id).
     */
    function create($record_table, $record_key, $record_val, $title='')
    {
        global $_admin;
        
        // Get current record.
        if (!$record = $this->getCurrent($record_table, $record_key, $record_val)) {
            logMsg(sprintf('Could not create %s version, record not found: %s, %s, %s.', $title, $record_table, $record_key, $record_val), LOG_ERR, __FILE__, __LINE__);
            return false;
        }
                
        // Clean-up old versions.
        $this->deleteOld($record_table, $record_key, $record_val);
        
        // Save as new version.
        dbQuery("
            INSERT INTO version_tbl (
                record_table,
                record_key,
                record_val,
                version_data,
                version_title,
                saved_by_admin_id,
                version_datetime
            ) VALUES (
                '" . mysql_real_escape_string($record_table) . "',
                '" . mysql_real_escape_string($record_key) . "',
                '" . mysql_real_escape_string($record_val) . "',
                '" . mysql_real_escape_string(gzcompress(serialize($record), 9)) . "',
                '" . mysql_real_escape_string($title) . "',
                '" . mysql_real_escape_string($_admin->getVal('user_id')) . "',
                NOW()
            )
        ");

        return mysql_insert_id($GLOBALS['dbh']);
    }

    /**
     * Copy a version back into it's original table.
     *
     * @param string $version_id    The id of the version to restore.
     *
     * @return int                  The id for the version (mysql last insert id).
     */
    function restore($version_id)
    {
        // Get version data.
        $qid = dbQuery("
            SELECT * FROM version_tbl 
            WHERE version_id = '" . mysql_real_escape_string($version_id) . "'
        ");
        $record = mysql_fetch_assoc($qid);
        $data = unserialize(gzuncompress($record['version_data']));
        
        $qid = dbQuery("SHOW COLUMNS FROM " . mysql_real_escape_string($record['record_table']));
        while ($row = mysql_fetch_assoc($qid)) {
            $fields[] = $row['Field'];
        }
        $schema_diff = array_diff($fields, array_keys($data));
        if (sizeof($schema_diff) > 0 || sizeof($fields) != sizeof($data)) {
            raiseMsg(sprintf(_("Version ID %s%s is not compatable with the current database table."), $version_id, (empty($record['version_title']) ? '' : ' (' . $record['version_title'] . ')')), MSG_ERR, __FILE__, __LINE__);
            logMsg('Version restoration failed, DB schema change: (' . join(', ', $schema_diff) . ')', LOG_ERR, __FILE__, __LINE__);
            return false;
        }

        // Replace current record with specified version.
        dbQuery("
            REPLACE INTO " . $record['record_table'] . " (
                " . join(",\n", array_map('mysql_real_escape_string', array_keys($data))) . "
            ) VALUES (
                '" . join("',\n'", array_map('mysql_real_escape_string', $data)) . "'
            )
        ");
        
        return $record;
    }

    /**
     * Version garbage collection. Deletes versions older than record_version_min_days
     * when quantity of versions exceeds record_version_min_qty. If quantity
     * exceeds 100 within record_version_min_days, the oldest are deleted to bring the 
     * quantity back down to record_version_min_qty.
     *
     * @param string $record_table  The table containing the record.
     * @param string $record_key    The key column for the record.
     * @param string $record_val    The value of the key column for the record.
     *
     * @return mixed                Array of versions, or false if none.
     */
    function deleteOld($record_table, $record_key, $record_val)
    {
        // Get total number of versions for this record.
        $qid = dbQuery("
            SELECT COUNT(*) FROM version_tbl
            WHERE record_table = '" . mysql_real_escape_string($record_table) . "'
            AND record_key = '" . mysql_real_escape_string($record_key) . "'
            AND record_val = '" . mysql_real_escape_string($record_val) . "'
        ");
        list($v_count) = mysql_fetch_row($qid);
        
        if ($v_count > $this->record_version_min_qty) {
            if ($v_count > $this->record_version_max_qty) {
                // To prevent a record bomb, limit max number of versions to record_version_max_qty.
                $qid = dbQuery("
                    SELECT version_id FROM version_tbl
                    WHERE record_table = '" . mysql_real_escape_string($record_table) . "'
                    AND record_key = '" . mysql_real_escape_string($record_key) . "'
                    AND record_val = '" . mysql_real_escape_string($record_val) . "'
                    ORDER BY version_datetime ASC
                    LIMIT " . ($v_count - $this->record_version_min_qty) . "
                ");
                while (list($old_id) = mysql_fetch_row($qid)) {
                    $old_versions[] = $old_id;
                }
                dbQuery("
                    DELETE FROM version_tbl
                    WHERE version_id IN ('" . join("','", $old_versions) . "')
                ");
            } else {
                // Delete versions older than record_version_min_days, while still keeping record_version_min_qty.
                $qid = dbQuery("
                    SELECT version_id FROM version_tbl
                    WHERE record_table = '" . mysql_real_escape_string($record_table) . "'
                    AND record_key = '" . mysql_real_escape_string($record_key) . "'
                    AND record_val = '" . mysql_real_escape_string($record_val) . "'
                    AND DATE_ADD(version_datetime, INTERVAL '" . $this->record_version_min_days . "' DAY) < NOW()
                    ORDER BY version_datetime ASC
                    LIMIT " . ($v_count - $this->record_version_min_qty) . "
                ");
                while (list($old_id) = mysql_fetch_row($qid)) {
                    $old_versions[] = $old_id;
                }
                if (sizeof($old_versions) > 0) {
                    dbQuery("
                        DELETE FROM version_tbl
                        WHERE version_id IN ('" . join("','", $old_versions) . "')
                    ");
                }
            }
        }
    }

    /**
     * Get a list of versions of specified record.
     *
     * @param string $record_table  The table containing the record.
     * @param string $record_key    The key column for the record.
     * @param string $record_val    The value of the key column for the record.
     *
     * @return mixed                Array of versions, or false if none.
     */
    function getList($record_table, $record_key, $record_val)
    {
        // Get versions of this record.
        $qid = dbQuery("
            SELECT version_id, saved_by_admin_id, version_datetime, version_title
            FROM version_tbl
            WHERE record_table = '" . mysql_real_escape_string($record_table) . "'
            AND record_key = '" . mysql_real_escape_string($record_key) . "'
            AND record_val = '" . mysql_real_escape_string($record_val) . "'
            ORDER BY version_datetime DESC
        ");
        while ($row = mysql_fetch_assoc($qid)) {
            // Get admin usernames.
            $qid2 = dbQuery("SELECT username FROM admin_tbl WHERE admin_id = '" . mysql_real_escape_string($row['saved_by_admin_id']) . "'");
            list($row['editor']) = mysql_fetch_row($qid2);
            $versions[] = $row;
        }
        if (is_array($versions) && !empty($versions)) {
            return $versions;
        } else {
            return false;
        }
    }

    /**
     * Get the version record for a specified version id.
     *
     * @param string $version_id    The id of the version to restore.
     *
     * @return mixed                Array of data saved in version, or false if none.
     */
    function getVerson($version_id)
    {
        // Get version data.
        $qid = dbQuery("
            SELECT * FROM version_tbl 
            WHERE version_id = '" . mysql_real_escape_string($version_id) . "'
        ");
        return mysql_fetch_assoc($qid);
    }

    /**
     * Get the data stored for a specified version id.
     *
     * @param string $version_id    The id of the version to restore.
     *
     * @return mixed                Array of data saved in version, or false if none.
     */
    function getData($version_id)
    {
        // Get version data.
        $qid = dbQuery("
            SELECT * FROM version_tbl 
            WHERE version_id = '" . mysql_real_escape_string($version_id) . "'
        ");
        $record = mysql_fetch_assoc($qid);
        if (isset($record['version_data'])) {
            return unserialize(gzuncompress($record['version_data']));
        } else {
            return false;
        }
    }

    /**
     * Get the current record data from the original table.
     *
     * @param string $version_id    The id of the version to restore.
     *
     * @return mixed                Array of data saved in version, or false if none.
     */
    function getCurrent($record_table, $record_key, $record_val)
    {
        $qid = dbQuery("
            SELECT * FROM " . mysql_real_escape_string($record_table) . "
            WHERE " . mysql_real_escape_string($record_key) . " = '" . mysql_real_escape_string($record_val) . "'
        ");
        if ($record = mysql_fetch_assoc($qid)) {
            return $record;
        } else {
            return false;
        }
    }


} // End of class.
?>