1<?php 2/** 3 * @package FrameworkOnFramework 4 * @subpackage database 5 * @copyright Copyright (C) 2010-2016 Nicholas K. Dionysopoulos / Akeeba Ltd. All rights reserved. 6 * @license GNU General Public License version 2 or later; see LICENSE.txt 7 * @note This file has been modified by the Joomla! Project and no longer reflects the original work of its author. 8 * 9 * This file is adapted from the Joomla! Platform. It is used to iterate a database cursor returning FOFTable objects 10 * instead of plain stdClass objects 11 */ 12 13// Protect from unauthorized access 14defined('FOF_INCLUDED') or die; 15 16/** 17 * PostgreSQL database driver 18 * 19 * @since 12.1 20 */ 21class FOFDatabaseDriverPostgresql extends FOFDatabaseDriver 22{ 23 /** 24 * The database driver name 25 * 26 * @var string 27 * @since 12.1 28 */ 29 public $name = 'postgresql'; 30 31 /** 32 * The type of the database server family supported by this driver. 33 * 34 * @var string 35 * @since CMS 3.5.0 36 */ 37 public $serverType = 'postgresql'; 38 39 /** 40 * Quote for named objects 41 * 42 * @var string 43 * @since 12.1 44 */ 45 protected $nameQuote = '"'; 46 47 /** 48 * The null/zero date string 49 * 50 * @var string 51 * @since 12.1 52 */ 53 protected $nullDate = '1970-01-01 00:00:00'; 54 55 /** 56 * The minimum supported database version. 57 * 58 * @var string 59 * @since 12.1 60 */ 61 protected static $dbMinimum = '8.3.18'; 62 63 /** 64 * Operator used for concatenation 65 * 66 * @var string 67 * @since 12.1 68 */ 69 protected $concat_operator = '||'; 70 71 /** 72 * FOFDatabaseDriverPostgresqlQuery object returned by getQuery 73 * 74 * @var FOFDatabaseDriverPostgresqlQuery 75 * @since 12.1 76 */ 77 protected $queryObject = null; 78 79 /** 80 * Database object constructor 81 * 82 * @param array $options List of options used to configure the connection 83 * 84 * @since 12.1 85 */ 86 public function __construct( $options ) 87 { 88 $options['host'] = (isset($options['host'])) ? $options['host'] : 'localhost'; 89 $options['user'] = (isset($options['user'])) ? $options['user'] : ''; 90 $options['password'] = (isset($options['password'])) ? $options['password'] : ''; 91 $options['database'] = (isset($options['database'])) ? $options['database'] : ''; 92 93 // Finalize initialization 94 parent::__construct($options); 95 } 96 97 /** 98 * Database object destructor 99 * 100 * @since 12.1 101 */ 102 public function __destruct() 103 { 104 $this->disconnect(); 105 } 106 107 /** 108 * Connects to the database if needed. 109 * 110 * @return void Returns void if the database connected successfully. 111 * 112 * @since 12.1 113 * @throws RuntimeException 114 */ 115 public function connect() 116 { 117 if ($this->connection) 118 { 119 return; 120 } 121 122 // Make sure the postgresql extension for PHP is installed and enabled. 123 if (!function_exists('pg_connect')) 124 { 125 throw new RuntimeException('PHP extension pg_connect is not available.'); 126 } 127 128 // Build the DSN for the connection. 129 $dsn = ''; 130 131 if (!empty($this->options['host'])) 132 { 133 $dsn .= "host={$this->options['host']} "; 134 } 135 136 $dsn .= "dbname={$this->options['database']} user={$this->options['user']} password={$this->options['password']}"; 137 138 // Attempt to connect to the server. 139 if (!($this->connection = @pg_connect($dsn))) 140 { 141 throw new RuntimeException('Error connecting to PGSQL database.'); 142 } 143 144 pg_set_error_verbosity($this->connection, PGSQL_ERRORS_DEFAULT); 145 pg_query('SET standard_conforming_strings=off'); 146 pg_query('SET escape_string_warning=off'); 147 } 148 149 /** 150 * Disconnects the database. 151 * 152 * @return void 153 * 154 * @since 12.1 155 */ 156 public function disconnect() 157 { 158 // Close the connection. 159 if (is_resource($this->connection)) 160 { 161 foreach ($this->disconnectHandlers as $h) 162 { 163 call_user_func_array($h, array( &$this)); 164 } 165 166 pg_close($this->connection); 167 } 168 169 $this->connection = null; 170 } 171 172 /** 173 * Method to escape a string for usage in an SQL statement. 174 * 175 * @param string $text The string to be escaped. 176 * @param boolean $extra Optional parameter to provide extra escaping. 177 * 178 * @return string The escaped string. 179 * 180 * @since 12.1 181 */ 182 public function escape($text, $extra = false) 183 { 184 $this->connect(); 185 186 $result = pg_escape_string($this->connection, $text); 187 188 if ($extra) 189 { 190 $result = addcslashes($result, '%_'); 191 } 192 193 return $result; 194 } 195 196 /** 197 * Test to see if the PostgreSQL connector is available 198 * 199 * @return boolean True on success, false otherwise. 200 * 201 * @since 12.1 202 */ 203 public static function test() 204 { 205 return (function_exists('pg_connect')); 206 } 207 208 /** 209 * Determines if the connection to the server is active. 210 * 211 * @return boolean 212 * 213 * @since 12.1 214 */ 215 public function connected() 216 { 217 $this->connect(); 218 219 if (is_resource($this->connection)) 220 { 221 return pg_ping($this->connection); 222 } 223 224 return false; 225 } 226 227 /** 228 * Drops a table from the database. 229 * 230 * @param string $tableName The name of the database table to drop. 231 * @param boolean $ifExists Optionally specify that the table must exist before it is dropped. 232 * 233 * @return boolean 234 * 235 * @since 12.1 236 * @throws RuntimeException 237 */ 238 public function dropTable($tableName, $ifExists = true) 239 { 240 $this->connect(); 241 242 $this->setQuery('DROP TABLE ' . ($ifExists ? 'IF EXISTS ' : '') . $this->quoteName($tableName)); 243 $this->execute(); 244 245 return true; 246 } 247 248 /** 249 * Get the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE for the previous executed SQL statement. 250 * 251 * @return integer The number of affected rows in the previous operation 252 * 253 * @since 12.1 254 */ 255 public function getAffectedRows() 256 { 257 $this->connect(); 258 259 return pg_affected_rows($this->cursor); 260 } 261 262 /** 263 * Method to get the database collation in use by sampling a text field of a table in the database. 264 * 265 * @return mixed The collation in use by the database or boolean false if not supported. 266 * 267 * @since 12.1 268 * @throws RuntimeException 269 */ 270 public function getCollation() 271 { 272 $this->connect(); 273 274 $this->setQuery('SHOW LC_COLLATE'); 275 $array = $this->loadAssocList(); 276 277 return $array[0]['lc_collate']; 278 } 279 280 /** 281 * Method to get the database connection collation, as reported by the driver. If the connector doesn't support 282 * reporting this value please return an empty string. 283 * 284 * @return string 285 */ 286 public function getConnectionCollation() 287 { 288 return pg_client_encoding($this->connection); 289 } 290 291 /** 292 * Get the number of returned rows for the previous executed SQL statement. 293 * This command is only valid for statements like SELECT or SHOW that return an actual result set. 294 * To retrieve the number of rows affected by a INSERT, UPDATE, REPLACE or DELETE query, use getAffectedRows(). 295 * 296 * @param resource $cur An optional database cursor resource to extract the row count from. 297 * 298 * @return integer The number of returned rows. 299 * 300 * @since 12.1 301 */ 302 public function getNumRows($cur = null) 303 { 304 $this->connect(); 305 306 return pg_num_rows((int) $cur ? $cur : $this->cursor); 307 } 308 309 /** 310 * Get the current or query, or new FOFDatabaseQuery object. 311 * 312 * @param boolean $new False to return the last query set, True to return a new FOFDatabaseQuery object. 313 * @param boolean $asObj False to return last query as string, true to get FOFDatabaseQueryPostgresql object. 314 * 315 * @return FOFDatabaseQuery The current query object or a new object extending the FOFDatabaseQuery class. 316 * 317 * @since 12.1 318 * @throws RuntimeException 319 */ 320 public function getQuery($new = false, $asObj = false) 321 { 322 if ($new) 323 { 324 // Make sure we have a query class for this driver. 325 if (!class_exists('FOFDatabaseQueryPostgresql')) 326 { 327 throw new RuntimeException('FOFDatabaseQueryPostgresql Class not found.'); 328 } 329 330 $this->queryObject = new FOFDatabaseQueryPostgresql($this); 331 332 return $this->queryObject; 333 } 334 else 335 { 336 if ($asObj) 337 { 338 return $this->queryObject; 339 } 340 else 341 { 342 return $this->sql; 343 } 344 } 345 } 346 347 /** 348 * Shows the table CREATE statement that creates the given tables. 349 * 350 * This is unsupported by PostgreSQL. 351 * 352 * @param mixed $tables A table name or a list of table names. 353 * 354 * @return string An empty char because this function is not supported by PostgreSQL. 355 * 356 * @since 12.1 357 */ 358 public function getTableCreate($tables) 359 { 360 return ''; 361 } 362 363 /** 364 * Retrieves field information about a given table. 365 * 366 * @param string $table The name of the database table. 367 * @param boolean $typeOnly True to only return field types. 368 * 369 * @return array An array of fields for the database table. 370 * 371 * @since 12.1 372 * @throws RuntimeException 373 */ 374 public function getTableColumns($table, $typeOnly = true) 375 { 376 $this->connect(); 377 378 $result = array(); 379 380 $tableSub = $this->replacePrefix($table); 381 382 $this->setQuery(' 383 SELECT a.attname AS "column_name", 384 pg_catalog.format_type(a.atttypid, a.atttypmod) as "type", 385 CASE WHEN a.attnotnull IS TRUE 386 THEN \'NO\' 387 ELSE \'YES\' 388 END AS "null", 389 CASE WHEN pg_catalog.pg_get_expr(adef.adbin, adef.adrelid, true) IS NOT NULL 390 THEN pg_catalog.pg_get_expr(adef.adbin, adef.adrelid, true) 391 END as "Default", 392 CASE WHEN pg_catalog.col_description(a.attrelid, a.attnum) IS NULL 393 THEN \'\' 394 ELSE pg_catalog.col_description(a.attrelid, a.attnum) 395 END AS "comments" 396 FROM pg_catalog.pg_attribute a 397 LEFT JOIN pg_catalog.pg_attrdef adef ON a.attrelid=adef.adrelid AND a.attnum=adef.adnum 398 LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid 399 WHERE a.attrelid = 400 (SELECT oid FROM pg_catalog.pg_class WHERE relname=' . $this->quote($tableSub) . ' 401 AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE 402 nspname = \'public\') 403 ) 404 AND a.attnum > 0 AND NOT a.attisdropped 405 ORDER BY a.attnum' 406 ); 407 408 $fields = $this->loadObjectList(); 409 410 if ($typeOnly) 411 { 412 foreach ($fields as $field) 413 { 414 $result[$field->column_name] = preg_replace("/[(0-9)]/", '', $field->type); 415 } 416 } 417 else 418 { 419 foreach ($fields as $field) 420 { 421 if (stristr(strtolower($field->type), "character varying")) 422 { 423 $field->Default = ""; 424 } 425 if (stristr(strtolower($field->type), "text")) 426 { 427 $field->Default = ""; 428 } 429 // Do some dirty translation to MySQL output. 430 // TODO: Come up with and implement a standard across databases. 431 $result[$field->column_name] = (object) array( 432 'column_name' => $field->column_name, 433 'type' => $field->type, 434 'null' => $field->null, 435 'Default' => $field->Default, 436 'comments' => '', 437 'Field' => $field->column_name, 438 'Type' => $field->type, 439 'Null' => $field->null, 440 // TODO: Improve query above to return primary key info as well 441 // 'Key' => ($field->PK == '1' ? 'PRI' : '') 442 ); 443 } 444 } 445 446 /* Change Postgresql's NULL::* type with PHP's null one */ 447 foreach ($fields as $field) 448 { 449 if (preg_match("/^NULL::*/", $field->Default)) 450 { 451 $field->Default = null; 452 } 453 } 454 455 return $result; 456 } 457 458 /** 459 * Get the details list of keys for a table. 460 * 461 * @param string $table The name of the table. 462 * 463 * @return array An array of the column specification for the table. 464 * 465 * @since 12.1 466 * @throws RuntimeException 467 */ 468 public function getTableKeys($table) 469 { 470 $this->connect(); 471 472 // To check if table exists and prevent SQL injection 473 $tableList = $this->getTableList(); 474 475 if (in_array($table, $tableList)) 476 { 477 // Get the details columns information. 478 $this->setQuery(' 479 SELECT indexname AS "idxName", indisprimary AS "isPrimary", indisunique AS "isUnique", 480 CASE WHEN indisprimary = true THEN 481 ( SELECT \'ALTER TABLE \' || tablename || \' ADD \' || pg_catalog.pg_get_constraintdef(const.oid, true) 482 FROM pg_constraint AS const WHERE const.conname= pgClassFirst.relname ) 483 ELSE pg_catalog.pg_get_indexdef(indexrelid, 0, true) 484 END AS "Query" 485 FROM pg_indexes 486 LEFT JOIN pg_class AS pgClassFirst ON indexname=pgClassFirst.relname 487 LEFT JOIN pg_index AS pgIndex ON pgClassFirst.oid=pgIndex.indexrelid 488 WHERE tablename=' . $this->quote($table) . ' ORDER BY indkey' 489 ); 490 491 $keys = $this->loadObjectList(); 492 493 return $keys; 494 } 495 496 return false; 497 } 498 499 /** 500 * Method to get an array of all tables in the database. 501 * 502 * @return array An array of all the tables in the database. 503 * 504 * @since 12.1 505 * @throws RuntimeException 506 */ 507 public function getTableList() 508 { 509 $this->connect(); 510 511 $query = $this->getQuery(true) 512 ->select('table_name') 513 ->from('information_schema.tables') 514 ->where('table_type=' . $this->quote('BASE TABLE')) 515 ->where('table_schema NOT IN (' . $this->quote('pg_catalog') . ', ' . $this->quote('information_schema') . ')') 516 ->order('table_name ASC'); 517 518 $this->setQuery($query); 519 $tables = $this->loadColumn(); 520 521 return $tables; 522 } 523 524 /** 525 * Get the details list of sequences for a table. 526 * 527 * @param string $table The name of the table. 528 * 529 * @return array An array of sequences specification for the table. 530 * 531 * @since 12.1 532 * @throws RuntimeException 533 */ 534 public function getTableSequences($table) 535 { 536 $this->connect(); 537 538 // To check if table exists and prevent SQL injection 539 $tableList = $this->getTableList(); 540 541 if (in_array($table, $tableList)) 542 { 543 $name = array( 544 's.relname', 'n.nspname', 't.relname', 'a.attname', 'info.data_type', 'info.minimum_value', 'info.maximum_value', 545 'info.increment', 'info.cycle_option' 546 ); 547 $as = array('sequence', 'schema', 'table', 'column', 'data_type', 'minimum_value', 'maximum_value', 'increment', 'cycle_option'); 548 549 if (version_compare($this->getVersion(), '9.1.0') >= 0) 550 { 551 $name[] .= 'info.start_value'; 552 $as[] .= 'start_value'; 553 } 554 555 // Get the details columns information. 556 $query = $this->getQuery(true) 557 ->select($this->quoteName($name, $as)) 558 ->from('pg_class AS s') 559 ->join('LEFT', "pg_depend d ON d.objid=s.oid AND d.classid='pg_class'::regclass AND d.refclassid='pg_class'::regclass") 560 ->join('LEFT', 'pg_class t ON t.oid=d.refobjid') 561 ->join('LEFT', 'pg_namespace n ON n.oid=t.relnamespace') 562 ->join('LEFT', 'pg_attribute a ON a.attrelid=t.oid AND a.attnum=d.refobjsubid') 563 ->join('LEFT', 'information_schema.sequences AS info ON info.sequence_name=s.relname') 564 ->where("s.relkind='S' AND d.deptype='a' AND t.relname=" . $this->quote($table)); 565 $this->setQuery($query); 566 $seq = $this->loadObjectList(); 567 568 return $seq; 569 } 570 571 return false; 572 } 573 574 /** 575 * Get the version of the database connector. 576 * 577 * @return string The database connector version. 578 * 579 * @since 12.1 580 */ 581 public function getVersion() 582 { 583 $this->connect(); 584 $version = pg_version($this->connection); 585 586 return $version['server']; 587 } 588 589 /** 590 * Method to get the auto-incremented value from the last INSERT statement. 591 * To be called after the INSERT statement, it's MANDATORY to have a sequence on 592 * every primary key table. 593 * 594 * To get the auto incremented value it's possible to call this function after 595 * INSERT INTO query, or use INSERT INTO with RETURNING clause. 596 * 597 * @example with insertid() call: 598 * $query = $this->getQuery(true) 599 * ->insert('jos_dbtest') 600 * ->columns('title,start_date,description') 601 * ->values("'testTitle2nd','1971-01-01','testDescription2nd'"); 602 * $this->setQuery($query); 603 * $this->execute(); 604 * $id = $this->insertid(); 605 * 606 * @example with RETURNING clause: 607 * $query = $this->getQuery(true) 608 * ->insert('jos_dbtest') 609 * ->columns('title,start_date,description') 610 * ->values("'testTitle2nd','1971-01-01','testDescription2nd'") 611 * ->returning('id'); 612 * $this->setQuery($query); 613 * $id = $this->loadResult(); 614 * 615 * @return integer The value of the auto-increment field from the last inserted row. 616 * 617 * @since 12.1 618 */ 619 public function insertid() 620 { 621 $this->connect(); 622 $insertQuery = $this->getQuery(false, true); 623 $table = $insertQuery->__get('insert')->getElements(); 624 625 /* find sequence column name */ 626 $colNameQuery = $this->getQuery(true); 627 $colNameQuery->select('column_default') 628 ->from('information_schema.columns') 629 ->where("table_name=" . $this->quote($this->replacePrefix(str_replace('"', '', $table[0]))), 'AND') 630 ->where("column_default LIKE '%nextval%'"); 631 632 $this->setQuery($colNameQuery); 633 $colName = $this->loadRow(); 634 $changedColName = str_replace('nextval', 'currval', $colName); 635 636 $insertidQuery = $this->getQuery(true); 637 $insertidQuery->select($changedColName); 638 $this->setQuery($insertidQuery); 639 $insertVal = $this->loadRow(); 640 641 return $insertVal[0]; 642 } 643 644 /** 645 * Locks a table in the database. 646 * 647 * @param string $tableName The name of the table to unlock. 648 * 649 * @return FOFDatabaseDriverPostgresql Returns this object to support chaining. 650 * 651 * @since 12.1 652 * @throws RuntimeException 653 */ 654 public function lockTable($tableName) 655 { 656 $this->transactionStart(); 657 $this->setQuery('LOCK TABLE ' . $this->quoteName($tableName) . ' IN ACCESS EXCLUSIVE MODE')->execute(); 658 659 return $this; 660 } 661 662 /** 663 * Execute the SQL statement. 664 * 665 * @return mixed A database cursor resource on success, boolean false on failure. 666 * 667 * @since 12.1 668 * @throws RuntimeException 669 */ 670 public function execute() 671 { 672 $this->connect(); 673 674 if (!is_resource($this->connection)) 675 { 676 if (class_exists('JLog')) 677 { 678 JLog::add(JText::sprintf('JLIB_DATABASE_QUERY_FAILED', $this->errorNum, $this->errorMsg), JLog::ERROR, 'database'); 679 } 680 throw new RuntimeException($this->errorMsg, $this->errorNum); 681 } 682 683 // Take a local copy so that we don't modify the original query and cause issues later 684 $query = $this->replacePrefix((string) $this->sql); 685 686 if (!($this->sql instanceof FOFDatabaseQuery) && ($this->limit > 0 || $this->offset > 0)) 687 { 688 $query .= ' LIMIT ' . $this->limit . ' OFFSET ' . $this->offset; 689 } 690 691 // Increment the query counter. 692 $this->count++; 693 694 // Reset the error values. 695 $this->errorNum = 0; 696 $this->errorMsg = ''; 697 698 // If debugging is enabled then let's log the query. 699 if ($this->debug) 700 { 701 // Add the query to the object queue. 702 $this->log[] = $query; 703 704 if (class_exists('JLog')) 705 { 706 JLog::add($query, JLog::DEBUG, 'databasequery'); 707 } 708 709 $this->timings[] = microtime(true); 710 } 711 712 // Execute the query. Error suppression is used here to prevent warnings/notices that the connection has been lost. 713 $this->cursor = @pg_query($this->connection, $query); 714 715 if ($this->debug) 716 { 717 $this->timings[] = microtime(true); 718 719 if (defined('DEBUG_BACKTRACE_IGNORE_ARGS')) 720 { 721 $this->callStacks[] = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); 722 } 723 else 724 { 725 $this->callStacks[] = debug_backtrace(); 726 } 727 } 728 729 // If an error occurred handle it. 730 if (!$this->cursor) 731 { 732 // Get the error number and message before we execute any more queries. 733 $errorNum = $this->getErrorNumber(); 734 $errorMsg = $this->getErrorMessage($query); 735 736 // Check if the server was disconnected. 737 if (!$this->connected()) 738 { 739 try 740 { 741 // Attempt to reconnect. 742 $this->connection = null; 743 $this->connect(); 744 } 745 // If connect fails, ignore that exception and throw the normal exception. 746 catch (RuntimeException $e) 747 { 748 $this->errorNum = $this->getErrorNumber(); 749 $this->errorMsg = $this->getErrorMessage($query); 750 751 // Throw the normal query exception. 752 if (class_exists('JLog')) 753 { 754 JLog::add(JText::sprintf('JLIB_DATABASE_QUERY_FAILED', $this->errorNum, $this->errorMsg), JLog::ERROR, 'database-error'); 755 } 756 757 throw new RuntimeException($this->errorMsg, null, $e); 758 } 759 760 // Since we were able to reconnect, run the query again. 761 return $this->execute(); 762 } 763 // The server was not disconnected. 764 else 765 { 766 // Get the error number and message from before we tried to reconnect. 767 $this->errorNum = $errorNum; 768 $this->errorMsg = $errorMsg; 769 770 // Throw the normal query exception. 771 if (class_exists('JLog')) 772 { 773 JLog::add(JText::sprintf('JLIB_DATABASE_QUERY_FAILED', $this->errorNum, $this->errorMsg), JLog::ERROR, 'database-error'); 774 } 775 776 throw new RuntimeException($this->errorMsg); 777 } 778 } 779 780 return $this->cursor; 781 } 782 783 /** 784 * Renames a table in the database. 785 * 786 * @param string $oldTable The name of the table to be renamed 787 * @param string $newTable The new name for the table. 788 * @param string $backup Not used by PostgreSQL. 789 * @param string $prefix Not used by PostgreSQL. 790 * 791 * @return FOFDatabaseDriverPostgresql Returns this object to support chaining. 792 * 793 * @since 12.1 794 * @throws RuntimeException 795 */ 796 public function renameTable($oldTable, $newTable, $backup = null, $prefix = null) 797 { 798 $this->connect(); 799 800 // To check if table exists and prevent SQL injection 801 $tableList = $this->getTableList(); 802 803 // Origin Table does not exist 804 if (!in_array($oldTable, $tableList)) 805 { 806 // Origin Table not found 807 throw new RuntimeException('Table not found in Postgresql database.'); 808 } 809 else 810 { 811 /* Rename indexes */ 812 $this->setQuery( 813 'SELECT relname 814 FROM pg_class 815 WHERE oid IN ( 816 SELECT indexrelid 817 FROM pg_index, pg_class 818 WHERE pg_class.relname=' . $this->quote($oldTable, true) . ' 819 AND pg_class.oid=pg_index.indrelid );' 820 ); 821 822 $oldIndexes = $this->loadColumn(); 823 824 foreach ($oldIndexes as $oldIndex) 825 { 826 $changedIdxName = str_replace($oldTable, $newTable, $oldIndex); 827 $this->setQuery('ALTER INDEX ' . $this->escape($oldIndex) . ' RENAME TO ' . $this->escape($changedIdxName)); 828 $this->execute(); 829 } 830 831 /* Rename sequence */ 832 $this->setQuery( 833 'SELECT relname 834 FROM pg_class 835 WHERE relkind = \'S\' 836 AND relnamespace IN ( 837 SELECT oid 838 FROM pg_namespace 839 WHERE nspname NOT LIKE \'pg_%\' 840 AND nspname != \'information_schema\' 841 ) 842 AND relname LIKE \'%' . $oldTable . '%\' ;' 843 ); 844 845 $oldSequences = $this->loadColumn(); 846 847 foreach ($oldSequences as $oldSequence) 848 { 849 $changedSequenceName = str_replace($oldTable, $newTable, $oldSequence); 850 $this->setQuery('ALTER SEQUENCE ' . $this->escape($oldSequence) . ' RENAME TO ' . $this->escape($changedSequenceName)); 851 $this->execute(); 852 } 853 854 /* Rename table */ 855 $this->setQuery('ALTER TABLE ' . $this->escape($oldTable) . ' RENAME TO ' . $this->escape($newTable)); 856 $this->execute(); 857 } 858 859 return true; 860 } 861 862 /** 863 * Selects the database, but redundant for PostgreSQL 864 * 865 * @param string $database Database name to select. 866 * 867 * @return boolean Always true 868 * 869 * @since 12.1 870 */ 871 public function select($database) 872 { 873 return true; 874 } 875 876 /** 877 * Custom settings for UTF support 878 * 879 * @return integer Zero on success, -1 on failure 880 * 881 * @since 12.1 882 */ 883 public function setUtf() 884 { 885 $this->connect(); 886 887 return pg_set_client_encoding($this->connection, 'UTF8'); 888 } 889 890 /** 891 * This function return a field value as a prepared string to be used in a SQL statement. 892 * 893 * @param array $columns Array of table's column returned by ::getTableColumns. 894 * @param string $field_name The table field's name. 895 * @param string $field_value The variable value to quote and return. 896 * 897 * @return string The quoted string. 898 * 899 * @since 12.1 900 */ 901 public function sqlValue($columns, $field_name, $field_value) 902 { 903 switch ($columns[$field_name]) 904 { 905 case 'boolean': 906 $val = 'NULL'; 907 908 if ($field_value == 't') 909 { 910 $val = 'TRUE'; 911 } 912 elseif ($field_value == 'f') 913 { 914 $val = 'FALSE'; 915 } 916 917 break; 918 919 case 'bigint': 920 case 'bigserial': 921 case 'integer': 922 case 'money': 923 case 'numeric': 924 case 'real': 925 case 'smallint': 926 case 'serial': 927 case 'numeric,': 928 $val = strlen($field_value) == 0 ? 'NULL' : $field_value; 929 break; 930 931 case 'date': 932 case 'timestamp without time zone': 933 if (empty($field_value)) 934 { 935 $field_value = $this->getNullDate(); 936 } 937 938 $val = $this->quote($field_value); 939 break; 940 941 default: 942 $val = $this->quote($field_value); 943 break; 944 } 945 946 return $val; 947 } 948 949 /** 950 * Method to commit a transaction. 951 * 952 * @param boolean $toSavepoint If true, commit to the last savepoint. 953 * 954 * @return void 955 * 956 * @since 12.1 957 * @throws RuntimeException 958 */ 959 public function transactionCommit($toSavepoint = false) 960 { 961 $this->connect(); 962 963 if (!$toSavepoint || $this->transactionDepth <= 1) 964 { 965 if ($this->setQuery('COMMIT')->execute()) 966 { 967 $this->transactionDepth = 0; 968 } 969 970 return; 971 } 972 973 $this->transactionDepth--; 974 } 975 976 /** 977 * Method to roll back a transaction. 978 * 979 * @param boolean $toSavepoint If true, rollback to the last savepoint. 980 * 981 * @return void 982 * 983 * @since 12.1 984 * @throws RuntimeException 985 */ 986 public function transactionRollback($toSavepoint = false) 987 { 988 $this->connect(); 989 990 if (!$toSavepoint || $this->transactionDepth <= 1) 991 { 992 if ($this->setQuery('ROLLBACK')->execute()) 993 { 994 $this->transactionDepth = 0; 995 } 996 997 return; 998 } 999 1000 $savepoint = 'SP_' . ($this->transactionDepth - 1); 1001 $this->setQuery('ROLLBACK TO SAVEPOINT ' . $this->quoteName($savepoint)); 1002 1003 if ($this->execute()) 1004 { 1005 $this->transactionDepth--; 1006 $this->setQuery('RELEASE SAVEPOINT ' . $this->quoteName($savepoint))->execute(); 1007 } 1008 } 1009 1010 /** 1011 * Method to initialize a transaction. 1012 * 1013 * @param boolean $asSavepoint If true and a transaction is already active, a savepoint will be created. 1014 * 1015 * @return void 1016 * 1017 * @since 12.1 1018 * @throws RuntimeException 1019 */ 1020 public function transactionStart($asSavepoint = false) 1021 { 1022 $this->connect(); 1023 1024 if (!$asSavepoint || !$this->transactionDepth) 1025 { 1026 if ($this->setQuery('START TRANSACTION')->execute()) 1027 { 1028 $this->transactionDepth = 1; 1029 } 1030 1031 return; 1032 } 1033 1034 $savepoint = 'SP_' . $this->transactionDepth; 1035 $this->setQuery('SAVEPOINT ' . $this->quoteName($savepoint)); 1036 1037 if ($this->execute()) 1038 { 1039 $this->transactionDepth++; 1040 } 1041 } 1042 1043 /** 1044 * Method to fetch a row from the result set cursor as an array. 1045 * 1046 * @param mixed $cursor The optional result set cursor from which to fetch the row. 1047 * 1048 * @return mixed Either the next row from the result set or false if there are no more rows. 1049 * 1050 * @since 12.1 1051 */ 1052 protected function fetchArray($cursor = null) 1053 { 1054 return pg_fetch_row($cursor ? $cursor : $this->cursor); 1055 } 1056 1057 /** 1058 * Method to fetch a row from the result set cursor as an associative array. 1059 * 1060 * @param mixed $cursor The optional result set cursor from which to fetch the row. 1061 * 1062 * @return mixed Either the next row from the result set or false if there are no more rows. 1063 * 1064 * @since 12.1 1065 */ 1066 protected function fetchAssoc($cursor = null) 1067 { 1068 return pg_fetch_assoc($cursor ? $cursor : $this->cursor); 1069 } 1070 1071 /** 1072 * Method to fetch a row from the result set cursor as an object. 1073 * 1074 * @param mixed $cursor The optional result set cursor from which to fetch the row. 1075 * @param string $class The class name to use for the returned row object. 1076 * 1077 * @return mixed Either the next row from the result set or false if there are no more rows. 1078 * 1079 * @since 12.1 1080 */ 1081 protected function fetchObject($cursor = null, $class = 'stdClass') 1082 { 1083 return pg_fetch_object(is_null($cursor) ? $this->cursor : $cursor, null, $class); 1084 } 1085 1086 /** 1087 * Method to free up the memory used for the result set. 1088 * 1089 * @param mixed $cursor The optional result set cursor from which to fetch the row. 1090 * 1091 * @return void 1092 * 1093 * @since 12.1 1094 */ 1095 protected function freeResult($cursor = null) 1096 { 1097 pg_free_result($cursor ? $cursor : $this->cursor); 1098 } 1099 1100 /** 1101 * Inserts a row into a table based on an object's properties. 1102 * 1103 * @param string $table The name of the database table to insert into. 1104 * @param object &$object A reference to an object whose public properties match the table fields. 1105 * @param string $key The name of the primary key. If provided the object property is updated. 1106 * 1107 * @return boolean True on success. 1108 * 1109 * @since 12.1 1110 * @throws RuntimeException 1111 */ 1112 public function insertObject($table, &$object, $key = null) 1113 { 1114 $columns = $this->getTableColumns($table); 1115 1116 $fields = array(); 1117 $values = array(); 1118 1119 // Iterate over the object variables to build the query fields and values. 1120 foreach (get_object_vars($object) as $k => $v) 1121 { 1122 // Only process non-null scalars. 1123 if (is_array($v) or is_object($v) or $v === null) 1124 { 1125 continue; 1126 } 1127 1128 // Ignore any internal fields or primary keys with value 0. 1129 if (($k[0] == "_") || ($k == $key && (($v === 0) || ($v === '0')))) 1130 { 1131 continue; 1132 } 1133 1134 // Prepare and sanitize the fields and values for the database query. 1135 $fields[] = $this->quoteName($k); 1136 $values[] = $this->sqlValue($columns, $k, $v); 1137 } 1138 1139 // Create the base insert statement. 1140 $query = $this->getQuery(true) 1141 ->insert($this->quoteName($table)) 1142 ->columns($fields) 1143 ->values(implode(',', $values)); 1144 1145 $retVal = false; 1146 1147 if ($key) 1148 { 1149 $query->returning($key); 1150 1151 // Set the query and execute the insert. 1152 $this->setQuery($query); 1153 1154 $id = $this->loadResult(); 1155 1156 if ($id) 1157 { 1158 $object->$key = $id; 1159 $retVal = true; 1160 } 1161 } 1162 else 1163 { 1164 // Set the query and execute the insert. 1165 $this->setQuery($query); 1166 1167 if ($this->execute()) 1168 { 1169 $retVal = true; 1170 } 1171 } 1172 1173 return $retVal; 1174 } 1175 1176 /** 1177 * Test to see if the PostgreSQL connector is available. 1178 * 1179 * @return boolean True on success, false otherwise. 1180 * 1181 * @since 12.1 1182 */ 1183 public static function isSupported() 1184 { 1185 return (function_exists('pg_connect')); 1186 } 1187 1188 /** 1189 * Returns an array containing database's table list. 1190 * 1191 * @return array The database's table list. 1192 * 1193 * @since 12.1 1194 */ 1195 public function showTables() 1196 { 1197 $this->connect(); 1198 1199 $query = $this->getQuery(true) 1200 ->select('table_name') 1201 ->from('information_schema.tables') 1202 ->where('table_type = ' . $this->quote('BASE TABLE')) 1203 ->where('table_schema NOT IN (' . $this->quote('pg_catalog') . ', ' . $this->quote('information_schema') . ' )'); 1204 1205 $this->setQuery($query); 1206 $tableList = $this->loadColumn(); 1207 1208 return $tableList; 1209 } 1210 1211 /** 1212 * Get the substring position inside a string 1213 * 1214 * @param string $substring The string being sought 1215 * @param string $string The string/column being searched 1216 * 1217 * @return integer The position of $substring in $string 1218 * 1219 * @since 12.1 1220 */ 1221 public function getStringPositionSql( $substring, $string ) 1222 { 1223 $this->connect(); 1224 1225 $query = "SELECT POSITION( $substring IN $string )"; 1226 $this->setQuery($query); 1227 $position = $this->loadRow(); 1228 1229 return $position['position']; 1230 } 1231 1232 /** 1233 * Generate a random value 1234 * 1235 * @return float The random generated number 1236 * 1237 * @since 12.1 1238 */ 1239 public function getRandom() 1240 { 1241 $this->connect(); 1242 1243 $this->setQuery('SELECT RANDOM()'); 1244 $random = $this->loadAssoc(); 1245 1246 return $random['random']; 1247 } 1248 1249 /** 1250 * Get the query string to alter the database character set. 1251 * 1252 * @param string $dbName The database name 1253 * 1254 * @return string The query that alter the database query string 1255 * 1256 * @since 12.1 1257 */ 1258 public function getAlterDbCharacterSet( $dbName ) 1259 { 1260 $query = 'ALTER DATABASE ' . $this->quoteName($dbName) . ' SET CLIENT_ENCODING TO ' . $this->quote('UTF8'); 1261 1262 return $query; 1263 } 1264 1265 /** 1266 * Get the query string to create new Database in correct PostgreSQL syntax. 1267 * 1268 * @param object $options object coming from "initialise" function to pass user and database name to database driver. 1269 * @param boolean $utf True if the database supports the UTF-8 character set, not used in PostgreSQL "CREATE DATABASE" query. 1270 * 1271 * @return string The query that creates database, owned by $options['user'] 1272 * 1273 * @since 12.1 1274 */ 1275 public function getCreateDbQuery($options, $utf) 1276 { 1277 $query = 'CREATE DATABASE ' . $this->quoteName($options->db_name) . ' OWNER ' . $this->quoteName($options->db_user); 1278 1279 if ($utf) 1280 { 1281 $query .= ' ENCODING ' . $this->quote('UTF-8'); 1282 } 1283 1284 return $query; 1285 } 1286 1287 /** 1288 * This function replaces a string identifier <var>$prefix</var> with the string held is the 1289 * <var>tablePrefix</var> class variable. 1290 * 1291 * @param string $query The SQL statement to prepare. 1292 * @param string $prefix The common table prefix. 1293 * 1294 * @return string The processed SQL statement. 1295 * 1296 * @since 12.1 1297 */ 1298 public function replacePrefix($query, $prefix = '#__') 1299 { 1300 $query = trim($query); 1301 1302 if (strpos($query, '\'')) 1303 { 1304 // Sequence name quoted with ' ' but need to be replaced 1305 if (strpos($query, 'currval')) 1306 { 1307 $query = explode('currval', $query); 1308 1309 for ($nIndex = 1; $nIndex < count($query); $nIndex = $nIndex + 2) 1310 { 1311 $query[$nIndex] = str_replace($prefix, $this->tablePrefix, $query[$nIndex]); 1312 } 1313 1314 $query = implode('currval', $query); 1315 } 1316 1317 // Sequence name quoted with ' ' but need to be replaced 1318 if (strpos($query, 'nextval')) 1319 { 1320 $query = explode('nextval', $query); 1321 1322 for ($nIndex = 1; $nIndex < count($query); $nIndex = $nIndex + 2) 1323 { 1324 $query[$nIndex] = str_replace($prefix, $this->tablePrefix, $query[$nIndex]); 1325 } 1326 1327 $query = implode('nextval', $query); 1328 } 1329 1330 // Sequence name quoted with ' ' but need to be replaced 1331 if (strpos($query, 'setval')) 1332 { 1333 $query = explode('setval', $query); 1334 1335 for ($nIndex = 1; $nIndex < count($query); $nIndex = $nIndex + 2) 1336 { 1337 $query[$nIndex] = str_replace($prefix, $this->tablePrefix, $query[$nIndex]); 1338 } 1339 1340 $query = implode('setval', $query); 1341 } 1342 1343 $explodedQuery = explode('\'', $query); 1344 1345 for ($nIndex = 0; $nIndex < count($explodedQuery); $nIndex = $nIndex + 2) 1346 { 1347 if (strpos($explodedQuery[$nIndex], $prefix)) 1348 { 1349 $explodedQuery[$nIndex] = str_replace($prefix, $this->tablePrefix, $explodedQuery[$nIndex]); 1350 } 1351 } 1352 1353 $replacedQuery = implode('\'', $explodedQuery); 1354 } 1355 else 1356 { 1357 $replacedQuery = str_replace($prefix, $this->tablePrefix, $query); 1358 } 1359 1360 return $replacedQuery; 1361 } 1362 1363 /** 1364 * Method to release a savepoint. 1365 * 1366 * @param string $savepointName Savepoint's name to release 1367 * 1368 * @return void 1369 * 1370 * @since 12.1 1371 */ 1372 public function releaseTransactionSavepoint( $savepointName ) 1373 { 1374 $this->connect(); 1375 $this->setQuery('RELEASE SAVEPOINT ' . $this->quoteName($this->escape($savepointName))); 1376 $this->execute(); 1377 } 1378 1379 /** 1380 * Method to create a savepoint. 1381 * 1382 * @param string $savepointName Savepoint's name to create 1383 * 1384 * @return void 1385 * 1386 * @since 12.1 1387 */ 1388 public function transactionSavepoint( $savepointName ) 1389 { 1390 $this->connect(); 1391 $this->setQuery('SAVEPOINT ' . $this->quoteName($this->escape($savepointName))); 1392 $this->execute(); 1393 } 1394 1395 /** 1396 * Unlocks tables in the database, this command does not exist in PostgreSQL, 1397 * it is automatically done on commit or rollback. 1398 * 1399 * @return FOFDatabaseDriverPostgresql Returns this object to support chaining. 1400 * 1401 * @since 12.1 1402 * @throws RuntimeException 1403 */ 1404 public function unlockTables() 1405 { 1406 $this->transactionCommit(); 1407 1408 return $this; 1409 } 1410 1411 /** 1412 * Updates a row in a table based on an object's properties. 1413 * 1414 * @param string $table The name of the database table to update. 1415 * @param object &$object A reference to an object whose public properties match the table fields. 1416 * @param array $key The name of the primary key. 1417 * @param boolean $nulls True to update null fields or false to ignore them. 1418 * 1419 * @return boolean True on success. 1420 * 1421 * @since 12.1 1422 * @throws RuntimeException 1423 */ 1424 public function updateObject($table, &$object, $key, $nulls = false) 1425 { 1426 $columns = $this->getTableColumns($table); 1427 $fields = array(); 1428 $where = array(); 1429 1430 if (is_string($key)) 1431 { 1432 $key = array($key); 1433 } 1434 1435 if (is_object($key)) 1436 { 1437 $key = (array) $key; 1438 } 1439 1440 // Create the base update statement. 1441 $statement = 'UPDATE ' . $this->quoteName($table) . ' SET %s WHERE %s'; 1442 1443 // Iterate over the object variables to build the query fields/value pairs. 1444 foreach (get_object_vars($object) as $k => $v) 1445 { 1446 // Only process scalars that are not internal fields. 1447 if (is_array($v) or is_object($v) or $k[0] == '_') 1448 { 1449 continue; 1450 } 1451 1452 // Set the primary key to the WHERE clause instead of a field to update. 1453 if (in_array($k, $key)) 1454 { 1455 $key_val = $this->sqlValue($columns, $k, $v); 1456 $where[] = $this->quoteName($k) . '=' . $key_val; 1457 continue; 1458 } 1459 1460 // Prepare and sanitize the fields and values for the database query. 1461 if ($v === null) 1462 { 1463 // If the value is null and we want to update nulls then set it. 1464 if ($nulls) 1465 { 1466 $val = 'NULL'; 1467 } 1468 // If the value is null and we do not want to update nulls then ignore this field. 1469 else 1470 { 1471 continue; 1472 } 1473 } 1474 // The field is not null so we prep it for update. 1475 else 1476 { 1477 $val = $this->sqlValue($columns, $k, $v); 1478 } 1479 1480 // Add the field to be updated. 1481 $fields[] = $this->quoteName($k) . '=' . $val; 1482 } 1483 1484 // We don't have any fields to update. 1485 if (empty($fields)) 1486 { 1487 return true; 1488 } 1489 1490 // Set the query and execute the update. 1491 $this->setQuery(sprintf($statement, implode(",", $fields), implode(' AND ', $where))); 1492 1493 return $this->execute(); 1494 } 1495 1496 /** 1497 * Return the actual SQL Error number 1498 * 1499 * @return integer The SQL Error number 1500 * 1501 * @since 3.4.6 1502 */ 1503 protected function getErrorNumber() 1504 { 1505 return (int) pg_result_error_field($this->cursor, PGSQL_DIAG_SQLSTATE) . ' '; 1506 } 1507 1508 /** 1509 * Return the actual SQL Error message 1510 * 1511 * @param string $query The SQL Query that fails 1512 * 1513 * @return string The SQL Error message 1514 * 1515 * @since 3.4.6 1516 */ 1517 protected function getErrorMessage($query) 1518 { 1519 $errorMessage = (string) pg_last_error($this->connection); 1520 1521 // Replace the Databaseprefix with `#__` if we are not in Debug 1522 if (!$this->debug) 1523 { 1524 $errorMessage = str_replace($this->tablePrefix, '#__', $errorMessage); 1525 $query = str_replace($this->tablePrefix, '#__', $query); 1526 } 1527 1528 return $errorMessage . "SQL=" . $query; 1529 } 1530} 1531