Keep redirecting old realurl urls after migrating to TYPO3 9+

ext_localconf.php

<?php
// Override an 
$GLOBALS['TYPO3_CONF_VARS']['SYS']['Objects'][\TYPO3\CMS\Core\Error\PageErrorHandler\PageContentErrorHandler::class] = [
    'className' => \Site\Site\ErrorHandler\PageContentErrorHandler::class
];

ext_tables.sql

#
# Realurl tables keeped for Page not found and 301 redirects
#
CREATE TABLE tx_realurl_urldata (
	uid int(11) NOT NULL auto_increment,
	pid int(11) DEFAULT '0' NOT NULL,
	crdate int(11) DEFAULT '0' NOT NULL,
	page_id int(11) DEFAULT '0' NOT NULL,
	rootpage_id int(11) DEFAULT '0' NOT NULL,
	original_url text,
    original_url_hash int(11) unsigned DEFAULT '0' NOT NULL,
	speaking_url text,
    speaking_url_hash int(11) unsigned DEFAULT '0' NOT NULL,
	request_variables text,
	expire int(11) DEFAULT '0' NOT NULL,

	PRIMARY KEY (uid),
	KEY parent (pid),
	KEY pathq1 (rootpage_id,original_url_hash,expire),
	KEY pathq2 (rootpage_id,speaking_url_hash,expire),
	KEY page_id (page_id)
) ENGINE=InnoDB;
CREATE TABLE tx_realurl_pathdata (
	uid int(11) NOT NULL auto_increment,
	pid int(11) DEFAULT '0' NOT NULL,
	page_id int(11) DEFAULT '0' NOT NULL,
	language_id int(11) DEFAULT '0' NOT NULL,
	rootpage_id int(11) DEFAULT '0' NOT NULL,
	mpvar tinytext,
	pagepath text,
	expire int(11) DEFAULT '0' NOT NULL,

	PRIMARY KEY (uid),
	KEY parent (pid),
	KEY pathq1 (rootpage_id,pagepath(32),expire),
	KEY pathq2 (page_id,language_id,rootpage_id,expire),
	KEY expire (expire)
) ENGINE=InnoDB;
CREATE TABLE tx_realurl_uniqalias (
	uid int(11) NOT NULL auto_increment,
	pid int(11) DEFAULT '0' NOT NULL,
	tablename varchar(255) DEFAULT '' NOT NULL,
	field_alias varchar(255) DEFAULT '' NOT NULL,
	field_id varchar(60) DEFAULT '' NOT NULL,
	value_alias varchar(255) DEFAULT '' NOT NULL,
	value_id int(11) DEFAULT '0' NOT NULL,
	lang int(11) DEFAULT '0' NOT NULL,
	expire int(11) DEFAULT '0' NOT NULL,

	PRIMARY KEY (uid),
	KEY parent (pid),
	KEY tablename (tablename),
	KEY bk_realurl01 (field_alias(20),field_id,value_id,lang,expire),
	KEY bk_realurl02 (tablename(32),field_alias(20),field_id,value_alias(20),expire)
) ENGINE=InnoDB;

PageContentErrorHandler.php

<?php


namespace Site\Site\ErrorHandler;

use Psr\Http\Message\ResponseInterface;
use Psr\Http\Message\ServerRequestInterface;
use Site\Site\Service\RealurlRedirectService;
use TYPO3\CMS\Core\Site\Entity\Site;
use TYPO3\CMS\Core\Site\Entity\SiteLanguage;
use TYPO3\CMS\Core\Utility\GeneralUtility;

/**
 * This ErrorHandler checks if the requested url is in the realurl cache and issues a 301
 * *
 * @package Site\Site\ErrorHandler
 */
class PageContentErrorHandler extends \TYPO3\CMS\Core\Error\PageErrorHandler\PageContentErrorHandler
{
    /**
     * @param ServerRequestInterface $request
     * @param string $message
     * @param array $reasons
     * @return ResponseInterface
     * @throws \RuntimeException
     */
    public function handlePageError(ServerRequestInterface $request, string $message, array $reasons = []): ResponseInterface
    {
        if ($response = $this->tryRealUrlCache($request)) {
            return $response;
        }

        return parent::handlePageError($request, $message, $reasons);
    }

