4 +-----------------------------------------------------------------------+
5 | program/include/rcube_db.php |
7 | This file is part of the RoundCube Webmail client |
8 | Copyright (C) 2005-2008, 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.php 1291 2008-04-12 13:54:45Z thomasb $
26 * Database independent query interface
28 * This is a wrapper for the PEAR::DB class
31 * @author David Saez Padros <david@ols.es>
32 * @author Thomas Bruederli <roundcube@gmail.com>
34 * @link http://pear.php.net/package/DB
38 var $db_dsnw; // DSN for write operations
39 var $db_dsnr; // DSN for read operations
40 var $db_connected = false; // Already connected ?
41 var $db_mode = ''; // Connection mode
42 var $db_handle = 0; // Connection handle
43 var $db_pconn = false; // Use persistent connections
44 var $db_error = false;
45 var $db_error_msg = '';
47 var $a_query_results = array('dummy');
54 * @param string DSN for read/write operations
55 * @param string Optional DSN for read only operations
57 function __construct($db_dsnw, $db_dsnr='', $pconn=false)
62 $this->db_dsnw = $db_dsnw;
63 $this->db_dsnr = $db_dsnr;
64 $this->db_pconn = $pconn;
66 $dsn_array = DB::parseDSN($db_dsnw);
67 $this->db_provider = $dsn_array['phptype'];
72 * PHP 4 object constructor
74 * @see rcube_db::__construct
76 function rcube_db($db_dsnw, $db_dsnr='', $pconn=false)
78 $this->__construct($db_dsnw, $db_dsnr, $pconn);
83 * Connect to specific database
85 * @param string DSN for DB connections
86 * @return object PEAR database handle
89 function dsn_connect($dsn)
91 // Use persistent connections if available
92 $dbh = DB::connect($dsn, array('persistent' => $this->db_pconn));
94 if (DB::isError($dbh))
96 $this->db_error = TRUE;
97 $this->db_error_msg = $dbh->getMessage();
99 raise_error(array('code' => 603, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
100 'message' => $this->db_error_msg), TRUE, FALSE);
105 else if ($this->db_provider=='sqlite')
107 $dsn_array = DB::parseDSN($dsn);
108 if (!filesize($dsn_array['database']) && !empty($this->sqlite_initials))
109 $this->_sqlite_create_database($dbh, $this->sqlite_initials);
117 * Connect to appropiate databse
118 * depending on the operation
120 * @param string Connection mode (r|w)
123 function db_connect($mode)
125 $this->db_mode = $mode;
128 if ($this->db_connected)
130 // no replication, current connection is ok
131 if ($this->db_dsnw==$this->db_dsnr)
134 // connected to master, current connection is ok
135 if ($this->db_mode=='w')
138 // Same mode, current connection is ok
139 if ($this->db_mode==$mode)
144 $dsn = $this->db_dsnr;
146 $dsn = $this->db_dsnw;
148 $this->db_handle = $this->dsn_connect($dsn);
149 $this->db_connected = $this->db_handle ? TRUE : FALSE;
154 * Activate/deactivate debug mode
157 function set_debug($dbg = true)
164 * Getter for error state
166 * @param boolean True on error
170 return $this->db_error ? $this->db_error_msg : FALSE;
175 * Execute a SQL query
177 * @param string SQL query to execute
178 * @param mixed Values to be inserted in query
179 * @return number Query handle identifier
184 $params = func_get_args();
185 $query = array_shift($params);
187 return $this->_query($query, 0, 0, $params);
192 * Execute a SQL query with limits
194 * @param string SQL query to execute
195 * @param number Offset for LIMIT statement
196 * @param number Number of rows for LIMIT statement
197 * @param mixed Values to be inserted in query
198 * @return number Query handle identifier
201 function limitquery()
203 $params = func_get_args();
204 $query = array_shift($params);
205 $offset = array_shift($params);
206 $numrows = array_shift($params);
208 return $this->_query($query, $offset, $numrows, $params);
213 * Execute a SQL query with limits
215 * @param string SQL query to execute
216 * @param number Offset for LIMIT statement
217 * @param number Number of rows for LIMIT statement
218 * @param array Values to be inserted in query
219 * @return number Query handle identifier
222 function _query($query, $offset, $numrows, $params)
225 if (strtolower(trim(substr($query,0,6)))=='select')
230 $this->db_connect($mode);
232 if (!$this->db_connected)
235 if ($this->db_provider == 'sqlite')
236 $this->_sqlite_prepare();
238 if ($numrows || $offset)
239 $result = $this->db_handle->limitQuery($query,$offset,$numrows,$params);
241 $result = $this->db_handle->query($query, $params);
243 // add result, even if it's an error
244 return $this->_add_result($result);
249 * Get number of rows for a SQL query
250 * If no query handle is specified, the last query will be taken as reference
252 * @param number Optional query handle identifier
253 * @return mixed Number of rows or FALSE on failure
256 function num_rows($res_id=NULL)
258 if (!$this->db_handle)
261 if ($result = $this->_get_result($res_id))
262 return $result->numRows();
269 * Get number of affected rows fort he last query
271 * @return mixed Number of rows or FALSE on failure
274 function affected_rows()
276 if (!$this->db_handle)
279 return $this->db_handle->affectedRows();
284 * Get last inserted record ID
285 * For Postgres databases, a sequence name is required
287 * @param string Sequence name for increment
288 * @return mixed ID or FALSE on failure
291 function insert_id($sequence = '')
293 if (!$this->db_handle || $this->db_mode=='r')
296 switch($this->db_provider)
299 $result = &$this->db_handle->getOne("SELECT CURRVAL('$sequence')");
300 if (DB::isError($result))
301 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
302 'message' => $result->getMessage()), TRUE, FALSE);
306 $result = &$this->db_handle->getOne("SELECT @@IDENTITY");
307 if (DB::isError($result))
308 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
309 'message' => $result->getMessage()), TRUE, FALSE);
312 case 'mysql': // This is unfortuneate
313 return mysql_insert_id($this->db_handle->connection);
316 return mysqli_insert_id($this->db_handle->connection);
319 return sqlite_last_insert_rowid($this->db_handle->connection);
322 die("portability issue with this database, please have the developer fix");
328 * Get an associative array for one row
329 * If no query handle is specified, the last query will be taken as reference
331 * @param number Optional query handle identifier
332 * @return mixed Array with col values or FALSE on failure
335 function fetch_assoc($res_id=NULL)
337 $result = $this->_get_result($res_id);
338 return $this->_fetch_row($result, DB_FETCHMODE_ASSOC);
343 * Get an index array for one row
344 * If no query handle is specified, the last query will be taken as reference
346 * @param number Optional query handle identifier
347 * @return mixed Array with col values or FALSE on failure
350 function fetch_array($res_id=NULL)
352 $result = $this->_get_result($res_id);
353 return $this->_fetch_row($result, DB_FETCHMODE_ORDERED);
358 * Get co values for a result row
360 * @param object Query result handle
361 * @param number Fetch mode identifier
362 * @return mixed Array with col values or FALSE on failure
365 function _fetch_row($result, $mode)
367 if (!$result || DB::isError($result))
369 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
370 'message' => $this->db_link->getMessage()), TRUE, FALSE);
373 elseif (!is_object($result))
376 return $result->fetchRow($mode);
381 * Formats input so it can be safely used in a query
383 * @param mixed Value to quote
384 * @return string Quoted/converted string for use in query
387 function quote($input)
389 // create DB handle if not available
390 if (!$this->db_handle)
391 $this->db_connect('r');
393 // escape pear identifier chars
394 $rep_chars = array('?' => '\?',
398 return $this->db_handle->quoteSmart(strtr($input, $rep_chars));
403 * Quotes a string so it can be safely used as a table or column name
405 * @param string Value to quote
406 * @return string Quoted string for use in query
407 * @deprecated Replaced by rcube_db::quote_identifier
408 * @see rcube_db::quote_identifier
411 function quoteIdentifier($str)
413 return $this->quote_identifier($str);
418 * Quotes a string so it can be safely used as a table or column name
420 * @param string Value to quote
421 * @return string Quoted string for use in query
424 function quote_identifier($str)
426 if (!$this->db_handle)
427 $this->db_connect('r');
429 return $this->db_handle->quoteIdentifier($str);
436 * @param string The string to be escaped
437 * @return string The escaped string
440 function escapeSimple($str)
442 if (!$this->db_handle)
443 $this->db_connect('r');
445 return $this->db_handle->escapeSimple($str);
450 * Return SQL function for current time and date
452 * @return string SQL function to use in query
457 switch($this->db_provider)
469 * Return SQL statement to convert a field value into a unix timestamp
471 * @param string Field name
472 * @return string SQL statement to use in query
475 function unixtimestamp($field)
477 switch($this->db_provider)
480 return "EXTRACT (EPOCH FROM $field)";
483 return "datediff(s, '1970-01-01 00:00:00', $field)";
486 return "UNIX_TIMESTAMP($field)";
492 * Return SQL statement to convert from a unix timestamp
494 * @param string Field name
495 * @return string SQL statement to use in query
498 function fromunixtime($timestamp)
500 switch($this->db_provider)
505 return sprintf("FROM_UNIXTIME(%d)", $timestamp);
508 return date("'Y-m-d H:i:s'", $timestamp);
514 * Adds a query result and returns a handle ID
516 * @param object Query handle
517 * @return mixed Handle ID or FALE on failure
520 function _add_result($res)
523 if (DB::isError($res))
525 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
526 'message' => $res->getMessage() . " Query: " . substr(preg_replace('/[\r\n]+\s*/', ' ', $res->userinfo), 0, 512)), TRUE, FALSE);
531 $res_id = sizeof($this->a_query_results);
532 $this->a_query_results[$res_id] = $res;
533 $this->last_res_id = $res_id;
540 * Resolves a given handle ID and returns the according query handle
541 * If no ID is specified, the last ressource handle will be returned
543 * @param number Handle ID
544 * @return mixed Ressource handle or FALE on failure
547 function _get_result($res_id=NULL)
550 $res_id = $this->last_res_id;
552 if ($res_id && isset($this->a_query_results[$res_id]))
553 return $this->a_query_results[$res_id];
560 * Create a sqlite database from a file
562 * @param object SQLite database handle
563 * @param string File path to use for DB creation
566 function _sqlite_create_database($dbh, $file_name)
568 if (empty($file_name) || !is_string($file_name))
572 if ($fd = fopen($file_name, 'r'))
574 $data = fread($fd, filesize($file_name));
579 sqlite_exec($dbh->connection, $data);
584 * Add some proprietary database functions to the current SQLite handle
585 * in order to make it MySQL compatible
589 function _sqlite_prepare()
591 include_once('include/rcube_sqlite.inc');
593 // we emulate via callback some missing MySQL function
594 sqlite_create_function($this->db_handle->connection, "from_unixtime", "rcube_sqlite_from_unixtime");
595 sqlite_create_function($this->db_handle->connection, "unix_timestamp", "rcube_sqlite_unix_timestamp");
596 sqlite_create_function($this->db_handle->connection, "now", "rcube_sqlite_now");
597 sqlite_create_function($this->db_handle->connection, "md5", "rcube_sqlite_md5");
601 } // end class rcube_db