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