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