[ Index ]

PHP Cross Reference of MyBB 1.8.32

title

Body

[close]

/inc/ -> db_sqlite.php (source)

   1  <?php
   2  /**
   3   * MyBB 1.8
   4   * Copyright 2014 MyBB Group, All Rights Reserved
   5   *
   6   * Website: http://www.mybb.com
   7   * License: http://www.mybb.com/about/license
   8   *
   9   */
  10  
  11  class DB_SQLite implements DB_Base
  12  {
  13      /**
  14       * The title of this layer.
  15       *
  16       * @var string
  17       */
  18      public $title = "SQLite 3";
  19  
  20      /**
  21       * The short title of this layer.
  22       *
  23       * @var string
  24       */
  25      public $short_title = "SQLite";
  26  
  27      /**
  28       * The type of db software being used.
  29       *
  30       * @var string
  31       */
  32      public $type;
  33  
  34      /**
  35       * PDOStatement objects of performed queries.
  36       *
  37       * @var array 
  38       */
  39      public $query_objects = array();
  40  
  41      /**
  42       * A count of the number of queries.
  43       *
  44       * @var int
  45       */
  46      public $query_count = 0;
  47  
  48      /**
  49       * A list of the performed queries.
  50       *
  51       * @var array
  52       */
  53      public $querylist = array();
  54  
  55      /**
  56       * 1 if error reporting enabled, 0 if disabled.
  57       *
  58       * @var boolean
  59       */
  60      public $error_reporting = 1;
  61  
  62      /**
  63       * The database connection resource.
  64       *
  65       * @var resource
  66       */
  67      public $link;
  68  
  69      /**
  70       * @var array
  71       */
  72      public $connections = array();
  73  
  74      /**
  75       * Explanation of a query.
  76       *
  77       * @var string
  78       */
  79      public $explain;
  80  
  81      /**
  82       * The current version of SQLite.
  83       *
  84       * @var string
  85       */
  86      public $version;
  87  
  88      /**
  89       * The current table type in use (myisam/innodb)
  90       *
  91       * @var string
  92       */
  93      public $table_type = "myisam";
  94  
  95      /**
  96       * The table prefix used for simple select, update, insert and delete queries
  97       *
  98       * @var string
  99       */
 100      public $table_prefix;
 101  
 102      /**
 103       * The extension used to run the SQL database
 104       *
 105       * @var string
 106       */
 107      public $engine = "pdo";
 108  
 109      /**
 110       * Weather or not this engine can use the search functionality
 111       *
 112       * @var boolean
 113       */
 114      public $can_search = true;
 115  
 116      /**
 117       * The database encoding currently in use (if supported)
 118       *
 119       * @var string
 120       */
 121      public $db_encoding = "";
 122  
 123      /**
 124       * The time spent performing queries
 125       *
 126       * @var float
 127       */
 128      public $query_time = 0;
 129  
 130      /**
 131       * Our pdo implementation
 132       *
 133       * @var dbpdoEngine
 134       */
 135      var $db;
 136  
 137      /**
 138       * Connect to the database server.
 139       *
 140       * @param array $config Array of DBMS connection details.
 141       * @return bool Returns false on failure, otherwise true
 142       */
 143  	function connect($config)
 144      {
 145          get_execution_time();
 146  
 147          require_once  MYBB_ROOT."inc/db_pdo.php";
 148  
 149          try {
 150              $this->db = new dbpdoEngine("sqlite:{$config['database']}");
 151          } catch (Exception $ex) {
 152              $this->error("[READ] Unable to open the SQLite database");
 153  
 154              return false;
 155          }
 156  
 157          $query_time = get_execution_time();
 158  
 159          $this->query_time += $query_time;
 160  
 161          $this->connections[] = "[WRITE] {$config['database']} (Connected in ".format_time_duration($query_time).")";
 162  
 163          if($this->db)
 164          {
 165              $this->query('PRAGMA short_column_names = 1');
 166              return true;
 167          }
 168          else
 169          {
 170              return false;
 171          }
 172      }
 173  
 174      /**
 175       * Query the database.
 176       *
 177       * @param string $string The query SQL.
 178       * @param boolean|int $hide_errors 1 if hide errors, 0 if not.
 179       * @param integer $write_query 1 if executes on master database, 0 if not.
 180       * @return PDOStatement The query data.
 181       */
 182  	function query($string, $hide_errors=0, $write_query=0)
 183      {
 184          global $mybb;
 185  
 186          get_execution_time();
 187  
 188          if(strtolower(substr(ltrim($string), 0, 5)) == 'alter')
 189          {
 190              $string = preg_replace("#\sAFTER\s([a-z_]+?)(;*?)$#i", "", $string);
 191  
 192              $queryparts = preg_split("/[\s]+/", $string, 4, PREG_SPLIT_NO_EMPTY);
 193              $tablename = $queryparts[2];
 194              $alterdefs = $queryparts[3];
 195              if(strtolower($queryparts[1]) != 'table' || $queryparts[2] == '')
 196              {
 197                  $this->error_msg = "near \"{$queryparts[0]}\": syntax error";
 198              }
 199              else
 200              {
 201                  // SQLITE 3 supports ADD and RENAME TO alter statements
 202                  if(strtolower(substr(ltrim($alterdefs), 0, 3)) == 'add' || strtolower(substr(ltrim($alterdefs), 0, 9)) == "rename to")
 203                  {
 204                      $query = $this->db->query($string);
 205                      $query->closeCursor();
 206                  }
 207                  else
 208                  {
 209                      $query = $this->alter_table_parse($tablename, $alterdefs, $string);
 210                  }
 211              }
 212          }
 213            else
 214            {
 215              try
 216              {
 217                  $query = $this->db->query($string);
 218              }
 219              catch(PDOException $exception)
 220              {
 221                  $error = array(
 222                      "message" => $exception->getMessage(),
 223                      "code" => $exception->getCode()
 224                  );
 225  
 226                  $this->error($error['message'], $error['code']);
 227              }
 228          }
 229  
 230          $this->query_objects[] = $query;
 231  
 232          if($this->error_number($query) > 0 && !$hide_errors)
 233          {
 234              $this->error($string, $query);
 235              exit;
 236          }
 237  
 238          $query_time = get_execution_time();
 239          $this->query_time += $query_time;
 240          $this->query_count++;
 241  
 242          if($mybb->debug_mode)
 243          {
 244              $this->explain_query($string, $query_time);
 245          }
 246  
 247          if(strtolower(substr(ltrim($string), 0, 6)) == "create")
 248          {
 249              $query->closeCursor();
 250              return null;
 251          }
 252  
 253          return $query;
 254      }
 255  
 256      /**
 257       * Explain a query on the database.
 258       *
 259       * @param string $string The query SQL.
 260       * @param string $qtime The time it took to perform the query.
 261       */
 262  	function explain_query($string, $qtime)
 263      {
 264          if(preg_match("#^\s*select#i", $string))
 265          {
 266              $this->explain .= "<table style=\"background-color: #666;\" width=\"95%\" cellpadding=\"4\" cellspacing=\"1\" align=\"center\">\n".
 267                  "<tr>\n".
 268                  "<td colspan=\"8\" style=\"background-color: #ccc;\"><strong>#".$this->query_count." - Select Query</strong></td>\n".
 269                  "</tr>\n".
 270                  "<tr>\n".
 271                  "<td colspan=\"8\" style=\"background-color: #fefefe;\"><span style=\"font-family: Courier; font-size: 14px;\">".htmlspecialchars_uni($string)."</span></td>\n".
 272                  "</tr>\n".
 273                  "<tr>\n".
 274                  "<td colspan=\"8\" style=\"background-color: #fff;\">Query Time: ".format_time_duration($qtime)."</td>\n".
 275                  "</tr>\n".
 276                  "</table>\n".
 277                  "<br />\n";
 278          }
 279          else
 280          {
 281              $this->explain .= "<table style=\"background-color: #666;\" width=\"95%\" cellpadding=\"4\" cellspacing=\"1\" align=\"center\">\n".
 282                  "<tr>\n".
 283                  "<td style=\"background-color: #ccc;\"><strong>#".$this->query_count." - Write Query</strong></td>\n".
 284                  "</tr>\n".
 285                  "<tr style=\"background-color: #fefefe;\">\n".
 286                  "<td><span style=\"font-family: Courier; font-size: 14px;\">".htmlspecialchars_uni($string)."</span></td>\n".
 287                  "</tr>\n".
 288                  "<tr>\n".
 289                  "<td bgcolor=\"#ffffff\">Query Time: ".format_time_duration($qtime)."</td>\n".
 290                  "</tr>\n".
 291                  "</table>\n".
 292                  "<br />\n";
 293          }
 294  
 295          $this->querylist[$this->query_count]['query'] = $string;
 296          $this->querylist[$this->query_count]['time'] = $qtime;
 297      }
 298  
 299      /**
 300       * Execute a write query on the database
 301       *
 302       * @param string $query The query SQL.
 303       * @param boolean|int $hide_errors 1 if hide errors, 0 if not.
 304       * @return PDOStatement The query data.
 305       */
 306  	function write_query($query, $hide_errors=0)
 307      {
 308          return $this->query($query, $hide_errors);
 309      }
 310  
 311      /**
 312       * Return a result array for a query.
 313       *
 314       * @param PDOStatement $query The result data.
 315       * @param int $resulttype One of PDO's constants: FETCH_ASSOC, FETCH_BOUND, FETCH_CLASS, FETCH_INTO, FETCH_LAZY, FETCH_NAMED, FETCH_NUM, FETCH_OBJ or FETCH_BOTH
 316       * @return array The array of results.
 317       */
 318  	function fetch_array($query, $resulttype=PDO::FETCH_BOTH)
 319      {
 320          $array = $this->db->fetch_array($query, $resulttype);
 321          return $array;
 322      }
 323  
 324      /**
 325       * Return a specific field from a query.
 326       *
 327       * @param PDOStatement $query The query ID.
 328       * @param string $field The name of the field to return.
 329       * @param int|bool $row The number of the row to fetch it from.
 330       * @return mixed
 331       */
 332  	function fetch_field($query, $field, $row=false)
 333      {
 334          if($row !== false)
 335          {
 336              $this->data_seek($query, $row);
 337          }
 338          $array = $this->fetch_array($query);
 339          if($array !== null && $array !== false)
 340          {
 341              return $array[$field];
 342          }
 343          return null;
 344      }
 345  
 346      /**
 347       * Moves internal row pointer to the next row
 348       *
 349       * @param PDOStatement $query The query ID.
 350       * @param int $row The pointer to move the row to.
 351       */
 352  	function data_seek($query, $row)
 353      {
 354          $this->db->seek($query, $row);
 355      }
 356  
 357      /**
 358       * Closes cursors of registered queries.
 359       *
 360       */
 361  	function close_cursors()
 362      {
 363          $result = true;
 364  
 365          foreach($this->query_objects as $query)
 366          {
 367              if(!$query->closeCursor())
 368              {
 369                  $result = false;
 370              }
 371          }
 372  
 373          return $result;
 374      }
 375  
 376      /**
 377       * Return the number of rows resulting from a query.
 378       *
 379       * @param PDOStatement $query The query data.
 380       * @return int The number of rows in the result.
 381       */
 382  	function num_rows($query)
 383      {
 384          return $this->db->num_rows($query);
 385      }
 386  
 387      /**
 388       * Return the last id number of inserted data.
 389       *
 390       * @param string $name
 391       * @return int The id number.
 392       */
 393  	function insert_id($name="")
 394      {
 395          return $this->db->insert_id($name);
 396      }
 397  
 398      /**
 399       * Close the connection with the DBMS.
 400       *
 401       */
 402  	function close()
 403      {
 404          return;
 405      }
 406  
 407      /**
 408       * Return an error number.
 409       *
 410       * @param PDOStatement $query
 411       * @return int The error number of the current error.
 412       */
 413  	function error_number($query=null)
 414      {
 415          if($query == null)
 416          {
 417              $query = $this->db->last_query;
 418          }
 419  
 420          $this->error_number = $this->db->error_number($query);
 421  
 422          return $this->error_number;
 423      }
 424  
 425      /**
 426       * Return an error string.
 427       *
 428       * @param PDOStatement $query
 429       * @return string The explanation for the current error.
 430       */
 431  	function error_string($query=null)
 432      {
 433          if($this->error_number != "")
 434          {
 435              if($query == null)
 436              {
 437                  $query = $this->db->last_query;
 438              }
 439  
 440              $error_string = $this->db->error_string($query);
 441              $this->error_number = "";
 442  
 443              return $error_string;
 444          }
 445  
 446          return '';
 447      }
 448  
 449      /**
 450       * Output a database error.
 451       *
 452       * @param string $string The string to present as an error.
 453       * @param PDOStatement $query
 454       * @param string $error
 455       * @param int $error_no
 456       */
 457  	function error($string="", $query=null, $error="", $error_no=0)
 458      {
 459          if($this->error_reporting)
 460          {
 461              if($query == null)
 462              {
 463                  $query = $this->db->last_query;
 464              }
 465  
 466              if($error_no == 0)
 467              {
 468                  $error_no = $this->error_number($query);
 469              }
 470  
 471              if($error == "")
 472              {
 473                  $error = $this->error_string($query);
 474              }
 475  
 476              if(class_exists("errorHandler"))
 477              {
 478                  global $error_handler;
 479  
 480                  if(!is_object($error_handler))
 481                  {
 482                      require_once  MYBB_ROOT."inc/class_error.php";
 483                      $error_handler = new errorHandler();
 484                  }
 485  
 486                  $error = array(
 487                      "error_no" => $error_no,
 488                      "error" => $error,
 489                      "query" => $string
 490                  );
 491                  $error_handler->error(MYBB_SQL, $error);
 492              }
 493              else
 494              {
 495                  trigger_error("<strong>[SQL] [{$error_no}] {$error}</strong><br />{$string}", E_USER_ERROR);
 496              }
 497          }
 498      }
 499  
 500      /**
 501       * Returns the number of affected rows in a query.
 502       *
 503       * @param PDOStatement $query
 504       * @return int The number of affected rows.
 505       */
 506  	function affected_rows($query=null)
 507      {
 508          if($query == null)
 509          {
 510              $query = $this->db->last_query;
 511          }
 512  
 513          return $this->db->affected_rows($query);
 514      }
 515  
 516      /**
 517       * Return the number of fields.
 518       *
 519       * @param PDOStatement $query The query data.
 520       * @return int The number of fields.
 521       */
 522  	function num_fields($query)
 523      {
 524          if(!$query)
 525          {
 526              $query = $this->db->last_query;
 527          }
 528  
 529          return $this->db->num_fields($query);
 530      }
 531  
 532      /**
 533       * Lists all tables in the database.
 534       *
 535       * @param string $database The database name.
 536       * @param string $prefix Prefix of the table (optional)
 537       * @return array The table list.
 538       */
 539  	function list_tables($database, $prefix='')
 540      {
 541          if($prefix)
 542          {
 543              $query = $this->query("SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name LIKE '".$this->escape_string($prefix)."%'");
 544          }
 545          else
 546          {
 547              $query = $this->query("SELECT tbl_name FROM sqlite_master WHERE type = 'table'");
 548          }
 549  
 550          $tables = array();
 551          while($table = $this->fetch_array($query))
 552          {
 553              $tables[] = $table['tbl_name'];
 554          }
 555          $query->closeCursor();
 556          return $tables;
 557      }
 558  
 559      /**
 560       * Check if a table exists in a database.
 561       *
 562       * @param string $table The table name.
 563       * @return boolean True when exists, false if not.
 564       */
 565  	function table_exists($table)
 566      {
 567          $query = $this->query("SELECT COUNT(name) as count FROM sqlite_master WHERE type='table' AND name='{$this->table_prefix}{$table}'");
 568          $exists = $this->fetch_field($query, "count");
 569          $query->closeCursor();
 570  
 571          if($exists > 0)
 572          {
 573              return true;
 574          }
 575          else
 576          {
 577              return false;
 578          }
 579      }
 580  
 581      /**
 582       * Check if a field exists in a database.
 583       *
 584       * @param string $field The field name.
 585       * @param string $table The table name.
 586       * @return boolean True when exists, false if not.
 587       */
 588  	function field_exists($field, $table)
 589      {
 590          $query = $this->query("PRAGMA table_info('{$this->table_prefix}{$table}')");
 591  
 592          $exists = 0;
 593  
 594          while($row = $this->fetch_array($query))
 595          {
 596              if($row['name'] == $field)
 597              {
 598                  ++$exists;
 599              }
 600          }
 601  
 602          $query->closeCursor();
 603  
 604          if($exists > 0)
 605          {
 606              return true;
 607          }
 608          else
 609          {
 610              return false;
 611          }
 612      }
 613  
 614      /**
 615       * Add a shutdown query.
 616       *
 617       * @param PDOStatement $query The query data.
 618       * @param string $name An optional name for the query.
 619       */
 620  	function shutdown_query($query, $name="")
 621      {
 622          global $shutdown_queries;
 623          if($name)
 624          {
 625              $shutdown_queries[$name] = $query;
 626          }
 627          else
 628          {
 629              $shutdown_queries[] = $query;
 630          }
 631      }
 632  
 633      /**
 634       * Performs a simple select query.
 635       *
 636       * @param string $table The table name to be queried.
 637       * @param string $fields Comma delimetered list of fields to be selected.
 638       * @param string $conditions SQL formatted list of conditions to be matched.
 639       * @param array $options List of options: group by, order by, order direction, limit, limit start.
 640       * @return PDOStatement The query data.
 641       */
 642  	function simple_select($table, $fields="*", $conditions="", $options=array())
 643      {
 644          $query = "SELECT ".$fields." FROM ".$this->table_prefix.$table;
 645  
 646          if($conditions != "")
 647          {
 648              $query .= " WHERE ".$conditions;
 649          }
 650  
 651          if(isset($options['group_by']))
 652          {
 653              $query .= " GROUP BY ".$options['group_by'];
 654          }
 655  
 656          if(isset($options['order_by']))
 657          {
 658              $query .= " ORDER BY ".$options['order_by'];
 659  
 660              if(isset($options['order_dir']))
 661              {
 662                  $query .= " ".strtoupper($options['order_dir']);
 663              }
 664          }
 665  
 666          if(isset($options['limit_start']) && isset($options['limit']))
 667          {
 668              $query .= " LIMIT ".$options['limit_start'].", ".$options['limit'];
 669          }
 670          else if(isset($options['limit']))
 671          {
 672              $query .= " LIMIT ".$options['limit'];
 673          }
 674  
 675          return $this->query($query);
 676      }
 677  
 678      /**
 679       * Build an insert query from an array.
 680       *
 681       * @param string $table The table name to perform the query on.
 682       * @param array $array An array of fields and their values.
 683       * @return int|bool The insert ID if available or false if an error is found
 684       */
 685  	function insert_query($table, $array)
 686      {
 687          global $mybb;
 688  
 689          if(!is_array($array))
 690          {
 691              return false;
 692          }
 693  
 694          foreach($array as $field => $value)
 695          {
 696              if(isset($mybb->binary_fields[$table][$field]) && $mybb->binary_fields[$table][$field])
 697              {
 698                  if($value[0] != 'X') // Not escaped?
 699                  {
 700                      $value = $this->escape_binary($value);
 701                  }
 702                  
 703                  $array[$field] = $value;
 704              }
 705              else
 706              {
 707                  $array[$field] = $this->quote_val($value);
 708              }
 709          }
 710  
 711          $fields = implode(",", array_keys($array));
 712          $values = implode(",", $array);
 713          $query = $this->write_query("
 714              INSERT
 715              INTO {$this->table_prefix}{$table} (".$fields.")
 716              VALUES (".$values.")
 717          ");
 718          $query->closeCursor();
 719          return $this->insert_id();
 720      }
 721  
 722      /**
 723       * Build one query for multiple inserts from a multidimensional array.
 724       *
 725       * @param string $table The table name to perform the query on.
 726       * @param array $array An array of inserts.
 727       * @return void
 728       */
 729  	function insert_query_multiple($table, $array)
 730      {
 731          global $mybb;
 732  
 733          if(!is_array($array))
 734          {
 735              return;
 736          }
 737          // Field names
 738          $fields = array_keys($array[0]);
 739          $fields = implode(",", $fields);
 740  
 741          $insert_rows = array();
 742          foreach($array as $values)
 743          {
 744              foreach($values as $field => $value)
 745              {
 746                  if(isset($mybb->binary_fields[$table][$field]) && $mybb->binary_fields[$table][$field])
 747                  {
 748                      if($value[0] != 'X') // Not escaped?
 749                      {
 750                          $value = $this->escape_binary($value);
 751                      }
 752                  
 753                      $values[$field] = $value;
 754                  }
 755                  else
 756                  {
 757                      $values[$field] = $this->quote_val($value);
 758                  }
 759              }
 760              $insert_rows[] = "(".implode(",", $values).")";
 761          }
 762          $insert_rows = implode(", ", $insert_rows);
 763  
 764          $query = $this->write_query("
 765              INSERT
 766              INTO {$this->table_prefix}{$table} ({$fields})
 767              VALUES {$insert_rows}
 768          ");
 769          $query->closeCursor();
 770      }
 771  
 772      /**
 773       * Build an update query from an array.
 774       *
 775       * @param string $table The table name to perform the query on.
 776       * @param array $array An array of fields and their values.
 777       * @param string $where An optional where clause for the query.
 778       * @param string $limit An optional limit clause for the query.
 779       * @param boolean $no_quote An option to quote incoming values of the array.
 780       * @return PDOStatement The query data.
 781       */
 782  	function update_query($table, $array, $where="", $limit="", $no_quote=false)
 783      {
 784          global $mybb;
 785  
 786          if(!is_array($array))
 787          {
 788              return false;
 789          }
 790  
 791          $comma = "";
 792          $query = "";
 793          $quote = "'";
 794  
 795          if($no_quote == true)
 796          {
 797              $quote = "";
 798          }
 799  
 800          foreach($array as $field => $value)
 801          {
 802              if(isset($mybb->binary_fields[$table][$field]) && $mybb->binary_fields[$table][$field])
 803              {
 804                  if($value[0] != 'X') // Not escaped?
 805                  {
 806                      $value = $this->escape_binary($value);
 807                  }
 808                  
 809                  $query .= $comma.$field."=".$value;
 810              }
 811              else
 812              {
 813                  $quoted_value = $this->quote_val($value, $quote);
 814  
 815                  $query .= $comma.$field."={$quoted_value}";
 816              }
 817              $comma = ', ';
 818          }
 819  
 820          if(!empty($where))
 821          {
 822              $query .= " WHERE $where";
 823          }
 824  
 825          $query = $this->query("UPDATE {$this->table_prefix}$table SET $query");
 826          $query->closeCursor();
 827          return $query;
 828      }
 829  
 830      /**
 831       * @param int|string $value
 832       * @param string $quote
 833       *
 834       * @return int|string
 835       */
 836  	private function quote_val($value, $quote="'")
 837      {
 838          if(is_int($value))
 839          {
 840              $quoted = $value;
 841          }
 842          else
 843          {
 844              $quoted = $quote . $value . $quote;
 845          }
 846  
 847          return $quoted;
 848      }
 849  
 850      /**
 851       * Build a delete query.
 852       *
 853       * @param string $table The table name to perform the query on.
 854       * @param string $where An optional where clause for the query.
 855       * @param string $limit An optional limit clause for the query.
 856       * @return PDOStatement The query data.
 857       */
 858  	function delete_query($table, $where="", $limit="")
 859      {
 860          $query = "";
 861          if(!empty($where))
 862          {
 863              $query .= " WHERE $where";
 864          }
 865  
 866          $query = $this->query("DELETE FROM {$this->table_prefix}$table $query");
 867          $query->closeCursor();
 868          return $query;
 869      }
 870  
 871      /**
 872       * Escape a string
 873       *
 874       * @param string $string The string to be escaped.
 875       * @return string The escaped string.
 876       */
 877  	function escape_string($string)
 878      {
 879          $string = $this->db->escape_string($string);
 880          return $string;
 881      }
 882  
 883      /**
 884       * Serves no purposes except compatibility
 885       *
 886       * @param PDOStatement $query
 887       * @return boolean Returns true on success, false on failure
 888       */
 889  	function free_result($query)
 890      {
 891          return true;
 892      }
 893  
 894      /**
 895       * Escape a string used within a like command.
 896       *
 897       * @param string $string The string to be escaped.
 898       * @return string The escaped string.
 899       */
 900  	function escape_string_like($string)
 901      {
 902          return $this->escape_string(str_replace(array('\\', '%', '_') , array('\\\\', '\\%' , '\\_') , $string));
 903      }
 904  
 905      /**
 906       * Gets the current version of SQLLite.
 907       *
 908       * @return string Version of MySQL.
 909       */
 910  	function get_version()
 911      {
 912          if($this->version)
 913          {
 914              return $this->version;
 915          }
 916          $this->version = $this->db->get_attribute("ATTR_SERVER_VERSION");
 917  
 918          return $this->version;
 919      }
 920  
 921      /**
 922       * Optimizes a specific table.
 923       *
 924       * @param string $table The name of the table to be optimized.
 925       */
 926  	function optimize_table($table)
 927      {
 928          // SQLite doesn't support table level optimization.
 929          // Using `VACUUM [main | $db_name]` may also be blocked by any opened query cursor, hence generating an error.
 930      }
 931  
 932      /**
 933       * Analyzes a specific table.
 934       *
 935       * @param string $table The name of the table to be analyzed.
 936       */
 937  	function analyze_table($table)
 938      {
 939          $query = $this->query("ANALYZE ".$this->table_prefix.$table."");
 940          $query->closeCursor();
 941      }
 942  
 943      /**
 944       * Show the "create table" command for a specific table.
 945       *
 946       * @param string $table The name of the table.
 947       * @return string The SQLite command to create the specified table.
 948       */
 949  	function show_create_table($table)
 950      {
 951          $old_tbl_prefix = $this->table_prefix;
 952          $this->set_table_prefix("");
 953          $query = $this->simple_select("sqlite_master", "sql", "type = 'table' AND name = '{$old_tbl_prefix}{$table}' ORDER BY type DESC, name");
 954          $this->set_table_prefix($old_tbl_prefix);
 955  
 956          $result = $this->fetch_field($query, 'sql');
 957  
 958          $query->closeCursor();
 959  
 960          return $result;
 961      }
 962  
 963      /**
 964       * Show the "show fields from" command for a specific table.
 965       *
 966       * @param string $table The name of the table.
 967       * @return array Field info for that table
 968       */
 969  	function show_fields_from($table)
 970      {
 971          $query = $this->write_query("PRAGMA TABLE_INFO('".$this->table_prefix.$table."')");
 972          $field_info = array();
 973          while($field = $this->fetch_array($query))
 974          {
 975              if(!empty($field['pk']))
 976              {
 977                  $field['_key'] = 'PRI';
 978                  $field['_extra'] = 'auto_increment';
 979              }
 980              else
 981              {
 982                  $field['_key'] = '';
 983                  $field['_extra'] = '';
 984              }
 985  
 986              // SQLite allows NULLs in most PRIMARY KEY columns due to a bug in early versions, even in an INTEGER PRIMARY KEY column, read https://sqlite.org/lang_createtable.html for details. We won't fix this for consistency among other database engines.
 987              $field['_nullable'] = $field['notnull'] ? 'NO' : 'YES';
 988  
 989              $field_info[] = array(
 990                  'Field' => $field['name'],
 991                  'Type' => $field['type'],
 992                  'Null' => $field['_nullable'],
 993                  'Key' => $field['_key'],
 994                  'Default' => $field['dflt_value'],
 995                  'Extra' => $field['_extra'],
 996              );
 997          }
 998          $query->closeCursor();
 999          return $field_info;
1000      }
1001  
1002      /**
1003       * Returns whether or not the table contains a fulltext index.
1004       *
1005       * @param string $table The name of the table.
1006       * @param string $index Optionally specify the name of the index.
1007       * @return boolean True or false if the table has a fulltext index or not.
1008       */
1009  	function is_fulltext($table, $index="")
1010      {
1011          return false;
1012      }
1013  
1014      /**
1015       * Returns whether or not this database engine supports fulltext indexing.
1016       *
1017       * @param string $table The table to be checked.
1018       * @return boolean True or false if supported or not.
1019       */
1020  
1021  	function supports_fulltext($table)
1022      {
1023          return false;
1024      }
1025  
1026      /**
1027       * Returns whether or not this database engine supports boolean fulltext matching.
1028       *
1029       * @param string $table The table to be checked.
1030       * @return boolean True or false if supported or not.
1031       */
1032  	function supports_fulltext_boolean($table)
1033      {
1034          return false;
1035      }
1036  
1037      /**
1038       * Creates a fulltext index on the specified column in the specified table with optional index name.
1039       *
1040       * @param string $table The name of the table.
1041       * @param string $column Name of the column to be indexed.
1042       * @param string $name The index name, optional.
1043       * @return bool
1044       */
1045  	function create_fulltext_index($table, $column, $name="")
1046      {
1047          return false;
1048      }
1049  
1050      /**
1051       * Drop an index with the specified name from the specified table
1052       *
1053       * @param string $table The name of the table.
1054       * @param string $name The name of the index.
1055       */
1056  	function drop_index($table, $name)
1057      {
1058          $query = $this->query("ALTER TABLE {$this->table_prefix}$table DROP INDEX $name");
1059          $query->closeCursor();
1060      }
1061  
1062      /**
1063       * Checks to see if an index exists on a specified table
1064       *
1065       * @param string $table The name of the table.
1066       * @param string $index The name of the index.
1067       * @return bool Returns whether index exists
1068       */
1069  	function index_exists($table, $index)
1070      {
1071          return false;
1072      }
1073  
1074      /**
1075       * Drop an table with the specified table
1076       *
1077       * @param string $table The name of the table.
1078       * @param boolean $hard hard drop - no checking
1079       * @param boolean $table_prefix 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          $table_prefix_bak = $this->table_prefix;
1093          $this->table_prefix = '';
1094          if($hard == false)
1095          {
1096              if($this->table_exists($table_prefix.$table))
1097              {
1098                  $query = $this->query('DROP TABLE '.$table_prefix.$table);
1099              }
1100          }
1101          else
1102          {
1103              $query = $this->query('DROP TABLE '.$table_prefix.$table);
1104          }
1105          $this->table_prefix = $table_prefix_bak;
1106  
1107          if(isset($query))
1108          {
1109              $query->closeCursor();
1110          }
1111      }
1112  
1113      /**
1114       * Renames a table
1115       *
1116       * @param string $old_table The old table name
1117       * @param string $new_table the new table name
1118       * @param boolean $table_prefix use table prefix
1119       * @return PDOStatement
1120       */
1121  	function rename_table($old_table, $new_table, $table_prefix=true)
1122      {
1123          if($table_prefix == false)
1124          {
1125              $table_prefix = "";
1126          }
1127          else
1128          {
1129              $table_prefix = $this->table_prefix;
1130          }
1131  
1132          $query = $this->write_query("ALTER TABLE {$table_prefix}{$old_table} RENAME TO {$table_prefix}{$new_table}");
1133          $query->closeCursor();
1134          return $query;
1135      }
1136  
1137      /**
1138       * Replace contents of table with values
1139       *
1140       * @param string $table The table
1141       * @param array $replacements The replacements
1142       * @param string|array $default_field The default field(s)
1143       * @param boolean $insert_id Whether or not to return an insert id. True by default
1144       * @return int|PDOStatement|bool Returns either the insert id (if a new row is inserted), the query resource (if a row is updated) or false on failure
1145       */
1146  	function replace_query($table, $replacements=array(), $default_field="", $insert_id=true)
1147      {
1148          global $mybb;
1149  
1150          $columns = '';
1151          $values = '';
1152          $comma = '';
1153          foreach($replacements as $column => $value)
1154          {
1155              $columns .= $comma.$column;
1156              if(isset($mybb->binary_fields[$table][$column]) && $mybb->binary_fields[$table][$column])
1157              {
1158                  if($value[0] != 'X') // Not escaped?
1159                  {
1160                      $value = $this->escape_binary($value);
1161                  }
1162                  
1163                  $values .= $comma.$value;
1164              }
1165              else
1166              {
1167                  $values .= $comma.$this->quote_val($value);
1168              }
1169  
1170              $comma = ',';
1171          }
1172  
1173          if(empty($columns) || empty($values))
1174          {
1175               return false;
1176          }
1177  
1178          if($default_field == "")
1179          {
1180              $query = $this->query("REPLACE INTO {$this->table_prefix}{$table} ({$columns}) VALUES({$values})");
1181              $query->closeCursor();
1182              return $query;
1183          }
1184          else
1185          {
1186              $update = false;
1187              if(is_array($default_field) && !empty($default_field))
1188              {
1189                  $search_bit = array();
1190                  foreach($default_field as $field)
1191                  {
1192                      $search_bit[] = "{$field} = '".$replacements[$field]."'";
1193                  }
1194  
1195                  $search_bit = implode(" AND ", $search_bit);
1196                  $query = $this->write_query("SELECT COUNT(".$default_field[0].") as count FROM {$this->table_prefix}{$table} WHERE {$search_bit} LIMIT 1");
1197                  if($this->fetch_field($query, "count") == 1)
1198                  {
1199                      $update = true;
1200                  }
1201              }
1202              else
1203              {
1204                  $query = $this->write_query("SELECT {$default_field} FROM {$this->table_prefix}{$table}");
1205                  $search_bit = "{$default_field}='".$replacements[$default_field]."'";
1206  
1207                  while($column = $this->fetch_array($query))
1208                  {
1209                      if($column[$default_field] == $replacements[$default_field])
1210                      {
1211                          $update = true;
1212                          break;
1213                      }
1214                  }
1215              }
1216  
1217              if($update === true)
1218              {
1219                  return $this->update_query($table, $replacements, $search_bit);
1220              }
1221              else
1222              {
1223                  return $this->insert_query($table, $replacements);
1224              }
1225          }
1226      }
1227  
1228      /**
1229       * Sets the table prefix used by the simple select, insert, update and delete functions
1230       *
1231       * @param string $prefix The new table prefix
1232       */
1233  	function set_table_prefix($prefix)
1234      {
1235          $this->table_prefix = $prefix;
1236      }
1237  
1238      /**
1239       * Fetched the total size of all mysql tables or a specific table
1240       *
1241       * @param string $table The table (optional) (ignored)
1242       * @return integer the total size of all mysql tables or a specific table
1243       */
1244  	function fetch_size($table='')
1245      {
1246          global $config, $lang;
1247  
1248          $total = @filesize($config['database']['database']);
1249          if(!$total || $table != '')
1250          {
1251              $total = $lang->na;
1252          }
1253          return $total;
1254      }
1255  
1256      /**
1257       * Perform an "Alter Table" query in SQLite < 3.2.0 - Code taken from http://code.jenseng.com/db/
1258       *
1259       * @param string $table The table (optional)
1260       * @param string $alterdefs
1261       * @param string $fullquery
1262       * @return bool True on success, false on failure
1263       */
1264  	function alter_table_parse($table, $alterdefs, $fullquery="")
1265      {
1266          if(!$fullquery)
1267          {
1268              $fullquery = " ... {$alterdefs}";
1269          }
1270  
1271          if(!defined("TIME_NOW"))
1272          {
1273              define("TIME_NOW", time());
1274          }
1275  
1276          if($alterdefs != '')
1277          {
1278              $result = $this->query("SELECT sql,name,type FROM sqlite_master WHERE tbl_name = '{$table}' ORDER BY type DESC");
1279              if($this->num_rows($result) > 0)
1280              {
1281                  $row = $this->fetch_array($result); // Table sql
1282                  $result->closeCursor();
1283                  $tmpname = 't'.TIME_NOW;
1284                  $origsql = trim(preg_replace("/[\s]+/", " ", str_replace(",", ", ", preg_replace("/[\(]/","( ", $row['sql'], 1))));
1285                  $createtemptableSQL = 'CREATE TEMPORARY '.substr(trim(preg_replace("'".$table."'", $tmpname, $origsql, 1)), 6);
1286                  $defs = preg_split("/[,]+/", $alterdefs, -1, PREG_SPLIT_NO_EMPTY);
1287                  $prevword = $table;
1288                  $oldcols = preg_split("/[,]+/", substr(trim($createtemptableSQL), strpos(trim($createtemptableSQL), '(')+1), -1, PREG_SPLIT_NO_EMPTY);
1289                  $newcols = array();
1290  
1291                  for($i = 0; $i < sizeof($oldcols); $i++)
1292                  {
1293                      $colparts = preg_split("/[\s]+/", $oldcols[$i], -1, PREG_SPLIT_NO_EMPTY);
1294                      $oldcols[$i] = $colparts[0];
1295                      $newcols[$colparts[0]] = $colparts[0];
1296                  }
1297  
1298                  $newcolumns = '';
1299                  $oldcolumns = '';
1300                  reset($newcols);
1301  
1302                  foreach($newcols as $key => $val)
1303                  {
1304                      $newcolumns .= ($newcolumns ? ', ' : '').$val;
1305                      $oldcolumns .= ($oldcolumns ? ', ' : '').$key;
1306                  }
1307  
1308                  $copytotempsql = 'INSERT INTO '.$tmpname.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$table;
1309                  $dropoldsql = 'DROP TABLE '.$table;
1310                  $createtesttableSQL = $createtemptableSQL;
1311  
1312                  foreach($defs as $def)
1313                  {
1314                      $defparts = preg_split("/[\s]+/", $def, -1, PREG_SPLIT_NO_EMPTY);
1315                      $action = strtolower($defparts[0]);
1316  
1317                      switch($action)
1318                      {
1319                          case 'change':
1320                              if(sizeof($defparts) <= 3)
1321                              {
1322                                  $this->error($alterdefs, 'near "'.$defparts[0].($defparts[1] ? ' '.$defparts[1] : '').($defparts[2] ? ' '.$defparts[2] : '').'": syntax error', E_USER_WARNING);
1323                                  return false;
1324                              }
1325  
1326                              if($severpos = strpos($createtesttableSQL, ' '.$defparts[1].' '))
1327                              {
1328                                  if($newcols[$defparts[1]] != $defparts[1])
1329                                  {
1330                                      $this->error($alterdefs, 'unknown column "'.$defparts[1].'" in "'.$table.'"');
1331                                      return false;
1332                                  }
1333  
1334                                  $newcols[$defparts[1]] = $defparts[2];
1335                                  $nextcommapos = strpos($createtesttableSQL, ',', $severpos);
1336                                  $insertval = '';
1337  
1338                                  for($i = 2; $i < sizeof($defparts); $i++)
1339                                  {
1340                                      $insertval .= ' '.$defparts[$i];
1341                                  }
1342  
1343                                  if($nextcommapos)
1344                                  {
1345                                      $createtesttableSQL = substr($createtesttableSQL, 0, $severpos).$insertval.substr($createtesttableSQL, $nextcommapos);
1346                                  }
1347                                  else
1348                                  {
1349                                      $createtesttableSQL = substr($createtesttableSQL, 0, $severpos-(strpos($createtesttableSQL, ',') ? 0 : 1)).$insertval.')';
1350                                  }
1351                              }
1352                              else
1353                              {
1354                                  $this->error($fullquery, 'unknown column "'.$defparts[1].'" in "'.$table.'"', E_USER_WARNING);
1355                                  return false;
1356                              }
1357                              break;
1358                          case 'drop':
1359                              if(sizeof($defparts) < 2)
1360                              {
1361                                  $this->error($fullquery, 'near "'.$defparts[0].($defparts[1] ? ' '.$defparts[1] : '').'": syntax error');
1362                                  return false;
1363                              }
1364  
1365                              if($severpos = strpos($createtesttableSQL, ' '.$defparts[1].' '))
1366                              {
1367                                  $nextcommapos = strpos($createtesttableSQL, ',', $severpos);
1368  
1369                                  if($nextcommapos)
1370                                  {
1371                                      $createtesttableSQL = substr($createtesttableSQL, 0, $severpos).substr($createtesttableSQL, $nextcommapos + 1);
1372                                  }
1373                                  else
1374                                  {
1375                                      $createtesttableSQL = substr($createtesttableSQL, 0, $severpos-(strpos($createtesttableSQL, ',') ? 0 : 1) - 1).')';
1376                                  }
1377  
1378                                  unset($newcols[$defparts[1]]);
1379                              }
1380                              else
1381                              {
1382                                  $this->error($fullquery, 'unknown column "'.$defparts[1].'" in "'.$table.'"');
1383                                  return false;
1384                              }
1385                              break;
1386                          default:
1387                              $this->error($fullquery, 'near "'.$prevword.'": syntax error');
1388                              return false;
1389                      }
1390  
1391                      $prevword = $defparts[sizeof($defparts)-1];
1392                  }
1393  
1394                  // This block of code generates a test table simply to verify that the columns specifed are valid in an sql statement
1395                  // This ensures that no reserved words are used as columns, for example
1396                  $this->query($createtesttableSQL);
1397  
1398                  $droptempsql = 'DROP TABLE '.$tmpname;
1399                  $query = $this->query($droptempsql, 0);    
1400                  if($query === false)
1401                  {
1402                      return false;
1403                  }
1404                  $query->closeCursor();
1405                  // End block
1406  
1407  
1408                  $createnewtableSQL = 'CREATE '.substr(trim(preg_replace("'{$tmpname}'", $table, $createtesttableSQL, 1)), 17);
1409                  $newcolumns = '';
1410                  $oldcolumns = '';
1411                  reset($newcols);
1412  
1413                  foreach($newcols as $key => $val)
1414                  {
1415                      $newcolumns .= ($newcolumns ? ', ' : '').$val;
1416                      $oldcolumns .= ($oldcolumns ? ', ' : '').$key;
1417                  }
1418  
1419                  $copytonewsql = 'INSERT INTO '.$table.'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$tmpname;
1420  
1421  
1422                  $this->query($createtemptableSQL); // Create temp table
1423                  $query = $this->query($copytotempsql); // Copy to table
1424                  $query->closeCursor();
1425                  $query = $this->query($dropoldsql); // Drop old table
1426                  $query->closeCursor();
1427  
1428                  $this->query($createnewtableSQL); // Recreate original table
1429                  $query = $this->query($copytonewsql); // Copy back to original table
1430                  $query->closeCursor();
1431                  $query = $this->query($droptempsql); // Drop temp table
1432                  $query->closeCursor();
1433              }
1434              else
1435              {
1436                  $this->error($fullquery, 'no such table: '.$table);
1437                  return false;
1438              }
1439          }
1440          return true;
1441      }
1442  
1443      /**
1444       * Drops a column
1445       *
1446       * @param string $table The table
1447       * @param string $column The column name
1448       * @return PDOStatement
1449       */
1450  	function drop_column($table, $column)
1451      {
1452          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} DROP {$column}");
1453      }
1454  
1455      /**
1456       * Adds a column
1457       *
1458       * @param string $table The table
1459       * @param string $column The column name
1460       * @param string $definition the new column definition
1461       * @return PDOStatement
1462       */
1463  	function add_column($table, $column, $definition)
1464      {
1465          $query = $this->write_query("ALTER TABLE {$this->table_prefix}{$table} ADD {$column} {$definition}");
1466          $query->closeCursor();
1467          return $query;
1468      }
1469  
1470      /**
1471       * Modifies a column
1472       *
1473       * @param string $table The table
1474       * @param string $column The column name
1475       * @param string $new_definition the new column definition
1476       * @param boolean|string $new_not_null Whether to "drop" or "set" the NOT NULL attribute (no change if false)
1477       * @param boolean|string $new_default_value The new default value, or false to drop the attribute
1478       * @return bool Returns true if all queries are executed successfully or false if one of them failed
1479       */
1480  	function modify_column($table, $column, $new_definition, $new_not_null=false, $new_default_value=false)
1481      {
1482          // We use a rename query as both need to duplicate the table etc...
1483          return $this->rename_column($table, $column, $column, $new_definition, $new_not_null, $new_default_value);
1484      }
1485  
1486      /**
1487       * Renames a column
1488       *
1489       * @param string $table The table
1490       * @param string $old_column The old column name
1491       * @param string $new_column the new column name
1492       * @param string $new_definition the new column definition
1493       * @param boolean|string $new_not_null Whether to "drop" or "set" the NOT NULL attribute (no change if false)
1494       * @param boolean|string $new_default_value The new default value, or false to drop the attribute
1495       * @return bool Returns true if all queries are executed successfully
1496       */
1497  	function rename_column($table, $old_column, $new_column, $new_definition, $new_not_null=false, $new_default_value=false)
1498      {
1499          if($new_not_null !== false)
1500          {
1501              if(strtolower($new_not_null) == "set")
1502              {
1503                  $not_null = "NOT NULL";
1504              }
1505              else
1506              {
1507                  $not_null = "NULL";
1508              }
1509          }
1510          else
1511          {
1512              $not_null = '';
1513          }
1514  
1515          if($new_default_value !== false)
1516          {
1517              $default = "DEFAULT ".$new_default_value;
1518          }
1519          else
1520          {
1521              $default = '';
1522          }
1523  
1524          // This will trigger the "alter_table_parse" function which will copy the table and rename the column
1525          return (bool) $this->write_query("ALTER TABLE {$this->table_prefix}{$table} CHANGE {$old_column} {$new_column} {$new_definition} {$not_null} {$default}");
1526      }
1527  
1528      /**
1529       * Fetch a list of database character sets this DBMS supports
1530       *
1531       * @return array|bool Array of supported character sets with array key being the name, array value being display name. False if unsupported
1532       */
1533  	function fetch_db_charsets()
1534      {
1535          return false;
1536      }
1537  
1538      /**
1539       * Fetch a database collation for a particular database character set
1540       *
1541       * @param string $charset The database character set
1542       * @return string|bool The matching database collation, false if unsupported
1543       */
1544  	function fetch_charset_collation($charset)
1545      {
1546          return false;
1547      }
1548  
1549      /**
1550       * Fetch a character set/collation string for use with CREATE TABLE statements. Uses current DB encoding
1551       *
1552       * @return string The built string, empty if unsupported
1553       */
1554  	function build_create_table_collation()
1555      {
1556          return '';
1557      }
1558  
1559      /**
1560       * Time how long it takes for a particular piece of code to run. Place calls above & below the block of code.
1561       *
1562       * @deprecated
1563       */
1564  	function get_execution_time()
1565      {
1566          return get_execution_time();
1567      }
1568  
1569      /**
1570       * Binary database fields require special attention.
1571       *
1572       * @param string $string Binary value
1573       * @return string Encoded binary value
1574       */
1575  	function escape_binary($string)
1576      {
1577          return "X'".$this->escape_string(bin2hex($string))."'";
1578      }
1579  
1580      /**
1581       * Unescape binary data.
1582       *
1583       * @param string $string Binary value
1584       * @return string Encoded binary value
1585       */
1586  	function unescape_binary($string)
1587      {
1588          // Nothing to do
1589          return $string;
1590      }
1591  }
1592  


2005 - 2021 © MyBB.de | Alle Rechte vorbehalten! | Sponsor: netcup Cross-referenced by PHPXref