[TASK] Reduce SQL queries of page tree in workspaces 28/22528/7
authorNicole Cordes <typo3@cordes.co>
Wed, 15 Jul 2015 19:22:00 +0000 (21:22 +0200)
committerNicole Cordes <typo3@cordes.co>
Thu, 16 Jul 2015 10:02:25 +0000 (12:02 +0200)
Nodes of the page tree are highlighted, if that particular node
has versioned elements in the current workspace. However, this is
determined by iterating over all tables and all records for each
page. If having installed many extensions with many tables and
many records, this will produce a lot of SQL queries.

This patch introduces a new cache for record versions per page which is
fetched once from the database. This cache is located within the
\TYPO3\CMS\Workspaces\Service\WorkspaceService and can be shared between
several other classes as this is implements a SingletonInterface.

Resolves: #50349
Resolves: #66231
Related: #61184
Releases: master, 6.2
Change-Id: Ibaef07bdab87147c6826af8578870c52803dfe03
Reviewed-on: http://review.typo3.org/22528
Reviewed-by: Georg Ringer <georg.ringer@gmail.com>
Tested-by: Georg Ringer <georg.ringer@gmail.com>
Reviewed-by: Nicole Cordes <typo3@cordes.co>
Tested-by: Nicole Cordes <typo3@cordes.co>
typo3/sysext/backend/Classes/Utility/BackendUtility.php
typo3/sysext/core/Documentation/Changelog/master/Deprecation-50349-ReduceSQLQueriesOfPageTreeInWorkspaces.rst [new file with mode: 0644]
typo3/sysext/workspaces/Classes/ExtDirect/PagetreeCollectionsProcessor.php
typo3/sysext/workspaces/Classes/Service/WorkspaceService.php

