Location: PHPKode > projects > Maintainable PHP Framework > vendor/Horde/Db/Adapter/Mysqli.php
<?php
/**
 * Copyright 2007 Maintainable Software, LLC
 * Copyright 2006-2008 The Horde Project (http://www.horde.org/)
 *
 * @author     Mike Naberezny <hide@address.com>
 * @author     Derek DeVries <hide@address.com>
 * @author     Chuck Hagenbuch <hide@address.com>
 * @license    http://opensource.org/licenses/bsd-license.php
 * @category   Horde
 * @package    Horde_Db
 * @subpackage Adapter
 */

/**
 * MySQL Improved Horde_Db_Adapter
 *
 * @author     Mike Naberezny <hide@address.com>
 * @author     Derek DeVries <hide@address.com>
 * @author     Chuck Hagenbuch <hide@address.com>
 * @license    http://opensource.org/licenses/bsd-license.php
 * @category   Horde
 * @package    Horde_Db
 * @subpackage Adapter
 */
class Horde_Db_Adapter_Mysqli extends Horde_Db_Adapter_Abstract
{
    /**
     * Mysqli database connection object.
     * @var mysqli
     */
    protected $_connection = null;

    /**
     * Last auto-generated insert_id
     * @var integer
     */
    protected $_insertId;

    /**
     * @var string
     */
    protected $_schemaClass = 'Horde_Db_Adapter_Mysql_Schema';


    /*##########################################################################
    # Public
    ##########################################################################*/

    /**
     * Returns the human-readable name of the adapter.  Use mixed case - one
     * can always use downcase if needed.
     *
     * @return  string
     */
    public function adapterName()
    {
        return 'MySQLi';
    }

    /**
     * Does this adapter support migrations?  Backend specific, as the
     * abstract adapter always returns +false+.
     *
     * @return  boolean
     */
    public function supportsMigrations()
    {
        return true;
    }


    /*##########################################################################
    # Connection Management
    ##########################################################################*/

    /**
     * Connect to the db
     *
     * MySQLi can connect using SSL if $config contains an 'ssl' sub-array
     * containing the following keys:
     *     + key      The path to the key file.
     *     + cert     The path to the certificate file.
     *     + ca       The path to the certificate authority file.
     *     + capath   The path to a directory that contains trusted SSL
     *                CA certificates in pem format.
     *     + cipher   The list of allowable ciphers for SSL encryption.
     *
     * Example of how to connect using SSL:
     * <code>
     * $config = array(
     *     'username' => 'someuser',
     *     'password' => 'apasswd',
     *     'hostspec' => 'localhost',
     *     'database' => 'thedb',
     *     'ssl'      => array(
     *         'key'      => 'client-key.pem',
     *         'cert'     => 'client-cert.pem',
     *         'ca'       => 'cacert.pem',
     *         'capath'   => '/path/to/ca/dir',
     *         'cipher'   => 'AES',
     *     ),
     * );
     *
     * $db = new Horde_Db_Adapter_Mysqli($config);
     * </code>
     */
    public function connect()
    {
        $config = $this->_parseConfig();

        if (!empty($config['ssl'])) {
            $mysqli = mysqli_init();
            $mysqli->ssl_set(
                empty($config['ssl']['key'])    ? null : $config['ssl']['key'],
                empty($config['ssl']['cert'])   ? null : $config['ssl']['cert'],
                empty($config['ssl']['ca'])     ? null : $config['ssl']['ca'],
                empty($config['ssl']['capath']) ? null : $config['ssl']['capath'],
                empty($config['ssl']['cipher']) ? null : $config['ssl']['cipher']
            );
            $mysqli->real_connect(
                $config['host'], $config['username'], $config['password'],
                $config['dbname'], $config['port'], $config['socket']);
        } else {
            $oldErrorReporting = error_reporting(0);
            $mysqli = new mysqli(
                $config['host'], $config['username'], $config['password'],
                $config['dbname'], $config['port'], $config['socket']);
            error_reporting($oldErrorReporting);
        }
        if (mysqli_connect_errno()) {
            throw new Horde_Db_Exception('Connect failed: (' . mysqli_connect_errno() . ') ' . mysqli_connect_error(), mysqli_connect_errno());
        }

        // If supported, request real datatypes from MySQL instead of returning
        // everything as a string.
        if (defined('MYSQLI_OPT_INT_AND_FLOAT_NATIVE'))
            $mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);

