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 /**
  20 * MySQLi Database Abstraction Layer
  21 * Compatible with:
  22 * MySQL 4.1+
  23 * MySQL 5.0+
  24 * @package dbal
  25 */
  26 class phpbb_dbal_mysqli extends phpbb_dbal
  27 {
  28         /**
  29         * @var string Database type. No distinction between versions or used extensions.
  30         */
  31         public $dbms_type = 'mysql';
  32
  33         /**
  34         * @var array Database type map, column layout information
  35         */
  36         public $dbms_type_map = array(
  37                 'INT:'          => 'int(%d)',
  38                 'BINT'          => 'bigint(20)',
  39                 'UINT'          => 'mediumint(8) UNSIGNED',
  40                 'UINT:'         => 'int(%d) UNSIGNED',
  41                 'TINT:'         => 'tinyint(%d)',
  42                 'USINT'         => 'smallint(4) UNSIGNED',
  43                 'BOOL'          => 'tinyint(1) UNSIGNED',
  44                 'VCHAR'         => 'varchar(255)',
  45                 'VCHAR:'        => 'varchar(%d)',
  46                 'CHAR:'         => 'char(%d)',
  47                 'XSTEXT'        => 'text',
  48                 'XSTEXT_UNI'=> 'varchar(100)',
  49                 'STEXT'         => 'text',
  50                 'STEXT_UNI'     => 'varchar(255)',
  51                 'TEXT'          => 'text',
  52                 'TEXT_UNI'      => 'text',
  53                 'MTEXT'         => 'mediumtext',
  54                 'MTEXT_UNI'     => 'mediumtext',
  55                 'TIMESTAMP'     => 'int(11) UNSIGNED',
  56                 'DECIMAL'       => 'decimal(5,2)',
  57                 'DECIMAL:'      => 'decimal(%d,2)',
  58                 'PDECIMAL'      => 'decimal(6,3)',
  59                 'PDECIMAL:'     => 'decimal(%d,3)',
  60                 'VCHAR_UNI'     => 'varchar(255)',
  61                 'VCHAR_UNI:'=> 'varchar(%d)',
  62                 'VARBINARY'     => 'varbinary(255)',
  63         );
  64
  65         /**
  66         * Connect to server. See {@link phpbb_dbal::sql_connect() sql_connect()} for details.
  67         */
  68         public function sql_connect($server, $user, $password, $database, $port = false, $persistency = false , $new_link = false)
  69         {
  70                 $this->persistency = $persistency;
  71                 $this->user = $user;
  72                 $this->server = $server;
  73                 $this->dbname = $database;
  74                 $this->port = (!$port) ? NULL : $port;
  75
  76                 // Persistant connections not supported by the mysqli extension?
  77                 $this->db_connect_id = @mysqli_connect($this->server, $this->user, $password, $this->dbname, $this->port);
  78
  79                 if (!$this->db_connect_id || !$this->dbname)
  80                 {
  81                         return $this->sql_error('');
  82                 }
  83
  84                 @mysqli_query($this->db_connect_id, "SET NAMES 'utf8'");
  85
  86                 // enforce strict mode on databases that support it
  87                 if (version_compare($this->sql_server_info(true), '5.0.2', '>='))
  88                 {
  89                         $result = @mysqli_query($this->db_connect_id, 'SELECT @@session.sql_mode AS sql_mode');
  90                         $row = @mysqli_fetch_assoc($result);
  91                         @mysqli_free_result($result);
  92
  93                         $modes = array_map('trim', explode(',', $row['sql_mode']));
  94
  95                         // TRADITIONAL includes STRICT_ALL_TABLES and STRICT_TRANS_TABLES
  96                         if (!in_array('TRADITIONAL', $modes))
  97                         {
  98                                 if (!in_array('STRICT_ALL_TABLES', $modes))
  99                                 {
 100                                         $modes[] = 'STRICT_ALL_TABLES';
 101                                 }
 102
 103                                 if (!in_array('STRICT_TRANS_TABLES', $modes))
 104                                 {
 105                                         $modes[] = 'STRICT_TRANS_TABLES';
 106                                 }
 107                         }
 108
 109                         $mode = implode(',', $modes);
 110                         @mysqli_query($this->db_connect_id, "SET SESSION sql_mode='{$mode}'");
 111                 }
 112
 113                 return $this->db_connect_id;
 114         }
 115
 116         /**
 117         * Version information about used database. See {@link phpbb_dbal::sql_server_info() sql_server_info()} for details.
 118         */
 119         public function sql_server_info($raw = false)
 120         {
 121                 if (!phpbb::registered('acm') || ($this->sql_server_version = phpbb::$acm->get('#mysqli_version')) === false)
 122                 {
 123                         $result = @mysqli_query($this->db_connect_id, 'SELECT VERSION() AS version');
 124                         $row = @mysqli_fetch_assoc($result);
 125                         @mysqli_free_result($result);
 126
 127                         $this->sql_server_version = trim($row['version']);
 128
 129                         if (phpbb::registered('acm'))
 130                         {
 131                                 phpbb::$acm->put('#mysqli_version', $this->sql_server_version);
 132                         }
 133                 }
 134
 135                 return ($raw) ? $this->sql_server_version : 'MySQL(i) ' . $this->sql_server_version;
 136         }
 137
 138         /**
 139         * DB-specific base query method. See {@link phpbb_dbal::_sql_query() _sql_query()} for details.
 140         */
 141         protected function _sql_query($query)
 142         {
 143                 return @mysqli_query($this->db_connect_id, $query);
 144         }
 145
 146         /**
 147         * Build LIMIT query and run it. See {@link phpbb_dbal::_sql_query_limit() _sql_query_limit()} for details.
 148         */
 149         protected function _sql_query_limit($query, $total, $offset, $cache_ttl)
 150         {
 151                 // if $total is set to 0 we do not want to limit the number of rows
 152                 if ($total == 0)
 153                 {
 154                         // MySQL 4.1+ no longer supports -1 in limit queries
 155                         $total = '18446744073709551615';
 156                 }
 157
 158                 $query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total);
 159                 return $this->sql_query($query, $cache_ttl);
 160         }
 161
 162         /**
 163         * Close sql connection. See {@link phpbb_dbal::_sql_close() _sql_close()} for details.
 164         */
 165         protected function _sql_close()
 166         {
 167                 return @mysqli_close($this->db_connect_id);
 168         }
 169
 170         /**
 171         * SQL Transaction. See {@link phpbb_dbal::_sql_transaction() _sql_transaction()} for details.
 172         */
 173         protected function _sql_transaction($status)
 174         {
 175                 switch ($status)
 176                 {
 177                         case 'begin':
 178                                 return @mysqli_autocommit($this->db_connect_id, false);
 179                         break;
 180
 181                         case 'commit':
 182                                 $result = @mysqli_commit($this->db_connect_id);
 183                                 @mysqli_autocommit($this->db_connect_id, true);
 184                                 return $result;
 185                         break;
 186
 187                         case 'rollback':
 188                                 $result = @mysqli_rollback($this->db_connect_id);
 189                                 @mysqli_autocommit($this->db_connect_id, true);
 190                                 return $result;
 191                         break;
 192                 }
 193
 194                 return true;
 195         }
 196
 197         /**
 198         * Return number of affected rows. See {@link phpbb_dbal::sql_affectedrows() sql_affectedrows()} for details.
 199         */
 200         public function sql_affectedrows()
 201         {
 202                 return ($this->db_connect_id) ? @mysqli_affected_rows($this->db_connect_id) : false;
 203         }
 204
 205         /**
 206         * Get last inserted id after insert statement. See {@link phpbb_dbal::sql_nextid() sql_nextid()} for details.
 207         */
 208         public function sql_nextid()
 209         {
 210                 return ($this->db_connect_id) ? @mysqli_insert_id($this->db_connect_id) : false;
 211         }
 212
 213         /**
 214         * Fetch current row. See {@link phpbb_dbal::_sql_fetchrow() _sql_fetchrow()} for details.
 215         */
 216         protected function _sql_fetchrow($query_id)
 217         {
 218                 return @mysqli_fetch_assoc($query_id);
 219         }
 220
 221         /**
 222         * Free query result. See {@link phpbb_dbal::_sql_freeresult() _sql_freeresult()} for details.
 223         */
 224         protected function _sql_freeresult($query_id)
 225         {
 226                 return @mysqli_free_result($query_id);
 227         }
 228
 229         /**
 230         * Correctly adjust LIKE expression for special characters. See {@link phpbb_dbal::_sql_like_expression() _sql_like_expression()} for details.
 231         */
 232         protected function _sql_like_expression($expression)
 233         {
 234                 return $expression;
 235         }
 236
 237         /**
 238         * Escape string used in sql query. See {@link phpbb_dbal::sql_escape() sql_escape()} for details.
 239         */
 240         public function sql_escape($msg)
 241         {
 242                 return @mysqli_real_escape_string($this->db_connect_id, $msg);
 243         }
 244
 245         /**
 246         * Expose a DBMS specific function. See {@link phpbb_dbal::sql_function() sql_function()} for details.
 247         */
 248         public function sql_function($type, $col)
 249         {
 250                 switch ($type)
 251                 {
 252                         case 'length_varchar':
 253                         case 'length_text':
 254                                 return 'LENGTH(' . $col . ')';
 255                         break;
 256                 }
 257         }
 258
 259         /**
 260         * Handle data by using prepared statements. See {@link phpbb_dbal::sql_handle_data() sql_handle_data()} for details.
 261         public function sql_handle_data($type, $table, $data, $where = '')
 262         {
 263                 if ($type === 'INSERT')
 264                 {
 265                         $stmt = mysqli_prepare($this->db_connect_id, "INSERT INTO $table (". implode(', ', array_keys($data)) . ") VALUES (" . substr(str_repeat('?, ', sizeof($data)) ,0, -1) . ')');
 266                 }
 267                 else
 268                 {
 269                         $query = "UPDATE $table SET ";
 270
 271                         $set = array();
 272                         foreach (array_keys($data) as $key)
 273                         {
 274                                 $set[] = "$key = ?";
 275                         }
 276                         $query .= implode(', ', $set);
 277
 278                         if ($where !== '')
 279                         {
 280                                 $query .= ' WHERE ' . $where;
 281                         }
 282
 283                         $stmt = mysqli_prepare($this->db_connect_id, $query);
 284                 }
 285
 286                 // get the stmt onto the top of the function arguments
 287                 array_unshift($data, $stmt);
 288
 289                 call_user_func_array('mysqli_stmt_bind_param', $data);
 290                 mysqli_stmt_execute($stmt);
 291
 292                 mysqli_stmt_close($stmt);
 293         }
 294 */
 295
 296         /**
 297         * Build DB-specific query bits. See {@link phpbb_dbal::_sql_custom_build() _sql_custom_build()} for details.
 298         */
 299         protected function _sql_custom_build($stage, $data)
 300         {
 301                 switch ($stage)
 302                 {
 303                         case 'FROM':
 304                                 $data = '(' . $data . ')';
 305                         break;
 306                 }
 307
 308                 return $data;
 309         }
 310
 311         /**
 312         * return sql error array. See {@link phpbb_dbal::_sql_error() _sql_error()} for details.
 313         */
 314         protected function _sql_error()
 315         {
 316                 if (!$this->db_connect_id)
 317                 {
 318                         return array(
 319                                 'message'       => @mysqli_connect_error(),
 320                                 'code'          => @mysqli_connect_errno()
 321                         );
 322                 }
 323
 324                 return array(
 325                         'message'       => @mysqli_error($this->db_connect_id),
 326                         'code'          => @mysqli_errno($this->db_connect_id)
 327                 );
 328         }
 329
 330         /**
 331         * Run DB-specific code to build SQL Report to explain queries, show statistics and runtime information. See {@link phpbb_dbal::_sql_report() _sql_report()} for details.
 332         */
 333         protected function _sql_report($mode, $query = '')
 334         {
 335                 static $test_prof;
 336                 static $test_extend;
 337
 338                 // current detection method, might just switch to see the existance of INFORMATION_SCHEMA.PROFILING
 339                 if ($test_prof === null)
 340                 {
 341                         $test_prof = $test_extend = false;
 342                         if (version_compare($this->sql_server_info(true), '5.0.37', '>=') && version_compare($this->sql_server_info(true), '5.1', '<'))
 343                         {
 344                                 $test_prof = true;
 345                         }
 346
 347                         if (version_compare($this->sql_server_info(true), '4.1.1', '>='))
 348                         {
 349                                 $test_extend = true;
 350                         }
 351                 }
 352
 353                 switch ($mode)
 354                 {
 355                         case 'start':
 356
 357                                 $explain_query = $query;
 358                                 if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 359                                 {
 360                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 361                                 }
 362                                 else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 363                                 {
 364                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 365                                 }
 366
 367                                 if (preg_match('/^SELECT/', $explain_query))
 368                                 {
 369                                         $html_table = false;
 370
 371                                         // begin profiling
 372                                         if ($test_prof)
 373                                         {
 374                                                 @mysqli_query($this->db_connect_id, 'SET profiling = 1;');
 375                                         }
 376
 377                                         if ($result = @mysqli_query($this->db_connect_id, 'EXPLAIN ' . (($test_extend) ? 'EXTENDED ' : '') . "$explain_query"))
 378                                         {
 379                                                 while ($row = @mysqli_fetch_assoc($result))
 380                                                 {
 381                                                         $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 382                                                 }
 383                                         }
 384                                         @mysqli_free_result($result);
 385
 386                                         if ($html_table)
 387                                         {
 388                                                 $this->html_hold .= '</table>';
 389                                         }
 390
 391                                         if ($test_extend)
 392                                         {
 393                                                 $html_table = false;
 394
 395                                                 if ($result = @mysqli_query($this->db_connect_id, 'SHOW WARNINGS'))
 396                                                 {
 397                                                         $this->html_hold .= '<br />';
 398                                                         while ($row = @mysqli_fetch_assoc($result))
 399                                                         {
 400                                                                 $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 401                                                         }
 402                                                 }
 403                                                 @mysqli_free_result($result);
 404
 405                                                 if ($html_table)
 406                                                 {
 407                                                         $this->html_hold .= '</table>';
 408                                                 }
 409                                         }
 410
 411                                         if ($test_prof)
 412                                         {
 413                                                 $html_table = false;
 414
 415                                                 // get the last profile
 416                                                 if ($result = @mysqli_query($this->db_connect_id, 'SHOW PROFILE ALL;'))
 417                                                 {
 418                                                         $this->html_hold .= '<br />';
 419                                                         while ($row = @mysqli_fetch_assoc($result))
 420                                                         {
 421                                                                 // make <unknown> HTML safe
 422                                                                 if (!empty($row['Source_function']))
 423                                                                 {
 424                                                                         $row['Source_function'] = str_replace(array('<', '>'), array('&lt;', '&gt;'), $row['Source_function']);
 425                                                                 }
 426
 427                                                                 // remove unsupported features
 428                                                                 foreach ($row as $key => $val)
 429                                                                 {
 430                                                                         if ($val === null)
 431                                                                         {
 432                                                                                 unset($row[$key]);
 433                                                                         }
 434                                                                 }
 435                                                                 $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 436                                                         }
 437                                                 }
 438                                                 @mysqli_free_result($result);
 439
 440                                                 if ($html_table)
 441                                                 {
 442                                                         $this->html_hold .= '</table>';
 443                                                 }
 444
 445                                                 @mysqli_query($this->db_connect_id, 'SET profiling = 0;');
 446                                         }
 447                                 }
 448
 449                         break;
 450
 451                         case 'fromcache':
 452                                 $endtime = explode(' ', microtime());
 453                                 $endtime = $endtime[0] + $endtime[1];
 454
 455                                 $result = @mysqli_query($this->db_connect_id, $query);
 456                                 while ($void = @mysqli_fetch_assoc($result))
 457                                 {
 458                                         // Take the time spent on parsing rows into account
 459                                 }
 460                                 @mysqli_free_result($result);
 461
 462                                 $splittime = explode(' ', microtime());
 463                                 $splittime = $splittime[0] + $splittime[1];
 464
 465                                 $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 466
 467                         break;
 468                 }
 469         }
 470 }
 471
 472 ?>