phpBB/includes/db/mysqli.php

   1 <?php
   2 /**
   3 *
   4 * @package dbal
   5 * @version $Id$
   6 * @copyright (c) 2005 phpBB Group
   7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   8 *
   9 */
  10
  11 /**
  12 * @ignore
  13 */
  14 if (!defined('IN_PHPBB'))
  15 {
  16         exit;
  17 }
  18
  19 include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
  20
  21 /**
  22 * MySQLi Database Abstraction Layer
  23 * Compatible with:
  24 * MySQL 4.1+
  25 * MySQL 5.0+
  26 * @package dbal
  27 */
  28 class dbal_mysqli extends dbal
  29 {
  30         var $multi_insert = true;
  31
  32         // Supports multiple table deletion
  33         var $multi_table_deletion = true;
  34
  35         var $dbms_type = 'mysql';
  36
  37         /**
  38         * Connect to server
  39         */
  40         function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false , $new_link = false)
  41         {
  42                 $this->persistency = $persistency;
  43                 $this->user = $sqluser;
  44                 $this->server = $sqlserver;
  45                 $this->dbname = $database;
  46                 $port = (!$port) ? NULL : $port;
  47
  48                 // Persistant connections not supported by the mysqli extension?
  49                 $this->db_connect_id = @mysqli_connect($this->server, $this->user, $sqlpassword, $this->dbname, $port);
  50
  51                 if ($this->db_connect_id && $this->dbname != '')
  52                 {
  53                         @mysqli_query($this->db_connect_id, "SET NAMES 'utf8'");
  54                         // enforce strict mode on databases that support it
  55                         if (mysqli_get_server_version($this->db_connect_id) >= 50002)
  56                         {
  57                                 $result = @mysqli_query($this->db_connect_id, 'SELECT @@session.sql_mode AS sql_mode');
  58                                 $row = @mysqli_fetch_assoc($result);
  59                                 @mysqli_free_result($result);
  60                                 $modes = array_map('trim', explode(',', $row['sql_mode']));
  61
  62                                 // TRADITIONAL includes STRICT_ALL_TABLES and STRICT_TRANS_TABLES
  63                                 if (!in_array('TRADITIONAL', $modes))
  64                                 {
  65                                         if (!in_array('STRICT_ALL_TABLES', $modes))
  66                                         {
  67                                                 $modes[] = 'STRICT_ALL_TABLES';
  68                                         }
  69
  70                                         if (!in_array('STRICT_TRANS_TABLES', $modes))
  71                                         {
  72                                                 $modes[] = 'STRICT_TRANS_TABLES';
  73                                         }
  74                                 }
  75
  76                                 $mode = implode(',', $modes);
  77                                 @mysqli_query($this->db_connect_id, "SET SESSION sql_mode='{$mode}'");
  78                         }
  79                         return $this->db_connect_id;
  80                 }
  81
  82                 return $this->sql_error('');
  83         }
  84
  85         /**
  86         * Version information about used database
  87         */
  88         function sql_server_info()
  89         {
  90                 return 'MySQL(i) ' . @mysqli_get_server_info($this->db_connect_id);
  91         }
  92
  93         /**
  94         * SQL Transaction
  95         * @access private
  96         */
  97         function _sql_transaction($status = 'begin')
  98         {
  99                 switch ($status)
 100                 {
 101                         case 'begin':
 102                                 return @mysqli_autocommit($this->db_connect_id, false);
 103                         break;
 104
 105                         case 'commit':
 106                                 $result = @mysqli_commit($this->db_connect_id);
 107                                 @mysqli_autocommit($this->db_connect_id, true);
 108                                 return $result;
 109                         break;
 110
 111                         case 'rollback':
 112                                 $result = @mysqli_rollback($this->db_connect_id);
 113                                 @mysqli_autocommit($this->db_connect_id, true);
 114                                 return $result;
 115                         break;
 116                 }
 117
 118                 return true;
 119         }
 120
 121         /**
 122         * Base query method
 123         *
 124         * @param        string  $query          Contains the SQL query which shall be executed
 125         * @param        int             $cache_ttl      Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
 126         * @return       mixed                           When casted to bool the returned value returns true on success and false on failure
 127         *
 128         * @access       public
 129         */
 130         function sql_query($query = '', $cache_ttl = 0)
 131         {
 132                 if ($query != '')
 133                 {
 134                         global $cache;
 135
 136                         // EXPLAIN only in extra debug mode
 137                         if (defined('DEBUG_EXTRA'))
 138                         {
 139                                 $this->sql_report('start', $query);
 140                         }
 141
 142                         $this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
 143                         $this->sql_add_num_queries($this->query_result);
 144
 145                         if ($this->query_result === false)
 146                         {
 147                                 if (($this->query_result = @mysqli_query($this->db_connect_id, $query)) === false)
 148                                 {
 149                                         $this->sql_error($query);
 150                                 }
 151
 152                                 if (defined('DEBUG_EXTRA'))
 153                                 {
 154                                         $this->sql_report('stop', $query);
 155                                 }
 156
 157                                 if ($cache_ttl && method_exists($cache, 'sql_save'))
 158                                 {
 159                                         $cache->sql_save($query, $this->query_result, $cache_ttl);
 160                                 }
 161                         }
 162                         else if (defined('DEBUG_EXTRA'))
 163                         {
 164                                 $this->sql_report('fromcache', $query);
 165                         }
 166                 }
 167                 else
 168                 {
 169                         return false;
 170                 }
 171
 172                 return ($this->query_result) ? $this->query_result : false;
 173         }
 174
 175         /**
 176         * Build LIMIT query
 177         */
 178         function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
 179         {
 180                 $this->query_result = false;
 181
 182                 // if $total is set to 0 we do not want to limit the number of rows
 183                 if ($total == 0)
 184                 {
 185                         // MySQL 4.1+ no longer supports -1 in limit queries
 186                         $total = '18446744073709551615';
 187                 }
 188
 189                 $query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total);
 190
 191                 return $this->sql_query($query, $cache_ttl);
 192         }
 193
 194         /**
 195         * Return number of affected rows
 196         */
 197         function sql_affectedrows()
 198         {
 199                 return ($this->db_connect_id) ? @mysqli_affected_rows($this->db_connect_id) : false;
 200         }
 201
 202         /**
 203         * Fetch current row
 204         */
 205         function sql_fetchrow($query_id = false)
 206         {
 207                 global $cache;
 208
 209                 if ($query_id === false)
 210                 {
 211                         $query_id = $this->query_result;
 212                 }
 213
 214                 if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
 215                 {
 216                         return $cache->sql_fetchrow($query_id);
 217                 }
 218
 219                 return ($query_id !== false) ? @mysqli_fetch_assoc($query_id) : false;
 220         }
 221
 222         /**
 223         * Get last inserted id after insert statement
 224         */
 225         function sql_nextid()
 226         {
 227                 return ($this->db_connect_id) ? @mysqli_insert_id($this->db_connect_id) : false;
 228         }
 229
 230         /**
 231         * Free sql result
 232         */
 233         function sql_freeresult($query_id = false)
 234         {
 235                 global $cache;
 236
 237                 if ($query_id === false)
 238                 {
 239                         $query_id = $this->query_result;
 240                 }
 241
 242                 if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
 243                 {
 244                         return $cache->sql_freeresult($query_id);
 245                 }
 246
 247                 return @mysqli_free_result($query_id);
 248         }
 249
 250         /**
 251         * Escape string used in sql query
 252         */
 253         function sql_escape($msg)
 254         {
 255                 return @mysqli_real_escape_string($this->db_connect_id, $msg);
 256         }
 257
 258         /**
 259         * Expose a DBMS specific function
 260         */
 261         function sql_function($type, $col)
 262         {
 263                 switch ($type)
 264                 {
 265                         case 'length_varchar':
 266                         case 'length_text':
 267                                 return 'LENGTH(' . $col . ')';
 268                         break;
 269                 }
 270         }
 271
 272         function sql_handle_data($type, $table, $data, $where = '')
 273         {
 274                 if ($type === 'INSERT')
 275                 {
 276                         $stmt = mysqli_prepare($this->db_connect_id, "INSERT INTO $table (". implode(', ', array_keys($data)) . ") VALUES (" . substr(str_repeat('?, ', sizeof($data)) ,0, -1) . ')');
 277                 }
 278                 else
 279                 {
 280                         $query = "UPDATE $table SET ";
 281
 282                         $set = array();
 283                         foreach (array_keys($data) as $key)
 284                         {
 285                                 $set[] = "$key = ?";
 286                         }
 287                         $query .= implode(', ', $set);
 288
 289                         if ($where !== '')
 290                         {
 291                                 $query .= $where;
 292                         }
 293
 294                         $stmt = mysqli_prepare($this->db_connect_id, $query);
 295                 }
 296
 297                 // get the stmt onto the top of the function arguments
 298                 array_unshift($data, $stmt);
 299
 300                 call_user_func_array('mysqli_stmt_bind_param', $data);
 301                 mysqli_stmt_execute($stmt);
 302
 303                 mysqli_stmt_close($stmt);
 304         }
 305
 306
 307         /**
 308         * Build LIKE expression
 309         * @access private
 310         */
 311         function _sql_like_expression($expression)
 312         {
 313                 return $expression;
 314         }
 315
 316         /**
 317         * Build db-specific query data
 318         * @access private
 319         */
 320         function _sql_custom_build($stage, $data)
 321         {
 322                 switch ($stage)
 323                 {
 324                         case 'FROM':
 325                                 $data = '(' . $data . ')';
 326                         break;
 327                 }
 328
 329                 return $data;
 330         }
 331
 332         /**
 333         * return sql error array
 334         * @access private
 335         */
 336         function _sql_error()
 337         {
 338                 if (!$this->db_connect_id)
 339                 {
 340                         return array(
 341                                 'message'       => @mysqli_connect_error(),
 342                                 'code'          => @mysqli_connect_errno()
 343                         );
 344                 }
 345
 346                 return array(
 347                         'message'       => @mysqli_error($this->db_connect_id),
 348                         'code'          => @mysqli_errno($this->db_connect_id)
 349                 );
 350         }
 351
 352         /**
 353         * Close sql connection
 354         * @access private
 355         */
 356         function _sql_close()
 357         {
 358                 return @mysqli_close($this->db_connect_id);
 359         }
 360
 361         /**
 362         * Build db-specific report
 363         * @access private
 364         */
 365         function _sql_report($mode, $query = '')
 366         {
 367                 static $test_prof;
 368
 369                 // current detection method, might just switch to see the existance of INFORMATION_SCHEMA.PROFILING
 370                 if ($test_prof === null)
 371                 {
 372                         $test_prof = false;
 373                         if (strpos(mysqli_get_server_info($this->db_connect_id), 'community') !== false)
 374                         {
 375                                 $ver = mysqli_get_server_version($this->db_connect_id);
 376                                 if ($ver >= 50037 && $ver < 50100)
 377                                 {
 378                                         $test_prof = true;
 379                                 }
 380                         }
 381                 }
 382
 383                 switch ($mode)
 384                 {
 385                         case 'start':
 386
 387                                 $explain_query = $query;
 388                                 if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 389                                 {
 390                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 391                                 }
 392                                 else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 393                                 {
 394                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 395                                 }
 396
 397                                 if (preg_match('/^SELECT/', $explain_query))
 398                                 {
 399                                         $html_table = false;
 400
 401                                         // begin profiling
 402                                         if ($test_prof)
 403                                         {
 404                                                 @mysqli_query($this->db_connect_id, 'SET profiling = 1;');
 405                                         }
 406
 407                                         if ($result = @mysqli_query($this->db_connect_id, "EXPLAIN $explain_query"))
 408                                         {
 409                                                 while ($row = @mysqli_fetch_assoc($result))
 410                                                 {
 411                                                         $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 412                                                 }
 413                                         }
 414                                         @mysqli_free_result($result);
 415
 416                                         if ($html_table)
 417                                         {
 418                                                 $this->html_hold .= '</table>';
 419                                         }
 420
 421                                         if ($test_prof)
 422                                         {
 423                                                 $html_table = false;
 424
 425                                                 // get the last profile
 426                                                 if ($result = @mysqli_query($this->db_connect_id, 'SHOW PROFILE ALL;'))
 427                                                 {
 428                                                         $this->html_hold .= '<br />';
 429                                                         while ($row = @mysqli_fetch_assoc($result))
 430                                                         {
 431                                                                 // make <unknown> HTML safe
 432                                                                 if (!empty($row['Source_function']))
 433                                                                 {
 434                                                                         $row['Source_function'] = str_replace(array('<', '>'), array('&lt;', '&gt;'), $row['Source_function']);
 435                                                                 }
 436
 437                                                                 // remove unsupported features
 438                                                                 foreach ($row as $key => $val)
 439                                                                 {
 440                                                                         if ($val === null)
 441                                                                         {
 442                                                                                 unset($row[$key]);
 443                                                                         }
 444                                                                 }
 445                                                                 $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 446                                                         }
 447                                                 }
 448                                                 @mysqli_free_result($result);
 449
 450                                                 if ($html_table)
 451                                                 {
 452                                                         $this->html_hold .= '</table>';
 453                                                 }
 454
 455                                                 @mysqli_query($this->db_connect_id, 'SET profiling = 0;');
 456                                         }
 457                                 }
 458
 459                         break;
 460
 461                         case 'fromcache':
 462                                 $endtime = explode(' ', microtime());
 463                                 $endtime = $endtime[0] + $endtime[1];
 464
 465                                 $result = @mysqli_query($this->db_connect_id, $query);
 466                                 while ($void = @mysqli_fetch_assoc($result))
 467                                 {
 468                                         // Take the time spent on parsing rows into account
 469                                 }
 470                                 @mysqli_free_result($result);
 471
 472                                 $splittime = explode(' ', microtime());
 473                                 $splittime = $splittime[0] + $splittime[1];
 474
 475                                 $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 476
 477                         break;
 478                 }
 479         }
 480 }
 481
 482 ?>