[ Index ]

PHP Cross Reference of Web Application Component Toolkit

title

Body

[close]

/framework/db/drivers/ -> mysql.inc.php (source)

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


Generated: Sun Nov 28 19:36:09 2004 Cross-referenced by PHPXref 0.5