[ Index ] |
PHP Cross Reference of MyBB 1.8.38 |
[Summary view] [Print] [Text view]
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
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
2005 - 2021 © MyBB.de | Alle Rechte vorbehalten! | Sponsor: netcup | Cross-referenced by PHPXref |