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