|
Smarty
WARNING: All discussion is moving to https://reddit.com/r/smarty, please go there! This forum will be closing soon. |
|
View previous topic :: View next topic |
Author |
Message |
bluejester Smarty Regular
Joined: 26 Apr 2012 Posts: 55
|
Posted: Wed Jan 08, 2014 4:34 pm Post subject: Smarty 3 : PDO Cache handler (w/ optionnal GZIP support) |
|
|
Hello,
Here's my contribution to Smarty with 2 plugins that enables cache handling with PDO, and cache handling with PDO and Gzip.
Rodney, I know you made a class once for that (and I reused some code of it) but it didn't include the correct use (or not) of a cache_id and a compile_id.
Besides, I wanted to get the PDO connection out of the class, as it's not its role.
I also wanted to implement the Smarty::CLEAR_EXPIRED for deleting the expired caches. With tens of thousands of cache files, you no longer have to open each one to check if it's expired or not.
Here's the plugin :
Code: |
<?php
/**
* PDO Cache Handler
* Allows you to store Smarty Cache files into your db.
*
* Example table :
* CREATE TABLE `smarty_cache` (
`id` char(40) NOT NULL COMMENT 'sha1 hash',
`name` varchar(250) NOT NULL,
`cache_id` varchar(250) DEFAULT NULL,
`compile_id` varchar(250) DEFAULT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expire` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`content` mediumblob NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `cache_id` (`cache_id`),
KEY `compile_id` (`compile_id`),
KEY `modified` (`modified`),
KEY `expire` (`expire`)
) ENGINE=InnoDB
*
* Example usage :
* $cnx = new PDO("mysql:host=localhost;dbname=mydb", "username", "password");
* $smarty->setCachingType('pdo');
* $smarty->registerCacheResource('pdo', new Smarty_CacheResource_Pdo($cnx, 'smarty_cache'));
*
* @author Beno!t POLASZEK - 2014
*/
class Smarty_CacheResource_Pdo extends Smarty_CacheResource_Custom {
protected $fetchStatements = Array('default' => 'SELECT %2$s
FROM %1$s
WHERE 1
AND id = :id
AND cache_id IS NULL
AND compile_id IS NULL',
'withCacheId' => 'SELECT %2$s
FROM %1$s
WHERE 1
AND id = :id
AND cache_id = :cache_id
AND compile_id IS NULL',
'withCompileId' => 'SELECT %2$s
FROM %1$s
WHERE 1
AND id = :id
AND compile_id = :compile_id
AND cache_id IS NULL',
'withCacheIdAndCompileId'=> 'SELECT %2$s
FROM %1$s
WHERE 1
AND id = :id
AND cache_id = :cache_id
AND compile_id = :compile_id');
protected $insertStatement = 'INSERT INTO %s
SET id = :id,
name = :name,
cache_id = :cache_id,
compile_id = :compile_id,
modified = CURRENT_TIMESTAMP,
expire = DATE_ADD(CURRENT_TIMESTAMP, INTERVAL :expire SECOND),
content = :content
ON DUPLICATE KEY UPDATE
name = :name,
cache_id = :cache_id,
compile_id = :compile_id,
modified = CURRENT_TIMESTAMP,
expire = DATE_ADD(CURRENT_TIMESTAMP, INTERVAL :expire SECOND),
content = :content';
protected $deleteStatement = 'DELETE FROM %1$s WHERE %2$s';
protected $truncateStatement = 'TRUNCATE TABLE %s';
protected $fetchColumns = 'modified, content';
protected $fetchTimestampColumns = 'modified';
protected $pdo, $table, $database;
/*
* Constructor
*
* @param PDO $pdo PDO : active connection
* @param string $table : table (or view) name
* @param string $database : optionnal - if table is located in another db
*/
public function __construct(PDO $pdo, $table, $database = null) {
if (is_null($table))
throw new SmartyException("Table name for caching can't be null");
$this->pdo = $pdo;
$this->table = $table;
$this->database = $database;
$this->fillStatementsWithTableName();
}
/*
* Fills the table name into the statements.
*
* @return Current Instance
* @access protected
*/
protected function fillStatementsWithTableName() {
foreach ($this->fetchStatements AS &$statement)
$statement = sprintf($statement, $this->getTableName(), '%s');
$this->insertStatement = sprintf($this->insertStatement, $this->getTableName());
$this->deleteStatement = sprintf($this->deleteStatement, $this->getTableName(), '%s');
$this->truncateStatement = sprintf($this->truncateStatement, $this->getTableName());
return $this;
}
/*
* Gets the fetch statement, depending on what you specify
*
* @param string $columns : the column(s) name(s) you want to retrieve from the database
* @param string $id unique cache content identifier
* @param string|null $cache_id cache id
* @param string|null $compile_id compile id
* @access protected
*/
protected function getFetchStatement($columns, $id, $cache_id = null, $compile_id = null) {
if (!is_null($cache_id) && !is_null($compile_id))
$query = $this->fetchStatements['withCacheIdAndCompileId'] AND $args = Array('id' => $id, 'cache_id' => $cache_id, 'compile_id' => $compile_id);
elseif (is_null($cache_id) && !is_null($compile_id))
$query = $this->fetchStatements['withCompileId'] AND $args = Array('id' => $id, 'compile_id' => $compile_id);
elseif (!is_null($cache_id) && is_null($compile_id))
$query = $this->fetchStatements['withCacheId'] AND $args = Array('id' => $id, 'cache_id' => $cache_id);
else
$query = $this->fetchStatements['default'] AND $args = Array('id' => $id);
$query = sprintf($query, $columns);
$stmt = $this->pdo->prepare($query);
foreach ($args AS $key => $value)
$stmt->bindValue($key, $value);
return $stmt;
}
/**
* fetch cached content and its modification time from data source
*
* @param string $id unique cache content identifier
* @param string $name template name
* @param string|null $cache_id cache id
* @param string|null $compile_id compile id
* @param string $content cached content
* @param integer $mtime cache modification timestamp (epoch)
* @return void
* @access protected
*/
protected function fetch($id, $name, $cache_id = null, $compile_id = null, &$content, &$mtime) {
$stmt = $this->getFetchStatement($this->fetchColumns, $id, $cache_id, $compile_id);
$stmt -> execute();
$row = $stmt->fetch();
$stmt -> closeCursor();
if ($row) {
$content = $this->outputContent($row['content']);
$mtime = strtotime($row['modified']);
} else {
$content = null;
$mtime = null;
}
}
/**
* Fetch cached content's modification timestamp from data source
*
* {@internal implementing this method is optional.
* Only implement it if modification times can be accessed faster than loading the complete cached content.}}
*
* @param string $id unique cache content identifier
* @param string $name template name
* @param string|null $cache_id cache id
* @param string|null $compile_id compile id
* @return integer|boolean timestamp (epoch) the template was modified, or false if not found
* @access protected
*/
protected function fetchTimestamp($id, $name, $cache_id = null, $compile_id = null) {
$stmt = $this->getFetchStatement($this->fetchTimestampColumns, $id, $cache_id, $compile_id);
$stmt -> execute();
$mtime = strtotime($stmt->fetchColumn());
$stmt -> closeCursor();
return $mtime;
}
/**
* Save content to cache
*
* @param string $id unique cache content identifier
* @param string $name template name
* @param string|null $cache_id cache id
* @param string|null $compile_id compile id
* @param integer|null $exp_time seconds till expiration time in seconds or null
* @param string $content content to cache
* @return boolean success
* @access protected
*/
protected function save($id, $name, $cache_id = null, $compile_id = null, $exp_time, $content) {
$stmt = $this->pdo->prepare($this->insertStatement);
$stmt -> bindValue('id', $id);
$stmt -> bindValue('name', $name);
$stmt -> bindValue('cache_id', $cache_id, (is_null($cache_id)) ? PDO::PARAM_NULL : PDO::PARAM_STR);
$stmt -> bindValue('compile_id', $compile_id, (is_null($compile_id)) ? PDO::PARAM_NULL : PDO::PARAM_STR);
$stmt -> bindValue('expire', (int) $exp_time, PDO::PARAM_INT);
$stmt -> bindValue('content', $this->inputContent($content));
$stmt -> execute();
return !!$stmt->rowCount();
}
/*
* Encodes the content before saving to database
*
* @param string $content
* @return string $content
* @access protected
*/
protected function inputContent($content) {
return $content;
}
/*
* Decodes the content before saving to database
*
* @param string $content
* @return string $content
* @access protected
*/
protected function outputContent($content) {
return $content;
}
/**
* Delete content from cache
*
* @param string|null $name template name
* @param string|null $cache_id cache id
* @param string|null $compile_id compile id
* @param integer|null|-1 $exp_time seconds till expiration or null
* @return integer number of deleted caches
* @access protected
*/
protected function delete($name = null, $cache_id = null, $compile_id = null, $exp_time = null) {
// Temporary bugfix for "string:[...]" templates - Issue #169
if (preg_match('/(^string:(.*)$)/', $name))
$name = substr($name, 7);
// delete the whole cache
if ($name === null && $cache_id === null && $compile_id === null && $exp_time === null) {
// returning the number of deleted caches would require a second query to count them
$this->pdo->query($this->truncateStatement);
return -1;
}
// build the filter
$where = array();
// equal test name
if ($name !== null) {
$where[] = 'name = ' . $this->pdo->quote($name);
}
// equal test cache_id and match sub-groups
if ($cache_id !== null) {
$where[] = '(cache_id = '. $this->pdo->quote($cache_id)
. ' OR cache_id LIKE '. $this->pdo->quote($cache_id .'|%') .')';
}
// equal test compile_id
if ($compile_id !== null) {
$where[] = 'compile_id = ' . $this->pdo->quote($compile_id);
}
// for clearing expired caches
if ($exp_time === Smarty::CLEAR_EXPIRED) {
$where[] = 'expire < CURRENT_TIMESTAMP';
}
// range test expiration time
elseif ($exp_time !== null) {
$where[] = 'modified < DATE_SUB(NOW(), INTERVAL ' . intval($exp_time) . ' SECOND)';
}
// run delete query
$query = $this->pdo->query(sprintf($this->deleteStatement, join(' AND ', $where)));
return $query->rowCount();
}
/**
* Gets the formatted table name
*
* @return string
* @access protected
*/
protected function getTableName() {
return (is_null($this->database)) ? "`{$this->table}`" : "`{$this->database}`.`{$this->table}`";
}
}
|
And here's the extension with gzip support :
Code: |
<?php
/**
* PDO Cache Handler with GZIP support
*
* Example usage :
* $cnx = new PDO("mysql:host=localhost;dbname=mydb", "username", "password");
* $smarty->setCachingType('pdo_gzip');
* $smarty->registerCacheResource('pdo_gzip', new Smarty_CacheResource_Pdo_Gzip($cnx, 'smarty_cache'));
*
* @require Smarty_CacheResource_Pdo class
* @author Beno!t POLASZEK - 2014
*/
class Smarty_CacheResource_Pdo_Gzip extends Smarty_CacheResource_Pdo {
/*
* Encodes the content before saving to database
*
* @param string $content
* @return string $content
* @access protected
*/
protected function inputContent($content) {
return gzdeflate($content);
}
/*
* Decodes the content before saving to database
*
* @param string $content
* @return string $content
* @access protected
*/
protected function outputContent($content) {
return gzinflate($content);
}
}
|
Don't hesitate to report any bug here.
Ben
Last edited by bluejester on Thu Jan 09, 2014 8:21 am; edited 2 times in total |
|
Back to top |
|
U.Tews Administrator
Joined: 22 Nov 2006 Posts: 5068 Location: Hamburg / Germany
|
Posted: Thu Jan 09, 2014 12:10 am Post subject: |
|
|
I have a comment for optimization.
For database resources you should remove the fetchTimestamp() method. If this is not implemented Smarty will fallback to fetch() and cache the content internally for later use. This will save 1 database access when the cache is still valid. |
|
Back to top |
|
bluejester Smarty Regular
Joined: 26 Apr 2012 Posts: 55
|
Posted: Thu Jan 09, 2014 8:35 am Post subject: |
|
|
Hello Uwe,
Indeed. Is it better to remove the fetchTimestamp() method so ?
Do you mean that the $smarty->isCached() method doesn't just fetch the modification time, but the whole content that will be used later (for $smarty->display() for example) ?
As an evolution of this class, I was wondering if it wouldn't be nice to use the vertical partitionning.
Example :
Table smarty_cache_headers : id, name, cache_id, compile_id, modified, expire
Table smarty_cache_content : id, content
View smarty_cache : smarty_cache_headers INNER JOIN smarty_cache_content USING (id)
When querying smarty_cache view, the fetchTimestamp() method would only read the smarty_cache_headers table.
But since you can't update a view, the save() method would be more complex.
What do you think about this ?
Thanks,
Ben |
|
Back to top |
|
U.Tews Administrator
Joined: 22 Nov 2006 Posts: 5068 Location: Hamburg / Germany
|
Posted: Thu Jan 09, 2014 8:19 pm Post subject: |
|
|
Even if you do not call isCached() display() would call fetchTimestamp() and fetch(). For example for the normal file resource it makes sense, because the timestamp can be accessed without reading the file itself.
For the a datebase it does not make sense at all because the most likely access will be the situation that the cache is valid and you can access timestamp and content at the same time.
So remove fetchTimestamp().
It's just the rare case when the cache is invalid that you read content that will not be used.
Vertical partitioning does not make sense. It will just create overhead. |
|
Back to top |
|
bluejester Smarty Regular
Joined: 26 Apr 2012 Posts: 55
|
Posted: Tue Jan 14, 2014 10:11 am Post subject: |
|
|
OK, that makes sense.
Here's the new class without the fetchTimestamp() and the getFetchStatement() methods and simplified statements :
Code: |
<?php
/**
* PDO Cache Handler
* Allows you to store Smarty Cache files into your db.
*
* Example table :
* CREATE TABLE `smarty_cache` (
`id` char(40) NOT NULL COMMENT 'sha1 hash',
`name` varchar(250) NOT NULL,
`cache_id` varchar(250) DEFAULT NULL,
`compile_id` varchar(250) DEFAULT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expire` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`content` mediumblob NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `cache_id` (`cache_id`),
KEY `compile_id` (`compile_id`),
KEY `modified` (`modified`),
KEY `expire` (`expire`)
) ENGINE=InnoDB
*
* Example usage :
* $cnx = new PDO("mysql:host=localhost;dbname=mydb", "username", "password");
* $smarty->setCachingType('pdo');
* $smarty->registerCacheResource('pdo', new Smarty_CacheResource_Pdo($cnx, 'smarty_cache'));
*
* @author Beno!t POLASZEK - 2014
*/
class Smarty_CacheResource_Pdo extends Smarty_CacheResource_Custom {
protected $fetchStatements = Array('default' => 'SELECT modified, content
FROM %s
WHERE 1
AND id = :id
AND cache_id IS NULL
AND compile_id IS NULL',
'withCacheId' => 'SELECT modified, content
FROM %s
WHERE 1
AND id = :id
AND cache_id = :cache_id
AND compile_id IS NULL',
'withCompileId' => 'SELECT modified, content
FROM %s
WHERE 1
AND id = :id
AND compile_id = :compile_id
AND cache_id IS NULL',
'withCacheIdAndCompileId'=> 'SELECT modified, content
FROM %s
WHERE 1
AND id = :id
AND cache_id = :cache_id
AND compile_id = :compile_id');
protected $insertStatement = 'INSERT INTO %s
SET id = :id,
name = :name,
cache_id = :cache_id,
compile_id = :compile_id,
modified = CURRENT_TIMESTAMP,
expire = DATE_ADD(CURRENT_TIMESTAMP, INTERVAL :expire SECOND),
content = :content
ON DUPLICATE KEY UPDATE
name = :name,
cache_id = :cache_id,
compile_id = :compile_id,
modified = CURRENT_TIMESTAMP,
expire = DATE_ADD(CURRENT_TIMESTAMP, INTERVAL :expire SECOND),
content = :content';
protected $deleteStatement = 'DELETE FROM %1$s WHERE %2$s';
protected $truncateStatement = 'TRUNCATE TABLE %s';
protected $pdo, $table, $database;
/*
* Constructor
*
* @param PDO $pdo PDO : active connection
* @param string $table : table (or view) name
* @param string $database : optionnal - if table is located in another db
*/
public function __construct(PDO $pdo, $table, $database = null) {
if (is_null($table))
throw new SmartyException("Table name for caching can't be null");
$this->pdo = $pdo;
$this->table = $table;
$this->database = $database;
$this->fillStatementsWithTableName();
}
/*
* Fills the table name into the statements.
*
* @return Current Instance
* @access protected
*/
protected function fillStatementsWithTableName() {
foreach ($this->fetchStatements AS &$statement)
$statement = sprintf($statement, $this->getTableName());
$this->insertStatement = sprintf($this->insertStatement, $this->getTableName());
$this->deleteStatement = sprintf($this->deleteStatement, $this->getTableName(), '%s');
$this->truncateStatement = sprintf($this->truncateStatement, $this->getTableName());
return $this;
}
/**
* fetch cached content and its modification time from data source
*
* @param string $id unique cache content identifier
* @param string $name template name
* @param string|null $cache_id cache id
* @param string|null $compile_id compile id
* @param string $content cached content
* @param integer $mtime cache modification timestamp (epoch)
* @return void
* @access protected
*/
protected function fetch($id, $name, $cache_id = null, $compile_id = null, &$content, &$mtime) {
if (!is_null($cache_id) && !is_null($compile_id))
$query = $this->fetchStatements['withCacheIdAndCompileId'] AND $args = Array('id' => $id, 'cache_id' => $cache_id, 'compile_id' => $compile_id);
elseif (is_null($cache_id) && !is_null($compile_id))
$query = $this->fetchStatements['withCompileId'] AND $args = Array('id' => $id, 'compile_id' => $compile_id);
elseif (!is_null($cache_id) && is_null($compile_id))
$query = $this->fetchStatements['withCacheId'] AND $args = Array('id' => $id, 'cache_id' => $cache_id);
else
$query = $this->fetchStatements['default'] AND $args = Array('id' => $id);
$stmt = $this->pdo->prepare($query);
foreach ($args AS $key => $value)
$stmt->bindValue($key, $value);
$stmt -> execute();
$row = $stmt->fetch();
$stmt -> closeCursor();
if ($row) {
$content = $this->outputContent($row['content']);
$mtime = strtotime($row['modified']);
} else {
$content = null;
$mtime = null;
}
}
/**
* Save content to cache
*
* @param string $id unique cache content identifier
* @param string $name template name
* @param string|null $cache_id cache id
* @param string|null $compile_id compile id
* @param integer|null $exp_time seconds till expiration time in seconds or null
* @param string $content content to cache
* @return boolean success
* @access protected
*/
protected function save($id, $name, $cache_id = null, $compile_id = null, $exp_time, $content) {
$stmt = $this->pdo->prepare($this->insertStatement);
$stmt -> bindValue('id', $id);
$stmt -> bindValue('name', $name);
$stmt -> bindValue('cache_id', $cache_id, (is_null($cache_id)) ? PDO::PARAM_NULL : PDO::PARAM_STR);
$stmt -> bindValue('compile_id', $compile_id, (is_null($compile_id)) ? PDO::PARAM_NULL : PDO::PARAM_STR);
$stmt -> bindValue('expire', (int) $exp_time, PDO::PARAM_INT);
$stmt -> bindValue('content', $this->inputContent($content));
$stmt -> execute();
return !!$stmt->rowCount();
}
/*
* Encodes the content before saving to database
*
* @param string $content
* @return string $content
* @access protected
*/
protected function inputContent($content) {
return $content;
}
/*
* Decodes the content before saving to database
*
* @param string $content
* @return string $content
* @access protected
*/
protected function outputContent($content) {
return $content;
}
/**
* Delete content from cache
*
* @param string|null $name template name
* @param string|null $cache_id cache id
* @param string|null $compile_id compile id
* @param integer|null|-1 $exp_time seconds till expiration or null
* @return integer number of deleted caches
* @access protected
*/
protected function delete($name = null, $cache_id = null, $compile_id = null, $exp_time = null) {
// Temporary bugfix for "string:[...]" templates - Issue #169
if (preg_match('/(^string:(.*)$)/', $name))
$name = substr($name, 7);
// delete the whole cache
if ($name === null && $cache_id === null && $compile_id === null && $exp_time === null) {
// returning the number of deleted caches would require a second query to count them
$this->pdo->query($this->truncateStatement);
return -1;
}
// build the filter
$where = array();
// equal test name
if ($name !== null) {
$where[] = 'name = ' . $this->pdo->quote($name);
}
// equal test cache_id and match sub-groups
if ($cache_id !== null) {
$where[] = '(cache_id = '. $this->pdo->quote($cache_id)
. ' OR cache_id LIKE '. $this->pdo->quote($cache_id .'|%') .')';
}
// equal test compile_id
if ($compile_id !== null) {
$where[] = 'compile_id = ' . $this->pdo->quote($compile_id);
}
// for clearing expired caches
if ($exp_time === Smarty::CLEAR_EXPIRED) {
$where[] = 'expire < CURRENT_TIMESTAMP';
}
// range test expiration time
elseif ($exp_time !== null) {
$where[] = 'modified < DATE_SUB(NOW(), INTERVAL ' . intval($exp_time) . ' SECOND)';
}
// run delete query
$query = $this->pdo->query(sprintf($this->deleteStatement, join(' AND ', $where)));
return $query->rowCount();
}
/**
* Gets the formatted table name
*
* @return string
* @access protected
*/
protected function getTableName() {
return (is_null($this->database)) ? "`{$this->table}`" : "`{$this->database}`.`{$this->table}`";
}
}
|
|
|
Back to top |
|
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|