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

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