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

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