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

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