4 +-----------------------------------------------------------------------+
5 | program/include/rcube_db.inc |
7 | This file is part of the RoundCube Webmail client |
8 | Copyright (C) 2005, RoundCube Dev. - Switzerland |
9 | Licensed under the GNU GPL |
12 | PEAR:DB wrapper class that implements PEAR DB functions |
13 | See http://pear.php.net/package/DB |
15 +-----------------------------------------------------------------------+
16 | Author: David Saez Padros <david@ols.es> |
17 | Thomas Bruederli <roundcube@gmail.com> |
18 +-----------------------------------------------------------------------+
20 $Id: rcube_db.inc 262 2006-06-25 09:17:07Z thomasb $
26 * Obtain the PEAR::DB class that is used for abstraction
28 require_once('DB.php');
32 * Database independent query interface
34 * This is a wrapper for the PEAR::DB class
36 * @package RoundCube Webmail
37 * @author David Saez Padros <david@ols.es>
38 * @author Thomas Bruederli <roundcube@gmail.com>
40 * @link http://pear.php.net/package/DB
44 var $db_dsnw; // DSN for write operations
45 var $db_dsnr; // DSN for read operations
46 var $db_connected = false; // Already connected ?
47 var $db_mode = ''; // Connection mode
48 var $db_handle = 0; // Connection handle
49 var $db_pconn = false; // Use persistent connections
50 var $db_error = false;
51 var $db_error_msg = '';
53 var $a_query_results = array('dummy');
60 * @param string DSN for read/write operations
61 * @param string Optional DSN for read only operations
63 function __construct($db_dsnw, $db_dsnr='', $pconn=false)
68 $this->db_dsnw = $db_dsnw;
69 $this->db_dsnr = $db_dsnr;
70 $this->db_pconn = $pconn;
72 $dsn_array = DB::parseDSN($db_dsnw);
73 $this->db_provider = $dsn_array['phptype'];
78 * PHP 4 object constructor
80 * @see rcube_db::__construct
82 function rcube_db($db_dsnw, $db_dsnr='', $pconn=false)
84 $this->__construct($db_dsnw, $db_dsnr, $pconn);
89 * Connect to specific database
91 * @param string DSN for DB connections
92 * @return object PEAR database handle
95 function dsn_connect($dsn)
97 // Use persistent connections if available
98 $dbh = DB::connect($dsn, array('persistent' => $this->db_pconn));
100 if (DB::isError($dbh))
102 $this->db_error = TRUE;
103 $this->db_error_msg = $dbh->getMessage();
105 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
106 'message' => $this->db_error_msg), TRUE, FALSE);
111 else if ($this->db_provider=='sqlite')
113 $dsn_array = DB::parseDSN($dsn);
114 if (!filesize($dsn_array['database']) && !empty($this->sqlite_initials))
115 $this->_sqlite_create_database($dbh, $this->sqlite_initials);
123 * Connect to appropiate databse
124 * depending on the operation
126 * @param string Connection mode (r|w)
129 function db_connect($mode)
131 $this->db_mode = $mode;
134 if ($this->db_connected)
136 // no replication, current connection is ok
137 if ($this->db_dsnw==$this->db_dsnr)
140 // connected to master, current connection is ok
141 if ($this->db_mode=='w')
144 // Same mode, current connection is ok
145 if ($this->db_mode==$mode)
150 $dsn = $this->db_dsnr;
152 $dsn = $this->db_dsnw;
154 $this->db_handle = $this->dsn_connect($dsn);
155 $this->db_connected = $this->db_handle ? TRUE : FALSE;
160 * Getter for error state
162 * @param boolean True on error
166 return $this->db_error ? $this->db_error_msg : FALSE;
171 * Execute a SQL query
173 * @param string SQL query to execute
174 * @param mixed Values to be inserted in query
175 * @return number Query handle identifier
180 $params = func_get_args();
181 $query = array_shift($params);
183 return $this->_query($query, 0, 0, $params);
188 * Execute a SQL query with limits
190 * @param string SQL query to execute
191 * @param number Offset for LIMIT statement
192 * @param number Number of rows for LIMIT statement
193 * @param mixed Values to be inserted in query
194 * @return number Query handle identifier
197 function limitquery()
199 $params = func_get_args();
200 $query = array_shift($params);
201 $offset = array_shift($params);
202 $numrows = array_shift($params);
204 return $this->_query($query, $offset, $numrows, $params);
209 * Execute a SQL query with limits
211 * @param string SQL query to execute
212 * @param number Offset for LIMIT statement
213 * @param number Number of rows for LIMIT statement
214 * @param array Values to be inserted in query
215 * @return number Query handle identifier
218 function _query($query, $offset, $numrows, $params)
221 if (strtolower(trim(substr($query,0,6)))=='select')
226 $this->db_connect($mode);
228 if (!$this->db_connected)
231 if ($this->db_provider == 'sqlite')
232 $this->_sqlite_prepare();
234 if ($numrows || $offset)
235 $result = $this->db_handle->limitQuery($query,$offset,$numrows,$params);
237 $result = $this->db_handle->query($query, $params);
239 // add result, even if it's an error
240 return $this->_add_result($result);
245 * Get number of rows for a SQL query
246 * If no query handle is specified, the last query will be taken as reference
248 * @param number Optional query handle identifier
249 * @return mixed Number of rows or FALSE on failure
252 function num_rows($res_id=NULL)
254 if (!$this->db_handle)
257 if ($result = $this->_get_result($res_id))
258 return $result->numRows();
265 * Get number of affected rows fort he last query
267 * @return mixed Number of rows or FALSE on failure
270 function affected_rows()
272 if (!$this->db_handle)
275 return $this->db_handle->affectedRows();
280 * Get last inserted record ID
281 * For Postgres databases, a sequence name is required
283 * @param string Sequence name for increment
284 * @return mixed ID or FALSE on failure
287 function insert_id($sequence = '')
289 if (!$this->db_handle || $this->db_mode=='r')
292 switch($this->db_provider)
295 // PostgreSQL uses sequences
296 $result = &$this->db_handle->getOne("SELECT CURRVAL('$sequence')");
297 if (DB::isError($result))
299 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
300 'message' => $result->getMessage()), TRUE, FALSE);
305 case 'mysql': // This is unfortuneate
306 return mysql_insert_id($this->db_handle->connection);
309 return mysqli_insert_id($this->db_handle->connection);
312 return sqlite_last_insert_rowid($this->db_handle->connection);
315 die("portability issue with this database, please have the developer fix");
321 * Get an associative array for one row
322 * If no query handle is specified, the last query will be taken as reference
324 * @param number Optional query handle identifier
325 * @return mixed Array with col values or FALSE on failure
328 function fetch_assoc($res_id=NULL)
330 $result = $this->_get_result($res_id);
331 return $this->_fetch_row($result, DB_FETCHMODE_ASSOC);
336 * Get an index array for one row
337 * If no query handle is specified, the last query will be taken as reference
339 * @param number Optional query handle identifier
340 * @return mixed Array with col values or FALSE on failure
343 function fetch_array($res_id=NULL)
345 $result = $this->_get_result($res_id);
346 return $this->_fetch_row($result, DB_FETCHMODE_ORDERED);
351 * Get co values for a result row
353 * @param object Query result handle
354 * @param number Fetch mode identifier
355 * @return mixed Array with col values or FALSE on failure
358 function _fetch_row($result, $mode)
360 if (DB::isError($result))
362 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
363 'message' => $this->db_link->getMessage()), TRUE, FALSE);
367 return $result->fetchRow($mode);
372 * Formats input so it can be safely used in a query
374 * @param mixed Value to quote
375 * @return string Quoted/converted string for use in query
378 function quote($input)
380 // create DB handle if not available
381 if (!$this->db_handle)
382 $this->db_connect('r');
384 // escape pear identifier chars
385 $rep_chars = array('?' => '\?',
389 return $this->db_handle->quoteSmart(strtr($input, $rep_chars));
394 * Quotes a string so it can be safely used as a table or column name
396 * @param string Value to quote
397 * @return string Quoted string for use in query
398 * @deprecated Replaced by rcube_db::quote_identifier
399 * @see rcube_db::quote_identifier
402 function quoteIdentifier($str)
404 return $this->quote_identifier($str);
409 * Quotes a string so it can be safely used as a table or column name
411 * @param string Value to quote
412 * @return string Quoted string for use in query
415 function quote_identifier($str)
417 if (!$this->db_handle)
418 $this->db_connect('r');
420 return $this->db_handle->quoteIdentifier($str);
425 * Return SQL statement to convert a field value into a unix timestamp
427 * @param string Field name
428 * @return string SQL statement to use in query
431 function unixtimestamp($field)
433 switch($this->db_provider)
436 return "EXTRACT (EPOCH FROM $field)";
440 return "UNIX_TIMESTAMP($field)";
446 * Return SQL statement to convert from a unix timestamp
448 * @param string Field name
449 * @return string SQL statement to use in query
452 function fromunixtime($timestamp)
454 switch($this->db_provider)
459 return "FROM_UNIXTIME($timestamp)";
462 return date("'Y-m-d H:i:s'", $timestamp);
468 * Adds a query result and returns a handle ID
470 * @param object Query handle
471 * @return mixed Handle ID or FALE on failure
474 function _add_result($res)
477 if (DB::isError($res))
479 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
480 'message' => $res->getMessage() . " Query: " . substr(preg_replace('/[\r\n]+\s*/', ' ', $res->userinfo), 0, 512)), TRUE, FALSE);
485 $res_id = sizeof($this->a_query_results);
486 $this->a_query_results[$res_id] = $res;
487 $this->last_res_id = $res_id;
494 * Resolves a given handle ID and returns the according query handle
495 * If no ID is specified, the last ressource handle will be returned
497 * @param number Handle ID
498 * @return mixed Ressource handle or FALE on failure
501 function _get_result($res_id=NULL)
504 $res_id = $this->last_res_id;
506 if ($res_id && isset($this->a_query_results[$res_id]))
507 return $this->a_query_results[$res_id];
514 * Create a sqlite database from a file
516 * @param object SQLite database handle
517 * @param string File path to use for DB creation
520 function _sqlite_create_database($dbh, $file_name)
522 if (empty($file_name) || !is_string($file_name))
526 if ($fd = fopen($file_name, 'r'))
528 $data = fread($fd, filesize($file_name));
533 sqlite_exec($dbh->connection, $data);
538 * Add some proprietary database functions to the current SQLite handle
539 * in order to make it MySQL compatible
543 function _sqlite_prepare()
545 include_once('include/rcube_sqlite.inc');
547 // we emulate via callback some missing MySQL function
548 sqlite_create_function($this->db_handle->connection, "from_unixtime", "rcube_sqlite_from_unixtime");
549 sqlite_create_function($this->db_handle->connection, "unix_timestamp", "rcube_sqlite_unix_timestamp");
550 sqlite_create_function($this->db_handle->connection, "now", "rcube_sqlite_now");
551 sqlite_create_function($this->db_handle->connection, "md5", "rcube_sqlite_md5");
555 } // end class rcube_db