4 +-----------------------------------------------------------------------+
5 | program/include/rcube_db.inc |
7 | This file is part of the RoundCube Webmail client |
8 | Copyright (C) 2005-2007, 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 543 2007-04-28 18:07:12Z 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' => 603, '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 $result = &$this->db_handle->getOne("SELECT CURRVAL('$sequence')");
296 if (DB::isError($result))
297 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
298 'message' => $result->getMessage()), TRUE, FALSE);
302 $result = &$this->db_handle->getOne("SELECT @@IDENTITY");
303 if (DB::isError($result))
304 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
305 'message' => $result->getMessage()), TRUE, FALSE);
308 case 'mysql': // This is unfortuneate
309 return mysql_insert_id($this->db_handle->connection);
312 return mysqli_insert_id($this->db_handle->connection);
315 return sqlite_last_insert_rowid($this->db_handle->connection);
318 die("portability issue with this database, please have the developer fix");
324 * Get an associative array for one row
325 * If no query handle is specified, the last query will be taken as reference
327 * @param number Optional query handle identifier
328 * @return mixed Array with col values or FALSE on failure
331 function fetch_assoc($res_id=NULL)
333 $result = $this->_get_result($res_id);
334 return $this->_fetch_row($result, DB_FETCHMODE_ASSOC);
339 * Get an index array for one row
340 * If no query handle is specified, the last query will be taken as reference
342 * @param number Optional query handle identifier
343 * @return mixed Array with col values or FALSE on failure
346 function fetch_array($res_id=NULL)
348 $result = $this->_get_result($res_id);
349 return $this->_fetch_row($result, DB_FETCHMODE_ORDERED);
354 * Get co values for a result row
356 * @param object Query result handle
357 * @param number Fetch mode identifier
358 * @return mixed Array with col values or FALSE on failure
361 function _fetch_row($result, $mode)
363 if (!$result || DB::isError($result))
365 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
366 'message' => $this->db_link->getMessage()), TRUE, FALSE);
369 elseif (!is_object($result))
372 return $result->fetchRow($mode);
377 * Formats input so it can be safely used in a query
379 * @param mixed Value to quote
380 * @return string Quoted/converted string for use in query
383 function quote($input)
385 // create DB handle if not available
386 if (!$this->db_handle)
387 $this->db_connect('r');
389 // escape pear identifier chars
390 $rep_chars = array('?' => '\?',
394 return $this->db_handle->quoteSmart(strtr($input, $rep_chars));
399 * Quotes a string so it can be safely used as a table or column name
401 * @param string Value to quote
402 * @return string Quoted string for use in query
403 * @deprecated Replaced by rcube_db::quote_identifier
404 * @see rcube_db::quote_identifier
407 function quoteIdentifier($str)
409 return $this->quote_identifier($str);
414 * Quotes a string so it can be safely used as a table or column name
416 * @param string Value to quote
417 * @return string Quoted string for use in query
420 function quote_identifier($str)
422 if (!$this->db_handle)
423 $this->db_connect('r');
425 return $this->db_handle->quoteIdentifier($str);
430 * Return SQL function for current time and date
432 * @return string SQL function to use in query
437 switch($this->db_provider)
449 * Return SQL statement to convert a field value into a unix timestamp
451 * @param string Field name
452 * @return string SQL statement to use in query
455 function unixtimestamp($field)
457 switch($this->db_provider)
460 return "EXTRACT (EPOCH FROM $field)";
463 return "datediff(s, '1970-01-01 00:00:00', $field)";
466 return "UNIX_TIMESTAMP($field)";
472 * Return SQL statement to convert from a unix timestamp
474 * @param string Field name
475 * @return string SQL statement to use in query
478 function fromunixtime($timestamp)
480 switch($this->db_provider)
485 return sprintf("FROM_UNIXTIME(%d)", $timestamp);
488 return date("'Y-m-d H:i:s'", $timestamp);
494 * Adds a query result and returns a handle ID
496 * @param object Query handle
497 * @return mixed Handle ID or FALE on failure
500 function _add_result($res)
503 if (DB::isError($res))
505 raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
506 'message' => $res->getMessage() . " Query: " . substr(preg_replace('/[\r\n]+\s*/', ' ', $res->userinfo), 0, 512)), TRUE, FALSE);
511 $res_id = sizeof($this->a_query_results);
512 $this->a_query_results[$res_id] = $res;
513 $this->last_res_id = $res_id;
520 * Resolves a given handle ID and returns the according query handle
521 * If no ID is specified, the last ressource handle will be returned
523 * @param number Handle ID
524 * @return mixed Ressource handle or FALE on failure
527 function _get_result($res_id=NULL)
530 $res_id = $this->last_res_id;
532 if ($res_id && isset($this->a_query_results[$res_id]))
533 return $this->a_query_results[$res_id];
540 * Create a sqlite database from a file
542 * @param object SQLite database handle
543 * @param string File path to use for DB creation
546 function _sqlite_create_database($dbh, $file_name)
548 if (empty($file_name) || !is_string($file_name))
552 if ($fd = fopen($file_name, 'r'))
554 $data = fread($fd, filesize($file_name));
559 sqlite_exec($dbh->connection, $data);
564 * Add some proprietary database functions to the current SQLite handle
565 * in order to make it MySQL compatible
569 function _sqlite_prepare()
571 include_once('include/rcube_sqlite.inc');
573 // we emulate via callback some missing MySQL function
574 sqlite_create_function($this->db_handle->connection, "from_unixtime", "rcube_sqlite_from_unixtime");
575 sqlite_create_function($this->db_handle->connection, "unix_timestamp", "rcube_sqlite_unix_timestamp");
576 sqlite_create_function($this->db_handle->connection, "now", "rcube_sqlite_now");
577 sqlite_create_function($this->db_handle->connection, "md5", "rcube_sqlite_md5");
581 } // end class rcube_db