890fd7c641a77b4554af40d3940328813b09f532
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Integrity / DatabaseIntegrityCheck.php
1 <?php
2 namespace TYPO3\CMS\Core\Integrity;
3
4 /*
5 * This file is part of the TYPO3 CMS project.
6 *
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
10 *
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
13 *
14 * The TYPO3 project - inspiring people to share!
15 */
16
17 use Doctrine\DBAL\Types\Type;
18 use TYPO3\CMS\Backend\Utility\BackendUtility;
19 use TYPO3\CMS\Core\Database\Connection;
20 use TYPO3\CMS\Core\Database\ConnectionPool;
21 use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
22 use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
23 use TYPO3\CMS\Core\Database\RelationHandler;
24 use TYPO3\CMS\Core\Utility\GeneralUtility;
25
26 /**
27 * This class holds functions used by the TYPO3 backend to check the integrity of the database (The DBint module, 'lowlevel' extension)
28 *
29 * Depends on \TYPO3\CMS\Core\Database\RelationHandler
30 *
31 * @todo Need to really extend this class when the DataHandler library has been updated and the whole API is better defined. There are some known bugs in this library. Further it would be nice with a facility to not only analyze but also clean up!
32 * @see \TYPO3\CMS\Lowlevel\View\DatabaseIntegrityView::func_relations(), \TYPO3\CMS\Lowlevel\View\DatabaseIntegrityView::func_records()
33 */
34 class DatabaseIntegrityCheck
35 {
36 /**
37 * @var bool If set, genTree() includes deleted pages. This is default.
38 */
39 public $genTree_includeDeleted = true;
40
41 /**
42 * @var bool If set, genTree() includes versionized pages/records. This is default.
43 */
44 public $genTree_includeVersions = true;
45
46 /**
47 * @var bool If set, genTree() includes records from pages.
48 */
49 public $genTree_includeRecords = false;
50
51 /**
52 * @var array Will hold id/rec pairs from genTree()
53 */
54 public $page_idArray = [];
55
56 /**
57 * @var array
58 */
59 public $rec_idArray = [];
60
61 /**
62 * @var array
63 */
64 public $checkFileRefs = [];
65
66 /**
67 * @var array From the select-fields
68 */
69 public $checkSelectDBRefs = [];
70
71 /**
72 * @var array From the group-fields
73 */
74 public $checkGroupDBRefs = [];
75
76 /**
77 * @var array Statistics
78 */
79 public $recStats = [
80 'allValid' => [],
81 'published_versions' => [],
82 'deleted' => []
83 ];
84
85 /**
86 * @var array
87 */
88 public $lRecords = [];
89
90 /**
91 * @var string
92 */
93 public $lostPagesList = '';
94
95 /**
96 * Generates a list of Page-uid's that corresponds to the tables in the tree.
97 * This list should ideally include all records in the pages-table.
98 *
99 * @param int $theID a pid (page-record id) from which to start making the tree
100 * @param string $depthData HTML-code (image-tags) used when this function calls itself recursively.
101 * @param bool $versions Internal variable, don't set from outside!
102 */
103 public function genTree($theID, $depthData = '', $versions = false)
104 {
105 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
106 $queryBuilder->getRestrictions()->removeAll();
107 if (!$this->genTree_includeDeleted) {
108 $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
109 }
110 $queryBuilder->select('uid', 'title', 'doktype', 'deleted', 'hidden')
111 ->from('pages')
112 ->orderBy('sorting');
113 if ($versions) {
114 $queryBuilder->addSelect('t3ver_wsid', 't3ver_id', 't3ver_count');
115 $queryBuilder->where(
116 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter(-1, \PDO::PARAM_INT)),
117 $queryBuilder->expr()->eq('t3ver_oid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
118 );
119 } else {
120 $queryBuilder->where(
121 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
122 );
123 }
124 $result = $queryBuilder->execute();
125 // Traverse the records selected
126 while ($row = $result->fetch()) {
127 $newID = $row['uid'];
128 // Register various data for this item:
129 $this->page_idArray[$newID] = $row;
130 $this->recStats['all_valid']['pages'][$newID] = $newID;
131 if ($row['deleted']) {
132 $this->recStats['deleted']['pages'][$newID] = $newID;
133 }
134 if ($versions && $row['t3ver_count'] >= 1) {
135 $this->recStats['published_versions']['pages'][$newID] = $newID;
136 }
137 if ($row['deleted']) {
138 $this->recStats['deleted']++;
139 }
140 if ($row['hidden']) {
141 $this->recStats['hidden']++;
142 }
143 $this->recStats['doktype'][$row['doktype']]++;
144 // If all records should be shown, do so:
145 if ($this->genTree_includeRecords) {
146 foreach ($GLOBALS['TCA'] as $tableName => $cfg) {
147 if ($tableName !== 'pages') {
148 $this->genTree_records($newID, '', $tableName);
149 }
150 }
151 }
152 // Add sub pages:
153 $this->genTree($newID);
154 // If versions are included in the tree, add those now:
155 if ($this->genTree_includeVersions) {
156 $this->genTree($newID, '', true);
157 }
158 }
159 }
160
161 /**
162 * @param int $theID a pid (page-record id) from which to start making the tree
163 * @param string $_ Unused parameter
164 * @param string $table Table to get the records from
165 * @param bool $versions Internal variable, don't set from outside!
166 */
167 public function genTree_records($theID, $_ = '', $table = '', $versions = false)
168 {
169 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
170 $queryBuilder->getRestrictions()->removeAll();
171 if (!$this->genTree_includeDeleted) {
172 $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
173 }
174 $queryBuilder
175 ->select(...explode(',', BackendUtility::getCommonSelectFields($table)))
176 ->from($table);
177
178 // Select all records from table pointing to this page
179 if ($versions) {
180 $queryBuilder->where(
181 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter(-1, \PDO::PARAM_INT)),
182 $queryBuilder->expr()->eq('t3ver_oid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
183 );
184 } else {
185 $queryBuilder->where(
186 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($theID, \PDO::PARAM_INT))
187 );
188 }
189 $queryResult = $queryBuilder->execute();
190 // Traverse selected
191 while ($row = $queryResult->fetch()) {
192 $newID = $row['uid'];
193 // Register various data for this item:
194 $this->rec_idArray[$table][$newID] = $row;
195 $this->recStats['all_valid'][$table][$newID] = $newID;
196 if ($row['deleted']) {
197 $this->recStats['deleted'][$table][$newID] = $newID;
198 }
199 if ($versions && $row['t3ver_count'] >= 1 && $row['t3ver_wsid'] == 0) {
200 $this->recStats['published_versions'][$table][$newID] = $newID;
201 }
202 // Select all versions of this record:
203 if ($this->genTree_includeVersions && $GLOBALS['TCA'][$table]['ctrl']['versioningWS']) {
204 $this->genTree_records($newID, '', $table, true);
205 }
206 }
207 }
208
209 /**
210 * Fills $this->lRecords with the records from all tc-tables that are not attached to a PID in the pid-list.
211 *
212 * @param string $pid_list list of pid's (page-record uid's). This list is probably made by genTree()
213 */
214 public function lostRecords($pid_list)
215 {
216 $this->lostPagesList = '';
217 $pageIds = GeneralUtility::intExplode(',', $pid_list);
218 if (is_array($pageIds)) {
219 foreach ($GLOBALS['TCA'] as $table => $tableConf) {
220 $pageIdsForTable = $pageIds;
221 // Remove preceding "-1," for non-versioned tables
222 if (!BackendUtility::isTableWorkspaceEnabled($table)) {
223 $pageIdsForTable = array_combine($pageIdsForTable, $pageIdsForTable);
224 unset($pageIdsForTable[-1]);
225 }
226 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
227 $queryBuilder->getRestrictions()->removeAll();
228 $selectFields = ['uid', 'pid'];
229 if (!empty($GLOBALS['TCA'][$table]['ctrl']['label'])) {
230 $selectFields[] = $GLOBALS['TCA'][$table]['ctrl']['label'];
231 }
232 $queryResult = $queryBuilder->select(...$selectFields)
233 ->from($table)
234 ->where(
235 $queryBuilder->expr()->notIn(
236 'pid',
237 $queryBuilder->createNamedParameter($pageIdsForTable, Connection::PARAM_INT_ARRAY)
238 )
239 )
240 ->execute();
241 $lostIdList = [];
242 while ($row = $queryResult->fetch()) {
243 $this->lRecords[$table][$row['uid']] = [
244 'uid' => $row['uid'],
245 'pid' => $row['pid'],
246 'title' => strip_tags(BackendUtility::getRecordTitle($table, $row))
247 ];
248 $lostIdList[] = $row['uid'];
249 }
250 if ($table === 'pages') {
251 $this->lostPagesList = implode(',', $lostIdList);
252 }
253 }
254 }
255 }
256
257 /**
258 * Fixes lost record from $table with uid $uid by setting the PID to zero.
259 * If there is a disabled column for the record that will be set as well.
260 *
261 * @param string $table Database tablename
262 * @param int $uid The uid of the record which will have the PID value set to 0 (zero)
263 * @return bool TRUE if done.
264 */
265 public function fixLostRecord($table, $uid)
266 {
267 if ($table && $GLOBALS['TCA'][$table] && $uid && is_array($this->lRecords[$table][$uid]) && $GLOBALS['BE_USER']->user['admin']) {
268 $updateFields = [
269 'pid' => 0
270 ];
271 // If possible a lost record restored is hidden as default
272 if ($GLOBALS['TCA'][$table]['ctrl']['enablecolumns']['disabled']) {
273 $updateFields[$GLOBALS['TCA'][$table]['ctrl']['enablecolumns']['disabled']] = 1;
274 }
275 GeneralUtility::makeInstance(ConnectionPool::class)
276 ->getConnectionForTable($table)
277 ->update($table, $updateFields, ['uid' => (int)$uid]);
278 return true;
279 } else {
280 return false;
281 }
282 }
283
284 /**
285 * Counts records from $GLOBALS['TCA']-tables that ARE attached to an existing page.
286 *
287 * @param string $pid_list list of pid's (page-record uid's). This list is probably made by genTree()
288 * @return array an array with the number of records from all $GLOBALS['TCA']-tables that are attached to a PID in the pid-list.
289 */
290 public function countRecords($pid_list)
291 {
292 $list = [];
293 $list_n = [];
294 $pageIds = GeneralUtility::intExplode(',', $pid_list);
295 if (!empty($pageIds)) {
296 foreach ($GLOBALS['TCA'] as $table => $tableConf) {
297 $pageIdsForTable = $pageIds;
298 // Remove preceding "-1," for non-versioned tables
299 if (!BackendUtility::isTableWorkspaceEnabled($table)) {
300 $pageIdsForTable = array_combine($pageIdsForTable, $pageIdsForTable);
301 unset($pageIdsForTable[-1]);
302 }
303 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
304 $queryBuilder->getRestrictions()->removeAll();
305 $count = $queryBuilder->count('uid')
306 ->from($table)
307 ->where(
308 $queryBuilder->expr()->in(
309 'pid',
310 $queryBuilder->createNamedParameter($pageIds, Connection::PARAM_INT_ARRAY)
311 )
312 )
313 ->execute()
314 ->fetchColumn(0);
315 if ($count) {
316 $list[$table] = $count;
317 }
318
319 // same query excluding all deleted records
320 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
321 $queryBuilder->getRestrictions()
322 ->removeAll()
323 ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
324 $count = $queryBuilder->count('uid')
325 ->from($table)
326 ->where(
327 $queryBuilder->expr()->in(
328 'pid',
329 $queryBuilder->createNamedParameter($pageIdsForTable, Connection::PARAM_INT_ARRAY)
330 )
331 )
332 ->execute()
333 ->fetchColumn(0);
334 if ($count) {
335 $list_n[$table] = $count;
336 }
337 }
338 }
339 return ['all' => $list, 'non_deleted' => $list_n];
340 }
341
342 /**
343 * Finding relations in database based on type 'group' (files or database-uid's in a list)
344 *
345 * @param string $mode $mode = file, $mode = db, $mode = '' (all...)
346 * @return array An array with all fields listed that somehow are references to other records (foreign-keys) or files
347 */
348 public function getGroupFields($mode)
349 {
350 $result = [];
351 foreach ($GLOBALS['TCA'] as $table => $tableConf) {
352 $cols = $GLOBALS['TCA'][$table]['columns'];
353 foreach ($cols as $field => $config) {
354 if ($config['config']['type'] === 'group') {
355 if ((!$mode || $mode === 'file') && $config['config']['internal_type'] === 'file' || (!$mode || $mode === 'db') && $config['config']['internal_type'] === 'db') {
356 $result[$table][] = $field;
357 }
358 }
359 if ((!$mode || $mode === 'db') && $config['config']['type'] === 'select' && $config['config']['foreign_table']) {
360 $result[$table][] = $field;
361 }
362 }
363 if ($result[$table]) {
364 $result[$table] = implode(',', $result[$table]);
365 }
366 }
367 return $result;
368 }
369
370 /**
371 * Finds all fields that hold filenames from uploadfolder
372 *
373 * @param string $uploadfolder Path to uploadfolder
374 * @return array An array with all fields listed that have references to files in the $uploadfolder
375 */
376 public function getFileFields($uploadfolder)
377 {
378 $result = [];
379 foreach ($GLOBALS['TCA'] as $table => $tableConf) {
380 $cols = $GLOBALS['TCA'][$table]['columns'];
381 foreach ($cols as $field => $config) {
382 if ($config['config']['type'] === 'group' && $config['config']['internal_type'] === 'file' && $config['config']['uploadfolder'] == $uploadfolder) {
383 $result[] = [$table, $field];
384 }
385 }
386 }
387 return $result;
388 }
389
390 /**
391 * Returns an array with arrays of table/field pairs which are allowed to hold references to the input table name - according to $GLOBALS['TCA']
392 *
393 * @param string $theSearchTable Table name
394 * @return array
395 */
396 public function getDBFields($theSearchTable)
397 {
398 $result = [];
399 foreach ($GLOBALS['TCA'] as $table => $tableConf) {
400 $cols = $GLOBALS['TCA'][$table]['columns'];
401 foreach ($cols as $field => $config) {
402 if ($config['config']['type'] === 'group' && $config['config']['internal_type'] === 'db') {
403 if (trim($config['config']['allowed']) === '*' || strstr($config['config']['allowed'], $theSearchTable)) {
404 $result[] = [$table, $field];
405 }
406 } elseif ($config['config']['type'] === 'select' && $config['config']['foreign_table'] == $theSearchTable) {
407 $result[] = [$table, $field];
408 }
409 }
410 }
411 return $result;
412 }
413
414 /**
415 * This selects non-empty-records from the tables/fields in the fkey_array generated by getGroupFields()
416 *
417 * @param array $fkey_arrays Array with tables/fields generated by getGroupFields()
418 * @see getGroupFields()
419 */
420 public function selectNonEmptyRecordsWithFkeys($fkey_arrays)
421 {
422 if (is_array($fkey_arrays)) {
423 $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
424 foreach ($fkey_arrays as $table => $field_list) {
425 if ($GLOBALS['TCA'][$table] && trim($field_list)) {
426 $connection = $connectionPool->getConnectionForTable($table);
427 $schemaManager = $connection->getSchemaManager();
428 $tableColumns = $schemaManager->listTableColumns($table);
429
430 $queryBuilder = $connectionPool->getQueryBuilderForTable($table);
431 $queryBuilder->getRestrictions()->removeAll();
432
433 $fields = GeneralUtility::trimExplode(',', $field_list, true);
434
435 $queryBuilder->select('uid')
436 ->from($table);
437 $whereClause = [];
438
439 foreach ($fields as $fieldName) {
440 // The array index of $tableColumns is the lowercased column name!
441 // It is quoted for keywords
442 $column = $tableColumns[strtolower($fieldName)]
443 ?? $tableColumns[$connection->quoteIdentifier(strtolower($fieldName))];
444 $fieldType = $column->getType()->getName();
445 if (in_array(
446 $fieldType,
447 [Type::BIGINT, Type::INTEGER, Type::SMALLINT, Type::DECIMAL, Type::FLOAT],
448 true
449 )) {
450 $whereClause[] = $queryBuilder->expr()->andX(
451 $queryBuilder->expr()->isNotNull($fieldName),
452 $queryBuilder->expr()->neq(
453 $fieldName,
454 $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)
455 )
456 );
457 } elseif (in_array($fieldType, [Type::STRING, Type::TEXT], true)) {
458 $whereClause[] = $queryBuilder->expr()->andX(
459 $queryBuilder->expr()->isNotNull($fieldName),
460 $queryBuilder->expr()->neq(
461 $fieldName,
462 $queryBuilder->createNamedParameter('', \PDO::PARAM_STR)
463 )
464 );
465 } elseif (in_array($fieldType, [Type::BLOB], true)) {
466 $whereClause[] = $queryBuilder->expr()->andX(
467 $queryBuilder->expr()->isNotNull($fieldName),
468 $queryBuilder->expr()
469 ->comparison(
470 $queryBuilder->expr()->length($fieldName),
471 ExpressionBuilder::GT,
472 $queryBuilder->createNamedParameter(0, \PDO::PARAM_INT)
473 )
474 );
475 }
476 }
477 $queryResult = $queryBuilder->orWhere(...$whereClause)->execute();
478
479 while ($row = $queryResult->fetch()) {
480 foreach ($fields as $field) {
481 if (trim($row[$field])) {
482 $fieldConf = $GLOBALS['TCA'][$table]['columns'][$field]['config'];
483 if ($fieldConf['type'] === 'group') {
484 if ($fieldConf['internal_type'] === 'file') {
485 // Files...
486 if ($fieldConf['MM']) {
487 $tempArr = [];
488 $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
489 $dbAnalysis->start('', 'files', $fieldConf['MM'], $row['uid']);
490 foreach ($dbAnalysis->itemArray as $somekey => $someval) {
491 if ($someval['id']) {
492 $tempArr[] = $someval['id'];
493 }
494 }
495 } else {
496 $tempArr = explode(',', trim($row[$field]));
497 }
498 foreach ($tempArr as $file) {
499 $file = trim($file);
500 if ($file) {
501 $this->checkFileRefs[$fieldConf['uploadfolder']][$file] += 1;
502 }
503 }
504 }
505 if ($fieldConf['internal_type'] === 'db') {
506 $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
507 $dbAnalysis->start(
508 $row[$field],
509 $fieldConf['allowed'],
510 $fieldConf['MM'],
511 $row['uid'],
512 $table,
513 $fieldConf
514 );
515 foreach ($dbAnalysis->itemArray as $tempArr) {
516 $this->checkGroupDBRefs[$tempArr['table']][$tempArr['id']] += 1;
517 }
518 }
519 }
520 if ($fieldConf['type'] === 'select' && $fieldConf['foreign_table']) {
521 $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
522 $dbAnalysis->start(
523 $row[$field],
524 $fieldConf['foreign_table'],
525 $fieldConf['MM'],
526 $row['uid'],
527 $table,
528 $fieldConf
529 );
530 foreach ($dbAnalysis->itemArray as $tempArr) {
531 if ($tempArr['id'] > 0) {
532 $this->checkGroupDBRefs[$fieldConf['foreign_table']][$tempArr['id']] += 1;
533 }
534 }
535 }
536 }
537 }
538 }
539 }
540 }
541 }
542 }
543
544 /**
545 * Depends on selectNonEmpty.... to be executed first!!
546 *
547 * @return array Report over files; keys are "moreReferences", "noReferences", "noFile", "error
548 */
549 public function testFileRefs()
550 {
551 $output = [];
552 // Handle direct references with upload folder setting (workaround)
553 $newCheckFileRefs = [];
554 foreach ($this->checkFileRefs as $folder => $files) {
555 // Only direct references without a folder setting
556 if ($folder !== '') {
557 $newCheckFileRefs[$folder] = $files;
558 continue;
559 }
560 foreach ($files as $file => $references) {
561 // Direct file references have often many references (removes occurrences in the moreReferences section of the result array)
562 if ($references > 1) {
563 $references = 1;
564 }
565 // The directory must be empty (prevents checking of the root directory)
566 $directory = dirname($file);
567 if ($directory !== '') {
568 $newCheckFileRefs[$directory][basename($file)] = $references;
569 }
570 }
571 }
572 $this->checkFileRefs = $newCheckFileRefs;
573 foreach ($this->checkFileRefs as $folder => $fileArr) {
574 $path = PATH_site . $folder;
575 if (@is_dir($path) && @is_readable($path)) {
576 $d = dir($path);
577 while ($entry = $d->read()) {
578 if (@is_file(($path . '/' . $entry))) {
579 if (isset($fileArr[$entry])) {
580 if ($fileArr[$entry] > 1) {
581 $temp = $this->whereIsFileReferenced($folder, $entry);
582 $tempList = '';
583 foreach ($temp as $inf) {
584 $tempList .= '[' . $inf['table'] . '][' . $inf['uid'] . '][' . $inf['field'] . '] (pid:' . $inf['pid'] . ') - ';
585 }
586 $output['moreReferences'][] = [$path, $entry, $fileArr[$entry], $tempList];
587 }
588 unset($fileArr[$entry]);
589 } else {
590 // Contains workaround for direct references
591 if (!strstr($entry, 'index.htm') && !preg_match(('/^' . preg_quote($GLOBALS['TYPO3_CONF_VARS']['BE']['fileadminDir'], '/') . '/'), $folder)) {
592 $output['noReferences'][] = [$path, $entry];
593 }
594 }
595 }
596 }
597 $d->close();
598 $tempCounter = 0;
599 foreach ($fileArr as $file => $value) {
600 // Workaround for direct file references
601 if (preg_match('/^' . preg_quote($GLOBALS['TYPO3_CONF_VARS']['BE']['fileadminDir'], '/') . '/', $folder)) {
602 $file = $folder . '/' . $file;
603 $folder = '';
604 $path = substr(PATH_site, 0, -1);
605 }
606 $temp = $this->whereIsFileReferenced($folder, $file);
607 $tempList = '';
608 foreach ($temp as $inf) {
609 $tempList .= '[' . $inf['table'] . '][' . $inf['uid'] . '][' . $inf['field'] . '] (pid:' . $inf['pid'] . ') - ';
610 }
611 $tempCounter++;
612 $output['noFile'][substr($path, -3) . '_' . substr($file, 0, 3) . '_' . $tempCounter] = [$path, $file, $tempList];
613 }
614 } else {
615 $output['error'][] = [$path];
616 }
617 }
618 return $output;
619 }
620
621 /**
622 * Depends on selectNonEmpty.... to be executed first!!
623 *
624 * @param array $theArray Table with key/value pairs being table names and arrays with uid numbers
625 * @return string HTML Error message
626 */
627 public function testDBRefs($theArray)
628 {
629 $result = '';
630 foreach ($theArray as $table => $dbArr) {
631 if ($GLOBALS['TCA'][$table]) {
632 $ids = array_keys($dbArr);
633 if (!empty($ids)) {
634 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
635 ->getQueryBuilderForTable($table);
636 $queryBuilder->getRestrictions()
637 ->removeAll()
638 ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
639 $queryResult = $queryBuilder
640 ->select('uid')
641 ->from($table)
642 ->where(
643 $queryBuilder->expr()->in(
644 'uid',
645 $queryBuilder->createNamedParameter($ids, Connection::PARAM_INT_ARRAY)
646 )
647 )
648 ->execute();
649 while ($row = $queryResult->fetch()) {
650 if (isset($dbArr[$row['uid']])) {
651 unset($dbArr[$row['uid']]);
652 } else {
653 $result .= 'Strange Error. ...<br />';
654 }
655 }
656 foreach ($dbArr as $theId => $theC) {
657 $result .= 'There are ' . $theC . ' records pointing to this missing or deleted record; [' . $table . '][' . $theId . ']<br />';
658 }
659 }
660 } else {
661 $result .= 'Codeerror. Table is not a table...<br />';
662 }
663 }
664 return $result;
665 }
666
667 /**
668 * Finding all references to record based on table/uid
669 *
670 * @param string $searchTable Table name
671 * @param int $id Uid of database record
672 * @return array Array with other arrays containing information about where references was found
673 */
674 public function whereIsRecordReferenced($searchTable, $id)
675 {
676 // Gets tables / Fields that reference to files
677 $fileFields = $this->getDBFields($searchTable);
678 $theRecordList = [];
679 foreach ($fileFields as $info) {
680 list($table, $field) = $info;
681 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
682 $queryBuilder->getRestrictions()->removeAll();
683 $queryResult = $queryBuilder
684 ->select('uid', 'pid', $GLOBALS['TCA'][$table]['ctrl']['label'], $field)
685 ->from($table)
686 ->where(
687 $queryBuilder->expr()->like(
688 $field,
689 $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards($id) . '%')
690 )
691 )
692 ->execute();
693
694 while ($row = $queryResult->fetch()) {
695 // Now this is the field, where the reference COULD come from.
696 // But we're not guaranteed, so we must carefully examine the data.
697 $fieldConf = $GLOBALS['TCA'][$table]['columns'][$field]['config'];
698 $allowedTables = $fieldConf['type'] === 'group' ? $fieldConf['allowed'] : $fieldConf['foreign_table'];
699 $dbAnalysis = GeneralUtility::makeInstance(RelationHandler::class);
700 $dbAnalysis->start($row[$field], $allowedTables, $fieldConf['MM'], $row['uid'], $table, $fieldConf);
701 foreach ($dbAnalysis->itemArray as $tempArr) {
702 if ($tempArr['table'] == $searchTable && $tempArr['id'] == $id) {
703 $theRecordList[] = [
704 'table' => $table,
705 'uid' => $row['uid'],
706 'field' => $field,
707 'pid' => $row['pid']
708 ];
709 }
710 }
711 }
712 }
713 return $theRecordList;
714 }
715
716 /**
717 * Finding all references to file based on uploadfolder / filename
718 *
719 * @param string $uploadFolder Upload folder where file is found
720 * @param string $filename Filename to search for
721 * @return array Array with other arrays containing information about where references was found
722 */
723 public function whereIsFileReferenced($uploadFolder, $filename)
724 {
725 // Gets tables / Fields that reference to files
726 $fileFields = $this->getFileFields($uploadFolder);
727 $theRecordList = [];
728 foreach ($fileFields as $info) {
729 list($table, $field) = $info;
730 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
731 $queryBuilder->getRestrictions()->removeAll();
732 $queryResult = $queryBuilder
733 ->select('uid', 'pid', $GLOBALS['TCA'][$table]['ctrl']['label'], $field)
734 ->from($table)
735 ->where(
736 $queryBuilder->expr()->like(
737 $field,
738 $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards($filename) . '%')
739 )
740 )
741 ->execute();
742 while ($row = $queryResult->fetch()) {
743 // Now this is the field, where the reference COULD come from.
744 // But we're not guaranteed, so we must carefully examine the data.
745 $tempArr = explode(',', trim($row[$field]));
746 foreach ($tempArr as $file) {
747 $file = trim($file);
748 if ($file == $filename) {
749 $theRecordList[] = [
750 'table' => $table,
751 'uid' => $row['uid'],
752 'field' => $field,
753 'pid' => $row['pid']
754 ];
755 }
756 }
757 }
758 }
759 return $theRecordList;
760 }
761 }