[FEATURE] Caching framework db backend: better db layout
authorBjörn Pedersen <bjoern.pedersen@frm2.tum.de>
Sun, 19 Jun 2011 13:09:08 +0000 (15:09 +0200)
committerChristian Kuhn <lolli@schwarzbu.ch>
Wed, 22 Jun 2011 17:11:35 +0000 (19:11 +0200)
Introduce a better indexable database layout that allows
effective use of indices.

Change-Id: Ie97563e1612d64759bcbd2ee4db005a6d5a33812
Resolves: #25119
Depends: #27506
Depends: #27536
Reviewed-on: http://review.typo3.org/1083
Reviewed-by: Georg Ringer
Tested-by: Georg Ringer
Reviewed-by: Stefan Neufeind
Reviewed-by: Björn Pedersen
Reviewed-by: Christian Kuhn
Tested-by: Christian Kuhn
t3lib/cache/backend/class.t3lib_cache_backend_dbbackend.php
t3lib/cache/backend/resources/dbbackend-layout-cache.sql
tests/t3lib/cache/backend/class.t3lib_cache_backend_dbbackendTest.php

index d69ed06..e1a4a53 100644 (file)
 class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend {
 
        /**
+        * @var integer Timestamp of 2038-01-01)
+        */
+       const FAKED_UNLIMITED_EXPIRE = 2145909600;
+
+       /**
         * @var string Name of the cache data table
         */
        protected $cacheTable;
@@ -52,11 +57,39 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
         */
        protected $compressionLevel = -1;
 
+       /**
+        * @var string Name of the identifier field, 'table_name.identifier'
+        */
        protected $identifierField;
-       protected $creationField;
-       protected $lifetimeField;
+
+       /**
+        * @var string Name of the expire field, 'table_name.expires'
+        */
+       protected $expiresField;
+
+       /**
+        * @var integer Maximum lifetime to stay with expire field below FAKED_UNLIMITED_LIFETIME
+        */
+       protected $maximumLifetime;
+
+       /**
+        * @var string SQL where for a not expired entry
+        */
        protected $notExpiredStatement;
+
+       /**
+        * @var string Opposite of notExpiredStatement
+        */
+       protected $expiredStatement;
+
+       /**
+        * @var string Data and tags table name comma separated
+        */
        protected $tableList;
+
+       /**
+        * @var string Join condition for data and tags table
+        */
        protected $tableJoin;
 
        /**
@@ -82,12 +115,12 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
         */
        protected function initializeCommonReferences() {
                $this->identifierField = $this->cacheTable . '.identifier';
-               $this->creationField = $this->cacheTable . '.crdate';
-               $this->lifetimeField = $this->cacheTable . '.lifetime';
+               $this->expiresField = $this->cacheTable . '.expires';
+               $this->maximumLifetime = self::FAKED_UNLIMITED_EXPIRE - $GLOBALS['EXEC_TIME'];
                $this->tableList = $this->cacheTable . ', ' . $this->tagsTable;
                $this->tableJoin = $this->identifierField . ' = ' . $this->tagsTable . '.identifier';
-               $this->notExpiredStatement = '(' . $this->creationField . ' + ' . $this->lifetimeField .
-                                                                        ' >= ' . $GLOBALS['EXEC_TIME'] . ' OR ' . $this->lifetimeField . ' = 0)';
+               $this->expiredStatement = $this->expiresField . ' < ' . $GLOBALS['EXEC_TIME'];
+               $this->notExpiredStatement = $this->expiresField . ' >= ' . $GLOBALS['EXEC_TIME'];
        }
 
        /**
@@ -120,6 +153,10 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                if (is_null($lifetime)) {
                        $lifetime = $this->defaultLifetime;
                }
+               if ($lifetime === 0 || $lifetime > $this->maximumLifetime) {
+                       $lifetime = $this->maximumLifetime;
+               }
+               $expires = $GLOBALS['EXEC_TIME'] + $lifetime;
 
                $this->remove($entryIdentifier);
 
@@ -130,10 +167,9 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                $GLOBALS['TYPO3_DB']->exec_INSERTquery(
                        $this->cacheTable,
                        array(
-                                'identifier' => $entryIdentifier,
-                                'crdate' => $GLOBALS['EXEC_TIME'],
-                                'content' => $data,
-                                'lifetime' => $lifetime
+                               'identifier' => $entryIdentifier,
+                               'expires' => $expires,
+                               'content' => $data,
                        )
                );
 
@@ -178,8 +214,8 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                $cacheEntry = $GLOBALS['TYPO3_DB']->exec_SELECTgetSingleRow(
                        'content',
                        $this->cacheTable,
-                       'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr($entryIdentifier, $this->cacheTable) . ' '
-                       . 'AND (crdate + lifetime >= ' . $GLOBALS['EXEC_TIME'] . ' OR lifetime = 0)'
+                       'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr($entryIdentifier, $this->cacheTable) .
+                               ' AND ' . $this->notExpiredStatement
                );
 
                if (is_array($cacheEntry)) {
@@ -214,7 +250,7 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                        '*',
                        $this->cacheTable,
                        'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr($entryIdentifier, $this->cacheTable) .
-                       ' AND (crdate + lifetime >= ' . $GLOBALS['EXEC_TIME'] . ' OR lifetime = 0)'
+                               ' AND ' . $this->notExpiredStatement
                );
                if ($cacheEntries >= 1) {
                        $hasEntry = TRUE;
@@ -355,7 +391,7 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                $tagsEntryIdentifierRowsResource = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
                        'identifier',
                        $this->cacheTable,
-                       'crdate + lifetime < ' . $GLOBALS['EXEC_TIME'] . ' AND lifetime > 0'
+                       $this->expiredStatement
                );
 
                $tagsEntryIdentifiers = array();
@@ -378,7 +414,7 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                        // Delete expired cache rows
                $GLOBALS['TYPO3_DB']->exec_DELETEquery(
                        $this->cacheTable,
-                       'crdate + lifetime < ' . $GLOBALS['EXEC_TIME'] . ' AND lifetime > 0'
+                       $this->expiredStatement
                );
        }
 
index 17252e5..78759b6 100644 (file)
@@ -1,9 +1,8 @@
 CREATE TABLE ###CACHE_TABLE### (
        id int(11) unsigned NOT NULL auto_increment,
        identifier varchar(250) DEFAULT '' NOT NULL,
-       crdate int(11) unsigned DEFAULT '0' NOT NULL,
+       expires int(11) unsigned DEFAULT '0' NOT NULL,
        content mediumblob,
-       lifetime int(11) unsigned DEFAULT '0' NOT NULL,
        PRIMARY KEY (id),
-       KEY cache_id (identifier)
+       KEY cache_id (identifier, expires)
 ) ENGINE=InnoDB;
\ No newline at end of file
index 59558f4..a10a29b 100644 (file)
@@ -79,11 +79,10 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
                $GLOBALS['TYPO3_DB']->sql_query('CREATE TABLE ' . $this->testingCacheTable . ' (
                        id int(11) unsigned NOT NULL auto_increment,
                        identifier varchar(250) DEFAULT \'\' NOT NULL,
-                       crdate int(11) unsigned DEFAULT \'0\' NOT NULL,
+                       expires int(11) unsigned DEFAULT \'0\' NOT NULL,
                        content mediumblob,
-                       lifetime int(11) unsigned DEFAULT \'0\' NOT NULL,
                        PRIMARY KEY (id),
-                       KEY cache_id (identifier)
+                       KEY cache_id (identifier, expires)
                ) ENGINE=InnoDB;
                ');
 
@@ -110,12 +109,14 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
         * Helper method to inject a mock frontend to backend instance
         *
         * @param t3lib_cache_backend_DbBackend $backend Current backend instance
-        * @return void
+        * @return t3lib_cache_frontend_Frontend Mock frontend
         */
        protected function setUpMockFrontendOfBackend(t3lib_cache_backend_DbBackend $backend) {
                $mockCache = $this->getMock('t3lib_cache_frontend_AbstractFrontend', array(), array(), '', FALSE);
                $mockCache->expects($this->any())->method('getIdentifier')->will($this->returnValue('Testing'));
                $backend->setCache($mockCache);
+
+               return $mockCache;
        }
 
        /**
@@ -410,7 +411,7 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
         */
        public function collectGarbageReallyRemovesAnExpiredCacheEntry() {
                $backend = $this->setUpBackend();
-               $this->setUpMockFrontendOfBackend($backend);
+               $mockCache = $this->setUpMockFrontendOfBackend($backend);
 
                $data = 'some data' . microtime();
                $entryIdentifier = 'BackendDbRemovalTest';
@@ -418,6 +419,10 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
                $backend->set($entryIdentifier, $data, array(), 1);
 
                $GLOBALS['EXEC_TIME'] += 2;
+                       // setCache calls initializeCommonReferences which recalculate expire statement
+                       // needed after manual $GLOBALS['EXEC_TIME'] manipulation
+               $backend->setCache($mockCache);
+
                $backend->collectGarbage();
 
                $entriesFound = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
@@ -435,7 +440,7 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
         */
        public function collectGarbageReallyRemovesAllExpiredCacheEntries() {
                $backend = $this->setUpBackend();
-               $this->setUpMockFrontendOfBackend($backend);
+               $mockCache = $this->setUpMockFrontendOfBackend($backend);
 
                $data = 'some data' . microtime();
                $entryIdentifier = 'BackendDbRemovalTest';
@@ -445,6 +450,10 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
                $backend->set($entryIdentifier . 'C', $data, array(), 1);
 
                $GLOBALS['EXEC_TIME'] += 2;
+                       // setCache calls initializeCommonReferences which recalculate expire statement
+                       // needed after manual $GLOBALS['EXEC_TIME'] manipulation
+               $backend->setCache($mockCache);
+
                $backend->collectGarbage();
 
                $entriesFound = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
@@ -634,7 +643,7 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
         */
        public function hasReturnsTheCorrectResultForEntryWithExceededLifetime() {
                $backend = $this->setUpBackend();
-               $this->setUpMockFrontendOfBackend($backend);
+               $mockCache = $this->setUpMockFrontendOfBackend($backend);
 
                $data = 'some data' . microtime();
                $entryIdentifier = 'BackendDbTest';
@@ -646,6 +655,9 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
                $backend->set($expiredEntryIdentifier, $expiredData, array(), 1);
 
                $GLOBALS['EXEC_TIME'] += 2;
+                       // setCache calls initializeCommonReferences which recalculate expire statement
+                       // needed after manual $GLOBALS['EXEC_TIME'] manipulation
+               $backend->setCache($mockCache);
 
                $this->assertFalse($backend->has($expiredEntryIdentifier));
        }
@@ -656,13 +668,17 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
         */
        public function hasReturnsTrueForEntryWithUnlimitedLifetime() {
                $backend = $this->setUpBackend();
-               $this->setUpMockFrontendOfBackend($backend);
+               $mockCache = $this->setUpMockFrontendOfBackend($backend);
 
                $entryIdentifier = 'BackendDbTest';
 
                $backend->set($entryIdentifier, 'data', array(), 0);
 
                $GLOBALS['EXEC_TIME'] += 1;
+                       // setCache calls initializeCommonReferences which recalculate expire statement
+                       // needed after manual $GLOBALS['EXEC_TIME'] manipulation
+               $backend->setCache($mockCache);
+
                $this->assertTrue($backend->has($entryIdentifier));
        }
 
@@ -672,7 +688,7 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
         */
        public function getReturnsFalseForEntryWithExceededLifetime() {
                $backend = $this->setUpBackend();
-               $this->setUpMockFrontendOfBackend($backend);
+               $mockCache = $this->setUpMockFrontendOfBackend($backend);
 
                $data = 'some data' . microtime();
                $entryIdentifier = 'BackendDbTest';
@@ -684,6 +700,9 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
                $backend->set($expiredEntryIdentifier, $expiredData, array(), 1);
 
                $GLOBALS['EXEC_TIME'] += 2;
+                       // setCache calls initializeCommonReferences which recalculate expire statement
+                       // needed after manual $GLOBALS['EXEC_TIME'] manipulation
+               $backend->setCache($mockCache);
 
                $this->assertEquals($data, $backend->get($entryIdentifier));
                $this->assertFalse($backend->get($expiredEntryIdentifier));
@@ -704,14 +723,13 @@ class t3lib_cache_backend_DbBackendTest extends tx_phpunit_testcase {
         */
        public function findIdentifiersByTagReturnsEmptyArrayForEntryWithExceededLifetime() {
                $backend = $this->setUpBackend();
-               $mockCache = $this->getMock('t3lib_cache_frontend_AbstractFrontend', array(), array(), '', FALSE);
-               $mockCache->expects($this->any())->method('getIdentifier')->will($this->returnValue('Testing'));
-               $backend->setCache($mockCache);
+               $mockCache = $this->setUpMockFrontendOfBackend($backend);
 
                $backend->set('BackendDbTest', 'some data', array('UnitTestTag%special'), 1);
 
                $GLOBALS['EXEC_TIME'] += 2;
-                       // Not required, but used to update the pre-calculated queries:
+                       // setCache calls initializeCommonReferences which recalculate expire statement
+                       // needed after manual $GLOBALS['EXEC_TIME'] manipulation
                $backend->setCache($mockCache);
 
                $this->assertEquals(array(), $backend->findIdentifiersByTag('UnitTestTag%special'));