[TASK] Only use one select to calculate page cache timeout 13/43613/7
authorAlexander Opitz <alexander.opitz@netresearch.de>
Fri, 1 Mar 2013 09:26:50 +0000 (10:26 +0100)
committerChristian Kuhn <lolli@schwarzbu.ch>
Fri, 9 Oct 2015 20:35:59 +0000 (22:35 +0200)
To get the first time value we do 2 SQL select statements at the
moment but this can be combined in one SQL select statement. This
will save as time and reduces bandwidth to/from the SQL server.

The one SQL select statement from 4.6 had a bug and was changed
to use 2 SQL select statements instead of fixing the statement itself.
So this is a revert to the old source with the fixed statement.

Resolves: #38838
Releases: master
Change-Id: Ida3542ef31e696a9457042c5980e9aa0c6cdfede
Reviewed-on: http://review.typo3.org/43613
Reviewed-by: Morton Jonuschat <m.jonuschat@mojocode.de>
Tested-by: Morton Jonuschat <m.jonuschat@mojocode.de>
Reviewed-by: Christian Kuhn <lolli@schwarzbu.ch>
Tested-by: Christian Kuhn <lolli@schwarzbu.ch>
typo3/sysext/dbal/Tests/Unit/Database/SqlParserTest.php
typo3/sysext/frontend/Classes/Controller/TypoScriptFrontendController.php
typo3/sysext/frontend/Tests/Functional/Controller/TypoScriptFrontendControllerTest.php [new file with mode: 0644]
typo3/sysext/frontend/Tests/Functional/Controller/fixtures.xml [new file with mode: 0644]

index 6b0821d..5a4f346 100644 (file)
@@ -1194,6 +1194,18 @@ class SqlParserTest extends AbstractTestCase
 
     /**
      * @test
+     * @see https://forge.typo3.org/issues/38838
+     */
+    public function caseWithBooleanConditionIsSupportedWithinAggregateFunction()
+    {
+        $parseString = 'MIN(CASE WHEN foo < 100 THEN NULL ELSE foo END) AS foo';
+        $result = $this->subject->parseFieldList($parseString);
+        $this->assertInternalType('array', $result);
+        $this->assertEmpty($parseString);
+    }
+
+    /**
+     * @test
      * @see http://forge.typo3.org/issues/21903
      */
     public function caseWithMultipleWhenIsProperlyCompiled()
index 819440c..f496894 100644 (file)
@@ -4729,20 +4729,51 @@ class TypoScriptFrontendController
         }
         // Additional fields
         $showHidden = $tableName === 'pages' ? $this->showHiddenPage : $this->showHiddenRecords;
-        $enableFields = $this->sys_page->enableFields($tableName, $showHidden, array('starttime' => true, 'endtime' => true));
-        // For each start or end time field, get the minimum value
+        $enableFields = $this->sys_page->enableFields(
+            $tableName,
+            $showHidden,
+            array('starttime' => true, 'endtime' => true)
+        );
+
+        $timeFields = array();
+        $selectFields = array();
+        $whereConditions = array();
         foreach (array('starttime', 'endtime') as $field) {
             if (isset($GLOBALS['TCA'][$tableName]['ctrl']['enablecolumns'][$field])) {
-                $timeField = $GLOBALS['TCA'][$tableName]['ctrl']['enablecolumns'][$field];
-                $selectField = 'MIN(' . $timeField . ') AS ' . $field;
-                $whereCondition = $timeField . ' > ' . $now;
-                // Find the smallest timestamp which could influence the cache duration (but is larger than 0)
-                $row = $this->getDatabaseConnection()->exec_SELECTgetSingleRow($selectField, $tableName, 'pid = ' . (int)$pid . ' AND ' . $whereCondition . $enableFields);
-                if ($row && !is_null($row[$field])) {
-                    $result = min($result, $row[$field]);
+                $timeFields[$field] = $GLOBALS['TCA'][$tableName]['ctrl']['enablecolumns'][$field];
+                $selectFields[$field]
+                    = 'MIN('
+                        . 'CASE WHEN ' . $timeFields[$field] . ' <= ' . $now
+                        . ' THEN NULL ELSE ' . $timeFields[$field] . ' END'
+                        . ') AS ' . $field;
+                $whereConditions[$field] = $timeFields[$field] . '>' . $now;
+            }
+        }
+
+        // if starttime or endtime are defined, evaluate them
+        if (!empty($timeFields)) {
+            // find the timestamp, when the current page's content changes the next time
+            $row = $GLOBALS['TYPO3_DB']->exec_SELECTgetSingleRow(
+                implode(', ', $selectFields),
+                $tableName,
+                'pid=' . (int)$pid
+                . ' AND (' . implode(' OR ', $whereConditions) . ')'
+                . $enableFields
+            );
+            if ($row) {
+                foreach ($timeFields as $timeField => $_) {
+                    // if a MIN value is found, take it into account for the
+                    // cache lifetime we have to filter out start/endtimes < $now,
+                    // as the SQL query also returns rows with starttime < $now
+                    // and endtime > $now (and using a starttime from the past
+                    // would be wrong)
+                    if ($row[$timeField] !== null && (int)$row[$timeField] > $now) {
+                        $result = min($result, (int)$row[$timeField]);
+                    }
                 }
             }
         }
+
         return $result;
     }
 
