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