Location: PHPKode > projects > dizzyPages > dizzypages/include/DzeMySQL.class.php
<?php
/*
 * Copyright (c) 2004, Doron Enav, dizzyPages
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * - Redistributions of source code must retain the above copyright notice,
 *   this list of conditions and the following disclaimer.
 * - Redistributions in binary form must reproduce the above copyright notice,
 *   this list of conditions and the following disclaimer in the documentation
 *   and/or other materials provided with the distribution.
 * - Neither the name dizzyPages nor the names of its contributors may be used
 *   to endorse or promote products derived from this software without specific
 *   prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */
/**
 * dizzyPages application framework
 *
 * @package dizzyPages
 * @copyright dizzyPages
 * @author Doron Enav <hide@address.com>
 */
/**
 * DzeMySQL
 *
 * MySQL database connectivity including DDL and DML functionality.
 *
 * @author Doron Enav
 */
class DzeMySQL
{
   /**
    * @var resource as defined by the return valu of the function 
    * {@link http://www.php.net/manual/en/function.mysql-query.php mysql_query}
    */
   public $dbResult;
   /**
    * @var integer number of rows selected or affected by executing an SQL
    * statement
    */
   public $dbCount;

   private $host;
   private $user;
   private $password;
   private $name;

   private $serverHandle;
   private $dbHandle;
 
   private $dbResultStack = array();
   private $dbResultDepth = 0;

   private $errorDieOn = true;
   private $errorId;
   private $errorText;

   private $logHandle;

   private $tableType;
   private $isMySqlI;

   /**
    * create an instance of a MySQL object
    *
    * a connection to the database is established upon creation
    *
    * @param array database session configuration.  The configuration is an
    * asscoiative array containing the following settings:
    * <ul>
    * <li> 'mysql_server' - MySQL server connecting to
    * <li> 'mysql_db' - MySQL database connecting to
    * <li> 'mysql_user' - MySQL database user
    * <li> 'mysql_password' - MySQL database user password
    * </ul>
    */
   function __construct(& $config)
   {
      $this->host     = $config["mysql_server"];
      $this->name     = $config["mysql_db"];
      $this->user     = $config["mysql_user"];
      $this->password = $config["mysql_password"];

      $this->setUpLogging($config);

      $this->isMySqlI = (extension_loaded("mysql")) ? false : true ; 

      if ($this->isMySqlI) {
         $this->serverHandle = @mysqli_connect(
            $this->host,
            $this->user,
            $this->password,
	    $this->name
         ) or $this->connect_error();
      }
      else {
         $this->serverHandle = @mysql_connect(
            $this->host,
            $this->user,
            $this->password
         ) or $this->error();
         $this->dbHandle = @mysql_select_db($this->name) or $this->error();
      }

      // The table type set to determine which type of locking to use in
      // begin and commit work. If the tables are not InnoDB than Table 
      // locking is used instead of row locking
      // Table locking will cause serial instead of parallel transaction
      // processing
      // $this->tableType = $this->getTableType("DZE_USR");
      //
      // for now only supporting InnoDB tables
      $this->tableType = 'InnoDB';
   }

   /**#@+
    * @internal
    * @access private
    */
   private function setUpLogging(& $config) {
      if (   !is_null($config)
          && $config["log_level"] >= 5) {

         $ext = $config["log_type"] == 2 ? ".txt" : ".sql";

         if ($config["log_type"] == 3) {
            $ct = 0;
            $file =   $config["directory"]
                    . $config["log_file"] . '.'
                    . $config["run_at"]
                    . sprintf("%03d", $ct)
                    . $ext;
            while (file_exists($file)) {
               $ct++;
               $file =   $config["directory"]
                       . $config["log_file"] . '.'
                       . $config["run_at"]
                       . sprintf("%03d", $ct)
                       . $ext;
            }
         }
         else {
            $file =   $config["directory"] . $config["log_file"] . $ext;
         }

         $this->logHandle = fopen ($file, "a");
      }
   }

   public function getTableType($tableName) {
      $this->query("show table status like '" . $tableName . "'");

      if ($this->isMySqlI)
         $row = mysqli_fetch_assoc($this->dbResult);
      else
         $row = mysql_fetch_assoc($this->dbResult);
      return ($row ? $row['Type'] : 'Unknown');
   }

   private function connect_error()
   {
      $this->errorId = mysqli_connect_errno();
      $this->errorTxt = mysqli_connect_error();

      if ($this->errorDieOn)
        throw new Exception("MYSQL ERROR: $this->errorId  MESSAGE: $this->errorTxt $message", 501);
   }

   private function error($message = "")
   {
      if ($this->isMySqlI) {
         $this->errorId = mysqli_errno($this->serverHandle);
         $this->errorTxt = mysqli_error($this->serverHandle);
      }
      else {
         $this->errorId = mysql_errno();
         $this->errorTxt = mysql_error();
      }

      if ($this->errorDieOn)
        throw new Exception("MYSQL ERROR: $this->errorId  MESSAGE: $this->errorTxt $message", 501);
   }

