[TASK] Reduce SQL queries of page tree in workspaces 25/41425/3
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:21:04 +0000 (12:21 +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/41425
Reviewed-by: Nicole Cordes <typo3@cordes.co>
Tested-by: Nicole Cordes <typo3@cordes.co>
typo3/sysext/backend/Classes/Tree/View/AbstractTreeView.php
typo3/sysext/workspaces/Classes/ExtDirect/PagetreeCollectionsProcessor.php
typo3/sysext/workspaces/Classes/Service/WorkspaceService.php

index d61bd31..02f686b 100644 (file)
@@ -17,6 +17,7 @@ namespace TYPO3\CMS\Backend\Tree\View;
 use TYPO3\CMS\Backend\Utility\BackendUtility;
 use TYPO3\CMS\Backend\Utility\IconUtility;
 use TYPO3\CMS\Core\Utility\GeneralUtility;
+use TYPO3\CMS\Workspaces\Service\WorkspaceService;
 
 /**
  * Base class for creating a browsable array/page/folder tree in HTML
@@ -295,13 +296,18 @@ abstract class AbstractTreeView {
        public $recs = array();
 
        /**
+        * @var WorkspaceService
+        */
+       protected $workspaceService = NULL;
+
+       /**
         * Sets the script url depending on being a module or script request
         */
        protected function determineScriptUrl() {
-               if ($moduleName = \TYPO3\CMS\Core\Utility\GeneralUtility::_GP('M')) {
+               if ($moduleName = GeneralUtility::_GP('M')) {
                        $this->thisScript = \TYPO3\CMS\Backend\Utility\BackendUtility::getModuleUrl($moduleName);
                } else {
-                       $this->thisScript = \TYPO3\CMS\Core\Utility\GeneralUtility::getIndpEnv('SCRIPT_NAME');
+                       $this->thisScript = GeneralUtility::getIndpEnv('SCRIPT_NAME');
                }
        }
 
@@ -963,7 +969,7 @@ abstract class AbstractTreeView {
                        }
                        // Passing on default <td> class for subelements:
                        if (is_array($row) && $subCSSclass !== '') {
-                               if ($this->table === 'pages' && $this->highlightPagesWithVersions && !isset($row['_CSSCLASS']) && count(BackendUtility::countVersionsOfRecordsOnPage($this->BE_USER->workspace, $row['uid']))) {
+                               if ($this->table === 'pages' && $this->highlightPagesWithVersions && !isset($row['_CSSCLASS']) && $this->getWorkspaceService()->hasPageRecordVersions($this->BE_USER->workspace, $row['uid'])) {
                                        $row['_CSSCLASS'] = 'ver-versions';
                                }
                                if (!isset($row['_CSSCLASS'])) {
@@ -1035,4 +1041,15 @@ abstract class AbstractTreeView {
                $this->dataLookup = &$treeLookupArr;
        }
 
+       /**
+        * @return WorkspaceService
+        */
+       protected function getWorkspaceService() {
+               if ($this->workspaceService === NULL) {
+                       $this->workspaceService = GeneralUtility::makeInstance('TYPO3\\CMS\\Workspaces\\Service\\WorkspaceService');
+               }
+
+               return $this->workspaceService;
+       }
+
 }
index c570a37..5392bc1 100644 (file)
@@ -13,6 +13,10 @@ 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
  *
@@ -21,6 +25,11 @@ namespace TYPO3\CMS\Workspaces\ExtDirect;
 class PagetreeCollectionsProcessor implements \TYPO3\CMS\Backend\Tree\Pagetree\CollectionProcessorInterface {
 
        /**
+        * @var WorkspaceService
+        */
+       protected $workspaceService = NULL;
+
+       /**
         * @abstract
         * @param \TYPO3\CMS\Backend\Tree\Pagetree\PagetreeNode $node
         * @param integer $mountPoint
@@ -71,9 +80,20 @@ class PagetreeCollectionsProcessor implements \TYPO3\CMS\Backend\Tree\Pagetree\C
         * @return void
         */
        protected function highlightVersionizedElements(\TYPO3\CMS\Backend\Tree\TreeNode $node) {
-               if (!$node->getCls() && count(\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('TYPO3\\CMS\\Workspaces\\Service\\WorkspaceService');
+               }
+
+               return $this->workspaceService;
+       }
+
 }
index 68f1926..6bf72f3 100644 (file)
@@ -15,6 +15,8 @@ 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;
 
@@ -23,13 +25,18 @@ use TYPO3\CMS\Core\Versioning\VersionState;
  *
  * @author Workspaces Team (http://forge.typo3.org/projects/show/typo3v4-workspaces)
  */
-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;
@@ -291,7 +298,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();
        }
 
@@ -338,7 +345,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();
        }
 
@@ -391,7 +398,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 = \TYPO3\CMS\Core\Utility\GeneralUtility::intExplode(',', $pageList, TRUE);
                        if (!in_array($pageId, $pageIds)) {
@@ -660,6 +667,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() {