5 * CacheResource Implementation based on the Custom API to use
6 * MySQL as the storage resource for Smarty's output caching.
8 * <pre>CREATE TABLE IF NOT EXISTS `output_cache` (
9 * `id` CHAR(40) NOT NULL COMMENT 'sha1 hash',
10 * `name` VARCHAR(250) NOT NULL,
11 * `cache_id` VARCHAR(250) NULL DEFAULT NULL,
12 * `compile_id` VARCHAR(250) NULL DEFAULT NULL,
13 * `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
14 * `content` LONGTEXT NOT NULL,
18 * INDEX(`compile_id`),
20 * ) ENGINE = InnoDB;</pre>
22 * @package CacheResource-examples
25 class Smarty_CacheResource_Mysql extends Smarty_CacheResource_Custom
30 protected $fetchTimestamp;
33 public function __construct()
36 $this->db = new PDO("mysql:dbname=test;host=127.0.0.1", "smarty");
38 catch (PDOException $e) {
39 throw new SmartyException('Mysql Resource failed: ' . $e->getMessage());
41 $this->fetch = $this->db->prepare('SELECT modified, content FROM output_cache WHERE id = :id');
42 $this->fetchTimestamp = $this->db->prepare('SELECT modified FROM output_cache WHERE id = :id');
43 $this->save = $this->db->prepare('REPLACE INTO output_cache (id, name, cache_id, compile_id, content)
44 VALUES (:id, :name, :cache_id, :compile_id, :content)');
48 * fetch cached content and its modification time from data source
50 * @param string $id unique cache content identifier
51 * @param string $name template name
52 * @param string $cache_id cache id
53 * @param string $compile_id compile id
54 * @param string $content cached content
55 * @param integer $mtime cache modification timestamp (epoch)
59 protected function fetch($id, $name, $cache_id, $compile_id, &$content, &$mtime)
61 $this->fetch->execute(array('id' => $id));
62 $row = $this->fetch->fetch();
63 $this->fetch->closeCursor();
65 $content = $row['content'];
66 $mtime = strtotime($row['modified']);
74 * Fetch cached content's modification timestamp from data source
76 * @note implementing this method is optional. Only implement it if modification times can be accessed faster than loading the complete cached content.
78 * @param string $id unique cache content identifier
79 * @param string $name template name
80 * @param string $cache_id cache id
81 * @param string $compile_id compile id
83 * @return integer|boolean timestamp (epoch) the template was modified, or false if not found
85 protected function fetchTimestamp($id, $name, $cache_id, $compile_id)
87 $this->fetchTimestamp->execute(array('id' => $id));
88 $mtime = strtotime($this->fetchTimestamp->fetchColumn());
89 $this->fetchTimestamp->closeCursor();
95 * Save content to cache
97 * @param string $id unique cache content identifier
98 * @param string $name template name
99 * @param string $cache_id cache id
100 * @param string $compile_id compile id
101 * @param integer|null $exp_time seconds till expiration time in seconds or null
102 * @param string $content content to cache
104 * @return boolean success
106 protected function save($id, $name, $cache_id, $compile_id, $exp_time, $content)
108 $this->save->execute(array(
111 'cache_id' => $cache_id,
112 'compile_id' => $compile_id,
113 'content' => $content,
116 return !!$this->save->rowCount();
120 * Delete content from cache
122 * @param string $name template name
123 * @param string $cache_id cache id
124 * @param string $compile_id compile id
125 * @param integer|null $exp_time seconds till expiration or null
127 * @return integer number of deleted caches
129 protected function delete($name, $cache_id, $compile_id, $exp_time)
131 // delete the whole cache
132 if ($name === null && $cache_id === null && $compile_id === null && $exp_time === null) {
133 // returning the number of deleted caches would require a second query to count them
134 $query = $this->db->query('TRUNCATE TABLE output_cache');
141 if ($name !== null) {
142 $where[] = 'name = ' . $this->db->quote($name);
144 // equal test compile_id
145 if ($compile_id !== null) {
146 $where[] = 'compile_id = ' . $this->db->quote($compile_id);
148 // range test expiration time
149 if ($exp_time !== null) {
150 $where[] = 'modified < DATE_SUB(NOW(), INTERVAL ' . intval($exp_time) . ' SECOND)';
152 // equal test cache_id and match sub-groups
153 if ($cache_id !== null) {
154 $where[] = '(cache_id = ' . $this->db->quote($cache_id)
155 . ' OR cache_id LIKE ' . $this->db->quote($cache_id . '|%') . ')';
158 $query = $this->db->query('DELETE FROM output_cache WHERE ' . join(' AND ', $where));
160 return $query->rowCount();