‪TYPO3CMS  11.5
DatabaseIntegrityCheck.php
Go to the documentation of this file.
1 <?php
2 
3 /*
4  * This file is part of the TYPO3 CMS project.
5  *
6  * It is free software; you can redistribute it and/or modify it under
7  * the terms of the GNU General Public License, either version 2
8  * of the License, or any later version.
9  *
10  * For the full copyright and license information, please read the
11  * LICENSE.txt file that was distributed with this source code.
12  *
13  * The TYPO3 project - inspiring people to share!
14  */
15 
17 
18 use Doctrine\DBAL\Types\Types;
19 use TYPO3\CMS\Backend\Utility\BackendUtility;
26 
41 {
45  protected ‪$genTreeIncludeDeleted = true;
46 
50  protected ‪$genTreeIncludeVersions = true;
51 
55  protected ‪$genTreeIncludeRecords = false;
56 
60  protected ‪$pageIdArray = [];
61 
65  protected ‪$pageTranslatedPageIDArray = [];
66 
70  protected ‪$recIdArray = [];
71 
75  protected ‪$checkSelectDBRefs = [];
76 
80  protected ‪$checkGroupDBRefs = [];
81 
85  protected ‪$recStats = [
86  'allValid' => [],
87  'published_versions' => [],
88  'deleted' => [],
89  ];
90 
94  protected ‪$lRecords = [];
95 
99  protected ‪$lostPagesList = '';
100 
104  public function ‪getPageTranslatedPageIDArray(): array
105  {
107  }
108 
116  public function ‪genTree($theID, $versions = false)
117  {
118  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
119  $queryBuilder->getRestrictions()->removeAll();
120  if (!$this->genTreeIncludeDeleted) {
121  $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
122  }
123  $queryBuilder->select('uid', 'title', 'doktype', 'deleted', 'hidden', 'sys_language_uid')
124  ->from('pages')
125  ->orderBy('sorting');
126  if ($versions) {
127  $queryBuilder->addSelect('t3ver_wsid');
128  $queryBuilder->where(
129  $queryBuilder->expr()->eq('t3ver_oid', $queryBuilder->createNamedParameter($theID, ‪Connection::PARAM_INT))
130  );
131  } else {
132  $queryBuilder->where(
133  $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($theID, ‪Connection::PARAM_INT))
134  );
135  }
136  $result = $queryBuilder->executeQuery();
137  // Traverse the records selected
138  while ($row = $result->fetchAssociative()) {
139  $newID = $row['uid'];
140  // Register various data for this item:
141  if ($row['sys_language_uid'] === 0) {
142  $this->pageIdArray[$newID] = $row;
143  } else {
144  $this->pageTranslatedPageIDArray[$newID] = $row;
145  }
146  $this->recStats['all_valid']['pages'][$newID] = $newID;
147  if ($row['deleted']) {
148  $this->recStats['deleted']['pages'][$newID] = $newID;
149  }
150 
151  if (!isset($this->recStats['hidden'])) {
152  $this->recStats['hidden'] = 0;
153  }
154 
155  if ($row['hidden']) {
156  $this->recStats['hidden']++;
157  }
158 
159  $this->recStats['doktype'][$row['doktype']] ??= 0;
160  $this->recStats['doktype'][$row['doktype']]++;
161  // If all records should be shown, do so:
162  if ($this->genTreeIncludeRecords) {
163  foreach (‪$GLOBALS['TCA'] as $tableName => $cfg) {
164  if ($tableName !== 'pages') {
165  $this->‪genTree_records($newID, $tableName);
166  }
167  }
168  }
169  // Add sub pages:
170  $this->‪genTree($newID);
171  // If versions are included in the tree, add those now:
172  if ($this->genTreeIncludeVersions) {
173  $this->‪genTree($newID, true);
174  }
175  }
176  }
177 
183  public function ‪genTree_records($theID, $table, $versions = false): void
184  {
185  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
186  $queryBuilder->getRestrictions()->removeAll();
187  if (!$this->genTreeIncludeDeleted) {
188  $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
189  }
190  $queryBuilder
191  ->select(...explode(',', BackendUtility::getCommonSelectFields($table)))
192  ->from($table);
193 
194  // Select all records from table pointing to this page
195  if ($versions) {
196  $queryBuilder->where(
197  $queryBuilder->expr()->eq('t3ver_oid', $queryBuilder->createNamedParameter($theID, ‪Connection::PARAM_INT))
198  );
199  } else {
200  $queryBuilder->where(
201  $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($theID, ‪Connection::PARAM_INT))
202  );
203  }
204  $queryResult = $queryBuilder->executeQuery();
205  // Traverse selected
206  while ($row = $queryResult->fetchAssociative()) {
207  $newID = $row['uid'];
208  // Register various data for this item:
209  $this->recIdArray[$table][$newID] = $row;
210  $this->recStats['all_valid'][$table][$newID] = $newID;
211  if ($row['deleted']) {
212  $this->recStats['deleted'][$table][$newID] = $newID;
213  }
214  // Select all versions of this record:
215  if ($this->genTreeIncludeVersions && BackendUtility::isTableWorkspaceEnabled($table)) {
216  $this->‪genTree_records($newID, $table, true);
217  }
218  }
219  }
220 
226  public function ‪lostRecords($pid_list): void
227  {
228  $this->lostPagesList = '';
229  $pageIds = ‪GeneralUtility::intExplode(',', $pid_list);
230  if (is_array($pageIds)) {
231  foreach (‪$GLOBALS['TCA'] as $table => $tableConf) {
232  $pageIdsForTable = $pageIds;
233  // Remove preceding "-1," for non-versioned tables
234  if (!BackendUtility::isTableWorkspaceEnabled($table)) {
235  $pageIdsForTable = array_combine($pageIdsForTable, $pageIdsForTable);
236  unset($pageIdsForTable[-1]);
237  }
238  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
239  $queryBuilder->getRestrictions()->removeAll();
240  $selectFields = ['uid', 'pid'];
241  if (!empty(‪$GLOBALS['TCA'][$table]['ctrl']['label'])) {
242  $selectFields[] = ‪$GLOBALS['TCA'][$table]['ctrl']['label'];
243  }
244  $queryResult = $queryBuilder->select(...$selectFields)
245  ->from($table)
246  ->where(
247  $queryBuilder->expr()->notIn(
248  'pid',
249  $queryBuilder->createNamedParameter($pageIdsForTable, Connection::PARAM_INT_ARRAY)
250  )
251  )
252  ->executeQuery();
253  $lostIdList = [];
254  while ($row = $queryResult->fetchAssociative()) {
255  $this->lRecords[$table][$row['uid']] = [
256  'uid' => $row['uid'],
257  'pid' => $row['pid'],
258  'title' => strip_tags(BackendUtility::getRecordTitle($table, $row)),
259  ];
260  $lostIdList[] = $row['uid'];
261  }
262  if ($table === 'pages') {
263  $this->lostPagesList = implode(',', $lostIdList);
264  }
265  }
266  }
267  }
268 
277  public function ‪fixLostRecord($table, $uid): bool
278  {
279  if ($table && ‪$GLOBALS['TCA'][$table] && $uid && is_array($this->lRecords[$table][$uid]) && ‪$GLOBALS['BE_USER']->isAdmin()) {
280  $updateFields = [
281  'pid' => 0,
282  ];
283  // If possible a lost record restored is hidden as default
284  if (‪$GLOBALS['TCA'][$table]['ctrl']['enablecolumns']['disabled']) {
285  $updateFields[‪$GLOBALS['TCA'][$table]['ctrl']['enablecolumns']['disabled']] = 1;
286  }
287  GeneralUtility::makeInstance(ConnectionPool::class)
288  ->getConnectionForTable($table)
289  ->update($table, $updateFields, ['uid' => (int)$uid]);
290  return true;
291  }
292  return false;
293  }
294 
301  public function ‪countRecords($pid_list): array
302  {
303  $list = [];
304  $list_n = [];
305  $pageIds = ‪GeneralUtility::intExplode(',', $pid_list);
306  if (!empty($pageIds)) {
307  foreach (‪$GLOBALS['TCA'] as $table => $tableConf) {
308  $pageIdsForTable = $pageIds;
309  // Remove preceding "-1," for non-versioned tables
310  if (!BackendUtility::isTableWorkspaceEnabled($table)) {
311  $pageIdsForTable = array_combine($pageIdsForTable, $pageIdsForTable);
312  unset($pageIdsForTable[-1]);
313  }
314  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
315  $queryBuilder->getRestrictions()->removeAll();
316  $count = $queryBuilder->count('uid')
317  ->from($table)
318  ->where(
319  $queryBuilder->expr()->in(
320  'pid',
321  $queryBuilder->createNamedParameter($pageIds, Connection::PARAM_INT_ARRAY)
322  )
323  )
324  ->executeQuery()
325  ->fetchOne();
326  if ($count) {
327  $list[$table] = $count;
328  }
329 
330  // same query excluding all deleted records
331  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
332  $queryBuilder->getRestrictions()
333  ->removeAll()
334  ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
335  $count = $queryBuilder->count('uid')
336  ->from($table)
337  ->where(
338  $queryBuilder->expr()->in(
339  'pid',
340  $queryBuilder->createNamedParameter($pageIdsForTable, Connection::PARAM_INT_ARRAY)
341  )
342  )
343  ->executeQuery()
344  ->fetchOne();
345  if ($count) {
346  $list_n[$table] = $count;
347  }
348  }
349  }
350  return ['all' => $list, 'non_deleted' => $list_n];
351  }
352 
358  public function ‪getGroupFields(): array
359  {
360  $result = [];
361  foreach (‪$GLOBALS['TCA'] as $table => $tableConf) {
362  $cols = ‪$GLOBALS['TCA'][$table]['columns'];
363  foreach ($cols as $field => $config) {
364  $fieldType = $config['config']['type'] ?? '';
365  if ($fieldType === 'group' && ($config['config']['internal_type'] ?? '') !== 'folder') {
366  $result[$table][] = $field;
367  }
368  if (($fieldType === 'select' || $fieldType === 'category')
369  && ($config['config']['foreign_table'] ?? false)
370  ) {
371  $result[$table][] = $field;
372  }
373  }
374  }
375  return $result;
376  }
377 
383  public function ‪selectNonEmptyRecordsWithFkeys(): void
384  {
385  $fkey_arrays = $this->‪getGroupFields();
386  $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
387  foreach ($fkey_arrays as $table => ‪$fields) {
388  $connection = $connectionPool->getConnectionForTable($table);
389  $schemaManager = $connection->createSchemaManager();
390  $tableColumns = $schemaManager->listTableColumns($table);
391 
392  $queryBuilder = $connectionPool->getQueryBuilderForTable($table);
393  $queryBuilder->getRestrictions()->removeAll();
394 
395  $queryBuilder->select('*')
396  ->from($table);
397  $whereClause = [];
398 
399  foreach (‪$fields as $fieldName) {
400  // The array index of $tableColumns is the lowercased column name!
401  // It is quoted for keywords
402  $column = $tableColumns[strtolower($fieldName)]
403  ?? $tableColumns[$connection->quoteIdentifier(strtolower($fieldName))];
404  if (!$column) {
405  // Throw meaningful exception if field does not exist in DB - 'none' is not filtered here since the
406  // method is only called with type=group fields
407  throw new \RuntimeException(
408  'Field ' . $fieldName . ' for table ' . $table . ' has been defined in TCA, but does not exist in DB',
409  1536248937
410  );
411  }
412  $fieldType = $column->getType()->getName();
413  if (in_array(
414  $fieldType,
415  [Types::BIGINT, Types::INTEGER, Types::SMALLINT, Types::DECIMAL, Types::FLOAT],
416  true
417  )) {
418  $whereClause[] = $queryBuilder->expr()->andX(
419  $queryBuilder->expr()->isNotNull($fieldName),
420  $queryBuilder->expr()->neq(
421  $fieldName,
422  $queryBuilder->createNamedParameter(0, ‪Connection::PARAM_INT)
423  )
424  );
425  } elseif (in_array($fieldType, [Types::STRING, Types::TEXT], true)) {
426  $whereClause[] = $queryBuilder->expr()->andX(
427  $queryBuilder->expr()->isNotNull($fieldName),
428  $queryBuilder->expr()->neq(
429  $fieldName,
430  $queryBuilder->createNamedParameter('', ‪Connection::PARAM_STR)
431  )
432  );
433  } elseif ($fieldType === Types::BLOB) {
434  $whereClause[] = $queryBuilder->expr()->andX(
435  $queryBuilder->expr()->isNotNull($fieldName),
436  $queryBuilder->expr()
437  ->comparison(
438  $queryBuilder->expr()->length($fieldName),
440  $queryBuilder->createNamedParameter(0, ‪Connection::PARAM_INT)
441  )
442  );
443  }
444  }
445  $queryResult = $queryBuilder->orWhere(...$whereClause)->executeQuery();
446 
447  while ($row = $queryResult->fetchAssociative()) {
448  foreach (‪$fields as $field) {
449  if (trim($row[$field] ?? '')) {
450  $fieldConf = ‪$GLOBALS['TCA'][$table]['columns'][$field]['config'];
451  if ($fieldConf['type'] === 'group' && !empty($fieldConf['allowed'])) {
452  $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
453  $dbAnalysis->start(
454  $row[$field],
455  $fieldConf['allowed'],
456  $fieldConf['MM'] ?? null,
457  $row['uid'],
458  $table,
459  $fieldConf
460  );
461  foreach ($dbAnalysis->itemArray as $tempArr) {
462  if (!isset($this->checkGroupDBRefs[$tempArr['table']][$tempArr['id']])) {
463  $this->checkGroupDBRefs[$tempArr['table']][$tempArr['id']] = 0;
464  }
465  $this->checkGroupDBRefs[$tempArr['table']][$tempArr['id']] += 1;
466  }
467  }
468  if (($fieldConf['foreign_table'] ?? false)
469  && ($fieldConf['type'] === 'select' || $fieldConf['type'] === 'category')
470  ) {
471  $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
472  $dbAnalysis->start(
473  $row[$field],
474  $fieldConf['foreign_table'],
475  $fieldConf['MM'] ?? null,
476  $row['uid'],
477  $table,
478  $fieldConf
479  );
480  foreach ($dbAnalysis->itemArray as $tempArr) {
481  if ($tempArr['id'] > 0) {
482  if (!isset($this->checkSelectDBRefs[$fieldConf['foreign_table']][$tempArr['id']])) {
483  $this->checkSelectDBRefs[$fieldConf['foreign_table']][$tempArr['id']] = 0;
484  }
485  $this->checkSelectDBRefs[$fieldConf['foreign_table']][$tempArr['id']] += 1;
486  }
487  }
488  }
489  }
490  }
491  }
492  }
493  }
494 
501  public function ‪testDBRefs($theArray): string
502  {
503  $result = '';
504  foreach ($theArray as $table => $dbArr) {
505  if (‪$GLOBALS['TCA'][$table]) {
506  $ids = array_keys($dbArr);
507  if (!empty($ids)) {
508  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
509  ->getQueryBuilderForTable($table);
510  $queryBuilder->getRestrictions()
511  ->removeAll()
512  ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
513  $queryResult = $queryBuilder
514  ->select('uid')
515  ->from($table)
516  ->where(
517  $queryBuilder->expr()->in(
518  'uid',
519  $queryBuilder->createNamedParameter($ids, Connection::PARAM_INT_ARRAY)
520  )
521  )
522  ->executeQuery();
523  while ($row = $queryResult->fetchAssociative()) {
524  if (isset($dbArr[$row['uid']])) {
525  unset($dbArr[$row['uid']]);
526  } else {
527  $result .= 'Strange Error. ...<br />';
528  }
529  }
530  foreach ($dbArr as $theId => $theC) {
531  $result .= 'There are ' . $theC . ' records pointing to this missing or deleted record; [' . $table . '][' . $theId . ']<br />';
532  }
533  }
534  } else {
535  $result .= 'Codeerror. Table is not a table...<br />';
536  }
537  }
538  return $result;
539  }
540 
544  public function ‪getPageIdArray(): array
545  {
546  return ‪$this->pageIdArray;
547  }
548 
552  public function ‪getCheckGroupDBRefs(): array
553  {
555  }
556 
560  public function ‪getCheckSelectDBRefs(): array
561  {
563  }
564 
568  public function ‪getRecStats(): array
569  {
570  return ‪$this->recStats;
571  }
572 
576  public function ‪getLRecords(): array
577  {
578  return ‪$this->lRecords;
579  }
580 
584  public function ‪getLostPagesList(): string
585  {
587  }
588 }
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getCheckGroupDBRefs
‪array getCheckGroupDBRefs()
Definition: DatabaseIntegrityCheck.php:544
‪TYPO3\CMS\Lowlevel\Integrity
Definition: DatabaseIntegrityCheck.php:16
‪TYPO3\CMS\Core\Database\Connection\PARAM_INT
‪const PARAM_INT
Definition: Connection.php:49
‪TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
Definition: ExpressionBuilder.php:36
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$pageTranslatedPageIDArray
‪$pageTranslatedPageIDArray
Definition: DatabaseIntegrityCheck.php:63
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getPageTranslatedPageIDArray
‪array getPageTranslatedPageIDArray()
Definition: DatabaseIntegrityCheck.php:96
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\genTree
‪genTree($theID, $versions=false)
Definition: DatabaseIntegrityCheck.php:108
‪TYPO3\CMS\Core\Database\RelationHandler
Definition: RelationHandler.php:37
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getLostPagesList
‪string getLostPagesList()
Definition: DatabaseIntegrityCheck.php:576
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getGroupFields
‪array getGroupFields()
Definition: DatabaseIntegrityCheck.php:350
‪$fields
‪$fields
Definition: pages.php:5
‪TYPO3\CMS\Core\Database\Connection\PARAM_STR
‪const PARAM_STR
Definition: Connection.php:54
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$lRecords
‪array $lRecords
Definition: DatabaseIntegrityCheck.php:87
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getRecStats
‪array getRecStats()
Definition: DatabaseIntegrityCheck.php:560
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$genTreeIncludeRecords
‪bool $genTreeIncludeRecords
Definition: DatabaseIntegrityCheck.php:53
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$checkGroupDBRefs
‪array $checkGroupDBRefs
Definition: DatabaseIntegrityCheck.php:75
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$pageIdArray
‪$pageIdArray
Definition: DatabaseIntegrityCheck.php:58
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\countRecords
‪array countRecords($pid_list)
Definition: DatabaseIntegrityCheck.php:293
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\fixLostRecord
‪bool fixLostRecord($table, $uid)
Definition: DatabaseIntegrityCheck.php:269
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$genTreeIncludeDeleted
‪bool $genTreeIncludeDeleted
Definition: DatabaseIntegrityCheck.php:44
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\lostRecords
‪lostRecords($pid_list)
Definition: DatabaseIntegrityCheck.php:218
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\genTree_records
‪genTree_records($theID, $table, $versions=false)
Definition: DatabaseIntegrityCheck.php:175
‪TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder\GT
‪const GT
Definition: ExpressionBuilder.php:41
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$checkSelectDBRefs
‪array $checkSelectDBRefs
Definition: DatabaseIntegrityCheck.php:71
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\selectNonEmptyRecordsWithFkeys
‪selectNonEmptyRecordsWithFkeys()
Definition: DatabaseIntegrityCheck.php:375
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\testDBRefs
‪string testDBRefs($theArray)
Definition: DatabaseIntegrityCheck.php:493
‪TYPO3\CMS\Core\Database\Connection
Definition: Connection.php:38
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getCheckSelectDBRefs
‪array getCheckSelectDBRefs()
Definition: DatabaseIntegrityCheck.php:552
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck
Definition: DatabaseIntegrityCheck.php:41
‪$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\Lowlevel\Integrity\DatabaseIntegrityCheck\$recStats
‪array $recStats
Definition: DatabaseIntegrityCheck.php:79
‪TYPO3\CMS\Core\Utility\GeneralUtility\intExplode
‪static int[] intExplode($delimiter, $string, $removeEmptyValues=false, $limit=0)
Definition: GeneralUtility.php:927
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getLRecords
‪array getLRecords()
Definition: DatabaseIntegrityCheck.php:568
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getPageIdArray
‪array getPageIdArray()
Definition: DatabaseIntegrityCheck.php:536
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$lostPagesList
‪string $lostPagesList
Definition: DatabaseIntegrityCheck.php:91
‪TYPO3\CMS\Core\Database\ConnectionPool
Definition: ConnectionPool.php:46
‪TYPO3\CMS\Core\Utility\GeneralUtility
Definition: GeneralUtility.php:50
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$genTreeIncludeVersions
‪$genTreeIncludeVersions
Definition: DatabaseIntegrityCheck.php:49
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$recIdArray
‪array $recIdArray
Definition: DatabaseIntegrityCheck.php:67