VersionX переполнил базу данных

Сниппет для удаления старых версий:

<?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>
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;

Вызывать так:

[[!versionCleanX? &contentType=`resource` &maxVersions=`10`]]

Если записей слишком много и сервер не может обработать запрос то добавьте это. У меня уже под 200к версий и выпадало в ошибку

<?php ini_set('memory_limit', '256M');

А все-таки можно просто очистить таблицу)?

Если нет, то скажите, пожалуйста, куда добавлять?

[[!versionCleanX? &contentType=`resource` &maxVersions=`10`]]

Сделал новую версию с табами и возможностью запуска сразу для всех вариантов.
Сначала содержимое для технического ресурса откуда будет запускаться выполнение сниппета.

[[!versionCleanXTabs? &maxVersions=`2` &types=`chunk,resource,template,snippet,plugin,templatevar` &dryRun=`0` &optimize=`1`]]

Далее содержимое versionCleanXTabs:
— в описании Tabbed wrapper for versionCleanX cleanup results.
— в код:

<?php
/**
 * versionCleanXTabs
 *
 * Runs versionCleanX for several VersionX entity tables and shows results in tabs.
 *
 * PROPERTIES:
 * &types - comma-separated list: chunk,resource,template,snippet,plugin,templatevar
 * &maxVersions - integer max versions to keep per element
 * &dryRun - 1 to show what would be deleted without deleting rows
 * &optimize - 1 to run OPTIMIZE TABLE in versionCleanX
 */

$escape = function ($value) {
    return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
};

$toBool = function ($value) {
    return filter_var($value, FILTER_VALIDATE_BOOLEAN);
};

$typesRaw = (string)$modx->getOption('types', $scriptProperties, 'chunk,resource,template,snippet,plugin,templatevar');
$maxVersions = (int)$modx->getOption('maxVersions', $scriptProperties, 2);
$dryRun = $toBool($modx->getOption('dryRun', $scriptProperties, false));
$optimize = $toBool($modx->getOption('optimize', $scriptProperties, true));

$labels = [
    'chunk' => ['Чанки', 'versionx_chunk'],
    'resource' => ['Ресурсы', 'versionx_resource'],
    'template' => ['Шаблоны', 'versionx_template'],
    'snippet' => ['Сниппеты', 'versionx_snippet'],
    'plugin' => ['Плагины', 'versionx_plugin'],
    'templatevar' => ['TV-поля', 'versionx_templatevar'],
];

$types = [];
foreach (preg_split('/\s*,\s*/', $typesRaw, -1, PREG_SPLIT_NO_EMPTY) as $type) {
    $type = strtolower(trim($type));
    if (isset($labels[$type]) && !in_array($type, $types, true)) {
        $types[] = $type;
    }
}

if ($maxVersions < 1) {
    $maxVersions = 2;
}

if (empty($types)) {
    return '<p>versionCleanXTabs: не передан ни один поддерживаемый тип VersionX.</p>';
}

$uid = 'vcx-tabs-' . substr(md5(uniqid('', true)), 0, 10);

$css = <<<'VCX_STYLE'
<style>
.vcx-tool {
    max-width: 1180px;
    margin: 32px auto;
    padding: 0 18px 40px;
    color: #172033;
    font-family: Arial, sans-serif;
}
.vcx-tool h1 {
    margin: 0 0 12px;
    font-size: 34px;
    line-height: 1.15;
    font-weight: 600;
}
.vcx-tool__lead {
    max-width: 860px;
    margin: 0 0 22px;
    color: #536071;
    font-size: 16px;
    line-height: 1.55;
}
.vcx-tabs__nav {
    display: flex;
    flex-wrap: wrap;
    gap: 8px;
    margin: 24px 0 0;
    border-bottom: 1px solid #d8dee8;
}
.vcx-tabs__button {
    appearance: none;
    border: 1px solid #d8dee8;
    border-bottom: 0;
    border-radius: 8px 8px 0 0;
    background: #f4f7fb;
    color: #1b2a41;
    padding: 11px 15px 10px;
    min-width: 132px;
    text-align: left;
    cursor: pointer;
}
.vcx-tabs__button small {
    display: block;
    margin-top: 2px;
    color: #6b7585;
    font-size: 11px;
}
.vcx-tabs__button.is-active {
    background: #fff;
    color: #00458f;
    border-color: #c5d1df;
}
.vcx-tabs__panel {
    display: none;
    padding: 24px 0 0;
}
.vcx-tabs__panel.is-active {
    display: block;
}
.vcx-result {
    overflow-x: auto;
}
.vcx-result h3 {
    margin-top: 0;
}
.vcx-result table {
    width: 100%;
    border-collapse: collapse;
    margin-top: 14px;
}
.vcx-result th,
.vcx-result td {
    padding: 9px 10px;
    border-bottom: 1px solid #e5e9f0;
    text-align: left;
    vertical-align: top;
}
.vcx-result th {
    background: #f5f7fa;
    font-weight: 600;
}
.vcx-tool__note {
    margin-top: 18px;
    padding: 12px 14px;
    border-left: 4px solid #00458f;
    background: #f3f7fc;
    color: #3d4a5c;
}
@media (max-width: 680px) {
    .vcx-tool h1 {
        font-size: 27px;
    }
    .vcx-tabs__button {
        flex: 1 1 100%;
    }
}
</style>
VCX_STYLE;

