[Summary view]

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

   1  <?php
   2  /**
   3   * ADOdb PostgreSQL 7 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  // security - hide paths
  23  if (!defined('ADODB_DIR')) die();
  24  
  25  include_once(ADODB_DIR."/drivers/adodb-postgres64.inc.php");
  26  
  27  class ADODB_postgres7 extends ADODB_postgres64 {
  28  	 var $databaseType = 'postgres7';
  29  	 var $hasLimit = true;	 // set to true for pgsql 6.5+ only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
  30  	 var $ansiOuter = true;
  31  	 var $charSet = true; //set to true for Postgres 7 and above - PG client supports encodings
  32  
  33  	 // Richard 3/18/2012 - Modified SQL to return SERIAL type correctly AS old driver no longer return SERIAL as data type.
  34  	 var $metaColumnsSQL = "
  35  	 	 SELECT
  36  	 	 	 a.attname,
  37  	 	 	 CASE
  38  	 	 	 	 WHEN x.sequence_name != ''
  39  	 	 	 	 THEN 'SERIAL'
  40  	 	 	 	 ELSE t.typname
  41  	 	 	 END AS typname,
  42  	 	 	 a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
  43  	 	 FROM
  44  	 	 	 pg_class c,
  45  	 	 	 pg_attribute a
  46  	 	 JOIN pg_type t ON a.atttypid = t.oid
  47  	 	 LEFT JOIN (
  48  	 	 	 SELECT
  49  	 	 	 	 c.relname as sequence_name,
  50  	 	 	 	 c1.relname as related_table,
  51  	 	 	 	 a.attname as related_column
  52  	 	 	 FROM pg_class c
  53  	 	 	 JOIN pg_depend d ON d.objid = c.oid
  54  	 	 	 LEFT JOIN pg_class c1 ON d.refobjid = c1.oid
  55  	 	 	 LEFT JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
  56  	 	 	 WHERE c.relkind = 'S' AND c1.relname = '%s'
  57  	 	 ) x ON x.related_column= a.attname
  58  	 	 WHERE
  59  	 	 	 c.relkind in ('r','v')
  60  	 	 	 AND (c.relname='%s' or c.relname = lower('%s'))
  61  	 	 	 AND a.attname not like '....%%'
  62  	 	 	 AND a.attnum > 0
  63  	 	 	 AND a.attrelid = c.oid
  64  	 	 ORDER BY
  65  	 	 	 a.attnum";
  66  
  67  	 // used when schema defined
  68  	 var $metaColumnsSQL1 = "
  69  	 	 SELECT
  70  	 	 	 a.attname,
  71  	 	 	 CASE
  72  	 	 	 	 WHEN x.sequence_name != ''
  73  	 	 	 	 THEN 'SERIAL'
  74  	 	 	 	 ELSE t.typname
  75  	 	 	 END AS typname,
  76  	 	 	 a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
  77  	 	 FROM
  78  	 	 	 pg_class c,
  79  	 	 	 pg_namespace n,
  80  	 	 	 pg_attribute a
  81  	 	 JOIN pg_type t ON a.atttypid = t.oid
  82  	 	 LEFT JOIN (
  83  	 	 	 SELECT
  84  	 	 	 	 c.relname as sequence_name,
  85  	 	 	 	 c1.relname as related_table,
  86  	 	 	 	 a.attname as related_column
  87  	 	 	 FROM pg_class c
  88  	 	 	 JOIN pg_depend d ON d.objid = c.oid
  89  	 	 	 LEFT JOIN pg_class c1 ON d.refobjid = c1.oid
  90  	 	 	 LEFT JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
  91  	 	 	 WHERE c.relkind = 'S' AND c1.relname = '%s'
  92  	 	 ) x ON x.related_column= a.attname
  93  	 	 WHERE
  94  	 	 	 c.relkind in ('r','v')
  95  	 	 	 AND (c.relname='%s' or c.relname = lower('%s'))
  96  	 	 	 AND c.relnamespace=n.oid and n.nspname='%s'
  97  	 	 	 AND a.attname not like '....%%'
  98  	 	 	 AND a.attnum > 0
  99  	 	 	 AND a.atttypid = t.oid
 100  	 	 	 AND a.attrelid = c.oid
 101  	 	 ORDER BY a.attnum";
 102  
 103  
 104  	function __construct()
 105  	 {
 106  	 	 parent::__construct();
 107  	 	 if (ADODB_ASSOC_CASE !== ADODB_ASSOC_CASE_NATIVE) {
 108  	 	 	 $this->rsPrefix .= 'assoc_';
 109  	 	 }
 110  	 	 $this->_bindInputArray = true;
 111  	 }
 112  
 113  
 114  	 // the following should be compat with postgresql 7.2,
 115  	 // which makes obsolete the LIMIT limit,offset syntax
 116  	function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
 117  	 {
 118  	 	 $nrows = (int) $nrows;
 119  	 	 $offset = (int) $offset;
 120  	 	 $offsetStr = ($offset >= 0) ? " OFFSET ".((integer)$offset) : '';
 121  	 	 $limitStr  = ($nrows >= 0)  ? " LIMIT ".((integer)$nrows) : '';
 122  	 	 if ($secs2cache)
 123  	 	 	 $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
 124  	 	 else
 125  	 	 	 $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
 126  
 127  	 	 return $rs;
 128  	 }
 129  
 130  	 /*
 131  	 function Prepare($sql)
 132  	 {
 133  	 	 $info = $this->ServerInfo();
 134  	 	 if ($info['version']>=7.3) {
 135  	 	 	 return array($sql,false);
 136  	 	 }
 137  	 	 return $sql;
 138  	 }
 139  	 */
 140  
 141  	 /**
 142  	  * Generate the SQL to retrieve MetaColumns data
 143  	  * @param string $table Table name
 144  	  * @param string $schema Schema name (can be blank)
 145  	  * @return string SQL statement to execute
 146  	  */
 147  	protected function _generateMetaColumnsSQL($table, $schema)
 148  	 {
 149  	 	 if ($schema) {
 150  	 	 	 return sprintf($this->metaColumnsSQL1, $table, $table, $table, $schema);
 151  	 	 }
 152  	 	 else {
 153  	 	 	 return sprintf($this->metaColumnsSQL, $table, $table, $schema);
 154  	 	 }
 155  	 }
 156  
 157  	 /**
 158  	  * @returns assoc array where keys are tables, and values are foreign keys
 159  	  */
 160  	function MetaForeignKeys($table, $owner=false, $upper=false)
 161  	 {
 162  	 	 # Regex isolates the 2 terms between parenthesis using subexpressions
 163  	 	 $regex = '^.*\((.*)\).*\((.*)\).*$';
 164  	 	 $sql="
 165  	 	 	 SELECT
 166  	 	 	 	 lookup_table,
 167  	 	 	 	 regexp_replace(consrc, '$regex', '\\2') AS lookup_field,
 168  	 	 	 	 dep_table,
 169  	 	 	 	 regexp_replace(consrc, '$regex', '\\1') AS dep_field
 170  	 	 	 FROM (
 171  	 	 	 	 SELECT
 172  	 	 	 	 	 pg_get_constraintdef(c.oid) AS consrc,
 173  	 	 	 	 	 t.relname AS dep_table,
 174  	 	 	 	 	 ft.relname AS lookup_table
 175  	 	 	 	 FROM pg_constraint c
 176  	 	 	 	 JOIN pg_class t ON (t.oid = c.conrelid)
 177  	 	 	 	 JOIN pg_class ft ON (ft.oid = c.confrelid)
 178  	 	 	 	 JOIN pg_namespace nft ON (nft.oid = ft.relnamespace)
 179  	 	 	 	 LEFT JOIN pg_description ds ON (ds.objoid = c.oid)
 180  	 	 	 	 JOIN pg_namespace n ON (n.oid = t.relnamespace)
 181  	 	 	 	 WHERE c.contype = 'f'::\"char\"
 182  	 	 	 	 ORDER BY t.relname, n.nspname, c.conname, c.oid
 183  	 	 	 	 ) constraints
 184  	 	 	 WHERE
 185  	 	 	 	 dep_table='".strtolower($table)."'
 186  	 	 	 ORDER BY
 187  	 	 	 	 lookup_table,
 188  	 	 	 	 dep_table,
 189  	 	 	 	 dep_field";
 190  	 	 $rs = $this->Execute($sql);
 191  
 192  	 	 if (!$rs || $rs->EOF) return false;
 193  
 194  	 	 $a = array();
 195  	 	 while (!$rs->EOF) {
 196  	 	 	 if ($upper) {
 197  	 	 	 	 $a[strtoupper($rs->Fields('lookup_table'))][] = strtoupper(str_replace('"','',$rs->Fields('dep_field').'='.$rs->Fields('lookup_field')));
 198  	 	 	 } else {
 199  	 	 	 	 $a[$rs->Fields('lookup_table')][] = str_replace('"','',$rs->Fields('dep_field').'='.$rs->Fields('lookup_field'));
 200  	 	 	 }
 201  	 	 	 $rs->MoveNext();
 202  	 	 }
 203  
 204  	 	 return $a;
 205  
 206  	 }
 207  
 208  	 // from  Edward Jaramilla, improved version - works on pg 7.4
 209  	function _old_MetaForeignKeys($table, $owner=false, $upper=false)
 210  	 {
 211  	 	 $sql = 'SELECT t.tgargs as args
 212  	 	 FROM
 213  	 	 pg_trigger t,pg_class c,pg_proc p
 214  	 	 WHERE
 215  	 	 t.tgenabled AND
 216  	 	 t.tgrelid = c.oid AND
 217  	 	 t.tgfoid = p.oid AND
 218  	 	 p.proname = \'RI_FKey_check_ins\' AND
 219  	 	 c.relname = \''.strtolower($table).'\'
 220  	 	 ORDER BY
 221  	 	 	 t.tgrelid';
 222  
 223  	 	 $rs = $this->Execute($sql);
 224  
 225  	 	 if (!$rs || $rs->EOF) return false;
 226  
 227  	 	 $arr = $rs->GetArray();
 228  	 	 $a = array();
 229  	 	 foreach($arr as $v) {
 230  	 	 	 $data = explode(chr(0), $v['args']);
 231  	 	 	 $size = count($data)-1; //-1 because the last node is empty
 232  	 	 	 for($i = 4; $i < $size; $i++) {
 233  	 	 	 	 if ($upper)
 234  	 	 	 	 	 $a[strtoupper($data[2])][] = strtoupper($data[$i].'='.$data[++$i]);
 235  	 	 	 	 else
 236  	 	 	 	 	 $a[$data[2]][] = $data[$i].'='.$data[++$i];
 237  	 	 	 }
 238  	 	 }
 239  	 	 return $a;
 240  	 }
 241  
 242  	function _query($sql,$inputarr=false)
 243  	 {
 244  	 	 if (! $this->_bindInputArray) {
 245  	 	 	 // We don't have native support for parameterized queries, so let's emulate it at the parent
 246  	 	 	 return ADODB_postgres64::_query($sql, $inputarr);
 247  	 	 }
 248  
 249  	 	 $this->_pnum = 0;
 250  	 	 $this->_errorMsg = false;
 251  	 	 // -- added Cristiano da Cunha Duarte
 252  	 	 if ($inputarr) {
 253  	 	 	 $sqlarr = explode('?',trim($sql));
 254  	 	 	 $sql = '';
 255  	 	 	 $i = 1;
 256  	 	 	 $last = sizeof($sqlarr)-1;
 257  	 	 	 foreach($sqlarr as $v) {
 258  	 	 	 	 if ($last < $i) $sql .= $v;
 259  	 	 	 	 else $sql .= $v.' $'.$i;
 260  	 	 	 	 $i++;
 261  	 	 	 }
 262  
 263  	 	 	 $rez = pg_query_params($this->_connectionID,$sql, $inputarr);
 264  	 	 } else {
 265  	 	 	 $rez = pg_query($this->_connectionID,$sql);
 266  	 	 }
 267  	 	 // check if no data returned, then no need to create real recordset
 268  	 	 if ($rez && pg_num_fields($rez) <= 0) {
 269  	 	 	 if (is_resource($this->_resultid) && get_resource_type($this->_resultid) === 'pgsql result') {
 270  	 	 	 	 pg_free_result($this->_resultid);
 271  	 	 	 }
 272  	 	 	 $this->_resultid = $rez;
 273  	 	 	 return true;
 274  	 	 }
 275  	 	 return $rez;
 276  	 }
 277  
 278  	 /**
 279  	  * Retrieve the client connection's current character set.
 280  
 281  	  * If no charsets were compiled into the server, the function will always
 282  	  * return 'SQL_ASCII'.
 283  	  * @see https://www.php.net/manual/en/function.pg-client-encoding.php
 284  	  *
 285  	  * @return string|false The character set, or false if it can't be determined.
 286  	  */
 287  	function getCharSet()
 288  	 {
 289  	 	 if (!$this->_connectionID) {
 290  	 	 	 return false;
 291  	 	 }
 292  	 	 $this->charSet = pg_client_encoding($this->_connectionID);
 293  	 	 return $this->charSet ?: false;
 294  	 }
 295  
 296  	 /**
 297  	  * Sets the client-side character set (encoding).
 298  	  *
 299  	  * Allows managing client encoding - very important if the database and
 300  	  * the output target (i.e. HTML) don't match; for instance, you may have a
 301  	  * UNICODE database and server your pages as WIN1251, etc.
 302  	  *
 303  	  * Supported on PostgreSQL 7.0 and above. Available charsets depend on
 304  	  * PostgreSQL version and the distribution's compile flags.
 305  	  *
 306  	  * @param string $charset The character set to switch to.
 307  	  *
 308  	  * @return bool True if the character set was changed successfully, false otherwise.
 309  	  */
 310  	function setCharSet($charset)
 311  	 {
 312  	 	 if ($this->charSet !== $charset) {
 313  	 	 	 if (!$this->_connectionID || pg_set_client_encoding($this->_connectionID, $charset) != 0) {
 314  	 	 	 	 return false;
 315  	 	 	 }
 316  	 	 	 $this->getCharSet();
 317  	 	 }
 318  	 	 return true;
 319  	 }
 320  
 321  }
 322  
 323  /*--------------------------------------------------------------------------------------
 324  	 Class Name: Recordset
 325  --------------------------------------------------------------------------------------*/
 326  
 327  class ADORecordSet_postgres7 extends ADORecordSet_postgres64{
 328  
 329  	 var $databaseType = "postgres7";
 330  
 331  	 // 10% speedup to move MoveNext to child class
 332  	function MoveNext()
 333  	 {
 334  	 	 if (!$this->EOF) {
 335  	 	 	 $this->_currentRow++;
 336  	 	 	 if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
 337  	 	 	 	 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
 338  
 339  	 	 	 	 if (is_array($this->fields)) {
 340  	 	 	 	 	 if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
 341  	 	 	 	 	 return true;
 342  	 	 	 	 }
 343  	 	 	 }
 344  	 	 	 $this->fields = false;
 345  	 	 	 $this->EOF = true;
 346  	 	 }
 347  	 	 return false;
 348  	 }
 349  
 350  }
 351  
 352  class ADORecordSet_assoc_postgres7 extends ADORecordSet_postgres64{
 353  
 354  	 var $databaseType = "postgres7";
 355  
 356  
 357  	function _fetch()
 358  	 {
 359  	 	 if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0) {
 360  	 	 	 return false;
 361  	 	 }
 362  
 363  	 	 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
 364  
 365  	 	 if ($this->fields) {
 366  	 	 	 if (isset($this->_blobArr)) $this->_fixblobs();
 367  	 	 	 $this->_updatefields();
 368  	 	 }
 369  
 370  	 	 return (is_array($this->fields));
 371  	 }
 372  
 373  	function MoveNext()
 374  	 {
 375  	 	 if (!$this->EOF) {
 376  	 	 	 $this->_currentRow++;
 377  	 	 	 if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
 378  	 	 	 	 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
 379  
 380  	 	 	 	 if (is_array($this->fields)) {
 381  	 	 	 	 	 if ($this->fields) {
 382  	 	 	 	 	 	 if (isset($this->_blobArr)) $this->_fixblobs();
 383  
 384  	 	 	 	 	 	 $this->_updatefields();
 385  	 	 	 	 	 }
 386  	 	 	 	 	 return true;
 387  	 	 	 	 }
 388  	 	 	 }
 389  
 390  
 391  	 	 	 $this->fields = false;
 392  	 	 	 $this->EOF = true;
 393  	 	 }
 394  	 	 return false;
 395  	 }
 396  }