[ Index ] |
PHP Cross Reference of Web Application Component Toolkit |
[Summary view] [Print] [Text view]
1 <?php 2 //-------------------------------------------------------------------------------- 3 // Copyright 2003 Procata, Inc. 4 // Released under the LGPL license (http://www.gnu.org/copyleft/lesser.html) 5 //-------------------------------------------------------------------------------- 6 /** 7 * @package WACT_DB 8 * @version $Id: pear.inc.php,v 1.31 2004/11/20 18:09:47 jeffmoore Exp $ 9 */ 10 //-------------------------------------------------------------------------------- 11 /** 12 * Make sure dataspace is loaded 13 */ 14 if (!class_exists('DataSpace')) { 15 require WACT_ROOT . 'util/dataspace.inc.php'; 16 } 17 18 /** 19 * Include PEAR::DB 20 */ 21 if ( !defined('PEAR_LIBRARY_PATH') ) { 22 define('PEAR_LIBRARY_PATH', ConfigManager::getOptionAsPath('config', 'pear', 'library_path')); 23 } 24 if (!@include_once PEAR_LIBRARY_PATH . 'DB.php') { 25 RaiseError('runtime', 'LIBRARY_REQUIRED', array( 26 'library' => 'PEAR::DB', 27 'path' => PEAR_LIBRARY_PATH)); 28 } 29 30 //-------------------------------------------------------------------------------- 31 /** 32 * Encapsulates a database connection. Allows for lazy connections. 33 * implements Connection interface 34 * @see Connection 35 * @see http://wact.sourceforge.net/index.php/Connection 36 * @access public 37 * @package WACT_DB 38 */ 39 class PearConnection { 40 /** 41 * PEAR DB Connection object 42 * @var object subclass of PEAR::DB_Common 43 * @access private 44 */ 45 var $ConnectionId; 46 47 /** 48 * Configuration information 49 * @var string 50 * @access private 51 */ 52 var $config; 53 54 /** 55 * Create a PEAR::DB database connection. 56 * @param object Connection Configuration information 57 * @access private 58 */ 59 function PearConnection(&$config) { 60 $this->config =& $config; 61 } 62 63 /** 64 * Return connectionId for a PEAR database. Allow a lazy connection. 65 * @return object subclass of PEAR::DB_Common 66 * @access protected 67 */ 68 function &getConnectionId() { 69 if (!isset($this->ConnectionId)) { 70 $this->connect(); 71 } 72 return $this->ConnectionId; 73 } 74 75 /** 76 * Connect to the the database 77 * @return void 78 * @access protected 79 */ 80 function connect() { 81 $dsn = $this->config->get('dbtype') . "://"; 82 83 $user = $this->config->get('user'); 84 if ($user) { 85 $dsn .= $user; 86 $password = $this->config->get('password'); 87 if ($password) { 88 $dsn .= ":" . $password; 89 } 90 } 91 $host = $this->config->get('host'); 92 if ($host) { 93 $dsn .= "@" . $host; 94 } 95 $database = $this->config->get('database'); 96 if ($database) { 97 $dsn .= "/" . $database; 98 } 99 $this->ConnectionId = DB::connect($dsn); 100 if (DB::isError($this->ConnectionId)) { 101 $this->RaiseError(); 102 } 103 } 104 105 /** 106 * Raises an error, passing it on to the framework level error mechanisms 107 * @param string (optional) SQL statement 108 * @return void 109 * @access private 110 */ 111 function RaiseError($sql = NULL) { 112 $error = & $this->getConnectionId(); 113 $id = 'DB_ERROR'; 114 $info = array('driver' => 'Pear'); 115 if ( DB::isError($error) ) { 116 $errno = $error->getCode(); 117 if ($errno != -1) { 118 $info['errorno'] = $errno; 119 $info['error'] = $error->getMessage(); 120 $id .= '_MESSAGE'; 121 } 122 } 123 if (!is_null($sql)) { 124 $info['sql'] = $sql; 125 $id .= '_SQL'; 126 } 127 RaiseError('db', $id, $info); 128 } 129 130 /** 131 * Convert a PHP value into an SQL literal. The type to convert to is 132 * based on the type of the PHP value passed, or the type string passed. 133 * @param mixed value to convert 134 * @param string (optional) type to convert to 135 * Allowable types are: boolean, string, int, float. 136 * @return string literal SQL fragment 137 * @access public 138 */ 139 function makeLiteral($value, $type = NULL) { 140 if (is_null($value)) { 141 return 'NULL'; 142 } 143 if (is_null($type)) { 144 $type = gettype($value); 145 } 146 switch (strtolower($type)) { 147 case 'string': 148 $conn = & $this->getConnectionId(); 149 return $conn->quoteSmart($value); 150 case 'boolean': 151 return ($value) ? 1 : 0; 152 default: 153 return strval($value); 154 } 155 } 156 157 /** 158 * Factory function to create a Record object 159 * @see http://wact.sourceforge.net/index.php/NewRecord 160 * @param DataSpace or subclass (optional) 161 * used to initialize the fields of the new record prior to calling insert() 162 * @return Record reference 163 * @access public 164 */ 165 function &NewRecord($DataSpace = NULL) { 166 $Record =& new PearRecord($this); 167 if (!is_null($DataSpace)) { 168 $Record->import($DataSpace->export()); 169 } 170 return $Record; 171 } 172 173 /** 174 * Factory function used to retrieve more than one row from a pear database, 175 * applying a filter to the data if supplied as an argument 176 * @see http://wact.sourceforge.net/index.php/NewRecordSet 177 * @param string SQL statement 178 * @param object filter class (optional) 179 * @return RecordSet reference 180 * @access public 181 */ 182 function &NewRecordSet($query, $filter = NULL) { 183 $RecordSet =& new PearRecordSet($this, $query); 184 if (!is_null($filter)) { 185 $RecordSet->registerFilter($filter); 186 } 187 return $RecordSet; 188 } 189 190 /** 191 * Factory function used to retrieve more than one row from a pear database, 192 * applying a filter to the data if supplied as an argument, and applying a 193 * pager to the result set as well. 194 * @param string SQL statement 195 * @param object pager 196 * @param object filter class (optional) 197 * @return RecordSet reference 198 * @access public 199 */ 200 function &NewPagedRecordSet($query, &$pager, $filter = NULL) { 201 $RecordSet =& $this->NewRecordSet($query, $filter); 202 $RecordSet->paginate($pager); 203 return $RecordSet; 204 } 205 206 /** 207 * Retreive a single record from the database based on a query. 208 * @param string SQL Query 209 * @return Record object or NULL if not found 210 * @access public 211 */ 212 function &FindRecord($query) { 213 $Record =& new PearRecord($this); 214 $QueryId = $this->_execute($query); 215 $Record->properties =& $QueryId->fetchRow(DB_FETCHMODE_ASSOC); 216 $QueryId->free(); 217 if (is_array($Record->properties)) { 218 return $Record; 219 } 220 } 221 222 /** 223 * Get a single value from the first column of a single record from 224 * a database query. 225 * @param string SQL Query 226 * @return Value or NULL if not found 227 * @access public 228 */ 229 function getOneValue($query) { 230 $QueryId = $this->_execute($query); 231 $row = $QueryId->fetchRow(); 232 $QueryId->free(); 233 if (is_array($row)) { 234 return $row[0]; 235 } 236 } 237 238 /** 239 * Retreive an array where each element of the array is the value from the 240 * first column of a database query. 241 * @param string SQL Query 242 * @access public 243 */ 244 function getOneColumnArray($query) { 245 $Column = array(); 246 $QueryId = $this->_execute($query); 247 while (is_array($row = $QueryId->fetchRow())) { 248 $Column[] = $row[0]; 249 } 250 $QueryId->free(); 251 return $Column; 252 } 253 254 /** 255 * Retreive an associative array where each element of the array is based 256 * on the first column as a key and the second column as data. 257 * @param string SQL Query 258 * @access public 259 */ 260 function getTwoColumnArray($query) { 261 $Column = array(); 262 $QueryId = $this->_execute($query); 263 while (is_array($row = $QueryId->fetchRow())) { 264 $Column[$row[0]] = $row[1]; 265 } 266 $QueryId->free(); 267 return $Column; 268 } 269 270 /** 271 * Performs any query that does not return a cursor. 272 * @param string SQL query 273 * @return boolean TRUE if query is successful 274 */ 275 function execute($sql) { 276 return (Boolean) $this->_execute($sql); 277 } 278 279 /** 280 * For internal driver use only 281 * @param string SQL query 282 * @return object PEAR DB_Result or DB_Error 283 * @access public 284 */ 285 function _execute($sql) { 286 $conn = & $this->getConnectionId(); 287 $result = & $conn->query($sql); 288 if (DB::isError($result)) { 289 $this->RaiseError($sql); 290 } 291 return $result; 292 } 293 294 /** 295 * Disconnect from database 296 * @return void 297 * @access public 298 */ 299 function disconnect() { 300 if (is_object($this->ConnectionId)) { 301 $this->ConnectionId->disconnect(); 302 $this->ConnectionId = NULL; 303 } 304 } 305 306 } 307 308 /** 309 * Encapsulates operations on a database via PEAR::DB. Generally this 310 * class is only used for INSERT, UPDATE and DELETE operations 311 * implements Record interface 312 * @see Record 313 * @see http://wact.sourceforge.net/index.php/Record 314 * @access public 315 * @package WACT_DB 316 */ 317 class PearRecord extends DataSpace { 318 /** 319 * Database connection encasulated in PearConnection 320 * @var PearConnection instance 321 * @access private 322 */ 323 var $Connection; 324 325 /** 326 * Construct a record 327 * @param PearConnection 328 */ 329 function PearRecord(& $Connection) { 330 $this->Connection = & $Connection; 331 } 332 333 /** 334 * Build a list of values to assign to columns 335 * @param array associative of field_name => type 336 * @param array associative (optional) of field_name => value 337 * @return array List of values to assign 338 * @access protected 339 */ 340 function buildAssignmentList($fields, $extrafields) { 341 $queryParams = array(); 342 foreach ($fields as $fieldname => $type) { 343 if (!is_string($fieldname)) { 344 $fieldname = $type; // Swap if no type is specified 345 $type = NULL; 346 } 347 $queryParams[$fieldname] = $this->Connection->makeLiteral( 348 $this->get($fieldname), $type 349 ); 350 } 351 if (!is_null($extrafields)) { 352 foreach ($extrafields as $fieldname => $value) { 353 $queryParams[$fieldname] = $value; 354 } 355 } 356 return $queryParams; 357 } 358 359 /** 360 * INSERT a record into a table with a primary key represented by a 361 * auto_increment/serial column and return the primary key of the 362 * inserted record. 363 * the field list parameter allows expressions to defined in the sql 364 * statements as well as field values defined in the record. 365 * @param string table name 366 * @param array associative of field_name => type 367 * @param string Name of primary key field field 368 * @param array associative (optional) of field_name => value 369 * @return integer Primary key of the newly inserted record or FALSE if no 370 * record was inserted. 371 */ 372 function insertId($table, $fields, $primary_key_field, $extrafields = NULL) { 373 $valueList = $this->buildAssignmentList($fields, $extrafields); 374 $query = 'INSERT INTO ' . $table . 375 ' (' . implode(',', array_keys($valueList)) .') VALUES' . 376 ' (' . implode(',', $valueList) . ')'; 377 $result = $this->Connection->execute($query); 378 if ($result) { 379 $valueList = $this->buildAssignmentList($fields, null); 380 $pairs = array(); 381 foreach ($valueList as $key => $value) { 382 $pairs[] = $key .'='. $value; 383 } 384 $query = 'SELECT MAX('.$primary_key_field.') FROM ' . $table . 385 ' WHERE (' . implode(' AND ', $pairs) . ')'; 386 $result = (int)$this->Connection->getOneValue($query); 387 } 388 return $result; 389 } 390 391 392 /** 393 * INSERTs the values of this record into a single table 394 * the field list parameter allows expressions to defined in the sql 395 * statements as well as field values defined in the record. 396 * @param string table name 397 * @param array associative of field_name => type 398 * @param string (default = null) Name of autoincrement field 399 * @param array associative (optional) of field_name => value 400 * @return Boolean True on success. 401 */ 402 function insert($table, $fields, $extrafields = NULL) { 403 $valueList = $this->buildAssignmentList($fields, $extrafields); 404 $query = 'INSERT INTO ' . $table . 405 ' (' . implode(',', array_keys($valueList)) .') VALUES' . 406 ' (' . implode(',', $valueList) . ')'; 407 return (Boolean) $this->Connection->execute($query); 408 } 409 410 /** 411 * Performs an UPDATE on a single table 412 * @param string table name 413 * @param array associative of field_name => type 414 * @param string (optional) SQL where clause 415 * @param array associative (optional) of field_name => value 416 * @return boolean true on success, false on failure 417 * @access public 418 */ 419 function update($table, $fields, $where = NULL, $extrafields = NULL) { 420 $valueList = $this->buildAssignmentList($fields, $extrafields); 421 $query = 'UPDATE ' . $table . ' SET '; 422 $sep = ''; 423 foreach ($valueList as $key => $value) { 424 $query .= $sep . $key .'='. $value; 425 $sep = ', '; 426 } 427 if (!is_null($where)) { 428 $query .= ' WHERE ' . $where; 429 } 430 return (Boolean) $this->Connection->execute($query); 431 } 432 433 /** 434 * Gets the number of rows changed by a query 435 * @return int number of affected rows 436 * @access public 437 */ 438 function getAffectedRowCount() { 439 $QueryId = & $this->Connection->getConnectionId(); 440 return $QueryId->affectedRows(); 441 } 442 443 } 444 445 /** 446 * Encapsulates the results of a SELECT, SHOW, DESCRIBE or EXPLAIN sql statement 447 * Implements the Iterator interface defined in the DataSpace 448 * implements RecordSet and PagedDataSet interfaces 449 * @see RecordSet 450 * @see PagedDataSet 451 * @see http://wact.sourceforge.net/index.php/RecordSet 452 * @access public 453 * @package WACT_DB 454 */ 455 class PearRecordSet extends PearRecord { 456 /** 457 * PEAR::DB Result Object 458 * @var object 459 * @access private 460 */ 461 var $QueryId; 462 463 /** 464 * Pager 465 * @var object The current pager for this query. 466 * @access private 467 */ 468 var $pager; 469 470 /** 471 * SQL Statement 472 * @var string 473 * @access private 474 */ 475 var $Query; 476 477 /** 478 * Switch to watch if this is the first row 479 * @var boolean (default = TRUE) 480 * @access private 481 */ 482 var $first = TRUE; 483 484 /** 485 * Switch to watch for resets 486 * @var boolean (default = FALSE) 487 * @access private 488 */ 489 var $reentry = FALSE; 490 491 /** 492 * Construct a record set. 493 * @param object PearConnection 494 * @param string SQL SELECT, SHOW, DESCRIBE, or EXPLAIN statement 495 * @access public 496 */ 497 function PearRecordSet($Connection, $Query_String) { 498 $this->Connection = $Connection; 499 $this->Query = $Query_String; 500 } 501 502 /** 503 * Stores the SQL statement and makes sure the result object is 504 * empty 505 * @param string SQL statement 506 * @return void 507 * @access protected 508 */ 509 function query($Query_String) { 510 $this->freeQuery(); 511 $this->Query = $Query_String; 512 } 513 514 /** 515 * Assign a pager to this query for the purposes of breaking up the resulting 516 * cursor into paged chucks. 517 * @param interface Pager 518 * @return void 519 * @access public 520 */ 521 function paginate(&$pager) { 522 $this->pager =& $pager; 523 $pager->setPagedDataSet($this); 524 } 525 526 /** 527 * Frees up the Result object if one exists 528 * @return void 529 * @access private 530 */ 531 function freeQuery() { 532 if (isset($this->QueryId) && is_object($this->QueryId)) { 533 $this->QueryId->free(); 534 $this->QueryId = NULL; 535 } 536 } 537 538 /** 539 * Move the current pointer to the first position in the cursor. 540 * @return void 541 * @access public 542 */ 543 function reset() { 544 $query = $this->Query; 545 if (isset($this->pager)) { 546 $conn = & $this->Connection->getConnectionId(); 547 $query = $conn->modifyLimitQuery($query, 548 $this->pager->getStartingItem(),$this->pager->getItemsPerPage()); 549 } 550 $this->QueryId = $this->Connection->_execute($query); 551 return TRUE; 552 } 553 554 /** 555 * Iterator next method 556 * @return boolean TRUE if there are more results to fetch 557 * @access public 558 */ 559 function next() { 560 if (!isset($this->QueryId)) { 561 return FALSE; 562 } 563 $this->properties = $this->QueryId->fetchRow(DB_FETCHMODE_ASSOC); 564 if (is_array($this->properties)) { 565 $this->prepare(); 566 return TRUE; 567 } else { 568 $this->freeQuery(); 569 return FALSE; 570 } 571 } 572 573 /** 574 * Returns the number of rows in a query 575 * @return int number of rows 576 * @access public 577 */ 578 function getRowCount() { 579 return $this->QueryId->numRows(); 580 } 581 582 /** 583 * Returns the total number of rows that a query would return, ignoring paging 584 * restrictions. Query re-writing based on _adodb_getcount. 585 * @return int number of rows 586 * @access public 587 */ 588 function getTotalRowCount() { 589 if (!(preg_match("/^\s*SELECT\s+DISTINCT/is", $this->Query) && preg_match('/\s+GROUP\s+BY\s+/is',$this->Query))) { 590 $rewritesql = preg_replace( 591 '/^\s*SELECT\s.*\s+FROM\s/Uis','SELECT COUNT(*) FROM ',$this->Query); 592 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is','',$rewritesql); 593 $QueryId = $this->Connection->_execute($rewritesql); 594 $row = $QueryId->fetchRow(); 595 $QueryId->free(); 596 if (is_array($row)) { 597 return $row[0]; 598 } 599 } 600 601 // could not re-write the query, try a different method. 602 $QueryId = $this->Connection->_execute($this->Query); 603 $count = $QueryId->numRows(); 604 $QueryId->free(); 605 return $count; 606 } 607 } 608 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
Generated: Sun Nov 28 19:36:09 2004 | Cross-referenced by PHPXref 0.5 |