[TASK] Improve EM performance when getting extension list from TER 96/27496/6
authorJost Baron <jost@netzkoenig.de>
Sun, 9 Feb 2014 03:22:15 +0000 (04:22 +0100)
committerWouter Wolters <typo3@wouterwolters.nl>
Tue, 11 Feb 2014 22:27:45 +0000 (23:27 +0100)
When importing the extension list from TER, each version of each
extension is one database record. For each extension key the EM
calculates the newest version and sets the column 'current_version'
to 1 for the corresponsing extension record.

The old implementation issued one database query for each extension
key, resulting in about 6k queries, taking a long time to execute
(several minutes on my machine). After applying this patch the same
thing is done in three queries, speeding up the process.

Resolves: #55820
Related: #52949
Releases: 6.2
Change-Id: I55d3699a63b13e7c07af4babbf57d0d06f367027
Reviewed-on: https://review.typo3.org/27496
Reviewed-by: Markus Klein
Tested-by: Markus Klein
Reviewed-by: Jost Baron
Tested-by: Jost Baron
Reviewed-by: Oliver Klee
Reviewed-by: Wouter Wolters
Tested-by: Wouter Wolters
typo3/sysext/extensionmanager/Classes/Domain/Repository/ExtensionRepository.php
typo3/sysext/extensionmanager/Classes/Utility/Importer/ExtensionListUtility.php

index 2c9874d..554ca72 100644 (file)
@@ -29,6 +29,10 @@ namespace TYPO3\CMS\Extensionmanager\Domain\Repository;
  * @author Susanne Moog <typo3@susannemoog.de>
  */
 class ExtensionRepository extends \TYPO3\CMS\Extbase\Persistence\Repository {
+       /**
+        * @var string
+        */
+       const TABLE_NAME = 'tx_extensionmanager_domain_model_extension';
 
        /**
         * @var \TYPO3\CMS\Core\Database\DatabaseConnection
@@ -238,43 +242,69 @@ class ExtensionRepository extends \TYPO3\CMS\Extbase\Persistence\Repository {
        }
 
        /**
-        * Update the current_version field after update
-        * For performance reason "native" TYPO3_DB is
-        * used here directly.
+        * Updates the current_version field after update.
         *
-        * @param integer $repositoryUid
-        * @return integer
+        * @param int $repositoryUid
+        * @return int
         */
        public function insertLastVersion($repositoryUid = 1) {
-               $repositoryUid = (int)$repositoryUid;
-               $groupedRows = $this->databaseConnection->exec_SELECTgetRows(
-                       'extension_key, max(integer_version) as maxintversion',
-                       'tx_extensionmanager_domain_model_extension',
-                       'repository=' . $repositoryUid,
+               $this->markExtensionWithMaximumVersionAsCurrent($repositoryUid);
+
+               return $this->getNumberOfCurrentExtensions();
+       }
+
+       /**
+        * Sets current_version = 1 for all extensions where the extension version is maximal.
+        *
+        * For performance reasons, the "native" TYPO3_DB is used here directly.
+        *
+        * @param int $repositoryUid
+        * @return void
+        */
+       protected function markExtensionWithMaximumVersionAsCurrent($repositoryUid) {
+               $whereClauseParts = array();
+               foreach ($this->fetchMaximalVersionsForAllExtensions($repositoryUid) as $row) {
+                       $whereClauseParts[] = '(extension_key = ' . $this->databaseConnection->fullQuoteStr($row['extension_key'], self::TABLE_NAME) .
+                               ' AND integer_version = ' . (int)$row['max_version'] . ')';
+               }
+
+               $whereClause = implode(' OR ', $whereClauseParts);
+
+               $this->databaseConnection->exec_UPDATEquery(
+                       self::TABLE_NAME,
+                       $whereClause,
+                       array(
+                               'current_version' => 1,
+                       )
+               );
+       }
+
+       /**
+        * Fetches the maximal version for all extensions.
+        *
+        * @param int $repositoryUid
+        * @return array
+        */
+       protected function fetchMaximalVersionsForAllExtensions($repositoryUid) {
+               return $this->databaseConnection->exec_SELECTgetRows(
+                       'extension_key, max(integer_version) AS max_version',
+                       self::TABLE_NAME,
+                       'repository = ' . (int)$repositoryUid,
                        'extension_key'
                );
-               $extensions = count($groupedRows);
+       }
 
-               if ($extensions > 0) {
-                       // set all to 0
-                       $this->databaseConnection->exec_UPDATEquery(
-                               'tx_extensionmanager_domain_model_extension',
-                               'current_version=1 AND repository=' . $repositoryUid,
-                               array('current_version' => 0)
-                       );
-                       // Find latest version of extensions and set current_version to 1 for these
-                       foreach ($groupedRows as $row) {
-                               $this->databaseConnection->exec_UPDATEquery(
-                                       'tx_extensionmanager_domain_model_extension',
-                                       'extension_key=' .
-                                                       $this->databaseConnection->fullQuoteStr($row['extension_key'], 'tx_extensionmanager_domain_model_extension') .
-                                                       ' AND integer_version=' . (int)$row['maxintversion'] .
-                                                       ' AND repository=' . $repositoryUid,
-                                       array('current_version' => 1)
-                               );
-                       }
-               }
-               return $extensions;
+       /**
+        * Returns the number of extensions that are current.
+        *
+        * @return int
+        */
+       protected function getNumberOfCurrentExtensions() {
+               return $this->databaseConnection->exec_SELECTcountRows(
+                       '*',
+                       self::TABLE_NAME,
+                       'current_version = 1'
+               );
        }
 
        /**
@@ -299,5 +329,4 @@ class ExtensionRepository extends \TYPO3\CMS\Extbase\Persistence\Repository {
                }
                return $query;
        }
-
 }
index 9131786..ffc57c1 100644 (file)
@@ -69,6 +69,7 @@ class ExtensionListUtility implements \SplObserver {
                'extension_key',
                'version',
                'integer_version',
+               'current_version',
                'alldownloadcounter',
                'downloadcounter',
                'title',
@@ -92,7 +93,7 @@ class ExtensionListUtility implements \SplObserver {
         *
         * @var array
         */
-       static protected $fieldIndicesNoQuote = array(2, 3, 4, 10, 12, 13, 14, 15);
+       static protected $fieldIndicesNoQuote = array(2, 3, 5, 11, 13, 14, 15, 16);
 
        /**
         * Keeps repository UID.
@@ -181,6 +182,8 @@ class ExtensionListUtility implements \SplObserver {
                        $subject->getExtkey(),
                        $subject->getVersion(),
                        $versionRepresentations['version_int'],
+                       // initialize current_version, correct value computed later:
+                       0,
                        (int)$subject->getAlldownloadcounter(),
                        (int)$subject->getDownloadcounter(),
                        !is_null($subject->getTitle()) ? $subject->getTitle() : '',