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