Fixed bug #11903: Use separate tables for tags in the caching framework
authorOliver Hader <oliver.hader@typo3.org>
Mon, 21 Sep 2009 13:11:01 +0000 (13:11 +0000)
committerOliver Hader <oliver.hader@typo3.org>
Mon, 21 Sep 2009 13:11:01 +0000 (13:11 +0000)
git-svn-id: https://svn.typo3.org/TYPO3v4/Core/trunk@6025 709f56b5-9817-0410-a4d7-c38de5d9e867

ChangeLog
t3lib/cache/backend/class.t3lib_cache_backend_dbbackend.php
t3lib/class.t3lib_db.php
t3lib/config_default.php
t3lib/stddb/tables.sql
tests/t3lib/cache/backend/t3lib_cache_backend_dbbackendtestcase.php
typo3/sysext/cms/ext_tables.sql

index 3c02b1d..aa6ea5b 100755 (executable)
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,7 @@
+2009-09-21  Oliver Hader  <oliver@typo3.org>
+
+       * !!! Fixed bug #11903: Use separate tables for tags in the caching framework
+
 2009-09-20  Benjamin Mack  <benni@typo3.org>
 
        * Fixed bug #11642: filelinks are now created via the typolink method
index 9c1069f..90be991 100644 (file)
 class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend {
 
        protected $cacheTable;
+       protected $tagsTable;
+
+       protected $identifierField;
+       protected $creationField;
+       protected $lifetimeField;
+       protected $notExpiredStatement;
+       protected $tableList;
+       protected $tableJoin;
+
+       /**
+        * Constructs this backend
+        *
+        * @param mixed Configuration options - depends on the actual backend
+        */
+       public function __construct(array $options = array()) {
+               parent::__construct($options);
+
+               if (!$this->cacheTable) {
+                       throw new t3lib_cache_Exception(
+                               'No table to write data to has been set using the setting "cacheTable".',
+                               1253534136
+                       );
+               }
+
+               if (!$this->tagsTable) {
+                       throw new t3lib_cache_Exception(
+                               'No table to write tags to has been set using the setting "tagsTable".',
+                               1253534137
+                       );
+               }
+
+               $this->initializeCommonReferences();
+       }
+
+       /**
+        * Initializes common references used in this backend.
+        *
+        * @return      void
+        */
+       protected function initializeCommonReferences() {
+               $this->identifierField = $this->cacheTable . '.identifier';
+               $this->creationField = $this->cacheTable . '.crdate';
+               $this->lifetimeField = $this->cacheTable . '.lifetime';
+               $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)';
+       }
 
        /**
         * Saves data in a cache file.
@@ -73,10 +121,19 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                                'identifier' => $entryIdentifier,
                                'crdate'     => $GLOBALS['EXEC_TIME'],
                                'content'    => $data,
-                               'tags'       => implode(',', $tags),
                                'lifetime'   => $lifetime
                        )
                );
+
+               foreach ($tags as $tag) {
+                       $GLOBALS['TYPO3_DB']->exec_INSERTquery(
+                               $this->tagsTable,
+                               array(
+                                       'identifier' => $entryIdentifier,
+                                       'tag'        => $tag,
+                               )
+                       );
+               }
        }
 
        /**
@@ -143,6 +200,11 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                        'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr($entryIdentifier, $this->cacheTable)
                );
 
+               $GLOBALS['TYPO3_DB']->exec_DELETEquery(
+                       $this->tagsTable,
+                       'identifier = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr($entryIdentifier, $this->tagsTable)
+               );
+
                if($GLOBALS['TYPO3_DB']->sql_affected_rows($res) == 1) {
                        $entryRemoved = true;
                }
@@ -161,9 +223,12 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                $cacheEntryIdentifiers = array();
 
                $cacheEntryIdentifierRows = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
-                       'identifier',
-                       $this->cacheTable,
-                       $this->getListQueryForTag($tag) . ' AND (crdate + lifetime >= ' . $GLOBALS['EXEC_TIME'] . ' OR lifetime = 0)'
+                       $this->identifierField,
+                       $this->tableList,
+                       $this->getQueryForTag($tag) .
+                               ' AND ' . $this->tableJoin .
+                               ' AND ' . $this->notExpiredStatement,
+                       $this->identifierField
                );
 
                foreach ($cacheEntryIdentifierRows as $cacheEntryIdentifierRow) {
@@ -186,14 +251,17 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                $whereClause  = array();
 
                foreach ($tags as $tag) {
-                       $whereClause[] = $this->getListQueryForTag($tag);
+                       $whereClause[] = $this->getQueryForTag($tag);
                }
-               $whereClause[] = '(crdate + lifetime >= ' . $GLOBALS['EXEC_TIME'] . ' OR lifetime = 0)';
+
+               $whereClause[] = $this->tableJoin;
+               $whereClause[] = $this->notExpiredStatement;
 
                $cacheEntryIdentifierRows = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
-                       'identifier',
-                       $this->cacheTable,
-                       implode(' AND ', $whereClause)
+                       $this->identifierField,
+                       $this->tableList,
+                       implode(' AND ', $whereClause),
+                       $this->identifierField
                );
 
                foreach ($cacheEntryIdentifierRows as $cacheEntryIdentifierRow) {
@@ -211,6 +279,7 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
         */
        public function flush() {
                $GLOBALS['TYPO3_DB']->sql_query('TRUNCATE ' . $this->cacheTable);
+               $GLOBALS['TYPO3_DB']->sql_query('TRUNCATE ' . $this->tagsTable);
        }
 
        /**
@@ -220,9 +289,11 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
         * @return void
         */
        public function flushByTag($tag) {
-               $GLOBALS['TYPO3_DB']->exec_DELETEquery(
-                       $this->cacheTable,
-                       $this->getListQueryForTag($tag)
+               $GLOBALS['TYPO3_DB']->exec_DELETEmultipleTablesQuery(
+                       $this->tableList,
+                       $this->tableList,
+                       $this->tableJoin .
+                               ' AND ' . $this->getQueryForTag($tag)
                );
        }
 
@@ -233,16 +304,19 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
         * @return void
         */
        public function flushByTags(array $tags) {
-               $listQueryConditions = array();
-               foreach ($tags as $tag) {
-                       $listQueryConditions[$tag] = $this->getListQueryForTag($tag);
+               if (count($tags)) {
+                       $listQueryConditions = array();
+                       foreach ($tags as $tag) {
+                               $listQueryConditions[$tag] = $this->getQueryForTag($tag);
+                       }
+       
+                       $GLOBALS['TYPO3_DB']->exec_DELETEmultipleTablesQuery(
+                               $this->tableList,
+                               $this->tableList,
+                               $this->tableJoin .
+                                       ' AND (' . implode(' OR ', $listQueryConditions) . ')'
+                       );
                }
-
-               $listQuery = implode(' OR ', $listQueryConditions);
-               $GLOBALS['TYPO3_DB']->exec_DELETEquery(
-                       $this->cacheTable,
-                       $listQuery
-               );
        }
 
        /**
@@ -252,6 +326,14 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
         * @author Ingo Renner <ingo@typo3.org>
         */
        public function collectGarbage() {
+               $GLOBALS['TYPO3_DB']->exec_DELETEmultipleTablesQuery(
+                       $this->tableList,
+                       $this->tableList,
+                       $this->tableJoin .
+                               ' AND ' . $this->cacheTable . '.crdate + ' . $this->cacheTable . '.lifetime < ' . $GLOBALS['EXEC_TIME'] .
+                               ' AND ' . $this->cacheTable . '.lifetime > 0'
+               );
+
                $GLOBALS['TYPO3_DB']->exec_DELETEquery(
                        $this->cacheTable,
                        'crdate + lifetime < ' . $GLOBALS['EXEC_TIME'] . ' AND lifetime > 0'
@@ -300,6 +382,7 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
                }
 */
                $this->cacheTable = $cacheTable;
+               $this->initializeCommonReferences();
        }
 
        /**
@@ -313,6 +396,26 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
        }
 
        /**
+        * Sets the table where cache tags are stored.
+        *
+        * @param       string          $tagsTabls: Name of the table
+        * @return      void
+        */
+       public function setTagsTable($tagsTable) {
+               $this->tagsTable = $tagsTable;
+               $this->initializeCommonReferences();
+       }
+
+       /**
+        * Gets the table where cache tags are stored.
+        *
+        * @return      string          Name of the table storing tags
+        */
+       public function getTagsTable() {
+               return $this->tagsTable;
+       }
+
+       /**
         * Gets the query to be used for selecting entries by a tag. The asterisk ("*")
         * is allowed as a wildcard at the beginning and the end of a tag.
         *
@@ -320,8 +423,18 @@ class t3lib_cache_backend_DbBackend extends t3lib_cache_backend_AbstractBackend
         * @return string the query to be used for selecting entries
         * @author Oliver Hader <oliver@typo3.org>
         */
-       protected function getListQueryForTag($tag) {
-               return str_replace('*', '%', $GLOBALS['TYPO3_DB']->listQuery('tags', $tag, $this->cacheTable));
+       protected function getQueryForTag($tag) {
+               if (strpos($tag, '*') === false) {
+                       $query = $this->tagsTable . '.tag = ' . $GLOBALS['TYPO3_DB']->fullQuoteStr($tag, $this->tagsTable);
+               } else {
+                       $patternForLike = $GLOBALS['TYPO3_DB']->escapeStrForLike(
+                               $GLOBALS['TYPO3_DB']->quoteStr($tag, $this->tagsTable),
+                               $this->tagsTable
+                       );
+                       $query = $this->tagsTable . '.tag LIKE \'' . $patternForLike . '\'';
+               }
+
+               return $query;
        }
 }
 
index 6308cff..d852101 100644 (file)
@@ -216,6 +216,22 @@ class t3lib_DB {
        }
 
        /**
+        * Creates and executes a DELETE SQL-statement for multiple tables.
+        *
+        * @param       string          $tablesToDeleteFrom: Name of the tables to delete from
+        * @param       string          $tablesReference: Name of the tables references join the results
+        * @param       string          $where: WHERE clause, eg. "uid=1". NOTICE: You must escape values in this argument with $this->fullQuoteStr() yourself!
+        * @return      pointer         MySQL result pointer
+        */
+       public function exec_DELETEmultipleTablesQuery($tablesToDeleteFrom, $tablesReference, $where) {
+               $res = mysql_query($this->DELETEmultipleTablesQuery($tablesToDeleteFrom, $tablesReference, $where), $this->link);
+               if ($this->debugOutput) {
+                       $this->debug('exec_DELETEquery');
+               }
+               return $res;
+       }
+
+       /**
         * Creates and executes a SELECT SQL-statement
         * Using this function specifically allow us to handle the LIMIT feature independently of DB.
         * Usage count/core: 340
@@ -466,6 +482,31 @@ class t3lib_DB {
        }
 
        /**
+        * Creates a DELETE SQL-statement for multiple tables
+        * Usage count/core: 3
+        *
+        * @param       string          See exec_DELETEmultipleTablesQuery()
+        * @param       string          See exec_DELETEmultipleTablesQuery()
+        * @param       string          See exec_DELETEmultipleTablesQuery()
+        * @return      string          Full SQL query for DELETE from multiple tables
+        */
+       public function DELETEmultipleTablesQuery($tablesToDeleteFrom, $tablesReference, $where) {
+               if (is_string($where)) {
+                               // Table and fieldnames should be "SQL-injection-safe" when supplied to this function
+                       $query = 'DELETE ' . $tablesToDeleteFrom . ' FROM ' . $tablesReference .
+                               (strlen($where) > 0 ? ' WHERE ' . $where : '');
+
+                       if ($this->debugOutput || $this->store_lastBuiltQuery) {
+                               $this->debug_lastBuiltQuery = $query;
+                       }
+
+                       return $query;
+               } else {
+                       die('<strong>TYPO3 Fatal Error:</strong> "Where" clause argument for DELETE query was not a string in $this->DELETEmultipleTablesQuery() !');
+               }
+       }
+
+       /**
         * Creates a SELECT SQL-statement
         * Usage count/core: 11
         *
index cac9d86..2187181 100644 (file)
@@ -123,19 +123,22 @@ $TYPO3_CONF_VARS = Array(
                                'cache_hash' => array(
                                        'backend' => 't3lib_cache_backend_DbBackend',
                                        'options' => array(
-                                               'cacheTable' => 'cachingframework_cache_hash'
+                                               'cacheTable' => 'cachingframework_cache_hash',
+                                               'tagsTable' => 'cachingframework_cache_hash_tags',
                                        )
                                ),
                                'cache_pages' => array(
                                        'backend' => 't3lib_cache_backend_DbBackend',
                                        'options' => array(
-                                               'cacheTable' => 'cachingframework_cache_pages'
+                                               'cacheTable' => 'cachingframework_cache_pages',
+                                               'tagsTable' => 'cachingframework_cache_pages_tags',
                                        )
                                ),
                                'cache_pagesection' => array(
                                        'backend' => 't3lib_cache_backend_DbBackend',
                                        'options' => array(
-                                               'cacheTable' => 'cachingframework_cache_pagesection'
+                                               'cacheTable' => 'cachingframework_cache_pagesection',
+                                               'tagsTable' => 'cachingframework_cache_pagesection_tags',
                                        )
                                )
                                /*
index d6ad1cd..475b1e5 100644 (file)
@@ -138,15 +138,28 @@ CREATE TABLE cache_hash (
 #
 CREATE TABLE cachingframework_cache_hash (
   id int(11) unsigned NOT NULL auto_increment,
-  identifier varchar(250) DEFAULT '' NOT NULL,
+  identifier varchar(128) DEFAULT '' NOT NULL,
   crdate int(11) unsigned DEFAULT '0' NOT NULL,
   content mediumtext,
-  tags mediumtext,
   lifetime int(11) unsigned DEFAULT '0' NOT NULL,
   PRIMARY KEY (id),
   KEY cache_id (identifier)
 ) ENGINE=InnoDB;
 
+
+#
+# Table structure for table 'cachingframework_cache_hash_tags'
+#
+CREATE TABLE cachingframework_cache_hash_tags (
+  id int(11) unsigned NOT NULL auto_increment,
+  identifier varchar(128) DEFAULT '' NOT NULL,
+  tag varchar(128) DEFAULT '' NOT NULL,
+  PRIMARY KEY (id),
+  KEY cache_id (identifier),
+  KEY cache_tag (tag)
+) ENGINE=InnoDB;
+
+
 #
 # Table structure for table 'cache_imagesizes'
 #
index 21e63fc..7875ece 100644 (file)
@@ -63,6 +63,7 @@ class t3lib_cache_backend_DbBackendTestCase extends tx_phpunit_testcase {
        protected $backend;
 
        protected $testingCacheTable;
+       protected $testingTagsTable;
 
        /**
         * Sets up this testcase
@@ -72,6 +73,7 @@ class t3lib_cache_backend_DbBackendTestCase extends tx_phpunit_testcase {
         */
        public function setUp() {
                $this->testingCacheTable = 'test_cache_dbbackend';
+               $this->testingTagsTable = 'test_cache_dbbackend_tags';
 
                $GLOBALS['TYPO3_DB']->sql_query('CREATE TABLE ' . $this->testingCacheTable . ' (
                        id int(11) unsigned NOT NULL auto_increment,
@@ -85,9 +87,22 @@ class t3lib_cache_backend_DbBackendTestCase extends tx_phpunit_testcase {
                ) ENGINE=InnoDB;
                ');
 
+               $GLOBALS['TYPO3_DB']->sql_query('CREATE TABLE ' . $this->testingTagsTable . ' (
+                       id int(11) unsigned NOT NULL auto_increment,
+                       identifier varchar(128) DEFAULT \'\' NOT NULL,
+                       tag varchar(128) DEFAULT \'\' NOT NULL,
+                       PRIMARY KEY (id),
+                       KEY cache_id (identifier),
+                       KEY cache_tag (tag)
+               ) ENGINE=InnoDB;
+               ');
+
                $this->backend = t3lib_div::makeInstance(
                        't3lib_cache_backend_DbBackend',
-                       array('cacheTable' => $this->testingCacheTable)
+                       array(
+                               'cacheTable' => $this->testingCacheTable,
+                               'tagsTable' => $this->testingTagsTable,
+                       )
                );
        }
 
@@ -213,13 +228,17 @@ class t3lib_cache_backend_DbBackendTestCase extends tx_phpunit_testcase {
 
                $entriesFound = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
                        '*',
-                       $this->testingCacheTable,
+                       $this->testingTagsTable,
                        'identifier = \'' . $entryIdentifier . '\''
                );
 
-               $tags = explode(',', $entriesFound[0]['tags']);
+               $tags = array();
 
-               $this->assertTrue(is_array($tags) && count($entriesFound) > 0, 'The tags do not exist.');
+               foreach ($entriesFound as $entry) {
+                       $tags[] = $entry['tag'];
+               }
+
+               $this->assertTrue(count($tags) > 0, 'The tags do not exist.');
                $this->assertTrue(in_array('UnitTestTag%tag1', $tags), 'Tag UnitTestTag%tag1 does not exist.');
                $this->assertTrue(in_array('UnitTestTag%tag2', $tags), 'Tag UnitTestTag%tag2 does not exist.');
        }
@@ -336,6 +355,7 @@ class t3lib_cache_backend_DbBackendTestCase extends tx_phpunit_testcase {
                $this->assertTrue(is_array($entriesFound) && count($entriesFound) > 0, 'The cache entry does not exist.');
 
                sleep(2);
+               $GLOBALS['EXEC_TIME'] += 2;
                $this->backend->collectGarbage();
 
                $entriesFound = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
@@ -377,6 +397,7 @@ class t3lib_cache_backend_DbBackendTestCase extends tx_phpunit_testcase {
                $this->assertTrue(is_array($entriesFound) && count($entriesFound) > 0, 'The cache entries do not exist.');
 
                sleep(2);
+               $GLOBALS['EXEC_TIME'] += 2;
                $this->backend->collectGarbage();
 
                $entriesFound = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
@@ -495,6 +516,7 @@ class t3lib_cache_backend_DbBackendTestCase extends tx_phpunit_testcase {
                $this->backend->set($expiredEntryIdentifier, $expiredData, array(), 1);
 
                sleep(2);
+               $GLOBALS['EXEC_TIME'] += 2;
 
                $this->assertFalse($this->backend->has($expiredEntryIdentifier), 'has() did not return FALSE.');
        }
@@ -522,6 +544,7 @@ class t3lib_cache_backend_DbBackendTestCase extends tx_phpunit_testcase {
                $this->backend->set($expiredEntryIdentifier, $expiredData, array(), 1);
 
                sleep(2);
+               $GLOBALS['EXEC_TIME'] += 2;
 
                $this->assertEquals($data, $this->backend->get($entryIdentifier), 'The original and the retrieved data don\'t match.');
                $this->assertFalse($this->backend->get($expiredEntryIdentifier), 'The expired entry could be loaded.');
@@ -543,6 +566,9 @@ class t3lib_cache_backend_DbBackendTestCase extends tx_phpunit_testcase {
                $this->backend->set('BackendDbTest', 'some data', array('UnitTestTag%special'), 1);
 
                sleep(2);
+               $GLOBALS['EXEC_TIME'] += 2;
+                       // Not required, but used to update the pre-calculated queries:
+               $this->backend->setTagsTable($this->testingTagsTable);
 
                $this->assertEquals(array(), $this->backend->findIdentifiersByTag('UnitTestTag%special'));
        }
@@ -579,13 +605,16 @@ class t3lib_cache_backend_DbBackendTestCase extends tx_phpunit_testcase {
 
 
        /**
-        * @test
         * @author Ingo Renner <ingo@typo3.org>
         */
        public function tearDown() {
                $GLOBALS['TYPO3_DB']->sql_query(
                        'DROP TABLE ' . $this->testingCacheTable . ';'
                );
+
+               $GLOBALS['TYPO3_DB']->sql_query(
+                       'DROP TABLE ' . $this->testingTagsTable . ';'
+               );
        }
 
 }
index 646a54b..151f083 100755 (executable)
@@ -41,10 +41,9 @@ CREATE TABLE cache_pagesection (
 #
 CREATE TABLE cachingframework_cache_pages (
   id int(11) unsigned NOT NULL auto_increment,
-  identifier varchar(250) DEFAULT '' NOT NULL,
+  identifier varchar(128) DEFAULT '' NOT NULL,
   crdate int(11) unsigned DEFAULT '0' NOT NULL,
   content mediumtext,
-  tags mediumtext,
   lifetime int(11) unsigned DEFAULT '0' NOT NULL,
   PRIMARY KEY (id),
   KEY cache_id (identifier)
@@ -52,14 +51,26 @@ CREATE TABLE cachingframework_cache_pages (
 
 
 #
+# Table structure for table 'cachingframework_cache_pages_tags'
+#
+CREATE TABLE cachingframework_cache_pages_tags (
+  id int(11) unsigned NOT NULL auto_increment,
+  identifier varchar(128) DEFAULT '' NOT NULL,
+  tag varchar(128) DEFAULT '' NOT NULL,
+  PRIMARY KEY (id),
+  KEY cache_id (identifier),
+  KEY cache_tag (tag)
+) ENGINE=InnoDB;
+
+
+#
 # Table structure for table 'cachingframework_cache_pagesection'
 #
 CREATE TABLE cachingframework_cache_pagesection (
   id int(11) unsigned NOT NULL auto_increment,
-  identifier varchar(250) DEFAULT '' NOT NULL,
+  identifier varchar(128) DEFAULT '' NOT NULL,
   crdate int(11) unsigned DEFAULT '0' NOT NULL,
   content mediumtext,
-  tags mediumtext,
   lifetime int(11) unsigned DEFAULT '0' NOT NULL,
   PRIMARY KEY (id),
   KEY cache_id (identifier)
@@ -67,6 +78,19 @@ CREATE TABLE cachingframework_cache_pagesection (
 
 
 #
+# Table structure for table 'cachingframework_cache_pagesection_tags'
+#
+CREATE TABLE cachingframework_cache_pagesection_tags (
+  id int(11) unsigned NOT NULL auto_increment,
+  identifier varchar(128) DEFAULT '' NOT NULL,
+  tag varchar(128) DEFAULT '' NOT NULL,
+  PRIMARY KEY (id),
+  KEY cache_id (identifier),
+  KEY cache_tag (tag)
+) ENGINE=InnoDB;
+
+
+#
 # Table structure for table 'cache_typo3temp_log'
 #
 CREATE TABLE cache_typo3temp_log (