DatabaseIntegrityCheck.php

Go to the documentation of this file.

<?php
 
/*
 * 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!
 */
 
namespace ‪TYPO3\CMS\Lowlevel\Integrity;
 
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Types\Types;
use TYPO3\CMS\Backend\Utility\BackendUtility;
use ‪TYPO3\CMS\Core\Database\Connection;
use ‪TYPO3\CMS\Core\Database\ConnectionPool;
use ‪TYPO3\CMS\Core\Database\Platform\PlatformInformation;
use ‪TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
use ‪TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
use ‪TYPO3\CMS\Core\Database\RelationHandler;
use ‪TYPO3\CMS\Core\Utility\GeneralUtility;
 
class ‪DatabaseIntegrityCheck
{
    protected ‪$genTreeIncludeDeleted = true;
 
    protected ‪$genTreeIncludeVersions = true;
 
    protected ‪$genTreeIncludeRecords = false;
 
    protected ‪$pageIdArray = [];
 
    protected ‪$pageTranslatedPageIDArray = [];
 
    protected ‪$recIdArray = [];
 
    protected ‪$checkSelectDBRefs = [];
 
    protected ‪$checkGroupDBRefs = [];
 
    protected ‪$recStats = [
        'allValid' => [],
        'published_versions' => [],
        'deleted' => [],
    ];
 
    protected ‪$lRecords = [];
 
    protected ‪$lostPagesList = '';
 
    public function ‪getPageTranslatedPageIDArray(): array
    {
        return ‪$this->pageTranslatedPageIDArray;
    }
 
