9.5/_separate_sys_history_from_sys_log_update_8php_source.html

<?php

declare(strict_types = 1);

namespace ‪TYPO3\CMS\Install\Updates;


/*

 * This file is part of the TYPO3 CMS project.

 *

 * It is free software; you can redistribute it and/or modify it under

 * the terms of the GNU General Public License, either version 2

 * of the License, or any later version.

 *

 * For the full copyright and license information, please read the

 * LICENSE.txt file that was distributed with this source code.

 *

 * The TYPO3 project - inspiring people to share!

 */


use ‪TYPO3\CMS\Core\Database\Connection;

use ‪TYPO3\CMS\Core\Database\ConnectionPool;

use ‪TYPO3\CMS\Core\DataHandling\History\RecordHistoryStore;

use ‪TYPO3\CMS\Core\Registry;

use ‪TYPO3\CMS\Core\Utility\GeneralUtility;


class ‪SeparateSysHistoryFromSysLogUpdate implements ‪UpgradeWizardInterface, ‪RepeatableInterface

{


    private const ‪BATCH_SIZE = 100;


    private const ‪MOVE_DATA = 0;


    private const ‪UPDATE_HISTORY = 1;


    public function ‪getIdentifier(): string

    {

        return 'separateSysHistoryFromLog';

    }


    public function ‪getTitle(): string

    {

        return 'Migrates existing sys_log entries into sys_history';

    }


    public function ‪getDescription(): string

    {

        return 'The history of changes of a record is now solely stored within sys_history.'

            . ' Previous data within sys_log needs to be migrated into sys_history now.';

    }


    public function ‪updateNecessary(): bool

    {

        // sys_log field has been removed, no need to do something.

        if (!$this->‪checkIfFieldInTableExists('sys_history', 'sys_log_uid')) {

            return false;

        }


        // Check if there is data to migrate

        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)

            ->getQueryBuilderForTable('sys_history');

        $queryBuilder->getRestrictions()->removeAll();

        $count = $queryBuilder->count('*')

            ->from('sys_history')

            ->where($queryBuilder->expr()->neq('sys_log_uid', 0))

            ->execute()

            ->fetchColumn(0);


