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