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