source: trunk/lib/Version.inc.php @ 354

<?php
/**
 * Version.inc.php
 * code by strangecode :: www.strangecode.com :: this document contains copyrighted information
 *
 * The Version 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>
 * @version 2.1
 */
class Version {

    // Configuration of this object.
    var $_params = array(
        'max_qty' => 100, // Never have more than this many versions of each record.
        'min_qty' => 25, // Keep at least this many versions of each record.
        'min_days' => 7, // Keep ALL versions within this many days, even if MORE than min_qty.
        'db_table' => 'version_tbl',

        // Automatically create table and verify columns. Better set to false after site launch.
        'create_table' => true,
        'db_schema_strict' => true, // If true, makes an exact comparison of saved vs. live table schemas. If false, just checks that the saved columns are available.
    );

    // Auth_SQL object from which to access a current user_id.
    var $_auth;

    /**
     * This method enforces the singleton pattern for this class.
     *
     * @return  object  Reference to the global Lock object.
     * @access  public
     * @static
     */
    function &getInstance($auth_object)
    {
        static $instance = null;

        if ($instance === null) {
            $instance = new Version($auth_object);
        }

        return $instance;
    }

    /**
     * Constructor. Pass an Auth object on which to perform user lookups.
     *
     * @param mixed  $auth_object  An Auth_SQL object.
     */
    function Version($auth_object)
    {
        $app =& App::getInstance();

        if (!method_exists($auth_object, 'get') || !method_exists($auth_object, 'getUsername')) {
            trigger_error('Constructor not provided a valid Auth_* object.', E_USER_ERROR);
        }

        $this->_auth = $auth_object;

        // Get create tables config from global context.
        if (!is_null($app->getParam('db_create_tables'))) {
            $this->setParam(array('create_table' => $app->getParam('db_create_tables')));
        }
    }

    /**
     * Setup the database table for this class.
     *
     * @access  public
     * @author  Quinn Comendant <quinn@strangecode.com>
     * @since   26 Aug 2005 17:09:36
     */
    function initDB($recreate_db=false)
    {
        $app =& App::getInstance();
        $db =& DB::getInstance();

        static $_db_tested = false;

        if ($recreate_db || !$_db_tested && $this->getParam('create_table')) {
            if ($recreate_db) {
                $db->query("DROP TABLE IF EXISTS " . $this->getParam('db_table'));
                $app->logMsg(sprintf('Dropping and recreating table %s.', $this->getParam('db_table')), LOG_INFO, __FILE__, __LINE__);
            }
            $db->query("CREATE TABLE IF NOT EXISTS " . $db->escapeString($this->getParam('db_table')) . " (
                version_id INT NOT NULL AUTO_INCREMENT,
                record_table VARCHAR(255) NOT NULL DEFAULT '',
                record_key VARCHAR(255) NOT NULL DEFAULT '',
                record_val VARCHAR(255) NOT NULL DEFAULT '',
                version_data MEDIUMBLOB NOT NULL,
                version_title VARCHAR(255) NOT NULL DEFAULT '',
                version_number SMALLINT(11) UNSIGNED NOT NULL DEFAULT '0',
                version_notes VARCHAR(255) NOT NULL DEFAULT '',
                saved_by_user_id SMALLINT(11) NOT NULL DEFAULT '0',
                version_datetime DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
                PRIMARY KEY (version_id),
                KEY record_table (record_table),
                KEY record_key (record_key),
                KEY record_val (record_val)
            )");

            if (!$db->columnExists($this->getParam('db_table'), array(
                'version_id',
                'record_table',
                'record_key',
                'record_val',
                'version_data',
                'version_title',
                'version_number',
                'version_notes',
                'saved_by_user_id',
                'version_datetime',
            ), false, false)) {
                $app->logMsg(sprintf('Database table %s has invalid columns. Please update this table manually.', $this->getParam('db_table')), LOG_ALERT, __FILE__, __LINE__);
                trigger_error(sprintf('Database table %s has invalid columns. Please update this table manually.', $this->getParam('db_table')), E_USER_ERROR);
            }
        }
        $_db_tested = true;
    }

    /**
     * Set the params of this object.
     *
     * @param  array $params   Array of param keys and values to set.
     */
    function setParam($params=null)
    {
        if (isset($params) && is_array($params)) {
            // Merge new parameters with old overriding only those passed.
            $this->_params = array_merge($this->_params, $params);
        }
    }

