[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  ?>