[Summary view]

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

   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  	public function metaForeignKeys($table, $owner = '', $upper = false, $associative = false)
 158  	 {
 159  	 	 # Regex isolates the 2 terms between parenthesis using subexpressions
 160  	 	 $regex = '^.*\((.*)\).*\((.*)\).*$';
 161  	 	 $sql="
 162  	 	 	 SELECT
 163  	 	 	 	 lookup_table,
 164  	 	 	 	 regexp_replace(consrc, '$regex', '\\2') AS lookup_field,
 165  	 	 	 	 dep_table,
 166  	 	 	 	 regexp_replace(consrc, '$regex', '\\1') AS dep_field
 167  	 	 	 FROM (
 168  	 	 	 	 SELECT
 169  	 	 	 	 	 pg_get_constraintdef(c.oid) AS consrc,
 170  	 	 	 	 	 t.relname AS dep_table,
 171  	 	 	 	 	 ft.relname AS lookup_table
 172  	 	 	 	 FROM pg_constraint c
 173  	 	 	 	 JOIN pg_class t ON (t.oid = c.conrelid)
 174  	 	 	 	 JOIN pg_class ft ON (ft.oid = c.confrelid)
 175  	 	 	 	 JOIN pg_namespace nft ON (nft.oid = ft.relnamespace)
 176  	 	 	 	 LEFT JOIN pg_description ds ON (ds.objoid = c.oid)
 177  	 	 	 	 JOIN pg_namespace n ON (n.oid = t.relnamespace)
 178  	 	 	 	 WHERE c.contype = 'f'::\"char\"
 179  	 	 	 	 ORDER BY t.relname, n.nspname, c.conname, c.oid
 180  	 	 	 	 ) constraints
 181  	 	 	 WHERE
 182  	 	 	 	 dep_table='".strtolower($table)."'
 183  	 	 	 ORDER BY
 184  	 	 	 	 lookup_table,
 185  	 	 	 	 dep_table,
 186  	 	 	 	 dep_field";
 187  	 	 $rs = $this->Execute($sql);
 188  
 189  	 	 if (!$rs || $rs->EOF) return false;
 190  
 191  	 	 $a = array();
 192  	 	 while (!$rs->EOF) {
 193  	 	 	 $lookup_table = $rs->fields('lookup_table');
 194  	 	 	 $fields = $rs->fields('dep_field') . '=' . $rs->fields('lookup_field');
 195  	 	 	 if ($upper) {
 196  	 	 	 	 $lookup_table = strtoupper($lookup_table);
 197  	 	 	 	 $fields = strtoupper($fields);
 198  	 	 	 }
 199  	 	 	 $a[$lookup_table][] = str_replace('"','', $fields);
 200  
 201  	 	 	 $rs->MoveNext();
 202  	 	 }
 203  
 204  	 	 return $a;
 205  	 }
 206  
 207  	function _query($sql,$inputarr=false)
 208  	 {
 209  	 	 if (! $this->_bindInputArray) {
 210  	 	 	 // We don't have native support for parameterized queries, so let's emulate it at the parent
 211  	 	 	 return ADODB_postgres64::_query($sql, $inputarr);
 212  	 	 }
 213  
 214  	 	 $this->_pnum = 0;
 215  	 	 $this->_errorMsg = false;
 216  	 	 // -- added Cristiano da Cunha Duarte
 217  	 	 if ($inputarr) {
 218  	 	 	 $sqlarr = explode('?',trim($sql));
 219  	 	 	 $sql = '';
 220  	 	 	 $i = 1;
 221  	 	 	 $last = sizeof($sqlarr)-1;
 222  	 	 	 foreach($sqlarr as $v) {
 223  	 	 	 	 if ($last < $i) $sql .= $v;
 224  	 	 	 	 else $sql .= $v.' $'.$i;
 225  	 	 	 	 $i++;
 226  	 	 	 }
 227  
 228  	 	 	 $rez = pg_query_params($this->_connectionID,$sql, $inputarr);
 229  	 	 } else {
 230  	 	 	 $rez = pg_query($this->_connectionID,$sql);
 231  	 	 }
 232  	 	 // check if no data returned, then no need to create real recordset
 233  	 	 if ($rez && pg_num_fields($rez) <= 0) {
 234  	 	 	 if ($this->_resultid !== false) {
 235  	 	 	 	 pg_free_result($this->_resultid);
 236  	 	 	 }
 237  	 	 	 $this->_resultid = $rez;
 238  	 	 	 return true;
 239  	 	 }
 240  	 	 return $rez;
 241  	 }
 242  
 243  	 /**
 244  	  * Retrieve the client connection's current character set.
 245  
 246  	  * If no charsets were compiled into the server, the function will always
 247  	  * return 'SQL_ASCII'.
 248  	  * @see https://www.php.net/manual/en/function.pg-client-encoding.php
 249  	  *
 250  	  * @return string|false The character set, or false if it can't be determined.
 251  	  */
 252  	function getCharSet()
 253  	 {
 254  	 	 if (!$this->_connectionID) {
 255  	 	 	 return false;
 256  	 	 }
 257  	 	 $this->charSet = pg_client_encoding($this->_connectionID);
 258  	 	 return $this->charSet ?: false;
 259  	 }
 260  
 261  	 /**
 262  	  * Sets the client-side character set (encoding).
 263  	  *
 264  	  * Allows managing client encoding - very important if the database and
 265  	  * the output target (i.e. HTML) don't match; for instance, you may have a
 266  	  * UNICODE database and server your pages as WIN1251, etc.
 267  	  *
 268  	  * Supported on PostgreSQL 7.0 and above. Available charsets depend on
 269  	  * PostgreSQL version and the distribution's compile flags.
 270  	  *
 271  	  * @param string $charset The character set to switch to.
 272  	  *
 273  	  * @return bool True if the character set was changed successfully, false otherwise.
 274  	  */
 275  	function setCharSet($charset)
 276  	 {
 277  	 	 if ($this->charSet !== $charset) {
 278  	 	 	 if (!$this->_connectionID || pg_set_client_encoding($this->_connectionID, $charset) != 0) {
 279  	 	 	 	 return false;
 280  	 	 	 }
 281  	 	 	 $this->getCharSet();
 282  	 	 }
 283  	 	 return true;
 284  	 }
 285  
 286  }
 287  
 288  /*--------------------------------------------------------------------------------------
 289  	 Class Name: Recordset
 290  --------------------------------------------------------------------------------------*/
 291  
 292  class ADORecordSet_postgres7 extends ADORecordSet_postgres64{
 293  
 294  	 var $databaseType = "postgres7";
 295  
 296  	 // 10% speedup to move MoveNext to child class
 297  	function MoveNext()
 298  	 {
 299  	 	 if (!$this->EOF) {
 300  	 	 	 $this->_currentRow++;
 301  	 	 	 if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
 302  	 	 	 	 $this->_prepfields();
 303  	 	 	 	 if ($this->fields !== false) {
 304  	 	 	 	 	 return true;
 305  	 	 	 	 }
 306  	 	 	 }
 307  	 	 	 $this->fields = false;
 308  	 	 	 $this->EOF = true;
 309  	 	 }
 310  	 	 return false;
 311  	 }
 312  
 313  }
 314  
 315  class ADORecordSet_assoc_postgres7 extends ADORecordSet_postgres64{
 316  
 317  	 var $databaseType = "postgres7";
 318  
 319  
 320  	function _fetch()
 321  	 {
 322  	 	 if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0) {
 323  	 	 	 return false;
 324  	 	 }
 325  
 326  	 	 $this->_prepfields();
 327  	 	 if ($this->fields !== false) {
 328  	 	 	 $this->_updatefields();
 329  	 	 	 return true;
 330  	 	 }
 331  
 332  	 	 return false;
 333  	 }
 334  
 335  	function MoveNext()
 336  	 {
 337  	 	 if (!$this->EOF) {
 338  	 	 	 $this->_currentRow++;
 339  	 	 	 if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
 340  	 	 	 	 $this->_prepfields();
 341  	 	 	 	 if ($this->fields !== false) {
 342  	 	 	 	 	 $this->_updatefields();
 343  	 	 	 	 	 return true;
 344  	 	 	 	 }
 345  	 	 	 }
 346  
 347  	 	 	 $this->fields = false;
 348  	 	 	 $this->EOF = true;
 349  	 	 }
 350  	 	 return false;
 351  	 }
 352  }