Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 4.1.x will end 13 November 2023 (12 months).
  • Bug fixes for security issues in 4.1.x will end 10 November 2025 (36 months).
  • PHP version: minimum PHP 7.4.0 Note: minimum PHP version has increased since Moodle 4.0. PHP 8.0.x is supported too.
<?php
/**
 * PDO PostgreSQL (pgsql) driver
 *
 * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
 *
 * @package ADOdb
 * @link https://adodb.org Project's web site and documentation
 * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
 *
 * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
 * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
 * any later version. This means you can use it in proprietary products.
 * See the LICENSE.md file distributed with this source code for details.
 * @license BSD-3-Clause
 * @license LGPL-2.1-or-later
 *
 * @copyright 2000-2013 John Lim
 * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
 */

class ADODB_pdo_pgsql extends ADODB_pdo {
	var $metaDatabasesSQL = "select datname from pg_database where datname not in ('template0','template1') order by 1";
    var $metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
	and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages',
	 'sql_packages', 'sql_sizing', 'sql_sizing_profiles')
	union
        select viewname,'V' from pg_views where viewname not like 'pg\_%'";
	//"select tablename from pg_tables where tablename not like 'pg_%' order by 1";
	var $isoDates = true; // accepts dates in ISO format
	var $sysDate = "CURRENT_DATE";
	var $sysTimeStamp = "CURRENT_TIMESTAMP";
	var $blobEncodeType = 'C';
	var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum
		FROM pg_class c, pg_attribute a,pg_type t
		WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) and a.attname not like '....%%'
AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";

	// used when schema defined
	var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s'))
 and c.relnamespace=n.oid and n.nspname='%s'
	and a.attname not like '....%%' AND a.attnum > 0
	AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";

	// get primary key etc -- from Freek Dijkstra
	var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key
	FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a WHERE bc.oid = i.indrelid AND ic.oid = i.indexrelid AND (i.indkey[0] = a.attnum OR i.indkey[1] = a.attnum OR i.indkey[2] = a.attnum OR i.indkey[3] = a.attnum OR i.indkey[4] = a.attnum OR i.indkey[5] = a.attnum OR i.indkey[6] = a.attnum OR i.indkey[7] = a.attnum) AND a.attrelid = bc.oid AND bc.relname = '%s'";

	var $hasAffectedRows = true;
	var $hasLimit = false;	// set to true for pgsql 7 only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
	// below suggested by Freek Dijkstra
	var $true = 't';		// string that represents TRUE for a database
	var $false = 'f';		// string that represents FALSE for a database
	var $fmtDate = "'Y-m-d'";	// used by DBDate() as the default date format used by the database
	var $fmtTimeStamp = "'Y-m-d G:i:s'"; // used by DBTimeStamp as the default timestamp fmt.
	var $hasMoveFirst = true;
	var $hasGenID = true;
	var $_genIDSQL = "SELECT NEXTVAL('%s')";
	var $_genSeqSQL = "CREATE SEQUENCE %s START %s";
	var $_dropSeqSQL = "DROP SEQUENCE %s";
	var $metaDefaultsSQL = "SELECT d.adnum as num, d.adsrc as def from pg_attrdef d, pg_class c where d.adrelid=c.oid and c.relname='%s' order by d.adnum";
	var $random = 'random()';		/// random function
	var $concat_operator='||';

	function _init($parentDriver)
	{

		$parentDriver->hasTransactions = false; ## <<< BUG IN PDO pgsql driver
		$parentDriver->hasInsertID = true;
		$parentDriver->_nestedSQL = true;
	}

	function ServerInfo()
	{
		$arr['description'] = ADOConnection::GetOne("select version()");
		$arr['version'] = ADOConnection::_findvers($arr['description']);
		return $arr;
	}

	function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
	{
		$nrows = (int) $nrows;
		$offset = (int) $offset;
		$offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
		$limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : '';
		if ($secs2cache)
			$rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
		else
			$rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);

		return $rs;
	}

	function MetaTables($ttype=false,$showSchema=false,$mask=false)
	{
		$info = $this->ServerInfo();
		if ($info['version'] >= 7.3) {
< $this->metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%' < and schemaname not in ( 'pg_catalog','information_schema')
> $this->metaTablesSQL = " > select tablename,'T' from pg_tables > where tablename not like 'pg\_%' and schemaname not in ( 'pg_catalog','information_schema')
union
< select viewname,'V' from pg_views where viewname not like 'pg\_%' and schemaname not in ( 'pg_catalog','information_schema') ";
> select viewname,'V' from pg_views > where viewname not like 'pg\_%' and schemaname not in ( 'pg_catalog','information_schema')";
} if ($mask) { $save = $this->metaTablesSQL; $mask = $this->qstr(strtolower($mask)); if ($info['version']>=7.3) $this->metaTablesSQL = "
< select tablename,'T' from pg_tables where tablename like $mask and schemaname not in ( 'pg_catalog','information_schema')
> select tablename,'T' from pg_tables > where tablename like $mask and schemaname not in ( 'pg_catalog','information_schema')
union
< select viewname,'V' from pg_views where viewname like $mask and schemaname not in ( 'pg_catalog','information_schema') ";
> select viewname,'V' from pg_views > where viewname like $mask and schemaname not in ( 'pg_catalog','information_schema')";
else $this->metaTablesSQL = " select tablename,'T' from pg_tables where tablename like $mask union select viewname,'V' from pg_views where viewname like $mask"; } $ret = ADOConnection::MetaTables($ttype,$showSchema); if ($mask) { $this->metaTablesSQL = $save; } return $ret; } function MetaColumns($table,$normalize=true) { global $ADODB_FETCH_MODE; $schema = false; $this->_findschema($table,$schema); if ($normalize) $table = strtolower($table); $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false); if ($schema) $rs = $this->Execute(sprintf($this->metaColumnsSQL1,$table,$table,$schema)); else $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table,$table)); if (isset($savem)) $this->SetFetchMode($savem); $ADODB_FETCH_MODE = $save; if ($rs === false) { $false = false; return $false; } if (!empty($this->metaKeySQL)) { // If we want the primary keys, we have to issue a separate query // Of course, a modified version of the metaColumnsSQL query using a // LEFT JOIN would have been much more elegant, but postgres does // not support OUTER JOINS. So here is the clumsy way. $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; $rskey = $this->Execute(sprintf($this->metaKeySQL,($table))); // fetch all result in once for performance. $keys = $rskey->GetArray(); if (isset($savem)) $this->SetFetchMode($savem); $ADODB_FETCH_MODE = $save; $rskey->Close(); unset($rskey); } $rsdefa = array(); if (!empty($this->metaDefaultsSQL)) { $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC; $sql = sprintf($this->metaDefaultsSQL, ($table)); $rsdef = $this->Execute($sql); if (isset($savem)) $this->SetFetchMode($savem); $ADODB_FETCH_MODE = $save; if ($rsdef) { while (!$rsdef->EOF) { $num = $rsdef->fields['num']; $s = $rsdef->fields['def']; if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */ $s = substr($s, 1); $s = substr($s, 0, strlen($s) - 1); } $rsdefa[$num] = $s; $rsdef->MoveNext(); } } else { ADOConnection::outp( "==> SQL => " . $sql); } unset($rsdef); } $retarr = array(); while (!$rs->EOF) { $fld = new ADOFieldObject(); $fld->name = $rs->fields[0]; $fld->type = $rs->fields[1]; $fld->max_length = $rs->fields[2]; if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4; if ($fld->max_length <= 0) $fld->max_length = -1; if ($fld->type == 'numeric') { $fld->scale = $fld->max_length & 0xFFFF; $fld->max_length >>= 16; } // dannym // 5 hasdefault; 6 num-of-column $fld->has_default = ($rs->fields[5] == 't'); if ($fld->has_default) { $fld->default_value = $rsdefa[$rs->fields[6]]; } //Freek if ($rs->fields[4] == $this->true) { $fld->not_null = true; } // Freek if (is_array($keys)) { foreach($keys as $key) { if ($fld->name == $key['column_name'] AND $key['primary_key'] == $this->true) $fld->primary_key = true; if ($fld->name == $key['column_name'] AND $key['unique_key'] == $this->true) $fld->unique = true; // What name is more compatible? } } if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld; else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld; $rs->MoveNext(); } $rs->Close(); if (empty($retarr)) { $false = false; return $false; } else return $retarr; } function BeginTrans() { if (!$this->hasTransactions) { return false; } if ($this->transOff) { return true; } $this->transCnt += 1; return $this->_connectionID->beginTransaction(); } function CommitTrans($ok = true) { if (!$this->hasTransactions) { return false; } if ($this->transOff) { return true; } if (!$ok) { return $this->RollbackTrans(); } if ($this->transCnt) { $this->transCnt -= 1; } $this->_autocommit = true; $ret = $this->_connectionID->commit(); return $ret; } function RollbackTrans() { if (!$this->hasTransactions) { return false; } if ($this->transOff) { return true; } if ($this->transCnt) { $this->transCnt -= 1; } $this->_autocommit = true; $ret = $this->_connectionID->rollback(); return $ret; } function SetTransactionMode( $transaction_mode ) { $this->_transmode = $transaction_mode; if (empty($transaction_mode)) { $this->_connectionID->query('SET TRANSACTION ISOLATION LEVEL READ COMMITTED'); return; } if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode; $this->_connectionID->query("SET TRANSACTION ".$transaction_mode);
> } } > } > /** > * Returns a driver-specific format for a bind parameter > * > * Unlike the native driver, we use :name parameters > * instead of offsets > * > * @param string $name > * @param string $type (ignored in driver) > * > * @return string > */ > public function param($name,$type='C') { > if (!$name) { > return ''; > } > > return sprintf(':%s', $name);