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