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

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