[Summary view] [Print] [Text view]

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