index d319811..c90dec8 100644 (file)
@@ -4249,8 +4249,10 @@ class BackendUtility {
         * @param int $workspace Workspace ID
         * @param int $pageId Page ID
         * @return array Overview of records
+        * @deprecated since TYPO3 CMS 7. Will be removed with TYPO3 CMS 8. Please use \TYPO3\CMS\Workspaces\Service\WorkspaceService::hasPageRecordVersions to check for record versions.
         */
        static public function countVersionsOfRecordsOnPage($workspace, $pageId) {
+               GeneralUtility::logDeprecatedFunction();
                if ((int)$workspace === 0) {
                        return array();
                }
diff --git a/typo3/sysext/core/Documentation/Changelog/master/Deprecation-50349-ReduceSQLQueriesOfPageTreeInWorkspaces.rst b/typo3/sysext/core/Documentation/Changelog/master/Deprecation-50349-ReduceSQLQueriesOfPageTreeInWorkspaces.rst
new file mode 100644 (file)
index 0000000..9da4251
--- /dev/null
@@ -0,0 +1,26 @@
+===================================================================
+Deprecation: #50349 - Reduce SQL queries of page tree in workspaces
+===================================================================
+
+Description
+===========
+
+The performance of the calculcation of versions of a record has been improved. Therefore the method ``\TYPO3\CMS\Backend\Utility::countVersionsOfRecordsOnPage()`` has been marked as deprecated and is beeing replaced with ``\TYPO3\CMS\Workspaces\Service\WorkspaceService::hasPageRecordVersions()``.
+
+
+Impact
+======
+
+All calls to the PHP method will throw a deprecation warning.
+
+
+Affected Installations
+======================
+
+Instances which make use of ``\TYPO3\CMS\Backend\Utility::countVersionsOfRecordsOnPage()``
+
+
+Migration
+=========
+
+Use ``\TYPO3\CMS\Workspaces\Service\WorkspaceService::hasPageRecordVersions()`` instead.
\ No newline at end of file
index e0f9f40..3fde7dd 100644 (file)
@@ -14,12 +14,20 @@ namespace TYPO3\CMS\Workspaces\ExtDirect;
  * The TYPO3 project - inspiring people to share!
  */
 
+use TYPO3\CMS\Core\Utility\GeneralUtility;
+use TYPO3\CMS\Workspaces\Service\WorkspaceService;
+
 /**
  * Interface for classes which perform pre or post processing
  */
 class PagetreeCollectionsProcessor implements \TYPO3\CMS\Backend\Tree\Pagetree\CollectionProcessorInterface {
 
        /**
+        * @var WorkspaceService
+        */
+       protected $workspaceService = NULL;
+
+       /**
         * @abstract
         * @param \TYPO3\CMS\Backend\Tree\Pagetree\PagetreeNode $node
         * @param int $mountPoint
@@ -70,9 +78,20 @@ class PagetreeCollectionsProcessor implements \TYPO3\CMS\Backend\Tree\Pagetree\C
         * @return void
         */
        protected function highlightVersionizedElements(\TYPO3\CMS\Backend\Tree\TreeNode $node) {
-               if (!$node->getCls() && !empty(\TYPO3\CMS\Backend\Utility\BackendUtility::countVersionsOfRecordsOnPage($GLOBALS['BE_USER']->workspace, $node->getId(), TRUE))) {
+               if (!$node->getCls() && $this->getWorkspaceService()->hasPageRecordVersions($GLOBALS['BE_USER']->workspace, $node->getId())) {
                        $node->setCls('ver-versions');
                }
        }
 
+       /**
+        * @return WorkspaceService
+        */
+       protected function getWorkspaceService() {
+               if ($this->workspaceService === NULL) {
+                       $this->workspaceService = GeneralUtility::makeInstance(WorkspaceService::class);
+               }
+
+               return $this->workspaceService;
+       }
+
 }
index 78a7711..b386672 100644 (file)
@@ -15,19 +15,26 @@ namespace TYPO3\CMS\Workspaces\Service;
  */
 
 use TYPO3\CMS\Backend\Utility\BackendUtility;
+use TYPO3\CMS\Core\Database\DatabaseConnection;
+use TYPO3\CMS\Core\SingletonInterface;
 use TYPO3\CMS\Core\Utility\GeneralUtility;
 use TYPO3\CMS\Core\Versioning\VersionState;
 
 /**
  * Workspace service
  */
-class WorkspaceService implements \TYPO3\CMS\Core\SingletonInterface {
+class WorkspaceService implements SingletonInterface {
 
        /**
         * @var array
         */
        protected $pageCache = array();
 
+       /**
+        * @var array
+        */
+       protected $versionsOnPageCache = array();
+
        const TABLE_WORKSPACE = 'sys_workspace';
        const SELECT_ALL_WORKSPACES = -98;
        const LIVE_WORKSPACE_ID = 0;
@@ -290,7 +297,7 @@ class WorkspaceService implements \TYPO3\CMS\Core\SingletonInterface {
                // Select all records from this table in the database from the workspace
                // This joins the online version with the offline version as tables A and B
                // Order by UID, mostly to have a sorting in the backend overview module which doesn't "jump around" when swapping.
-               $res = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows($fields, $from, $where, '', 'B.uid');
+               $res = $this->getDatabaseConnection()->exec_SELECTgetRows($fields, $from, $where, '', 'B.uid');
                return is_array($res) ? $res : array();
        }
 
@@ -337,7 +344,7 @@ class WorkspaceService implements \TYPO3\CMS\Core\SingletonInterface {
                $where .= BackendUtility::deleteClause($table, 'A');
                $where .= BackendUtility::deleteClause($table, 'B');
                $where .= BackendUtility::deleteClause($table, 'C');
-               $res = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows($fields, $from, $where, '', 'A.uid');
+               $res = $this->getDatabaseConnection()->exec_SELECTgetRows($fields, $from, $where, '', 'A.uid');
                return is_array($res) ? $res : array();
        }
 
@@ -390,7 +397,7 @@ class WorkspaceService implements \TYPO3\CMS\Core\SingletonInterface {
                        } while ($changed);
                        $pageList = implode(',', $newList);
                        // In case moving pages is enabled we need to replace all move-to pointer with their origin
-                       $pages = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows('uid, t3ver_move_id', 'pages', 'uid IN (' . $pageList . ')' . BackendUtility::deleteClause('pages'), '', 'uid', '', 'uid');
+                       $pages = $this->getDatabaseConnection()->exec_SELECTgetRows('uid, t3ver_move_id', 'pages', 'uid IN (' . $pageList . ')' . BackendUtility::deleteClause('pages'), '', 'uid', '', 'uid');
                        $newList = array();
                        $pageIds = GeneralUtility::intExplode(',', $pageList, TRUE);
                        if (!in_array($pageId, $pageIds)) {
@@ -654,6 +661,67 @@ class WorkspaceService implements \TYPO3\CMS\Core\SingletonInterface {
        }
 
        /**
+        * Checks if a page has record versions according to a given workspace
+        *
+        * @param int $workspace
+        * @param int $pageId
+        * @return bool
+        */
+       public function hasPageRecordVersions($workspace, $pageId) {
+               $workspace = (int)$workspace;
+               $pageId = (int)$pageId;
+               if ($workspace === 0) {
+                       return FALSE;
+               }
+
+               if (isset($this->versionsOnPageCache[$pageId][$workspace])) {
+                       return $this->versionsOnPageCache[$pageId][$workspace];
+               }
+
+               if (!empty($this->versionsOnPageCache)) {
+                       return FALSE;
+               }
+
+               $this->versionsOnPageCache[$pageId][$workspace] = FALSE;
+               foreach ($GLOBALS['TCA'] as $tableName => $tableConfiguration) {
+                       if ($tableName === 'pages' || empty($tableConfiguration['ctrl']['versioningWS'])) {
+                               continue;
+                       }
+                       $joinStatement = 'A.t3ver_oid=B.uid';
+                       // Consider records that are moved to a different page
+                       if (BackendUtility::isTableMovePlaceholderAware($tableName)) {
+                               $movePointer = new VersionState(VersionState::MOVE_POINTER);
+                               $joinStatement = '(A.t3ver_oid=B.uid AND A.t3ver_state<>' . $movePointer
+                                       . ' OR A.t3ver_oid=B.t3ver_move_id AND A.t3ver_state=' . $movePointer . ')';
+                       }
+                       // Select all records from this table in the database from the workspace
+                       // This joins the online version with the offline version as tables A and B
+                       $records = $this->getDatabaseConnection()->exec_SELECTgetRows(
+                               'B.uid as live_uid, B.pid as live_pid, A.uid as offline_uid',
+                               $tableName . ' A,' . $tableName . ' B',
+                               'A.pid=-1 AND A.t3ver_wsid=' . $workspace . ' AND ' . $joinStatement .
+                               BackendUtility::deleteClause($tableName, 'A') . BackendUtility::deleteClause($tableName, 'B'),
+                               'live_pid'
+                       );
+
+                       if (!empty($records)) {
+                               foreach ($records as $record) {
+                                       $this->versionsOnPageCache[$record['live_pid']][$workspace] = TRUE;
+                               }
+                       }
+               }
+
+               return $this->versionsOnPageCache[$pageId][$workspace];
+       }
+
+       /**
+        * @return DatabaseConnection
+        */
+       protected function getDatabaseConnection() {
+               return $GLOBALS['TYPO3_DB'];
+       }
+
+       /**
         * @return \TYPO3\CMS\Extbase\Object\ObjectManager
         */
        protected function getObjectManager() {