Fixed Issue #141 - TDbCache performance: modify structure of indices, new property 'FlushInterval' to control how often expired items will be removed

3 files changed, 160 insertions, 17 deletions

diff --git a/HISTORY b/HISTORY
index 4da06061..b6f75d96 100644
--- a/HISTORY
+++ b/HISTORY
@@ -23,6 +23,7 @@ ENH: Issue#115 - Registry for Prado generated clientside counterparts of servers
 ENH: Issue#117 - TValidationSummary: new display mode "HeaderOnly" that only render value of HeaderText property (Yves)
 ENH: Issue#135 - Add AutoPostBack property to TActiveFileUpload (Bradley)
 ENH: Issue#146 - replace deprecated(php5.3>) split with explode in TPgsqlMetaData:getPrimaryKeys(), PradoBase::getUserLanguages() (Yves)
+ENH: Issue#141 - TDbCache performance: modify structure of indices, new property 'FlushInterval' to control how often expired items will be removed (Yves)
 CHG: Issue#7 - Clients Scripts are not combined anymore in Debug application mode (Christophe)
 ENH: Added caching of message files to TException (Michael)
 ENH: Updated to scriptaculous 1.8.2 & Prototype 1.6.0.3
diff --git a/UPGRADE b/UPGRADE
index baec1403..3dbd63e2 100644
--- a/UPGRADE
+++ b/UPGRADE
@@ -1,5 +1,5 @@
 
-         Upgrading Instructions for PRADO Framework v3.1.4
+         Upgrading Instructions for PRADO Framework v3.1.5
          =================================================
 
 !!!IMPORTANT!!!
@@ -9,6 +9,15 @@ if you want to upgrade from version A to version C and there is
 version B between A and C, you need to following the instructions
 for both A and B.
 
+Upgrading from v3.1.4
+---------------------
+- The structure of indices used by TDbCache has been changed by replacing
+  PRIMARY KEY on 'itemkey' with non-unique index and adding an additional index on column 'expire'.
+  Existing tables should be amended or deleted and recreated as follows: 
+  CREATE TABLE pradocache (itemkey CHAR(128), value BLOB, expire INT)
+  CREATE INDEX IX_itemkey ON pradocache (itemkey)
+  CREATE INDEX IX_expire ON pradocache (expire)
+
 Upgrading from v3.1.3
 ---------------------
 - The prado-cli and prado-cli.bat scripts have been moved into
diff --git a/framework/Caching/TDbCache.php b/framework/Caching/TDbCache.php
index 2c2c863d..376ff249 100644
--- a/framework/Caching/TDbCache.php
+++ b/framework/Caching/TDbCache.php
@@ -31,14 +31,23 @@ Prado::using('System.Data.TDbConnection');
  * The cached data is stored in a table in the specified database.

  * By default, the name of the table is called 'pradocache'. If the table does not

  * exist in the database, it will be automatically created with the following structure:

- * (itemkey CHAR(128) PRIMARY KEY, value BLOB, expire INT)

+ * <code>

+ * CREATE TABLE pradocache (itemkey CHAR(128), value BLOB, expire INT)

+ * CREATE INDEX IX_itemkey ON pradocache (itemkey)

+ * CREATE INDEX IX_expire ON pradocache (expire)

+ * </code>

  *

  * Note, some DBMS might not support BLOB type. In this case, replace 'BLOB' with a suitable

  * binary data type (e.g. LONGBLOB in MySQL, BYTEA in PostgreSQL.)

  *

+ * Important: Make sure that the indices are non-unique!

+ *

  * If you want to change the cache table name, or if you want to create the table by yourself,

  * you may set {@link setCacheTableName CacheTableName} and {@link setAutoCreateCacheTable AutoCreateCacheTableName} properties.

  *

+ * {@link setFlushInterval FlushInterval} control how often expired items will be removed from cache.

+ * If you prefer to remove expired items manualy e.g. via cronjob you can disable automatic deletion by setting FlushInterval to '0'.

+ *

  * The following basic cache operations are implemented:

  * - {@link get} : retrieve the value with a key (if any) from cache

  * - {@link set} : store the value with a key into cache

@@ -92,6 +101,18 @@ class TDbCache extends TCache
 	 */

 	private $_cacheTable='pradocache';

 	/**

+	 * @var integer Interval expired items will be removed from cache

+	 */

+	private $_flushInterval=60;