        $this->_connection = $mysqli;
        $this->_active     = true;
    }

    /**
     * Disconnect from db
     */
    public function disconnect()
    {
        if ($this->_connection)
            $this->_connection->close();
        $this->_connection = null;
        $this->_active = false;
    }

    /**
     * Check if the connection is active
     *
     * @return  boolean
     */
    public function isActive()
    {
        return isset($this->_connection) && $this->_connection->query('SELECT 1');
    }


    /*##########################################################################
    # Quoting
    ##########################################################################*/

    /**
     * Quotes a string, escaping any ' (single quote) and \ (backslash)
     * characters..
     *
     * @param   string  $string
     * @return  string
     */
    public function quoteString($string)
    {
        return "'".$this->_connection->real_escape_string($string)."'";
    }


    /*##########################################################################
    # Database Statements
    ##########################################################################*/

    /**
     * Returns an array of records with the column names as keys, and
     * column values as values.
     *
     * @param   string  $sql
     * @param   mixed   $arg1  Either an array of bound parameters or a query name.
     * @param   string  $arg2  If $arg1 contains bound parameters, the query name.
     * @return  array
     */
    public function select($sql, $arg1=null, $arg2=null)
    {
        return new Horde_Db_Adapter_Mysqli_Result($this, $sql, $arg1, $arg2);
    }

    /**
     * Returns an array of record hashes with the column names as keys and
     * column values as values.
     *
     * @param   string  $sql
     * @param   mixed   $arg1  Either an array of bound parameters or a query name.
     * @param   string  $arg2  If $arg1 contains bound parameters, the query name.
     */
    public function selectAll($sql, $arg1=null, $arg2=null)
    {
        $result = $this->execute($sql, $arg1, $arg2);
        $rows = array();
        if ($result) {
            while ($row = $result->fetch_array()) {
                $rows[] = $row;
            }
        }
        return $rows;
    }

    /**
     * Returns a record hash with the column names as keys and column values
     * as values.
     *
     * @param   string  $sql
     * @param   mixed   $arg1  Either an array of bound parameters or a query name.
     * @param   string  $arg2  If $arg1 contains bound parameters, the query name.
     * @return  array
     */
    public function selectOne($sql, $arg1=null, $arg2=null)
    {
        $result = $this->execute($sql, $arg1, $arg2);
        return $result ? $result->fetch_array() : array();
    }

    /**
     * Returns a single value from a record
     *
     * @param   string  $sql
     * @param   mixed   $arg1  Either an array of bound parameters or a query name.
     * @param   string  $arg2  If $arg1 contains bound parameters, the query name.
     * @return  string
     */
    public function selectValue($sql, $arg1=null, $arg2=null)
    {
        $result = $this->selectOne($sql, $arg1, $arg2);
        return $result ? current($result) : null;
    }

    /**
     * Returns an array of the values of the first column in a select:
     *   select_values("SELECT id FROM companies LIMIT 3") => [1,2,3]
     *
     * @param   string  $sql
     * @param   mixed   $arg1  Either an array of bound parameters or a query name.
     * @param   string  $arg2  If $arg1 contains bound parameters, the query name.
     */
    public function selectValues($sql, $arg1=null, $arg2=null)
    {
        $values = array();
        $result = $this->execute($sql, $arg1, $arg2);
        if ($result) {
            while ($row = $result->fetch_row()) {
                $values[] = $row[0];
            }
        }
        return $values;
    }

    /**
     * Executes the SQL statement in the context of this connection.
     *
     * @param   string  $sql
     * @param   mixed   $arg1  Either an array of bound parameters or a query name.
     * @param   string  $arg2  If $arg1 contains bound parameters, the query name.
     */
    public function execute($sql, $arg1=null, $arg2=null)
    {
        if (is_array($arg1)) {
            $sql = $this->_replaceParameters($sql, $arg1);
            $name = $arg2;
        } else {
            $name = $arg1;
        }

        $t = new Horde_Support_Timer;
        $t->push();

        $stmt = $this->_connection->query($sql);
        if (!$stmt) {
            $this->_logInfo($sql, 'QUERY FAILED: ' . $this->_connection->error);
            $this->_logInfo($sql, $name);
            throw new Horde_Db_Exception('QUERY FAILED: ' . $this->_connection->error . "\n\n" . $sql,
                                         $this->_errorCode($this->_connection->sqlstate, $this->_connection->errno));
        }

        $this->_logInfo($sql, $name, $t->pop());
        //@TODO if ($this->_connection->info) $this->_loginfo($sql, $this->_connection->info);
        //@TODO also log warnings? http://php.net/mysqli.warning-count and http://php.net/mysqli.get-warnings

        $this->_rowCount = $this->_connection->affected_rows;
        $this->_insertId = $this->_connection->insert_id;
        return $stmt;
    }

    /**
     * Returns the last auto-generated ID from the affected table.
     *
     * @param   string  $sql
     * @param   mixed   $arg1  Either an array of bound parameters or a query name.
     * @param   string  $arg2  If $arg1 contains bound parameters, the query name.
     * @param   string  $pk
     * @param   int     $idValue
     * @param   string  $sequenceName
     */
    public function insert($sql, $arg1=null, $arg2=null, $pk=null, $idValue=null, $sequenceName=null)
    {
        $this->execute($sql, $arg1, $arg2);
        return isset($idValue) ? $idValue : $this->_insertId;
    }

    /**
     * Begins the transaction (and turns off auto-committing).
     */
    public function beginDbTransaction()
    {
        $this->_transactionStarted = true;
        $this->_connection->autocommit(false);
    }

    /**
     * Appends +LIMIT+ and +OFFSET+ options to a SQL statement.
     *
     * @param   string  $sql
     * @param   array   $options
     * @return  string
     */
    public function addLimitOffset($sql, $options)
    {
        if (isset($options['limit']) && $limit = $options['limit']) {
            if (isset($options['offset']) && $offset = $options['offset']) {
                $sql .= " LIMIT $offset, $limit";
            } else {
                $sql .= " LIMIT $limit";
            }
        }
        return $sql;
    }


    /*##########################################################################
    # Protected
    ##########################################################################*/

    /**
     * Return a standard error code
     *
     * @param   string   $sqlstate
     * @param   integer  $errno
     * @return  integer
     */
    protected function _errorCode($sqlstate, $errno)
    {
        /*@TODO do something with standard sqlstate vs. MySQL error codes vs. whatever else*/
        return $errno;
    }

    /**
     * Parse configuration array into options for PDO constructor.
     *
     * @throws  Horde_Db_Exception
     * @return  array  [host, username, password, dbname, port, socket]
     */
    protected function _parseConfig()
    {
        // check required config keys are present
        $required = array('username');
        $diff = array_diff_key(array_flip($required), $this->_config);
        if (! empty($diff)) {
            $msg = 'Required config missing: ' . implode(', ', array_keys($diff));
            throw new Horde_Db_Exception($msg);
        }

        if (isset($this->_config['port'])) {
            if (empty($this->_config['host'])) {
                $msg = 'host is required if port is specified';
                throw new Horde_Db_Exception($msg);
            }
        }

        $config = $this->_config;

        if (!isset($config['host']))      $config['host'] = null;
        if (!isset($config['username']))  $config['username'] = null;
        if (!isset($config['password']))  $config['password'] = null;
        if (!isset($config['dbname']))    $config['dbname'] = null;
        if (!isset($config['port']))      $config['port'] = null;
        if (!isset($config['socket']))    $config['socket'] = null;

        return $config;
    }

}
Return current item: Maintainable PHP Framework