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