    public function ‪genTree($theID, $versions = false)
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
        $queryBuilder->getRestrictions()->removeAll();
        if (!$this->genTreeIncludeDeleted) {
            $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
        }
        $queryBuilder->select('uid', 'title', 'doktype', 'deleted', 'hidden', 'sys_language_uid')
            ->from('pages')
            ->orderBy('sorting');
        if ($versions) {
            $queryBuilder->addSelect('t3ver_wsid');
            $queryBuilder->where(
                $queryBuilder->expr()->eq('t3ver_oid', $queryBuilder->createNamedParameter($theID, ‪Connection::PARAM_INT))
            );
        } else {
            $queryBuilder->where(
                $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($theID, ‪Connection::PARAM_INT))
            );
        }
        $result = $queryBuilder->executeQuery();
        // Traverse the records selected
        while ($row = $result->fetchAssociative()) {
            $newID = $row['uid'];
            // Register various data for this item:
            if ($row['sys_language_uid'] === 0) {
                $this->pageIdArray[$newID] = $row;
            } else {
                $this->pageTranslatedPageIDArray[$newID] = $row;
            }
            $this->recStats['all_valid']['pages'][$newID] = $newID;
            if ($row['deleted']) {
                $this->recStats['deleted']['pages'][$newID] = $newID;
            }
 
            if (!isset($this->recStats['hidden'])) {
                $this->recStats['hidden'] = 0;
            }
 
            if ($row['hidden']) {
                $this->recStats['hidden']++;
            }
 
            $this->recStats['doktype'][$row['doktype']] ??= 0;
            $this->recStats['doktype'][$row['doktype']]++;
            // If all records should be shown, do so:
            if ($this->genTreeIncludeRecords) {
                foreach (‪$GLOBALS['TCA'] as $tableName => $cfg) {
                    if ($tableName !== 'pages') {
                        $this->‪genTree_records($newID, $tableName);
                    }
                }
            }
            // Add sub pages:
            $this->‪genTree($newID);
            // If versions are included in the tree, add those now:
            if ($this->genTreeIncludeVersions) {
                $this->‪genTree($newID, true);
            }
        }
    }
 
    public function ‪genTree_records($theID, $table, $versions = false): void
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
        $queryBuilder->getRestrictions()->removeAll();
        if (!$this->genTreeIncludeDeleted) {
            $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
        }
        $queryBuilder
            ->select(...explode(',', BackendUtility::getCommonSelectFields($table)))
            ->from($table);
 
        // Select all records from table pointing to this page
        if ($versions) {
            $queryBuilder->where(
                $queryBuilder->expr()->eq('t3ver_oid', $queryBuilder->createNamedParameter($theID, ‪Connection::PARAM_INT))
            );
        } else {
            $queryBuilder->where(
                $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($theID, ‪Connection::PARAM_INT))
            );
        }
        $queryResult = $queryBuilder->executeQuery();
        // Traverse selected
        while ($row = $queryResult->fetchAssociative()) {
            $newID = $row['uid'];
            // Register various data for this item:
            $this->recIdArray[$table][$newID] = $row;
            $this->recStats['all_valid'][$table][$newID] = $newID;
            if ($row['deleted']) {
                $this->recStats['deleted'][$table][$newID] = $newID;
            }
            // Select all versions of this record:
            if ($this->genTreeIncludeVersions && BackendUtility::isTableWorkspaceEnabled($table)) {
                $this->‪genTree_records($newID, $table, true);
            }
        }
    }
 
    public function ‪lostRecords($pid_list): void
    {
        $this->lostPagesList = '';
        $pageIds = ‪GeneralUtility::intExplode(',', $pid_list);
        if (is_array($pageIds)) {
            foreach (‪$GLOBALS['TCA'] as $table => $tableConf) {
                $pageIdsForTable = $pageIds;
                // Remove preceding "-1," for non-versioned tables
                if (!BackendUtility::isTableWorkspaceEnabled($table)) {
                    $pageIdsForTable = array_combine($pageIdsForTable, $pageIdsForTable);
                    unset($pageIdsForTable[-1]);
                }
                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
                $queryBuilder->getRestrictions()->removeAll();
                $selectFields = ['uid', 'pid'];
                if (!empty(‪$GLOBALS['TCA'][$table]['ctrl']['label'])) {
                    $selectFields[] = ‪$GLOBALS['TCA'][$table]['ctrl']['label'];
                }
                $queryResult = $queryBuilder->select(...$selectFields)
                    ->from($table)
                    ->where(
                        $queryBuilder->expr()->notIn(
                            'pid',
                            $queryBuilder->createNamedParameter($pageIdsForTable, ‪Connection::PARAM_INT_ARRAY)
                        )
                    )
                    ->executeQuery();
                $lostIdList = [];
                while ($row = $queryResult->fetchAssociative()) {
                    $this->lRecords[$table][$row['uid']] = [
                        'uid' => $row['uid'],
                        'pid' => $row['pid'],
                        'title' => strip_tags(BackendUtility::getRecordTitle($table, $row)),
                    ];
                    $lostIdList[] = $row['uid'];
                }
                if ($table === 'pages') {
                    $this->lostPagesList = implode(',', $lostIdList);
                }
            }
        }
    }
 
    public function ‪fixLostRecord($table, ‪$uid): bool
    {
        if ($table && ‪$GLOBALS['TCA'][$table] && ‪$uid && is_array($this->lRecords[$table][‪$uid]) && ‪$GLOBALS['BE_USER']->isAdmin()) {
            $updateFields = [
                'pid' => 0,
            ];
            // If possible a lost record restored is hidden as default
            if (‪$GLOBALS['TCA'][$table]['ctrl']['enablecolumns']['disabled']) {
                $updateFields[‪$GLOBALS['TCA'][$table]['ctrl']['enablecolumns']['disabled']] = 1;
            }
            GeneralUtility::makeInstance(ConnectionPool::class)
                ->getConnectionForTable($table)
                ->update($table, $updateFields, ['uid' => (int)‪$uid]);
            return true;
        }
        return false;
    }
 
    public function ‪countRecords($pid_list): array
    {
        $list = [];
        $list_n = [];
        $pageIds = ‪GeneralUtility::intExplode(',', $pid_list);
        if (!empty($pageIds)) {
            foreach (‪$GLOBALS['TCA'] as $table => $tableConf) {
                $pageIdsForTable = $pageIds;
                // Remove preceding "-1," for non-versioned tables
                if (!BackendUtility::isTableWorkspaceEnabled($table)) {
                    $pageIdsForTable = array_combine($pageIdsForTable, $pageIdsForTable);
                    unset($pageIdsForTable[-1]);
                }
                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
                $queryBuilder->getRestrictions()->removeAll();
                $count = $queryBuilder->count('uid')
                    ->from($table)
                    ->where(
                        $queryBuilder->expr()->in(
                            'pid',
                            $queryBuilder->createNamedParameter($pageIds, ‪Connection::PARAM_INT_ARRAY)
                        )
                    )
                    ->executeQuery()
                    ->fetchOne();
                if ($count) {
                    $list[$table] = $count;
                }
 
                // same query excluding all deleted records
                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
                $queryBuilder->getRestrictions()
                    ->removeAll()
                    ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
                $count = $queryBuilder->count('uid')
                    ->from($table)
                    ->where(
                        $queryBuilder->expr()->in(
                            'pid',
                            $queryBuilder->createNamedParameter($pageIdsForTable, ‪Connection::PARAM_INT_ARRAY)
                        )
                    )
                    ->executeQuery()
                    ->fetchOne();
                if ($count) {
                    $list_n[$table] = $count;
                }
            }
        }
        return ['all' => $list, 'non_deleted' => $list_n];
    }
 
    public function ‪getGroupFields(): array
    {
        $result = [];
        foreach (‪$GLOBALS['TCA'] as $table => $tableConf) {
            $cols = ‪$GLOBALS['TCA'][$table]['columns'];
            foreach ($cols as $field => $config) {
                $fieldType = $config['config']['type'] ?? '';
                if ($fieldType === 'group') {
                    $result[$table][] = $field;
                }
                if (($fieldType === 'select' || $fieldType === 'category')
                    && ($config['config']['foreign_table'] ?? false)
                ) {
                    $result[$table][] = $field;
                }
            }
        }
        return $result;
    }
 
    public function ‪selectNonEmptyRecordsWithFkeys(): void
    {
        $fkey_arrays = $this->‪getGroupFields();
        $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
        foreach ($fkey_arrays as $table => ‪$fields) {
            $connection = $connectionPool->getConnectionForTable($table);
            $schemaManager = $connection->createSchemaManager();
            $tableColumns = $schemaManager->listTableColumns($table);
 
            $queryBuilder = $connectionPool->getQueryBuilderForTable($table);
            $queryBuilder->getRestrictions()->removeAll();
 
            $queryBuilder->select('*')
                ->from($table);
            $whereClause = [];
 
            foreach (‪$fields as $fieldName) {
                // The array index of $tableColumns is the lowercased column name!
                // It is quoted for keywords
                $column = $tableColumns[strtolower($fieldName)]
                    ?? $tableColumns[$connection->quoteIdentifier(strtolower($fieldName))]
                    ?? '';
                if (empty($column)) {
                    // Throw meaningful exception if field does not exist in DB - 'none' is not filtered here since the
                    // method is only called with type=group fields
                    throw new \RuntimeException(
                        'Field ' . $fieldName . ' for table ' . $table . ' has been defined in TCA, but does not exist in DB',
                        1536248937
                    );
                }
                $fieldType = Type::getTypeRegistry()->lookupName($column->getType());
                if (in_array(
                    $fieldType,
                    [Types::BIGINT, Types::INTEGER, Types::SMALLINT, Types::DECIMAL, Types::FLOAT],
                    true
                )) {
                    $whereClause[] = $queryBuilder->expr()->and(
                        $queryBuilder->expr()->isNotNull($fieldName),
                        $queryBuilder->expr()->neq(
                            $fieldName,
                            $queryBuilder->createNamedParameter(0, ‪Connection::PARAM_INT)
                        )
                    );
                } elseif (in_array($fieldType, [Types::STRING, Types::TEXT], true)) {
                    $whereClause[] = $queryBuilder->expr()->and(
                        $queryBuilder->expr()->isNotNull($fieldName),
                        $queryBuilder->expr()->neq(
                            $fieldName,
                            $queryBuilder->createNamedParameter('')
                        )
                    );
                } elseif ($fieldType === Types::BLOB) {
                    $whereClause[] = $queryBuilder->expr()->and(
                        $queryBuilder->expr()->isNotNull($fieldName),
                        $queryBuilder->expr()
                            ->comparison(
                                $queryBuilder->expr()->length($fieldName),
                                ExpressionBuilder::GT,
                                $queryBuilder->createNamedParameter(0, ‪Connection::PARAM_INT)
                            )
                    );
                }
            }
            $queryResult = $queryBuilder->orWhere(...$whereClause)->executeQuery();
 
            while ($row = $queryResult->fetchAssociative()) {
                foreach (‪$fields as $field) {
                    if (trim($row[$field] ?? '')) {
                        $fieldConf = ‪$GLOBALS['TCA'][$table]['columns'][$field]['config'];
                        if ($fieldConf['type'] === 'group' && !empty($fieldConf['allowed'])) {
                            $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
                            $dbAnalysis->start(
                                $row[$field],
                                $fieldConf['allowed'],
                                $fieldConf['MM'] ?? null,
                                $row['uid'],
                                $table,
                                $fieldConf
                            );
                            foreach ($dbAnalysis->itemArray as $tempArr) {
                                if (!isset($this->checkGroupDBRefs[$tempArr['table']][$tempArr['id']])) {
                                    $this->checkGroupDBRefs[$tempArr['table']][$tempArr['id']] = 0;
                                }
                                $this->checkGroupDBRefs[$tempArr['table']][$tempArr['id']] += 1;
                            }
                        }
                        if (($fieldConf['foreign_table'] ?? false)
                            && ($fieldConf['type'] === 'select' || $fieldConf['type'] === 'category')
                        ) {
                            $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
                            $dbAnalysis->start(
                                $row[$field],
                                $fieldConf['foreign_table'],
                                $fieldConf['MM'] ?? null,
                                $row['uid'],
                                $table,
                                $fieldConf
                            );
                            foreach ($dbAnalysis->itemArray as $tempArr) {
                                if ($tempArr['id'] > 0) {
                                    if (!isset($this->checkSelectDBRefs[$fieldConf['foreign_table']][$tempArr['id']])) {
                                        $this->checkSelectDBRefs[$fieldConf['foreign_table']][$tempArr['id']] = 0;
                                    }
                                    $this->checkSelectDBRefs[$fieldConf['foreign_table']][$tempArr['id']] += 1;
                                }
                            }
                        }
                    }
                }
            }
        }
    }
 
    public function ‪testDBRefs($theArray): string
    {
        $rows = [];
        foreach ($theArray as $table => $dbArr) {
            if (‪$GLOBALS['TCA'][$table]) {
                $ids = array_keys($dbArr);
                if (!empty($ids)) {
                    $connection = GeneralUtility::makeInstance(ConnectionPool::class)
                        ->getConnectionForTable($table);
 
                    $maxBindParameters = ‪PlatformInformation::getMaxBindParameters($connection->getDatabasePlatform());
 
                    foreach (array_chunk($ids, $maxBindParameters, true) as $chunk) {
                        $queryBuilder = $connection->createQueryBuilder();
                        $queryBuilder->getRestrictions()
                            ->removeAll()
                            ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
                        $queryResult = $queryBuilder
                            ->select('uid')
                            ->from($table)
                            ->where(
                                $queryBuilder->expr()->in(
                                    'uid',
                                    $queryBuilder->createNamedParameter($chunk, ‪Connection::PARAM_INT_ARRAY)
                                )
                            )
                            ->executeQuery();
                        while ($row = $queryResult->fetchAssociative()) {
                            if (isset($dbArr[$row['uid']])) {
                                unset($dbArr[$row['uid']]);
                            } else {
                                $rows[] = 'Strange Error. ...';
                            }
                        }
                    }
 
                    foreach ($dbArr as $theId => $theC) {
                        $rows[] = 'There are ' . $theC . ' records pointing to this missing or deleted record: <code>[' . $table . '][' . $theId . ']</code>';
                    }
                }
            } else {
                $rows[] = 'Codeerror. Table is not a table...';
            }
        }
 
        return $rows !== [] ? '<ul class="list-unstyled" role="list">' . implode(LF, array_map(static fn(string $row): string => '<li>' . $row . '</li>', $rows)) . '</ul>' : '';
    }
 
    public function ‪getPageIdArray(): array
    {
        return ‪$this->pageIdArray;
    }
 
    public function ‪getCheckGroupDBRefs(): array
    {
        return ‪$this->checkGroupDBRefs;
    }
 
    public function ‪getCheckSelectDBRefs(): array
    {
        return ‪$this->checkSelectDBRefs;
    }
 
    public function ‪getRecStats(): array
    {
        return ‪$this->recStats;
    }
 
    public function ‪getLRecords(): array
    {
        return ‪$this->lRecords;
    }
 
    public function ‪getLostPagesList(): string
    {
        return ‪$this->lostPagesList;
    }
}