2 // +----------------------------------------------------------------------+
4 // +----------------------------------------------------------------------+
5 // | Copyright (c) 1997-2003 The PHP Group |
6 // +----------------------------------------------------------------------+
7 // | This source file is subject to version 2.0 of the PHP license, |
8 // | that is bundled with this package in the file LICENSE, and is |
9 // | available at through the world-wide-web at |
10 // | http://www.php.net/license/2_02.txt. |
11 // | If you did not receive a copy of the PHP license and are unable to |
12 // | obtain it through the world-wide-web, please send a note to |
13 // | license@php.net so we can mail you a copy immediately. |
14 // +----------------------------------------------------------------------+
15 // | Authors: Ulf Wendel <ulf.wendel@phpdoc.de> |
16 // | Sebastian Bergmann <sb@sebastian-bergmann.de> |
17 // | Chuck Hagenbuch <chuck@horde.org> |
18 // +----------------------------------------------------------------------+
20 require_once 'DB.php';
21 require_once 'Cache/Container.php';
24 * PEAR/DB Cache Container.
26 * WARNING: Other systems might or might not support certain datatypes of
27 * the tables shown. As far as I know there's no large binary
28 * type in SQL-92 or SQL-99. Postgres seems to lack any
29 * BLOB or TEXT type, for MS-SQL you could use IMAGE, don't know
30 * about other databases. Please add sugestions for other databases to
33 * The field 'changed' has no meaning for the Cache itself. It's just there
34 * because it's a good idea to have an automatically updated timestamp
35 * field for debugging in all of your tables.
37 * For _MySQL_ you need this DB table:
39 * CREATE TABLE cache (
40 * id CHAR(32) NOT NULL DEFAULT '',
41 * cachegroup VARCHAR(127) NOT NULL DEFAULT '',
42 * cachedata BLOB NOT NULL DEFAULT '',
43 * userdata VARCHAR(255) NOT NULL DEFAUL '',
44 * expires INT(9) NOT NULL DEFAULT 0,
46 * changed TIMESTAMP(14) NOT NULL,
49 * PRIMARY KEY (id, cachegroup)
52 * @author Sebastian Bergmann <sb@sebastian-bergmann.de>
56 class Cache_Container_db extends Cache_Container {
59 * Name of the DB table to store caching data
61 * @see Cache_Container_file::$filename_prefix
63 var $cache_table = '';
79 function Cache_Container_db($options)
81 if (!is_array($options) || !isset($options['dsn'])) {
82 return new Cache_Error('No dsn specified!', __FILE__, __LINE__);
85 $this->setOptions($options, array_merge($this->allowed_options, array('dsn', 'cache_table')));
88 return new Cache_Error('No dsn specified!', __FILE__, __LINE__);
90 $this->db = DB::connect($this->dsn, true);
91 if (DB::isError($this->db)) {
92 return new Cache_Error('DB::connect failed: ' . DB::errorMessage($this->db), __FILE__, __LINE__);
94 $this->db->setFetchMode(DB_FETCHMODE_ASSOC);
98 function fetch($id, $group)
100 $query = sprintf("SELECT cachedata, userdata, expires FROM %s WHERE id = '%s' AND cachegroup = '%s'",
106 $res = $this->db->query($query);
108 if (DB::isError($res))
109 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
111 $row = $res->fetchRow();
113 $data = array($row['expires'], $this->decode($row['cachedata']), $row['userdata']);
115 $data = array(NULL, NULL, NULL);
117 // last used required by the garbage collection
118 // WARNING: might be MySQL specific
119 $query = sprintf("UPDATE %s SET changed = (NOW() + 0) WHERE id = '%s' AND cachegroup = '%s'",
125 $res = $this->db->query($query);
127 if (DB::isError($res))
128 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
136 * WARNING: we use the SQL command REPLACE INTO this might be
137 * MySQL specific. As MySQL is very popular the method should
138 * work fine for 95% of you.
140 function save($id, $data, $expires, $group, $userdata)
142 $this->flushPreload($id, $group);
144 $query = sprintf("REPLACE INTO %s (userdata, cachedata, expires, id, cachegroup) VALUES ('%s', '%s', %d, '%s', '%s')",
146 addslashes($userdata),
147 addslashes($this->encode($data)),
148 $this->getExpiresAbsolute($expires) ,
153 $res = $this->db->query($query);
155 if (DB::isError($res)) {
156 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res) , __FILE__, __LINE__);
160 function remove($id, $group)
162 $this->flushPreload($id, $group);
164 $query = sprintf("DELETE FROM %s WHERE id = '%s' and cachegroup = '%s'",
170 $res = $this->db->query($query);
172 if (DB::isError($res))
173 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
176 function flush($group = '')
178 $this->flushPreload();
181 $query = sprintf("DELETE FROM %s WHERE cachegroup = '%s'", $this->cache_table, addslashes($group));
183 $query = sprintf("DELETE FROM %s", $this->cache_table);
186 $res = $this->db->query($query);
188 if (DB::isError($res))
189 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
192 function idExists($id, $group)
194 $query = sprintf("SELECT id FROM %s WHERE ID = '%s' AND cachegroup = '%s'",
200 $res = $this->db->query($query);
202 if (DB::isError($res))
203 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
205 $row = $res->fetchRow();
207 if (is_array($row)) {
214 function garbageCollection($maxlifetime)
216 $this->flushPreload();
218 $query = sprintf('DELETE FROM %s WHERE (expires <= %d AND expires > 0) OR changed <= %d',
221 time() - $maxlifetime
224 $res = $this->db->query($query);
226 $query = sprintf('select sum(length(cachedata)) as CacheSize from %s',
229 $cachesize = $this->db->GetOne($query);
230 if (DB::isError($cachesize)) {
231 return new Cache_Error('DB::query failed: ' . DB::errorMessage($cachesize), __FILE__, __LINE__);
234 //if cache is to big.
235 if ($cachesize > $this->highwater)
237 //find the lowwater mark.
238 $query = sprintf('select length(cachedata) as size, changed from %s order by changed DESC',
241 $res = $this->db->query($query);
242 if (DB::isError($res)) {
243 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
246 $numrows = $this->db->numRows($res);
248 while ($keep_size < $this->lowwater && $numrows--) {
249 $entry = $res->fetchRow(DB_FETCHMODE_ASSOC);
250 $keep_size += $entry['size'];
253 //delete all entries, which were changed before the "lowwwater mark"
254 $query = sprintf('delete from %s where changed <= '.($entry['changed'] ? $entry['changed'] : 0),
257 $res = $this->db->query($query);
260 if (DB::isError($res)) {
261 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);