    /**
     * Return the value of a parameter, if it exists.
     *
     * @access public
     * @param string $param        Which parameter to return.
     * @return mixed               Configured parameter value.
     */
    function getParam($param)
    {
        $app =& App::getInstance();
    
        if (isset($this->_params[$param])) {
            return $this->_params[$param];
        } else {
            $app->logMsg(sprintf('Parameter is not set: %s', $param), LOG_DEBUG, __FILE__, __LINE__);
            return null;
        }
    }

    /**
     * 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='', $notes='')
    {
        $app =& App::getInstance();
        $db =& DB::getInstance();

        $this->initDB();

        // Get current record.
        if (!$record = $this->getCurrent($record_table, $record_key, $record_val)) {
            $app->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;
        }
        
        // Get previous version_number.
        $qid = $db->query("
            SELECT MAX(version_number) FROM " . $db->escapeString($this->getParam('db_table')) . "
            WHERE record_table = '" . $db->escapeString($record_table) . "'
            AND record_key = '" . $db->escapeString($record_key) . "'
            AND record_val = '" . $db->escapeString($record_val) . "'
        ");
        list($last_version_number) = mysql_fetch_row($qid);

        // Clean-up old versions.
        $this->deleteOld($record_table, $record_key, $record_val);

        // Save as new version.
        // TODO: after MySQL 5.0.23 is released this query could benefit from INSERT DELAYED.
        $db->query("
            INSERT INTO " . $db->escapeString($this->getParam('db_table')) . " (
                record_table,
                record_key,
                record_val,
                version_data,
                version_title,
                version_number,
                version_notes,
                saved_by_user_id,
                version_datetime
            ) VALUES (
                '" . $db->escapeString($record_table) . "',
                '" . $db->escapeString($record_key) . "',
                '" . $db->escapeString($record_val) . "',
                '" . $db->escapeString(gzcompress(serialize($record), 9)) . "',
                '" . $db->escapeString($title) . "',
                '" . $db->escapeString($last_version_number + 1) . "',
                '" . $db->escapeString($notes) . "',
                '" . $db->escapeString($this->_auth->get('user_id')) . "',
                NOW()
            )
        ");

        return mysql_insert_id($db->getDBH());
    }

    /**
     * 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)
    {
        $app =& App::getInstance();
        $db =& DB::getInstance();

        $this->initDB();

        // Get version data.
        $qid = $db->query("
            SELECT * FROM " . $db->escapeString($this->getParam('db_table')) . "
            WHERE version_id = '" . $db->escapeString($version_id) . "'
        ");
        if (!$record = mysql_fetch_assoc($qid)) {
            $app->raiseMsg(sprintf(_("Version ID %s%s not found."), $version_id, (empty($record['version_title']) ? '' : ' (' . $record['version_title'] . ')')), MSG_WARNING, __FILE__, __LINE__);
            $app->logMsg(sprintf('Version ID %s%s not found.', $version_id, (empty($record['version_title']) ? '' : ' (' . $record['version_title'] . ')')), LOG_WARNING, __FILE__, __LINE__);
            return false;
        }
        $data = unserialize(gzuncompress($record['version_data']));

        // Ensure saved db columns match current table schema.
        if (!$db->columnExists($record['record_table'], array_keys($data), $this->getParam('db_schema_strict'))) {
            $app->raiseMsg(sprintf(_("Version ID %s%s is not compatible with the current database table."), $version_id, (empty($record['version_title']) ? '' : ' (' . $record['version_title'] . ')')), MSG_ERR, __FILE__, __LINE__);
            $app->logMsg(sprintf('Version ID %s%s restoration failed, DB schema does not match for table %s.', $version_id, (empty($record['version_title']) ? '' : ' (' . $record['version_title'] . ')'), $record['record_table']), LOG_ALERT, __FILE__, __LINE__);
            return false;
        }

        // SQLize the keys of the specified versioned record.
        $replace_keys = join(",\n", array_map(array($db, 'escapeString'), array_keys($data)));

        // SQLize the keys of the values of the specified versioned record. (These are more complex because we need to account for SQL null values.)
        $replace_values = '';
        $comma = '';
        foreach ($data as $v) {
            $replace_values .= is_null($v) ? "$comma\nNULL" : "$comma\n'" . $db->escapeString($v) . "'";
            $comma = ',';
        }

        // Replace current record with specified versioned record.
        $db->query("
            REPLACE INTO " . $record['record_table'] . " (
                $replace_keys
            ) VALUES (
                $replace_values
            )
        ");

        return $record;
    }

    /**
     * Version garbage collection. Deletes versions older than min_days
     * when quantity of versions exceeds min_qty. If quantity
     * exceeds 100 within min_days, the oldest are deleted to bring the
     * quantity back down to 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)
    {
        $db =& DB::getInstance();
    
        $this->initDB();

        // Get total number of versions for this record.
        $qid = $db->query("
            SELECT COUNT(*) FROM " . $db->escapeString($this->getParam('db_table')) . "
            WHERE record_table = '" . $db->escapeString($record_table) . "'
            AND record_key = '" . $db->escapeString($record_key) . "'
            AND record_val = '" . $db->escapeString($record_val) . "'
        ");
        list($v_count) = mysql_fetch_row($qid);

        if ($v_count > $this->getParam('min_qty')) {
            if ($v_count > $this->getParam('max_qty')) {
                // To prevent a record bomb, limit max number of versions to max_qty.
                // First query for oldest records, selecting enough to bring total number down to min_qty.
                $qid = $db->query("
                    SELECT version_id FROM " . $db->escapeString($this->getParam('db_table')) . "
                    WHERE record_table = '" . $db->escapeString($record_table) . "'
                    AND record_key = '" . $db->escapeString($record_key) . "'
                    AND record_val = '" . $db->escapeString($record_val) . "'
                    ORDER BY version_datetime ASC
                    LIMIT " . ($v_count - $this->getParam('min_qty')) . "
                ");
                while (list($old_id) = mysql_fetch_row($qid)) {
                    $old_versions[] = $old_id;
                }
                $db->query("
                    DELETE FROM " . $db->escapeString($this->getParam('db_table')) . "
                    WHERE version_id IN ('" . join("','", $old_versions) . "')
                ");
            } else {
                // Delete versions older than min_days, while still keeping min_qty.
                $qid = $db->query("
                    SELECT version_id FROM " . $db->escapeString($this->getParam('db_table')) . "
                    WHERE record_table = '" . $db->escapeString($record_table) . "'
                    AND record_key = '" . $db->escapeString($record_key) . "'
                    AND record_val = '" . $db->escapeString($record_val) . "'
                    AND DATE_ADD(version_datetime, INTERVAL '" . $this->getParam('min_days') . "' DAY) < NOW()
                    ORDER BY version_datetime ASC
                    LIMIT " . ($v_count - $this->getParam('min_qty')) . "
                ");
                while (list($old_id) = mysql_fetch_row($qid)) {
                    $old_versions[] = $old_id;
                }
                if (sizeof($old_versions) > 0) {
                    $db->query("
                        DELETE FROM " . $db->escapeString($this->getParam('db_table')) . "
                        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)
    {
        $db =& DB::getInstance();
    
        $this->initDB();

        // Get versions of this record.
        $qid = $db->query("
            SELECT 
                version_id,
                saved_by_user_id,
                version_datetime,
                version_title,
                version_number,
                version_notes
            FROM " . $db->escapeString($this->getParam('db_table')) . "
            WHERE record_table = '" . $db->escapeString($record_table) . "'
            AND record_key = '" . $db->escapeString($record_key) . "'
            AND record_val = '" . $db->escapeString($record_val) . "'
            ORDER BY version_datetime DESC
        ");
        $versions = array();
        while ($row = mysql_fetch_assoc($qid)) {
            // Get admin usernames.
            $row['editor'] = $this->_auth->getUsername($row['saved_by_user_id']);
            $versions[] = $row;
        }
        return $versions;
    }

    /**
     * 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)
    {
        $db =& DB::getInstance();
    
        $this->initDB();

        // Get version data.
        $qid = $db->query("
            SELECT * FROM " . $db->escapeString($this->getParam('db_table')) . "
            WHERE version_id = '" . $db->escapeString($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)
    {
        $db =& DB::getInstance();
    
        $this->initDB();

        // Get version data.
        $qid = $db->query("
            SELECT * FROM " . $db->escapeString($this->getParam('db_table')) . "
            WHERE version_id = '" . $db->escapeString($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)
    {
        $db =& DB::getInstance();
    
        $this->initDB();

        $qid = $db->query("
            SELECT * FROM " . $db->escapeString($record_table) . "
            WHERE " . $db->escapeString($record_key) . " = '" . $db->escapeString($record_val) . "'
        ");
        if ($record = mysql_fetch_assoc($qid)) {
            return $record;
        } else {
            return false;
        }
    }


} // End of class.
?>