+	/**

+	 * @var boolean

+	 */

+	private $_cacheInitialized = false;

+	/**

+	 * @var boolean

+	 */

+	private $_createCheck= false;

+	/**

 	 * @var boolean whether the cache DB table should be created automatically

 	 */

 	private $_autoCreate=true;

@@ -111,23 +132,70 @@ class TDbCache extends TCache
 

 	/**

 	 * Initializes this module.

-	 * This method is required by the IModule interface. It checks if the DbFile

-	 * property is set, and creates a SQLiteDatabase instance for it.

-	 * The database or the cache table does not exist, they will be created.

-	 * Expired values are also deleted.

+	 * This method is required by the IModule interface.

+	 * attach {@link doInitializeCache} to TApplication.OnLoadStateComplete event

+	 * attach {@link doFlushCacheExpired} to TApplication.OnSaveState event

+	 *

 	 * @param TXmlElement configuration for this module, can be null

-	 * @throws TConfigurationException if sqlite extension is not installed,

-	 *         DbFile is set invalid, or any error happens during creating database or cache table.

 	 */

 	public function init($config)

 	{

+		$this -> getApplication() -> attachEventHandler('OnLoadStateComplete', array($this, 'doInitializeCache'));

+		$this -> getApplication() -> attachEventHandler('OnSaveState', array($this, 'doFlushCacheExpired'));

+		parent::init($config);

+	}

+

+	/**

+	 * Event listener for TApplication.OnSaveState

+	 * @return void

+	 * @since 3.1.5

+	 * @see flushCacheExpired

+	 */

+	public function doFlushCacheExpired()

+	{

+		$this->flushCacheExpired(false);

+	}

+

+	/**

+	 * Event listener for TApplication.OnLoadStateComplete

+	 *

+	 * @return void

+	 * @since 3.1.5

+	 * @see initializeCache

+	 */

+	public function doInitializeCache()

+	{

+		$this->initializeCache();

+	}

+

+	/**

+	 * Initialize TDbCache

+	 *

+	 * If {@link setAutoCreateCacheTable AutoCreateCacheTableName} is 'true' check existence of cache table

+	 * and create table if does not exist.

+	 *

+	 * @return void

+	 * @throws TConfigurationException if any error happens during creating database or cache table.

+	 * @since 3.1.5

+	 */

+	protected function initializeCache()

+	{

+		if($this->_cacheInitialized) return;

+

 		$db=$this->getDbConnection();

 		$db->setActive(true);

-

-		$sql='DELETE FROM '.$this->_cacheTable.' WHERE expire<>0 AND expire<'.time();

 		try

 		{

-			$db->createCommand($sql)->execute();

+			$key = 'TDbCache:' . $this->_cacheTable . ':created';

+			$this -> _createCheck = $this -> getApplication() -> getGlobalState($key, 0);

+

+			if($this->_autoCreate && !$this -> _createCheck) {

+				$sql='SELECT 1 FROM '.$this->_cacheTable.' WHERE 0';

+				$db->createCommand($sql)->queryScalar();

+

+				$this -> _createCheck = true;

+				$this -> getApplication() -> setGlobalState($key, time());

+			}

 		}

 		catch(Exception $e)

 		{

@@ -142,14 +210,69 @@ class TDbCache extends TCache
 				else

 					$blob='BLOB';

 

-				$sql='CREATE TABLE '.$this->_cacheTable." (itemkey CHAR(128) PRIMARY KEY, value $blob, expire INT)";

+				$sql='CREATE TABLE '.$this->_cacheTable." (itemkey CHAR(128), value $blob, expire INT)";

+				$db->createCommand($sql)->execute();

+

+				$sql='CREATE INDEX IX_itemkey ON ' . $this->_cacheTable . ' (itemkey)';

 				$db->createCommand($sql)->execute();

+

+				$sql='CREATE INDEX IX_expire ON ' . $this->_cacheTable . ' (expire)';

+				$db->createCommand($sql)->execute();

+

+				$this -> _createCheck = true;

+				$this -> getApplication() -> setGlobalState($key, time());

 			}

 			else

 				throw new TConfigurationException('db_cachetable_inexistent',$this->_cacheTable);

 		}

+		$this->_cacheInitialized = true;

+	}

 

-		parent::init($config);