diff --git a/typo3/sysext/frontend/Tests/Functional/Controller/TypoScriptFrontendControllerTest.php b/typo3/sysext/frontend/Tests/Functional/Controller/TypoScriptFrontendControllerTest.php
new file mode 100644 (file)
index 0000000..f618387
--- /dev/null
@@ -0,0 +1,93 @@
+<?php
+namespace TYPO3\CMS\Frontend\Tests\Functional\Controller;
+
+/*
+ * This file is part of the TYPO3 CMS project.
+ *
+ * It is free software; you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License, either version 2
+ * of the License, or any later version.
+ *
+ * For the full copyright and license information, please read the
+ * LICENSE.txt file that was distributed with this source code.
+ *
+ * The TYPO3 project - inspiring people to share!
+ */
+
+use TYPO3\CMS\Frontend\Controller\TypoScriptFrontendController;
+use TYPO3\CMS\Core\Tests\FunctionalTestCase;
+
+/**
+ * Test case
+ */
+class TypoScriptFrontendControllerTest extends FunctionalTestCase
+{
+    /**
+     * @var TypoScriptFrontendController
+     */
+    protected $tsFrontendController;
+
+    protected function setUp()
+    {
+        parent::setUp();
+        $this->importDataSet(__DIR__ . '/fixtures.xml');
+
+        $this->tsFrontendController = $this->getAccessibleMock(
+            TypoScriptFrontendController::class,
+            array('dummy'),
+            array(),
+            '',
+            false
+        );
+
+        $pageContextMock = $this->getMock(\TYPO3\CMS\Frontend\Page\PageRepository::class);
+        $this->tsFrontendController->_set('sys_page', $pageContextMock);
+    }
+
+    /**
+     * @test
+     */
+    public function getFirstTimeValueForRecordReturnCorrectData()
+    {
+        $this->assertSame(
+            $this->getFirstTimeValueForRecordCall('tt_content:2', 1),
+            2,
+            'The next start/endtime should be 2'
+        );
+        $this->assertSame(
+            $this->getFirstTimeValueForRecordCall('tt_content:2', 2),
+            3,
+            'The next start/endtime should be 3'
+        );
+        $this->assertSame(
+            $this->getFirstTimeValueForRecordCall('tt_content:2', 4),
+            5,
+            'The next start/endtime should be 5'
+        );
+        $this->assertSame(
+            $this->getFirstTimeValueForRecordCall('tt_content:2', 5),
+            PHP_INT_MAX,
+            'The next start/endtime should be PHP_INT_MAX as there are no more'
+        );
+        $this->assertSame(
+            $this->getFirstTimeValueForRecordCall('tt_content:3', 1),
+            PHP_INT_MAX,
+            'Should be PHP_INT_MAX as table has not this PID'
+        );
+        $this->assertSame(
+            $this->getFirstTimeValueForRecordCall('fe_groups:2', 1),
+            PHP_INT_MAX,
+            'Should be PHP_INT_MAX as table fe_groups has no start/endtime in TCA'
+        );
+    }
+
+    /**
+     * @param string $tablePid
+     * @param int $now
+     * @return int
+     */
+    public function getFirstTimeValueForRecordCall($tablePid, $now)
+    {
+        return $this->tsFrontendController->_call('getFirstTimeValueForRecord', $tablePid, $now);
+    }
+}
diff --git a/typo3/sysext/frontend/Tests/Functional/Controller/fixtures.xml b/typo3/sysext/frontend/Tests/Functional/Controller/fixtures.xml
new file mode 100644 (file)
index 0000000..acc4050
--- /dev/null
@@ -0,0 +1,152 @@
+<?xml version="1.0" encoding="utf-8"?>
+<dataset>
+       <pages>
+               <uid>1</uid>
+               <pid>0</pid>
+               <title>Root 1</title>
+               <deleted>0</deleted>
+       </pages>
+       <pages>
+               <uid>2</uid>
+               <pid>1</pid>
+               <title>Dummy 1-2</title>
+               <deleted>0</deleted>
+               <perms_everybody>15</perms_everybody>
+       </pages>
+       <tt_content>
+               <uid>1</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>0</starttime>
+               <endtime>1</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>2</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>0</starttime>
+               <endtime>2</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>3</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>0</starttime>
+               <endtime>4</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>4</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>1</starttime>
+               <endtime>1</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>5</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>1</starttime>
+               <endtime>2</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>6</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>1</starttime>
+               <endtime>4</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>7</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>2</starttime>
+               <endtime>1</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>8</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>2</starttime>
+               <endtime>2</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>9</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>2</starttime>
+               <endtime>4</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>10</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>3</starttime>
+               <endtime>1</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>11</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>3</starttime>
+               <endtime>2</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>12</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>3</starttime>
+               <endtime>4</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>13</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>4</starttime>
+               <endtime>1</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>14</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>4</starttime>
+               <endtime>2</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>15</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>4</starttime>
+               <endtime>4</endtime>
+       </tt_content>
+       <tt_content>
+               <uid>16</uid>
+               <pid>2</pid>
+               <header>On page 2</header>
+               <deleted>0</deleted>
+               <starttime>4</starttime>
+               <endtime>5</endtime>
+       </tt_content>
+       <fe_groups>
+               <uid>1</uid>
+               <pid>2</pid>
+               <title>Test fe group on page 2</title>
+               <description>Test only</description>
+               <subgroup></subgroup>
+               <TSconfig></TSconfig>
+       </fe_groups>
+</dataset>