$out = $css;
$out .= '<section class="vcx-tool" id="' . $escape($uid) . '">';
$out .= '<h1>VersionX cleanup</h1>';
$out .= '<p class="vcx-tool__lead">Служебная страница очистки промежуточных версий VersionX. При открытии страницы запускается очистка всех вкладок: остаются только ' . (int)$maxVersions . ' последние версии для выбранных сущностей.</p>';
if ($dryRun) {
    $out .= '<p class="vcx-tool__note"><strong>Dry run:</strong> записи не удаляются, показан только расчет.</p>';
}

$out .= '<div class="vcx-tabs">';
$out .= '<div class="vcx-tabs__nav" role="tablist" aria-label="VersionX cleanup tabs">';
foreach ($types as $index => $type) {
    $panelId = $uid . '-' . $type;
    $active = $index === 0 ? ' is-active' : '';
    $out .= '<button class="vcx-tabs__button' . $active . '" type="button" role="tab" aria-selected="' . ($index === 0 ? 'true' : 'false') . '" data-vcx-tab="' . $escape($panelId) . '">';
    $out .= '<span>' . $escape($labels[$type][0]) . '</span><small>_' . $escape($labels[$type][1]) . '</small>';
    $out .= '</button>';
}
$out .= '</div>';

foreach ($types as $index => $type) {
    $panelId = $uid . '-' . $type;
    $active = $index === 0 ? ' is-active' : '';
    $result = (string)$modx->runSnippet('versionCleanX', [
        'contentType' => $type,
        'maxVersions' => $maxVersions,
        'dryRun' => $dryRun ? 1 : 0,
        'optimize' => $optimize ? 1 : 0,
    ]);

    if ($result === '') {
        $result = '<p>Сниппет versionCleanX не вернул результат для типа ' . $escape($type) . '.</p>';
    }

    $out .= '<div class="vcx-tabs__panel' . $active . '" id="' . $escape($panelId) . '" role="tabpanel">';
    $out .= '<div class="vcx-result">' . $result . '</div>';
    $out .= '</div>';
}
$out .= '</div>';

$out .= <<<'VCX_SCRIPT'
<script>
(function () {
    var root = document.currentScript ? document.currentScript.closest('.vcx-tool') : null;
    if (!root) {
        return;
    }
    var buttons = root.querySelectorAll('[data-vcx-tab]');
    var panels = root.querySelectorAll('.vcx-tabs__panel');
    buttons.forEach(function (button) {
        button.addEventListener('click', function () {
            var targetId = button.getAttribute('data-vcx-tab');
            buttons.forEach(function (item) {
                var active = item === button;
                item.classList.toggle('is-active', active);
                item.setAttribute('aria-selected', active ? 'true' : 'false');
            });
            panels.forEach(function (panel) {
                panel.classList.toggle('is-active', panel.id === targetId);
            });
        });
    });
})();
</script>
VCX_SCRIPT;

$out .= '</section>';

return $out;

Далее содержимое сниппета versionCleanX (отличается от варианта выше):

<?php
ini_set('memory_limit', '256M');

