‪TYPO3CMS  10.4
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;
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', 't3ver_count');
128  $queryBuilder->where(
129  $queryBuilder->expr()->eq('t3ver_oid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
130  );
131  } else {
132  $queryBuilder->where(
133  $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
134  );
135  }
136  $result = $queryBuilder->execute();
137  // Traverse the records selected
138  while ($row = $result->fetch()) {
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  if ($versions && $row['t3ver_count'] >= 1) {
151  $this->recStats['published_versions']['pages'][$newID] = $newID;
152  }
153  if ($row['hidden']) {
154  $this->recStats['hidden']++;
155  }
156  $this->recStats['doktype'][$row['doktype']]++;
157  // If all records should be shown, do so:
158  if ($this->genTreeIncludeRecords) {
159  foreach (‪$GLOBALS['TCA'] as $tableName => $cfg) {
160  if ($tableName !== 'pages') {
161  $this->‪genTree_records($newID, $tableName);
162  }
163  }
164  }
165  // Add sub pages:
166  $this->‪genTree($newID);
167  // If versions are included in the tree, add those now:
168  if ($this->genTreeIncludeVersions) {
169  $this->‪genTree($newID, true);
170  }
171  }
172  }
173 
179  public function ‪genTree_records($theID, $table, $versions = false): void
180  {
181  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
182  $queryBuilder->getRestrictions()->removeAll();
183  if (!$this->genTreeIncludeDeleted) {
184  $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
185  }
186  $queryBuilder
187  ->select(...explode(',', ‪BackendUtility::getCommonSelectFields($table)))
188  ->from($table);
189 
190  // Select all records from table pointing to this page
191  if ($versions) {
192  $queryBuilder->where(
193  $queryBuilder->expr()->eq('t3ver_oid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
194  );
195  } else {
196  $queryBuilder->where(
197  $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
198  );
199  }
200  $queryResult = $queryBuilder->execute();
201  // Traverse selected
202  while ($row = $queryResult->fetch()) {
203  $newID = $row['uid'];
204  // Register various data for this item:
205  $this->recIdArray[$table][$newID] = $row;
206  $this->recStats['all_valid'][$table][$newID] = $newID;
207  if ($row['deleted']) {
208  $this->recStats['deleted'][$table][$newID] = $newID;
209  }
210  if ($versions && $row['t3ver_count'] >= 1 && $row['t3ver_wsid'] == 0) {
211  $this->recStats['published_versions'][$table][$newID] = $newID;
212  }
213  // Select all versions of this record:
214  if ($this->genTreeIncludeVersions && ‪BackendUtility::isTableWorkspaceEnabled($table)) {
215  $this->‪genTree_records($newID, $table, true);
216  }
217  }
218  }
219 
225  public function ‪lostRecords($pid_list): void
226  {
227  $this->lostPagesList = '';
228  $pageIds = ‪GeneralUtility::intExplode(',', $pid_list);
229  if (is_array($pageIds)) {
230  foreach (‪$GLOBALS['TCA'] as $table => $tableConf) {
231  $pageIdsForTable = $pageIds;
232  // Remove preceding "-1," for non-versioned tables
234  $pageIdsForTable = array_combine($pageIdsForTable, $pageIdsForTable);
235  unset($pageIdsForTable[-1]);
236  }
237  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
238  $queryBuilder->getRestrictions()->removeAll();
239  $selectFields = ['uid', 'pid'];
240  if (!empty(‪$GLOBALS['TCA'][$table]['ctrl']['label'])) {
241  $selectFields[] = ‪$GLOBALS['TCA'][$table]['ctrl']['label'];
242  }
243  $queryResult = $queryBuilder->select(...$selectFields)
244  ->from($table)
245  ->where(
246  $queryBuilder->expr()->notIn(
247  'pid',
248  $queryBuilder->createNamedParameter($pageIdsForTable, Connection::PARAM_INT_ARRAY)
249  )
250  )
251  ->execute();
252  $lostIdList = [];
253  while ($row = $queryResult->fetch()) {
254  $this->lRecords[$table][$row['uid']] = [
255  'uid' => $row['uid'],
256  'pid' => $row['pid'],
257  'title' => strip_tags(‪BackendUtility::getRecordTitle($table, $row))
258  ];
259  $lostIdList[] = $row['uid'];
260  }
261  if ($table === 'pages') {
262  $this->lostPagesList = implode(',', $lostIdList);
263  }
264  }
265  }
266  }
267 
276  public function ‪fixLostRecord($table, $uid): bool
277  {
278  if ($table && ‪$GLOBALS['TCA'][$table] && $uid && is_array($this->lRecords[$table][$uid]) && ‪$GLOBALS['BE_USER']->isAdmin()) {
279  $updateFields = [
280  'pid' => 0
281  ];
282  // If possible a lost record restored is hidden as default
283  if (‪$GLOBALS['TCA'][$table]['ctrl']['enablecolumns']['disabled']) {
284  $updateFields[‪$GLOBALS['TCA'][$table]['ctrl']['enablecolumns']['disabled']] = 1;
285  }
286  GeneralUtility::makeInstance(ConnectionPool::class)
287  ->getConnectionForTable($table)
288  ->update($table, $updateFields, ['uid' => (int)$uid]);
289  return true;
290  }
291  return false;
292  }
293 
300  public function ‪countRecords($pid_list): array
301  {
302  $list = [];
303  $list_n = [];
304  $pageIds = ‪GeneralUtility::intExplode(',', $pid_list);
305  if (!empty($pageIds)) {
306  foreach (‪$GLOBALS['TCA'] as $table => $tableConf) {
307  $pageIdsForTable = $pageIds;
308  // Remove preceding "-1," for non-versioned tables
310  $pageIdsForTable = array_combine($pageIdsForTable, $pageIdsForTable);
311  unset($pageIdsForTable[-1]);
312  }
313  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
314  $queryBuilder->getRestrictions()->removeAll();
315  $count = $queryBuilder->count('uid')
316  ->from($table)
317  ->where(
318  $queryBuilder->expr()->in(
319  'pid',
320  $queryBuilder->createNamedParameter($pageIds, Connection::PARAM_INT_ARRAY)
321  )
322  )
323  ->execute()
324  ->fetchColumn(0);
325  if ($count) {
326  $list[$table] = $count;
327  }
328 
329  // same query excluding all deleted records
330  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
331  $queryBuilder->getRestrictions()
332  ->removeAll()
333  ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
334  $count = $queryBuilder->count('uid')
335  ->from($table)
336  ->where(
337  $queryBuilder->expr()->in(
338  'pid',
339  $queryBuilder->createNamedParameter($pageIdsForTable, Connection::PARAM_INT_ARRAY)
340  )
341  )
342  ->execute()
343  ->fetchColumn(0);
344  if ($count) {
345  $list_n[$table] = $count;
346  }
347  }
348  }
349  return ['all' => $list, 'non_deleted' => $list_n];
350  }
351 
357  public function ‪getGroupFields(): array
358  {
359  $result = [];
360  foreach (‪$GLOBALS['TCA'] as $table => $tableConf) {
361  $cols = ‪$GLOBALS['TCA'][$table]['columns'];
362  foreach ($cols as $field => $config) {
363  if ($config['config']['type'] === 'group' && $config['config']['internal_type'] === 'db') {
364  $result[$table][] = $field;
365  }
366  if ($config['config']['type'] === 'select' && $config['config']['foreign_table']) {
367  $result[$table][] = $field;
368  }
369  }
370  }
371  return $result;
372  }
373 
379  public function ‪selectNonEmptyRecordsWithFkeys(): void
380  {
381  $fkey_arrays = $this->‪getGroupFields();
382  $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
383  foreach ($fkey_arrays as $table => ‪$fields) {
384  $connection = $connectionPool->getConnectionForTable($table);
385  $schemaManager = $connection->getSchemaManager();
386  $tableColumns = $schemaManager->listTableColumns($table);
387 
388  $queryBuilder = $connectionPool->getQueryBuilderForTable($table);
389  $queryBuilder->getRestrictions()->removeAll();
390 
391  $queryBuilder->select('uid')
392  ->from($table);
393  $whereClause = [];
394 
395  foreach (‪$fields as $fieldName) {
396  // The array index of $tableColumns is the lowercased column name!
397  // It is quoted for keywords
398  $column = $tableColumns[strtolower($fieldName)]
399  ?? $tableColumns[$connection->quoteIdentifier(strtolower($fieldName))];
400  if (!$column) {
401  // Throw meaningful exception if field does not exist in DB - 'none' is not filtered here since the
402  // method is only called with type=group fields
403  throw new \RuntimeException(
404  'Field ' . $fieldName . ' for table ' . $table . ' has been defined in TCA, but does not exist in DB',
405  1536248937
406  );
407  }
408  $fieldType = $column->getType()->getName();
409  if (in_array(
410  $fieldType,
411  [Types::BIGINT, Types::INTEGER, Types::SMALLINT, Types::DECIMAL, Types::FLOAT],
412  true
413  )) {
414  $whereClause[] = $queryBuilder->expr()->andX(
415  $queryBuilder->expr()->isNotNull($fieldName),
416  $queryBuilder->expr()->neq(
417  $fieldName,
418  $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)
419  )
420  );
421  } elseif (in_array($fieldType, [Types::STRING, Types::TEXT], true)) {
422  $whereClause[] = $queryBuilder->expr()->andX(
423  $queryBuilder->expr()->isNotNull($fieldName),
424  $queryBuilder->expr()->neq(
425  $fieldName,
426  $queryBuilder->createNamedParameter('', \PDO::PARAM_STR)
427  )
428  );
429  } elseif ($fieldType === Types::BLOB) {
430  $whereClause[] = $queryBuilder->expr()->andX(
431  $queryBuilder->expr()->isNotNull($fieldName),
432  $queryBuilder->expr()
433  ->comparison(
434  $queryBuilder->expr()->length($fieldName),
436  $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)
437  )
438  );
439  }
440  }
441  $queryResult = $queryBuilder->orWhere(...$whereClause)->execute();
442 
443  while ($row = $queryResult->fetch()) {
444  foreach (‪$fields as $field) {
445  if (trim($row[$field])) {
446  $fieldConf = ‪$GLOBALS['TCA'][$table]['columns'][$field]['config'];
447  if ($fieldConf['type'] === 'group' && $fieldConf['internal_type'] === 'db') {
448  $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
449  $dbAnalysis->start(
450  $row[$field],
451  $fieldConf['allowed'],
452  $fieldConf['MM'],
453  $row['uid'],
454  $table,
455  $fieldConf
456  );
457  foreach ($dbAnalysis->itemArray as $tempArr) {
458  $this->checkGroupDBRefs[$tempArr['table']][$tempArr['id']] += 1;
459  }
460  }
461  if ($fieldConf['type'] === 'select' && $fieldConf['foreign_table']) {
462  $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
463  $dbAnalysis->start(
464  $row[$field],
465  $fieldConf['foreign_table'],
466  $fieldConf['MM'],
467  $row['uid'],
468  $table,
469  $fieldConf
470  );
471  foreach ($dbAnalysis->itemArray as $tempArr) {
472  if ($tempArr['id'] > 0) {
473  $this->checkSelectDBRefs[$fieldConf['foreign_table']][$tempArr['id']] += 1;
474  }
475  }
476  }
477  }
478  }
479  }
480  }
481  }
482 
489  public function ‪testDBRefs($theArray): string
490  {
491  $result = '';
492  foreach ($theArray as $table => $dbArr) {
493  if (‪$GLOBALS['TCA'][$table]) {
494  $ids = array_keys($dbArr);
495  if (!empty($ids)) {
496  $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
497  ->getQueryBuilderForTable($table);
498  $queryBuilder->getRestrictions()
499  ->removeAll()
500  ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
501  $queryResult = $queryBuilder
502  ->select('uid')
503  ->from($table)
504  ->where(
505  $queryBuilder->expr()->in(
506  'uid',
507  $queryBuilder->createNamedParameter($ids, Connection::PARAM_INT_ARRAY)
508  )
509  )
510  ->execute();
511  while ($row = $queryResult->fetch()) {
512  if (isset($dbArr[$row['uid']])) {
513  unset($dbArr[$row['uid']]);
514  } else {
515  $result .= 'Strange Error. ...<br />';
516  }
517  }
518  foreach ($dbArr as $theId => $theC) {
519  $result .= 'There are ' . $theC . ' records pointing to this missing or deleted record; [' . $table . '][' . $theId . ']<br />';
520  }
521  }
522  } else {
523  $result .= 'Codeerror. Table is not a table...<br />';
524  }
525  }
526  return $result;
527  }
528 
532  public function ‪getPageIdArray(): array
533  {
534  return ‪$this->pageIdArray;
535  }
536 
540  public function ‪getCheckGroupDBRefs(): array
541  {
543  }
544 
548  public function ‪getCheckSelectDBRefs(): array
549  {
551  }
552 
556  public function ‪getRecStats(): array
557  {
558  return ‪$this->recStats;
559  }
560 
564  public function ‪getLRecords(): array
565  {
566  return ‪$this->lRecords;
567  }
568 
572  public function ‪getLostPagesList(): string
573  {
575  }
576 }
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getCheckGroupDBRefs
‪array getCheckGroupDBRefs()
Definition: DatabaseIntegrityCheck.php:532
‪TYPO3\CMS\Lowlevel\Integrity
Definition: DatabaseIntegrityCheck.php:16
‪TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder
Definition: ExpressionBuilder.php:35
‪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:35
‪TYPO3\CMS\Backend\Utility\BackendUtility\getCommonSelectFields
‪static string getCommonSelectFields($table, $prefix='', $fields=[])
Definition: BackendUtility.php:2136
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getLostPagesList
‪string getLostPagesList()
Definition: DatabaseIntegrityCheck.php:564
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getGroupFields
‪array getGroupFields()
Definition: DatabaseIntegrityCheck.php:349
‪$fields
‪$fields
Definition: pages.php:5
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$lRecords
‪array $lRecords
Definition: DatabaseIntegrityCheck.php:87
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getRecStats
‪array getRecStats()
Definition: DatabaseIntegrityCheck.php:548
‪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:292
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\fixLostRecord
‪bool fixLostRecord($table, $uid)
Definition: DatabaseIntegrityCheck.php:268
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$genTreeIncludeDeleted
‪bool $genTreeIncludeDeleted
Definition: DatabaseIntegrityCheck.php:44
‪TYPO3\CMS\Backend\Utility\BackendUtility\isTableWorkspaceEnabled
‪static bool isTableWorkspaceEnabled($table)
Definition: BackendUtility.php:4021
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\lostRecords
‪lostRecords($pid_list)
Definition: DatabaseIntegrityCheck.php:217
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\genTree_records
‪genTree_records($theID, $table, $versions=false)
Definition: DatabaseIntegrityCheck.php:171
‪TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder\GT
‪const GT
Definition: ExpressionBuilder.php:40
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$checkSelectDBRefs
‪array $checkSelectDBRefs
Definition: DatabaseIntegrityCheck.php:71
‪TYPO3\CMS\Backend\Utility\BackendUtility\getRecordTitle
‪static string getRecordTitle($table, $row, $prep=false, $forceResult=true)
Definition: BackendUtility.php:1541
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\selectNonEmptyRecordsWithFkeys
‪selectNonEmptyRecordsWithFkeys()
Definition: DatabaseIntegrityCheck.php:371
‪TYPO3\CMS\Backend\Utility\BackendUtility
Definition: BackendUtility.php:75
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\testDBRefs
‪string testDBRefs($theArray)
Definition: DatabaseIntegrityCheck.php:481
‪TYPO3\CMS\Core\Database\Connection
Definition: Connection.php:36
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getCheckSelectDBRefs
‪array getCheckSelectDBRefs()
Definition: DatabaseIntegrityCheck.php:540
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck
Definition: DatabaseIntegrityCheck.php:41
‪$GLOBALS
‪$GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['adminpanel']['modules']
Definition: ext_localconf.php:5
‪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:988
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getLRecords
‪array getLRecords()
Definition: DatabaseIntegrityCheck.php:556
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\getPageIdArray
‪array getPageIdArray()
Definition: DatabaseIntegrityCheck.php:524
‪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:46
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$genTreeIncludeVersions
‪$genTreeIncludeVersions
Definition: DatabaseIntegrityCheck.php:49
‪TYPO3\CMS\Lowlevel\Integrity\DatabaseIntegrityCheck\$recIdArray
‪array $recIdArray
Definition: DatabaseIntegrityCheck.php:67