typo3/sysext/lowlevel/Classes/Command/OrphanRecordsCommand.php

   1 <?php
   2 declare(strict_types = 1);
   3 namespace TYPO3\CMS\Lowlevel\Command;
   4
   5 /*
   6  * This file is part of the TYPO3 CMS project.
   7  *
   8  * It is free software; you can redistribute it and/or modify it under
   9  * the terms of the GNU General Public License, either version 2
  10  * of the License, or any later version.
  11  *
  12  * For the full copyright and license information, please read the
  13  * LICENSE.txt file that was distributed with this source code.
  14  *
  15  * The TYPO3 project - inspiring people to share!
  16  */
  17
  18 use Symfony\Component\Console\Command\Command;
  19 use Symfony\Component\Console\Input\InputInterface;
  20 use Symfony\Component\Console\Input\InputOption;
  21 use Symfony\Component\Console\Output\OutputInterface;
  22 use Symfony\Component\Console\Style\SymfonyStyle;
  23 use TYPO3\CMS\Backend\Utility\BackendUtility;
  24 use TYPO3\CMS\Core\Core\Bootstrap;
  25 use TYPO3\CMS\Core\Database\ConnectionPool;
  26 use TYPO3\CMS\Core\DataHandling\DataHandler;
  27 use TYPO3\CMS\Core\Utility\GeneralUtility;
  28
  29 /**
  30  * Finds (and fixes) all records that have an invalid / deleted page ID
  31  */
  32 class OrphanRecordsCommand extends Command
  33 {
  34
  35     /**
  36      * Configure the command by defining the name, options and arguments
  37      */
  38     public function configure()
  39     {
  40         $this
  41             ->setDescription('Find and delete records that have lost their connection with the page tree.')
  42             ->setHelp('Assumption: All actively used records on the website from TCA configured tables are located in the page tree exclusively.
  43
  44 All records managed by TYPO3 via the TCA array configuration has to belong to a page in the page tree, either directly or indirectly as a version of another record.
  45 VERY TIME, CPU and MEMORY intensive operation since the full page tree is looked up!
  46
  47 Automatic Repair of Errors:
  48 - Silently deleting the orphaned records. In theory they should not be used anywhere in the system, but there could be references. See below for more details on this matter.
  49
  50 Manual repair suggestions:
  51 - Possibly re-connect orphaned records to page tree by setting their "pid" field to a valid page id. A lookup in the sys_refindex table can reveal if there are references to a orphaned record. If there are such references (from records that are not themselves orphans) you might consider to re-connect the record to the page tree, otherwise it should be safe to delete it.
  52
  53  If you want to get more detailed information, use the --verbose option.')
  54             ->addOption(
  55                 'dry-run',
  56                 null,
  57                 InputOption::VALUE_NONE,
  58                 'If this option is set, the records will not actually be deleted, but just the output which records would be deleted are shown'
  59             );
  60     }
  61
  62     /**
  63      * Executes the command to find records not attached to the pagetree
  64      * and permanently delete these records
  65      *
  66      * @param InputInterface $input
  67      * @param OutputInterface $output
  68      */
  69     protected function execute(InputInterface $input, OutputInterface $output)
  70     {
  71         // Make sure the _cli_ user is loaded
  72         Bootstrap::initializeBackendAuthentication();
  73
  74         $io = new SymfonyStyle($input, $output);
  75         $io->title($this->getDescription());
  76
  77         if ($io->isVerbose()) {
  78             $io->section('Searching the database now for orphaned records.');
  79         }
  80
  81         // type unsafe comparison and explicit boolean setting on purpose
  82         $dryRun = $input->hasOption('dry-run') && $input->getOption('dry-run') != false ? true : false;
  83
  84         // find all records that should be deleted
  85         $allRecords = $this->findAllConnectedRecordsInPage(0, 10000);
  86
  87         // Find orphans
  88         $orphans = [];
  89         foreach (array_keys($GLOBALS['TCA']) as $tableName) {
  90             $idList = [0];
  91             if (is_array($allRecords[$tableName]) && !empty($allRecords[$tableName])) {
  92                 $idList = $allRecords[$tableName];
  93             }
  94             // Select all records that are NOT connected
  95             $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
  96                 ->getQueryBuilderForTable($tableName);
  97
  98             $result = $queryBuilder
  99                 ->select('uid')
 100                 ->from($tableName)
 101                 ->where(
 102                     $queryBuilder->expr()->notIn(
 103                         'uid',
 104                         // do not use named parameter here as the list can get too long
 105                         array_map('intval', $idList)
 106                     )
 107                 )
 108                 ->orderBy('uid')
 109                 ->execute();
 110
 111             $totalOrphans = 0;
 112             $rowCount = $queryBuilder->count('uid')->execute()->fetchColumn(0);
 113             if ($rowCount) {
 114                 $orphans[$tableName] = [];
 115                 while ($orphanRecord = $result->fetch()) {
 116                     $orphans[$tableName][$orphanRecord['uid']] = $orphanRecord['uid'];
 117                 }
 118                 $totalOrphans += count($orphans[$tableName]);
 119
 120                 if ($io->isVeryVerbose() && count($orphans[$tableName])) {
 121                     $io->writeln('Found ' . count($orphans[$tableName]) . ' orphan records in table "' . $tableName . '".');
 122                 }
 123             }
 124             if (!$io->isQuiet() && $totalOrphans) {
 125                 $io->note('Found ' . $totalOrphans . ' records in ' . count($orphans) . ' database tables.');
 126             }
 127         }
 128
 129         if (count($orphans)) {
 130             $io->section('Deletion process starting now.' . ($dryRun ? ' (Not deleting now, just a dry run)' : ''));
 131
 132             // Actually permanently delete them
 133             $this->deleteRecords($orphans, $dryRun, $io);
 134
 135             $io->success('All done!');
 136         } else {
 137             $io->success('No orphan records found.');
 138         }
 139     }
 140
 141     /**
 142      * Recursive traversal of page tree to fetch all records marked as "deleted",
 143      * via option $GLOBALS[TCA][$tableName][ctrl][delete]
 144      * This also takes deleted versioned records into account.
 145      *
 146      * @param int $pageId the uid of the pages record (can also be 0)
 147      * @param int $depth The current depth of levels to go down
 148      * @param array $allRecords the records that are already marked as deleted (used when going recursive)
 149      *
 150      * @return array the modified $deletedRecords array
 151      */
 152     protected function findAllConnectedRecordsInPage(int $pageId, int $depth, array $allRecords = []): array
 153     {
 154         // Register page
 155         if ($pageId > 0) {
 156             $allRecords['pages'][$pageId] = $pageId;
 157         }
 158         // Traverse tables of records that belongs to page
 159         foreach (array_keys($GLOBALS['TCA']) as $tableName) {
 160             if ($tableName !== 'pages') {
 161                 // Select all records belonging to page:
 162                 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
 163                     ->getQueryBuilderForTable($tableName);
 164
 165                 $queryBuilder->getRestrictions()->removeAll();
 166
 167                 $result = $queryBuilder
 168                     ->select('uid')
 169                     ->from($tableName)
 170                     ->where(
 171                         $queryBuilder->expr()->eq(
 172                             'pid',
 173                             $queryBuilder->createNamedParameter($pageId, \PDO::PARAM_INT)
 174                         )
 175                     )
 176                     ->execute();
 177
 178                 while ($rowSub = $result->fetch()) {
 179                     $allRecords[$tableName][$rowSub['uid']] = $rowSub['uid'];
 180                     // Add any versions of those records:
 181                     $versions = BackendUtility::selectVersionsOfRecord($tableName, $rowSub['uid'], 'uid,t3ver_wsid,t3ver_count', null, true);
 182                     if (is_array($versions)) {
 183                         foreach ($versions as $verRec) {
 184                             if (!$verRec['_CURRENT_VERSION']) {
 185                                 $allRecords[$tableName][$verRec['uid']] = $verRec['uid'];
 186                             }
 187                         }
 188                     }
 189                 }
 190             }
 191         }
 192         // Find subpages to root ID and traverse (only when rootID is not a version or is a branch-version):
 193         if ($depth > 0) {
 194             $depth--;
 195             $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
 196                 ->getQueryBuilderForTable('pages');
 197
 198             $queryBuilder->getRestrictions()->removeAll();
 199
 200             $result = $queryBuilder
 201                 ->select('uid')
 202                 ->from('pages')
 203                 ->where(
 204                     $queryBuilder->expr()->eq(
 205                         'pid',
 206                         $queryBuilder->createNamedParameter($pageId, \PDO::PARAM_INT)
 207                     )
 208                 )
 209                 ->orderBy('sorting')
 210                 ->execute();
 211
 212             while ($row = $result->fetch()) {
 213                 $allRecords = $this->findAllConnectedRecordsInPage((int)$row['uid'], $depth, $allRecords);
 214             }
 215         }
 216
 217         // Add any versions of pages
 218         if ($pageId > 0) {
 219             $versions = BackendUtility::selectVersionsOfRecord('pages', $pageId, 'uid,t3ver_oid,t3ver_wsid,t3ver_count', null, true);
 220             if (is_array($versions)) {
 221                 foreach ($versions as $verRec) {
 222                     if (!$verRec['_CURRENT_VERSION']) {
 223                         $allRecords = $this->findAllConnectedRecordsInPage((int)$verRec['uid'], $depth, $allRecords);
 224                     }
 225                 }
 226             }
 227         }
 228         return $allRecords;
 229     }
 230
 231     /**
 232      * Deletes records via DataHandler
 233      *
 234      * @param array $orphanedRecords two level array with tables and uids
 235      * @param bool $dryRun check if the records should NOT be deleted (use --dry-run to avoid)
 236      * @param SymfonyStyle $io
 237      */
 238     protected function deleteRecords(array $orphanedRecords, bool $dryRun, SymfonyStyle $io)
 239     {
 240         // Putting "pages" table in the bottom
 241         if (isset($orphanedRecords['pages'])) {
 242             $_pages = $orphanedRecords['pages'];
 243             unset($orphanedRecords['pages']);
 244             // To delete sub pages first assuming they are accumulated from top of page tree.
 245             $orphanedRecords['pages'] = array_reverse($_pages);
 246         }
 247
 248         // set up the data handler instance
 249         $dataHandler = GeneralUtility::makeInstance(DataHandler::class);
 250         $dataHandler->start([], []);
 251
 252         // Loop through all tables and their records
 253         foreach ($orphanedRecords as $table => $list) {
 254             if ($io->isVerbose()) {
 255                 $io->writeln('Flushing ' . count($list) . ' orphaned records from table "' . $table . '"');
 256             }
 257             foreach ($list as $uid) {
 258                 if ($io->isVeryVerbose()) {
 259                     $io->writeln('Flushing record "' . $table . ':' . $uid . '"');
 260                 }
 261                 if (!$dryRun) {
 262                     // Notice, we are deleting pages with no regard to subpages/subrecords - we do this since they
 263                     // should also be included in the set of deleted pages of course (no un-deleted record can exist
 264                     // under a deleted page...)
 265                     $dataHandler->deleteRecord($table, $uid, true, true);
 266                     // Return errors if any:
 267                     if (!empty($dataHandler->errorLog)) {
 268                         $errorMessage = array_merge(['DataHandler reported an error'], $dataHandler->errorLog);
 269                         $io->error($errorMessage);
 270                     } elseif (!$io->isQuiet()) {
 271                         $io->writeln('Permanently deleted orphaned record "' . $table . ':' . $uid . '".');
 272                     }
 273                 }
 274             }
 275         }
 276     }
 277 }