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