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