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 // +----------------------------------------------------------------------+
22 require_once 'DB.php';
23 require_once 'Cache/Container.php';
26 * PEAR/DB Cache Container.
28 * WARNING: Other systems might or might not support certain datatypes of
29 * the tables shown. As far as I know there's no large binary
30 * type in SQL-92 or SQL-99. Postgres seems to lack any
31 * BLOB or TEXT type, for MS-SQL you could use IMAGE, don't know
32 * about other databases. Please add sugestions for other databases to
35 * The field 'changed' has no meaning for the Cache itself. It's just there
36 * because it's a good idea to have an automatically updated timestamp
37 * field for debugging in all of your tables.
39 * For _MySQL_ you need this DB table:
41 * CREATE TABLE cache (
42 * id CHAR(32) NOT NULL DEFAULT '',
43 * cachegroup VARCHAR(127) NOT NULL DEFAULT '',
44 * cachedata BLOB NOT NULL DEFAULT '',
45 * userdata VARCHAR(255) NOT NULL DEFAUL '',
46 * expires INT(9) NOT NULL DEFAULT 0,
48 * changed TIMESTAMP(14) NOT NULL,
51 * PRIMARY KEY (id, cachegroup)
54 * @author Sebastian Bergmann <sb@sebastian-bergmann.de>
58 class Cache_Container_db extends Cache_Container {
61 * Name of the DB table to store caching data
63 * @see Cache_Container_file::$filename_prefix
65 var $cache_table = '';
81 function Cache_Container_db($options)
83 if (!is_array($options) || !isset($options['dsn'])) {
84 return new Cache_Error('No dsn specified!', __FILE__, __LINE__);
87 $this->setOptions($options, array_merge($this->allowed_options, array('dsn', 'cache_table')));
90 return new Cache_Error('No dsn specified!', __FILE__, __LINE__);
92 $this->db = DB::connect($this->dsn, true);
93 if (DB::isError($this->db)) {
94 return new Cache_Error('DB::connect failed: ' . DB::errorMessage($this->db), __FILE__, __LINE__);
96 $this->db->setFetchMode(DB_FETCHMODE_ASSOC);
100 function fetch($id, $group)
102 $query = sprintf("SELECT cachedata, userdata, expires FROM %s WHERE id = '%s' AND cachegroup = '%s'",
108 $res = $this->db->query($query);
110 if (DB::isError($res))
111 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
113 $row = $res->fetchRow();
115 $data = array($row['expires'], $this->decode($row['cachedata']), $row['userdata']);
117 $data = array(NULL, NULL, NULL);
119 // last used required by the garbage collection
120 // WARNING: might be MySQL specific
121 $query = sprintf("UPDATE %s SET changed = (NOW() + 0) WHERE id = '%s' AND cachegroup = '%s'",
127 $res = $this->db->query($query);
129 if (DB::isError($res))
130 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
138 * WARNING: we use the SQL command REPLACE INTO this might be
139 * MySQL specific. As MySQL is very popular the method should
140 * work fine for 95% of you.
142 function save($id, $data, $expires, $group, $userdata)
144 $this->flushPreload($id, $group);
146 $query = sprintf("REPLACE INTO %s (userdata, cachedata, expires, id, cachegroup) VALUES ('%s', '%s', %d, '%s', '%s')",
148 addslashes($userdata),
149 addslashes($this->encode($data)),
150 $this->getExpiresAbsolute($expires) ,
155 $res = $this->db->query($query);
157 if (DB::isError($res)) {
158 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res) , __FILE__, __LINE__);
162 function remove($id, $group)
164 $this->flushPreload($id, $group);
166 $query = sprintf("DELETE FROM %s WHERE id = '%s' and cachegroup = '%s'",
172 $res = $this->db->query($query);
174 if (DB::isError($res))
175 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
178 function flush($group = '')
180 $this->flushPreload();
183 $query = sprintf("DELETE FROM %s WHERE cachegroup = '%s'", $this->cache_table, addslashes($group));
185 $query = sprintf("DELETE FROM %s", $this->cache_table);
188 $res = $this->db->query($query);
190 if (DB::isError($res))
191 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
194 function idExists($id, $group)
196 $query = sprintf("SELECT id FROM %s WHERE ID = '%s' AND cachegroup = '%s'",
202 $res = $this->db->query($query);
204 if (DB::isError($res))
205 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
207 $row = $res->fetchRow();
209 if (is_array($row)) {
216 function garbageCollection($maxlifetime)
218 $this->flushPreload();
220 $query = sprintf('DELETE FROM %s WHERE (expires <= %d AND expires > 0) OR changed <= %d',
223 time() - $maxlifetime
226 $res = $this->db->query($query);
228 $query = sprintf('select sum(length(cachedata)) as CacheSize from %s',
231 $cachesize = $this->db->GetOne($query);
232 if (DB::isError($cachesize)) {
233 return new Cache_Error('DB::query failed: ' . DB::errorMessage($cachesize), __FILE__, __LINE__);
236 //if cache is to big.
237 if ($cachesize > $this->highwater)
239 //find the lowwater mark.
240 $query = sprintf('select length(cachedata) as size, changed from %s order by changed DESC',
243 $res = $this->db->query($query);
244 if (DB::isError($res)) {
245 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);
248 $numrows = $this->db->numRows($res);
250 while ($keep_size < $this->lowwater && $numrows--) {
251 $entry = $res->fetchRow(DB_FETCHMODE_ASSOC);
252 $keep_size += $entry['size'];
255 //delete all entries, which were changed before the "lowwwater mark"
256 $query = sprintf('delete from %s where changed <= '.($entry['changed'] ? $entry['changed'] : 0),
259 $res = $this->db->query($query);
262 if (DB::isError($res)) {
263 return new Cache_Error('DB::query failed: ' . DB::errorMessage($res), __FILE__, __LINE__);