        return $count > 0;

    }


    public function ‪getPrerequisites(): array

    {

        return [

            DatabaseUpdatedPrerequisite::class

        ];

    }


    public function ‪executeUpdate(): bool

    {

        // If rows from the target table that is updated and the sys_registry table are on the

        // same connection, the update statement and sys_registry position update will be

        // handled in a transaction to have an atomic operation in case of errors during execution.

        $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);

        $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('sys_history');

        $connectionForSysRegistry = $connectionPool->getConnectionForTable('sys_registry');


        // In case the PHP ended for whatever reason, fetch the last position from registry

        // and only execute the phase(s) that has/have not been executed yet

        $startPositionAndPhase = $this->‪getStartPositionAndPhase();


        if ($startPositionAndPhase['phase'] === self::MOVE_DATA) {

            $startPositionAndPhase = $this->‪moveDataFromSysLogToSysHistory(

                $connection,

                $connectionForSysRegistry,

                $startPositionAndPhase

            );

        }


        if ($startPositionAndPhase['phase'] === self::UPDATE_HISTORY) {

            $this->‪keepHistoryForInsertAndDeleteActions(

                $connectionForSysRegistry,

                $startPositionAndPhase

            );

        }


        return true;

    }


    protected function ‪moveDataFromSysLogToSysHistory(

        ‪Connection $connection,

        ‪Connection $connectionForSysRegistry,

        array $startPositionAndPhase

    ): array {

        do {

            $processedRows = 0;


            // update "modify" statements (= decoupling)

            $queryBuilder = $connection->‪createQueryBuilder();

            $rows = $queryBuilder->‪select('sys_history.uid AS history_uid', 'sys_history.history_data', 'sys_log.*')

                ->‪from('sys_history')

                ->‪leftJoin(

                    'sys_history',

                    'sys_log',

                    'sys_log',

                    $queryBuilder->expr()->eq('sys_history.sys_log_uid', $queryBuilder->quoteIdentifier('sys_log.uid'))

                )

                ->‪where($queryBuilder->expr()->gt('sys_history.uid', $queryBuilder->createNamedParameter($startPositionAndPhase['uid'])))

                ->‪setMaxResults(self::BATCH_SIZE)

                ->‪orderBy('sys_history.uid', 'ASC')

                ->‪execute()

                ->fetchAll();


            foreach ($rows as $row) {

                $logData = $this->‪unserializeToArray((string)($row['log_data'] ?? ''));

                $historyData = $this->‪unserializeToArray((string)($row['history_data'] ?? ''));

                $updateData = [

                    'actiontype' => ‪RecordHistoryStore::ACTION_MODIFY,

                    'usertype' => 'BE',

                    'userid' => $row['userid'],

                    'sys_log_uid' => 0,

                    'history_data' => json_encode($historyData),

                    'originaluserid' => empty($logData['originalUser']) ? null : $logData['originalUser']

                ];


                if ($connection === $connectionForSysRegistry) {

                    // sys_history and sys_registry tables are on the same connection, use a transaction

                    $connection->beginTransaction();

                    try {

                        $startPositionAndPhase = $this->‪updateTablesAndTrackProgress(

                            $connection,

                            $connection,

                            $updateData,

                            $logData,

                            $row

                        );

                        $connection->commit();

                    } catch (\‪Exception $up) {

                        $connection->rollBack();

                        throw ($up);

                    }

                } else {

                    // Different connections for sys_history and sys_registry -> execute two

                    // distinct queries and hope for the best.

                    $startPositionAndPhase = $this->‪updateTablesAndTrackProgress(

                        $connection,

                        $connectionForSysRegistry,

                        $updateData,

                        $logData,

                        $row

                    );

                }


                $processedRows++;

            }

            // repeat until a resultset smaller than the batch size was processed

        } while ($processedRows === self::BATCH_SIZE);


        // phase 0 is finished

        $registry = GeneralUtility::makeInstance(Registry::class);

        $startPositionAndPhase = [

            'phase' => ‪self::UPDATE_HISTORY,

            'uid' => 0,

        ];

        $registry->set('installSeparateHistoryFromSysLog', 'phaseAndPosition', $startPositionAndPhase);


        return $startPositionAndPhase;

    }


    protected function ‪updateTablesAndTrackProgress(

        ‪Connection $connection,

        ‪Connection $connectionForSysRegistry,

        array $updateData,

        array $logData,

        array $row

    ): array {

        $connection->‪update(

            'sys_history',

            $updateData,

            ['uid' => (int)$row['history_uid']],

            ['uid' => ‪Connection::PARAM_INT]

        );


        // Store information about history entry in sys_log table

        $logData['history'] = $row['history_uid'];

        $connection->‪update(

            'sys_log',

            ['log_data' => serialize($logData)],

            ['uid' => (int)$row['uid']],

            ['uid' => ‪Connection::PARAM_INT]

        );

        $startPositionAndPhase = [

            'phase' => ‪self::MOVE_DATA,

            'uid' => $row['history_uid'],

        ];

        $connectionForSysRegistry->‪update(

            'sys_registry',

            [

                'entry_value' => serialize($startPositionAndPhase)

            ],

            [

                'entry_namespace' => 'installSeparateHistoryFromSysLog',

                'entry_key' => 'phaseAndPosition',

            ]

        );


        return $startPositionAndPhase;

    }


    protected function ‪keepHistoryForInsertAndDeleteActions(

        ‪Connection $connectionForSysRegistry,

        array $startPositionAndPhase

    ) {

        do {

            $processedRows = 0;


            // Add insert/delete calls

            $logQueryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_log');

            $result = $logQueryBuilder->select('uid', 'userid', 'action', 'tstamp', 'log_data', 'tablename', 'recuid')

                ->from('sys_log')

                ->where(

                    $logQueryBuilder->expr()->eq('type', $logQueryBuilder->createNamedParameter(1, \PDO::PARAM_INT)),

                    $logQueryBuilder->expr()->orX(

                        $logQueryBuilder->expr()->eq('action', $logQueryBuilder->createNamedParameter(1, \PDO::PARAM_INT)),

                        $logQueryBuilder->expr()->eq('action', $logQueryBuilder->createNamedParameter(3, \PDO::PARAM_INT))

                    )

                )

                ->andWhere(

                    $logQueryBuilder->expr()->gt('uid', $logQueryBuilder->createNamedParameter($startPositionAndPhase['uid']))

                )

                ->orderBy('uid', 'ASC')

                ->setMaxResults(self::BATCH_SIZE)

                ->execute();


            foreach ($result as $row) {

                $logData = $this->‪unserializeToArray((string)($row['log_data'] ?? ''));

                $store = GeneralUtility::makeInstance(

                    RecordHistoryStore::class,

                    ‪RecordHistoryStore::USER_BACKEND,

                    $row['userid'],

                    (empty($logData['originalUser']) ? null : $logData['originalUser']),

                    $row['tstamp']

                );


                switch ($row['action']) {

                    // Insert

                    case 1:

                        $store->addRecord($row['tablename'], (int)$row['recuid'], $logData);

                        break;

                    // Delete

                    case 3:

                        $store->deleteRecord($row['tablename'], (int)$row['recuid']);

                        break;

                }


                $startPositionAndPhase = [

                    'phase' => ‪self::UPDATE_HISTORY,

                    'uid' => $row['uid'],

                ];

                $connectionForSysRegistry->‪update(

                    'sys_registry',

                    [

                        'entry_value' => serialize($startPositionAndPhase)

                    ],

                    [

                        'entry_namespace' => 'installSeparateHistoryFromSysLog',

                        'entry_key' => 'phaseAndPosition',

                    ]

                );


                $processedRows++;

            }

            // repeat until a result set smaller than the batch size was processed

        } while ($processedRows === self::BATCH_SIZE);

    }


    protected function ‪checkIfFieldInTableExists($table, $fieldName): bool

    {

        $tableColumns = GeneralUtility::makeInstance(ConnectionPool::class)

            ->getConnectionForTable($table)

            ->getSchemaManager()

            ->listTableColumns($table);

        return isset($tableColumns[$fieldName]);

    }


    protected function ‪getStartPositionAndPhase(): array

    {

        $registry = GeneralUtility::makeInstance(Registry::class);

        $startPosition = $registry->get('installSeparateHistoryFromSysLog', 'phaseAndPosition', []);

        if (empty($startPosition)) {

            $startPosition = [

                'phase' => ‪self::MOVE_DATA,

                'uid' => 0,

            ];

            $registry->set('installSeparateHistoryFromSysLog', 'phaseAndPosition', $startPosition);

        }


        return $startPosition;

    }


    protected function ‪unserializeToArray(string $serialized): array

    {

        $unserialized = unserialize($serialized, ['allowed_classes' => false]);

        return is_array($unserialized) ? $unserialized : [];

    }

}