[Summary view]

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401]

   1  <?php
   2  /**
   3   * PDO PostgreSQL (pgsql) driver
   4   *
   5   * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
   6   *
   7   * @package ADOdb
   8   * @link https://adodb.org Project's web site and documentation
   9   * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
  10   *
  11   * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
  12   * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
  13   * any later version. This means you can use it in proprietary products.
  14   * See the LICENSE.md file distributed with this source code for details.
  15   * @license BSD-3-Clause
  16   * @license LGPL-2.1-or-later
  17   *
  18   * @copyright 2000-2013 John Lim
  19   * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
  20   */
  21  
  22  class ADODB_pdo_pgsql extends ADODB_pdo {
  23  	 var $metaDatabasesSQL = "select datname from pg_database where datname not in ('template0','template1') order by 1";
  24  	 var $metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
  25  	 	 and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages',
  26  	 	 	 'sql_packages', 'sql_sizing', 'sql_sizing_profiles')
  27  	 	 union
  28  	 	 select viewname,'V' from pg_views where viewname not like 'pg\_%'";
  29  	 //"select tablename from pg_tables where tablename not like 'pg_%' order by 1";
  30  	 var $isoDates = true; // accepts dates in ISO format
  31  	 var $sysDate = "CURRENT_DATE";
  32  	 var $sysTimeStamp = "CURRENT_TIMESTAMP";
  33  	 var $blobEncodeType = 'C';
  34  	 var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum
  35  	 	 FROM pg_class c, pg_attribute a,pg_type t
  36  	 	 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) and a.attname not like '....%%'
  37  	 	 AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
  38  
  39  	 // used when schema defined
  40  	 var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
  41  	 	 FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
  42  	 	 WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s'))
  43  	 	 and c.relnamespace=n.oid and n.nspname='%s'
  44  	 	 and a.attname not like '....%%' AND a.attnum > 0
  45  	 	 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
  46  
  47  	 // get primary key etc -- from Freek Dijkstra
  48  	 var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key
  49  	 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'";
  50  
  51  	 var $hasAffectedRows = true;
  52  	 var $hasLimit = false;	 // set to true for pgsql 7 only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
  53  	 // below suggested by Freek Dijkstra
  54  	 var $true = 't';	 	 // string that represents TRUE for a database
  55  	 var $false = 'f';	 	 // string that represents FALSE for a database
  56  	 var $fmtDate = "'Y-m-d'";	 // used by DBDate() as the default date format used by the database
  57  	 var $fmtTimeStamp = "'Y-m-d G:i:s'"; // used by DBTimeStamp as the default timestamp fmt.
  58  	 var $hasMoveFirst = true;
  59  	 var $hasGenID = true;
  60  	 var $_genIDSQL = "SELECT NEXTVAL('%s')";
  61  	 var $_genSeqSQL = "CREATE SEQUENCE %s START %s";
  62  	 var $_dropSeqSQL = "DROP SEQUENCE %s";
  63  	 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";
  64  	 var $random = 'random()';	 	 /// random function
  65  	 var $concat_operator='||';
  66  
  67  	function _init($parentDriver)
  68  	 {
  69  
  70  	 	 $parentDriver->hasTransactions = false; ## <<< BUG IN PDO pgsql driver
  71  	 	 $parentDriver->hasInsertID = true;
  72  	 	 $parentDriver->_nestedSQL = true;
  73  	 }
  74  
  75  	function ServerInfo()
  76  	 {
  77  	 	 $arr['description'] = ADOConnection::GetOne("select version()");
  78  	 	 $arr['version'] = ADOConnection::_findvers($arr['description']);
  79  	 	 return $arr;
  80  	 }
  81  
  82  	function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
  83  	 {
  84  	 	 $nrows = (int) $nrows;
  85  	 	 $offset = (int) $offset;
  86  	 	 $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
  87  	 	 $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : '';
  88  	 	 if ($secs2cache)
  89  	 	 	 $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
  90  	 	 else
  91  	 	 	 $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
  92  
  93  	 	 return $rs;
  94  	 }
  95  
  96  	function MetaTables($ttype=false,$showSchema=false,$mask=false)
  97  	 {
  98  	 	 $info = $this->ServerInfo();
  99  	 	 if ($info['version'] >= 7.3) {
 100  	 	 	 $this->metaTablesSQL = "
 101  select tablename,'T' from pg_tables
 102  where tablename not like 'pg\_%' and schemaname  not in ( 'pg_catalog','information_schema')
 103  union
 104  select viewname,'V' from pg_views
 105  where viewname not like 'pg\_%'  and schemaname  not in ( 'pg_catalog','information_schema')";
 106  	 	 }
 107  	 	 if ($mask) {
 108  	 	 	 $save = $this->metaTablesSQL;
 109  	 	 	 $mask = $this->qstr(strtolower($mask));
 110  	 	 	 if ($info['version']>=7.3)
 111  	 	 	 	 $this->metaTablesSQL = "
 112  select tablename,'T' from pg_tables
 113  where tablename like $mask and schemaname not in ( 'pg_catalog','information_schema')
 114  union
 115  select viewname,'V' from pg_views
 116  where viewname like $mask and schemaname  not in ( 'pg_catalog','information_schema')";
 117  	 	 	 else
 118  	 	 	 	 $this->metaTablesSQL = "
 119  select tablename,'T' from pg_tables where tablename like $mask
 120  union
 121  select viewname,'V' from pg_views where viewname like $mask";
 122  	 	 }
 123  	 	 $ret = ADOConnection::MetaTables($ttype,$showSchema);
 124  
 125  	 	 if ($mask) {
 126  	 	 	 $this->metaTablesSQL = $save;
 127  	 	 }
 128  	 	 return $ret;
 129  	 }
 130  
 131  	function MetaColumns($table,$normalize=true)
 132  	 {
 133  	 global $ADODB_FETCH_MODE;
 134  
 135  	 	 $schema = false;
 136  	 	 $this->_findschema($table,$schema);
 137  
 138  	 	 if ($normalize) $table = strtolower($table);
 139  
 140  	 	 $save = $ADODB_FETCH_MODE;
 141  	 	 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 142  	 	 if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
 143  
 144  	 	 if ($schema) $rs = $this->Execute(sprintf($this->metaColumnsSQL1,$table,$table,$schema));
 145  	 	 else $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table,$table));
 146  	 	 if (isset($savem)) $this->SetFetchMode($savem);
 147  	 	 $ADODB_FETCH_MODE = $save;
 148  
 149  	 	 if ($rs === false) {
 150  	 	 	 $false = false;
 151  	 	 	 return $false;
 152  	 	 }
 153  	 	 if (!empty($this->metaKeySQL)) {
 154  	 	 	 // If we want the primary keys, we have to issue a separate query
 155  	 	 	 // Of course, a modified version of the metaColumnsSQL query using a
 156  	 	 	 // LEFT JOIN would have been much more elegant, but postgres does
 157  	 	 	 // not support OUTER JOINS. So here is the clumsy way.
 158  
 159  	 	 	 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
 160  
 161  	 	 	 $rskey = $this->Execute(sprintf($this->metaKeySQL,($table)));
 162  	 	 	 // fetch all result in once for performance.
 163  	 	 	 $keys = $rskey->GetArray();
 164  	 	 	 if (isset($savem)) $this->SetFetchMode($savem);
 165  	 	 	 $ADODB_FETCH_MODE = $save;
 166  
 167  	 	 	 $rskey->Close();
 168  	 	 	 unset($rskey);
 169  	 	 }
 170  
 171  	 	 $rsdefa = array();
 172  	 	 if (!empty($this->metaDefaultsSQL)) {
 173  	 	 	 $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
 174  	 	 	 $sql = sprintf($this->metaDefaultsSQL, ($table));
 175  	 	 	 $rsdef = $this->Execute($sql);
 176  	 	 	 if (isset($savem)) $this->SetFetchMode($savem);
 177  	 	 	 $ADODB_FETCH_MODE = $save;
 178  
 179  	 	 	 if ($rsdef) {
 180  	 	 	 	 while (!$rsdef->EOF) {
 181  	 	 	 	 	 $num = $rsdef->fields['num'];
 182  	 	 	 	 	 $s = $rsdef->fields['def'];
 183  	 	 	 	 	 if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
 184  	 	 	 	 	 	 $s = substr($s, 1);
 185  	 	 	 	 	 	 $s = substr($s, 0, strlen($s) - 1);
 186  	 	 	 	 	 }
 187  
 188  	 	 	 	 	 $rsdefa[$num] = $s;
 189  	 	 	 	 	 $rsdef->MoveNext();
 190  	 	 	 	 }
 191  	 	 	 } else {
 192  	 	 	 	 ADOConnection::outp( "==> SQL => " . $sql);
 193  	 	 	 }
 194  	 	 	 unset($rsdef);
 195  	 	 }
 196  
 197  	 	 $retarr = array();
 198  	 	 while (!$rs->EOF) {
 199  	 	 	 $fld = new ADOFieldObject();
 200  	 	 	 $fld->name = $rs->fields[0];
 201  	 	 	 $fld->type = $rs->fields[1];
 202  	 	 	 $fld->max_length = $rs->fields[2];
 203  	 	 	 if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
 204  	 	 	 if ($fld->max_length <= 0) $fld->max_length = -1;
 205  	 	 	 if ($fld->type == 'numeric') {
 206  	 	 	 	 $fld->scale = $fld->max_length & 0xFFFF;
 207  	 	 	 	 $fld->max_length >>= 16;
 208  	 	 	 }
 209  	 	 	 // dannym
 210  	 	 	 // 5 hasdefault; 6 num-of-column
 211  	 	 	 $fld->has_default = ($rs->fields[5] == 't');
 212  	 	 	 if ($fld->has_default) {
 213  	 	 	 	 $fld->default_value = $rsdefa[$rs->fields[6]];
 214  	 	 	 }
 215  
 216  	 	 	 //Freek
 217  	 	 	 if ($rs->fields[4] == $this->true) {
 218  	 	 	 	 $fld->not_null = true;
 219  	 	 	 }
 220  
 221  	 	 	 // Freek
 222  	 	 	 if (is_array($keys)) {
 223  	 	 	 	 foreach($keys as $key) {
 224  	 	 	 	 	 if ($fld->name == $key['column_name'] AND $key['primary_key'] == $this->true)
 225  	 	 	 	 	 	 $fld->primary_key = true;
 226  	 	 	 	 	 if ($fld->name == $key['column_name'] AND $key['unique_key'] == $this->true)
 227  	 	 	 	 	 	 $fld->unique = true; // What name is more compatible?
 228  	 	 	 	 }
 229  	 	 	 }
 230  
 231  	 	 	 if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
 232  	 	 	 else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
 233  
 234  	 	 	 $rs->MoveNext();
 235  	 	 }
 236  	 	 $rs->Close();
 237  	 	 if (empty($retarr)) {
 238  	 	 	 $false = false;
 239  	 	 	 return $false;
 240  	 	 } else return $retarr;
 241  
 242  	 }
 243  
 244  	function BeginTrans()
 245  	 {
 246  	 	 if (!$this->hasTransactions) {
 247  	 	 	 return false;
 248  	 	 }
 249  	 	 if ($this->transOff) {
 250  	 	 	 return true;
 251  	 	 }
 252  	 	 $this->transCnt += 1;
 253  
 254  	 	 return $this->_connectionID->beginTransaction();
 255  	 }
 256  
 257  	function CommitTrans($ok = true)
 258  	 {
 259  	 	 if (!$this->hasTransactions) {
 260  	 	 	 return false;
 261  	 	 }
 262  	 	 if ($this->transOff) {
 263  	 	 	 return true;
 264  	 	 }
 265  	 	 if (!$ok) {
 266  	 	 	 return $this->RollbackTrans();
 267  	 	 }
 268  	 	 if ($this->transCnt) {
 269  	 	 	 $this->transCnt -= 1;
 270  	 	 }
 271  	 	 $this->_autocommit = true;
 272  
 273  	 	 $ret = $this->_connectionID->commit();
 274  	 	 return $ret;
 275  	 }
 276  
 277  	function RollbackTrans()
 278  	 {
 279  	 	 if (!$this->hasTransactions) {
 280  	 	 	 return false;
 281  	 	 }
 282  	 	 if ($this->transOff) {
 283  	 	 	 return true;
 284  	 	 }
 285  	 	 if ($this->transCnt) {
 286  	 	 	 $this->transCnt -= 1;
 287  	 	 }
 288  	 	 $this->_autocommit = true;
 289  
 290  	 	 $ret = $this->_connectionID->rollback();
 291  	 	 return $ret;
 292  	 }
 293  
 294  	function SetTransactionMode( $transaction_mode )
 295  	 {
 296  	 	 $this->_transmode  = $transaction_mode;
 297  	 	 if (empty($transaction_mode)) {
 298  	 	 	 $this->_connectionID->query('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
 299  	 	 	 return;
 300  	 	 }
 301  	 	 if (!stristr($transaction_mode,'isolation')) $transaction_mode = 'ISOLATION LEVEL '.$transaction_mode;
 302  	 	 $this->_connectionID->query("SET TRANSACTION ".$transaction_mode);
 303  	 }
 304  
 305  	 /**
 306  	  * Returns a driver-specific format for a bind parameter
 307  	  *
 308  	  * Unlike the native driver, we use :name parameters
 309  	  * instead of offsets
 310  	  *
 311  	  * @param string $name
 312  	  * @param string $type (ignored in driver)
 313  	  *
 314  	  * @return string
 315  	  */
 316  	public function param($name,$type='C') {
 317  	 	 if (!$name) {
 318  	 	 	 return '';
 319  	 	 }
 320  
 321  	 	 return sprintf(':%s', $name);
 322  	 }
 323  }