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