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