    protected function tryRealUrlCache(ServerRequestInterface $request): ?ResponseInterface
    {
        $site = $request->getAttribute('site', null);
        if (!$site instanceof Site) {
            return null;
        }
        $languageId = 0;
        $siteLanguage = $request->getAttribute('language');
        if ($siteLanguage instanceof SiteLanguage) {
            $languageId = $siteLanguage->getLanguageId();
        }

        $requestUri = $request->getUri();
        $speakinkUrl = ltrim($requestUri->getPath(), '/');
        if ($requestUri->getQuery()) {
            $speakinkUrl .= '?' . $requestUri->getQuery();
        }

        $realurlRedirectService = GeneralUtility::makeInstance(RealurlRedirectService::class);
        $cacheEntry = $realurlRedirectService->getRealurlCacheEntryBySpeakingUrl($site->getRootPageId(), $speakinkUrl, $languageId);
        if (!$cacheEntry) {
            return null;
        }

        $newUri = $realurlRedirectService->getUriFromRealurlCacheEntry($cacheEntry);

        if (!$newUri || $newUri === $speakinkUrl) {
            return null;
        }

        return new \TYPO3\CMS\Core\Http\RedirectResponse($newUri, 301);
    }
}

RealurlRedirectService.php

<?php


namespace Site\Site\Service;


use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Site\SiteFinder;
use TYPO3\CMS\Core\Utility\ArrayUtility;
use TYPO3\CMS\Core\Utility\GeneralUtility;

class RealurlRedirectService
{
    /**
     * This regex should adapted
     */
    const IGNORED_GET_PARAMETERS_REGEXP = '/^(?:gclid|utm_(?:source|medium|campaign|term|content)|fbclid|pk_campaign|pk_kwd|TSFE_ADMIN_PANEL.*|dm_t)$/';

    /**
     * @var array
     */
    protected $ignoredUrlParameters = [];

    /**
     * @var Connection
     */
    protected $connection;

    /**
     * @var SiteFinder
     */
    private $siteFinder;

    public function __construct(Connection $connection = null, SiteFinder $siteFinder = null)
    {
        $this->connection = $connection ?? GeneralUtility::makeInstance(ConnectionPool::class)
                ->getConnectionForTable('tx_realurl_urldata');
        $this->siteFinder = $siteFinder ?? GeneralUtility::makeInstance(SiteFinder::class);
    }

    /**
     * Try to fecth a speaking url from realurl cache.
     *
     * This method was copied and adapted from ext:realurl
     *
     * @param int $rootPageId
     * @param string $speakingUrl
     * @param int $languageId
     * @return array
     */
    public function getRealurlCacheEntryBySpeakingUrl(int $rootPageId, string $speakingUrl, int $languageId): array
    {
        $speakingUrl = $this->removeIgnoredParametersFromURL($speakingUrl);
        $speakingUrlWithTrailingSlash = rtrim($speakingUrl, '/') . '/';
        $speakingUrlWithoutTrailingSlash = rtrim($speakingUrl, '/');

        $qb = $this->connection->createQueryBuilder();
        $qb->select('*')
            ->from('tx_realurl_urldata')
            ->where(
                $qb->expr()->andX(
                    $qb->expr()->eq('rootpage_id', $qb->createNamedParameter($rootPageId, \PDO::PARAM_INT)),
                    $qb->expr()->orX(
                        // whithout trailing slash
                        $qb->expr()->andX(
                            $qb->expr()->eq('speaking_url_hash', $qb->createNamedParameter(sprintf('%u', crc32($speakingUrlWithoutTrailingSlash)), \PDO::PARAM_STR)),
                            $qb->expr()->eq('speaking_url', $qb->createNamedParameter($speakingUrlWithoutTrailingSlash, \PDO::PARAM_STR))
                        ),
                        // whith trailing slash
                        $qb->expr()->andX(
                            $qb->expr()->eq('speaking_url_hash', $qb->createNamedParameter(sprintf('%u', crc32($speakingUrlWithTrailingSlash)), \PDO::PARAM_STR)),
                            $qb->expr()->eq('speaking_url', $qb->createNamedParameter($speakingUrlWithTrailingSlash, \PDO::PARAM_STR))
                        )
                    )
                )
            )
            ->orderBy('expire');
        $rows = $qb->execute();

        $row = null;
        foreach ($rows as $rowCandidate) {
            $variables = (array)@json_decode($rowCandidate['request_variables'], TRUE);
            if (is_null($languageId)) {
                // No language known, we retrieve only the URL with lowest expiration value
                // See https://github.com/dmitryd/typo3-realurl/issues/250
                if (is_null($row) || $rowCandidate['expire'] <= $row['expire']) {
                    $row = $rowCandidate;
                    if (isset($variables['cHash'])) {
                        break;
                    }
                }
            } else {
                // Should check for language match
                // See https://github.com/dmitryd/typo3-realurl/issues/103
                if (isset($variables['L'])) {
                    if ((int)$variables['L'] === (int)$languageId) {
                        // Found language!
                        if (is_null($row) || $rowCandidate['expire'] <= $row['expire']) {
                            $row = $rowCandidate;
                            if (isset($variables['cHash'])) {
                                break;
                            }
                        }
                    }
                } elseif ($languageId === 0 && is_null($row)) {
                    // No L in URL parameters of the URL but default language requested. This is a match.
                    $row = $rowCandidate;
                }
            }
        }

        if (is_array($row)) {
            $cacheEntry = [
                'rootpage_id' => (int)$row['rootpage_id'],
                'page_id' => (int)$row['page_id'],
                'original_url' => $row['original_url'],
                'speaking_url' => $speakingUrl,
            ];
            $requestVariables = @json_decode($row['request_variables'], TRUE) ?? [];
            $cacheEntry['request_variables'] = $requestVariables;
        }

        // Maybe if an url was not found in tx_realurl_urldata it can be found in tx_realurl_pathdata.
        // Also, tx_realurl_uniqalias could be used at some point for record, but that would be quite difficult.
        // Thus, we rely entirely on tx_realurl_urldata, which should be quite accurate.

        return $cacheEntry ?? [];
    }

