‪TYPO3CMS  ‪main
WorkspaceVersionRecordsCommand.php
Go to the documentation of this file.
1 <?php
2 
3 declare(strict_types=1);
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 
19 
20 use Symfony\Component\Console\Attribute\AsCommand;
21 use Symfony\Component\Console\Command\Command;
22 use Symfony\Component\Console\Input\InputInterface;
23 use Symfony\Component\Console\Input\InputOption;
24 use Symfony\Component\Console\Output\OutputInterface;
25 use Symfony\Component\Console\Style\SymfonyStyle;
26 use TYPO3\CMS\Backend\Utility\BackendUtility;
34 
40 #[AsCommand('cleanup:versions', 'Find all versioned records and possibly cleans up invalid records in the database.')]
41 class ‪WorkspaceVersionRecordsCommand extends Command
42 {
47  protected ‪$allWorkspaces = [0 => 'Live Workspace'];
48 
49  public function ‪__construct(private readonly ‪ConnectionPool $connectionPool)
50  {
51  parent::__construct();
52  }
53 
58  protected ‪$foundRecords = [
59  // All versions of records found
60  // Subset of "all" which are offline versions (t3ver_oid > 0) [Informational]
61  'all_versioned_records' => [],
62  // All records that has been published and can therefore be removed permanently
63  // Subset of "versions" that is a count of 1 or more (has been published) [Informational]
64  'published_versions' => [],
65  // All versions that are offline versions in the Live workspace. You may wish to flush these if you only use
66  // workspaces for versioning since then you might find lots of versions piling up in the live workspace which
67  // have simply been disconnected from the workspace before they were published.
68  'versions_in_live' => [],
69  // Versions that has lost their connection to a workspace in TYPO3.
70  // Subset of "versions" that doesn't belong to an existing workspace [Warning: Fix by move to live workspace]
71  'invalid_workspace' => [],
72  ];
73 
77  public function ‪configure()
78  {
79  $this
80  ->setHelp('Traverse page tree and find versioned records. Also list all versioned records, additionally with some inconsistencies in the database, which can cleaned up with the "action" option. If you want to get more detailed information, use the --verbose option.')
81  ->addOption(
82  'pid',
83  'p',
84  InputOption::VALUE_REQUIRED,
85  'Setting start page in page tree. Default is the page tree root, 0 (zero)'
86  )
87  ->addOption(
88  'depth',
89  'd',
90  InputOption::VALUE_REQUIRED,
91  'Setting traversal depth. 0 (zero) will only analyze start page (see --pid), 1 will traverse one level of subpages etc.'
92  )
93  ->addOption(
94  'dry-run',
95  null,
96  InputOption::VALUE_NONE,
97  'If this option is set, the records will not actually be deleted/modified, but just the output which records would be touched are shown'
98  )
99  ->addOption(
100  'action',
101  null,
102  InputOption::VALUE_OPTIONAL,
103  'Specify which action should be taken. Set it to "versions_in_live", "published_versions" or "invalid_workspace"'
104  );
105  }
106 
110  protected function ‪execute(InputInterface $input, OutputInterface $output): int
111  {
112  // Make sure the _cli_ user is loaded
114 
115  $io = new SymfonyStyle($input, $output);
116  $io->title($this->getDescription());
117 
118  $startingPoint = 0;
119  if ($input->hasOption('pid') && ‪MathUtility::canBeInterpretedAsInteger($input->getOption('pid'))) {
120  $startingPoint = ‪MathUtility::forceIntegerInRange((int)$input->getOption('pid'), 0);
121  }
122 
123  $depth = 1000;
124  if ($input->hasOption('depth') && ‪MathUtility::canBeInterpretedAsInteger($input->getOption('depth'))) {
125  $depth = ‪MathUtility::forceIntegerInRange((int)$input->getOption('depth'), 0);
126  }
127 
128  $action = '';
129  if ($input->hasOption('action') && !empty($input->getOption('action'))) {
130  $action = $input->getOption('action');
131  }
132 
133  // type unsafe comparison and explicit boolean setting on purpose
134  $dryRun = $input->hasOption('dry-run') && $input->getOption('dry-run') != false ? true : false;
135 
136  if ($io->isVerbose()) {
137  $io->section('Searching the database now for versioned records.');
138  }
139 
141 
142  // Find all records that are versioned
143  $this->‪traversePageTreeForVersionedRecords($startingPoint, $depth);
144  // Sort recStats (for diff'able displays)
145  foreach ($this->foundRecords as $kk => $vv) {
146  foreach ($this->foundRecords[$kk] as $tables => $recArrays) {
147  ksort($this->foundRecords[$kk][$tables]);
148  }
149  ksort($this->foundRecords[$kk]);
150  }
151 
152  if (!$io->isQuiet()) {
153  $numberOfVersionedRecords = 0;
154  foreach ($this->foundRecords['all_versioned_records'] as $records) {
155  $numberOfVersionedRecords += count($records);
156  }
157 
158  $io->section('Found ' . $numberOfVersionedRecords . ' versioned records in the database.');
159  if ($io->isVeryVerbose()) {
160  foreach ($this->foundRecords['all_versioned_records'] as $table => $records) {
161  $io->writeln('Table "' . $table . '"');
162  $io->listing($records);
163  }
164  }
165 
166  $numberOfPublishedVersions = 0;
167  foreach ($this->foundRecords['published_versions'] as $records) {
168  $numberOfPublishedVersions += count($records);
169  }
170  $io->section('Found ' . $numberOfPublishedVersions . ' versioned records that have been published.');
171  if ($io->isVeryVerbose()) {
172  foreach ($this->foundRecords['published_versions'] as $table => $records) {
173  $io->writeln('Table "' . $table . '"');
174  $io->listing($records);
175  }
176  }
177 
178  $numberOfVersionsInLiveWorkspace = 0;
179  foreach ($this->foundRecords['versions_in_live'] as $records) {
180  $numberOfVersionsInLiveWorkspace += count($records);
181  }
182  $io->section('Found ' . $numberOfVersionsInLiveWorkspace . ' versioned records that are in the live workspace.');
183  if ($io->isVeryVerbose()) {
184  foreach ($this->foundRecords['versions_in_live'] as $table => $records) {
185  $io->writeln('Table "' . $table . '"');
186  $io->listing($records);
187  }
188  }
189 
190  $numberOfVersionsWithInvalidWorkspace = 0;
191  foreach ($this->foundRecords['invalid_workspace'] as $records) {
192  $numberOfVersionsWithInvalidWorkspace += count($records);
193  }
194  $io->section('Found ' . $numberOfVersionsWithInvalidWorkspace . ' versioned records with an invalid workspace.');
195  if ($io->isVeryVerbose()) {
196  foreach ($this->foundRecords['invalid_workspace'] as $table => $records) {
197  $io->writeln('Table "' . $table . '"');
198  $io->listing($records);
199  }
200  }
201  }
202 
203  // Actually permanently delete / update records
204  switch ($action) {
205  // All versions that are offline versions in the Live workspace. You may wish to flush these if you only use
206  // workspaces for versioning since then you might find lots of versions piling up in the live workspace which
207  // have simply been disconnected from the workspace before they were published.
208  case 'versions_in_live':
209  $io->section('Deleting versioned records in live workspace now. ' . ($dryRun ? ' (Not deleting now, just a dry run)' : ''));
210  $this->‪deleteRecords($this->foundRecords['versions_in_live'], $dryRun, $io);
211  break;
212 
213  // All records that has been published and can therefore be removed permanently
214  // Subset of "versions" that is a count of 1 or more (has been published)
215  case 'published_versions':
216  $io->section('Deleting published records in live workspace now. ' . ($dryRun ? ' (Not deleting now, just a dry run)' : ''));
217  $this->‪deleteRecords($this->foundRecords['published_versions'], $dryRun, $io);
218  break;
219 
220  // Versions that has lost their connection to a workspace in TYPO3.
221  // Subset of "versions" that doesn't belong to an existing workspace [Warning: Fix by move to live workspace]
222  case 'invalid_workspace':
223  $io->section('Moving versions in invalid workspaces to live workspace now. ' . ($dryRun ? ' (Not deleting now, just a dry run)' : ''));
224  $this->‪resetRecordsWithoutValidWorkspace($this->foundRecords['invalid_workspace'], $dryRun, $io);
225  break;
226 
227  default:
228  $io->note('No action specified, just displaying statistics. See --action option for details.');
229  break;
230  }
231  $io->success('All done!');
232  return Command::SUCCESS;
233  }
234 
243  protected function ‪traversePageTreeForVersionedRecords(int $rootID, int $depth, bool $isInsideVersionedPage = false, bool $rootIsVersion = false)
244  {
245  $queryBuilder = $this->connectionPool->getQueryBuilderForTable('pages');
246  $queryBuilder->getRestrictions()->removeAll();
247 
248  $pageRecord = $queryBuilder
249  ->select(
250  'deleted',
251  'title',
252  't3ver_wsid'
253  )
254  ->from('pages')
255  ->where($queryBuilder->expr()->eq('uid', $queryBuilder->createNamedParameter($rootID, ‪Connection::PARAM_INT)))
256  ->executeQuery()
257  ->fetchAssociative();
258 
259  // If rootIsVersion is set it means that the input rootID is that of a version of a page. See below where the recursive call is made.
260  if ($rootIsVersion) {
261  $workspaceId = (int)$pageRecord['t3ver_wsid'];
262  $this->foundRecords['all_versioned_records']['pages'][$rootID] = $rootID;
263  // If it has been published and is in archive now...
264  if ($workspaceId === 0) {
265  $this->foundRecords['versions_in_live']['pages'][$rootID] = $rootID;
266  }
267  // If it doesn't belong to a workspace...
268  if (!isset($this->allWorkspaces[$workspaceId])) {
269  $this->foundRecords['invalid_workspace']['pages'][$rootID] = $rootID;
270  }
271  }
272  // Only check for records if not inside a version
273  if (!$isInsideVersionedPage) {
274  // Traverse tables of records that belongs to page
275  $tableNames = $this->‪getAllVersionableTables();
276  foreach ($tableNames as $tableName) {
277  if ($tableName !== 'pages') {
278  // Select all records belonging to page:
279  $queryBuilder = $this->connectionPool
280  ->getQueryBuilderForTable($tableName);
281 
282  $queryBuilder->getRestrictions()->removeAll();
283 
284  $result = $queryBuilder
285  ->select('uid')
286  ->from($tableName)
287  ->where(
288  $queryBuilder->expr()->eq(
289  'pid',
290  $queryBuilder->createNamedParameter($rootID, ‪Connection::PARAM_INT)
291  )
292  )
293  ->executeQuery();
294  while ($rowSub = $result->fetchAssociative()) {
295  // Add any versions of those records
296  $versions = BackendUtility::selectVersionsOfRecord($tableName, $rowSub['uid'], 'uid,t3ver_wsid' . ((‪$GLOBALS['TCA'][$tableName]['ctrl']['delete'] ?? false) ? ',' . ‪$GLOBALS['TCA'][$tableName]['ctrl']['delete'] : ''), null, true);
297  if (is_array($versions)) {
298  foreach ($versions as $verRec) {
299  if (!($verRec['_CURRENT_VERSION'] ?? false)) {
300  // Register version
301  $this->foundRecords['all_versioned_records'][$tableName][$verRec['uid']] = $verRec['uid'];
302  $workspaceId = (int)$verRec['t3ver_wsid'];
303  if ($workspaceId === 0) {
304  $this->foundRecords['versions_in_live'][$tableName][$verRec['uid']] = $verRec['uid'];
305  }
306  if (!isset($this->allWorkspaces[$workspaceId])) {
307  $this->foundRecords['invalid_workspace'][$tableName][$verRec['uid']] = $verRec['uid'];
308  }
309  }
310  }
311  }
312  }
313  }
314  }
315  }
316  // Find subpages to root ID and traverse (only when rootID is not a version or is a branch-version):
317  if ($depth > 0) {
318  $depth--;
319  $queryBuilder = $this->connectionPool
320  ->getQueryBuilderForTable('pages');
321 
322  $queryBuilder->getRestrictions()->removeAll();
323  $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
324 
325  $queryBuilder
326  ->select('uid')
327  ->from('pages')
328  ->where(
329  $queryBuilder->expr()->eq(
330  'pid',
331  $queryBuilder->createNamedParameter($rootID, ‪Connection::PARAM_INT)
332  )
333  )
334  ->orderBy('sorting');
335 
336  $result = $queryBuilder->executeQuery();
337  while ($row = $result->fetchAssociative()) {
338  $this->‪traversePageTreeForVersionedRecords((int)$row['uid'], $depth, $isInsideVersionedPage, false);
339  }
340  }
341  // Add any versions of pages
342  if ($rootID > 0) {
343  $versions = BackendUtility::selectVersionsOfRecord('pages', $rootID, 'uid,t3ver_oid,t3ver_wsid', null, true);
344  if (is_array($versions)) {
345  foreach ($versions as $verRec) {
346  if (!($verRec['_CURRENT_VERSION'] ?? false)) {
347  $this->‪traversePageTreeForVersionedRecords((int)$verRec['uid'], $depth, true, true);
348  }
349  }
350  }
351  }
352  }
353 
354  /**************************
355  * actions / delete methods
356  **************************/
363  protected function ‪deleteRecords(array $records, bool $dryRun, SymfonyStyle $io)
364  {
365  // Putting "pages" table in the bottom
366  if (isset($records['pages'])) {
367  $_pages = $records['pages'];
368  unset($records['pages']);
369  // To delete sub pages first assuming they are accumulated from top of page tree.
370  $records['pages'] = array_reverse($_pages);
371  }
372 
373  // Set up the data handler instance
374  $dataHandler = GeneralUtility::makeInstance(DataHandler::class);
375  $dataHandler->start([], []);
376 
377  // Traversing records
378  foreach ($records as $table => $uidsInTable) {
379  if ($io->isVerbose()) {
380  $io->writeln('Flushing published records from table "' . $table . '"');
381  }
382  foreach ($uidsInTable as ‪$uid) {
383  if ($io->isVeryVerbose()) {
384  $io->writeln('Flushing record "' . $table . ':' . ‪$uid . '"');
385  }
386  if (!$dryRun) {
387  $dataHandler->deleteEl($table, ‪$uid, true, true);
388  if (!empty($dataHandler->errorLog)) {
389  $errorMessage = array_merge(['DataHandler reported an error'], $dataHandler->errorLog);
390  $io->error($errorMessage);
391  } elseif (!$io->isQuiet()) {
392  $io->writeln('Flushed published record "' . $table . ':' . ‪$uid . '".');
393  }
394  }
395  }
396  }
397  }
398 
406  protected function ‪resetRecordsWithoutValidWorkspace(array $records, bool $dryRun, SymfonyStyle $io)
407  {
408  foreach ($records as $table => $uidsInTable) {
409  if ($io->isVerbose()) {
410  $io->writeln('Resetting workspace to zero for records from table "' . $table . '"');
411  }
412  foreach ($uidsInTable as ‪$uid) {
413  if ($io->isVeryVerbose()) {
414  $io->writeln('Flushing record "' . $table . ':' . ‪$uid . '"');
415  }
416  if (!$dryRun) {
417  $queryBuilder = $this->connectionPool
418  ->getQueryBuilderForTable($table);
419 
420  $queryBuilder
421  ->update($table)
422  ->where(
423  $queryBuilder->expr()->eq(
424  'uid',
425  $queryBuilder->createNamedParameter(‪$uid, ‪Connection::PARAM_INT)
426  )
427  )
428  ->set('t3ver_wsid', 0)
429  ->executeStatement();
430  if (!$io->isQuiet()) {
431  $io->writeln('Flushed record "' . $table . ':' . ‪$uid . '".');
432  }
433  }
434  }
435  }
436  }
437 
447  protected function ‪loadAllWorkspaceRecords(): array
448  {
449  $queryBuilder = $this->connectionPool
450  ->getQueryBuilderForTable('sys_workspace');
451 
452  $queryBuilder->getRestrictions()
453  ->removeAll()
454  ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
455 
456  $result = $queryBuilder
457  ->select('uid', 'title')
458  ->from('sys_workspace')
459  ->executeQuery();
460 
461  while ($workspaceRecord = $result->fetchAssociative()) {
462  $this->allWorkspaces[(int)$workspaceRecord['uid']] = $workspaceRecord['title'];
463  }
465  }
466 
470  protected function ‪getAllVersionableTables(): array
471  {
472  $tables = [];
473  foreach (‪$GLOBALS['TCA'] as $tableName => $config) {
474  if (BackendUtility::isTableWorkspaceEnabled($tableName)) {
475  $tables[] = $tableName;
476  }
477  }
478  return $tables;
479  }
480 }
‪TYPO3\CMS\Core\DataHandling\DataHandler
Definition: DataHandler.php:104
‪TYPO3\CMS\Workspaces\Command\WorkspaceVersionRecordsCommand\execute
‪execute(InputInterface $input, OutputInterface $output)
Definition: WorkspaceVersionRecordsCommand.php:108
‪TYPO3\CMS\Core\Database\Connection\PARAM_INT
‪const PARAM_INT
Definition: Connection.php:52
‪TYPO3\CMS\Workspaces\Command\WorkspaceVersionRecordsCommand\__construct
‪__construct(private readonly ConnectionPool $connectionPool)
Definition: WorkspaceVersionRecordsCommand.php:48
‪TYPO3\CMS\Workspaces\Command\WorkspaceVersionRecordsCommand
Definition: WorkspaceVersionRecordsCommand.php:42
‪TYPO3\CMS\Workspaces\Command\WorkspaceVersionRecordsCommand\deleteRecords
‪deleteRecords(array $records, bool $dryRun, SymfonyStyle $io)
Definition: WorkspaceVersionRecordsCommand.php:361
‪TYPO3\CMS\Workspaces\Command\WorkspaceVersionRecordsCommand\$allWorkspaces
‪array $allWorkspaces
Definition: WorkspaceVersionRecordsCommand.php:46
‪TYPO3\CMS\Workspaces\Command\WorkspaceVersionRecordsCommand\loadAllWorkspaceRecords
‪array loadAllWorkspaceRecords()
Definition: WorkspaceVersionRecordsCommand.php:445
‪TYPO3\CMS\Workspaces\Command
Definition: AutoPublishCommand.php:18
‪TYPO3\CMS\Core\Utility\MathUtility\canBeInterpretedAsInteger
‪static bool canBeInterpretedAsInteger(mixed $var)
Definition: MathUtility.php:74
‪TYPO3\CMS\Workspaces\Command\WorkspaceVersionRecordsCommand\$foundRecords
‪array $foundRecords
Definition: WorkspaceVersionRecordsCommand.php:56
‪TYPO3\CMS\Workspaces\Command\WorkspaceVersionRecordsCommand\traversePageTreeForVersionedRecords
‪traversePageTreeForVersionedRecords(int $rootID, int $depth, bool $isInsideVersionedPage=false, bool $rootIsVersion=false)
Definition: WorkspaceVersionRecordsCommand.php:241
‪TYPO3\CMS\Workspaces\Command\WorkspaceVersionRecordsCommand\getAllVersionableTables
‪getAllVersionableTables()
Definition: WorkspaceVersionRecordsCommand.php:468
‪TYPO3\CMS\Workspaces\Command\WorkspaceVersionRecordsCommand\configure
‪configure()
Definition: WorkspaceVersionRecordsCommand.php:75
‪TYPO3\CMS\Core\Database\Connection
Definition: Connection.php:41
‪TYPO3\CMS\Webhooks\Message\$uid
‪identifier readonly int $uid
Definition: PageModificationMessage.php:35
‪$GLOBALS
‪$GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['adminpanel']['modules']
Definition: ext_localconf.php:25
‪TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction
Definition: DeletedRestriction.php:28
‪TYPO3\CMS\Core\Core\Bootstrap
Definition: Bootstrap.php:63
‪TYPO3\CMS\Core\Utility\MathUtility
Definition: MathUtility.php:24
‪TYPO3\CMS\Core\Database\ConnectionPool
Definition: ConnectionPool.php:46
‪TYPO3\CMS\Core\Utility\MathUtility\forceIntegerInRange
‪static int forceIntegerInRange(mixed $theInt, int $min, int $max=2000000000, int $defaultValue=0)
Definition: MathUtility.php:34
‪TYPO3\CMS\Core\Utility\GeneralUtility
Definition: GeneralUtility.php:52
‪TYPO3\CMS\Workspaces\Command\WorkspaceVersionRecordsCommand\resetRecordsWithoutValidWorkspace
‪resetRecordsWithoutValidWorkspace(array $records, bool $dryRun, SymfonyStyle $io)
Definition: WorkspaceVersionRecordsCommand.php:404
‪TYPO3\CMS\Core\Core\Bootstrap\initializeBackendAuthentication
‪static initializeBackendAuthentication()
Definition: Bootstrap.php:527