[ Index ]

PHP Cross Reference of MyBB 1.8.27

title

Body

[close]

/inc/ -> db_pgsql.php (source)

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


2005 - 2021 © MyBB.de | Alle Rechte vorbehalten! | Sponsor: netcup Cross-referenced by PHPXref