+	/**

+	 * Flush expired values from cache depending on {@link setFlushInterval FlushInterval}

+	 * @param boolean override {@link setFlushInterval FlushInterval} and force deletion of expired items

+	 * @return void

+	 * @since 3.1.5

+	 */

+	public function flushCacheExpired($force=false)

+	{

+		$interval = $this -> getFlushInterval();

+		if(!$force && $interval === 0) return;

+

+		$key	= 'TDbCache:' . $this->_cacheTable . ':flushed';

+		$now	= time();

+		$next	= $interval + (integer)$this -> getApplication() -> getGlobalState($key, 0);

+

+		if($force || $next <= $now)

+		{

+			if(!$this->_cacheInitialized) $this->initializeCache();

+			$sql='DELETE FROM '.$this->_cacheTable.' WHERE expire<>0 AND expire<'.$now;

+			$this->getDbConnection()->createCommand($sql)->execute();

+			$this -> getApplication() -> setGlobalState($key, $now);

+		}

+	}

+

+	/**

+	 * @return integer Interval in sec expired items will be removed from cache. Default to 60

+	 * @since 3.1.5

+	 */

+	public function getFlushInterval()

+	{

+		return $this->_flushInterval;

+	}

+

+	/**

+	 * Sets interval expired items will be removed from cache

+	 *

+	 * To disable automatic deletion of expired items,

+	 * e.g. for external flushing via cron you can set value to '0'

+	 *

+	 * @param integer Interval in sec

+	 * @since 3.1.5

+	 */

+	public function setFlushInterval($value)

+	{

+		$this->_flushInterval = (integer) $value;

 	}

 

 	/**

@@ -284,9 +407,17 @@ class TDbCache extends TCache
 	 * Note, if {@link setAutoCreateCacheTable AutoCreateCacheTable} is false

 	 * and you want to create the DB table manually by yourself,

 	 * you need to make sure the DB table is of the following structure:

-	 * (itemkey CHAR(128) PRIMARY KEY, value BLOB, expire INT)

+	 * <code>

+	 * CREATE TABLE pradocache (itemkey CHAR(128), value BLOB, expire INT)

+	 * CREATE INDEX IX_itemkey ON pradocache (itemkey)

+	 * CREATE INDEX IX_expire ON pradocache (expire)

+	 * </code>

+	 *

 	 * Note, some DBMS might not support BLOB type. In this case, replace 'BLOB' with a suitable

 	 * binary data type (e.g. LONGBLOB in MySQL, BYTEA in PostgreSQL.)

+	 *

+	 * Important: Make sure that the indices are non-unique!

+	 *

 	 * @param string the name of the DB table to store cache content

 	 * @see setAutoCreateCacheTable

 	 */

@@ -321,7 +452,8 @@ class TDbCache extends TCache
 	 */

 	protected function getValue($key)

 	{

-		$sql='SELECT value FROM '.$this->_cacheTable.' WHERE itemkey=\''.$key.'\' AND (expire=0 OR expire>'.time().')';

+		if(!$this->_cacheInitialized) $this->initializeCache();

+		$sql='SELECT value FROM '.$this->_cacheTable.' WHERE itemkey=\''.$key.'\' AND (expire=0 OR expire>'.time().') ORDER BY expire DESC';

 		return $this->_db->createCommand($sql)->queryScalar();

 	}

 

@@ -351,6 +483,7 @@ class TDbCache extends TCache
 	 */

 	protected function addValue($key,$value,$expire)

 	{

+		if(!$this->_cacheInitialized) $this->initializeCache();

 		$expire=($expire<=0)?0:time()+$expire;

 		$sql="INSERT INTO {$this->_cacheTable} (itemkey,value,expire) VALUES(:key,:value,$expire)";

 		try

@@ -375,6 +508,7 @@ class TDbCache extends TCache
 	 */

 	protected function deleteValue($key)

 	{

+		if(!$this->_cacheInitialized) $this->initializeCache();

 		$command=$this->_db->createCommand("DELETE FROM {$this->_cacheTable} WHERE itemkey=:key");

 		$command->bindValue(':key',$key,PDO::PARAM_STR);

 		$command->execute();

@@ -387,9 +521,8 @@ class TDbCache extends TCache
 	 */

 	public function flush()

 	{

+		if(!$this->_cacheInitialized) $this->initializeCache();

 		$this->_db->createCommand("DELETE FROM {$this->_cacheTable}")->execute();

 		return true;

 	}

 }

-

-?>