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