library/adodb/drivers/adodb-postgres64.inc.php

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