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