5 * Allows you to store Smarty Cache files into your db.
7 * CREATE TABLE `smarty_cache` (
8 * `id` char(40) NOT NULL COMMENT 'sha1 hash',
9 * `name` varchar(250) NOT NULL,
10 * `cache_id` varchar(250) DEFAULT NULL,
11 * `compile_id` varchar(250) DEFAULT NULL,
12 * `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
13 * `expire` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
14 * `content` mediumblob NOT NULL,
16 * KEY `name` (`name`),
17 * KEY `cache_id` (`cache_id`),
18 * KEY `compile_id` (`compile_id`),
19 * KEY `modified` (`modified`),
20 * KEY `expire` (`expire`)
23 * $cnx = new PDO("mysql:host=localhost;dbname=mydb", "username", "password");
24 * $smarty->setCachingType('pdo');
25 * $smarty->loadPlugin('Smarty_CacheResource_Pdo');
26 * $smarty->registerCacheResource('pdo', new Smarty_CacheResource_Pdo($cnx, 'smarty_cache'));
28 * @author Beno!t POLASZEK - 2014
30 class Smarty_CacheResource_Pdo extends Smarty_CacheResource_Custom
33 protected $fetchStatements = Array('default' => 'SELECT %2$s
38 AND compile_id IS NULL',
40 'withCacheId' => 'SELECT %2$s
44 AND cache_id = :cache_id
45 AND compile_id IS NULL',
47 'withCompileId' => 'SELECT %2$s
51 AND compile_id = :compile_id
52 AND cache_id IS NULL',
54 'withCacheIdAndCompileId' => 'SELECT %2$s
58 AND cache_id = :cache_id
59 AND compile_id = :compile_id');
61 protected $insertStatement = 'INSERT INTO %s
66 compile_id = :compile_id,
67 modified = CURRENT_TIMESTAMP,
68 expire = DATE_ADD(CURRENT_TIMESTAMP, INTERVAL :expire SECOND),
71 ON DUPLICATE KEY UPDATE
74 compile_id = :compile_id,
75 modified = CURRENT_TIMESTAMP,
76 expire = DATE_ADD(CURRENT_TIMESTAMP, INTERVAL :expire SECOND),
79 protected $deleteStatement = 'DELETE FROM %1$s WHERE %2$s';
81 protected $truncateStatement = 'TRUNCATE TABLE %s';
83 protected $fetchColumns = 'modified, content';
85 protected $fetchTimestampColumns = 'modified';
87 protected $pdo, $table, $database;
92 * @param PDO $pdo PDO : active connection
93 * @param string $table : table (or view) name
94 * @param string $database : optionnal - if table is located in another db
96 public function __construct(PDO $pdo, $table, $database = null)
99 if (is_null($table)) {
100 throw new SmartyException("Table name for caching can't be null");
104 $this->table = $table;
105 $this->database = $database;
107 $this->fillStatementsWithTableName();
111 * Fills the table name into the statements.
113 * @return Current Instance
116 protected function fillStatementsWithTableName()
119 foreach ($this->fetchStatements AS &$statement) {
120 $statement = sprintf($statement, $this->getTableName(), '%s');
123 $this->insertStatement = sprintf($this->insertStatement, $this->getTableName());
124 $this->deleteStatement = sprintf($this->deleteStatement, $this->getTableName(), '%s');
125 $this->truncateStatement = sprintf($this->truncateStatement, $this->getTableName());
131 * Gets the fetch statement, depending on what you specify
133 * @param string $columns : the column(s) name(s) you want to retrieve from the database
134 * @param string $id unique cache content identifier
135 * @param string|null $cache_id cache id
136 * @param string|null $compile_id compile id
139 protected function getFetchStatement($columns, $id, $cache_id = null, $compile_id = null)
142 if (!is_null($cache_id) && !is_null($compile_id)) {
143 $query = $this->fetchStatements[ 'withCacheIdAndCompileId' ] AND
144 $args = Array('id' => $id, 'cache_id' => $cache_id, 'compile_id' => $compile_id);
145 } elseif (is_null($cache_id) && !is_null($compile_id)) {
146 $query = $this->fetchStatements[ 'withCompileId' ] AND
147 $args = Array('id' => $id, 'compile_id' => $compile_id);
148 } elseif (!is_null($cache_id) && is_null($compile_id)) {
149 $query = $this->fetchStatements[ 'withCacheId' ] AND $args = Array('id' => $id, 'cache_id' => $cache_id);
151 $query = $this->fetchStatements[ 'default' ] AND $args = Array('id' => $id);
154 $query = sprintf($query, $columns);
156 $stmt = $this->pdo->prepare($query);
158 foreach ($args AS $key => $value) {
159 $stmt->bindValue($key, $value);
166 * fetch cached content and its modification time from data source
168 * @param string $id unique cache content identifier
169 * @param string $name template name
170 * @param string|null $cache_id cache id
171 * @param string|null $compile_id compile id
172 * @param string $content cached content
173 * @param integer $mtime cache modification timestamp (epoch)
178 protected function fetch($id, $name, $cache_id = null, $compile_id = null, &$content, &$mtime)
181 $stmt = $this->getFetchStatement($this->fetchColumns, $id, $cache_id, $compile_id);
183 $row = $stmt->fetch();
184 $stmt->closeCursor();
187 $content = $this->outputContent($row[ 'content' ]);
188 $mtime = strtotime($row[ 'modified' ]);
196 * Fetch cached content's modification timestamp from data source
197 * {@internal implementing this method is optional.
198 * Only implement it if modification times can be accessed faster than loading the complete cached content.}}
200 * @param string $id unique cache content identifier
201 * @param string $name template name
202 * @param string|null $cache_id cache id
203 * @param string|null $compile_id compile id
205 * @return integer|boolean timestamp (epoch) the template was modified, or false if not found
208 // protected function fetchTimestamp($id, $name, $cache_id = null, $compile_id = null) {
209 // $stmt = $this->getFetchStatement($this->fetchTimestampColumns, $id, $cache_id, $compile_id);
210 // $stmt -> execute();
211 // $mtime = strtotime($stmt->fetchColumn());
212 // $stmt -> closeCursor();
217 * Save content to cache
219 * @param string $id unique cache content identifier
220 * @param string $name template name
221 * @param string|null $cache_id cache id
222 * @param string|null $compile_id compile id
223 * @param integer|null $exp_time seconds till expiration time in seconds or null
224 * @param string $content content to cache
226 * @return boolean success
229 protected function save($id, $name, $cache_id = null, $compile_id = null, $exp_time, $content)
232 $stmt = $this->pdo->prepare($this->insertStatement);
234 $stmt->bindValue('id', $id);
235 $stmt->bindValue('name', $name);
236 $stmt->bindValue('cache_id', $cache_id, (is_null($cache_id)) ? PDO::PARAM_NULL : PDO::PARAM_STR);
237 $stmt->bindValue('compile_id', $compile_id, (is_null($compile_id)) ? PDO::PARAM_NULL : PDO::PARAM_STR);
238 $stmt->bindValue('expire', (int) $exp_time, PDO::PARAM_INT);
239 $stmt->bindValue('content', $this->inputContent($content));
242 return !!$stmt->rowCount();
246 * Encodes the content before saving to database
248 * @param string $content
249 * @return string $content
252 protected function inputContent($content)
258 * Decodes the content before saving to database
260 * @param string $content
261 * @return string $content
264 protected function outputContent($content)
270 * Delete content from cache
272 * @param string|null $name template name
273 * @param string|null $cache_id cache id
274 * @param string|null $compile_id compile id
275 * @param integer|null|-1 $exp_time seconds till expiration or null
277 * @return integer number of deleted caches
280 protected function delete($name = null, $cache_id = null, $compile_id = null, $exp_time = null)
283 // delete the whole cache
284 if ($name === null && $cache_id === null && $compile_id === null && $exp_time === null) {
285 // returning the number of deleted caches would require a second query to count them
286 $this->pdo->query($this->truncateStatement);
292 if ($name !== null) {
293 $where[] = 'name = ' . $this->pdo->quote($name);
295 // equal test cache_id and match sub-groups
296 if ($cache_id !== null) {
297 $where[] = '(cache_id = ' . $this->pdo->quote($cache_id) . ' OR cache_id LIKE ' .
298 $this->pdo->quote($cache_id . '|%') . ')';
300 // equal test compile_id
301 if ($compile_id !== null) {
302 $where[] = 'compile_id = ' . $this->pdo->quote($compile_id);
304 // for clearing expired caches
305 if ($exp_time === Smarty::CLEAR_EXPIRED) {
306 $where[] = 'expire < CURRENT_TIMESTAMP';
307 } // range test expiration time
308 elseif ($exp_time !== null) {
309 $where[] = 'modified < DATE_SUB(NOW(), INTERVAL ' . intval($exp_time) . ' SECOND)';
312 $query = $this->pdo->query(sprintf($this->deleteStatement, join(' AND ', $where)));
313 return $query->rowCount();
317 * Gets the formatted table name
322 protected function getTableName()
324 return (is_null($this->database)) ? "`{$this->table}`" : "`{$this->database}`.`{$this->table}`";