[Summary view]

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

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