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