1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
|
<?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->loadPlugin('Smarty_CacheResource_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)
{
// 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}`";
}
}
|
