| commit | author | age | ||
| f45ec7 | 1 | <?php |
| S | 2 | |
| 3 | /* | |
| 4 | +-----------------------------------------------------------------------+ | |
| 47124c | 5 | | program/include/rcube_mdb2.php | |
| f45ec7 | 6 | | | |
| e019f2 | 7 | | This file is part of the Roundcube Webmail client | |
| f5e7b3 | 8 | | Copyright (C) 2005-2009, The Roundcube Dev Team | |
| f45ec7 | 9 | | Licensed under the GNU GPL | |
| S | 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 | * Database independent query interface | |
| 26 | * | |
| 27 | * This is a wrapper for the PEAR::MDB2 class | |
| 28 | * | |
| 6d969b | 29 | * @package Database |
| d2a9db | 30 | * @author David Saez Padros <david@ols.es> |
| T | 31 | * @author Thomas Bruederli <roundcube@gmail.com> |
| 32 | * @author Lukas Kahwe Smith <smith@pooteeweet.org> | |
| a3de4f | 33 | * @version 1.17 |
| d2a9db | 34 | * @link http://pear.php.net/package/MDB2 |
| T | 35 | */ |
| 6d969b | 36 | class rcube_mdb2 |
| a004bb | 37 | { |
| A | 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_error = false; | |
| 44 | var $db_error_msg = ''; | |
| f45ec7 | 45 | |
| a004bb | 46 | private $debug_mode = false; |
| A | 47 | private $a_query_results = array('dummy'); |
| 48 | private $last_res_id = 0; | |
| 49 | private $tables; | |
| f45ec7 | 50 | |
| d2a9db | 51 | |
| a004bb | 52 | /** |
| A | 53 | * Object constructor |
| 54 | * | |
| 5c461b | 55 | * @param string $db_dsnw DSN for read/write operations |
| A | 56 | * @param string $db_dsnr Optional DSN for read only operations |
| a004bb | 57 | */ |
| A | 58 | function __construct($db_dsnw, $db_dsnr='', $pconn=false) |
| f45ec7 | 59 | { |
| a004bb | 60 | if ($db_dsnr == '') |
| A | 61 | $db_dsnr = $db_dsnw; |
| d2a9db | 62 | |
| a004bb | 63 | $this->db_dsnw = $db_dsnw; |
| A | 64 | $this->db_dsnr = $db_dsnr; |
| 65 | $this->db_pconn = $pconn; | |
| 05a7e3 | 66 | |
| a004bb | 67 | $dsn_array = MDB2::parseDSN($db_dsnw); |
| A | 68 | $this->db_provider = $dsn_array['phptype']; |
| f45ec7 | 69 | } |
| S | 70 | |
| d2a9db | 71 | |
| a004bb | 72 | /** |
| A | 73 | * Connect to specific database |
| 74 | * | |
| 5c461b | 75 | * @param string $dsn DSN for DB connections |
| A | 76 | * @return MDB2 PEAR database handle |
| a004bb | 77 | * @access private |
| A | 78 | */ |
| 79 | private function dsn_connect($dsn) | |
| f45ec7 | 80 | { |
| a004bb | 81 | // Use persistent connections if available |
| A | 82 | $db_options = array( |
| 83 | 'persistent' => $this->db_pconn, | |
| 84 | 'emulate_prepared' => $this->debug_mode, | |
| 85 | 'debug' => $this->debug_mode, | |
| 86 | 'debug_handler' => 'mdb2_debug_handler', | |
| 05a7e3 | 87 | 'portability' => MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL); |
| 9f23f0 | 88 | |
| a004bb | 89 | if ($this->db_provider == 'pgsql') { |
| A | 90 | $db_options['disable_smart_seqname'] = true; |
| 91 | $db_options['seqname_format'] = '%s'; | |
| 92 | } | |
| 9f23f0 | 93 | |
| a004bb | 94 | $dbh = MDB2::connect($dsn, $db_options); |
| d2a9db | 95 | |
| a004bb | 96 | if (MDB2::isError($dbh)) { |
| A | 97 | $this->db_error = true; |
| 98 | $this->db_error_msg = $dbh->getMessage(); | |
| 05a7e3 | 99 | |
| a004bb | 100 | raise_error(array('code' => 500, 'type' => 'db', |
| A | 101 | 'line' => __LINE__, 'file' => __FILE__, |
| 102 | 'message' => $dbh->getUserInfo()), true, false); | |
| f45ec7 | 103 | } |
| a004bb | 104 | else if ($this->db_provider == 'sqlite') { |
| A | 105 | $dsn_array = MDB2::parseDSN($dsn); |
| 106 | if (!filesize($dsn_array['database']) && !empty($this->sqlite_initials)) | |
| 107 | $this->_sqlite_create_database($dbh, $this->sqlite_initials); | |
| f45ec7 | 108 | } |
| a004bb | 109 | else if ($this->db_provider!='mssql' && $this->db_provider!='sqlsrv') |
| A | 110 | $dbh->setCharset('utf8'); |
| d2a9db | 111 | |
| a004bb | 112 | return $dbh; |
| f45ec7 | 113 | } |
| S | 114 | |
| ccfda8 | 115 | |
| a004bb | 116 | /** |
| A | 117 | * Connect to appropiate database depending on the operation |
| 118 | * | |
| 5c461b | 119 | * @param string $mode Connection mode (r|w) |
| a004bb | 120 | * @access public |
| A | 121 | */ |
| 122 | function db_connect($mode) | |
| 10a699 | 123 | { |
| a004bb | 124 | // Already connected |
| A | 125 | if ($this->db_connected) { |
| 8603bb | 126 | // connected to read-write db, current connection is ok |
| T | 127 | if ($this->db_mode == 'w') |
| 128 | return; | |
| 129 | ||
| cb2bc8 | 130 | // no replication, current connection is ok for read and write |
| T | 131 | if (empty($this->db_dsnr) || $this->db_dsnw == $this->db_dsnr) { |
| 132 | $this->db_mode = 'w'; | |
| a004bb | 133 | return; |
| cb2bc8 | 134 | } |
| a004bb | 135 | |
| A | 136 | // Same mode, current connection is ok |
| 137 | if ($this->db_mode == $mode) | |
| 138 | return; | |
| 139 | } | |
| 140 | ||
| 141 | $dsn = ($mode == 'r') ? $this->db_dsnr : $this->db_dsnw; | |
| 142 | ||
| 143 | $this->db_handle = $this->dsn_connect($dsn); | |
| a3de4f | 144 | $this->db_connected = !PEAR::isError($this->db_handle); |
| T | 145 | $this->db_mode = $mode; |
| 10a699 | 146 | } |
| T | 147 | |
| 148 | ||
| a004bb | 149 | /** |
| A | 150 | * Activate/deactivate debug mode |
| 151 | * | |
| 5c461b | 152 | * @param boolean $dbg True if SQL queries should be logged |
| a004bb | 153 | * @access public |
| A | 154 | */ |
| 155 | function set_debug($dbg = true) | |
| d2a9db | 156 | { |
| a004bb | 157 | $this->debug_mode = $dbg; |
| A | 158 | if ($this->db_connected) { |
| 159 | $this->db_handle->setOption('debug', $dbg); | |
| 160 | $this->db_handle->setOption('emulate_prepared', $dbg); | |
| 161 | } | |
| d2a9db | 162 | } |
| ccfda8 | 163 | |
| d2a9db | 164 | |
| a004bb | 165 | /** |
| A | 166 | * Getter for error state |
| 167 | * | |
| 168 | * @param boolean True on error | |
| 169 | * @access public | |
| 170 | */ | |
| 171 | function is_error() | |
| d2a9db | 172 | { |
| a004bb | 173 | return $this->db_error ? $this->db_error_msg : false; |
| e1ac21 | 174 | } |
| a004bb | 175 | |
| A | 176 | |
| 177 | /** | |
| 178 | * Connection state checker | |
| 179 | * | |
| 180 | * @param boolean True if in connected state | |
| 181 | * @access public | |
| 182 | */ | |
| 183 | function is_connected() | |
| 184 | { | |
| 185 | return PEAR::isError($this->db_handle) ? false : $this->db_connected; | |
| 186 | } | |
| 187 | ||
| 188 | ||
| 189 | /** | |
| 190 | * Execute a SQL query | |
| 191 | * | |
| 192 | * @param string SQL query to execute | |
| 193 | * @param mixed Values to be inserted in query | |
| 194 | * @return number Query handle identifier | |
| 195 | * @access public | |
| 196 | */ | |
| 197 | function query() | |
| 198 | { | |
| 199 | $params = func_get_args(); | |
| 200 | $query = array_shift($params); | |
| 201 | ||
| 9db4ca | 202 | // Support one argument of type array, instead of n arguments |
| A | 203 | if (count($params) == 1 && is_array($params[0])) |
| 204 | $params = $params[0]; | |
| 205 | ||
| a004bb | 206 | return $this->_query($query, 0, 0, $params); |
| A | 207 | } |
| 208 | ||
| 209 | ||
| 210 | /** | |
| 211 | * Execute a SQL query with limits | |
| 212 | * | |
| 213 | * @param string SQL query to execute | |
| 214 | * @param number Offset for LIMIT statement | |
| 215 | * @param number Number of rows for LIMIT statement | |
| 216 | * @param mixed Values to be inserted in query | |
| 217 | * @return number Query handle identifier | |
| 218 | * @access public | |
| 219 | */ | |
| 220 | function limitquery() | |
| 221 | { | |
| 222 | $params = func_get_args(); | |
| 223 | $query = array_shift($params); | |
| 224 | $offset = array_shift($params); | |
| 225 | $numrows = array_shift($params); | |
| 226 | ||
| 227 | return $this->_query($query, $offset, $numrows, $params); | |
| 228 | } | |
| 229 | ||
| 230 | ||
| 231 | /** | |
| 232 | * Execute a SQL query with limits | |
| 233 | * | |
| 5c461b | 234 | * @param string $query SQL query to execute |
| A | 235 | * @param number $offset Offset for LIMIT statement |
| 236 | * @param number $numrows Number of rows for LIMIT statement | |
| 237 | * @param array $params Values to be inserted in query | |
| a004bb | 238 | * @return number Query handle identifier |
| A | 239 | * @access private |
| 240 | */ | |
| 241 | private function _query($query, $offset, $numrows, $params) | |
| 242 | { | |
| 243 | // Read or write ? | |
| 244 | $mode = (strtolower(substr(trim($query),0,6)) == 'select') ? 'r' : 'w'; | |
| 245 | ||
| 246 | $this->db_connect($mode); | |
| 247 | ||
| a3de4f | 248 | // check connection before proceeding |
| T | 249 | if (!$this->is_connected()) |
| 250 | return null; | |
| 251 | ||
| a004bb | 252 | if ($this->db_provider == 'sqlite') |
| A | 253 | $this->_sqlite_prepare(); |
| 254 | ||
| 255 | if ($numrows || $offset) | |
| 256 | $result = $this->db_handle->setLimit($numrows,$offset); | |
| 257 | ||
| 258 | if (empty($params)) | |
| 259 | $result = $mode == 'r' ? $this->db_handle->query($query) : $this->db_handle->exec($query); | |
| 260 | else { | |
| 261 | $params = (array)$params; | |
| 262 | $q = $this->db_handle->prepare($query, null, $mode=='w' ? MDB2_PREPARE_MANIP : null); | |
| 263 | if ($this->db_handle->isError($q)) { | |
| 264 | $this->db_error = true; | |
| 265 | $this->db_error_msg = $q->userinfo; | |
| 266 | ||
| 267 | raise_error(array('code' => 500, 'type' => 'db', | |
| 268 | 'line' => __LINE__, 'file' => __FILE__, | |
| 269 | 'message' => $this->db_error_msg), true, true); | |
| 270 | } | |
| 271 | else { | |
| 272 | $result = $q->execute($params); | |
| 273 | $q->free(); | |
| 274 | } | |
| 275 | } | |
| 276 | ||
| 277 | // add result, even if it's an error | |
| 278 | return $this->_add_result($result); | |
| 279 | } | |
| 280 | ||
| 281 | ||
| 282 | /** | |
| 283 | * Get number of rows for a SQL query | |
| 284 | * If no query handle is specified, the last query will be taken as reference | |
| 285 | * | |
| 5c461b | 286 | * @param number $res_id Optional query handle identifier |
| a004bb | 287 | * @return mixed Number of rows or false on failure |
| A | 288 | * @access public |
| 289 | */ | |
| 290 | function num_rows($res_id=null) | |
| 291 | { | |
| 292 | if (!$this->db_handle) | |
| 293 | return false; | |
| 294 | ||
| 295 | if ($result = $this->_get_result($res_id)) | |
| 296 | return $result->numRows(); | |
| 297 | else | |
| 298 | return false; | |
| 299 | } | |
| 300 | ||
| 301 | ||
| 302 | /** | |
| 303 | * Get number of affected rows for the last query | |
| 304 | * | |
| 5c461b | 305 | * @param number $res_id Optional query handle identifier |
| a004bb | 306 | * @return mixed Number of rows or false on failure |
| A | 307 | * @access public |
| 308 | */ | |
| 309 | function affected_rows($res_id = null) | |
| 310 | { | |
| 311 | if (!$this->db_handle) | |
| 312 | return false; | |
| 313 | ||
| 314 | return (int) $this->_get_result($res_id); | |
| 315 | } | |
| 316 | ||
| 317 | ||
| 318 | /** | |
| 319 | * Get last inserted record ID | |
| 320 | * For Postgres databases, a sequence name is required | |
| 321 | * | |
| 5c461b | 322 | * @param string $table Table name (to find the incremented sequence) |
| a004bb | 323 | * @return mixed ID or false on failure |
| A | 324 | * @access public |
| 325 | */ | |
| 326 | function insert_id($table = '') | |
| 327 | { | |
| 328 | if (!$this->db_handle || $this->db_mode == 'r') | |
| 329 | return false; | |
| 330 | ||
| 331 | if ($table) { | |
| 332 | if ($this->db_provider == 'pgsql') | |
| 333 | // find sequence name | |
| 334 | $table = get_sequence_name($table); | |
| 335 | else | |
| 336 | // resolve table name | |
| 337 | $table = get_table_name($table); | |
| 338 | } | |
| 339 | ||
| 340 | $id = $this->db_handle->lastInsertID($table); | |
| 05a7e3 | 341 | |
| a004bb | 342 | return $this->db_handle->isError($id) ? null : $id; |
| d2a9db | 343 | } |
| T | 344 | |
| 345 | ||
| a004bb | 346 | /** |
| A | 347 | * Get an associative array for one row |
| 348 | * If no query handle is specified, the last query will be taken as reference | |
| 349 | * | |
| 5c461b | 350 | * @param number $res_id Optional query handle identifier |
| a004bb | 351 | * @return mixed Array with col values or false on failure |
| A | 352 | * @access public |
| 353 | */ | |
| 354 | function fetch_assoc($res_id=null) | |
| d2a9db | 355 | { |
| a004bb | 356 | $result = $this->_get_result($res_id); |
| A | 357 | return $this->_fetch_row($result, MDB2_FETCHMODE_ASSOC); |
| d2a9db | 358 | } |
| T | 359 | |
| 360 | ||
| a004bb | 361 | /** |
| A | 362 | * Get an index array for one row |
| 363 | * If no query handle is specified, the last query will be taken as reference | |
| 364 | * | |
| 5c461b | 365 | * @param number $res_id Optional query handle identifier |
| a004bb | 366 | * @return mixed Array with col values or false on failure |
| A | 367 | * @access public |
| 368 | */ | |
| 369 | function fetch_array($res_id=null) | |
| d2a9db | 370 | { |
| a004bb | 371 | $result = $this->_get_result($res_id); |
| A | 372 | return $this->_fetch_row($result, MDB2_FETCHMODE_ORDERED); |
| d2a9db | 373 | } |
| T | 374 | |
| 375 | ||
| a004bb | 376 | /** |
| A | 377 | * Get col values for a result row |
| 378 | * | |
| 5c461b | 379 | * @param MDB2_Result_Common Query $result result handle |
| A | 380 | * @param number $mode Fetch mode identifier |
| a004bb | 381 | * @return mixed Array with col values or false on failure |
| A | 382 | * @access private |
| 383 | */ | |
| 384 | private function _fetch_row($result, $mode) | |
| d2a9db | 385 | { |
| a004bb | 386 | if ($result === false || PEAR::isError($result) || !$this->is_connected()) |
| A | 387 | return false; |
| d2a9db | 388 | |
| a004bb | 389 | return $result->fetchRow($mode); |
| d2a9db | 390 | } |
| T | 391 | |
| 392 | ||
| a004bb | 393 | /** |
| A | 394 | * Wrapper for the SHOW TABLES command |
| 395 | * | |
| 396 | * @return array List of all tables of the current database | |
| 397 | * @access public | |
| 398 | * @since 0.4-beta | |
| 399 | */ | |
| 400 | function list_tables() | |
| d2a9db | 401 | { |
| a004bb | 402 | // get tables if not cached |
| A | 403 | if (!$this->tables) { |
| 404 | $this->db_handle->loadModule('Manager'); | |
| 405 | if (!PEAR::isError($result = $this->db_handle->listTables())) | |
| 406 | $this->tables = $result; | |
| 407 | else | |
| 408 | $this->tables = array(); | |
| 409 | } | |
| d2a9db | 410 | |
| a004bb | 411 | return $this->tables; |
| d2a9db | 412 | } |
| T | 413 | |
| 414 | ||
| a004bb | 415 | /** |
| e6bb83 | 416 | * Wrapper for SHOW COLUMNS command |
| T | 417 | * |
| 418 | * @param string Table name | |
| 419 | * @return array List of table cols | |
| 420 | */ | |
| 421 | function list_cols($table) | |
| 422 | { | |
| 423 | $this->db_handle->loadModule('Manager'); | |
| 424 | if (!PEAR::isError($result = $this->db_handle->listTableFields($table))) { | |
| 425 | return $result; | |
| 426 | } | |
| 427 | ||
| 428 | return null; | |
| 429 | } | |
| 430 | ||
| 431 | ||
| 432 | /** | |
| a004bb | 433 | * Formats input so it can be safely used in a query |
| A | 434 | * |
| 5c461b | 435 | * @param mixed $input Value to quote |
| A | 436 | * @param string $type Type of data |
| a004bb | 437 | * @return string Quoted/converted string for use in query |
| A | 438 | * @access public |
| 439 | */ | |
| 440 | function quote($input, $type = null) | |
| 9c5bee | 441 | { |
| a004bb | 442 | // handle int directly for better performance |
| A | 443 | if ($type == 'integer') |
| 444 | return intval($input); | |
| 445 | ||
| 446 | // create DB handle if not available | |
| 447 | if (!$this->db_handle) | |
| 448 | $this->db_connect('r'); | |
| 449 | ||
| 450 | return $this->db_handle->quote($input, $type); | |
| 9c5bee | 451 | } |
| ccfda8 | 452 | |
| 10a699 | 453 | |
| a004bb | 454 | /** |
| A | 455 | * Quotes a string so it can be safely used as a table or column name |
| 456 | * | |
| 5c461b | 457 | * @param string $str Value to quote |
| a004bb | 458 | * @return string Quoted string for use in query |
| A | 459 | * @deprecated Replaced by rcube_MDB2::quote_identifier |
| 460 | * @see rcube_mdb2::quote_identifier | |
| 461 | * @access public | |
| 462 | */ | |
| 463 | function quoteIdentifier($str) | |
| f45ec7 | 464 | { |
| a004bb | 465 | return $this->quote_identifier($str); |
| f45ec7 | 466 | } |
| S | 467 | |
| a004bb | 468 | |
| A | 469 | /** |
| 470 | * Quotes a string so it can be safely used as a table or column name | |
| 471 | * | |
| 5c461b | 472 | * @param string $str Value to quote |
| a004bb | 473 | * @return string Quoted string for use in query |
| A | 474 | * @access public |
| 475 | */ | |
| 476 | function quote_identifier($str) | |
| 8f4dcb | 477 | { |
| a004bb | 478 | if (!$this->db_handle) |
| A | 479 | $this->db_connect('r'); |
| 480 | ||
| 481 | return $this->db_handle->quoteIdentifier($str); | |
| 482 | } | |
| 483 | ||
| 484 | ||
| 485 | /** | |
| 486 | * Escapes a string | |
| 487 | * | |
| 5c461b | 488 | * @param string $str The string to be escaped |
| a004bb | 489 | * @return string The escaped string |
| A | 490 | * @access public |
| 491 | * @since 0.1.1 | |
| 492 | */ | |
| 493 | function escapeSimple($str) | |
| 494 | { | |
| 495 | if (!$this->db_handle) | |
| 496 | $this->db_connect('r'); | |
| 05a7e3 | 497 | |
| a004bb | 498 | return $this->db_handle->escape($str); |
| 8f4dcb | 499 | } |
| T | 500 | |
| f45ec7 | 501 | |
| a004bb | 502 | /** |
| A | 503 | * Return SQL function for current time and date |
| 504 | * | |
| 505 | * @return string SQL function to use in query | |
| 506 | * @access public | |
| 507 | */ | |
| 508 | function now() | |
| 7139e3 | 509 | { |
| a004bb | 510 | switch($this->db_provider) { |
| A | 511 | case 'mssql': |
| 512 | case 'sqlsrv': | |
| 513 | return "getdate()"; | |
| 7139e3 | 514 | |
| a004bb | 515 | default: |
| A | 516 | return "now()"; |
| 517 | } | |
| 7139e3 | 518 | } |
| T | 519 | |
| 520 | ||
| a004bb | 521 | /** |
| A | 522 | * Return list of elements for use with SQL's IN clause |
| 523 | * | |
| 5c461b | 524 | * @param array $arr Input array |
| A | 525 | * @param string $type Type of data |
| a004bb | 526 | * @return string Comma-separated list of quoted values for use in query |
| A | 527 | * @access public |
| 528 | */ | |
| 529 | function array2list($arr, $type = null) | |
| ad84f9 | 530 | { |
| a004bb | 531 | if (!is_array($arr)) |
| A | 532 | return $this->quote($arr, $type); |
| 05a7e3 | 533 | |
| a004bb | 534 | foreach ($arr as $idx => $item) |
| A | 535 | $arr[$idx] = $this->quote($item, $type); |
| ad84f9 | 536 | |
| a004bb | 537 | return implode(',', $arr); |
| ad84f9 | 538 | } |
| A | 539 | |
| 540 | ||
| a004bb | 541 | /** |
| A | 542 | * Return SQL statement to convert a field value into a unix timestamp |
| 543 | * | |
| 5c461b | 544 | * @param string $field Field name |
| a004bb | 545 | * @return string SQL statement to use in query |
| A | 546 | * @access public |
| 547 | */ | |
| 548 | function unixtimestamp($field) | |
| f45ec7 | 549 | { |
| a004bb | 550 | switch($this->db_provider) { |
| A | 551 | case 'pgsql': |
| 552 | return "EXTRACT (EPOCH FROM $field)"; | |
| d2a9db | 553 | |
| a004bb | 554 | case 'mssql': |
| A | 555 | case 'sqlsrv': |
| 05a7e3 | 556 | return "DATEDIFF(second, '19700101', $field) + DATEDIFF(second, GETDATE(), GETUTCDATE())"; |
| 7139e3 | 557 | |
| a004bb | 558 | default: |
| A | 559 | return "UNIX_TIMESTAMP($field)"; |
| 560 | } | |
| f45ec7 | 561 | } |
| S | 562 | |
| 563 | ||
| a004bb | 564 | /** |
| A | 565 | * Return SQL statement to convert from a unix timestamp |
| 566 | * | |
| 5c461b | 567 | * @param string $timestamp Field name |
| a004bb | 568 | * @return string SQL statement to use in query |
| A | 569 | * @access public |
| 570 | */ | |
| 571 | function fromunixtime($timestamp) | |
| f45ec7 | 572 | { |
| a004bb | 573 | switch($this->db_provider) { |
| A | 574 | case 'mysqli': |
| 575 | case 'mysql': | |
| 576 | case 'sqlite': | |
| 577 | return sprintf("FROM_UNIXTIME(%d)", $timestamp); | |
| f45ec7 | 578 | |
| a004bb | 579 | default: |
| A | 580 | return date("'Y-m-d H:i:s'", $timestamp); |
| 581 | } | |
| f45ec7 | 582 | } |
| S | 583 | |
| d2a9db | 584 | |
| a004bb | 585 | /** |
| A | 586 | * Return SQL statement for case insensitive LIKE |
| 587 | * | |
| 5c461b | 588 | * @param string $column Field name |
| A | 589 | * @param string $value Search value |
| a004bb | 590 | * @return string SQL statement to use in query |
| A | 591 | * @access public |
| 592 | */ | |
| 593 | function ilike($column, $value) | |
| d8d416 | 594 | { |
| a004bb | 595 | // TODO: use MDB2's matchPattern() function |
| A | 596 | switch($this->db_provider) { |
| 597 | case 'pgsql': | |
| 598 | return $this->quote_identifier($column).' ILIKE '.$this->quote($value); | |
| 599 | default: | |
| 600 | return $this->quote_identifier($column).' LIKE '.$this->quote($value); | |
| 601 | } | |
| d8d416 | 602 | } |
| A | 603 | |
| 604 | ||
| a004bb | 605 | /** |
| A | 606 | * Encodes non-UTF-8 characters in string/array/object (recursive) |
| 607 | * | |
| 5c461b | 608 | * @param mixed $input Data to fix |
| a004bb | 609 | * @return mixed Properly UTF-8 encoded data |
| A | 610 | * @access public |
| 611 | */ | |
| 612 | function encode($input) | |
| ac6229 | 613 | { |
| a004bb | 614 | if (is_object($input)) { |
| A | 615 | foreach (get_object_vars($input) as $idx => $value) |
| 616 | $input->$idx = $this->encode($value); | |
| 617 | return $input; | |
| 618 | } | |
| 619 | else if (is_array($input)) { | |
| 620 | foreach ($input as $idx => $value) | |
| 621 | $input[$idx] = $this->encode($value); | |
| 622 | return $input; | |
| 623 | } | |
| ac6229 | 624 | |
| a004bb | 625 | return utf8_encode($input); |
| ac6229 | 626 | } |
| A | 627 | |
| 628 | ||
| a004bb | 629 | /** |
| A | 630 | * Decodes encoded UTF-8 string/object/array (recursive) |
| 631 | * | |
| 5c461b | 632 | * @param mixed $input Input data |
| a004bb | 633 | * @return mixed Decoded data |
| A | 634 | * @access public |
| 635 | */ | |
| 636 | function decode($input) | |
| ac6229 | 637 | { |
| a004bb | 638 | if (is_object($input)) { |
| A | 639 | foreach (get_object_vars($input) as $idx => $value) |
| 640 | $input->$idx = $this->decode($value); | |
| 641 | return $input; | |
| 642 | } | |
| 643 | else if (is_array($input)) { | |
| 644 | foreach ($input as $idx => $value) | |
| 645 | $input[$idx] = $this->decode($value); | |
| 646 | return $input; | |
| 647 | } | |
| ac6229 | 648 | |
| a004bb | 649 | return utf8_decode($input); |
| ac6229 | 650 | } |
| A | 651 | |
| 652 | ||
| a004bb | 653 | /** |
| A | 654 | * Adds a query result and returns a handle ID |
| 655 | * | |
| 5c461b | 656 | * @param object $res Query handle |
| a004bb | 657 | * @return mixed Handle ID |
| A | 658 | * @access private |
| 659 | */ | |
| 660 | private function _add_result($res) | |
| f45ec7 | 661 | { |
| a004bb | 662 | // sql error occured |
| A | 663 | if (PEAR::isError($res)) { |
| 664 | $this->db_error = true; | |
| 665 | $this->db_error_msg = $res->getMessage(); | |
| 666 | raise_error(array('code' => 500, 'type' => 'db', | |
| 05a7e3 | 667 | 'line' => __LINE__, 'file' => __FILE__, |
| A | 668 | 'message' => $res->getMessage() . " Query: " |
| 669 | . substr(preg_replace('/[\r\n]+\s*/', ' ', $res->userinfo), 0, 512)), | |
| 670 | true, false); | |
| a004bb | 671 | } |
| 05a7e3 | 672 | |
| a004bb | 673 | $res_id = sizeof($this->a_query_results); |
| A | 674 | $this->last_res_id = $res_id; |
| 675 | $this->a_query_results[$res_id] = $res; | |
| 676 | return $res_id; | |
| f45ec7 | 677 | } |
| d2a9db | 678 | |
| T | 679 | |
| a004bb | 680 | /** |
| A | 681 | * Resolves a given handle ID and returns the according query handle |
| 682 | * If no ID is specified, the last resource handle will be returned | |
| 683 | * | |
| 5c461b | 684 | * @param number $res_id Handle ID |
| a004bb | 685 | * @return mixed Resource handle or false on failure |
| A | 686 | * @access private |
| 687 | */ | |
| 688 | private function _get_result($res_id = null) | |
| d2a9db | 689 | { |
| a004bb | 690 | if ($res_id == null) |
| A | 691 | $res_id = $this->last_res_id; |
| d2a9db | 692 | |
| a004bb | 693 | if (isset($this->a_query_results[$res_id])) |
| A | 694 | if (!PEAR::isError($this->a_query_results[$res_id])) |
| 695 | return $this->a_query_results[$res_id]; | |
| 05a7e3 | 696 | |
| a004bb | 697 | return false; |
| d2a9db | 698 | } |
| T | 699 | |
| 700 | ||
| a004bb | 701 | /** |
| A | 702 | * Create a sqlite database from a file |
| 703 | * | |
| 5c461b | 704 | * @param MDB2 $dbh SQLite database handle |
| A | 705 | * @param string $file_name File path to use for DB creation |
| a004bb | 706 | * @access private |
| A | 707 | */ |
| 708 | private function _sqlite_create_database($dbh, $file_name) | |
| d2a9db | 709 | { |
| a004bb | 710 | if (empty($file_name) || !is_string($file_name)) |
| A | 711 | return; |
| d2a9db | 712 | |
| a004bb | 713 | $data = file_get_contents($file_name); |
| d2a9db | 714 | |
| a004bb | 715 | if (strlen($data)) |
| A | 716 | if (!sqlite_exec($dbh->connection, $data, $error) || MDB2::isError($dbh)) |
| 717 | raise_error(array('code' => 500, 'type' => 'db', | |
| 05a7e3 | 718 | 'line' => __LINE__, 'file' => __FILE__, |
| a004bb | 719 | 'message' => $error), true, false); |
| d2a9db | 720 | } |
| T | 721 | |
| 722 | ||
| a004bb | 723 | /** |
| A | 724 | * Add some proprietary database functions to the current SQLite handle |
| 725 | * in order to make it MySQL compatible | |
| 726 | * | |
| 727 | * @access private | |
| 728 | */ | |
| 729 | private function _sqlite_prepare() | |
| d2a9db | 730 | { |
| a004bb | 731 | include_once('include/rcube_sqlite.inc'); |
| d2a9db | 732 | |
| a004bb | 733 | // we emulate via callback some missing MySQL function |
| A | 734 | sqlite_create_function($this->db_handle->connection, |
| 735 | 'from_unixtime', 'rcube_sqlite_from_unixtime'); | |
| 736 | sqlite_create_function($this->db_handle->connection, | |
| 737 | 'unix_timestamp', 'rcube_sqlite_unix_timestamp'); | |
| 738 | sqlite_create_function($this->db_handle->connection, | |
| 739 | 'now', 'rcube_sqlite_now'); | |
| 740 | sqlite_create_function($this->db_handle->connection, | |
| 741 | 'md5', 'rcube_sqlite_md5'); | |
| d2a9db | 742 | } |
| T | 743 | |
| a004bb | 744 | } // end class rcube_db |
| f45ec7 | 745 | |
| 981472 | 746 | |
| T | 747 | /* this is our own debug handler for the MDB2 connection */ |
| 748 | function mdb2_debug_handler(&$db, $scope, $message, $context = array()) | |
| 749 | { | |
| a004bb | 750 | if ($scope != 'prepare') { |
| 860678 | 751 | $debug_output = sprintf('%s(%d): %s;', |
| A | 752 | $scope, $db->db_index, rtrim($message, ';')); |
| a004bb | 753 | write_log('sql', $debug_output); |
| A | 754 | } |
| 981472 | 755 | } |
| 05a7e3 | 756 | |
