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