Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 4.2.x will end 22 April 2024 (12 months).
- Bug fixes for security issues in 4.2.x will end 7 October 2024 (18 months).
- PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.1.x is supported too.
/lib/adodb/drivers/ -> adodb-postgres64.inc.php (source)
Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402] [Versions 401 and 402] [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 $col_names = $this->MetaColumnNames($table,true,true); 663 // 3rd param is use attnum, 664 // see https://sourceforge.net/p/adodb/bugs/45/ 665 $indexes = array(); 666 while ($row = $rs->FetchRow()) { 667 $columns = array(); 668 foreach (explode(' ', $row[2]) as $col) { 669 $columns[] = $col_names[$col]; 670 } 671 672 $indexes[$row[0]] = array( 673 'unique' => ($row[1] == 't'), 674 'columns' => $columns 675 ); 676 } 677 return $indexes; 678 } 679 680 /** 681 * Connect to a database. 682 * 683 * Examples: 684 * $db->Connect("host=host1 user=user1 password=secret port=4341"); 685 * $db->Connect('host1:4341', 'user1', 'secret'); 686 * 687 * @param string $str pg_connect() Connection string or Hostname[:port] 688 * @param string $user (Optional) The username to connect as. 689 * @param string $pwd (Optional) The password to connect with. 690 * @param string $db (Optional) The name of the database to start in when connected. 691 * @param int $ctype Connection type 692 * @return bool|null True if connected successfully, false if connection failed, or 693 * null if the PostgreSQL extension is not loaded. 694 */ 695 function _connect($str, $user='', $pwd='', $db='', $ctype=0) 696 { 697 if (!function_exists('pg_connect')) { 698 return null; 699 } 700 701 $this->_errorMsg = false; 702 703 // If $user, $pwd and $db are all null, then $str is a pg_connect() 704 // connection string. Otherwise we expect it to be a hostname, 705 // with optional port separated by ':' 706 if ($user || $pwd || $db) { 707 // Hostname & port 708 if ($str) { 709 $host = explode(':', $str); 710 if ($host[0]) { 711 $conn['host'] = $host[0]; 712 } 713 if (isset($host[1])) { 714 $conn['port'] = (int)$host[1]; 715 } elseif (!empty($this->port)) { 716 $conn['port'] = $this->port; 717 } 718 } 719 $conn['user'] = $user; 720 $conn['password'] = $pwd; 721 // @TODO not sure why we default to 'template1', pg_connect() uses the username when dbname is empty 722 $conn['dbname'] = $db ?: 'template1'; 723 724 // Generate connection string 725 $str = ''; 726 foreach ($conn as $param => $value) { 727 // Escaping single quotes and backslashes per pg_connect() documentation 728 $str .= $param . "='" . addcslashes($value, "'\\") . "' "; 729 } 730 } 731 732 if ($ctype === 1) { // persistent 733 $this->_connectionID = pg_pconnect($str); 734 } else { 735 if ($ctype === -1) { // nconnect, we trick pgsql ext by changing the connection str 736 static $ncnt; 737 738 if (empty($ncnt)) $ncnt = 1; 739 else $ncnt += 1; 740 741 $str .= str_repeat(' ',$ncnt); 742 } 743 $this->_connectionID = pg_connect($str); 744 } 745 if ($this->_connectionID === false) return false; 746 $this->Execute("set datestyle='ISO'"); 747 748 $info = $this->ServerInfo(false); 749 750 if (version_compare($info['version'], '7.1', '>=')) { 751 $this->_nestedSQL = true; 752 } 753 754 # PostgreSQL 9.0 changed the default output for bytea from 'escape' to 'hex' 755 # PHP does not handle 'hex' properly ('x74657374' is returned as 't657374') 756 # https://bugs.php.net/bug.php?id=59831 states this is in fact not a bug, 757 # so we manually set bytea_output 758 if (version_compare($info['version'], '9.0', '>=') 759 && version_compare($info['client'], '9.2', '<') 760 ) { 761 $this->Execute('set bytea_output=escape'); 762 } 763 764 return true; 765 } 766 767 function _nconnect($argHostname, $argUsername, $argPassword, $argDatabaseName) 768 { 769 return $this->_connect($argHostname, $argUsername, $argPassword, $argDatabaseName,-1); 770 } 771 772 // returns true or false 773 // 774 // examples: 775 // $db->PConnect("host=host1 user=user1 password=secret port=4341"); 776 // $db->PConnect('host1','user1','secret'); 777 function _pconnect($str,$user='',$pwd='',$db='') 778 { 779 return $this->_connect($str,$user,$pwd,$db,1); 780 } 781 782 783 // returns queryID or false 784 function _query($sql,$inputarr=false) 785 { 786 $this->_pnum = 0; 787 $this->_errorMsg = false; 788 if ($inputarr) { 789 /* 790 It appears that PREPARE/EXECUTE is slower for many queries. 791 792 For query executed 1000 times: 793 "select id,firstname,lastname from adoxyz 794 where firstname not like ? and lastname not like ? and id = ?" 795 796 with plan = 1.51861286163 secs 797 no plan = 1.26903700829 secs 798 */ 799 $plan = 'P'.md5($sql); 800 801 $execp = ''; 802 foreach($inputarr as $v) { 803 if ($execp) $execp .= ','; 804 if (is_string($v)) { 805 if (strncmp($v,"'",1) !== 0) $execp .= $this->qstr($v); 806 } else { 807 $execp .= $v; 808 } 809 } 810 811 if ($execp) $exsql = "EXECUTE $plan ($execp)"; 812 else $exsql = "EXECUTE $plan"; 813 814 $rez = @pg_query($this->_connectionID, $exsql); 815 if (!$rez) { 816 # Perhaps plan does not exist? Prepare/compile plan. 817 $params = ''; 818 foreach($inputarr as $v) { 819 if ($params) $params .= ','; 820 if (is_string($v)) { 821 $params .= 'VARCHAR'; 822 } else if (is_integer($v)) { 823 $params .= 'INTEGER'; 824 } else { 825 $params .= "REAL"; 826 } 827 } 828 $sqlarr = explode('?',$sql); 829 //print_r($sqlarr); 830 $sql = ''; 831 $i = 1; 832 foreach($sqlarr as $v) { 833 $sql .= $v.' $'.$i; 834 $i++; 835 } 836 $s = "PREPARE $plan ($params) AS ".substr($sql,0,strlen($sql)-2); 837 //adodb_pr($s); 838 $rez = pg_query($this->_connectionID, $s); 839 //echo $this->ErrorMsg(); 840 } 841 if ($rez) 842 $rez = pg_query($this->_connectionID, $exsql); 843 } else { 844 //adodb_backtrace(); 845 $rez = pg_query($this->_connectionID, $sql); 846 } 847 // check if no data returned, then no need to create real recordset 848 if ($rez && pg_num_fields($rez) <= 0) { 849 if ($this->_resultid !== false) { 850 pg_free_result($this->_resultid); 851 } 852 $this->_resultid = $rez; 853 return true; 854 } 855 856 return $rez; 857 } 858 859 function _errconnect() 860 { 861 if (defined('DB_ERROR_CONNECT_FAILED')) return DB_ERROR_CONNECT_FAILED; 862 else return 'Database connection failed'; 863 } 864 865 /* Returns: the last error message from previous database operation */ 866 function ErrorMsg() 867 { 868 if ($this->_errorMsg !== false) { 869 return $this->_errorMsg; 870 } 871 872 if (!empty($this->_resultid)) { 873 $this->_errorMsg = @pg_result_error($this->_resultid); 874 if ($this->_errorMsg) { 875 return $this->_errorMsg; 876 } 877 } 878 879 if (!empty($this->_connectionID)) { 880 $this->_errorMsg = @pg_last_error($this->_connectionID); 881 } else { 882 $this->_errorMsg = $this->_errconnect(); 883 } 884 885 return $this->_errorMsg; 886 } 887 888 function ErrorNo() 889 { 890 $e = $this->ErrorMsg(); 891 if (strlen($e)) { 892 return ADOConnection::MetaError($e); 893 } 894 return 0; 895 } 896 897 // returns true or false 898 function _close() 899 { 900 if ($this->transCnt) $this->RollbackTrans(); 901 if ($this->_resultid) { 902 @pg_free_result($this->_resultid); 903 $this->_resultid = false; 904 } 905 @pg_close($this->_connectionID); 906 $this->_connectionID = false; 907 return true; 908 } 909 910 911 /* 912 * Maximum size of C field 913 */ 914 function CharMax() 915 { 916 return 1000000000; // should be 1 Gb? 917 } 918 919 /* 920 * Maximum size of X field 921 */ 922 function TextMax() 923 { 924 return 1000000000; // should be 1 Gb? 925 } 926 927 928 } 929 930 /*-------------------------------------------------------------------------------------- 931 Class Name: Recordset 932 --------------------------------------------------------------------------------------*/ 933 934 class ADORecordSet_postgres64 extends ADORecordSet{ 935 var $_blobArr; 936 var $databaseType = "postgres64"; 937 var $canSeek = true; 938 939 function __construct($queryID, $mode=false) 940 { 941 if ($mode === false) { 942 global $ADODB_FETCH_MODE; 943 $mode = $ADODB_FETCH_MODE; 944 } 945 switch ($mode) 946 { 947 case ADODB_FETCH_NUM: $this->fetchMode = PGSQL_NUM; break; 948 case ADODB_FETCH_ASSOC:$this->fetchMode = PGSQL_ASSOC; break; 949 950 case ADODB_FETCH_DEFAULT: 951 case ADODB_FETCH_BOTH: 952 default: $this->fetchMode = PGSQL_BOTH; break; 953 } 954 $this->adodbFetchMode = $mode; 955 956 // Parent's constructor 957 parent::__construct($queryID); 958 } 959 960 function GetRowAssoc($upper = ADODB_ASSOC_CASE) 961 { 962 if ($this->fetchMode == PGSQL_ASSOC && $upper == ADODB_ASSOC_CASE_LOWER) { 963 return $this->fields; 964 } 965 $row = ADORecordSet::GetRowAssoc($upper); 966 return $row; 967 } 968 969 function _initRS() 970 { 971 global $ADODB_COUNTRECS; 972 $qid = $this->_queryID; 973 $this->_numOfRows = ($ADODB_COUNTRECS)? @pg_num_rows($qid):-1; 974 $this->_numOfFields = @pg_num_fields($qid); 975 976 // cache types for blob decode check 977 // apparently pg_field_type actually performs an sql query on the database to get the type. 978 if (empty($this->connection->noBlobs)) 979 for ($i=0, $max = $this->_numOfFields; $i < $max; $i++) { 980 if (pg_field_type($qid,$i) == 'bytea') { 981 $this->_blobArr[$i] = pg_field_name($qid,$i); 982 } 983 } 984 } 985 986 function fields($colname) 987 { 988 if ($this->fetchMode != PGSQL_NUM) { 989 return @$this->fields[$colname]; 990 } 991 992 if (!$this->bind) { 993 $this->bind = array(); 994 for ($i=0; $i < $this->_numOfFields; $i++) { 995 $o = $this->FetchField($i); 996 $this->bind[strtoupper($o->name)] = $i; 997 } 998 } 999 return $this->fields[$this->bind[strtoupper($colname)]]; 1000 } 1001 1002 function fetchField($fieldOffset = 0) 1003 { 1004 // offsets begin at 0 1005 1006 $o = new ADOFieldObject(); 1007 $o->name = @pg_field_name($this->_queryID, $fieldOffset); 1008 $o->type = @pg_field_type($this->_queryID, $fieldOffset); 1009 $o->max_length = @pg_field_size($this->_queryID, $fieldOffset); 1010 return $o; 1011 } 1012 1013 function _seek($row) 1014 { 1015 return @pg_fetch_row($this->_queryID,$row); 1016 } 1017 1018 function _decode($blob) 1019 { 1020 if ($blob === NULL) return NULL; 1021 // eval('$realblob="'.str_replace(array('"','$'),array('\"','\$'),$blob).'";'); 1022 return pg_unescape_bytea($blob); 1023 } 1024 1025 /** 1026 * Fetches and prepares the RecordSet's fields. 1027 * 1028 * Fixes the blobs if there are any. 1029 */ 1030 protected function _prepFields() 1031 { 1032 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode); 1033 1034 // Check prerequisites and bail early if we do not have what we need. 1035 if (!isset($this->_blobArr) || $this->fields === false) { 1036 return; 1037 } 1038 1039 if ($this->fetchMode == PGSQL_NUM || $this->fetchMode == PGSQL_BOTH) { 1040 foreach($this->_blobArr as $k => $v) { 1041 $this->fields[$k] = ADORecordSet_postgres64::_decode($this->fields[$k]); 1042 } 1043 } 1044 if ($this->fetchMode == PGSQL_ASSOC || $this->fetchMode == PGSQL_BOTH) { 1045 foreach($this->_blobArr as $k => $v) { 1046 $this->fields[$v] = ADORecordSet_postgres64::_decode($this->fields[$v]); 1047 } 1048 } 1049 } 1050 1051 // 10% speedup to move MoveNext to child class 1052 function MoveNext() 1053 { 1054 if (!$this->EOF) { 1055 $this->_currentRow++; 1056 if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) { 1057 $this->_prepfields(); 1058 if ($this->fields !== false) { 1059 return true; 1060 } 1061 } 1062 $this->fields = false; 1063 $this->EOF = true; 1064 } 1065 return false; 1066 } 1067 1068 function _fetch() 1069 { 1070 if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0) { 1071 return false; 1072 } 1073 1074 $this->_prepfields(); 1075 return $this->fields !== false; 1076 } 1077 1078 function _close() 1079 { 1080 if ($this->_queryID === false || $this->_queryID == self::DUMMY_QUERY_ID) { 1081 return true; 1082 } 1083 return pg_free_result($this->_queryID); 1084 } 1085 1086 function MetaType($t,$len=-1,$fieldobj=false) 1087 { 1088 if (is_object($t)) { 1089 $fieldobj = $t; 1090 $t = $fieldobj->type; 1091 $len = $fieldobj->max_length; 1092 } 1093 1094 $t = strtoupper($t); 1095 1096 if (array_key_exists($t,$this->connection->customActualTypes)) 1097 return $this->connection->customActualTypes[$t]; 1098 1099 switch ($t) { 1100 case 'MONEY': // stupid, postgres expects money to be a string 1101 case 'INTERVAL': 1102 case 'CHAR': 1103 case 'CHARACTER': 1104 case 'VARCHAR': 1105 case 'NAME': 1106 case 'BPCHAR': 1107 case '_VARCHAR': 1108 case 'CIDR': 1109 case 'INET': 1110 case 'MACADDR': 1111 case 'UUID': 1112 if ($len <= $this->blobSize) return 'C'; 1113 1114 case 'TEXT': 1115 return 'X'; 1116 1117 case 'IMAGE': // user defined type 1118 case 'BLOB': // user defined type 1119 case 'BIT': // This is a bit string, not a single bit, so don't return 'L' 1120 case 'VARBIT': 1121 case 'BYTEA': 1122 return 'B'; 1123 1124 case 'BOOL': 1125 case 'BOOLEAN': 1126 return 'L'; 1127 1128 case 'DATE': 1129 return 'D'; 1130 1131 1132 case 'TIMESTAMP WITHOUT TIME ZONE': 1133 case 'TIME': 1134 case 'DATETIME': 1135 case 'TIMESTAMP': 1136 case 'TIMESTAMPTZ': 1137 return 'T'; 1138 1139 case 'SMALLINT': 1140 case 'BIGINT': 1141 case 'INTEGER': 1142 case 'INT8': 1143 case 'INT4': 1144 case 'INT2': 1145 if (isset($fieldobj) && 1146 empty($fieldobj->primary_key) && (!$this->connection->uniqueIisR || empty($fieldobj->unique))) return 'I'; 1147 1148 case 'OID': 1149 case 'SERIAL': 1150 return 'R'; 1151 1152 case 'NUMERIC': 1153 case 'DECIMAL': 1154 case 'FLOAT4': 1155 case 'FLOAT8': 1156 return 'N'; 1157 1158 default: 1159 return ADODB_DEFAULT_METATYPE; 1160 } 1161 } 1162 1163 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
