[BUGFIX] Missing relations command: optimize SQL 86/47786/9
authorMarkus Klein <markus.klein@typo3.org>
Tue, 19 Apr 2016 13:36:26 +0000 (15:36 +0200)
committerMorton Jonuschat <m.jonuschat@mojocode.de>
Thu, 21 Apr 2016 09:27:46 +0000 (11:27 +0200)
Iterate over each result row instead of fetching
the whole result into PHP memory.

Resolves: #75783
Releases: master
Change-Id: Icd77e5bae1839b66bb156beeef17e7d4bb7535d7
Reviewed-on: https://review.typo3.org/47786
Reviewed-by: Nicole Cordes <typo3@cordes.co>
Tested-by: Nicole Cordes <typo3@cordes.co>
Reviewed-by: Christoph Kratz <ckr@rtp.ch>
Tested-by: Christoph Kratz <ckr@rtp.ch>
Reviewed-by: Wouter Wolters <typo3@wouterwolters.nl>
Tested-by: Philipp Gampe <philipp.gampe@typo3.org>
Reviewed-by: Stephan Großberndt <stephan@grossberndt.de>
Tested-by: Stephan Großberndt <stephan@grossberndt.de>
Reviewed-by: Morton Jonuschat <m.jonuschat@mojocode.de>
Tested-by: Morton Jonuschat <m.jonuschat@mojocode.de>
typo3/sysext/lowlevel/Classes/MissingRelationsCommand.php

index ffbb039..3bb4d46 100644 (file)
@@ -14,6 +14,12 @@ namespace TYPO3\CMS\Lowlevel;
  * The TYPO3 project - inspiring people to share!
  */
 
+use TYPO3\CMS\Backend\Utility\BackendUtility;
+use TYPO3\CMS\Core\Database\ConnectionPool;
+use TYPO3\CMS\Core\Database\Query\QueryBuilder;
+use TYPO3\CMS\Core\Database\ReferenceIndex;
+use TYPO3\CMS\Core\Utility\GeneralUtility;
+
 /**
  * Looking for missing relations.
  */
@@ -84,35 +90,47 @@ Reports missing relations';
             'deletedRecords_s' => array(),
             'nonExistingRecords_s' => array()
         );
+
         // Select DB relations from reference table
-        $recs = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows('*', 'sys_refindex', 'ref_table<>' . $GLOBALS['TYPO3_DB']->fullQuoteStr('_FILE', 'sys_refindex') . ' AND ref_uid>0' . $filterClause, '', 'sorting DESC');
-        // Traverse the records
+        /** @var QueryBuilder $queryBuilder */
+        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_refindex');
+        $rowIterator = $queryBuilder
+            ->select('ref_uid', 'ref_table', 'softref_key', 'hash', 'tablename', 'recuid', 'field', 'flexpointer', 'deleted')
+            ->from('sys_refindex')
+            ->where(
+                $queryBuilder->expr()->neq('ref_table', $queryBuilder->quote('_FILE'))
+            )
+            ->andWhere(
+                $queryBuilder->expr()->gt('ref_uid', 0)
+            )
+            ->orderBy('sorting', 'DESC')
+            ->execute();
+
         $tempExists = array();
-        if (is_array($recs)) {
-            foreach ($recs as $rec) {
-                $suffix = $rec['softref_key'] != '' ? '_s' : '_m';
-                $idx = $rec['ref_table'] . ':' . $rec['ref_uid'];
-                // Get referenced record:
-                if (!isset($tempExists[$idx])) {
-                    $tempExists[$idx] = \TYPO3\CMS\Backend\Utility\BackendUtility::getRecordRaw($rec['ref_table'], 'uid=' . (int)$rec['ref_uid'], 'uid,pid' . ($GLOBALS['TCA'][$rec['ref_table']]['ctrl']['delete'] ? ',' . $GLOBALS['TCA'][$rec['ref_table']]['ctrl']['delete'] : ''));
-                }
-                // Compile info string for location of reference:
-                $infoString = $this->infoStr($rec);
-                // Handle missing file:
-                if ($tempExists[$idx]['uid']) {
-                    if ($tempExists[$idx]['pid'] == -1) {
-                        $resultArray['offlineVersionRecords' . $suffix][$idx][$rec['hash']] = $infoString;
-                        ksort($resultArray['offlineVersionRecords' . $suffix][$idx]);
-                    } elseif ($GLOBALS['TCA'][$rec['ref_table']]['ctrl']['delete'] && $tempExists[$idx][$GLOBALS['TCA'][$rec['ref_table']]['ctrl']['delete']]) {
-                        $resultArray['deletedRecords' . $suffix][$idx][$rec['hash']] = $infoString;
-                        ksort($resultArray['deletedRecords' . $suffix][$idx]);
-                    }
-                } else {
-                    $resultArray['nonExistingRecords' . $suffix][$idx][$rec['hash']] = $infoString;
-                    ksort($resultArray['nonExistingRecords' . $suffix][$idx]);
+        while ($rec = $rowIterator->fetch()) {
+            $suffix = $rec['softref_key'] != '' ? '_s' : '_m';
+            $idx = $rec['ref_table'] . ':' . $rec['ref_uid'];
+            // Get referenced record:
+            if (!isset($tempExists[$idx])) {
+                $tempExists[$idx] = BackendUtility::getRecordRaw($rec['ref_table'], 'uid=' . (int)$rec['ref_uid'], 'uid,pid' . ($GLOBALS['TCA'][$rec['ref_table']]['ctrl']['delete'] ? ',' . $GLOBALS['TCA'][$rec['ref_table']]['ctrl']['delete'] : ''));
+            }
+            // Compile info string for location of reference:
+            $infoString = $this->infoStr($rec);
+            // Handle missing file:
+            if ($tempExists[$idx]['uid']) {
+                if ($tempExists[$idx]['pid'] == -1) {
+                    $resultArray['offlineVersionRecords' . $suffix][$idx][$rec['hash']] = $infoString;
+                    ksort($resultArray['offlineVersionRecords' . $suffix][$idx]);
+                } elseif ($GLOBALS['TCA'][$rec['ref_table']]['ctrl']['delete'] && $tempExists[$idx][$GLOBALS['TCA'][$rec['ref_table']]['ctrl']['delete']]) {
+                    $resultArray['deletedRecords' . $suffix][$idx][$rec['hash']] = $infoString;
+                    ksort($resultArray['deletedRecords' . $suffix][$idx]);
                 }
+            } else {
+                $resultArray['nonExistingRecords' . $suffix][$idx][$rec['hash']] = $infoString;
+                ksort($resultArray['nonExistingRecords' . $suffix][$idx]);
             }
         }
+
         ksort($resultArray['offlineVersionRecords_m']);
         ksort($resultArray['deletedRecords_m']);
         ksort($resultArray['nonExistingRecords_m']);
@@ -140,7 +158,7 @@ Reports missing relations';
                     if ($bypass = $this->cli_noExecutionCheck($recReference)) {
                         echo $bypass;
                     } else {
-                        $sysRefObj = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance(\TYPO3\CMS\Core\Database\ReferenceIndex::class);
+                        $sysRefObj = GeneralUtility::makeInstance(ReferenceIndex::class);
                         $error = $sysRefObj->setReferenceValue($hash, null);
                         if ($error) {
                             echo '             TYPO3\\CMS\\Core\\Database\\ReferenceIndex::setReferenceValue(): ' . $error . LF;