/**
 * versionCleanX
 *
 * Cleans old VersionX rows while keeping the newest N versions per element.
 *
 * PROPERTIES:
 * &contentType - resource, chunk, plugin, snippet, template, or templatevar
 * &maxVersions - integer max versions to keep per element
 * &dryRun - 1 to show what would be deleted without deleting rows
 * &optimize - 1 to run OPTIMIZE TABLE after cleanup
 *
 * USAGE:
 * [[!versionCleanX? &contentType=`resource` &maxVersions=`10`]]
 */

$type = strtolower(trim((string)$modx->getOption('contentType', $scriptProperties, 'resource')));
$maxVersions = (int)$modx->getOption('maxVersions', $scriptProperties, 5);
$dryRun = (bool)$modx->getOption('dryRun', $scriptProperties, false);
$optimize = (bool)$modx->getOption('optimize', $scriptProperties, true);

$titleColumns = [
    'chunk' => 'name',
    'plugin' => 'name',
    'snippet' => 'name',
    'template' => 'templatename',
    'templatevar' => 'name',
    'resource' => 'title',
];

if (!array_key_exists($type, $titleColumns)) {
    $type = 'resource';
}

if ($maxVersions < 1) {
    return 'VersionX cleanup error: maxVersions must be greater than 0.';
}

$table = $modx->getOption('table_prefix') . 'versionx_' . $type;
$titleColumn = $titleColumns[$type];
$query = "SELECT version_id, content_id, {$titleColumn} AS page_title FROM `{$table}` ORDER BY content_id ASC, version_id DESC";

$stmt = $modx->query($query);
if (!is_object($stmt)) {
    return 'VersionX cleanup query error: ' . print_r($modx->errorInfo(), true);
}

$rowsHtml = '';
$total = 0;
$deleted = 0;
$currentContentId = null;
$currentTitle = '';
$currentTotal = 0;
$currentDeleted = 0;
$keptForCurrent = 0;

$flushRow = function () use (&$rowsHtml, &$currentContentId, &$currentTitle, &$currentTotal, &$currentDeleted) {
    if ($currentContentId === null) {
        return;
    }

    $rowsHtml .= '<tr><td>' . htmlspecialchars((string)$currentTitle, ENT_QUOTES, 'UTF-8') . '</td><td>' .
        (int)$currentTotal . '</td><td>' . (int)$currentDeleted . "</td></tr>\n";
};

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $contentId = (int)$row['content_id'];

    if ($currentContentId !== $contentId) {
        $flushRow();
        $currentContentId = $contentId;
        $currentTitle = (string)$row['page_title'];
        $currentTotal = 0;
        $currentDeleted = 0;
        $keptForCurrent = 0;
    }

    $total++;
    $currentTotal++;

    if ($keptForCurrent < $maxVersions) {
        $keptForCurrent++;
        continue;
    }

    $versionId = (int)$row['version_id'];
    if (!$dryRun) {
        $delete = $modx->query("DELETE FROM `{$table}` WHERE version_id = {$versionId}");
        if (!is_object($delete)) {
            return 'VersionX cleanup delete error for ' . htmlspecialchars($currentTitle, ENT_QUOTES, 'UTF-8') .
                ': ' . print_r($modx->errorInfo(), true);
        }
    }

    $deleted++;
    $currentDeleted++;
}

$flushRow();

$optimizeMessage = '';
if (!$dryRun && $optimize) {
    $optimized = $modx->query("OPTIMIZE TABLE `{$table}`");
    if (!is_object($optimized)) {
        $optimizeMessage = '<p>Optimize error: ' . htmlspecialchars(print_r($modx->errorInfo(), true), ENT_QUOTES, 'UTF-8') . '</p>';
    }
}

return '<h3>VersionX Cleanup for ' . htmlspecialchars($table, ENT_QUOTES, 'UTF-8') . '</h3>' .
    ($dryRun ? '<p><strong>Dry run:</strong> rows were not deleted.</p>' : '') .
    '<p>Total records: <strong>' . (int)$total . '</strong>
Total deleted: <strong>' . (int)$deleted . '</strong></p>' .
    $optimizeMessage .
    '<table class="table table-striped"><thead><tr><th>Page name</th><th>Total found</th><th>Deleted</th></tr></thead><tbody>' .
    $rowsHtml .
    '</tbody></table>';

Итого имеем удобный вывод с вкладками.
<cut/>
Из статьи на моем сайте gowindo.ru/articles/modx/versioncleanxtabs-chistka-ot-ustarevshix-versij