1 <?php |
|
2 |
|
3 /** |
|
4 * PDO Cache Handler |
|
5 * Allows you to store Smarty Cache files into your db. |
|
6 * Example table : |
|
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, |
|
15 * PRIMARY KEY (`id`), |
|
16 * KEY `name` (`name`), |
|
17 * KEY `cache_id` (`cache_id`), |
|
18 * KEY `compile_id` (`compile_id`), |
|
19 * KEY `modified` (`modified`), |
|
20 * KEY `expire` (`expire`) |
|
21 * ) ENGINE=InnoDB |
|
22 * Example usage : |
|
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')); |
|
27 * |
|
28 * @author Beno!t POLASZEK - 2014 |
|
29 */ |
|
30 class Smarty_CacheResource_Pdo extends Smarty_CacheResource_Custom { |
|
31 |
|
32 protected $fetchStatements = Array('default' => 'SELECT %2$s |
|
33 FROM %1$s |
|
34 WHERE 1 |
|
35 AND id = :id |
|
36 AND cache_id IS NULL |
|
37 AND compile_id IS NULL', |
|
38 |
|
39 'withCacheId' => 'SELECT %2$s |
|
40 FROM %1$s |
|
41 WHERE 1 |
|
42 AND id = :id |
|
43 AND cache_id = :cache_id |
|
44 AND compile_id IS NULL', |
|
45 |
|
46 'withCompileId' => 'SELECT %2$s |
|
47 FROM %1$s |
|
48 WHERE 1 |
|
49 AND id = :id |
|
50 AND compile_id = :compile_id |
|
51 AND cache_id IS NULL', |
|
52 |
|
53 'withCacheIdAndCompileId' => 'SELECT %2$s |
|
54 FROM %1$s |
|
55 WHERE 1 |
|
56 AND id = :id |
|
57 AND cache_id = :cache_id |
|
58 AND compile_id = :compile_id'); |
|
59 protected $insertStatement = 'INSERT INTO %s |
|
60 |
|
61 SET id = :id, |
|
62 name = :name, |
|
63 cache_id = :cache_id, |
|
64 compile_id = :compile_id, |
|
65 modified = CURRENT_TIMESTAMP, |
|
66 expire = DATE_ADD(CURRENT_TIMESTAMP, INTERVAL :expire SECOND), |
|
67 content = :content |
|
68 |
|
69 ON DUPLICATE KEY UPDATE |
|
70 name = :name, |
|
71 cache_id = :cache_id, |
|
72 compile_id = :compile_id, |
|
73 modified = CURRENT_TIMESTAMP, |
|
74 expire = DATE_ADD(CURRENT_TIMESTAMP, INTERVAL :expire SECOND), |
|
75 content = :content'; |
|
76 |
|
77 protected $deleteStatement = 'DELETE FROM %1$s WHERE %2$s'; |
|
78 protected $truncateStatement = 'TRUNCATE TABLE %s'; |
|
79 |
|
80 protected $fetchColumns = 'modified, content'; |
|
81 protected $fetchTimestampColumns = 'modified'; |
|
82 |
|
83 protected $pdo, $table, $database; |
|
84 |
|
85 /* |
|
86 * Constructor |
|
87 * |
|
88 * @param PDO $pdo PDO : active connection |
|
89 * @param string $table : table (or view) name |
|
90 * @param string $database : optionnal - if table is located in another db |
|
91 */ |
|
92 public function __construct(PDO $pdo, $table, $database = null) { |
|
93 |
|
94 if (is_null($table)) { |
|
95 throw new SmartyException("Table name for caching can't be null"); |
|
96 } |
|
97 |
|
98 $this->pdo = $pdo; |
|
99 $this->table = $table; |
|
100 $this->database = $database; |
|
101 |
|
102 $this->fillStatementsWithTableName(); |
|
103 } |
|
104 |
|
105 /* |
|
106 * Fills the table name into the statements. |
|
107 * |
|
108 * @return Current Instance |
|
109 * @access protected |
|
110 */ |
|
111 protected function fillStatementsWithTableName() { |
|
112 |
|
113 foreach ($this->fetchStatements AS &$statement) { |
|
114 $statement = sprintf($statement, $this->getTableName(), '%s'); |
|
115 } |
|
116 |
|
117 $this->insertStatement = sprintf($this->insertStatement, $this->getTableName()); |
|
118 $this->deleteStatement = sprintf($this->deleteStatement, $this->getTableName(), '%s'); |
|
119 $this->truncateStatement = sprintf($this->truncateStatement, $this->getTableName()); |
|
120 |
|
121 return $this; |
|
122 } |
|
123 |
|
124 /* |
|
125 * Gets the fetch statement, depending on what you specify |
|
126 * |
|
127 * @param string $columns : the column(s) name(s) you want to retrieve from the database |
|
128 * @param string $id unique cache content identifier |
|
129 * @param string|null $cache_id cache id |
|
130 * @param string|null $compile_id compile id |
|
131 * @access protected |
|
132 */ |
|
133 protected function getFetchStatement($columns, $id, $cache_id = null, $compile_id = null) { |
|
134 |
|
135 if (!is_null($cache_id) && !is_null($compile_id)) { |
|
136 $query = $this->fetchStatements['withCacheIdAndCompileId'] AND $args = Array('id' => $id, 'cache_id' => $cache_id, 'compile_id' => $compile_id); |
|
137 } elseif (is_null($cache_id) && !is_null($compile_id)) { |
|
138 $query = $this->fetchStatements['withCompileId'] AND $args = Array('id' => $id, 'compile_id' => $compile_id); |
|
139 } elseif (!is_null($cache_id) && is_null($compile_id)) { |
|
140 $query = $this->fetchStatements['withCacheId'] AND $args = Array('id' => $id, 'cache_id' => $cache_id); |
|
141 } else { |
|
142 $query = $this->fetchStatements['default'] AND $args = Array('id' => $id); |
|
143 } |
|
144 |
|
145 $query = sprintf($query, $columns); |
|
146 |
|
147 $stmt = $this->pdo->prepare($query); |
|
148 |
|
149 foreach ($args AS $key => $value) { |
|
150 $stmt->bindValue($key, $value); |
|
151 } |
|
152 |
|
153 return $stmt; |
|
154 } |
|
155 |
|
156 /** |
|
157 * fetch cached content and its modification time from data source |
|
158 * |
|
159 * @param string $id unique cache content identifier |
|
160 * @param string $name template name |
|
161 * @param string|null $cache_id cache id |
|
162 * @param string|null $compile_id compile id |
|
163 * @param string $content cached content |
|
164 * @param integer $mtime cache modification timestamp (epoch) |
|
165 * |
|
166 * @return void |
|
167 * @access protected |
|
168 */ |
|
169 protected function fetch($id, $name, $cache_id = null, $compile_id = null, &$content, &$mtime) { |
|
170 |
|
171 $stmt = $this->getFetchStatement($this->fetchColumns, $id, $cache_id, $compile_id); |
|
172 $stmt->execute(); |
|
173 $row = $stmt->fetch(); |
|
174 $stmt->closeCursor(); |
|
175 |
|
176 if ($row) { |
|
177 $content = $this->outputContent($row['content']); |
|
178 $mtime = strtotime($row['modified']); |
|
179 } else { |
|
180 $content = null; |
|
181 $mtime = null; |
|
182 } |
|
183 } |
|
184 |
|
185 /** |
|
186 * Fetch cached content's modification timestamp from data source |
|
187 * {@internal implementing this method is optional. |
|
188 * Only implement it if modification times can be accessed faster than loading the complete cached content.}} |
|
189 * |
|
190 * @param string $id unique cache content identifier |
|
191 * @param string $name template name |
|
192 * @param string|null $cache_id cache id |
|
193 * @param string|null $compile_id compile id |
|
194 * |
|
195 * @return integer|boolean timestamp (epoch) the template was modified, or false if not found |
|
196 * @access protected |
|
197 */ |
|
198 // protected function fetchTimestamp($id, $name, $cache_id = null, $compile_id = null) { |
|
199 // $stmt = $this->getFetchStatement($this->fetchTimestampColumns, $id, $cache_id, $compile_id); |
|
200 // $stmt -> execute(); |
|
201 // $mtime = strtotime($stmt->fetchColumn()); |
|
202 // $stmt -> closeCursor(); |
|
203 // return $mtime; |
|
204 // } |
|
205 |
|
206 /** |
|
207 * Save content to cache |
|
208 * |
|
209 * @param string $id unique cache content identifier |
|
210 * @param string $name template name |
|
211 * @param string|null $cache_id cache id |
|
212 * @param string|null $compile_id compile id |
|
213 * @param integer|null $exp_time seconds till expiration time in seconds or null |
|
214 * @param string $content content to cache |
|
215 * |
|
216 * @return boolean success |
|
217 * @access protected |
|
218 */ |
|
219 protected function save($id, $name, $cache_id = null, $compile_id = null, $exp_time, $content) { |
|
220 |
|
221 $stmt = $this->pdo->prepare($this->insertStatement); |
|
222 |
|
223 $stmt->bindValue('id', $id); |
|
224 $stmt->bindValue('name', $name); |
|
225 $stmt->bindValue('cache_id', $cache_id, (is_null($cache_id)) ? PDO::PARAM_NULL : PDO::PARAM_STR); |
|
226 $stmt->bindValue('compile_id', $compile_id, (is_null($compile_id)) ? PDO::PARAM_NULL : PDO::PARAM_STR); |
|
227 $stmt->bindValue('expire', (int)$exp_time, PDO::PARAM_INT); |
|
228 $stmt->bindValue('content', $this->inputContent($content)); |
|
229 $stmt->execute(); |
|
230 |
|
231 return !!$stmt->rowCount(); |
|
232 } |
|
233 |
|
234 /* |
|
235 * Encodes the content before saving to database |
|
236 * |
|
237 * @param string $content |
|
238 * @return string $content |
|
239 * @access protected |
|
240 */ |
|
241 protected function inputContent($content) { |
|
242 return $content; |
|
243 } |
|
244 |
|
245 /* |
|
246 * Decodes the content before saving to database |
|
247 * |
|
248 * @param string $content |
|
249 * @return string $content |
|
250 * @access protected |
|
251 */ |
|
252 protected function outputContent($content) { |
|
253 return $content; |
|
254 } |
|
255 |
|
256 /** |
|
257 * Delete content from cache |
|
258 * |
|
259 * @param string|null $name template name |
|
260 * @param string|null $cache_id cache id |
|
261 * @param string|null $compile_id compile id |
|
262 * @param integer|null|-1 $exp_time seconds till expiration or null |
|
263 * |
|
264 * @return integer number of deleted caches |
|
265 * @access protected |
|
266 */ |
|
267 protected function delete($name = null, $cache_id = null, $compile_id = null, $exp_time = null) { |
|
268 |
|
269 // delete the whole cache |
|
270 if ($name === null && $cache_id === null && $compile_id === null && $exp_time === null) { |
|
271 // returning the number of deleted caches would require a second query to count them |
|
272 $this->pdo->query($this->truncateStatement); |
|
273 return -1; |
|
274 } |
|
275 // build the filter |
|
276 $where = array(); |
|
277 // equal test name |
|
278 if ($name !== null) { |
|
279 $where[] = 'name = ' . $this->pdo->quote($name); |
|
280 } |
|
281 // equal test cache_id and match sub-groups |
|
282 if ($cache_id !== null) { |
|
283 $where[] = '(cache_id = ' . $this->pdo->quote($cache_id) |
|
284 . ' OR cache_id LIKE ' . $this->pdo->quote($cache_id . '|%') . ')'; |
|
285 } |
|
286 // equal test compile_id |
|
287 if ($compile_id !== null) { |
|
288 $where[] = 'compile_id = ' . $this->pdo->quote($compile_id); |
|
289 } |
|
290 // for clearing expired caches |
|
291 if ($exp_time === Smarty::CLEAR_EXPIRED) { |
|
292 $where[] = 'expire < CURRENT_TIMESTAMP'; |
|
293 } // range test expiration time |
|
294 elseif ($exp_time !== null) { |
|
295 $where[] = 'modified < DATE_SUB(NOW(), INTERVAL ' . intval($exp_time) . ' SECOND)'; |
|
296 } |
|
297 // run delete query |
|
298 $query = $this->pdo->query(sprintf($this->deleteStatement, join(' AND ', $where))); |
|
299 return $query->rowCount(); |
|
300 } |
|
301 |
|
302 /** |
|
303 * Gets the formatted table name |
|
304 * |
|
305 * @return string |
|
306 * @access protected |
|
307 */ |
|
308 protected function getTableName() { |
|
309 return (is_null($this->database)) ? "`{$this->table}`" : "`{$this->database}`.`{$this->table}`"; |
|
310 } |
|
311 } |
|
312 |
|