source: github/program/include/rcube_db.inc @ cbdd6dc

HEADcourier-fixdev-browser-capabilitiespdorelease-0.6release-0.7release-0.8
Last change on this file since cbdd6dc was cbdd6dc, checked in by thomascube <thomas@…>, 7 years ago

Fixed persistent connections with PHP4
  • Property mode set to 100755
File size: 14.2 KB
Line 
1<?php
2
3/*
4 +-----------------------------------------------------------------------+
5 | program/include/rcube_db.inc                                          |
6 |                                                                       |
7 | This file is part of the RoundCube Webmail client                     |
8 | Copyright (C) 2005, RoundCube Dev. - Switzerland                      |
9 | Licensed under the GNU GPL                                            |
10 |                                                                       |
11 | PURPOSE:                                                              |
12 |   PEAR:DB wrapper class that implements PEAR DB functions             |
13 |   See http://pear.php.net/package/DB                                  |
14 |                                                                       |
15 +-----------------------------------------------------------------------+
16 | Author: David Saez Padros <david@ols.es>                              |
17 |         Thomas Bruederli <roundcube@gmail.com>                        |
18 +-----------------------------------------------------------------------+
19
20 $Id$
21
22*/
23
24
25/**
26 * Obtain the PEAR::DB class that is used for abstraction
27 */
28require_once('DB.php');
29
30
31/**
32 * Database independent query interface
33 *
34 * This is a wrapper for the PEAR::DB class
35 *
36 * @package    RoundCube Webmail
37 * @author     David Saez Padros <david@ols.es>
38 * @author     Thomas Bruederli <roundcube@gmail.com>
39 * @version    1.17
40 * @link       http://pear.php.net/package/DB
41 */
42class rcube_db
43  {
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 = '';
52
53  var $a_query_results = array('dummy');
54  var $last_res_id = 0;
55
56
57  /**
58   * Object constructor
59   *
60   * @param  string  DSN for read/write operations
61   * @param  string  Optional DSN for read only operations
62   */
63  function __construct($db_dsnw, $db_dsnr='', $pconn=false)
64    {
65    if ($db_dsnr=='')
66      $db_dsnr=$db_dsnw;
67       
68    $this->db_dsnw = $db_dsnw;
69    $this->db_dsnr = $db_dsnr;
70    $this->db_pconn = $pconn;
71       
72    $dsn_array = DB::parseDSN($db_dsnw);
73    $this->db_provider = $dsn_array['phptype'];       
74    }
75
76
77  /**
78   * PHP 4 object constructor
79   *
80   * @see  rcube_db::__construct
81   */
82  function rcube_db($db_dsnw, $db_dsnr='', $pconn=false)
83    {
84    $this->__construct($db_dsnw, $db_dsnr, $pconn);
85    }
86
87
88  /**
89   * Connect to specific database
90   *
91   * @param  string  DSN for DB connections
92   * @return object  PEAR database handle
93   * @access private
94   */
95  function dsn_connect($dsn)
96    {
97    // Use persistent connections if available
98    $dbh = DB::connect($dsn, array('persistent' => $this->db_pconn));
99       
100    if (DB::isError($dbh))
101      {
102      $this->db_error = TRUE;
103      $this->db_error_msg = $dbh->getMessage();
104
105      raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
106                        'message' => $this->db_error_msg), TRUE, FALSE);
107                       
108      return FALSE;
109      }
110
111    else if ($this->db_provider=='sqlite')
112      {
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);
116      }
117       
118    return $dbh;
119    }
120
121
122  /**
123   * Connect to appropiate databse
124   * depending on the operation
125   *
126   * @param  string  Connection mode (r|w)
127   * @access public
128   */
129  function db_connect($mode)
130    {
131    $this->db_mode = $mode;
132
133    // Already connected
134    if ($this->db_connected)
135      {
136      // no replication, current connection is ok
137      if ($this->db_dsnw==$this->db_dsnr)
138        return;
139           
140      // connected to master, current connection is ok
141      if ($this->db_mode=='w')
142        return;
143
144      // Same mode, current connection is ok
145      if ($this->db_mode==$mode)
146        return;
147      }
148     
149    if ($mode=='r')
150      $dsn = $this->db_dsnr;
151    else
152      $dsn = $this->db_dsnw;
153
154    $this->db_handle = $this->dsn_connect($dsn);
155    $this->db_connected = $this->db_handle ? TRUE : FALSE;
156    }
157   
158   
159  /**
160   * Getter for error state
161   *
162   * @param  boolean  True on error
163   */
164  function is_error()
165    {
166    return $this->db_error ? $this->db_error_msg : FALSE;
167    }
168
169
170  /**
171   * Execute a SQL query
172   *
173   * @param  string  SQL query to execute
174   * @param  mixed   Values to be inserted in query
175   * @return number  Query handle identifier
176   * @access public
177   */
178  function query()
179    {
180    $params = func_get_args();
181    $query = array_shift($params);
182
183    return $this->_query($query, 0, 0, $params);
184    }
185
186
187  /**
188   * Execute a SQL query with limits
189   *
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
195   * @access public
196   */
197  function limitquery()
198    {
199    $params = func_get_args();
200    $query = array_shift($params);
201    $offset = array_shift($params);
202    $numrows = array_shift($params);
203               
204    return $this->_query($query, $offset, $numrows, $params);
205    }
206
207
208  /**
209   * Execute a SQL query with limits
210   *
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
216   * @access private
217   */
218  function _query($query, $offset, $numrows, $params)
219    {
220    // Read or write ?
221    if (strtolower(trim(substr($query,0,6)))=='select')
222      $mode='r';
223    else
224      $mode='w';
225       
226    $this->db_connect($mode);
227   
228    if (!$this->db_connected)
229      return FALSE;
230
231    if ($this->db_provider == 'sqlite')
232      $this->_sqlite_prepare();
233
234    if ($numrows || $offset)
235      $result = $this->db_handle->limitQuery($query,$offset,$numrows,$params);
236    else   
237      $result = $this->db_handle->query($query, $params);
238       
239    // add result, even if it's an error
240    return $this->_add_result($result);
241    }
242
243
244  /**
245   * Get number of rows for a SQL query
246   * If no query handle is specified, the last query will be taken as reference
247   *
248   * @param  number  Optional query handle identifier
249   * @return mixed   Number of rows or FALSE on failure
250   * @access public
251   */
252  function num_rows($res_id=NULL)
253    {
254    if (!$this->db_handle)
255      return FALSE;
256
257    if ($result = $this->_get_result($res_id))   
258      return $result->numRows();
259    else
260      return FALSE;
261    }
262
263
264  /**
265   * Get number of affected rows fort he last query
266   *
267   * @return mixed   Number of rows or FALSE on failure
268   * @access public
269   */
270  function affected_rows()
271    {
272    if (!$this->db_handle)
273      return FALSE;
274
275    return $this->db_handle->affectedRows();
276    }
277
278
279  /**
280   * Get last inserted record ID
281   * For Postgres databases, a sequence name is required
282   *
283   * @param  string  Sequence name for increment
284   * @return mixed   ID or FALSE on failure
285   * @access public
286   */
287  function insert_id($sequence = '')
288    {
289    if (!$this->db_handle || $this->db_mode=='r')
290      return FALSE;
291
292    switch($this->db_provider)
293      {
294      case 'pgsql':
295        // PostgreSQL uses sequences
296        $result = &$this->db_handle->getOne("SELECT CURRVAL('$sequence')");
297        if (DB::isError($result))
298          {
299          raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
300                            'message' => $result->getMessage()), TRUE, FALSE);
301          }
302
303        return $result;
304               
305      case 'mysql': // This is unfortuneate
306        return mysql_insert_id($this->db_handle->connection);
307
308      case 'mysqli':
309        return mysqli_insert_id($this->db_handle->connection);
310       
311      case 'sqlite':
312        return sqlite_last_insert_rowid($this->db_handle->connection);
313
314      default:
315        die("portability issue with this database, please have the developer fix");
316      }
317    }
318
319
320  /**
321   * Get an associative array for one row
322   * If no query handle is specified, the last query will be taken as reference
323   *
324   * @param  number  Optional query handle identifier
325   * @return mixed   Array with col values or FALSE on failure
326   * @access public
327   */
328  function fetch_assoc($res_id=NULL)
329    {
330    $result = $this->_get_result($res_id);
331    return $this->_fetch_row($result, DB_FETCHMODE_ASSOC);
332    }
333
334
335  /**
336   * Get an index array for one row
337   * If no query handle is specified, the last query will be taken as reference
338   *
339   * @param  number  Optional query handle identifier
340   * @return mixed   Array with col values or FALSE on failure
341   * @access public
342   */
343  function fetch_array($res_id=NULL)
344    {
345    $result = $this->_get_result($res_id);
346    return $this->_fetch_row($result, DB_FETCHMODE_ORDERED);
347    }
348
349
350  /**
351   * Get co values for a result row
352   *
353   * @param  object  Query result handle
354   * @param  number  Fetch mode identifier
355   * @return mixed   Array with col values or FALSE on failure
356   * @access private
357   */
358  function _fetch_row($result, $mode)
359    {
360    if (DB::isError($result))
361      {
362      raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
363                        'message' => $this->db_link->getMessage()), TRUE, FALSE);
364      return FALSE;
365      }
366                         
367    return $result->fetchRow($mode);
368    }
369   
370
371  /**
372   * Formats input so it can be safely used in a query
373   *
374   * @param  mixed   Value to quote
375   * @return string  Quoted/converted string for use in query
376   * @access public
377   */
378  function quote($input)
379    {
380    // create DB handle if not available
381    if (!$this->db_handle)
382      $this->db_connect('r');
383     
384    // escape pear identifier chars
385    $rep_chars = array('?' => '\?',
386                       '!' => '\!',
387                       '&' => '\&');
388     
389    return $this->db_handle->quoteSmart(strtr($input, $rep_chars));
390    }
391   
392
393  /**
394   * Quotes a string so it can be safely used as a table or column name
395   *
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
400   * @access public
401   */
402  function quoteIdentifier($str)
403        {
404    return $this->quote_identifier($str);
405        }
406
407
408  /**
409   * Quotes a string so it can be safely used as a table or column name
410   *
411   * @param  string  Value to quote
412   * @return string  Quoted string for use in query
413   * @access public
414   */
415  function quote_identifier($str)
416    {
417    if (!$this->db_handle)
418      $this->db_connect('r');
419                       
420    return $this->db_handle->quoteIdentifier($str);
421    }
422
423
424  /**
425   * Return SQL statement to convert a field value into a unix timestamp
426   *
427   * @param  string  Field name
428   * @return string  SQL statement to use in query
429   * @access public
430   */
431  function unixtimestamp($field)
432    {
433    switch($this->db_provider)
434      {
435      case 'pgsql':
436        return "EXTRACT (EPOCH FROM $field)";
437        break;
438
439      default:
440        return "UNIX_TIMESTAMP($field)";
441      }
442    }
443
444
445  /**
446   * Return SQL statement to convert from a unix timestamp
447   *
448   * @param  string  Field name
449   * @return string  SQL statement to use in query
450   * @access public
451   */
452  function fromunixtime($timestamp)
453    {
454    switch($this->db_provider)
455      {
456      case 'mysqli':
457      case 'mysql':
458      case 'sqlite':
459        return "FROM_UNIXTIME($timestamp)";
460
461      default:
462        return date("'Y-m-d H:i:s'", $timestamp);
463      }
464    }
465
466
467  /**
468   * Adds a query result and returns a handle ID
469   *
470   * @param  object  Query handle
471   * @return mixed   Handle ID or FALE on failure
472   * @access private
473   */
474  function _add_result($res)
475    {
476    // sql error occured
477    if (DB::isError($res))
478      {
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);
481      return FALSE;
482      }
483    else
484      {
485      $res_id = sizeof($this->a_query_results);
486      $this->a_query_results[$res_id] = $res;
487      $this->last_res_id = $res_id;
488      return $res_id;
489      }
490    }
491
492
493  /**
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
496   *
497   * @param  number  Handle ID
498   * @return mixed   Ressource handle or FALE on failure
499   * @access private
500   */
501  function _get_result($res_id=NULL)
502    {
503    if ($res_id==NULL)
504      $res_id = $this->last_res_id;
505   
506     if ($res_id && isset($this->a_query_results[$res_id]))
507       return $this->a_query_results[$res_id];
508     else
509       return FALSE;
510    }
511
512
513  /**
514   * Create a sqlite database from a file
515   *
516   * @param  object  SQLite database handle
517   * @param  string  File path to use for DB creation
518   * @access private
519   */
520  function _sqlite_create_database($dbh, $file_name)
521    {
522    if (empty($file_name) || !is_string($file_name))
523      return;
524
525    $data = '';
526    if ($fd = fopen($file_name, 'r'))
527      {
528      $data = fread($fd, filesize($file_name));
529      fclose($fd);
530      }
531
532    if (strlen($data))
533      sqlite_exec($dbh->connection, $data);
534    }
535
536
537  /**
538   * Add some proprietary database functions to the current SQLite handle
539   * in order to make it MySQL compatible
540   *
541   * @access private
542   */
543  function _sqlite_prepare()
544    {
545    include_once('include/rcube_sqlite.inc');
546
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");   
552    }
553
554
555  }  // end class rcube_db
556
557?>
Note: See TracBrowser for help on using the repository browser.