TYPO3CMS  8
 All Classes Namespaces Files Functions Variables Pages
OrphanRecordsCommand.php
Go to the documentation of this file.
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;
29 
33 class OrphanRecordsCommand extends Command
34 {
35 
39  public function configure()
40  {
41  $this
42  ->setDescription('Find and delete records that have lost their connection with the page tree.')
43  ->setHelp('Assumption: All actively used records on the website from TCA configured tables are located in the page tree exclusively.
44 
45 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.
46 VERY TIME, CPU and MEMORY intensive operation since the full page tree is looked up!
47 
48 Automatic Repair of Errors:
49 - 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.
50 
51 Manual repair suggestions:
52 - 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.
53 
54  If you want to get more detailed information, use the --verbose option.')
55  ->addOption(
56  'dry-run',
57  null,
58  InputOption::VALUE_NONE,
59  'If this option is set, the records will not actually be deleted, but just the output which records would be deleted are shown'
60  );
61  }
62 
72  protected function execute(InputInterface $input, OutputInterface $output)
73  {
74  // The backend user needs super-powers because datahandler is executed
75  $previouslyAppliedAdminRights = $this->getBackendUser()->user['admin'];
76  $this->getBackendUser()->user['admin'] = 1;
77 
78  $io = new SymfonyStyle($input, $output);
79  $io->title($this->getDescription());
80 
81  if ($io->isVerbose()) {
82  $io->section('Searching the database now for orphaned records.');
83  }
84 
85  // type unsafe comparison and explicit boolean setting on purpose
86  $dryRun = $input->hasOption('dry-run') && $input->getOption('dry-run') != false ? true : false;
87 
88  // find all records that should be deleted
89  $allRecords = $this->findAllConnectedRecordsInPage(0, 10000);
90 
91  // Find orphans
92  $orphans = [];
93  foreach (array_keys($GLOBALS['TCA']) as $tableName) {
94  $idList = [0];
95  if (is_array($allRecords[$tableName]) && !empty($allRecords[$tableName])) {
96  $idList = $allRecords[$tableName];
97  }
98  // Select all records that are NOT connected
99  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
100  ->getQueryBuilderForTable($tableName);
101 
102  $result = $queryBuilder
103  ->select('uid')
104  ->from($tableName)
105  ->where(
106  $queryBuilder->expr()->notIn(
107  'uid',
108  $queryBuilder->createNamedParameter($idList, Connection::PARAM_INT_ARRAY)
109  )
110  )
111  ->orderBy('uid')
112  ->execute();
113 
114  $totalOrphans = 0;
115  if ($result->rowCount()) {
116  $orphans[$tableName] = [];
117  while ($orphanRecord = $result->fetch()) {
118  $orphans[$tableName][$orphanRecord['uid']] = $orphanRecord['uid'];
119  }
120  $totalOrphans += count($orphans[$tableName]);
121 
122  if ($io->isVeryVerbose() && count($orphans[$tableName])) {
123  $io->writeln('Found ' . count($orphans[$tableName]) . ' orphan records in table "' . $tableName . '".');
124  }
125  }
126  if (!$io->isQuiet() && $totalOrphans) {
127  $io->note('Found ' . $totalOrphans . ' records in ' . count($orphans) . ' database tables.');
128  }
129  }
130 
131  if (count($orphans)) {
132  $io->section('Deletion process starting now.' . ($dryRun ? ' (Not deleting now, just a dry run)' : ''));
133 
134  // Actually permanently delete them
135  $this->deleteRecords($orphans, $dryRun, $io);
136 
137  $io->success('All done!');
138  } else {
139  $io->success('No orphan records found.');
140  }
141 
142  // Restore backend user administration rights
143  $this->getBackendUser()->user['admin'] = $previouslyAppliedAdminRights;
144  }
145 
157  protected function findAllConnectedRecordsInPage(int $pageId, int $depth, array $allRecords = []): array
158  {
159  // Register page
160  if ($pageId > 0) {
161  $allRecords['pages'][$pageId] = $pageId;
162  }
163  // Traverse tables of records that belongs to page
164  foreach (array_keys($GLOBALS['TCA']) as $tableName) {
165  if ($tableName !== 'pages') {
166  // Select all records belonging to page:
167  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
168  ->getQueryBuilderForTable($tableName);
169 
170  $queryBuilder->getRestrictions()->removeAll();
171 
172  $result = $queryBuilder
173  ->select('uid')
174  ->from($tableName)
175  ->where(
176  $queryBuilder->expr()->eq(
177  'pid',
178  $queryBuilder->createNamedParameter($pageId, \PDO::PARAM_INT)
179  )
180  )
181  ->execute();
182 
183  while ($rowSub = $result->fetch()) {
184  $allRecords[$tableName][$rowSub['uid']] = $rowSub['uid'];
185  // Add any versions of those records:
186  $versions = BackendUtility::selectVersionsOfRecord($tableName, $rowSub['uid'], 'uid,t3ver_wsid,t3ver_count', null, true);
187  if (is_array($versions)) {
188  foreach ($versions as $verRec) {
189  if (!$verRec['_CURRENT_VERSION']) {
190  $allRecords[$tableName][$verRec['uid']] = $verRec['uid'];
191  }
192  }
193  }
194  }
195  }
196  }
197  // Find subpages to root ID and traverse (only when rootID is not a version or is a branch-version):
198  if ($depth > 0) {
199  $depth--;
200  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
201  ->getQueryBuilderForTable('pages');
202 
203  $queryBuilder->getRestrictions()->removeAll();
204 
205  $result = $queryBuilder
206  ->select('uid')
207  ->from('pages')
208  ->where(
209  $queryBuilder->expr()->eq(
210  'pid',
211  $queryBuilder->createNamedParameter($pageId, \PDO::PARAM_INT)
212  )
213  )
214  ->orderBy('sorting')
215  ->execute();
216 
217  while ($row = $result->fetch()) {
218  $allRecords = $this->findAllConnectedRecordsInPage($row['uid'], $depth, $allRecords);
219  }
220  }
221 
222  // Add any versions of pages
223  if ($pageId > 0) {
224  $versions = BackendUtility::selectVersionsOfRecord('pages', $pageId, 'uid,t3ver_oid,t3ver_wsid,t3ver_count', null, true);
225  if (is_array($versions)) {
226  foreach ($versions as $verRec) {
227  if (!$verRec['_CURRENT_VERSION']) {
228  $allRecords = $this->findAllConnectedRecordsInPage($verRec['uid'], $depth, $allRecords);
229  }
230  }
231  }
232  }
233  return $allRecords;
234  }
235 
244  protected function deleteRecords(array $orphanedRecords, bool $dryRun, SymfonyStyle $io)
245  {
246  // Putting "pages" table in the bottom
247  if (isset($orphanedRecords['pages'])) {
248  $_pages = $orphanedRecords['pages'];
249  unset($orphanedRecords['pages']);
250  // To delete sub pages first assuming they are accumulated from top of page tree.
251  $orphanedRecords['pages'] = array_reverse($_pages);
252  }
253 
254  // set up the data handler instance
255  $dataHandler = GeneralUtility::makeInstance(DataHandler::class);
256  $dataHandler->start([], []);
257 
258  // Loop through all tables and their records
259  foreach ($orphanedRecords as $table => $list) {
260  if ($io->isVerbose()) {
261  $io->writeln('Flushing ' . count($list) . ' orphaned records from table "' . $table . '"');
262  }
263  foreach ($list as $uid) {
264  if ($io->isVeryVerbose()) {
265  $io->writeln('Flushing record "' . $table . ':' . $uid . '"');
266  }
267  if (!$dryRun) {
268  // Notice, we are deleting pages with no regard to subpages/subrecords - we do this since they
269  // should also be included in the set of deleted pages of course (no un-deleted record can exist
270  // under a deleted page...)
271  $dataHandler->deleteRecord($table, $uid, true, true);
272  // Return errors if any:
273  if (!empty($dataHandler->errorLog)) {
274  $errorMessage = array_merge(['DataHandler reported an error'], $dataHandler->errorLog);
275  $io->error($errorMessage);
276  } elseif (!$io->isQuiet()) {
277  $io->writeln('Permanently deleted orphaned record "' . $table . ':' . $uid . '".');
278  }
279  }
280  }
281  }
282  }
283 
289  {
290  return $GLOBALS['BE_USER'];
291  }
292 }
findAllConnectedRecordsInPage(int $pageId, int $depth, array $allRecords=[])
execute(InputInterface $input, OutputInterface $output)
static selectVersionsOfRecord($table, $uid, $fields= '*', $workspace=0, $includeDeletedRecords=false, $row=null)
deleteRecords(array $orphanedRecords, bool $dryRun, SymfonyStyle $io)
if(TYPO3_MODE=== 'BE') $GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['t3lib/class.t3lib_tsfebeuserauth.php']['frontendEditingController']['default']
static makeInstance($className,...$constructorArguments)