On March 26, 2019 we launched new MODX Forums. Please join us at the new MODX Community Forums.
Subscribe: RSS
  • VersionX is great for backing up site content but with active sites the database can really fill up. One of my client sites had several thousand records and the table was over 40MB in size.

    Since an automatic housekeeping feature is not yet part of VersionX I created a snippet for manually deleting and cleaning up the database.

    To use it create a new snippet called versionCleanX and then add [[!versionCleanX? &contentType=`resource` &maxVersions=`10`]] to a page (with the resource group access set to Admin). The contentType variable can be set resource, chunk, plugin, snippet, template, or templatevar.

    <?php
    /**
     * versionCleanX
     *
     * @author Scott Pronych, September 27, 2013
     *
     * DESCRIPTION
     *
     * For use with VersionX to delete old content
     *
     * PROPERTIES:
     *
     * &contentType - can be resource, chunk, plugin, snippet, template, or templatevar
     * &maxVersions - integer value for max number of versions you would like to keep
     *
     * USAGE:
     *
     * [[!versionCleanX? &contentType=`resource` &maxVersions=`10`]]
     *
     */
    
    $cx_type = $modx->getOption('contentType', $scriptProperties, 'resource');
    $cx_max = (int) $modx->getOption('maxVersions', $scriptProperties, 5);
    
    $GLOBALS['count'] = 1;
    $GLOBALS['total'] = 0;
    $GLOBALS['deleted'] = 0;
    $GLOBALS['page_total'] = 0;
    $GLOBALS['page_deleted'] = 0;
    $GLOBALS['page_name'] = '';
    $GLOBALS['prev_content_id'] = 0;
    $GLOBALS['prev_version_id'] = 0;
    
    switch ($cx_type) {
        case 'chunk':
            $name = 'name';
            break;
        case 'plugin':
            $name = 'name';
            break;
        case 'snippet':
            $name = 'name';
            break;
        case 'template':
            $name = 'templatename';
            break;
        case 'templatevar':
            $name = 'name';
            break;
        default:
            $name = 'title';
            $cx_type = 'resource';
    }
    
    $GLOBALS['db_name'] = 'modx_versionx_' . $cx_type;
    
    function delete_row ($id) {
        global $modx;
        $query = "DELETE FROM `" . $GLOBALS['db_name'] . "` WHERE version_id = '" . $id . "'";
        $result = $modx->query($query);
        if (!is_object($result)) return false;
        else {
            $GLOBALS['deleted']++;
            $GLOBALS['page_deleted']++;
            $GLOBALS['page_total']++;
            $GLOBALS['total']++;
            $GLOBALS['count']++;
            return true;
        }
    }
    
    function log_row () {
        $GLOBALS['data'] .= '<tr><td>' . $GLOBALS['page_name'] . '</td><td>' . $GLOBALS['page_total'] . '</td><td>' . $GLOBALS['page_deleted'] . "</td></tr>\n";
        $GLOBALS['page_deleted'] = 0;
        $GLOBALS['page_total'] = 1;
        $GLOBALS['count'] = 1;
    }
    
    $query = "SELECT version_id, content_id, " . $name . " AS page_title FROM `" . $GLOBALS['db_name'] . "` ORDER BY content_id ASC, version_id DESC";
    
    $GLOBALS['data'] = '';
    $output = 'An error occurred: ';
    $versionx = $modx->query($query);
    if (!is_object($versionx)) {
       return $output . 'query error ' . print_r($modx->errorInfo(), true);
    }
    else {
    
    while($row = $versionx->fetch(PDO::FETCH_ASSOC)) {
        // New content_id so reset
        if ($prev_content_id == 0) {
            $prev_content_id = $row['content_id'];
            $prev_version_id = $row['version_id'];
            $GLOBALS['total']++;
            $GLOBALS['page_total']++;
            $GLOBALS['count']++;
            $GLOBALS['page_name'] = $row['page_title'];
        }
        elseif ($prev_content_id != $row['content_id']) {
            if ($GLOBALS['count'] > $cx_max) {
                if (!delete_row($prev_version_id)) return $output .  'deleting row for ' . $GLOBALS['page_name'] . ' Row: ' . $prev_content_id . ' ' . print_r($modx->errorInfo(), true);
                $GLOBALS['page_total']--;
    
            }
            else {
                $GLOBALS['total']++;
                $GLOBALS['count']++;
            }
            log_row();
            $prev_content_id = $row['content_id'];
            $prev_version_id = $row['version_id'];
            $GLOBALS['page_name'] = $row['page_title'];
        }
        // Content count is over the max so delete previous row
        elseif ($GLOBALS['count'] > $cx_max) {
                delete_row($prev_version_id);
                $prev_content_id = $row['content_id'];
                $prev_version_id = $row['version_id'];
        }
        else {
            $GLOBALS['count']++;
            $GLOBALS['page_total']++;
            $GLOBALS['total']++;
            $prev_content_id = $row['content_id'];
            $prev_version_id = $row['version_id'];
        }
    }
    log_row();
    
    if ($GLOBALS['data'] != '') {
    $output = '<h3>VersionX Cleanup for ' . $GLOBALS['db_name'] . '</h3>
    <p>Total records: <strong>' . $GLOBALS['total'] . '</strong><br>
    Total deleted: <strong>' . $GLOBALS['deleted'] . '</strong></p>
    <table class="table table-striped">
    <thead>
    <tr>
    <th>Page name</th>
    <th>Total found</th>
    <th>Deleted</th>
    </tr>
    </thead>
    <tbody>
    ' . $GLOBALS['data'] .  '</tbody></table>
    ';
    }
    else $output = 'Error: no data found.';
    }
    
    $query = "OPTIMIZE TABLE `" . $GLOBALS['db_name'] . "`";
    $versionx = $modx->query($query);
    if (!is_object($versionx)) {
       $output = 'Optimize error ' . print_r($modx->errorInfo(), true) . $output;
    }
    
    return $output;
    • Since an automatic housekeeping feature is not yet part of VersionX I created a snippet for manually deleting and cleaning up the database.

      This will be coming in the next release (along with some other cool new features), which I hope to get out before the end of the year wink

      Question though, why are you setting variables into $GLOBALS?
        Mark Hamstra • Developer spending his days working on Premium Extras and a MODX Site Dashboard with the ability to remotely upgrade MODX and extras to make the MODX world a little better.

        Tweet me @mark_hamstra, check my infrequent blog at markhamstra.com, my slightly more frequent ramblings at MODX.today or see code at Github.
      • Quote from: markh at Sep 27, 2013, 07:43 PM

        This will be coming in the next release (along with some other cool new features), which I hope to get out before the end of the year wink

        Question though, why are you setting variables into $GLOBALS?

        Good to hear. I look forward to seeing what you come up with (it is pretty slick already).

        And I had to use $GLOBALS since the variables weren't being recognized within my delete function call. I had tried declaring them as 'global' within the function which normally works for PHP but it would only work if I used the $GLOBALS notation. Not sure if it has to do with the version of PHP I am using or if it is because how MODX calls snippets (as a function?) but this was the only way I could get it to work. I tested on several different servers as well.
        • Is there an easier way to delete old version of a resource yet, or do I still need to install spronych's snippet?