source: subversion/trunk/roundcubemail/program/include/rcube_db.inc @ 1089

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