[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * MyBB 1.6
   4   * Copyright 2010 MyBB Group, All Rights Reserved
   5   *
   6   * Website: http://mybb.com
   7   * License: http://mybb.com/about/license
   8   *
   9   * $Id: db_mysql.php 5297 2010-12-28 22:01:14Z Tomm $
  10   */
  11  
  12  class DB_MySQL
  13  {
  14      /**
  15       * The title of this layer.
  16       *
  17       * @var string
  18       */
  19      public $title = "MySQL";
  20      
  21      /**
  22       * The short title of this layer.
  23       *
  24       * @var string
  25       */
  26      public $short_title = "MySQL";
  27      
  28      /**
  29       * The type of db software being used.
  30       *
  31       * @var string
  32       */
  33      public $type;
  34  
  35      /**
  36       * A count of the number of queries.
  37       *
  38       * @var int
  39       */
  40      public $query_count = 0;
  41  
  42      /**
  43       * A list of the performed queries.
  44       *
  45       * @var array
  46       */
  47      public $querylist = array();
  48  
  49      /**
  50       * 1 if error reporting enabled, 0 if disabled.
  51       *
  52       * @var boolean
  53       */
  54      public $error_reporting = 1;
  55  
  56      /**
  57       * The read database connection resource.
  58       *
  59       * @var resource
  60       */
  61      public $read_link;
  62      
  63      /**
  64       * The write database connection resource
  65       *
  66       * @var resource
  67       */
  68      public $write_link;
  69      
  70      /**
  71       * Reference to the last database connection resource used.
  72       *
  73       * @var resource
  74       */
  75      public $current_link;
  76  
  77      /**
  78       * Explanation of a query.
  79       *
  80       * @var string
  81       */
  82      public $explain;
  83  
  84      /**
  85       * The current version of MySQL.
  86       *
  87       * @var string
  88       */
  89      public $version;
  90  
  91      /**
  92       * The current table type in use (myisam/innodb)
  93       *
  94       * @var string
  95       */
  96      public $table_type = "myisam";
  97  
  98      /**
  99       * The table prefix used for simple select, update, insert and delete queries
 100       *
 101       * @var string
 102       */
 103      public $table_prefix;
 104      
 105      /**
 106       * The extension used to run the SQL database
 107       *
 108       * @var string
 109       */
 110      public $engine = "mysql";
 111      
 112      /**
 113       * Weather or not this engine can use the search functionality
 114       *
 115       * @var boolean
 116       */
 117      public $can_search = true;
 118  
 119      /**
 120       * The database encoding currently in use (if supported)
 121       *
 122       * @var string
 123       */
 124      public $db_encoding = "utf8";
 125  
 126      /**
 127       * The time spent performing queries
 128       *
 129       * @var float
 130       */
 131      public $query_time = 0;
 132      
 133      /**
 134       * Connect to the database server.
 135       *
 136       * @param array Array of DBMS connection details.
 137       * @return resource The DB connection resource. Returns false on fail or -1 on a db connect failure.
 138       */
 139  	function connect($config)
 140      {
 141          // Simple connection to one server
 142          if(array_key_exists('hostname', $config))
 143          {
 144              $connections['read'][] = $config;
 145          }
 146          // Connecting to more than one server
 147          else
 148          {
 149              // Specified multiple servers, but no specific read/write servers
 150              if(!array_key_exists('read', $config))
 151              {
 152                  foreach($config as $key => $settings)
 153                  {
 154                      if(is_int($key)) $connections['read'][] = $settings;
 155                  }
 156              }
 157              // Specified both read & write servers
 158              else
 159              {
 160                  $connections = $config;
 161              }
 162          }
 163  
 164          $this->db_encoding = $config['encoding'];
 165  
 166          // Actually connect to the specified servers
 167          foreach(array('read', 'write') as $type)
 168          {
 169              if(!is_array($connections[$type]))
 170              {
 171                  break;
 172              }
 173              
 174              if(array_key_exists('hostname', $connections[$type]))
 175              {
 176                  $details = $connections[$type];
 177                  unset($connections);
 178                  $connections[$type][] = $details;
 179              }
 180  
 181              // Shuffle the connections
 182              shuffle($connections[$type]);
 183  
 184              // Loop-de-loop
 185              foreach($connections[$type] as $single_connection)
 186              {
 187                  $connect_function = "mysql_connect";
 188                  if($single_connection['pconnect'])
 189                  {
 190                      $connect_function = "mysql_pconnect";
 191                  }
 192                  
 193                  $link = $type."_link";
 194  
 195                  $this->get_execution_time();
 196  
 197                  $this->$link = @$connect_function($single_connection['hostname'], $single_connection['username'], $single_connection['password'], 1);
 198  
 199                  $time_spent = $this->get_execution_time();
 200                  $this->query_time += $time_spent;
 201  
 202                  // Successful connection? break down brother!
 203                  if($this->$link)
 204                  {
 205                      $this->connections[] = "[".strtoupper($type)."] {$single_connection['username']}@{$single_connection['hostname']} (Connected in ".number_format($time_spent, 0)."s)";
 206                      break;
 207                  }
 208                  else
 209                  {
 210                      $this->connections[] = "<span style=\"color: red\">[FAILED] [".strtoupper($type)."] {$single_connection['username']}@{$single_connection['hostname']}</span>";
 211                  }
 212              }
 213          }
 214  
 215          // No write server was specified (simple connection or just multiple servers) - mirror write link
 216          if(!array_key_exists('write', $connections))
 217          {
 218              $this->write_link = &$this->read_link;
 219          }
 220  
 221          // Have no read connection?
 222          if(!$this->read_link)
 223          {
 224              $this->error("[READ] Unable to connect to MySQL server");
 225              return false;
 226          }
 227          // No write?
 228          else if(!$this->write_link)
 229          {
 230              $this->error("[WRITE] Unable to connect to MySQL server");
 231              return false;
 232          }
 233  
 234          // Select databases
 235          if(!$this->select_db($config['database']))
 236          {
 237              return -1;
 238          }
 239  
 240          $this->current_link = &$this->read_link;
 241          return $this->read_link;
 242      }
 243  
 244      /**
 245       * Selects the database to use.
 246       *
 247       * @param string The database name.
 248       * @return boolean True when successfully connected, false if not.
 249       */
 250  	function select_db($database)
 251      {
 252          global $mybb;
 253          
 254          $this->current_link = &$this->read_link;
 255          $read_success = @mysql_select_db($database, $this->read_link) or $this->error("[READ] Unable to select database", $this->read_link);
 256          if($this->write_link)
 257          {
 258              $this->current_link = &$this->write_link;
 259              $write_success = @mysql_select_db($database, $this->write_link) or $this->error("[WRITE] Unable to select database", $this->write_link);
 260              $success = ($read_success && $write_success ? true : false);
 261          }
 262          else
 263          {
 264              $success = $read_success;
 265          }
 266          
 267          if($success && $this->db_encoding)
 268          {
 269              $this->query("SET NAMES '{$this->db_encoding}'");
 270              if($write_success && count($this->connections) > 1)
 271              {
 272                  $this->write_query("SET NAMES '{$this->db_encoding}'");
 273              }
 274          }
 275          return $success;
 276      }
 277      
 278      /**
 279       * Query the database.
 280       *
 281       * @param string The query SQL.
 282       * @param integer 1 if hide errors, 0 if not.
 283       * @param integer 1 if executes on slave database, 0 if not.
 284       * @return resource The query data.
 285       */
 286  	function query($string, $hide_errors=0, $write_query=0)
 287      {
 288          global $pagestarttime, $db, $mybb;
 289  
 290          $this->get_execution_time();
 291          
 292          // Only execute write queries on slave database
 293          if($write_query && $this->write_link)
 294          {
 295              $this->current_link = &$this->write_link;
 296              $query = @mysql_query($string, $this->write_link);
 297          }
 298          else
 299          {
 300              $this->current_link = &$this->read_link;
 301              $query = @mysql_query($string, $this->read_link);
 302          }
 303  
 304          if($this->error_number() && !$hide_errors)
 305          {
 306               $this->error($string);
 307               exit;
 308          }
 309  
 310          $query_time = $this->get_execution_time();
 311          $this->query_time += $query_time;
 312          $this->query_count++;
 313          
 314          if($mybb->debug_mode)
 315          {
 316              $this->explain_query($string, $query_time);
 317          }
 318          
 319          return $query;
 320      }
 321  
 322      /**
 323       * Execute a write query on the slave database
 324       *
 325       * @param string The query SQL.
 326       * @param boolean 1 if hide errors, 0 if not.
 327       * @return resource The query data.
 328       */
 329  	function write_query($query, $hide_errors=0)
 330      {
 331          return $this->query($query, $hide_errors, 1);
 332      }
 333  
 334      /**
 335       * Explain a query on the database.
 336       *
 337       * @param string The query SQL.
 338       * @param string The time it took to perform the query.
 339       */
 340  	function explain_query($string, $qtime)
 341      {
 342          global $plugins;
 343          if($plugins->current_hook)
 344          {
 345              $debug_extra = "<div style=\"float_right\">(Plugin Hook: {$plugins->current_hook})</div>";
 346          }
 347          if(preg_match("#^\s*select#i", $string))
 348          {
 349              $query = mysql_query("EXPLAIN $string", $this->current_link);
 350              $this->explain .= "<table style=\"background-color: #666;\" width=\"95%\" cellpadding=\"4\" cellspacing=\"1\" align=\"center\">\n".
 351                  "<tr>\n".
 352                  "<td colspan=\"8\" style=\"background-color: #ccc;\">{$debug_extra}<div><strong>#".$this->query_count." - Select Query</strong></div></td>\n".
 353                  "</tr>\n".
 354                  "<tr>\n".
 355                  "<td colspan=\"8\" style=\"background-color: #fefefe;\"><span style=\"font-family: Courier; font-size: 14px;\">".$string."</span></td>\n".
 356                  "</tr>\n".
 357                  "<tr style=\"background-color: #efefef;\">\n".
 358                  "<td><strong>table</strong></td>\n".
 359                  "<td><strong>type</strong></td>\n".
 360                  "<td><strong>possible_keys</strong></td>\n".
 361                  "<td><strong>key</strong></td>\n".
 362                  "<td><strong>key_len</strong></td>\n".
 363                  "<td><strong>ref</strong></td>\n".
 364                  "<td><strong>rows</strong></td>\n".
 365                  "<td><strong>Extra</strong></td>\n".
 366                  "</tr>\n";
 367  
 368              while($table = mysql_fetch_array($query))
 369              {
 370                  $this->explain .=
 371                      "<tr bgcolor=\"#ffffff\">\n".
 372                      "<td>".$table['table']."</td>\n".
 373                      "<td>".$table['type']."</td>\n".
 374                      "<td>".$table['possible_keys']."</td>\n".
 375                      "<td>".$table['key']."</td>\n".
 376                      "<td>".$table['key_len']."</td>\n".
 377                      "<td>".$table['ref']."</td>\n".
 378                      "<td>".$table['rows']."</td>\n".
 379                      "<td>".$table['Extra']."</td>\n".
 380                      "</tr>\n";
 381              }
 382              $this->explain .=
 383                  "<tr>\n".
 384                  "<td colspan=\"8\" style=\"background-color: #fff;\">Query Time: ".$qtime."</td>\n".
 385                  "</tr>\n".
 386                  "</table>\n".
 387                  "<br />\n";
 388          }
 389          else
 390          {
 391              $this->explain .= "<table style=\"background-color: #666;\" width=\"95%\" cellpadding=\"4\" cellspacing=\"1\" align=\"center\">\n".
 392                  "<tr>\n".
 393                  "<td style=\"background-color: #ccc;\">{$debug_extra}<div><strong>#".$this->query_count." - Write Query</strong></div></td>\n".
 394                  "</tr>\n".
 395                  "<tr style=\"background-color: #fefefe;\">\n".
 396                  "<td><span style=\"font-family: Courier; font-size: 14px;\">".htmlspecialchars_uni($string)."</span></td>\n".
 397                  "</tr>\n".
 398                  "<tr>\n".
 399                  "<td bgcolor=\"#ffffff\">Query Time: ".$qtime."</td>\n".
 400                  "</tr>\n".
 401                  "</table>\n".
 402                  "<br />\n";
 403          }
 404  
 405          $this->querylist[$this->query_count]['query'] = $string;
 406          $this->querylist[$this->query_count]['time'] = $qtime;
 407      }
 408  
 409  
 410      /**
 411       * Return a result array for a query.
 412       *
 413       * @param resource The query ID.
 414       * @param constant The type of array to return.
 415       * @return array The array of results.
 416       */
 417  	function fetch_array($query)
 418      {
 419          $array = mysql_fetch_assoc($query);
 420          return $array;
 421      }
 422  
 423      /**
 424       * Return a specific field from a query.
 425       *
 426       * @param resource The query ID.
 427       * @param string The name of the field to return.
 428       * @param int The number of the row to fetch it from.
 429       */
 430  	function fetch_field($query, $field, $row=false)
 431      {
 432          if($row === false)
 433          {
 434              $array = $this->fetch_array($query);
 435              return $array[$field];
 436          }
 437          else
 438          {
 439              return mysql_result($query, $row, $field);
 440          }
 441      }
 442  
 443      /**
 444       * Moves internal row pointer to the next row
 445       *
 446       * @param resource The query ID.
 447       * @param int The pointer to move the row to.
 448       */
 449  	function data_seek($query, $row)
 450      {
 451          return mysql_data_seek($query, $row);
 452      }
 453  
 454      /**
 455       * Return the number of rows resulting from a query.
 456       *
 457       * @param resource The query ID.
 458       * @return int The number of rows in the result.
 459       */
 460  	function num_rows($query)
 461      {
 462          return mysql_num_rows($query);
 463      }
 464  
 465      /**
 466       * Return the last id number of inserted data.
 467       *
 468       * @return int The id number.
 469       */
 470  	function insert_id()
 471      {
 472          return mysql_insert_id($this->current_link);
 473      }
 474  
 475      /**
 476       * Close the connection with the DBMS.
 477       *
 478       */
 479  	function close()
 480      {
 481          @mysql_close($this->read_link);
 482          if($this->write_link)
 483          {
 484              @mysql_close($this->write_link);
 485          }
 486      }
 487  
 488      /**
 489       * Return an error number.
 490       *
 491       * @return int The error number of the current error.
 492       */
 493  	function error_number()
 494      {
 495          if($this->current_link)
 496          {
 497              return @mysql_errno($this->current_link);
 498          }
 499          else
 500          {
 501              return @mysql_errno();
 502          }
 503      }
 504  
 505      /**
 506       * Return an error string.
 507       *
 508       * @return string The explanation for the current error.
 509       */
 510  	function error_string()
 511      {
 512          if($this->current_link)
 513          {
 514              return @mysql_error($this->current_link);
 515          }
 516          else
 517          {
 518              return @mysql_error();
 519          }
 520      }
 521  
 522      /**
 523       * Output a database error.
 524       *
 525       * @param string The string to present as an error.
 526       */
 527  	function error($string="")
 528      {
 529          if($this->error_reporting)
 530          {
 531              if(class_exists("errorHandler"))
 532              {
 533                  global $error_handler;
 534                  
 535                  if(!is_object($error_handler))
 536                  {
 537                      require_once  MYBB_ROOT."inc/class_error.php";
 538                      $error_handler = new errorHandler();
 539                  }
 540                  
 541                  $error = array(
 542                      "error_no" => $this->error_number(),
 543                      "error" => $this->error_string(),
 544                      "query" => $string
 545                  );
 546                  $error_handler->error(MYBB_SQL, $error);
 547              }
 548              else
 549              {
 550                  trigger_error("<strong>[SQL] [".$this->error_number()."] ".$this->error_string()."</strong><br />{$string}", E_USER_ERROR);
 551              }
 552          }
 553          else
 554          {
 555              return false;
 556          }
 557      }
 558  
 559  
 560      /**
 561       * Returns the number of affected rows in a query.
 562       *
 563       * @return int The number of affected rows.
 564       */
 565  	function affected_rows()
 566      {
 567          return mysql_affected_rows($this->current_link);
 568      }
 569  
 570      /**
 571       * Return the number of fields.
 572       *
 573       * @param resource The query ID.
 574       * @return int The number of fields.
 575       */
 576  	function num_fields($query)
 577      {
 578          return mysql_num_fields($query);
 579      }
 580  
 581      /**
 582       * Lists all functions in the database.
 583       *
 584       * @param string The database name.
 585       * @param string Prefix of the table (optional)
 586       * @return array The table list.
 587       */
 588  	function list_tables($database, $prefix='')
 589      {
 590          if($prefix)
 591          {
 592              $query = $this->query("SHOW TABLES FROM `$database` LIKE '".$this->escape_string($prefix)."%'");
 593          }
 594          else
 595          {
 596              $query = $this->query("SHOW TABLES FROM `$database`");
 597          }
 598          
 599          while(list($table) = mysql_fetch_array($query))
 600          {
 601              $tables[] = $table;
 602          }
 603          
 604          return $tables;
 605      }
 606  
 607      /**
 608       * Check if a table exists in a database.
 609       *
 610       * @param string The table name.
 611       * @return boolean True when exists, false if not.
 612       */
 613  	function table_exists($table)
 614      {
 615          // Execute on master server to ensure if we've just created a table that we get the correct result
 616          $query = $this->write_query("
 617              SHOW TABLES 
 618              LIKE '{$this->table_prefix}$table'
 619          ");
 620          $exists = $this->num_rows($query);
 621          if($exists > 0)
 622          {
 623              return true;
 624          }
 625          else
 626          {
 627              return false;
 628          }
 629      }
 630  
 631      /**
 632       * Check if a field exists in a database.
 633       *
 634       * @param string The field name.
 635       * @param string The table name.
 636       * @return boolean True when exists, false if not.
 637       */
 638  	function field_exists($field, $table)
 639      {
 640          $query = $this->write_query("
 641              SHOW COLUMNS 
 642              FROM {$this->table_prefix}$table 
 643              LIKE '$field'
 644          ");
 645          $exists = $this->num_rows($query);
 646          if($exists > 0)
 647          {
 648              return true;
 649          }
 650          else
 651          {
 652              return false;
 653          }
 654      }
 655  
 656      /**
 657       * Add a shutdown query.
 658       *
 659       * @param resource The query data.
 660       * @param string An optional name for the query.
 661       */
 662  	function shutdown_query($query, $name=0)
 663      {
 664          global $shutdown_queries;
 665          if($name)
 666          {
 667              $shutdown_queries[$name] = $query;
 668          }
 669          else
 670          {
 671              $shutdown_queries[] = $query;
 672          }
 673      }
 674      /**
 675       * Performs a simple select query.
 676       *
 677       * @param string The table name to be queried.
 678       * @param string Comma delimetered list of fields to be selected.
 679       * @param string SQL formatted list of conditions to be matched.
 680       * @param array List of options, order by, order direction, limit, limit start.
 681       * @return resource The query data.
 682       */
 683  	function simple_select($table, $fields="*", $conditions="", $options=array())
 684      {
 685          $query = "SELECT ".$fields." FROM {$this->table_prefix}{$table}";
 686          if($conditions != "")
 687          {
 688              $query .= " WHERE ".$conditions;
 689          }
 690          if(isset($options['order_by']))
 691          {
 692              $query .= " ORDER BY ".$options['order_by'];
 693              if(isset($options['order_dir']))
 694              {
 695                  $query .= " ".my_strtoupper($options['order_dir']);
 696              }
 697          }
 698          if(isset($options['limit_start']) && isset($options['limit']))
 699          {
 700              $query .= " LIMIT ".$options['limit_start'].", ".$options['limit'];
 701          }
 702          elseif(isset($options['limit']))
 703          {
 704              $query .= " LIMIT ".$options['limit'];
 705          }
 706          return $this->query($query);
 707      }
 708      
 709      /**
 710       * Build an insert query from an array.
 711       *
 712       * @param string The table name to perform the query on.
 713       * @param array An array of fields and their values.
 714       * @return int The insert ID if available
 715       */
 716  	function insert_query($table, $array)
 717      {
 718          if(!is_array($array))
 719          {
 720              return false;
 721          }
 722          $fields = "`".implode("`,`", array_keys($array))."`";
 723          $values = implode("','", $array);
 724          $this->write_query("
 725              INSERT 
 726              INTO {$this->table_prefix}{$table} (".$fields.") 
 727              VALUES ('".$values."')
 728          ");
 729          return $this->insert_id();
 730      }
 731      
 732      /**
 733       * Build one query for multiple inserts from a multidimensional array.
 734       *
 735       * @param string The table name to perform the query on.
 736       * @param array An array of inserts.
 737       * @return int The insert ID if available
 738       */
 739  	function insert_query_multiple($table, $array)
 740      {
 741          if(!is_array($array))
 742          {
 743              return false;
 744          }
 745          // Field names
 746          $fields = array_keys($array[0]);
 747          $fields = "`".implode("`,`", $fields)."`";
 748  
 749          $insert_rows = array();
 750          foreach($array as $values)
 751          {
 752              $insert_rows[] = "('".implode("','", $values)."')";
 753          }
 754          $insert_rows = implode(", ", $insert_rows);
 755  
 756          $this->write_query("
 757              INSERT 
 758              INTO {$this->table_prefix}{$table} ({$fields}) 
 759              VALUES {$insert_rows}
 760          ");
 761      }
 762  
 763      /**
 764       * Build an update query from an array.
 765       *
 766       * @param string The table name to perform the query on.
 767       * @param array An array of fields and their values.
 768       * @param string An optional where clause for the query.
 769       * @param string An optional limit clause for the query.
 770       * @param boolean An option to quote incoming values of the array.
 771       * @return resource The query data.
 772       */
 773  	function update_query($table, $array, $where="", $limit="", $no_quote=false)
 774      {
 775          if(!is_array($array))
 776          {
 777              return false;
 778          }
 779          
 780          $comma = "";
 781          $query = "";
 782          $quote = "'";
 783          
 784          if($no_quote == true)
 785          {
 786              $quote = "";
 787          }
 788          
 789          foreach($array as $field => $value)
 790          {
 791              $query .= $comma."`".$field."`={$quote}{$value}{$quote}";
 792              $comma = ', ';
 793          }
 794          
 795          if(!empty($where))
 796          {
 797              $query .= " WHERE $where";
 798          }
 799          
 800          if(!empty($limit))
 801          {
 802              $query .= " LIMIT $limit";
 803          }
 804          
 805          return $this->write_query("
 806              UPDATE {$this->table_prefix}$table 
 807              SET $query
 808          ");
 809      }
 810  
 811      /**
 812       * Build a delete query.
 813       *
 814       * @param string The table name to perform the query on.
 815       * @param string An optional where clause for the query.
 816       * @param string An optional limit clause for the query.
 817       * @return resource The query data.
 818       */
 819  	function delete_query($table, $where="", $limit="")
 820      {
 821          $query = "";
 822          if(!empty($where))
 823          {
 824              $query .= " WHERE $where";
 825          }
 826          
 827          if(!empty($limit))
 828          {
 829              $query .= " LIMIT $limit";
 830          }
 831          
 832          return $this->write_query("
 833              DELETE 
 834              FROM {$this->table_prefix}$table 
 835              $query
 836          ");
 837      }
 838  
 839      /**
 840  
 841       * Escape a string according to the MySQL escape format.
 842       *
 843       * @param string The string to be escaped.
 844       * @return string The escaped string.
 845       */
 846  	function escape_string($string)
 847      {
 848          if(function_exists("mysql_real_escape_string") && $this->read_link)
 849          {
 850              $string = mysql_real_escape_string($string, $this->read_link);
 851          }
 852          else
 853          {
 854              $string = addslashes($string);
 855          }
 856          return $string;
 857      }
 858      
 859      /**
 860       * Frees the resources of a MySQLi query.
 861       *
 862       * @param object The query to destroy.
 863       * @return boolean Returns true on success, false on faliure
 864       */
 865  	function free_result($query)
 866      {
 867          return mysql_free_result($query);
 868      }
 869  
 870      /**
 871       * Escape a string used within a like command.
 872       *
 873       * @param string The string to be escaped.
 874       * @return string The escaped string.
 875       */
 876  	function escape_string_like($string)
 877      {
 878          return $this->escape_string(str_replace(array('%', '_') , array('\\%' , '\\_') , $string));
 879      }
 880  
 881      /**
 882       * Gets the current version of MySQL.
 883       *
 884       * @return string Version of MySQL.
 885       */
 886  	function get_version()
 887      {
 888          if($this->version)
 889          {
 890              return $this->version;
 891          }
 892          $query = $this->query("SELECT VERSION() as version");
 893          $ver = $this->fetch_array($query);
 894          if($ver['version'])
 895          {
 896              $version = explode(".", $ver['version'], 3);
 897              $this->version = intval($version[0]).".".intval($version[1]).".".intval($version[2]);
 898          }
 899          return $this->version;
 900      }
 901  
 902      /**
 903       * Optimizes a specific table.
 904       *
 905       * @param string The name of the table to be optimized.
 906       */
 907  	function optimize_table($table)
 908      {
 909          $this->write_query("OPTIMIZE TABLE {$this->table_prefix}{$table}");
 910      }
 911      
 912      /**
 913       * Analyzes a specific table.
 914       *
 915       * @param string The name of the table to be analyzed.
 916       */
 917  	function analyze_table($table)
 918      {
 919          $this->write_query("ANALYZE TABLE {$this->table_prefix}{$table}");
 920      }
 921  
 922      /**
 923       * Show the "create table" command for a specific table.
 924       *
 925       * @param string The name of the table.
 926       * @return string The MySQL command to create the specified table.
 927       */
 928  	function show_create_table($table)
 929      {
 930          $query = $this->write_query("SHOW CREATE TABLE {$this->table_prefix}{$table}");
 931          $structure = $this->fetch_array($query);
 932          return $structure['Create Table'];
 933      }
 934  
 935      /**
 936       * Show the "show fields from" command for a specific table.
 937       *
 938       * @param string The name of the table.
 939       * @return string Field info for that table
 940       */
 941  	function show_fields_from($table)
 942      {
 943          $query = $this->write_query("SHOW FIELDS FROM {$this->table_prefix}{$table}");
 944          while($field = $this->fetch_array($query))
 945          {
 946              $field_info[] = $field;
 947          }
 948          return $field_info;
 949      }
 950  
 951      /**
 952       * Returns whether or not the table contains a fulltext index.
 953       *
 954       * @param string The name of the table.
 955       * @param string Optionally specify the name of the index.
 956       * @return boolean True or false if the table has a fulltext index or not.
 957       */
 958  	function is_fulltext($table, $index="")
 959      {
 960          $structure = $this->show_create_table($table);
 961          if($index != "")
 962          {
 963              if(preg_match("#FULLTEXT KEY (`?)$index(`?)#i", $structure))
 964              {
 965                  return true;
 966              }
 967              else
 968              {
 969                  return false;
 970              }
 971          }
 972          if(preg_match('#FULLTEXT KEY#i', $structure))
 973          {
 974              return true;
 975          }
 976          return false;
 977      }
 978  
 979      /**
 980       * Returns whether or not this database engine supports fulltext indexing.
 981       *
 982       * @param string The table to be checked.
 983       * @return boolean True or false if supported or not.
 984       */
 985  
 986  	function supports_fulltext($table)
 987      {
 988          $version = $this->get_version();
 989          $query = $this->write_query("SHOW TABLE STATUS LIKE '{$this->table_prefix}$table'");
 990          $status = $this->fetch_array($query);
 991          if($status['Engine'])
 992          {
 993              $table_type = my_strtoupper($status['Engine']);
 994          }
 995          else
 996          {
 997              $table_type = my_strtoupper($status['Type']);
 998          }
 999          if($version >= '3.23.23' && $table_type == 'MYISAM')
1000          {
1001              return true;
1002          }
1003          return false;
1004      }
1005      
1006      /**
1007       * Checks to see if an index exists on a specified table
1008       *
1009       * @param string The name of the table.
1010       * @param string The name of the index.
1011       */
1012  	function index_exists($table, $index)
1013      {
1014          $index_exists = false;
1015          $query = $this->write_query("SHOW INDEX FROM {$this->table_prefix}{$table}");
1016          while($ukey = $this->fetch_array($query))
1017          {
1018              if($ukey['Key_name'] == $index)
1019              {
1020                  $index_exists = true;
1021                  break;
1022              }
1023          }
1024          
1025          if($index_exists)
1026          {
1027              return true;
1028          }
1029          
1030          return false;
1031      }
1032  
1033      /**
1034       * Returns whether or not this database engine supports boolean fulltext matching.
1035       *
1036       * @param string The table to be checked.
1037       * @return boolean True or false if supported or not.
1038       */
1039  	function supports_fulltext_boolean($table)
1040      {
1041          $version = $this->get_version();
1042          $supports_fulltext = $this->supports_fulltext($table);
1043          if($version >= '4.0.1' && $supports_fulltext == true)
1044          {
1045              return true;
1046          }
1047          return false;
1048      }
1049  
1050      /**
1051       * Creates a fulltext index on the specified column in the specified table with optional index name.
1052       *
1053       * @param string The name of the table.
1054       * @param string Name of the column to be indexed.
1055       * @param string The index name, optional.
1056       */
1057  	function create_fulltext_index($table, $column, $name="")
1058      {
1059          $this->write_query("
1060              ALTER TABLE {$this->table_prefix}$table 
1061              ADD FULLTEXT $name ($column)
1062          ");
1063      }
1064  
1065      /**
1066       * Drop an index with the specified name from the specified table
1067       *
1068       * @param string The name of the table.
1069       * @param string The name of the index.
1070       */
1071  	function drop_index($table, $name)
1072      {
1073          $this->write_query("
1074              ALTER TABLE {$this->table_prefix}$table 
1075              DROP INDEX $name
1076          ");
1077      }
1078      
1079      /**
1080       * Drop an table with the specified table
1081       *
1082       * @param boolean hard drop - no checking
1083       * @param boolean use table prefix
1084       */
1085  	function drop_table($table, $hard=false, $table_prefix=true)
1086      {
1087          if($table_prefix == false)
1088          {
1089              $table_prefix = "";
1090          }
1091          else
1092          {
1093              $table_prefix = $this->table_prefix;
1094          }
1095          
1096          if($hard == false)
1097          {
1098              $this->write_query('DROP TABLE IF EXISTS '.$table_prefix.$table);
1099          }
1100          else
1101          {
1102              $this->write_query('DROP TABLE '.$table_prefix.$table);
1103          }
1104      }
1105      
1106      /**
1107       * Replace contents of table with values
1108       *
1109       * @param string The table
1110       * @param array The replacements
1111       */
1112  	function replace_query($table, $replacements=array())
1113      {
1114          $values = '';
1115          $comma = '';
1116          foreach($replacements as $column => $value)
1117          {
1118              $values .= $comma."`".$column."`='".$value."'";
1119              
1120              $comma = ',';
1121          }
1122          
1123          if(empty($replacements))
1124          {
1125               return false;
1126          }
1127          
1128          return $this->write_query("REPLACE INTO {$this->table_prefix}{$table} SET {$values}");
1129      }
1130      
1131      /**
1132       * Drops a column
1133       *
1134       * @param string The table
1135       * @param string The column name
1136       */
1137  	function drop_column($table, $column)
1138      {
1139          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} DROP {$column}");
1140      }
1141      
1142      /**
1143       * Adds a column
1144       *
1145       * @param string The table
1146       * @param string The column name
1147       * @param string the new column definition
1148       */
1149  	function add_column($table, $column, $definition)
1150      {
1151          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} ADD {$column} {$definition}");
1152      }
1153      
1154      /**
1155       * Modifies a column
1156       *
1157       * @param string The table
1158       * @param string The column name
1159       * @param string the new column definition
1160       */
1161  	function modify_column($table, $column, $new_definition)
1162      {
1163          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} MODIFY {$column} {$new_definition}");
1164      }
1165      
1166      /**
1167       * Renames a column
1168       *
1169       * @param string The table
1170       * @param string The old column name
1171       * @param string the new column name
1172       * @param string the new column definition
1173       */
1174  	function rename_column($table, $old_column, $new_column, $new_definition)
1175      {
1176          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} CHANGE {$old_column} {$new_column} {$new_definition}");
1177      }
1178      
1179      /**
1180       * Sets the table prefix used by the simple select, insert, update and delete functions
1181       *
1182       * @param string The new table prefix
1183       */
1184  	function set_table_prefix($prefix)
1185      {
1186          $this->table_prefix = $prefix;
1187      }
1188      
1189      /**
1190       * Fetched the total size of all mysql tables or a specific table
1191       *
1192       * @param string The table (optional)
1193       * @return integer the total size of all mysql tables or a specific table
1194       */
1195  	function fetch_size($table='')
1196      {
1197          if($table != '')
1198          {
1199              $query = $this->query("SHOW TABLE STATUS LIKE '".$this->table_prefix.$table."'");
1200          }
1201          else
1202          {
1203              $query = $this->query("SHOW TABLE STATUS");
1204          }
1205          $total = 0;
1206          while($table = $this->fetch_array($query))
1207          {
1208              $total += $table['Data_length']+$table['Index_length'];
1209          }
1210          return $total;
1211      }
1212  
1213      /**
1214       * Fetch a list of database character sets this DBMS supports
1215       *
1216       * @return array Array of supported character sets with array key being the name, array value being display name. False if unsupported
1217       */
1218  	function fetch_db_charsets()
1219      {
1220          if($this->link && version_compare($this->get_version(), "4.1", "<"))
1221          {
1222              return false;
1223          }
1224          return array(
1225              'big5' => 'Big5 Traditional Chinese',
1226              'dec8' => 'DEC West European',
1227              'cp850' => 'DOS West European',
1228              'hp8' => 'HP West European',
1229              'koi8r' => 'KOI8-R Relcom Russian',
1230              'latin1' => 'cp1252 West European',
1231              'latin2' => 'ISO 8859-2 Central European',
1232              'swe7' => '7bit Swedish',
1233              'ascii' => 'US ASCII',
1234              'ujis' => 'EUC-JP Japanese',
1235              'sjis' => 'Shift-JIS Japanese',
1236              'hebrew' => 'ISO 8859-8 Hebrew',
1237              'tis620' => 'TIS620 Thai',
1238              'euckr' => 'EUC-KR Korean',
1239              'koi8u' => 'KOI8-U Ukrainian',
1240              'gb2312' => 'GB2312 Simplified Chinese',
1241              'greek' => 'ISO 8859-7 Greek',
1242              'cp1250' => 'Windows Central European',
1243              'gbk' => 'GBK Simplified Chinese',
1244              'latin5' => 'ISO 8859-9 Turkish',
1245              'armscii8' => 'ARMSCII-8 Armenian',
1246              'utf8' => 'UTF-8 Unicode',
1247              'ucs2' => 'UCS-2 Unicode',
1248              'cp866' => 'DOS Russian',
1249              'keybcs2' => 'DOS Kamenicky Czech-Slovak',
1250              'macce' => 'Mac Central European',
1251              'macroman' => 'Mac West European',
1252              'cp852' => 'DOS Central European',
1253              'latin7' => 'ISO 8859-13 Baltic',
1254              'cp1251' => 'Windows Cyrillic',
1255              'cp1256' => 'Windows Arabic',
1256              'cp1257' => 'Windows Baltic',
1257              'binary' => 'Binary pseudo charset',
1258              'geostd8' => 'GEOSTD8 Georgian',
1259              'cp932' => 'SJIS for Windows Japanese',
1260              'eucjpms' => 'UJIS for Windows Japanese',
1261          );
1262      }
1263  
1264      /**
1265       * Fetch a database collation for a particular database character set
1266       *
1267       * @param string The database character set
1268       * @return string The matching database collation, false if unsupported
1269       */
1270  	function fetch_charset_collation($charset)
1271      {
1272          $collations = array(
1273              'big5' => 'big5_chinese_ci',
1274              'dec8' => 'dec8_swedish_ci',
1275              'cp850' => 'cp850_general_ci',
1276              'hp8' => 'hp8_english_ci',
1277              'koi8r' => 'koi8r_general_ci',
1278              'latin1' => 'latin1_swedish_ci',
1279              'latin2' => 'latin2_general_ci',
1280              'swe7' => 'swe7_swedish_ci',
1281              'ascii' => 'ascii_general_ci',
1282              'ujis' => 'ujis_japanese_ci',
1283              'sjis' => 'sjis_japanese_ci',
1284              'hebrew' => 'hebrew_general_ci',
1285              'tis620' => 'tis620_thai_ci',
1286              'euckr' => 'euckr_korean_ci',
1287              'koi8u' => 'koi8u_general_ci',
1288              'gb2312' => 'gb2312_chinese_ci',
1289              'greek' => 'greek_general_ci',
1290              'cp1250' => 'cp1250_general_ci',
1291              'gbk' => 'gbk_chinese_ci',
1292              'latin5' => 'latin5_turkish_ci',
1293              'armscii8' => 'armscii8_general_ci',
1294              'utf8' => 'utf8_general_ci',
1295              'ucs2' => 'ucs2_general_ci',
1296              'cp866' => 'cp866_general_ci',
1297              'keybcs2' => 'keybcs2_general_ci',
1298              'macce' => 'macce_general_ci',
1299              'macroman' => 'macroman_general_ci',
1300              'cp852' => 'cp852_general_ci',
1301              'latin7' => 'latin7_general_ci',
1302              'cp1251' => 'cp1251_general_ci',
1303              'cp1256' => 'cp1256_general_ci',
1304              'cp1257' => 'cp1257_general_ci',
1305              'binary' => 'binary',
1306              'geostd8' => 'geostd8_general_ci',
1307              'cp932' => 'cp932_japanese_ci',
1308              'eucjpms' => 'eucjpms_japanese_ci',
1309          );
1310          if($collations[$charset])
1311          {
1312              return $collations[$charset];
1313          }
1314          return false;
1315      }
1316  
1317      /**
1318       * Fetch a character set/collation string for use with CREATE TABLE statements. Uses current DB encoding
1319       *
1320       * @return string The built string, empty if unsupported
1321       */
1322  	function build_create_table_collation()
1323      {
1324          if(!$this->db_encoding)
1325          {
1326              return '';
1327          }
1328  
1329          $collation = $this->fetch_charset_collation($this->db_encoding);
1330          if(!$collation)
1331          {
1332              return '';
1333          }
1334          return " CHARACTER SET {$this->db_encoding} COLLATE {$collation}";
1335      }
1336  
1337      /**
1338       * Time how long it takes for a particular piece of code to run. Place calls above & below the block of code.
1339       *
1340       * @return float The time taken
1341       */
1342  	function get_execution_time()
1343      {
1344          static $time_start;
1345  
1346          $time = microtime(true);
1347  
1348  
1349          // Just starting timer, init and return
1350          if(!$time_start)
1351          {
1352              $time_start = $time;
1353              return;
1354          }
1355          // Timer has run, return execution time
1356          else
1357          {
1358              $total = $time-$time_start;
1359              $time_start = 0;
1360              if($total < 0) $total = 0;
1361              return $total;
1362          }
1363      }
1364  }
1365  
1366  ?>