[Summary view] [Print] [Text view]

   1  <?php
   2  /*
   3   V5.19  23-Apr-2014  (c) 2000-2014 John Lim (jlim#natsoft.com). All rights reserved.
   4    Released under both BSD license and Lesser GPL library license.
   5    Whenever there is any discrepancy between the two licenses,
   6    the BSD license will take precedence.
   7    Set tabs to 8.
   8  
   9    Original version derived from Alberto Cerezal ([email protected]) - DBNet Informatica & Comunicaciones.
  10    08 Nov 2000 jlim - Minor corrections, removing mysql stuff
  11    09 Nov 2000 jlim - added insertid support suggested by "Christopher Kings-Lynne" <[email protected]>
  12                jlim - changed concat operator to || and data types to MetaType to match documented pgsql types
  13                       see http://www.postgresql.org/devel-corner/docs/postgres/datatype.htm
  14    22 Nov 2000 jlim - added changes to FetchField() and MetaTables() contributed by "raser" <[email protected]>
  15    27 Nov 2000 jlim - added changes to _connect/_pconnect from ideas by "Lennie" <[email protected]>
  16    15 Dec 2000 jlim - added changes suggested by Additional code changes by "Eric G. Werk" [email protected].
  17    31 Jan 2002 jlim - finally installed postgresql. testing
  18    01 Mar 2001 jlim - Freek Dijkstra changes, also support for text type
  19  
  20    See http://www.varlena.com/varlena/GeneralBits/47.php
  21  
  22      -- What indexes are on my table?
  23      select * from pg_indexes where tablename = 'tablename';
  24  
  25      -- What triggers are on my table?
  26      select c.relname as "Table", t.tgname as "Trigger Name",
  27         t.tgconstrname as "Constraint Name", t.tgenabled as "Enabled",
  28         t.tgisconstraint as "Is Constraint", cc.relname as "Referenced Table",
  29         p.proname as "Function Name"
  30      from pg_trigger t, pg_class c, pg_class cc, pg_proc p
  31      where t.tgfoid = p.oid and t.tgrelid = c.oid
  32         and t.tgconstrrelid = cc.oid
  33         and c.relname = 'tablename';
  34  
  35      -- What constraints are on my table?
  36      select r.relname as "Table", c.conname as "Constraint Name",
  37         contype as "Constraint Type", conkey as "Key Columns",
  38         confkey as "Foreign Columns", consrc as "Source"
  39      from pg_class r, pg_constraint c
  40      where r.oid = c.conrelid
  41         and relname = 'tablename';
  42  
  43  */
  44  
  45  // security - hide paths
  46  if (!defined('ADODB_DIR')) die();
  47  
  48  function adodb_addslashes($s)
  49  {
  50      $len = strlen($s);
  51      if ($len == 0) return "''";
  52      if (strncmp($s,"'",1) === 0 && substr($s,$len-1) == "'") return $s; // already quoted
  53  
  54      return "'".addslashes($s)."'";
  55  }
  56  
  57  class ADODB_postgres64 extends ADOConnection{
  58      var $databaseType = 'postgres64';
  59      var $dataProvider = 'postgres';
  60      var $hasInsertID = true;
  61      var $_resultid = false;
  62      var $concat_operator='||';
  63      var $metaDatabasesSQL = "select datname from pg_database where datname not in ('template0','template1') order by 1";
  64      var $metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
  65          and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages',
  66              'sql_packages', 'sql_sizing', 'sql_sizing_profiles')
  67      union
  68          select viewname,'V' from pg_views where viewname not like 'pg\_%'";
  69      //"select tablename from pg_tables where tablename not like 'pg_%' order by 1";
  70      var $isoDates = true; // accepts dates in ISO format
  71      var $sysDate = "CURRENT_DATE";
  72      var $sysTimeStamp = "CURRENT_TIMESTAMP";
  73      var $blobEncodeType = 'C';
  74      var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum
  75          FROM pg_class c, pg_attribute a,pg_type t
  76          WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) and a.attname not like '....%%'
  77          AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
  78  
  79      // used when schema defined
  80      var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
  81          FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n
  82          WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s'))
  83          and c.relnamespace=n.oid and n.nspname='%s'
  84          and a.attname not like '....%%' AND a.attnum > 0
  85          AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
  86  
  87      // get primary key etc -- from Freek Dijkstra
  88      var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key
  89          FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a
  90          WHERE bc.oid = i.indrelid AND ic.oid = i.indexrelid
  91          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)
  92          AND a.attrelid = bc.oid AND bc.relname = '%s'";
  93  
  94      var $hasAffectedRows = true;
  95      var $hasLimit = false;    // set to true for pgsql 7 only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
  96      // below suggested by Freek Dijkstra
  97      var $true = 'TRUE';        // string that represents TRUE for a database
  98      var $false = 'FALSE';        // string that represents FALSE for a database
  99      var $fmtDate = "'Y-m-d'";    // used by DBDate() as the default date format used by the database
 100      var $fmtTimeStamp = "'Y-m-d H:i:s'"; // used by DBTimeStamp as the default timestamp fmt.
 101      var $hasMoveFirst = true;
 102      var $hasGenID = true;
 103      var $_genIDSQL = "SELECT NEXTVAL('%s')";
 104      var $_genSeqSQL = "CREATE SEQUENCE %s START %s";
 105      var $_dropSeqSQL = "DROP SEQUENCE %s";
 106      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";
 107      var $random = 'random()';        /// random function
 108      var $autoRollback = true; // apparently pgsql does not autorollback properly before php 4.3.4
 109                              // http://bugs.php.net/bug.php?id=25404
 110  
 111      var $uniqueIisR = true;
 112      var $_bindInputArray = false; // requires postgresql 7.3+ and ability to modify database
 113      var $disableBlobs = false; // set to true to disable blob checking, resulting in 2-5% improvement in performance.
 114  
 115      var $_pnum = 0;
 116  
 117      // The last (fmtTimeStamp is not entirely correct:
 118      // PostgreSQL also has support for time zones,
 119      // and writes these time in this format: "2001-03-01 18:59:26+02".
 120      // There is no code for the "+02" time zone information, so I just left that out.
 121      // I'm not familiar enough with both ADODB as well as Postgres
 122      // to know what the concequences are. The other values are correct (wheren't in 0.94)
 123      // -- Freek Dijkstra
 124  
 125  	function __construct()
 126      {
 127          // changes the metaColumnsSQL, adds columns: attnum[6]
 128      }
 129  
 130  	function ServerInfo()
 131      {
 132          if (isset($this->version)) return $this->version;
 133  
 134          $arr['description'] = $this->GetOne("select version()");
 135          $arr['version'] = ADOConnection::_findvers($arr['description']);
 136          $this->version = $arr;
 137          return $arr;
 138      }
 139  
 140  	function IfNull( $field, $ifNull )
 141      {
 142          return " coalesce($field, $ifNull) ";
 143      }
 144  
 145      // get the last id - never tested
 146  	function pg_insert_id($tablename,$fieldname)
 147      {
 148          $result=pg_exec($this->_connectionID, "SELECT last_value FROM $tablename}_$fieldname}_seq");
 149          if ($result) {
 150              $arr = @pg_fetch_row($result,0);
 151              pg_freeresult($result);
 152              if (isset($arr[0])) return $arr[0];
 153          }
 154          return false;
 155      }
 156  
 157  /* Warning from http://www.php.net/manual/function.pg-getlastoid.php:
 158  Using a OID as a unique identifier is not generally wise.
 159  Unless you are very careful, you might end up with a tuple having
 160  a different OID if a database must be reloaded. */
 161  	function _insertid($table,$column)
 162      {
 163          if (!is_resource($this->_resultid) || get_resource_type($this->_resultid) !== 'pgsql result') return false;
 164          $oid = pg_getlastoid($this->_resultid);
 165          // to really return the id, we need the table and column-name, else we can only return the oid != id
 166          return empty($table) || empty($column) ? $oid : $this->GetOne("SELECT $column FROM $table WHERE oid=".(int)$oid);
 167      }
 168  
 169  // I get this error with PHP before 4.0.6 - jlim
 170  // Warning: This compilation does not support pg_cmdtuples() in adodb-postgres.inc.php on line 44
 171  	function _affectedrows()
 172      {
 173          if (!is_resource($this->_resultid) || get_resource_type($this->_resultid) !== 'pgsql result') return false;
 174          return pg_cmdtuples($this->_resultid);
 175      }
 176  
 177  
 178          // returns true/false
 179  	function BeginTrans()
 180      {
 181          if ($this->transOff) return true;
 182          $this->transCnt += 1;
 183          return @pg_Exec($this->_connectionID, "begin ".$this->_transmode);
 184      }
 185  
 186  	function RowLock($tables,$where,$col='1 as adodbignore')
 187      {
 188          if (!$this->transCnt) $this->BeginTrans();
 189          return $this->GetOne("select $col from $tables where $where for update");
 190      }
 191  
 192      // returns true/false.
 193  	function CommitTrans($ok=true)
 194      {
 195          if ($this->transOff) return true;
 196          if (!$ok) return $this->RollbackTrans();
 197  
 198          $this->transCnt -= 1;
 199          return @pg_Exec($this->_connectionID, "commit");
 200      }
 201  
 202      // returns true/false
 203  	function RollbackTrans()
 204      {
 205          if ($this->transOff) return true;
 206          $this->transCnt -= 1;
 207          return @pg_Exec($this->_connectionID, "rollback");
 208      }
 209  
 210  	function MetaTables($ttype=false,$showSchema=false,$mask=false)
 211      {
 212          $info = $this->ServerInfo();
 213          if ($info['version'] >= 7.3) {
 214          $this->metaTablesSQL = "
 215              select table_name,'T' from information_schema.tables where table_schema not in ( 'pg_catalog','information_schema')
 216              union
 217              select table_name,'V' from information_schema.views where table_schema not in ( 'pg_catalog','information_schema') ";
 218          }
 219          if ($mask) {
 220              $save = $this->metaTablesSQL;
 221              $mask = $this->qstr(strtolower($mask));
 222              if ($info['version']>=7.3)
 223                  $this->metaTablesSQL = "
 224                      select table_name,'T' from information_schema.tables where table_name like $mask and table_schema not in ( 'pg_catalog','information_schema')
 225                      union
 226                      select table_name,'V' from information_schema.views where table_name like $mask and table_schema not in ( 'pg_catalog','information_schema') ";
 227              else
 228                  $this->metaTablesSQL = "
 229                      select tablename,'T' from pg_tables where tablename like $mask
 230                      union
 231                      select viewname,'V' from pg_views where viewname like $mask";
 232          }
 233          $ret = ADOConnection::MetaTables($ttype,$showSchema);
 234  
 235          if ($mask) {
 236              $this->metaTablesSQL = $save;
 237          }
 238          return $ret;
 239      }
 240  
 241  
 242      // if magic quotes disabled, use pg_escape_string()
 243  	function qstr($s,$magic_quotes=false)
 244      {
 245          if (is_bool($s)) return $s ? 'true' : 'false';
 246  
 247          if (!$magic_quotes) {
 248              if (ADODB_PHPVER >= 0x5200 && $this->_connectionID) {
 249                  return  "'".pg_escape_string($this->_connectionID,$s)."'";
 250              }
 251              if (ADODB_PHPVER >= 0x4200) {
 252                  return  "'".pg_escape_string($s)."'";
 253              }
 254              if ($this->replaceQuote[0] == '\\'){
 255                  $s = adodb_str_replace(array('\\',"\0"),array('\\\\',"\\\\000"),$s);
 256              }
 257              return  "'".str_replace("'",$this->replaceQuote,$s)."'";
 258          }
 259  
 260          // undo magic quotes for "
 261          $s = str_replace('\\"','"',$s);
 262          return "'$s'";
 263      }
 264  
 265  
 266  
 267      // Format date column in sql string given an input format that understands Y M D
 268  	function SQLDate($fmt, $col=false)
 269      {
 270          if (!$col) $col = $this->sysTimeStamp;
 271          $s = 'TO_CHAR('.$col.",'";
 272  
 273          $len = strlen($fmt);
 274          for ($i=0; $i < $len; $i++) {
 275              $ch = $fmt[$i];
 276              switch($ch) {
 277              case 'Y':
 278              case 'y':
 279                  $s .= 'YYYY';
 280                  break;
 281              case 'Q':
 282              case 'q':
 283                  $s .= 'Q';
 284                  break;
 285  
 286              case 'M':
 287                  $s .= 'Mon';
 288                  break;
 289  
 290              case 'm':
 291                  $s .= 'MM';
 292                  break;
 293              case 'D':
 294              case 'd':
 295                  $s .= 'DD';
 296                  break;
 297  
 298              case 'H':
 299                  $s.= 'HH24';
 300                  break;
 301  
 302              case 'h':
 303                  $s .= 'HH';
 304                  break;
 305  
 306              case 'i':
 307                  $s .= 'MI';
 308                  break;
 309  
 310              case 's':
 311                  $s .= 'SS';
 312                  break;
 313  
 314              case 'a':
 315              case 'A':
 316                  $s .= 'AM';
 317                  break;
 318  
 319              case 'w':
 320                  $s .= 'D';
 321                  break;
 322  
 323              case 'l':
 324                  $s .= 'DAY';
 325                  break;
 326  
 327              case 'W':
 328                  $s .= 'WW';
 329                  break;
 330  
 331              default:
 332              // handle escape characters...
 333                  if ($ch == '\\') {
 334                      $i++;
 335                      $ch = substr($fmt,$i,1);
 336                  }
 337                  if (strpos('-/.:;, ',$ch) !== false) $s .= $ch;
 338                  else $s .= '"'.$ch.'"';
 339  
 340              }
 341          }
 342          return $s. "')";
 343      }
 344  
 345  
 346  
 347      /*
 348      * Load a Large Object from a file
 349      * - the procedure stores the object id in the table and imports the object using
 350      * postgres proprietary blob handling routines
 351      *
 352      * contributed by Mattia Rossi [email protected]
 353      * modified for safe mode by juraj chlebec
 354      */
 355  	function UpdateBlobFile($table,$column,$path,$where,$blobtype='BLOB')
 356      {
 357          pg_exec ($this->_connectionID, "begin");
 358  
 359          $fd = fopen($path,'r');
 360          $contents = fread($fd,filesize($path));
 361          fclose($fd);
 362  
 363          $oid = pg_lo_create($this->_connectionID);
 364          $handle = pg_lo_open($this->_connectionID, $oid, 'w');
 365          pg_lo_write($handle, $contents);
 366          pg_lo_close($handle);
 367  
 368          // $oid = pg_lo_import ($path);
 369          pg_exec($this->_connectionID, "commit");
 370          $rs = ADOConnection::UpdateBlob($table,$column,$oid,$where,$blobtype);
 371          $rez = !empty($rs);
 372          return $rez;
 373      }
 374  
 375      /*
 376      * Deletes/Unlinks a Blob from the database, otherwise it
 377      * will be left behind
 378      *
 379      * Returns TRUE on success or FALSE on failure.
 380      *
 381      * contributed by Todd Rogers todd#windfox.net
 382      */
 383  	function BlobDelete( $blob )
 384      {
 385          pg_exec ($this->_connectionID, "begin");
 386          $result = @pg_lo_unlink($blob);
 387          pg_exec ($this->_connectionID, "commit");
 388          return( $result );
 389      }
 390  
 391      /*
 392          Hueristic - not guaranteed to work.
 393      */
 394  	function GuessOID($oid)
 395      {
 396          if (strlen($oid)>16) return false;
 397          return is_numeric($oid);
 398      }
 399  
 400      /*
 401      * If an OID is detected, then we use pg_lo_* to open the oid file and read the
 402      * real blob from the db using the oid supplied as a parameter. If you are storing
 403      * blobs using bytea, we autodetect and process it so this function is not needed.
 404      *
 405      * contributed by Mattia Rossi [email protected]
 406      *
 407      * see http://www.postgresql.org/idocs/index.php?largeobjects.html
 408      *
 409      * Since adodb 4.54, this returns the blob, instead of sending it to stdout. Also
 410      * added maxsize parameter, which defaults to $db->maxblobsize if not defined.
 411      */
 412  	function BlobDecode($blob,$maxsize=false,$hastrans=true)
 413      {
 414          if (!$this->GuessOID($blob)) return $blob;
 415  
 416          if ($hastrans) @pg_exec($this->_connectionID,"begin");
 417          $fd = @pg_lo_open($this->_connectionID,$blob,"r");
 418          if ($fd === false) {
 419              if ($hastrans) @pg_exec($this->_connectionID,"commit");
 420              return $blob;
 421          }
 422          if (!$maxsize) $maxsize = $this->maxblobsize;
 423          $realblob = @pg_loread($fd,$maxsize);
 424          @pg_loclose($fd);
 425          if ($hastrans) @pg_exec($this->_connectionID,"commit");
 426          return $realblob;
 427      }
 428  
 429      /*
 430          See http://www.postgresql.org/idocs/index.php?datatype-binary.html
 431  
 432          NOTE: SQL string literals (input strings) must be preceded with two backslashes
 433          due to the fact that they must pass through two parsers in the PostgreSQL
 434          backend.
 435      */
 436  	function BlobEncode($blob)
 437      {
 438          if (ADODB_PHPVER >= 0x5200) return pg_escape_bytea($this->_connectionID, $blob);
 439          if (ADODB_PHPVER >= 0x4200) return pg_escape_bytea($blob);
 440  
 441          /*92=backslash, 0=null, 39=single-quote*/
 442          $badch = array(chr(92),chr(0),chr(39)); # \  null  '
 443          $fixch = array('\\\\134','\\\\000','\\\\047');
 444          return adodb_str_replace($badch,$fixch,$blob);
 445  
 446          // note that there is a pg_escape_bytea function only for php 4.2.0 or later
 447      }
 448  
 449      // assumes bytea for blob, and varchar for clob
 450  	function UpdateBlob($table,$column,$val,$where,$blobtype='BLOB')
 451      {
 452          if ($blobtype == 'CLOB') {
 453              return $this->Execute("UPDATE $table SET $column=" . $this->qstr($val) . " WHERE $where");
 454          }
 455          // do not use bind params which uses qstr(), as blobencode() already quotes data
 456          return $this->Execute("UPDATE $table SET $column='".$this->BlobEncode($val)."'::bytea WHERE $where");
 457      }
 458  
 459  	function OffsetDate($dayFraction,$date=false)
 460      {
 461          if (!$date) $date = $this->sysDate;
 462          else if (strncmp($date,"'",1) == 0) {
 463              $len = strlen($date);
 464              if (10 <= $len && $len <= 12) $date = 'date '.$date;
 465              else $date = 'timestamp '.$date;
 466          }
 467  
 468  
 469          return "($date+interval'".($dayFraction * 1440)." minutes')";
 470          #return "($date+interval'$dayFraction days')";
 471      }
 472  
 473  
 474      // for schema support, pass in the $table param "$schema.$tabname".
 475      // converts field names to lowercase, $upper is ignored
 476      // see http://phplens.com/lens/lensforum/msgs.php?id=14018 for more info
 477  	function MetaColumns($table,$normalize=true)
 478      {
 479      global $ADODB_FETCH_MODE;
 480  
 481          $schema = false;
 482          $false = false;
 483          $this->_findschema($table,$schema);
 484  
 485          if ($normalize) $table = strtolower($table);
 486  
 487          $save = $ADODB_FETCH_MODE;
 488          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 489          if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
 490  
 491          if ($schema) $rs = $this->Execute(sprintf($this->metaColumnsSQL1,$table,$table,$schema));
 492          else $rs = $this->Execute(sprintf($this->metaColumnsSQL,$table,$table,$table));
 493          if (isset($savem)) $this->SetFetchMode($savem);
 494          $ADODB_FETCH_MODE = $save;
 495  
 496          if ($rs === false) {
 497              return $false;
 498          }
 499          if (!empty($this->metaKeySQL)) {
 500              // If we want the primary keys, we have to issue a separate query
 501              // Of course, a modified version of the metaColumnsSQL query using a
 502              // LEFT JOIN would have been much more elegant, but postgres does
 503              // not support OUTER JOINS. So here is the clumsy way.
 504  
 505              $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
 506  
 507              $rskey = $this->Execute(sprintf($this->metaKeySQL,($table)));
 508              // fetch all result in once for performance.
 509              $keys = $rskey->GetArray();
 510              if (isset($savem)) $this->SetFetchMode($savem);
 511              $ADODB_FETCH_MODE = $save;
 512  
 513              $rskey->Close();
 514              unset($rskey);
 515          }
 516  
 517          $rsdefa = array();
 518          if (!empty($this->metaDefaultsSQL)) {
 519              $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
 520              $sql = sprintf($this->metaDefaultsSQL, ($table));
 521              $rsdef = $this->Execute($sql);
 522              if (isset($savem)) $this->SetFetchMode($savem);
 523              $ADODB_FETCH_MODE = $save;
 524  
 525              if ($rsdef) {
 526                  while (!$rsdef->EOF) {
 527                      $num = $rsdef->fields['num'];
 528                      $s = $rsdef->fields['def'];
 529                      if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
 530                          $s = substr($s, 1);
 531                          $s = substr($s, 0, strlen($s) - 1);
 532                      }
 533  
 534                      $rsdefa[$num] = $s;
 535                      $rsdef->MoveNext();
 536                  }
 537              } else {
 538                  ADOConnection::outp( "==> SQL => " . $sql);
 539              }
 540              unset($rsdef);
 541          }
 542  
 543          $retarr = array();
 544          while (!$rs->EOF) {
 545              $fld = new ADOFieldObject();
 546              $fld->name = $rs->fields[0];
 547              $fld->type = $rs->fields[1];
 548              $fld->max_length = $rs->fields[2];
 549              $fld->attnum = $rs->fields[6];
 550  
 551              if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
 552              if ($fld->max_length <= 0) $fld->max_length = -1;
 553              if ($fld->type == 'numeric') {
 554                  $fld->scale = $fld->max_length & 0xFFFF;
 555                  $fld->max_length >>= 16;
 556              }
 557              // dannym
 558              // 5 hasdefault; 6 num-of-column
 559              $fld->has_default = ($rs->fields[5] == 't');
 560              if ($fld->has_default) {
 561                  $fld->default_value = $rsdefa[$rs->fields[6]];
 562              }
 563  
 564              //Freek
 565              $fld->not_null = $rs->fields[4] == 't';
 566  
 567  
 568              // Freek
 569              if (is_array($keys)) {
 570                  foreach($keys as $key) {
 571                      if ($fld->name == $key['column_name'] AND $key['primary_key'] == 't')
 572                          $fld->primary_key = true;
 573                      if ($fld->name == $key['column_name'] AND $key['unique_key'] == 't')
 574                          $fld->unique = true; // What name is more compatible?
 575                  }
 576              }
 577  
 578              if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;
 579              else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
 580  
 581              $rs->MoveNext();
 582          }
 583          $rs->Close();
 584          if (empty($retarr))
 585              return  $false;
 586          else
 587              return $retarr;
 588  
 589      }
 590  
 591  	function Param($name,$type='C')
 592      {
 593          if ($name) {
 594              $this->_pnum += 1;
 595          } else {
 596              // Reset param num if $name is false
 597              $this->_pnum = 1;
 598          }
 599          return '$'.$this->_pnum;
 600      }
 601  
 602  	function MetaIndexes ($table, $primary = FALSE, $owner = false)
 603      {
 604          global $ADODB_FETCH_MODE;
 605  
 606          $schema = false;
 607          $this->_findschema($table,$schema);
 608  
 609          if ($schema) { // requires pgsql 7.3+ - pg_namespace used.
 610              $sql = '
 611                  SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
 612                  FROM pg_catalog.pg_class c
 613                  JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
 614                  JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
 615                      ,pg_namespace n
 616                  WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\'))
 617                  and c.relnamespace=c2.relnamespace
 618                  and c.relnamespace=n.oid
 619                  and n.nspname=\'%s\'';
 620          } else {
 621              $sql = '
 622                  SELECT c.relname as "Name", i.indisunique as "Unique", i.indkey as "Columns"
 623                  FROM pg_catalog.pg_class c
 624                  JOIN pg_catalog.pg_index i ON i.indexrelid=c.oid
 625                  JOIN pg_catalog.pg_class c2 ON c2.oid=i.indrelid
 626                  WHERE (c2.relname=\'%s\' or c2.relname=lower(\'%s\'))';
 627          }
 628  
 629          if ($primary == FALSE) {
 630              $sql .= ' AND i.indisprimary=false;';
 631          }
 632  
 633          $save = $ADODB_FETCH_MODE;
 634          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 635          if ($this->fetchMode !== FALSE) {
 636              $savem = $this->SetFetchMode(FALSE);
 637          }
 638  
 639          $rs = $this->Execute(sprintf($sql,$table,$table,$schema));
 640          if (isset($savem)) {
 641              $this->SetFetchMode($savem);
 642          }
 643          $ADODB_FETCH_MODE = $save;
 644  
 645          if (!is_object($rs)) {
 646              $false = false;
 647              return $false;
 648          }
 649  
 650          $col_names = $this->MetaColumnNames($table,true,true);
 651          //3rd param is use attnum,
 652          // see http://sourceforge.net/tracker/index.php?func=detail&aid=1451245&group_id=42718&atid=433976
 653          $indexes = array();
 654          while ($row = $rs->FetchRow()) {
 655              $columns = array();
 656              foreach (explode(' ', $row[2]) as $col) {
 657                  $columns[] = $col_names[$col];
 658              }
 659  
 660              $indexes[$row[0]] = array(
 661                  'unique' => ($row[1] == 't'),
 662                  'columns' => $columns
 663              );
 664          }
 665          return $indexes;
 666      }
 667  
 668      // returns true or false
 669      //
 670      // examples:
 671      //     $db->Connect("host=host1 user=user1 password=secret port=4341");
 672      //     $db->Connect('host1','user1','secret');
 673  	function _connect($str,$user='',$pwd='',$db='',$ctype=0)
 674      {
 675          if (!function_exists('pg_connect')) return null;
 676  
 677          $this->_errorMsg = false;
 678  
 679          if ($user || $pwd || $db) {
 680              $user = adodb_addslashes($user);
 681              $pwd = adodb_addslashes($pwd);
 682              if (strlen($db) == 0) $db = 'template1';
 683              $db = adodb_addslashes($db);
 684              if ($str)  {
 685                  $host = explode(":", $str);
 686                  if ($host[0]) $str = "host=".adodb_addslashes($host[0]);
 687                  else $str = '';
 688                  if (isset($host[1])) $str .= " port=$host[1]";
 689                  else if (!empty($this->port)) $str .= " port=".$this->port;
 690              }
 691              if ($user) $str .= " user=".$user;
 692              if ($pwd)  $str .= " password=".$pwd;
 693              if ($db)   $str .= " dbname=".$db;
 694          }
 695  
 696          //if ($user) $linea = "user=$user host=$linea password=$pwd dbname=$db port=5432";
 697  
 698          if ($ctype === 1) { // persistent
 699              $this->_connectionID = pg_pconnect($str);
 700          } else {
 701              if ($ctype === -1) { // nconnect, we trick pgsql ext by changing the connection str
 702                  static $ncnt;
 703  
 704                  if (empty($ncnt)) $ncnt = 1;
 705                  else $ncnt += 1;
 706  
 707                  $str .= str_repeat(' ',$ncnt);
 708              }
 709              $this->_connectionID = pg_connect($str);
 710          }
 711          if ($this->_connectionID === false) return false;
 712          $this->Execute("set datestyle='ISO'");
 713  
 714          $info = $this->ServerInfo();
 715          $this->pgVersion = (float) substr($info['version'],0,3);
 716          if ($this->pgVersion >= 7.1) { // good till version 999
 717              $this->_nestedSQL = true;
 718          }
 719  
 720          # PostgreSQL 9.0 changed the default output for bytea from 'escape' to 'hex'
 721          # PHP does not handle 'hex' properly ('x74657374' is returned as 't657374')
 722          # https://bugs.php.net/bug.php?id=59831 states this is in fact not a bug,
 723          # so we manually set bytea_output
 724          if (version_compare($info['version'], '9.0', '>=')) {
 725              $this->Execute('set bytea_output=escape');
 726          }
 727  
 728          return true;
 729      }
 730  
 731  	function _nconnect($argHostname, $argUsername, $argPassword, $argDatabaseName)
 732      {
 733          return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabaseName,-1);
 734      }
 735  
 736      // returns true or false
 737      //
 738      // examples:
 739      //     $db->PConnect("host=host1 user=user1 password=secret port=4341");
 740      //     $db->PConnect('host1','user1','secret');
 741  	function _pconnect($str,$user='',$pwd='',$db='')
 742      {
 743          return $this->_connect($str,$user,$pwd,$db,1);
 744      }
 745  
 746  
 747      // returns queryID or false
 748  	function _query($sql,$inputarr=false)
 749      {
 750          $this->_pnum = 0;
 751          $this->_errorMsg = false;
 752          if ($inputarr) {
 753          /*
 754              It appears that PREPARE/EXECUTE is slower for many queries.
 755  
 756              For query executed 1000 times:
 757              "select id,firstname,lastname from adoxyz
 758                  where firstname not like ? and lastname not like ? and id = ?"
 759  
 760              with plan = 1.51861286163 secs
 761              no plan =   1.26903700829 secs
 762  
 763  
 764  
 765          */
 766              $plan = 'P'.md5($sql);
 767  
 768              $execp = '';
 769              foreach($inputarr as $v) {
 770                  if ($execp) $execp .= ',';
 771                  if (is_string($v)) {
 772                      if (strncmp($v,"'",1) !== 0) $execp .= $this->qstr($v);
 773                  } else {
 774                      $execp .= $v;
 775                  }
 776              }
 777  
 778              if ($execp) $exsql = "EXECUTE $plan ($execp)";
 779              else $exsql = "EXECUTE $plan";
 780  
 781  
 782              $rez = @pg_exec($this->_connectionID,$exsql);
 783              if (!$rez) {
 784              # Perhaps plan does not exist? Prepare/compile plan.
 785                  $params = '';
 786                  foreach($inputarr as $v) {
 787                      if ($params) $params .= ',';
 788                      if (is_string($v)) {
 789                          $params .= 'VARCHAR';
 790                      } else if (is_integer($v)) {
 791                          $params .= 'INTEGER';
 792                      } else {
 793                          $params .= "REAL";
 794                      }
 795                  }
 796                  $sqlarr = explode('?',$sql);
 797                  //print_r($sqlarr);
 798                  $sql = '';
 799                  $i = 1;
 800                  foreach($sqlarr as $v) {
 801                      $sql .= $v.' $'.$i;
 802                      $i++;
 803                  }
 804                  $s = "PREPARE $plan ($params) AS ".substr($sql,0,strlen($sql)-2);
 805                  //adodb_pr($s);
 806                  $rez = pg_exec($this->_connectionID,$s);
 807                  //echo $this->ErrorMsg();
 808              }
 809              if ($rez)
 810                  $rez = pg_exec($this->_connectionID,$exsql);
 811          } else {
 812              //adodb_backtrace();
 813              $rez = pg_exec($this->_connectionID,$sql);
 814          }
 815          // check if no data returned, then no need to create real recordset
 816          if ($rez && pg_numfields($rez) <= 0) {
 817              if (is_resource($this->_resultid) && get_resource_type($this->_resultid) === 'pgsql result') {
 818                  pg_freeresult($this->_resultid);
 819              }
 820              $this->_resultid = $rez;
 821              return true;
 822          }
 823  
 824          return $rez;
 825      }
 826  
 827  	function _errconnect()
 828      {
 829          if (defined('DB_ERROR_CONNECT_FAILED')) return DB_ERROR_CONNECT_FAILED;
 830          else return 'Database connection failed';
 831      }
 832  
 833      /*    Returns: the last error message from previous database operation    */
 834  	function ErrorMsg()
 835      {
 836          if ($this->_errorMsg !== false) return $this->_errorMsg;
 837          if (ADODB_PHPVER >= 0x4300) {
 838              if (!empty($this->_resultid)) {
 839                  $this->_errorMsg = @pg_result_error($this->_resultid);
 840                  if ($this->_errorMsg) return $this->_errorMsg;
 841              }
 842  
 843              if (!empty($this->_connectionID)) {
 844                  $this->_errorMsg = @pg_last_error($this->_connectionID);
 845              } else $this->_errorMsg = $this->_errconnect();
 846          } else {
 847              if (empty($this->_connectionID)) $this->_errconnect();
 848              else $this->_errorMsg = @pg_errormessage($this->_connectionID);
 849          }
 850          return $this->_errorMsg;
 851      }
 852  
 853  	function ErrorNo()
 854      {
 855          $e = $this->ErrorMsg();
 856          if (strlen($e)) {
 857              return ADOConnection::MetaError($e);
 858          }
 859          return 0;
 860      }
 861  
 862      // returns true or false
 863  	function _close()
 864      {
 865          if ($this->transCnt) $this->RollbackTrans();
 866          if ($this->_resultid) {
 867              @pg_freeresult($this->_resultid);
 868              $this->_resultid = false;
 869          }
 870          @pg_close($this->_connectionID);
 871          $this->_connectionID = false;
 872          return true;
 873      }
 874  
 875  
 876      /*
 877      * Maximum size of C field
 878      */
 879  	function CharMax()
 880      {
 881          return 1000000000;  // should be 1 Gb?
 882      }
 883  
 884      /*
 885      * Maximum size of X field
 886      */
 887  	function TextMax()
 888      {
 889          return 1000000000; // should be 1 Gb?
 890      }
 891  
 892  
 893  }
 894  
 895  /*--------------------------------------------------------------------------------------
 896      Class Name: Recordset
 897  --------------------------------------------------------------------------------------*/
 898  
 899  class ADORecordSet_postgres64 extends ADORecordSet{
 900      var $_blobArr;
 901      var $databaseType = "postgres64";
 902      var $canSeek = true;
 903  
 904  	function __construct($queryID, $mode=false)
 905      {
 906          if ($mode === false) {
 907              global $ADODB_FETCH_MODE;
 908              $mode = $ADODB_FETCH_MODE;
 909          }
 910          switch ($mode)
 911          {
 912          case ADODB_FETCH_NUM: $this->fetchMode = PGSQL_NUM; break;
 913          case ADODB_FETCH_ASSOC:$this->fetchMode = PGSQL_ASSOC; break;
 914  
 915          case ADODB_FETCH_DEFAULT:
 916          case ADODB_FETCH_BOTH:
 917          default: $this->fetchMode = PGSQL_BOTH; break;
 918          }
 919          $this->adodbFetchMode = $mode;
 920  
 921          // Parent's constructor
 922          $this->ADORecordSet($queryID);
 923      }
 924  
 925  	function GetRowAssoc($upper=true)
 926      {
 927          if ($this->fetchMode == PGSQL_ASSOC && !$upper) return $this->fields;
 928          $row = ADORecordSet::GetRowAssoc($upper);
 929          return $row;
 930      }
 931  
 932  
 933  	function _initrs()
 934      {
 935      global $ADODB_COUNTRECS;
 936          $qid = $this->_queryID;
 937          $this->_numOfRows = ($ADODB_COUNTRECS)? @pg_numrows($qid):-1;
 938          $this->_numOfFields = @pg_numfields($qid);
 939  
 940          // cache types for blob decode check
 941          // apparently pg_fieldtype actually performs an sql query on the database to get the type.
 942          if (empty($this->connection->noBlobs))
 943          for ($i=0, $max = $this->_numOfFields; $i < $max; $i++) {
 944              if (pg_fieldtype($qid,$i) == 'bytea') {
 945                  $this->_blobArr[$i] = pg_fieldname($qid,$i);
 946              }
 947          }
 948      }
 949  
 950          /* Use associative array to get fields array */
 951  	function Fields($colname)
 952      {
 953          if ($this->fetchMode != PGSQL_NUM) return @$this->fields[$colname];
 954  
 955          if (!$this->bind) {
 956              $this->bind = array();
 957              for ($i=0; $i < $this->_numOfFields; $i++) {
 958                  $o = $this->FetchField($i);
 959                  $this->bind[strtoupper($o->name)] = $i;
 960              }
 961          }
 962          return $this->fields[$this->bind[strtoupper($colname)]];
 963      }
 964  
 965  	function FetchField($off = 0)
 966      {
 967          // offsets begin at 0
 968  
 969          $o= new ADOFieldObject();
 970          $o->name = @pg_fieldname($this->_queryID,$off);
 971          $o->type = @pg_fieldtype($this->_queryID,$off);
 972          $o->max_length = @pg_fieldsize($this->_queryID,$off);
 973          return $o;
 974      }
 975  
 976  	function _seek($row)
 977      {
 978          return @pg_fetch_row($this->_queryID,$row);
 979      }
 980  
 981  	function _decode($blob)
 982      {
 983          if ($blob === NULL) return NULL;
 984  //        eval('$realblob="'.adodb_str_replace(array('"','$'),array('\"','\$'),$blob).'";');
 985          return pg_unescape_bytea($blob);
 986      }
 987  
 988  	function _fixblobs()
 989      {
 990          if ($this->fetchMode == PGSQL_NUM || $this->fetchMode == PGSQL_BOTH) {
 991              foreach($this->_blobArr as $k => $v) {
 992                  $this->fields[$k] = ADORecordSet_postgres64::_decode($this->fields[$k]);
 993              }
 994          }
 995          if ($this->fetchMode == PGSQL_ASSOC || $this->fetchMode == PGSQL_BOTH) {
 996              foreach($this->_blobArr as $k => $v) {
 997                  $this->fields[$v] = ADORecordSet_postgres64::_decode($this->fields[$v]);
 998              }
 999          }
1000      }
1001  
1002      // 10% speedup to move MoveNext to child class
1003  	function MoveNext()
1004      {
1005          if (!$this->EOF) {
1006              $this->_currentRow++;
1007              if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
1008                  $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
1009                  if (is_array($this->fields) && $this->fields) {
1010                      if (isset($this->_blobArr)) $this->_fixblobs();
1011                      return true;
1012                  }
1013              }
1014              $this->fields = false;
1015              $this->EOF = true;
1016          }
1017          return false;
1018      }
1019  
1020  	function _fetch()
1021      {
1022  
1023          if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0)
1024              return false;
1025  
1026          $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
1027  
1028          if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
1029  
1030          return (is_array($this->fields));
1031      }
1032  
1033  	function _close()
1034      {
1035          return @pg_freeresult($this->_queryID);
1036      }
1037  
1038  	function MetaType($t,$len=-1,$fieldobj=false)
1039      {
1040          if (is_object($t)) {
1041              $fieldobj = $t;
1042              $t = $fieldobj->type;
1043              $len = $fieldobj->max_length;
1044          }
1045          switch (strtoupper($t)) {
1046                  case 'MONEY': // stupid, postgres expects money to be a string
1047                  case 'INTERVAL':
1048                  case 'CHAR':
1049                  case 'CHARACTER':
1050                  case 'VARCHAR':
1051                  case 'NAME':
1052                  case 'BPCHAR':
1053                  case '_VARCHAR':
1054                  case 'INET':
1055                  case 'MACADDR':
1056                      if ($len <= $this->blobSize) return 'C';
1057  
1058                  case 'TEXT':
1059                      return 'X';
1060  
1061                  case 'IMAGE': // user defined type
1062                  case 'BLOB': // user defined type
1063                  case 'BIT':    // This is a bit string, not a single bit, so don't return 'L'
1064                  case 'VARBIT':
1065                  case 'BYTEA':
1066                      return 'B';
1067  
1068                  case 'BOOL':
1069                  case 'BOOLEAN':
1070                      return 'L';
1071  
1072                  case 'DATE':
1073                      return 'D';
1074  
1075  
1076                  case 'TIMESTAMP WITHOUT TIME ZONE':
1077                  case 'TIME':
1078                  case 'DATETIME':
1079                  case 'TIMESTAMP':
1080                  case 'TIMESTAMPTZ':
1081                      return 'T';
1082  
1083                  case 'SMALLINT':
1084                  case 'BIGINT':
1085                  case 'INTEGER':
1086                  case 'INT8':
1087                  case 'INT4':
1088                  case 'INT2':
1089                      if (isset($fieldobj) &&
1090                  empty($fieldobj->primary_key) && (!$this->connection->uniqueIisR || empty($fieldobj->unique))) return 'I';
1091  
1092                  case 'OID':
1093                  case 'SERIAL':
1094                      return 'R';
1095  
1096                  default:
1097                      return 'N';
1098              }
1099      }
1100  
1101  }