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