   public function columnList($nameValueA, $delim = ", ")
   {
      return implode(array_keys($nameValueA), $delim);
   }

   public function valueList($nameValueA, $delim = ", ")
   {
      $valueList = "";
      $columnCt = 0;

      foreach($nameValueA as $Name => $Value) {
         if ($columnCt > 0) $valueList .= $delim; else $columnCt++;
         $valueList .= 
            (is_null($Value)
              ? "NULL"
              : "'" . ($this->isMySqlI ? mysqli_real_escape_string($this->serverHandle, $Value) : mysql_real_escape_string($Value)) . "'");
      }

      return $valueList;
   }

   public function nameValueList($nameValueA, $delim = ", ")
   {
      $valueList = "";
      $columnCt = 0;

      foreach($nameValueA as $Name => $Value) {
         if ($columnCt > 0) $valueList .= $delim; else $columnCt++;
         $valueList .= $Name . " = " .
            (is_null($Value)
               ? "NULL"
               : "'" . ($this->isMySqlI ? mysqli_real_escape_string($this->serverHandle, $Value) : mysql_real_escape_string($Value)) . "'");
      }

      return $valueList;
   }
   /**#@-*/

   /**
    * push the current query result {@link $dbResult} set onto the stack
    *
    * only one result set may be worked on at a time. The stack provides a
    * method by which result sets may be stored for later usage.
    *
    * @return true - current query result set pushed onto stack
    */
   public function pushResult()
   {
      $this->dbResultStack[$this->dbResultDepth]
         = array($this->dbResult, $this->dbCount);
      $this->dbResult = null; $this->dbCount = null;
      $this->dbResultDepth++;

      return true;
   }

   /**
    * pop the last query result set off the stack
    *
    * retrieves the last pushed result set off the stack as the
    * current query result {@link $dbResult} set.
    *
    * @return boolean true - current query result set popped off stack
    * @throws -401, Stack is empty.
    */
   public function popResult()
   {
      if ($this->dbResultDepth == 0)
         throw new Exception("Stack is empty.", -401);

      $this->dbResultDepth--;
      list($this->dbResult, $this->dbCount)
         = $this->dbResultStack[$this->dbResultDepth];
      unset($this->dbResultStack[$this->dbResultDepth]);

      return true;
   }

   /**
    * close the connection to the server and database
    *
    * @return boolean true - connection closed
    * @throws -411, Connection has already been closed
    */
   public function closeDB()
   {
      if (is_null($this->serverHandle))
         throw new Exception("Connection has already been closed.", -411);

      if (isset($this->logHandle)) fclose($this->logHandle);
      if ($this->isMySqlI)
         mysqli_close($this->serverHandle);
      else
         mysql_close($this->serverHandle);
      $this->serverHandle = null;

      return true;
   }

   /**
    * execute an SQL DDL or DML database query
    *
    * returns results to {@link $dbResult} and {@link $dbCount}
    *
    * @param string sql DDL or DML statement
    * @param string
    * <ul>
    * <li> 'Y' - Yes <b>$sqlStat</b> is an SQL select statement
    * <li> 'N' - No <b>$sqlStat</b> is not an SQL select statement
    * </ul>
    */
   public function query($sqlStat, $isSelect = "N")
   {
      if (isset($this->logHandle)) {
         fwrite($this->logHandle, $sqlStat . "\n");
         fflush($this->logHandle);
      }

      if ($this->isMySqlI) {
         $this->dbResult = @mysqli_query($this->serverHandle, $sqlStat)
            or $this->error($sqlStat);
         $this->dbCount =
            $isSelect == "Y"
              ? mysqli_num_rows($this->dbResult)
              : mysqli_affected_rows($this->serverHandle);
      }
      else {
         $this->dbResult = @mysql_query($sqlStat, $this->serverHandle)
            or $this->error($sqlStat);
         $this->dbCount =
            $isSelect == "Y"
              ? mysql_num_rows($this->dbResult)
              : mysql_affected_rows();
      }
   }

   /**
    * free program resources of in {@link $dbResult} after an SQL select query
    */
   public function freeResult()
   {
      if ($this->isMySqlI)
         mysqli_free_result($this->dbResult);
      else
         mysql_free_result($this->dbResult);
   }

   /**
    * fetch the next row from {@link $dbResult} as an associative array
    *
    * @return mixed
    * <ul>
    * <li> row values (array)
    * <li> false (boolean) - no more rows left in {@link $dbResult}
    * </ul>
    */
   public function fetchAssoc()
   {
      if ($this->isMySqlI)
         $row = mysqli_fetch_assoc($this->dbResult);
      else
         $row = mysql_fetch_assoc($this->dbResult);
      return ($row ? $row : false);
   }

