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