| [ Index ] |
PHP Cross Reference of Joomla 4.2.2 documentation |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * Part of the Joomla Framework Database Package 4 * 5 * @copyright Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved. 6 * @license GNU General Public License version 2 or later; see LICENSE 7 */ 8 9 namespace Joomla\Database\Pgsql; 10 11 use Joomla\Database\Pdo\PdoDriver; 12 13 /** 14 * PostgreSQL PDO Database Driver 15 * 16 * @since 1.5.0 17 */ 18 class PgsqlDriver extends PdoDriver 19 { 20 /** 21 * The database driver name 22 * 23 * @var string 24 * @since 1.5.0 25 */ 26 public $name = 'pgsql'; 27 28 /** 29 * The character(s) used to quote SQL statement names such as table names or field names, 30 * etc. The child classes should define this as necessary. If a single character string the 31 * same character is used for both sides of the quoted name, else the first character will be 32 * used for the opening quote and the second for the closing quote. 33 * 34 * @var string 35 * @since 1.5.0 36 */ 37 protected $nameQuote = '"'; 38 39 /** 40 * The null or zero representation of a timestamp for the database driver. This should be 41 * defined in child classes to hold the appropriate value for the engine. 42 * 43 * @var string 44 * @since 1.5.0 45 */ 46 protected $nullDate = '1970-01-01 00:00:00'; 47 48 /** 49 * The minimum supported database version. 50 * 51 * @var string 52 * @since 1.5.0 53 */ 54 protected static $dbMinimum = '9.4.0'; 55 56 /** 57 * Operator used for concatenation 58 * 59 * @var string 60 * @since 1.5.0 61 */ 62 protected $concat_operator = '||'; 63 64 /** 65 * Database object constructor 66 * 67 * @param array $options List of options used to configure the connection 68 * 69 * @since 1.5.0 70 */ 71 public function __construct($options) 72 { 73 $options['driver'] = 'pgsql'; 74 $options['host'] = $options['host'] ?? 'localhost'; 75 $options['user'] = $options['user'] ?? ''; 76 $options['password'] = $options['password'] ?? ''; 77 $options['database'] = $options['database'] ?? ''; 78 $options['port'] = $options['port'] ?? null; 79 80 // Finalize initialization 81 parent::__construct($options); 82 } 83 84 /** 85 * Connects to the database if needed. 86 * 87 * @return void 88 * 89 * @since 1.5.0 90 * @throws \RuntimeException 91 */ 92 public function connect() 93 { 94 if ($this->getConnection()) 95 { 96 return; 97 } 98 99 parent::connect(); 100 101 $this->setQuery('SET standard_conforming_strings = off')->execute(); 102 } 103 104 /** 105 * Method to get the database collation in use by sampling a text field of a table in the database. 106 * 107 * @return string|boolean The collation in use by the database or boolean false if not supported. 108 * 109 * @since 1.5.0 110 * @throws \RuntimeException 111 */ 112 public function getCollation() 113 { 114 $this->setQuery('SHOW LC_COLLATE'); 115 $array = $this->loadAssocList(); 116 117 return $array[0]['lc_collate']; 118 } 119 120 /** 121 * Method to get the database connection collation in use by sampling a text field of a table in the database. 122 * 123 * @return string|boolean The collation in use by the database connection (string) or boolean false if not supported. 124 * 125 * @since 1.6.0 126 * @throws \RuntimeException 127 */ 128 public function getConnectionCollation() 129 { 130 $this->setQuery('SHOW LC_COLLATE'); 131 $array = $this->loadAssocList(); 132 133 return $array[0]['lc_collate']; 134 } 135 136 /** 137 * Method to get the database encryption details (cipher and protocol) in use. 138 * 139 * @return string The database encryption details. 140 * 141 * @since 2.0.0 142 * @throws \RuntimeException 143 */ 144 public function getConnectionEncryption(): string 145 { 146 // Requires PostgreSQL 9.5 or newer 147 if (version_compare($this->getVersion(), '9.5', '<')) 148 { 149 return ''; 150 } 151 152 $query = $this->getQuery(true) 153 ->select($this->quoteName(['version', 'cipher'])) 154 ->from($this->quoteName('pg_stat_ssl')) 155 ->where($this->quoteName('pid') . ' = pg_backend_pid()'); 156 157 $variables = $this->setQuery($query)->loadAssoc(); 158 159 if (!empty($variables['cipher'])) 160 { 161 return $variables['version'] . ' (' . $variables['cipher'] . ')'; 162 } 163 164 return ''; 165 } 166 167 /** 168 * Method to test if the database TLS connections encryption are supported. 169 * 170 * @return boolean Whether the database supports TLS connections encryption. 171 * 172 * @since 2.0.0 173 */ 174 public function isConnectionEncryptionSupported(): bool 175 { 176 $variables = $this->setQuery('SHOW "ssl"')->loadAssoc(); 177 178 return !empty($variables['ssl']) && $variables['ssl'] === 'on'; 179 } 180 181 /** 182 * Internal function to get the name of the default schema for the current PostgreSQL connection. 183 * That is the schema where tables are created by Joomla. 184 * 185 * @return string 186 * 187 * @since 1.8.0 188 */ 189 private function getDefaultSchema() 190 { 191 // Supported since PostgreSQL 7.3 192 $this->setQuery('SELECT (current_schemas(false))[1]'); 193 194 return $this->loadResult(); 195 } 196 197 /** 198 * Shows the table CREATE statement that creates the given tables. 199 * 200 * This is unsupported by PostgreSQL. 201 * 202 * @param mixed $tables A table name or a list of table names. 203 * 204 * @return string An empty string because this function is not supported by PostgreSQL. 205 * 206 * @since 1.5.0 207 * @throws \RuntimeException 208 */ 209 public function getTableCreate($tables) 210 { 211 return ''; 212 } 213 214 /** 215 * Retrieves field information about a given table. 216 * 217 * @param string $table The name of the database table. 218 * @param boolean $typeOnly True to only return field types. 219 * 220 * @return array An array of fields for the database table. 221 * 222 * @since 1.5.0 223 * @throws \RuntimeException 224 */ 225 public function getTableColumns($table, $typeOnly = true) 226 { 227 $this->connect(); 228 229 $result = []; 230 $tableSub = $this->replacePrefix($table); 231 $defaultSchema = $this->getDefaultSchema(); 232 233 $this->setQuery(' 234 SELECT a.attname AS "column_name", 235 pg_catalog.format_type(a.atttypid, a.atttypmod) as "type", 236 CASE WHEN a.attnotnull IS TRUE 237 THEN \'NO\' 238 ELSE \'YES\' 239 END AS "null", 240 CASE WHEN pg_catalog.pg_get_expr(adef.adbin, adef.adrelid, true) IS NOT NULL 241 THEN pg_catalog.pg_get_expr(adef.adbin, adef.adrelid, true) 242 END as "Default", 243 CASE WHEN pg_catalog.col_description(a.attrelid, a.attnum) IS NULL 244 THEN \'\' 245 ELSE pg_catalog.col_description(a.attrelid, a.attnum) 246 END AS "comments" 247 FROM pg_catalog.pg_attribute a 248 LEFT JOIN pg_catalog.pg_attrdef adef ON a.attrelid=adef.adrelid AND a.attnum=adef.adnum 249 LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid 250 WHERE a.attrelid = 251 (SELECT oid FROM pg_catalog.pg_class WHERE relname=' . $this->quote($tableSub) . ' 252 AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE 253 nspname = ' . $this->quote($defaultSchema) . ') 254 ) 255 AND a.attnum > 0 AND NOT a.attisdropped 256 ORDER BY a.attnum' 257 ); 258 259 $fields = $this->loadObjectList(); 260 261 if ($typeOnly) 262 { 263 foreach ($fields as $field) 264 { 265 $result[$field->column_name] = preg_replace('/[(0-9)]/', '', $field->type); 266 } 267 } 268 else 269 { 270 foreach ($fields as $field) 271 { 272 if ($field->Default !== null) 273 { 274 // Normalise default values like datetime 275 if (preg_match('/^\'(.*)\'::.*/', $field->Default, $matches)) 276 { 277 $field->Default = $matches[1]; 278 } 279 280 // Change Postgresql's NULL::* type with PHP's null one. Do this last to avoid PHP type issues in PHP 8.1 and up 281 if (preg_match('/^NULL::*/', $field->Default)) 282 { 283 $field->Default = null; 284 } 285 } 286 287 // Do some dirty translation to MySQL output. 288 // @todo: Come up with and implement a standard across databases. 289 $result[$field->column_name] = (object) [ 290 'column_name' => $field->column_name, 291 'type' => $field->type, 292 'null' => $field->null, 293 'Default' => $field->Default, 294 'comments' => '', 295 'Field' => $field->column_name, 296 'Type' => $field->type, 297 'Null' => $field->null, 298 // @todo: Improve query above to return primary key info as well 299 // 'Key' => ($field->PK == '1' ? 'PRI' : '') 300 ]; 301 } 302 } 303 304 return $result; 305 } 306 307 /** 308 * Get the details list of keys for a table. 309 * 310 * @param string $table The name of the table. 311 * 312 * @return array An array of the column specification for the table. 313 * 314 * @since 1.5.0 315 * @throws \RuntimeException 316 */ 317 public function getTableKeys($table) 318 { 319 $this->connect(); 320 321 // To check if table exists and prevent SQL injection 322 $tableList = $this->getTableList(); 323 $tableSub = $this->replacePrefix($table); 324 325 if (\in_array($tableSub, $tableList, true)) 326 { 327 // Get the details columns information. 328 $this->setQuery(' 329 SELECT indexname AS "idxName", indisprimary AS "isPrimary", indisunique AS "isUnique", indkey AS "indKey", 330 CASE WHEN indisprimary = true THEN 331 ( SELECT \'ALTER TABLE \' || tablename || \' ADD \' || pg_catalog.pg_get_constraintdef(const.oid, true) 332 FROM pg_constraint AS const WHERE const.conname= pgClassFirst.relname ) 333 ELSE pg_catalog.pg_get_indexdef(indexrelid, 0, true) 334 END AS "Query" 335 FROM pg_indexes 336 LEFT JOIN pg_class AS pgClassFirst ON indexname=pgClassFirst.relname 337 LEFT JOIN pg_index AS pgIndex ON pgClassFirst.oid=pgIndex.indexrelid 338 WHERE tablename=' . $this->quote($tableSub) . ' ORDER BY indkey' 339 ); 340 341 return $this->loadObjectList(); 342 } 343 344 return []; 345 } 346 347 /** 348 * Get the list of column names this index indexes. 349 * 350 * @param string $table The name of the table. 351 * @param string $indKey The list of column numbers for the table 352 * 353 * @return string A list of the column names for the table. 354 * 355 * @since 2.0.0 356 * @throws \RuntimeException 357 */ 358 public function getNamesKey($table, $indKey) 359 { 360 $this->connect(); 361 362 $tableSub = $this->replacePrefix($table); 363 364 $tabInd = explode(' ', $indKey); 365 $colNames = []; 366 367 foreach ($tabInd as $numCol) 368 { 369 $query = $this->getQuery(true) 370 ->select('attname') 371 ->from('pg_attribute') 372 ->join('LEFT', 'pg_class ON pg_class.relname=' . $this->quote($tableSub)) 373 ->where('attnum=' . $numCol . ' AND attrelid=pg_class.oid'); 374 $this->setQuery($query); 375 $colNames[] = $this->loadResult(); 376 } 377 378 return implode(', ', $colNames); 379 } 380 381 /** 382 * Method to get an array of all tables in the database. 383 * 384 * @return array An array of all the tables in the database. 385 * 386 * @since 1.5.0 387 * @throws \RuntimeException 388 */ 389 public function getTableList() 390 { 391 $query = $this->getQuery(true) 392 ->select('table_name') 393 ->from('information_schema.tables') 394 ->where('table_type = ' . $this->quote('BASE TABLE')) 395 ->where('table_schema NOT IN (' . $this->quote('pg_catalog') . ', ' . $this->quote('information_schema') . ')') 396 ->order('table_name ASC'); 397 398 $this->setQuery($query); 399 400 return $this->loadColumn(); 401 } 402 403 /** 404 * Get the details list of sequences for a table. 405 * 406 * @param string $table The name of the table. 407 * 408 * @return array An array of sequences specification for the table. 409 * 410 * @since 1.5.0 411 * @throws \RuntimeException 412 */ 413 public function getTableSequences($table) 414 { 415 // To check if table exists and prevent SQL injection 416 $tableList = $this->getTableList(); 417 $tableSub = $this->replacePrefix($table); 418 419 if (\in_array($tableSub, $tableList, true)) 420 { 421 $name = [ 422 's.relname', 'n.nspname', 't.relname', 'a.attname', 'info.data_type', 423 'info.minimum_value', 'info.maximum_value', 'info.increment', 'info.cycle_option', 'info.start_value', 424 ]; 425 426 $as = [ 427 'sequence', 'schema', 'table', 'column', 'data_type', 'minimum_value', 'maximum_value', 'increment', 'cycle_option', 'start_value', 428 ]; 429 430 // Get the details columns information. 431 $query = $this->getQuery(true) 432 ->select($this->quoteName($name, $as)) 433 ->from('pg_class AS s') 434 ->leftJoin("pg_depend d ON d.objid = s.oid AND d.classid = 'pg_class'::regclass AND d.refclassid = 'pg_class'::regclass") 435 ->leftJoin('pg_class t ON t.oid = d.refobjid') 436 ->leftJoin('pg_namespace n ON n.oid = t.relnamespace') 437 ->leftJoin('pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid') 438 ->leftJoin('information_schema.sequences AS info ON info.sequence_name = s.relname') 439 ->where('s.relkind = ' . $this->quote('S') . ' AND d.deptype = ' . $this->quote('a') . ' AND t.relname = ' . $this->quote($tableSub)); 440 $this->setQuery($query); 441 442 return $this->loadObjectList(); 443 } 444 445 return []; 446 } 447 448 /** 449 * Method to get the last value of a sequence in the database. 450 * 451 * @param string $sequence The name of the sequence. 452 * 453 * @return integer The last value of the sequence. 454 * 455 * @since 2.0.0 456 * @throws \RuntimeException 457 */ 458 public function getSequenceLastValue($sequence) 459 { 460 $this->connect(); 461 462 $query = $this->getQuery(true) 463 ->select($this->quoteName('last_value')) 464 ->from($sequence); 465 466 $this->setQuery($query); 467 468 return $this->loadResult(); 469 } 470 471 /** 472 * Method to get the is_called attribute of a sequence. 473 * 474 * @param string $sequence The name of the sequence. 475 * 476 * @return boolean The is_called attribute of the sequence. 477 * 478 * @since 2.0.0 479 * @throws \RuntimeException 480 */ 481 public function getSequenceIsCalled($sequence) 482 { 483 $this->connect(); 484 485 $query = $this->getQuery(true) 486 ->select($this->quoteName('is_called')) 487 ->from($sequence); 488 489 $this->setQuery($query); 490 491 return $this->loadResult(); 492 } 493 494 /** 495 * Locks a table in the database. 496 * 497 * @param string $tableName The name of the table to unlock. 498 * 499 * @return $this 500 * 501 * @since 1.5.0 502 * @throws \RuntimeException 503 */ 504 public function lockTable($tableName) 505 { 506 $this->transactionStart(); 507 $this->setQuery('LOCK TABLE ' . $this->quoteName($tableName) . ' IN ACCESS EXCLUSIVE MODE')->execute(); 508 509 return $this; 510 } 511 512 /** 513 * Renames a table in the database. 514 * 515 * @param string $oldTable The name of the table to be renamed 516 * @param string $newTable The new name for the table. 517 * @param string $backup Not used by PostgreSQL. 518 * @param string $prefix Not used by PostgreSQL. 519 * 520 * @return $this 521 * 522 * @since 1.5.0 523 * @throws \RuntimeException 524 */ 525 public function renameTable($oldTable, $newTable, $backup = null, $prefix = null) 526 { 527 $this->connect(); 528 529 $oldTable = $this->replacePrefix($oldTable); 530 $newTable = $this->replacePrefix($newTable); 531 532 // To check if table exists and prevent SQL injection 533 $tableList = $this->getTableList(); 534 535 // Origin Table does not exist 536 if (!\in_array($oldTable, $tableList, true)) 537 { 538 // Origin Table not found 539 throw new \RuntimeException('Table not found in Postgresql database.'); 540 } 541 542 // Rename indexes 543 $subQuery = $this->getQuery(true) 544 ->select('indexrelid') 545 ->from('pg_index, pg_class') 546 ->where('pg_class.relname = ' . $this->quote($oldTable)) 547 ->where('pg_class.oid = pg_index.indrelid'); 548 549 $this->setQuery( 550 $this->getQuery(true) 551 ->select('relname') 552 ->from('pg_class') 553 ->where('oid IN (' . (string) $subQuery . ')') 554 ); 555 556 $oldIndexes = $this->loadColumn(); 557 558 foreach ($oldIndexes as $oldIndex) 559 { 560 $changedIdxName = str_replace($oldTable, $newTable, $oldIndex); 561 $this->setQuery('ALTER INDEX ' . $this->escape($oldIndex) . ' RENAME TO ' . $this->escape($changedIdxName))->execute(); 562 } 563 564 // Rename sequences 565 $subQuery = $this->getQuery(true) 566 ->select('oid') 567 ->from('pg_namespace') 568 ->where('nspname NOT LIKE ' . $this->quote('pg_%')) 569 ->where('nspname != ' . $this->quote('information_schema')); 570 571 $this->setQuery( 572 $this->getQuery(true) 573 ->select('relname') 574 ->from('pg_class') 575 ->where('relkind = ' . $this->quote('S')) 576 ->where('relnamespace IN (' . (string) $subQuery . ')') 577 ->where('relname LIKE ' . $this->quote("%$oldTable%")) 578 ); 579 580 $oldSequences = $this->loadColumn(); 581 582 foreach ($oldSequences as $oldSequence) 583 { 584 $changedSequenceName = str_replace($oldTable, $newTable, $oldSequence); 585 $this->setQuery('ALTER SEQUENCE ' . $this->escape($oldSequence) . ' RENAME TO ' . $this->escape($changedSequenceName))->execute(); 586 } 587 588 // Rename table 589 $this->setQuery('ALTER TABLE ' . $this->escape($oldTable) . ' RENAME TO ' . $this->escape($newTable))->execute(); 590 591 return $this; 592 } 593 594 /** 595 * This function return a field value as a prepared string to be used in a SQL statement. 596 * 597 * @param array $columns Array of table's column returned by ::getTableColumns. 598 * @param string $fieldName The table field's name. 599 * @param string $fieldValue The variable value to quote and return. 600 * 601 * @return string The quoted string. 602 * 603 * @since 1.5.0 604 */ 605 public function sqlValue($columns, $fieldName, $fieldValue) 606 { 607 switch ($columns[$fieldName]) 608 { 609 case 'boolean': 610 $val = 'NULL'; 611 612 if ($fieldValue === 't' || $fieldValue === true || $fieldValue === 1 || $fieldValue === '1') 613 { 614 $val = 'TRUE'; 615 } 616 elseif ($fieldValue === 'f' || $fieldValue === false || $fieldValue === 0 || $fieldValue === '0') 617 { 618 $val = 'FALSE'; 619 } 620 621 break; 622 623 case 'bigint': 624 case 'bigserial': 625 case 'integer': 626 case 'money': 627 case 'numeric': 628 case 'real': 629 case 'smallint': 630 case 'serial': 631 case 'numeric,': 632 $val = $fieldValue === '' ? 'NULL' : $fieldValue; 633 634 break; 635 636 case 'timestamp without time zone': 637 case 'date': 638 if (empty($fieldValue)) 639 { 640 $fieldValue = $this->getNullDate(); 641 } 642 643 $val = $this->quote($fieldValue); 644 645 break; 646 647 default: 648 $val = $this->quote($fieldValue); 649 650 break; 651 } 652 653 return $val; 654 } 655 656 /** 657 * Method to commit a transaction. 658 * 659 * @param boolean $toSavepoint If true, commit to the last savepoint. 660 * 661 * @return void 662 * 663 * @since 1.0 664 * @throws \RuntimeException 665 */ 666 public function transactionCommit($toSavepoint = false) 667 { 668 $this->connect(); 669 670 if (!$toSavepoint || $this->transactionDepth <= 1) 671 { 672 parent::transactionCommit($toSavepoint); 673 } 674 else 675 { 676 $this->transactionDepth--; 677 } 678 } 679 680 /** 681 * Method to roll back a transaction. 682 * 683 * @param boolean $toSavepoint If true, rollback to the last savepoint. 684 * 685 * @return void 686 * 687 * @since 1.0 688 * @throws \RuntimeException 689 */ 690 public function transactionRollback($toSavepoint = false) 691 { 692 $this->connect(); 693 694 if (!$toSavepoint || $this->transactionDepth <= 1) 695 { 696 parent::transactionRollback($toSavepoint); 697 } 698 else 699 { 700 $savepoint = 'SP_' . ($this->transactionDepth - 1); 701 $this->setQuery('ROLLBACK TO SAVEPOINT ' . $this->quoteName($savepoint)); 702 703 if ($this->execute()) 704 { 705 $this->transactionDepth--; 706 $this->setQuery('RELEASE SAVEPOINT ' . $this->quoteName($savepoint))->execute(); 707 } 708 } 709 } 710 711 /** 712 * Method to initialize a transaction. 713 * 714 * @param boolean $asSavepoint If true and a transaction is already active, a savepoint will be created. 715 * 716 * @return void 717 * 718 * @since 1.0 719 * @throws \RuntimeException 720 */ 721 public function transactionStart($asSavepoint = false) 722 { 723 $this->connect(); 724 725 if (!$asSavepoint || !$this->transactionDepth) 726 { 727 parent::transactionStart($asSavepoint); 728 } 729 else 730 { 731 $savepoint = 'SP_' . $this->transactionDepth; 732 $this->setQuery('SAVEPOINT ' . $this->quoteName($savepoint)); 733 734 if ($this->execute()) 735 { 736 $this->transactionDepth++; 737 } 738 } 739 } 740 741 /** 742 * Inserts a row into a table based on an object's properties. 743 * 744 * @param string $table The name of the database table to insert into. 745 * @param object $object A reference to an object whose public properties match the table fields. 746 * @param string $key The name of the primary key. If provided the object property is updated. 747 * 748 * @return boolean True on success. 749 * 750 * @since 1.5.0 751 * @throws \RuntimeException 752 */ 753 public function insertObject($table, &$object, $key = null) 754 { 755 $columns = $this->getTableColumns($table); 756 757 $fields = []; 758 $values = []; 759 760 // Iterate over the object variables to build the query fields and values. 761 foreach (get_object_vars($object) as $k => $v) 762 { 763 // Skip columns that don't exist in the table. 764 if (!\array_key_exists($k, $columns)) 765 { 766 continue; 767 } 768 769 // Only process non-null scalars. 770 if (\is_array($v) || \is_object($v) || $v === null) 771 { 772 continue; 773 } 774 775 // Ignore any internal fields or primary keys with value 0. 776 if (($k[0] === '_') || ($k == $key && (($v === 0) || ($v === '0')))) 777 { 778 continue; 779 } 780 781 // Ignore null timestamp fields. 782 if ($columns[$k] === 'timestamp without time zone' && empty($v)) 783 { 784 continue; 785 } 786 787 // Prepare and sanitize the fields and values for the database query. 788 $fields[] = $this->quoteName($k); 789 $values[] = $this->sqlValue($columns, $k, $v); 790 } 791 792 // Create the base insert statement. 793 $query = $this->getQuery(true); 794 795 $query->insert($this->quoteName($table)) 796 ->columns($fields) 797 ->values(implode(',', $values)); 798 799 if ($key) 800 { 801 $query->returning($key); 802 803 // Set the query and execute the insert. 804 $object->$key = $this->setQuery($query)->loadResult(); 805 } 806 else 807 { 808 // Set the query and execute the insert. 809 $this->setQuery($query)->execute(); 810 } 811 812 return true; 813 } 814 815 /** 816 * Test to see if the PostgreSQL connector is available. 817 * 818 * @return boolean True on success, false otherwise. 819 * 820 * @since 1.5.0 821 */ 822 public static function isSupported() 823 { 824 return class_exists('\\PDO') && \in_array('pgsql', \PDO::getAvailableDrivers(), true); 825 } 826 827 /** 828 * Returns an array containing database's table list. 829 * 830 * @return array The database's table list. 831 * 832 * @since 1.5.0 833 */ 834 public function showTables() 835 { 836 $query = $this->getQuery(true) 837 ->select('table_name') 838 ->from('information_schema.tables') 839 ->where('table_type=' . $this->quote('BASE TABLE')) 840 ->where('table_schema NOT IN (' . $this->quote('pg_catalog') . ', ' . $this->quote('information_schema') . ' )'); 841 842 $this->setQuery($query); 843 844 return $this->loadColumn(); 845 } 846 847 /** 848 * Get the substring position inside a string 849 * 850 * @param string $substring The string being sought 851 * @param string $string The string/column being searched 852 * 853 * @return integer The position of $substring in $string 854 * 855 * @since 1.5.0 856 */ 857 public function getStringPositionSql($substring, $string) 858 { 859 $this->setQuery("SELECT POSITION($substring IN $string)"); 860 $position = $this->loadRow(); 861 862 return $position['position']; 863 } 864 865 /** 866 * Generate a random value 867 * 868 * @return float The random generated number 869 * 870 * @since 1.5.0 871 */ 872 public function getRandom() 873 { 874 $this->setQuery('SELECT RANDOM()'); 875 $random = $this->loadAssoc(); 876 877 return $random['random']; 878 } 879 880 /** 881 * Get the query string to alter the database character set. 882 * 883 * @param string $dbName The database name 884 * 885 * @return string The query that alter the database query string 886 * 887 * @since 1.5.0 888 */ 889 public function getAlterDbCharacterSet($dbName) 890 { 891 return 'ALTER DATABASE ' . $this->quoteName($dbName) . ' SET CLIENT_ENCODING TO ' . $this->quote('UTF8'); 892 } 893 894 /** 895 * Get the query string to create new Database in correct PostgreSQL syntax. 896 * 897 * @param object $options object coming from "initialise" function to pass user and database name to database driver. 898 * @param boolean $utf True if the database supports the UTF-8 character set, not used in PostgreSQL "CREATE DATABASE" query. 899 * 900 * @return string The query that creates database, owned by $options['user'] 901 * 902 * @since 1.5.0 903 */ 904 public function getCreateDbQuery($options, $utf) 905 { 906 $query = 'CREATE DATABASE ' . $this->quoteName($options->db_name) . ' OWNER ' . $this->quoteName($options->db_user); 907 908 if ($utf) 909 { 910 $query .= ' ENCODING ' . $this->quote('UTF-8'); 911 } 912 913 return $query; 914 } 915 916 /** 917 * This function replaces a string identifier with the configured table prefix. 918 * 919 * @param string $sql The SQL statement to prepare. 920 * @param string $prefix The table prefix. 921 * 922 * @return string The processed SQL statement. 923 * 924 * @since 1.5.0 925 */ 926 public function replacePrefix($sql, $prefix = '#__') 927 { 928 $sql = trim($sql); 929 930 if (strpos($sql, '\'')) 931 { 932 // Sequence name quoted with ' ' but need to be replaced 933 if (strpos($sql, 'currval')) 934 { 935 $sql = explode('currval', $sql); 936 937 for ($nIndex = 1, $nIndexMax = \count($sql); $nIndex < $nIndexMax; $nIndex += 2) 938 { 939 $sql[$nIndex] = str_replace($prefix, $this->tablePrefix, $sql[$nIndex]); 940 } 941 942 $sql = implode('currval', $sql); 943 } 944 945 // Sequence name quoted with ' ' but need to be replaced 946 if (strpos($sql, 'nextval')) 947 { 948 $sql = explode('nextval', $sql); 949 950 for ($nIndex = 1, $nIndexMax = \count($sql); $nIndex < $nIndexMax; $nIndex += 2) 951 { 952 $sql[$nIndex] = str_replace($prefix, $this->tablePrefix, $sql[$nIndex]); 953 } 954 955 $sql = implode('nextval', $sql); 956 } 957 958 // Sequence name quoted with ' ' but need to be replaced 959 if (strpos($sql, 'setval')) 960 { 961 $sql = explode('setval', $sql); 962 963 for ($nIndex = 1, $nIndexMax = \count($sql); $nIndex < $nIndexMax; $nIndex += 2) 964 { 965 $sql[$nIndex] = str_replace($prefix, $this->tablePrefix, $sql[$nIndex]); 966 } 967 968 $sql = implode('setval', $sql); 969 } 970 971 $explodedQuery = explode('\'', $sql); 972 973 for ($nIndex = 0, $nIndexMax = \count($explodedQuery); $nIndex < $nIndexMax; $nIndex += 2) 974 { 975 if (strpos($explodedQuery[$nIndex], $prefix)) 976 { 977 $explodedQuery[$nIndex] = str_replace($prefix, $this->tablePrefix, $explodedQuery[$nIndex]); 978 } 979 } 980 981 $replacedQuery = implode('\'', $explodedQuery); 982 } 983 else 984 { 985 $replacedQuery = str_replace($prefix, $this->tablePrefix, $sql); 986 } 987 988 return $replacedQuery; 989 } 990 991 /** 992 * Unlocks tables in the database, this command does not exist in PostgreSQL, it is automatically done on commit or rollback. 993 * 994 * @return $this 995 * 996 * @since 1.5.0 997 * @throws \RuntimeException 998 */ 999 public function unlockTables() 1000 { 1001 $this->transactionCommit(); 1002 1003 return $this; 1004 } 1005 1006 /** 1007 * Updates a row in a table based on an object's properties. 1008 * 1009 * @param string $table The name of the database table to update. 1010 * @param object $object A reference to an object whose public properties match the table fields. 1011 * @param array|string $key The name of the primary key. 1012 * @param boolean $nulls True to update null fields or false to ignore them. 1013 * 1014 * @return boolean 1015 * 1016 * @since 1.5.0 1017 * @throws \RuntimeException 1018 */ 1019 public function updateObject($table, &$object, $key, $nulls = false) 1020 { 1021 $columns = $this->getTableColumns($table); 1022 $fields = []; 1023 $where = []; 1024 1025 if (\is_string($key)) 1026 { 1027 $key = [$key]; 1028 } 1029 1030 if (\is_object($key)) 1031 { 1032 $key = (array) $key; 1033 } 1034 1035 // Create the base update statement. 1036 $statement = 'UPDATE ' . $this->quoteName($table) . ' SET %s WHERE %s'; 1037 1038 // Iterate over the object variables to build the query fields/value pairs. 1039 foreach (get_object_vars($object) as $k => $v) 1040 { 1041 // Skip columns that don't exist in the table. 1042 if (!\array_key_exists($k, $columns)) 1043 { 1044 continue; 1045 } 1046 1047 // Only process scalars that are not internal fields. 1048 if (\is_array($v) || \is_object($v) || $k[0] === '_') 1049 { 1050 continue; 1051 } 1052 1053 // Set the primary key to the WHERE clause instead of a field to update. 1054 if (\in_array($k, $key, true)) 1055 { 1056 $key_val = $this->sqlValue($columns, $k, $v); 1057 $where[] = $this->quoteName($k) . '=' . $key_val; 1058 1059 continue; 1060 } 1061 1062 // Prepare and sanitize the fields and values for the database query. 1063 if ($v === null) 1064 { 1065 // If the value is null and we do not want to update nulls then ignore this field. 1066 if (!$nulls) 1067 { 1068 continue; 1069 } 1070 1071 // If the value is null and we want to update nulls then set it. 1072 $val = 'NULL'; 1073 } 1074 else 1075 { 1076 // The field is not null so we prep it for update. 1077 $val = $this->sqlValue($columns, $k, $v); 1078 } 1079 1080 // Add the field to be updated. 1081 $fields[] = $this->quoteName($k) . '=' . $val; 1082 } 1083 1084 // We don't have any fields to update. 1085 if (empty($fields)) 1086 { 1087 return true; 1088 } 1089 1090 // Set the query and execute the update. 1091 $this->setQuery(sprintf($statement, implode(',', $fields), implode(' AND ', $where))); 1092 1093 return $this->execute(); 1094 } 1095 1096 /** 1097 * Quotes a binary string to database requirements for use in database queries. 1098 * 1099 * @param string $data A binary string to quote. 1100 * 1101 * @return string The binary quoted input string. 1102 * 1103 * @since 1.7.0 1104 */ 1105 public function quoteBinary($data) 1106 { 1107 return "decode('" . bin2hex($data) . "', 'hex')"; 1108 } 1109 1110 /** 1111 * Replace special placeholder representing binary field with the original string. 1112 * 1113 * @param string|resource $data Encoded string or resource. 1114 * 1115 * @return string The original string. 1116 * 1117 * @since 1.7.0 1118 */ 1119 public function decodeBinary($data) 1120 { 1121 if (\is_resource($data)) 1122 { 1123 return stream_get_contents($data); 1124 } 1125 1126 return $data; 1127 } 1128 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Wed Sep 7 05:41:13 2022 | Chilli.vc Blog - For Webmaster,Blog-Writer,System Admin and Domainer |