phpBB/includes/db/mysql.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 * MySQL Database Abstraction Layer
  21 * Compatible with:
  22 * MySQL 4.1+
  23 * MySQL 5.0+
  24 * @package dbal
  25 */
  26 class phpbb_dbal_mysql 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 . (($port) ? ':' . $port : '');
  73                 $this->dbname = $database;
  74                 $this->port = $port;
  75
  76                 $this->db_connect_id = ($this->persistency) ? @mysql_pconnect($this->server, $this->user, $password, $new_link) : @mysql_connect($this->server, $this->user, $password, $new_link);
  77
  78                 if (!$this->db_connect_id || !$this->dbname)
  79                 {
  80                         return $this->sql_error('');
  81                 }
  82
  83                 if (!@mysql_select_db($this->dbname, $this->db_connect_id))
  84                 {
  85                         return $this->sql_error('');
  86                 }
  87
  88                 @mysql_query("SET NAMES 'utf8'", $this->db_connect_id);
  89
  90                 // enforce strict mode on databases that support it
  91                 if (version_compare($this->sql_server_info(true), '5.0.2', '>='))
  92                 {
  93                         $result = @mysql_query('SELECT @@session.sql_mode AS sql_mode', $this->db_connect_id);
  94                         $row = @mysql_fetch_assoc($result);
  95                         @mysql_free_result($result);
  96
  97                         $modes = array_map('trim', explode(',', $row['sql_mode']));
  98
  99                         // TRADITIONAL includes STRICT_ALL_TABLES and STRICT_TRANS_TABLES
 100                         if (!in_array('TRADITIONAL', $modes))
 101                         {
 102                                 if (!in_array('STRICT_ALL_TABLES', $modes))
 103                                 {
 104                                         $modes[] = 'STRICT_ALL_TABLES';
 105                                 }
 106
 107                                 if (!in_array('STRICT_TRANS_TABLES', $modes))
 108                                 {
 109                                         $modes[] = 'STRICT_TRANS_TABLES';
 110                                 }
 111                         }
 112
 113                         $mode = implode(',', $modes);
 114                         @mysql_query("SET SESSION sql_mode='{$mode}'", $this->db_connect_id);
 115                 }
 116
 117                 return $this->db_connect_id;
 118         }
 119
 120         /**
 121         * Version information about used database. See {@link phpbb_dbal::sql_server_info() sql_server_info()} for details.
 122         */
 123         public function sql_server_info($raw = false)
 124         {
 125                 if (!phpbb::registered('acm') || ($this->sql_server_version = phpbb::$acm->get('#mysql_version')) === false)
 126                 {
 127                         $result = @mysql_query('SELECT VERSION() AS version', $this->db_connect_id);
 128                         $row = @mysql_fetch_assoc($result);
 129                         @mysql_free_result($result);
 130
 131                         $this->sql_server_version = trim($row['version']);
 132
 133                         if (phpbb::registered('acm'))
 134                         {
 135                                 phpbb::$acm->put('#mysql_version', $this->sql_server_version);
 136                         }
 137                 }
 138
 139                 return ($raw) ? $this->sql_server_version : 'MySQL ' . $this->sql_server_version;
 140         }
 141
 142         /**
 143         * DB-specific base query method. See {@link phpbb_dbal::_sql_query() _sql_query()} for details.
 144         */
 145         protected function _sql_query($query)
 146         {
 147                 return @mysql_query($query, $this->db_connect_id);
 148         }
 149
 150         /**
 151         * Build LIMIT query and run it. See {@link phpbb_dbal::_sql_query_limit() _sql_query_limit()} for details.
 152         */
 153         protected function _sql_query_limit($query, $total, $offset, $cache_ttl)
 154         {
 155                 // if $total is set to 0 we do not want to limit the number of rows
 156                 if ($total == 0)
 157                 {
 158                         // MySQL 4.1+ no longer supports -1 in limit queries
 159                         $total = '18446744073709551615';
 160                 }
 161
 162                 $query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total);
 163                 return $this->sql_query($query, $cache_ttl);
 164         }
 165
 166         /**
 167         * Close sql connection. See {@link phpbb_dbal::_sql_close() _sql_close()} for details.
 168         */
 169         protected function _sql_close()
 170         {
 171                 return @mysql_close($this->db_connect_id);
 172         }
 173
 174         /**
 175         * SQL Transaction. See {@link phpbb_dbal::_sql_transaction() _sql_transaction()} for details.
 176         */
 177         protected function _sql_transaction($status)
 178         {
 179                 switch ($status)
 180                 {
 181                         case 'begin':
 182                                 return @mysql_query('BEGIN', $this->db_connect_id);
 183                         break;
 184
 185                         case 'commit':
 186                                 return @mysql_query('COMMIT', $this->db_connect_id);
 187                         break;
 188
 189                         case 'rollback':
 190                                 return @mysql_query('ROLLBACK', $this->db_connect_id);
 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) ? @mysql_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) ? @mysql_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 @mysql_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 @mysql_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                 if (!$this->db_connect_id)
 243                 {
 244                         return @mysql_real_escape_string($msg);
 245                 }
 246
 247                 return @mysql_real_escape_string($msg, $this->db_connect_id);
 248         }
 249
 250         /**
 251         * Expose a DBMS specific function. See {@link phpbb_dbal::sql_function() sql_function()} for details.
 252         */
 253         public function sql_function($type, $col)
 254         {
 255                 switch ($type)
 256                 {
 257                         case 'length_varchar':
 258                         case 'length_text':
 259                                 return 'LENGTH(' . $col . ')';
 260                         break;
 261                 }
 262         }
 263
 264         /**
 265         * Handle data by using prepared statements. See {@link phpbb_dbal::sql_handle_data() sql_handle_data()} for details.
 266         public function sql_handle_data($type, $table, $data, $where = '')
 267         {
 268         }
 269         */
 270
 271         /**
 272         * Build DB-specific query bits. See {@link phpbb_dbal::_sql_custom_build() _sql_custom_build()} for details.
 273         */
 274         protected function _sql_custom_build($stage, $data)
 275         {
 276                 switch ($stage)
 277                 {
 278                         case 'FROM':
 279                                 $data = '(' . $data . ')';
 280                         break;
 281                 }
 282
 283                 return $data;
 284         }
 285
 286         /**
 287         * return sql error array. See {@link phpbb_dbal::_sql_error() _sql_error()} for details.
 288         */
 289         protected function _sql_error()
 290         {
 291                 if (!$this->db_connect_id)
 292                 {
 293                         return array(
 294                                 'message'       => @mysql_error(),
 295                                 'code'          => @mysql_errno()
 296                         );
 297                 }
 298
 299                 return array(
 300                         'message'       => @mysql_error($this->db_connect_id),
 301                         'code'          => @mysql_errno($this->db_connect_id)
 302                 );
 303         }
 304
 305         /**
 306         * 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.
 307         */
 308         protected function _sql_report($mode, $query = '')
 309         {
 310                 static $test_prof;
 311                 static $test_extend;
 312
 313                 // current detection method, might just switch to see the existance of INFORMATION_SCHEMA.PROFILING
 314                 if ($test_prof === null)
 315                 {
 316                         $test_prof = $test_extend = false;
 317                         if (version_compare($this->sql_server_info(true), '5.0.37', '>=') && version_compare($this->sql_server_info(true), '5.1', '<'))
 318                         {
 319                                 $test_prof = true;
 320                         }
 321
 322                         if (version_compare($this->sql_server_info(true), '4.1.1', '>='))
 323                         {
 324                                 $test_extend = true;
 325                         }
 326                 }
 327
 328                 switch ($mode)
 329                 {
 330                         case 'start':
 331
 332                                 $explain_query = $query;
 333                                 if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 334                                 {
 335                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 336                                 }
 337                                 else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
 338                                 {
 339                                         $explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
 340                                 }
 341
 342                                 if (preg_match('/^SELECT/', $explain_query))
 343                                 {
 344                                         $html_table = false;
 345
 346                                         // begin profiling
 347                                         if ($test_prof)
 348                                         {
 349                                                 @mysql_query('SET profiling = 1;', $this->db_connect_id);
 350                                         }
 351
 352                                         if ($result = @mysql_query('EXPLAIN ' . (($test_extend) ? 'EXTENDED ' : '') . "$explain_query", $this->db_connect_id))
 353                                         {
 354                                                 while ($row = @mysql_fetch_assoc($result))
 355                                                 {
 356                                                         $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 357                                                 }
 358                                         }
 359                                         @mysql_free_result($result);
 360
 361                                         if ($html_table)
 362                                         {
 363                                                 $this->html_hold .= '</table>';
 364                                         }
 365
 366                                         if ($test_extend)
 367                                         {
 368                                                 $html_table = false;
 369
 370                                                 if ($result = @mysql_query('SHOW WARNINGS', $this->db_connect_id))
 371                                                 {
 372                                                         $this->html_hold .= '<br />';
 373                                                         while ($row = @mysql_fetch_assoc($result))
 374                                                         {
 375                                                                 $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 376                                                         }
 377                                                 }
 378                                                 @mysql_free_result($result);
 379
 380                                                 if ($html_table)
 381                                                 {
 382                                                         $this->html_hold .= '</table>';
 383                                                 }
 384                                         }
 385
 386                                         if ($test_prof)
 387                                         {
 388                                                 $html_table = false;
 389
 390                                                 // get the last profile
 391                                                 if ($result = @mysql_query('SHOW PROFILE ALL;', $this->db_connect_id))
 392                                                 {
 393                                                         $this->html_hold .= '<br />';
 394                                                         while ($row = @mysql_fetch_assoc($result))
 395                                                         {
 396                                                                 // make <unknown> HTML safe
 397                                                                 if (!empty($row['Source_function']))
 398                                                                 {
 399                                                                         $row['Source_function'] = str_replace(array('<', '>'), array('&lt;', '&gt;'), $row['Source_function']);
 400                                                                 }
 401
 402                                                                 // remove unsupported features
 403                                                                 foreach ($row as $key => $val)
 404                                                                 {
 405                                                                         if ($val === null)
 406                                                                         {
 407                                                                                 unset($row[$key]);
 408                                                                         }
 409                                                                 }
 410                                                                 $html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
 411                                                         }
 412                                                 }
 413                                                 @mysql_free_result($result);
 414
 415                                                 if ($html_table)
 416                                                 {
 417                                                         $this->html_hold .= '</table>';
 418                                                 }
 419
 420                                                 @mysql_query('SET profiling = 0;', $this->db_connect_id);
 421                                         }
 422                                 }
 423
 424                         break;
 425
 426                         case 'fromcache':
 427                                 $endtime = explode(' ', microtime());
 428                                 $endtime = $endtime[0] + $endtime[1];
 429
 430                                 $result = @mysql_query($query, $this->db_connect_id);
 431                                 while ($void = @mysql_fetch_assoc($result))
 432                                 {
 433                                         // Take the time spent on parsing rows into account
 434                                 }
 435                                 @mysql_free_result($result);
 436
 437                                 $splittime = explode(' ', microtime());
 438                                 $splittime = $splittime[0] + $splittime[1];
 439
 440                                 $this->sql_report('record_fromcache', $query, $endtime, $splittime);
 441
 442                         break;
 443                 }
 444         }
 445 }
 446
 447 ?>