[TASK] Doctrine: Migrate indexed_search part 2
[Packages/TYPO3.CMS.git] / typo3 / sysext / indexed_search / Classes / Domain / Repository / AdministrationRepository.php
index 78deac2..2258862 100644 (file)
@@ -20,11 +20,11 @@ use TYPO3\CMS\Core\Authentication\BackendUserAuthentication;
 use TYPO3\CMS\Core\Cache\CacheManager;
 use TYPO3\CMS\Core\Cache\Frontend\FrontendInterface;
 use TYPO3\CMS\Core\Database\ConnectionPool;
+use TYPO3\CMS\Core\Database\Query\QueryHelper;
 use TYPO3\CMS\Core\DataHandling\DataHandler;
 use TYPO3\CMS\Core\Imaging\Icon;
 use TYPO3\CMS\Core\Imaging\IconFactory;
 use TYPO3\CMS\Core\Utility\GeneralUtility;
-use TYPO3\CMS\Dbal\Database\DatabaseConnection;
 use TYPO3\CMS\IndexedSearch\FileContentParser;
 
 /**
@@ -58,16 +58,14 @@ class AdministrationRepository
     public function getGrlistRecord($phash)
     {
         $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('index_grlist');
-        $res = $queryBuilder
+        $result = $queryBuilder
             ->select('*')
             ->from('index_grlist')
-            ->where(
-                $queryBuilder->expr()->eq('phash', (int)$phash)
-            )
+            ->where($queryBuilder->expr()->eq('phash', (int)$phash))
             ->execute();
-        $numberOfRows = $res->rowCount();
+        $numberOfRows = $result->rowCount();
         $allRows = [];
-        while ($row = $res->fetch()) {
+        while ($row = $result->fetch()) {
             $row['pcount'] = $numberOfRows;
             $allRows[] = $row;
         }
@@ -117,34 +115,61 @@ class AdministrationRepository
     {
         $result = array();
 
-        $db = $this->getDatabaseConnection();
-        $res = $db->exec_SELECTquery(
-            'count(*) AS pcount,index_phash.*',
-            'index_phash',
-            'item_type<>\'0\'',
-            'phash_grouping,phash,cHashParams,data_filename,data_page_id,data_page_reg1,data_page_type,data_page_mp,gr_list,item_type,item_title,item_description,item_mtime,tstamp,item_size,contentHash,crdate,parsetime,sys_language_uid,item_crdate,externalUrl,recordUid,freeIndexUid,freeIndexSetId',
-            'item_type'
-        );
-        while ($row = $db->sql_fetch_assoc($res)) {
+        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('index_phash');
+        $res = $queryBuilder
+            ->select('index_phash.*')
+            ->addSelectLiteral($queryBuilder->expr()->count('*', 'pcount'))
+            ->from('index_phash')
+            ->where($queryBuilder->expr()->neq('item_type', 0))
+            ->groupBy(
+                'phash_grouping',
+                'phash',
+                'cHashParams',
+                'data_filename',
+                'data_page_id',
+                'data_page_reg1',
+                'data_page_type',
+                'data_page_mp',
+                'gr_list',
+                'item_type',
+                'item_title',
+                'item_description',
+                'item_mtime',
+                'tstamp',
+                'item_size',
+                'contentHash',
+                'crdate',
+                'parsetime',
+                'sys_language_uid',
+                'item_crdate',
+                'externalUrl',
+                'recordUid',
+                'freeIndexUid',
+                'freeIndexSetId'
+            )
+            ->orderBy('item_type')
+            ->execute();
+
+        while ($row = $res->fetch()) {
             $this->addAdditionalInformation($row);
 
             $result[] = $row;
 
             if ($row['pcount'] > 1) {
-                $res2 = $db->exec_SELECTquery(
-                    'index_phash.*',
-                    'index_phash',
-                    'phash_grouping=' . (int)$row['phash_grouping'] . ' AND phash<>' . (int)$row['phash']
-                );
-                while ($row2 = $db->sql_fetch_assoc($res2)) {
+                $res2 = $queryBuilder
+                    ->select('*')
+                    ->from('index_phash')
+                    ->where(
+                        $queryBuilder->expr()->eq('phash_grouping', (int)$row['phash_grouping']),
+                        $queryBuilder->expr()->neq('phash', (int)$row['phash'])
+                    )
+                    ->execute();
+                while ($row2 = $res2->fetch()) {
                     $this->addAdditionalInformation($row2);
                     $result[] = $row2;
                 }
-                $db->sql_free_result($res2);
             }
         }
-        $db->sql_free_result($res);
-
         return $result;
     }
 
@@ -192,19 +217,25 @@ class AdministrationRepository
         );
         $revTypes = array_flip($types);
         $revTypes[0] = 'TYPO3 page';
-        $db = $this->getDatabaseConnection();
-        $res = $db->exec_SELECTquery('count(*),item_type', 'index_phash', '', 'item_type', 'item_type');
-        while ($row = $db->sql_fetch_row($res)) {
-            $itemType = $row[1];
+
+        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('index_phash');
+        $res = $queryBuilder
+            ->select('item_type')
+            ->addSelectLiteral($queryBuilder->expr()->count('*', 'count'))
+            ->from('index_phash')
+            ->groupBy('item_type')
+            ->orderBy('item_type')
+            ->execute();
+
+        while ($row = $res->fetch()) {
+            $itemType = $row['item_type'];
             $counts[] = array(
-                'count' => $row[0],
+                'count' => $row['count'],
                 'name' => $revTypes[$itemType],
                 'type' => $itemType,
                 'uniqueCount' => $this->countUniqueTypes($itemType),
             );
         }
-        $db->sql_free_result($res);
-
         return $counts;
     }
 
@@ -216,17 +247,15 @@ class AdministrationRepository
      */
     protected function countUniqueTypes($itemType)
     {
-        $items = array();
         $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('index_phash');
-        $res = $queryBuilder
+        $items = $queryBuilder
             ->count('*')
             ->from('index_phash')
             ->where($queryBuilder->expr()->eq('item_type', $queryBuilder->createNamedParameter($itemType)))
             ->groupBy('phash_grouping')
-            ->execute();
-        while ($row = $res->fetch()) {
-            $items[] = $row;
-        }
+            ->execute()
+            ->fetchAll();
+
         return count($items);
     }
 