   /**
    * fetch the next row from {@link $dbResult} as an array
    *
    * @return mixed
    * <ul>
    * <li> row values (array)
    * <li> false (boolean) - no more rows left in {@link $dbResult}
    * </ul>
    */
   public function fetchArray()
   {
      if ($this->isMySqlI)
         $row = mysqli_fetch_array($this->dbResult);
      else
         $row = mysql_fetch_array($this->dbResult);
      return ($row ? $row : false);
   }

   /**
    * insert row into table
    *
    * @param string table name
    * @param array an associative array of column name and value pairs
    */
   public function insert($table, $columns)
   {
      $sql_stat =
           "insert into $table (" . $this->columnList($columns) . ")"
         . "         values (" . $this->valueList($columns) . ")";

      $this->query($sql_stat);
   }

   /**
    * update row/s in table
    *
    * @param string table name
    * @param array an associative array of column name and value pairs to update
    * placed in the set clause of the SQL update statement
    * @param array an associative array of column name and value pairs to 
    * identify the rows to update placed in the where clause of the SQL update
    * statement
    * @param string addition free format string added to the end of the SQL
    * update statement
    */
   public function update($table, $columns, $key, $extra = "")
   {
      $sql_stat =
           "update $table"
         . "   set " . $this->nameValueList($columns)
         . " where " . $this->nameValueList($key, " and ");

      if ($extra) $sql_stat .= " " . $extra;

      $this->query($sql_stat);
   }

   /**
    * select row/s from table
    *
    * @param string table name
    * @param array an array of column names to retrieve
    * placed in the set clause of the SQL update statement
    * @param array an associative array of column name and value pairs to 
    * identify the rows to select placed in the where clause of the SQL update
    * statement
    * @param string addition free format string added to the end of the SQL
    * update statement
    */
   public function select($table, $columns, $key, $extra = "")
   {
      $sql_stat =
           "select " . implode(', ', $columns)
         . "  from $table"
         . " where " . $this->nameValueList($key, " and ");

      if ($extra) $sql_stat .= " " . $extra;

      $this->query($sql_stat, "Y");
   }

   /**
    * delete row/s from table
    *
    * @param string table name
    * @param array an associative array of column name and value pairs to 
    * identify the rows to select placed in the where clause of the SQL update
    * statement
    * @param string addition free format string added to the end of the SQL
    * update statement
    */
   public function delete($table, $key, $extra = "")
   {
      $sql_stat =
           "delete from $table"
         . " where " . $this->nameValueList($key, " and ");

      if ($extra) $sql_stat .= " " . $extra;

      $this->query($sql_stat);
   }

   /**
    * begin an SQL transaction
    */
   public function beginWork()
   {
      switch ($this->tableType) {
         case 'InnoDB':
            $this->query("set autocommit = 0");
            $this->query("begin");
            break;
         default:
            // Not used anymore was an attempt to perform transactions on MISAM
            // tables.  Would cause all page processing to be serial.
            $this->query("lock table DZE_SESSION write, DZE_SEQ write, DZE_USR_ATTRIB_ERR write, DZE_USR_FORM_ERR write, DZE_USR_ATTRIB write, DZE_PAGE write, DZE_FORM_IN_PAGE write, DZE_FORM write, DZE_ATTRIB_IN_FORM write, DZE_ATTRIB write, DZE_ATTRIB_RULE write, DZE_USR write, DZE_USR_IN_APP write, DZE_SLCT_OPTION write");
      }
   }

   /**
    * commit an SQL transaction
    */
   public function commitWork()
   {
      switch ($this->tableType) {
         case 'InnoDB':
            $this->query("commit");
            break;
         default:
            $this->query("unlock tables");
      }
   }

   /**
    * rollback an SQL transaction
    */
   public function rollbackWork()
   {
      if ($this->tableType == 'InnoDB')
         $this->query("rollback");
   }

   /**
    * retrieve a sequence value
    *
    * @param string name of sequence counter
    * @return integer - next sequence value
    * @throws -441, Sequence [Sequence Name] name not found
    */
   public function seqValue($seqName)
   {
      $this->pushResult();

      $this->query(
           "select VALUE, FIRST_VALUE, LAST_VALUE"
         . "  from DZE_SEQ"
         . " where SEQ_NAME = '" . $seqName . "' for update"
      );

      if ($this->isMySqlI)
         $row = mysqli_fetch_row($this->dbResult);
      else
         $row = mysql_fetch_row($this->dbResult);

      $this->freeResult();

      if (! $row) throw new Exception("Sequence '$seqName' name not found.", -441);

      list($retValue, $firstValue, $lastValue) = $row;

      $next_value = ($retValue == $lastValue) ? $firstValue : $retValue + 1;

      $this->query(
          "update DZE_SEQ"
         . "   set VALUE = " . $next_value
         . " where SEQ_NAME = '" . $seqName . "'"
      );

      $this->popResult();

      return $retValue;
   }
}
?>
Return current item: dizzyPages