    /**
     * Try to build a URI from a realurl cache entry.
     *
     * @param array $cacheEntry
     * @return \Psr\Http\Message\UriInterface|null
     */
    public function getUriFromRealurlCacheEntry(array $cacheEntry): string
    {
        $siteFinder = GeneralUtility::makeInstance(SiteFinder::class);
        try {
            $site = $siteFinder->getSiteByRootPageId($cacheEntry['rootpage_id']);
        } catch (\TYPO3\CMS\Core\Exception\SiteNotFoundException $e) {
            // no site, no url
            return '';
        }

        $requestVariables = $cacheEntry['request_variables'];
        unset($requestVariables['id']);

        // chash will be recalculated if needed
        if (isset($requestVariables['cHash'])) {
            unset($requestVariables['cHash']);
        }

        // language needs to be passed in a special manner
        if (isset($requestVariables['L'])) {
            $requestVariables['_language'] = $requestVariables['L'];
            unset($requestVariables['L']);
        }

        // Converts array('tx_ext[var1]' => 1, 'tx_ext[var2]' => 2)
        // to array('tx_ext' => array('var1' => 1, 'var2' => 2))
        $queryString = GeneralUtility::implodeArrayForUrl('', $requestVariables, '', false, true);
        $requestVariables = GeneralUtility::explodeUrl2Array($queryString);

        // Restore ignored parameters
        if (count($this->ignoredUrlParameters) > 0) {
            ArrayUtility::mergeRecursiveWithOverrule($requestVariables, $this->ignoredUrlParameters);
        }

        try {
            $uri = $site->getRouter()->generateUri($cacheEntry['page_id'], $requestVariables);
        } catch (\TYPO3\CMS\Core\Routing\InvalidRouteArgumentsException $e) {
            return '';
        }

        return (string)$uri;
    }

    /**
     * Removes ignored parameters from the URL. Removed parameters are stored in
     * $this->ignoredUrlParameters and can be restored using restoreIgnoredUrlParameters.
     *
     * Copied from ext:realurl
     *
     * @param string $url
     * @return string
     */
    protected function removeIgnoredParametersFromURL(string $url): string
    {
        list($path, $queryString) = explode('?', $url, 2);
        $queryString = $this->removeIgnoredParametersFromQueryString((string)$queryString);

        $url = $path;
        if (!empty($queryString)) {
            $url .= '?';
        }
        $url .= $queryString;

        return $url;
    }

    /**
     * Removes ignored parameters from the query string.
     *
     * Copied from ext:realurl
     *
     * @param string $queryString
     * @return string
     */
    protected function removeIgnoredParametersFromQueryString(string $queryString): string
    {
        if ($queryString) {
            $ignoredParametersRegExp = static::IGNORED_GET_PARAMETERS_REGEXP;
            if ($ignoredParametersRegExp) {
                $collectedParameters = array();
                foreach (explode('&', trim($queryString, '&')) as $parameterPair) {
                    list($parameterName, $parameterValue) = explode('=', $parameterPair, 2);
                    if ($parameterName !== '') {
                        $parameterName = urldecode($parameterName);
                        if (preg_match($ignoredParametersRegExp, $parameterName)) {
                            $this->ignoredUrlParameters[$parameterName] = urldecode($parameterValue);
                        } else {
                            $collectedParameters[$parameterName] = urldecode($parameterValue);
                        }
                    }
                }
                $queryString = $this->createQueryStringFromParameters($collectedParameters);
            }
        } else {
            $queryString = '';
        }

        return $queryString;
    }

    /**
     * Creates a query string (without preceding question mark) from
     * parameters.
     *
     * Copied from ext:realurl
     *
     * @param array $parameters
     * @return string
     */
    protected function createQueryStringFromParameters(array $parameters): string
    {
        return substr(GeneralUtility::implodeArrayForUrl('', $parameters, '', false, true), 1);
    }
}