@@ -239,7 +268,7 @@ class AdministrationRepository
     public function getNumberOfSections($pageHash)
     {
         $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('index_section');
-        return $queryBuilder
+        return (int)$queryBuilder
             ->count('phash')
             ->from('index_section')
             ->where($queryBuilder->expr()->eq('phash', (int)$pageHash))
@@ -255,33 +284,60 @@ class AdministrationRepository
     public function getPageStatistic()
     {
         $result = array();
-        $db = $this->getDatabaseConnection();
-        $res = $db->exec_SELECTquery(
-            'count(*) AS pcount,index_phash.*',
-            'index_phash',
-            'data_page_id<>0',
-            'phash_grouping,phash,cHashParams,data_filename,data_page_id,data_page_reg1,data_page_type,data_page_mp,gr_list,item_type,item_title,item_description,item_mtime,tstamp,item_size,contentHash,crdate,parsetime,sys_language_uid,item_crdate,externalUrl,recordUid,freeIndexUid,freeIndexSetId',
-            'data_page_id'
-        );
-        while ($row = $db->sql_fetch_assoc($res)) {
+        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('index_phash');
+        $res = $queryBuilder
+            ->select('index_phash.*')
+            ->addSelectLiteral($queryBuilder->expr()->count('*', 'pcount'))
+            ->from('index_phash')
+            ->where($queryBuilder->expr()->neq('data_page_id', 0))
+            ->groupBy(
+                'phash_grouping',
+                'phash',
+                'cHashParams',
+                'data_filename',
+                'data_page_id',
+                'data_page_reg1',
+                'data_page_type',
+                'data_page_mp',
+                'gr_list',
+                'item_type',
+                'item_title',
+                'item_description',
+                'item_mtime',
+                'tstamp',
+                'item_size',
+                'contentHash',
+                'crdate',
+                'parsetime',
+                'sys_language_uid',
+                'item_crdate',
+                'externalUrl',
+                'recordUid',
+                'freeIndexUid',
+                'freeIndexSetId'
+            )
+            ->orderBy('data_page_id')
+            ->execute();
+
+        while ($row = $res->fetch()) {
             $this->addAdditionalInformation($row);
             $result[] = $row;
 
             if ($row['pcount'] > 1) {
-                $res2 = $db->exec_SELECTquery(
-                    'index_phash.*',
-                    'index_phash',
-                    'phash_grouping=' . (int)$row['phash_grouping'] . ' AND phash<>' . (int)$row['phash']
-                );
-                while ($row2 = $db->sql_fetch_assoc($res2)) {
+                $res2 = $queryBuilder
+                    ->select('*')
+                    ->from('index_phash')
+                    ->where(
+                        $queryBuilder->expr()->eq('phash_grouping', (int)$row['phash_grouping']),
+                        $queryBuilder->expr()->neq('phash', (int)$row['phash'])
+                    )
+                    ->execute();
+                while ($row2 = $res2->fetch()) {
                     $this->addAdditionalInformation($row2);
                     $result[] = $row2;
                 }
-                $db->sql_free_result($res2);
             }
         }
-        $db->sql_free_result($res);
-
         return $result;
     }
 
@@ -295,46 +351,34 @@ class AdministrationRepository
      */
     public function getGeneralSearchStatistic($additionalWhere, $pageUid, $max = 50)
     {
-        $queryParts = array(
-            'SELECT' => 'word, COUNT(*) AS c',
-            'FROM' => 'index_stat_word',
-            'WHERE' => sprintf('pageid= %d ' . $additionalWhere, $pageUid),
-            'GROUPBY' => 'word',
-            'ORDERBY' => '',
-            'LIMIT' => (int)$max
-        );
-        $db = $this->getDatabaseConnection();
-        $res = $db->exec_SELECTquery(
-            $queryParts['SELECT'],
-            $queryParts['FROM'],
-            $queryParts['WHERE'],
-            $queryParts['GROUPBY'],
-            $queryParts['ORDERBY'],
-            $queryParts['LIMIT']
-        );
-
-        $count = 0;
-        if ($res) {
-            $count = $db->sql_num_rows($res);
+        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
+            ->getQueryBuilderForTable('index_stat_word');
+        $queryBuilder
+            ->select('word')
+            ->from('index_stat_word')
+            ->addSelectLiteral($queryBuilder->expr()->count('*', 'c'))
+            ->where($queryBuilder->expr()->eq('pageid', (int)$pageUid))
+            ->groupBy('word')
+            ->setMaxResults((int)$max);
+
+        if (!empty($additionalWhere)) {
+            $queryBuilder->andWhere(QueryHelper::stripLogicalOperatorPrefix($additionalWhere));
         }
 
-        $db->sql_free_result($res);
+        $result = $queryBuilder->execute();
+        $count = (int)$result->rowCount();
+        $result->closeCursor();
 
         // exist several statistics for this page?
-        if ($count == 0) {
+        if ($count === 0) {
             // Limit access to pages of the current site
-            $secureAddWhere = ' AND pageid IN (' . $this->extGetTreeList((int)$pageUid, 100, 0, '1=1') . ') ';
-            $queryParts['WHERE'] = '1=1 ' . $additionalWhere . $secureAddWhere;
+            $queryBuilder->where(
+                $queryBuilder->expr()->in('pageid', $this->extGetTreeList((int)$pageUid, 100, 0, '1=1')),
+                QueryHelper::stripLogicalOperatorPrefix($additionalWhere)
+            );
         }
 
-        return $db->exec_SELECTgetRows(
-            $queryParts['SELECT'],
-            $queryParts['FROM'],
-            $queryParts['WHERE'],
-            $queryParts['GROUPBY'],
-            $queryParts['ORDERBY'],
-            $queryParts['LIMIT']
-        );
+        return $queryBuilder->execute()->fetchAll();
     }
 
     /**
@@ -384,52 +428,138 @@ class AdministrationRepository
         if ($depth > 0) {
             $tree->getTree((int)$pageId, $depth, '');
         }
-        $db = $this->getDatabaseConnection();
+
         foreach ($tree->tree as $singleLine) {
-            $res = $db->exec_SELECTquery(
-                'ISEC.phash_t3, ISEC.rl0, ISEC.rl1, ISEC.rl2, ISEC.page_id, ISEC.uniqid, ' .
-                'IP.phash, IP.phash_grouping, IP.cHashParams, IP.data_filename, IP.data_page_id, ' .
-                'IP.data_page_reg1, IP.data_page_type, IP.data_page_mp, IP.gr_list, IP.item_type, ' .
-                'IP.item_title, IP.item_description, IP.item_mtime, IP.tstamp, IP.item_size, ' .
-                'IP.contentHash, IP.crdate, IP.parsetime, IP.sys_language_uid, IP.item_crdate, ' .
-                'IP.externalUrl, IP.recordUid, IP.freeIndexUid, IP.freeIndexSetId, count(*) AS count_val',
-                'index_phash IP, index_section ISEC',
-                'IP.phash = ISEC.phash AND ISEC.page_id = ' . (int)$singleLine['row']['uid'],
-                'IP.phash,IP.phash_grouping,IP.cHashParams,IP.data_filename,IP.data_page_id,IP.data_page_reg1,IP.data_page_type,IP.data_page_mp,IP.gr_list,IP.item_type,IP.item_title,IP.item_description,IP.item_mtime,IP.tstamp,IP.item_size,IP.contentHash,IP.crdate,IP.parsetime,IP.sys_language_uid,IP.item_crdate,ISEC.phash,ISEC.phash_t3,ISEC.rl0,ISEC.rl1,ISEC.rl2,ISEC.page_id,ISEC.uniqid,IP.externalUrl,IP.recordUid,IP.freeIndexUid,IP.freeIndexSetId',
-                'IP.item_type, IP.tstamp',
-                10 + 1
-            );
-            $lines = array();
+            $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('index_phash');
+            $result = $queryBuilder->select(
+                'ISEC.phash_t3',
+                'ISEC.rl0',
+                'ISEC.rl1',
+                'ISEC.rl2',
+                'ISEC.page_id',
+                'ISEC.uniqid',
+                'IP.phash',
+                'IP.phash_grouping',
+                'IP.cHashParams',
+                'IP.data_filename',
+                'IP.data_page_id',
+                'IP.data_page_reg1',
+                'IP.data_page_type',
+                'IP.data_page_mp',
+                'IP.gr_list',
+                'IP.item_type',
+                'IP.item_title',
+                'IP.item_description',
+                'IP.item_mtime',
+                'IP.tstamp',
+                'IP.item_size',
+                'IP.contentHash',
+                'IP.crdate',
+                'IP.parsetime',
+                'IP.sys_language_uid',
+                'IP.item_crdate',
+                'IP.externalUrl',
+                'IP.recordUid',
+                'IP.freeIndexUid',
+                'IP.freeIndexSetId'
+            )
+            ->addSelectLiteral($queryBuilder->expr()->count('*', 'count_val'))
+            ->from('index_phash', 'IP')
+            ->from('index_section', 'ISEC')
+            ->where(
+                $queryBuilder->expr()->eq('IP.phash', $queryBuilder->quoteIdentifier('ISEC.phash')),
+                $queryBuilder->expr()->eq('ISEC.page_id', (int)$singleLine['row']['uid'])
+            )
+            ->groupBy(
+                'IP.phash',
+                'IP.phash_grouping',
+                'IP.cHashParams',
+                'IP.data_filename',
+                'IP.data_page_id',
+                'IP.data_page_reg1',
+                'IP.data_page_type',
+                'IP.data_page_mp',
+                'IP.gr_list',
+                'IP.item_type',
+                'IP.item_title',
+                'IP.item_description',
+                'IP.item_mtime',
+                'IP.tstamp',
+                'IP.item_size',
+                'IP.contentHash',
+                'IP.crdate',
+                'IP.parsetime',
+                'IP.sys_language_uid',
+                'IP.item_crdate',
+                'ISEC.phash',
+                'ISEC.phash_t3',
+                'ISEC.rl0',
+                'ISEC.rl1',
+                'ISEC.rl2',
+                'ISEC.page_id',
+                'ISEC.uniqid',
+                'IP.externalUrl',
+                'IP.recordUid',
+                'IP.freeIndexUid',
+                'IP.freeIndexSetId'
+            )
+            ->orderBy('IP.item_type')
+            ->addOrderBy('IP.tstamp')
+            ->setMaxResults(11)
+            ->execute();
+
+            $lines = [];
             // Collecting phash values (to remove local indexing for)
             // Traverse the result set of phash rows selected:
-            while ($row = $db->sql_fetch_assoc($res)) {
+            while ($row = $result->fetch()) {
+                $row['icon'] = $this->makeItemTypeIcon($row['item_type']);
                 $this->allPhashListed[] = $row['phash'];
+
                 // Adds a display row:
-                $row['icon'] = $this->makeItemTypeIcon($row['item_type']);
-                $row['wordCount'] = count($db->exec_SELECTgetRows(
-                    'index_words.baseword, index_rel.*',
-                    'index_rel, index_words',
-                    'index_rel.phash = ' . (int)$row['phash'] . ' AND index_words.wid = index_rel.wid',
-                    '',
-                    '',
-                    '',
-                    'baseword'
-                ));
+                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
+                    ->getQueryBuilderForTable('index_rel');
+                $wordCountResult = $queryBuilder->count('index_words.baseword')
+                    ->from('index_rel')
+                    ->from('index_words')
+                    ->where(
+                        $queryBuilder->expr()->eq('index_rel.phash', (int)$row['phash']),
+                        $queryBuilder->expr()->eq('index_words.wid', $queryBuilder->quoteIdentifier('index_rel.wid'))
+                    )
+                    ->groupBy('index_words.baseword')
+                    ->execute();
+
+                $row['wordCount'] = $wordCountResult->rowCount();
+                $wordCountResult->closeCursor();
 
                 if ($mode === 'content') {
-                    $row['fulltextData'] = $db->exec_SELECTgetSingleRow(
-                        '*',
-                        'index_fulltext',
-                        'phash = ' . $row['phash']);
-                    $wordRecords = $db->exec_SELECTgetRows(
-                        'index_words.baseword, index_rel.*',
-                        'index_rel, index_words',
-                        'index_rel.phash = ' . (int)$row['phash'] . ' AND index_words.wid = index_rel.wid',
-                        '', '', '', 'baseword');
+                    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
+                        ->getQueryBuilderForTable('index_fulltext');
+                    $row['fulltextData'] = $queryBuilder->select('*')
+                        ->from('index_fulltext')
+                        ->where($queryBuilder->expr()->eq('phash', (int)$row['phash']))
+                        ->setMaxResults(1)
+                        ->execute()
+                        ->fetch();
+
+                    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
+                        ->getQueryBuilderForTable('index_rel');
+                    $wordRecords = $queryBuilder->select('index_words.baseword')
+                        ->from('index_rel')
+                        ->from('index_words')
+                        ->where(
+                            $queryBuilder->expr()->eq('index_rel.phash', (int)$row['phash']),
+                            $queryBuilder->expr()->eq(
+                                'index_words.wid',
+                                $queryBuilder->quoteIdentifier('index_rel.wid')
+                            )
+                        )
+                        ->groupBy('index_words.baseword')
+                        ->orderBy('index_words.baseword')
+                        ->execute()
+                        ->fetchAll();
+
                     if (is_array($wordRecords)) {
-                        $indexed_words = array_keys($wordRecords);
-                        sort($indexed_words);
-                        $row['allWords'] = $indexed_words;
+                        $row['allWords'] = array_column($wordRecords, 'baseword');
                     }
                 }
 
@@ -455,7 +585,8 @@ class AdministrationRepository
      */
     protected function extGetTreeList($id, $depth, $begin = 0, $perms_clause)
     {
-        $list = GeneralUtility::makeInstance(FrontendBackendUserAuthentication::class)->extGetTreeList($id, $depth, $begin, $perms_clause);
+        $list = GeneralUtility::makeInstance(FrontendBackendUserAuthentication::class)
+            ->extGetTreeList($id, $depth, $begin, $perms_clause);
 
         if (empty($list)) {
             $list = $id;
@@ -488,7 +619,8 @@ class AdministrationRepository
             $phash = (int)$phash;
             if ($phash > 0) {
                 $idList = array();
-                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('index_section');
+                $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
+                    ->getQueryBuilderForTable('index_section');
                 $res = $queryBuilder
                     ->select('page_id')
                     ->from('index_section')
@@ -509,15 +641,18 @@ class AdministrationRepository
                 }
 
                 // Removing old registrations for all tables.
-                $tableArr = array('index_phash', 'index_rel', 'index_section', 'index_grlist', 'index_fulltext', 'index_debug');
+                $tableArr = [
+                    'index_phash',
+                    'index_rel',
+                    'index_section',
+                    'index_grlist',
+                    'index_fulltext',
+                    'index_debug'
+                ];
                 foreach ($tableArr as $table) {
-                    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
-                    $queryBuilder
-                        ->delete($table)
-                        ->where(
-                            $queryBuilder->expr()->eq('phash', (int)$phash)
-                        )
-                        ->execute();
+                    GeneralUtility::makeInstance(ConnectionPool::class)
+                        ->getConnectionForTable($table)
+                        ->delete($table, ['phash' => (int)$phash]);
                 }
             }
         }
@@ -592,14 +727,6 @@ class AdministrationRepository
         return $this->iconFileNameCache[$itemType];
     }
 
-    /**
-     * @return DatabaseConnection
-     */
-    protected function getDatabaseConnection()
-    {
-        return $GLOBALS['TYPO3_DB'];
-    }
-
     /**
      * @return BackendUserAuthentication
      */