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 10class 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> 65HTML; 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> 72HTML; 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 /> 83HTML; 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 /> 104HTML; 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}