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