SilverStripe 2 Versioned Records Housekeeping

Housekeeping.php

<?php

ini_set('memory_limit', '256M');
ini_set('max_execution_time', '600');

class Housekeeping extends BuildTask
{
    /**
     * @var string
     */
    protected $title = 'Database Housekeeping';

    /**
     * @var string
     */
    protected $description = 'Delete old SiteTree versions and drafts from the database';

    /**
     * @var int
     */
    protected $versionLimit = 1;

    /**
     * @var int
     */
    protected $draftLimit = 1;

    /**
     * @var string
     */
    protected $removeOlderThan = '-3 months';

    /**
     * @var array
     */
    protected $affectedTables = array();

    /**
     * @param SS_HTTPRequest $request
     */
    public function run($request)
    {
        $this->output('========== Starting housekeeping ==========');

        foreach (ClassInfo::dataClassesFor('SiteTree') as $class) {
            $this->affectedTables[$class . '_versions'] = 0;
        }

        $this->clear();
        $this->vacuum();

        $tables = array();

        foreach ($this->affectedTables as $table=>$count) {
            if ($count) {
                $tables[] = $table;
            }
        }

        $this->output(sprintf('Removed %d records across %d tables', $this->totalRowsAffected(), count($tables)));

        foreach ($this->affectedTables as $table=>$count) {
            if ($count) {
                $this->output("\t" . sprintf('%d from %s', $count, $table));
            }
        }

        $this->output('========== The house is kept ==========');
    }

    protected function clear()
    {
        $this->output('***** Sweeping the records *****');

        $pages = DataObject::get('SiteTree', sprintf("Created < '%s'", date('Y-m-d H:00:00', strtotime($this->removeOlderThan))));

        $totalPages = $pages->count();

        foreach ($pages as $currentPage=>$page) {
            $versions = DB::query(
                'SELECT Version ' .
                'FROM SiteTree_versions ' .
                sprintf('WHERE RecordID = %d AND Version != %d AND WasPublished = 1 ', $page->ID, $page->Version) .
                'ORDER BY LastEdited DESC ' .
                sprintf('LIMIT %d OFFSET %d', PHP_INT_MAX, $this->versionLimit)
            );

            $drafts = DB::query(
                'SELECT Version ' .
                'FROM SiteTree_versions ' .
                sprintf('WHERE RecordID = %d AND Version != %d AND WasPublished = 0 ', $page->ID, $page->Version) .
                'ORDER BY LastEdited DESC ' .
                sprintf('LIMIT %d OFFSET %d', PHP_INT_MAX, $this->draftLimit)
            );

            $toDelete = array_merge($versions->column('Version'), $drafts->column('Version'));

            $action = 'skipping';

            if ($page->ClassName && $count = count($toDelete)) {
                $action = sprintf('removing %d records for', $count);

                $classes = ClassInfo::dataClassesFor($page->ClassName);

                foreach ($classes as $class) {
                    $table = $class . '_versions';

                    DB::query(sprintf('DELETE FROM %s WHERE RecordID = %d AND Version IN (%s)', $table, $page->ID, implode(', ', $toDelete)));

                    if ($affectedRows = DB::affectedRows()) {
                        $this->affectedTables[$table] += $affectedRows;
                    }
                }
            }

            $this->output(sprintf(
                '%s/%d - %s (%s #%d) %s',
                str_pad($currentPage + 1, strlen($totalPages), ' ', STR_PAD_LEFT),
                $totalPages,
                $action,
                $page->ClassName,
                $page->ID,
                $page->Title
            ));
        }

        $this->output('***** Records are swept *****');
    }

    protected function vacuum()
    {
        $this->output('***** Vaccuming tables *****');

        if ($this->totalRowsAffected()) {
            global $databaseConfig;

            foreach (array_keys($this->affectedTables) as $table) {
                if (preg_match('/mysql/i', $databaseConfig['type'])) {
                    DB::query(sprintf('OPTIMIZE table "%s"', $table));
                } elseif (preg_match('/postgres/i', $databaseConfig['type'])) {
                    DB::query(sprintf('VACUUM "%s"', $table));
                }

                $this->output(sprintf('%s optimised', $table));
            }

            /* Sqlite just optimizes the database, not each table */
            if (preg_match('/sqlite/i', $databaseConfig['type'])) {
                DB::query('VACUUM');

                $this->output('Database optimised');
            }
        }

        $this->output('***** Tables are vaccumed *****');
    }

    protected function totalRowsAffected()
    {
        $total = 0;

        foreach ($this->affectedTables as $count) {
            $total += $count;
        }

        return $total;
    }

    /**
     * @param string $message
     */
    protected function output($message)
    {
        echo '[' . date('Y-m-d H:i:s') . '] ' . $message . (Director::is_cli() ? PHP_EOL : '<br>');
    }
}