[ Index ]

PHP Cross Reference of MyBB 1.8.27

title

Body

[close]

/inc/ -> db_pgsql_pdo.php (source)

   1  <?php
   2  /**
   3   * MyBB 1.8
   4   * Copyright 2020 MyBB Group, All Rights Reserved
   5   *
   6   * Website: http://www.mybb.com
   7   * License: http://www.mybb.com/about/license
   8   */
   9  
  10  class PostgresPdoDbDriver extends AbstractPdoDbDriver
  11  {
  12      /**
  13       * Explanation of a query.
  14       *
  15       * @var string
  16       */
  17      public $explain = '';
  18  
  19  	protected function getDsn($hostname, $db, $port, $encoding)
  20      {
  21          $dsn = "pgsql:host={$hostname};dbname={$db}";
  22  
  23          if ($port !== null) {
  24              $dsn .= ";port={$port}";
  25          }
  26  
  27          if (!empty($encoding)) {
  28              $dsn .= ";options='--client_encoding={$encoding}'";
  29          }
  30  
  31          return $dsn;
  32      }
  33  
  34  	public function query($string, $hideErrors = false, $writeQuery = false)
  35      {
  36          $string = preg_replace("#LIMIT (\s*)([0-9]+),(\s*)([0-9]+);?$#im", "LIMIT $4 OFFSET $2", trim($string));
  37  
  38          return parent::query($string, $hideErrors, $writeQuery);
  39      }
  40  
  41  	public function explain_query($string, $qtime)
  42      {
  43          $duration = format_time_duration($qtime);
  44          $queryText = htmlspecialchars_uni($string);
  45  
  46          if (preg_match('/^\\s*SELECT\\b/i', $string) === 1) {
  47              $query = $this->current_link->query("EXPLAIN {$string}");
  48  
  49              $this->explain .= <<<HTML
  50  <table style="background-color: #666;" width="95%" cellpadding="4" cellspacing="1" align="center">
  51      <tr>
  52          <td colspan="8" style="background-color: #ccc;">
  53              <strong>#{$this->query_count} - Select Query</strong>
  54          </td>
  55      </tr>
  56      <tr>
  57          <td colspan="8" style="background-color: #fefefe;">
  58              <span style=\"font-family: Courier; font-size: 14px;">{$queryText}</span>
  59          </td>
  60      <tr style="background-color: #efefef">
  61          <td>
  62              <strong>Info</strong>
  63          </td>
  64      </tr>
  65  HTML;
  66  
  67              while ($table = $query->fetch(PDO::FETCH_ASSOC)) {
  68                  $this->explain .= <<<HTML
  69      <tr style="background-color: #fff">
  70          <td>{$table['QUERY PLAN']}</td>
  71      </tr>
  72  HTML;
  73              }
  74  
  75              $this->explain .= <<<HTML
  76      <tr>
  77          <td colspan="8" style="background-color: #fff;">
  78              Query Time: {$duration}
  79          </td>
  80      </tr>
  81  </table>
  82  <br />
  83  HTML;
  84          } else {
  85              $this->explain .= <<<HTML
  86  <table style="background-color: #666;" width="95%" cellpadding="4" cellspacing="1" align="center">
  87      <tr>
  88          <td style="background-color: #ccc;">
  89              <strong>#{$this->query_count} - Write Query</strong>
  90          </td>
  91      </tr>
  92      <tr style="background-color: #fefefe;">
  93          <td>
  94              <span style="font-family: Courier; font-size: 14px;">{$queryText}</span>
  95          </td>
  96      </tr>
  97      <tr>
  98          <td style="background-color: #fff">
  99              Query Time: {$duration}
 100          </td>
 101      </tr>
 102  </table>
 103  <br />
 104  HTML;
 105          }
 106  
 107          $this->querylist[$this->query_count]['query'] = $string;
 108          $this->querylist[$this->query_count]['time'] = $qtime;
 109      }
 110  
 111  	public function list_tables($database, $prefix = '')
 112      {
 113          if ($prefix) {
 114              $query = $this->write_query("SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_name LIKE '{$this->escape_string($prefix)}%'");
 115          } else {
 116              $query = $this->write_query("SELECT table_name FROM information_schema.tables WHERE table_schema='public'");
 117          }
 118  
 119          return $query->fetchAll(PDO::FETCH_COLUMN);
 120      }
 121  
 122  	public function table_exists($table)
 123      {
 124          $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}'");
 125  
 126          $exists = $this->fetch_field($query, 'table_names');
 127  
 128          return $exists > 0;
 129      }
 130  
 131  	public function field_exists($field, $table)
 132      {
 133          $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}'");
 134  
 135          $exists = $this->fetch_field($query, 'column_names');
 136  
 137          return $exists > 0;
 138      }
 139  
 140  	public function simple_select($table, $fields = "*", $conditions = "", $options = array())
 141      {
 142          $query = "SELECT {$fields} FROM {$this->table_prefix}{$table}";
 143          if ($conditions != "") {
 144              $query .= " WHERE {$conditions}";
 145          }
 146  
 147          if (isset($options['group_by'])) {
 148              $query .= " GROUP BY {$options['group_by']}";
 149          }
 150  
 151          if (isset($options['order_by'])) {
 152              $query .= " ORDER BY {$options['order_by']}";
 153              if (isset($options['order_dir'])) {
 154                  $query .= " {$options['order_dir']}";
 155              }
 156          }
 157  
 158          if (isset($options['limit_start']) && isset($options['limit'])) {
 159              $query .= " LIMIT {$options['limit']} OFFSET {$options['limit_start']}";
 160          } else if (isset($options['limit'])) {
 161              $query .= " LIMIT {$options['limit']}";
 162          }
 163  
 164          return $this->query($query);
 165      }
 166  
 167  	public function insert_query($table, $array)
 168      {
 169          global $mybb;
 170  
 171          if (!is_array($array)) {
 172              return false;
 173          }
 174  
 175          foreach ($array as $field => $value) {
 176              if (isset($mybb->binary_fields[$table][$field]) && $mybb->binary_fields[$table][$field]) {
 177                  $array[$field] = $value;
 178              } else {
 179                  $array[$field] = $this->quote_val($value);
 180              }
 181          }
 182  
 183          $fields = implode(",", array_keys($array));
 184          $values = implode(",", $array);
 185          $this->write_query("
 186              INSERT
 187              INTO {$this->table_prefix}{$table} ({$fields})
 188              VALUES ({$values})
 189          ");
 190  
 191          return $this->insert_id();
 192      }
 193  
 194  	private function quote_val($value, $quote = "'")
 195      {
 196          if (is_int($value)) {
 197              return $value;
 198          }
 199  
 200          return "{$quote}{$value}{$quote}";
 201      }
 202  
 203  	public function insert_query_multiple($table, $array)
 204      {
 205          global $mybb;
 206  
 207          if (!is_array($array)){
 208              return;
 209          }
 210  
 211          // Field names
 212          $fields = array_keys($array[0]);
 213          $fields = implode(",", $fields);
 214  
 215          $insert_rows = array();
 216          foreach ($array as $values) {
 217              foreach ($values as $field => $value) {
 218                  if(isset($mybb->binary_fields[$table][$field]) && $mybb->binary_fields[$table][$field]) {
 219                      $values[$field] = $value;
 220                  } else {
 221                      $values[$field] = $this->quote_val($value);
 222                  }
 223              }
 224  
 225              $insert_rows[] = "(".implode(",", $values).")";
 226          }
 227  
 228          $insert_rows = implode(", ", $insert_rows);
 229  
 230          $this->write_query("
 231              INSERT
 232              INTO {$this->table_prefix}{$table} ({$fields})
 233              VALUES {$insert_rows}
 234          ");
 235      }
 236  
 237  	public function update_query($table, $array, $where = "", $limit = "", $no_quote = false)
 238      {
 239          global $mybb;
 240  
 241          if (!is_array($array)) {
 242              return false;
 243          }
 244  
 245          $comma = "";
 246          $query = "";
 247          $quote = "'";
 248  
 249          if ($no_quote == true) {
 250              $quote = "";
 251          }
 252  
 253          foreach($array as $field => $value) {
 254              if(isset($mybb->binary_fields[$table][$field]) && $mybb->binary_fields[$table][$field]) {
 255                  $query .= "{$comma}{$field}={$value}";
 256              } else {
 257                  $quoted_value = $this->quote_val($value, $quote);
 258  
 259                  $query .= "{$comma}{$field}={$quoted_value}";
 260              }
 261  
 262              $comma = ', ';
 263          }
 264  
 265          if(!empty($where)) {
 266              $query .= " WHERE {$where}";
 267          }
 268  
 269          return $this->write_query("
 270              UPDATE {$this->table_prefix}$table
 271              SET $query
 272          ");
 273      }
 274  
 275  	public function delete_query($table, $where = "", $limit = "")
 276      {
 277          $query = "";
 278          if (!empty($where)) {
 279              $query .= " WHERE {$where}";
 280          }
 281  
 282          return $this->write_query("
 283              DELETE
 284              FROM {$this->table_prefix}$table
 285              $query
 286          ");
 287      }
 288  
 289  	public function optimize_table($table)
 290      {
 291          $this->write_query("VACUUM {$this->table_prefix}{$table};");
 292      }
 293  
 294  	public function analyze_table($table)
 295      {
 296          $this->write_query("ANALYZE {$this->table_prefix}{$table};");
 297      }
 298  
 299  	public function show_create_table($table)
 300      {
 301          $query = $this->write_query("
 302              SELECT a.attnum, a.attname as field, t.typname as type, a.attlen as length, a.atttypmod as lengthvar, a.attnotnull as notnull
 303              FROM pg_class c
 304              LEFT JOIN pg_attribute a ON (a.attrelid = c.oid)
 305              LEFT JOIN pg_type t ON (a.atttypid = t.oid)
 306              WHERE c.relname = '{$this->table_prefix}{$table}' AND a.attnum > 0
 307              ORDER BY a.attnum
 308          ");
 309  
 310          $lines = array();
 311          $table_lines = "CREATE TABLE {$this->table_prefix}{$table} (\n";
 312  
 313          while ($row = $this->fetch_array($query)) {
 314              // Get the data from the table
 315              $query2 = $this->write_query("
 316                  SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault
 317                  FROM pg_attrdef d
 318                  LEFT JOIN pg_class c ON (c.oid = d.adrelid)
 319                  WHERE c.relname = '{$this->table_prefix}{$table}' AND d.adnum = '{$row['attnum']}'
 320              ");
 321  
 322              if (!$query2) {
 323                  unset($row['rowdefault']);
 324              } else {
 325                  $row['rowdefault'] = $this->fetch_field($query2, 'rowdefault');
 326              }
 327  
 328              if ($row['type'] == 'bpchar') {
 329                  // Stored in the engine as bpchar, but in the CREATE TABLE statement it's char
 330                  $row['type'] = 'char';
 331              }
 332  
 333              $line = "  {$row['field']} {$row['type']}";
 334  
 335              if (strpos($row['type'], 'char') !== false) {
 336                  if ($row['lengthvar'] > 0) {
 337                      $line .= '('.($row['lengthvar'] - 4).')';
 338                  }
 339              }
 340  
 341              if (strpos($row['type'], 'numeric') !== false) {
 342                  $line .= '('.sprintf("%s,%s", (($row['lengthvar'] >> 16) & 0xffff), (($row['lengthvar'] - 4) & 0xffff)).')';
 343              }
 344  
 345              if (!empty($row['rowdefault'])) {
 346                  $line .= " DEFAULT {$row['rowdefault']}";
 347              }
 348  
 349              if ($row['notnull'] == 't') {
 350                  $line .= ' NOT NULL';
 351              }
 352  
 353              $lines[] = $line;
 354          }
 355  
 356          // Get the listing of primary keys.
 357          $query = $this->write_query("
 358              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
 359              FROM pg_class bc
 360              LEFT JOIN pg_index i ON (bc.oid = i.indrelid)
 361              LEFT JOIN pg_class ic ON (ic.oid = i.indexrelid)
 362              LEFT JOIN pg_attribute ia ON (ia.attrelid = i.indexrelid)
 363              LEFT JOIN pg_attribute ta ON (ta.attrelid = bc.oid AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1])
 364              WHERE bc.relname = '{$this->table_prefix}{$table}'
 365              ORDER BY index_name, tab_name, column_name
 366          ");
 367  
 368          $primary_key = array();
 369          $primary_key_name = '';
 370  
 371          $unique_keys = array();
 372  
 373          // We do this in two steps. It makes placing the comma easier
 374          while ($row = $this->fetch_array($query)) {
 375              if ($row['primary_key'] == 't') {
 376                  $primary_key[] = $row['column_name'];
 377                  $primary_key_name = $row['index_name'];
 378              }
 379  
 380              if ($row['unique_key'] == 't') {
 381                  $unique_keys[$row['index_name']][] = $row['column_name'];
 382              }
 383          }
 384  
 385          if (!empty($primary_key)) {
 386              $lines[] = "  CONSTRAINT $primary_key_name PRIMARY KEY (".implode(', ', $primary_key).")";
 387          }
 388  
 389          foreach ($unique_keys as $key_name => $key_columns) {
 390              $lines[] = "  CONSTRAINT $key_name UNIQUE (".implode(', ', $key_columns).")";
 391          }
 392  
 393          $table_lines .= implode(", \n", $lines);
 394          $table_lines .= "\n)\n";
 395  
 396          return $table_lines;
 397      }
 398  
 399  	public function show_fields_from($table)
 400      {
 401          $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");
 402          $primary_key = $this->fetch_field($query, 'column_name');
 403  
 404          $query = $this->write_query("
 405              SELECT column_name as Field, data_type as Extra
 406              FROM information_schema.columns
 407              WHERE table_name = '{$this->table_prefix}{$table}'
 408          ");
 409  
 410          $field_info = array();
 411          while ($field = $this->fetch_array($query)) {
 412              if ($field['field'] == $primary_key) {
 413                  $field['extra'] = 'auto_increment';
 414              }
 415  
 416              $field_info[] = array('Extra' => $field['extra'], 'Field' => $field['field']);
 417          }
 418  
 419          return $field_info;
 420      }
 421  
 422  	function is_fulltext($table, $index = "")
 423      {
 424          return false;
 425      }
 426  
 427  	public function supports_fulltext($table)
 428      {
 429          return false;
 430      }
 431  
 432  	public function index_exists($table, $index)
 433      {
 434          $err = $this->error_reporting;
 435          $this->error_reporting = 0;
 436  
 437          $tableName = $this->escape_string("{$this->table_prefix}{$table}");
 438  
 439          $query = $this->write_query("SELECT * FROM pg_indexes WHERE tablename = '{$tableName}'");
 440  
 441          $exists = $this->fetch_field($query, $index);
 442          $this->error_reporting = $err;
 443  
 444          return (bool)$exists;
 445      }
 446  
 447  	public function supports_fulltext_boolean($table)
 448      {
 449          return false;
 450      }
 451  
 452  	public function create_fulltext_index($table, $column, $name = "")
 453      {
 454          return false;
 455      }
 456  
 457  	public function drop_index($table, $name)
 458      {
 459          $this->write_query("
 460              ALTER TABLE {$this->table_prefix}{$table}
 461              DROP INDEX {$name}
 462          ");
 463      }
 464  
 465  	public function drop_table($table, $hard = false, $table_prefix = true)
 466      {
 467          if ($table_prefix == false) {
 468              $table_prefix = "";
 469          } else {
 470              $table_prefix = $this->table_prefix;
 471          }
 472  
 473          if ($hard == false) {
 474              if($this->table_exists($table))
 475              {
 476                  $this->write_query("DROP TABLE {$table_prefix}{$table}");
 477              }
 478          } else {
 479              $this->write_query("DROP TABLE {$table_prefix}{$table}");
 480          }
 481  
 482          $query = $this->query("SELECT column_name FROM information_schema.constraint_column_usage WHERE table_name = '{$table}' and constraint_name = '{$table}_pkey' LIMIT 1");
 483          $field = $this->fetch_field($query, 'column_name');
 484  
 485          if ($field) {
 486              $this->write_query('DROP SEQUENCE {$table}_{$field}_id_seq');
 487          }
 488      }
 489  
 490  	public function rename_table($old_table, $new_table, $table_prefix = true)
 491      {
 492          if ($table_prefix == false) {
 493              $table_prefix = "";
 494          } else {
 495              $table_prefix = $this->table_prefix;
 496          }
 497  
 498          return $this->write_query("ALTER TABLE {$table_prefix}{$old_table} RENAME TO {$table_prefix}{$new_table}");
 499      }
 500  
 501  	public function replace_query($table, $replacements = array(), $default_field = "", $insert_id = true)
 502      {
 503          global $mybb;
 504  
 505          if ($default_field == "") {
 506              $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");
 507              $main_field = $this->fetch_field($query, 'column_name');
 508          } else {
 509              $main_field = $default_field;
 510          }
 511  
 512          $update = false;
 513          $search_bit = array();
 514  
 515          if (!is_array($main_field)) {
 516              $main_field = array($main_field);
 517          }
 518  
 519          foreach ($main_field as $field) {
 520              if (isset($mybb->binary_fields[$table][$field]) && $mybb->binary_fields[$table][$field]) {
 521                  $search_bit[] = "{$field} = ".$replacements[$field];
 522              } else {
 523                  $search_bit[] = "{$field} = ".$this->quote_val($replacements[$field]);
 524              }
 525          }
 526  
 527          $search_bit = implode(" AND ", $search_bit);
 528          $query = $this->write_query("SELECT COUNT(".$main_field[0].") as count FROM {$this->table_prefix}{$table} WHERE {$search_bit} LIMIT 1");
 529  
 530          if ($this->fetch_field($query, "count") == 1) {
 531              $update = true;
 532          }
 533  
 534          if ($update === true) {
 535              return $this->update_query($table, $replacements, $search_bit);
 536          } else {
 537              return $this->insert_query($table, $replacements);
 538          }
 539      }
 540  
 541  	public function drop_column($table, $column)
 542      {
 543          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} DROP {$column}");
 544      }
 545  
 546  	public function add_column($table, $column, $definition)
 547      {
 548          return $this->write_query("ALTER TABLE {$this->table_prefix}{$table} ADD {$column} {$definition}");
 549      }
 550  
 551  	public function modify_column($table, $column, $new_definition, $new_not_null = false, $new_default_value = false)
 552      {
 553          $result1 = $result2 = $result3 = true;
 554  
 555          if ($new_definition !== false) {
 556              $result1 = $this->write_query("ALTER TABLE {$this->table_prefix}{$table} ALTER COLUMN {$column} TYPE {$new_definition}");
 557          }
 558  
 559          if ($new_not_null !== false) {
 560              $set_drop = "DROP";
 561  
 562              if (strtolower($new_not_null) == "set") {
 563                  $set_drop = "SET";
 564              }
 565  
 566              $result2 = $this->write_query("ALTER TABLE {$this->table_prefix}{$table} ALTER COLUMN {$column} {$set_drop} NOT NULL");
 567          }
 568  
 569          if ($new_default_value !== null) {
 570              if($new_default_value !== false) {
 571                  $result3 = $this->write_query("ALTER TABLE {$this->table_prefix}{$table} ALTER COLUMN {$column} SET DEFAULT {$new_default_value}");
 572              } else {
 573                  $result3 = $this->write_query("ALTER TABLE {$this->table_prefix}{$table} ALTER COLUMN {$column} DROP DEFAULT");
 574              }
 575          }
 576  
 577          return $result1 && $result2 && $result3;
 578      }
 579  
 580  	public function rename_column($table, $old_column, $new_column, $new_definition, $new_not_null = false, $new_default_value = false)
 581      {
 582          $result1 = $this->write_query("ALTER TABLE {$this->table_prefix}{$table} RENAME COLUMN {$old_column} TO {$new_column}");
 583          $result2 = $this->modify_column($table, $new_column, $new_definition, $new_not_null, $new_default_value);
 584  
 585          return $result1 && $result2;
 586      }
 587  
 588  	public function fetch_size($table = '')
 589      {
 590          if (!empty($table)) {
 591              $query = $this->query("SELECT SUM(reltuples), SUM(relpages) FROM pg_class WHERE relname = '{$this->table_prefix}{$table}'");
 592          } else {
 593              $query = $this->query("SELECT SUM(reltuples), SUM(relpages) FROM pg_class");
 594          }
 595  
 596          if (null === $query) {
 597              return 0;
 598          }
 599  
 600          $result = $this->fetch_array($query, PDO::FETCH_NUM);
 601  
 602          if (false === $result) {
 603              return 0;
 604          }
 605  
 606          return $result[0] + $result[1];
 607      }
 608  
 609  	public function fetch_db_charsets()
 610      {
 611          return false;
 612      }
 613  
 614  	public function fetch_charset_collation($charset)
 615      {
 616          return false;
 617      }
 618  
 619  	public function build_create_table_collation()
 620      {
 621          return '';
 622      }
 623  
 624  	public function insert_id()
 625      {
 626          try {
 627              return $this->write_link->lastInsertId();
 628          } catch (PDOException $e) {
 629              // in order to behave the same way as the MySQL driver, we return false if there is no last insert ID
 630              return false;
 631          }
 632      }
 633  
 634  	public function escape_binary($string)
 635      {
 636          $hex = bin2hex($string);
 637          return "decode('{$hex}', 'hex')";
 638      }
 639  
 640  	public function unescape_binary($string)
 641      {
 642          // binary fields are treated as streams
 643          /** @var resource $string */
 644          return fgets($string);
 645      }
 646  
 647      /**
 648       * @param string $table
 649       * @param string $append
 650       *
 651       * @return string
 652       */
 653  	public function build_fields_string($table, $append="")
 654      {
 655          $fields = $this->show_fields_from($table);
 656          $comma = $fieldstring = '';
 657  
 658          foreach($fields as $key => $field)
 659          {
 660              $fieldstring .= "{$comma}{$append}{$field['Field']}";
 661              $comma = ',';
 662          }
 663  
 664          return $fieldstring;
 665      }
 666  
 667  	public function __set($name, $value)
 668      {
 669          if ($name === 'type') {
 670              // NOTE: This is to prevent the type being set - this type should appear as `pgsql` to ensure compatibility
 671              return;
 672          }
 673      }
 674  
 675  	public function __get($name)
 676      {
 677          if ($name === 'type') {
 678              // NOTE: this is to ensure compatibility checks on the DB type will work
 679              return 'pgsql';
 680          }
 681  
 682          return null;
 683      }
 684  }


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