[Summary view] [Print] [Text view]

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