bid/library/Cube/Db/Adapter/PDO/Mysql.php

<?php

/**
 *
 * Cube Framework $Id$ d2ibV6IrsImXE7ik3/w9UNhhd5mwvaSQ8uOcH9btDJo=
 *
 * @link        http://codecu.be/framework
 * @copyright   Copyright (c) 2014 CodeCube SRL
 * @license     http://codecu.be/framework/license Commercial License
 *
 * @version     1.0
 */
/**
 * pdo mysql database adapter
 */

namespace Cube\Db\Adapter\PDO;

use Cube\Db\Adapter\AbstractAdapter,
    Cube\Db\Select,
    Cube\Exception;

class Mysql extends AbstractAdapter
{

    /**
     *
     * default statement class for this adapter
     *
     * @var string
     */
    protected $_defaultStmtClass = '\\Cube\\Db\\Statement\\Pdo';

    /**
     *
     * adapter type
     *
     * @var string
     */
    protected $_pdoType = 'mysql';

    /**
     * Creates a PDO DSN for the adapter from $this->_config settings.
     *
     * @return string
     */
    protected function _dsn()
    {
        // baseline of DSN parts
        $dsn = $this->_config;

        // don't pass the username, password, charset, persistent and driver_options in the DSN
        unset($dsn['username']);
        unset($dsn['password']);
        unset($dsn['options']);
        unset($dsn['charset']);
        unset($dsn['persistent']);
        unset($dsn['driver_options']);

        // use all remaining parts in the DSN
        foreach ($dsn as $key => $val) {
            $dsn[$key] = "$key=$val";
        }

        $dsn = implode(';', $dsn);

        if (isset($this->_config['charset'])) {
            $dsn .= ';charset=' . (string)$this->_config['charset'];
        }

        return $this->_pdoType . ':' . $dsn;
    }

    /**
     * Creates a PDO object and connects to the database.
     *
     * @throws \RuntimeException
     * @throws \Cube\Exception
     * @return void
     */
    protected function _connect()
    {
        if ($this->_connection) {
            return;
        }

        if (isset($this->_config['charset'])) {
            $initCommand = "SET NAMES '" . $this->_config['charset'] . "'";
            $this->_config['driver_options'][1002] = $initCommand; // 1002 = PDO::MYSQL_ATTR_INIT_COMMAND
        }

        // get the dsn first, because some adapters alter the $_pdoType
        $dsn = $this->_dsn();

        // check for PDO extension
        if (!extension_loaded('pdo')) {
            /**
             * @see Zend_Db_Adapter_Exception
             */
            throw new \RuntimeException('The PDO extension is required for this adapter but the extension is not loaded');
        }

        // create PDO connection
        try {
            $this->_connection = @new \PDO(
                $dsn,
                $this->_config['username'],
                $this->_config['password'],
                $this->_config['driver_options']
            );

            $this->_connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        } catch (\PDOException $e) {
            throw new Exception($e->getMessage(), $e->getCode(), $e);
        }
    }

    /**
     * Test if a connection is active
     *
     * @return bool
     */
    public function isConnected()
    {
        return ((bool)($this->_connection instanceof \PDO));
    }

    /**
     * connect and return whether the connection was successful
     *
     * @return bool
     */
    public function canConnect()
    {
        $this->_connect();

        return $this->isConnected();
    }

    /**
     * Force the connection to close.
     *
     * @return void
     */
    public function closeConnection()
    {
        $this->_connection = null;
    }

    /**
     * Prepares an SQL statement.
     *
     * @param string $sql The SQL statement with placeholders.
     *
     * @throws \RuntimeException
     * @return \PDOStatement
     */
    public function prepare($sql)
    {
        $this->_connect();
        $stmtClass = $this->_defaultStmtClass;
        if (!class_exists($stmtClass)) {
            throw new \RuntimeException(
                sprintf("Could not load the statement class '%s'", $stmtClass));
        }

        /** @var \Cube\Db\Statement\AbstractStatement $stmt */
        $stmt = new $stmtClass($this, $sql);
        $stmt->setFetchMode($this->_fetchMode);

        return $stmt;
    }

    /**
     * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
     *
     * As a convention, on RDBMS brands that support sequences
     * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence
     * from the arguments and returns the last id generated by that sequence.
     * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
     * returns the last value generated for such a column, and the table name
     * argument is disregarded.
     *
     * On RDBMS brands that don't support sequences, $tableName and $primaryKey
     * are ignored.
     *
     * @param string $tableName  OPTIONAL Name of table.
     * @param string $primaryKey OPTIONAL Name of primary key column.
     *
     * @return string
     */
    public function lastInsertId($tableName = null, $primaryKey = null)
    {
        $this->_connect();

        return $this->_connection->lastInsertId();
    }

    /**
     * Special handling for PDO query().
     * All bind parameter names must begin with ':'
     *
     * @param string|\Cube\Db\Select $sql  The SQL statement with placeholders.
     * @param array                  $bind An array of data to bind to the placeholders.
     *
     * @return \Cube\Db\Adapter\AbstractAdapter
     * @throws \Cube\Exception To re-throw PDOException.
     */
    public function query($sql, $bind = array())
    {
        if (empty($bind) && $sql instanceof Select) {
            $bind = $sql->getBind();
        }

        if (is_array($bind)) {
            foreach ($bind as $name => $value) {
                if (!is_int($name) && !preg_match('/^:/', $name)) {
                    $newName = ":$name";
                    unset($bind[$name]);
                    $bind[$newName] = $value;
                }
            }
        }

        try {
            return parent::query($sql, $bind);
        } catch (\PDOException $e) {
            throw new Exception($e->getMessage(), $e->getCode(), $e);
        }
    }

    /**
     * Executes an SQL statement and return the number of affected rows
     *
     * @param  mixed $sql   The SQL statement with placeholders.
     *                      May be a string or a \Cube\Db\Select object.
     *
     * @throws \Cube\Exception
     * @return integer      Number of rows that were modified
     *                      or deleted by the SQL statement
     */
    public function exec($sql)
    {
        if ($sql instanceof Select) {
            $sql = $sql->assemble();
        }

        try {
            $affected = $this->getConnection()->exec($sql);

            if ($affected === false) {
                $errorInfo = $this->getConnection()->errorInfo();
                throw new Exception($errorInfo[2]);
            }

            return $affected;
        } catch (\PDOException $e) {
            throw new Exception($e->getMessage(), $e->getCode(), $e);
        }
    }

    /**
     * Quote a raw string.
     *
     * @param string $value Raw string
     *
     * @return string           Quoted string
     */
    protected function _quote($value)
    {
        if (is_int($value) || is_float($value)) {
            return $value;
        }
        $this->_connect();

        return $this->_connection->quote($value);
    }

    /**
     * Begin a transaction.
     */
    protected function _beginTransaction()
    {
        $this->_connect();
        $this->_connection->beginTransaction();
    }

    /**
     * Commit a transaction.
     */
    protected function _commit()
    {
        $this->_connect();
        $this->_connection->commit();
    }

    /**
     * Roll-back a transaction.
     */
    protected function _rollBack()
    {
        $this->_connect();
        $this->_connection->rollBack();
    }

    /**
     * Set the PDO fetch mode.
     *
     * @param int $mode A PDO fetch mode.
     *
     * @throws \RuntimeException
     * @throws \InvalidArgumentException
     * @return void
     */
    public function setFetchMode($mode)
    {
        //check for PDO extension
        if (!extension_loaded('pdo')) {
            throw new \RuntimeException('The PDO extension is required for this adapter but the extension is not loaded');
        }
        switch ($mode) {
            case \PDO::FETCH_LAZY:
            case \PDO::FETCH_ASSOC:
            case \PDO::FETCH_NUM:
            case \PDO::FETCH_BOTH:
            case \PDO::FETCH_NAMED:
            case \PDO::FETCH_OBJ:
                $this->_fetchMode = $mode;
                break;
            default:
                throw new \InvalidArgumentException("Invalid fetch mode '$mode' specified");
                break;
        }
    }

    /**
     * Check if the adapter supports real SQL parameters.
     *
     * @param string $type 'positional' or 'named'
     *
     * @return bool
     */
    public function supportsParameters($type)
    {
        switch ($type) {
            case 'positional':
            case 'named':
            default:
                return true;
        }
    }

    /**
     * @return string
     */
    public function getQuoteIdentifierSymbol()
    {
        return "`";
    }

    /**
     * Returns the column descriptions for a table.
     *
     * The return value is an associative array keyed by the column name,
     * as returned by the RDBMS.
     *
     * The value of each array element is an associative array
     * with the following keys:
     *
     * TABLE_NAME       => string;
     * COLUMN_NAME      => string; column name
     * COLUMN_POSITION  => number; ordinal position of column in table
     * DATA_TYPE        => string; SQL datatype name of column
     * DEFAULT          => string; default expression of column, null if none
     * NULLABLE         => bool; true if column can have nulls
     * LENGTH           => number; length of CHAR/VARCHAR
     * SCALE            => number; scale of NUMERIC/DECIMAL
     * PRECISION        => number; precision of NUMERIC/DECIMAL
     * UNSIGNED         => bool; unsigned property of an integer type
     * PRIMARY          => bool; true if column is part of the primary key
     * PRIMARY_POSITION => integer; position of column in primary key
     * IDENTITY         => integer; true if column is auto-generated with unique values
     *
     * @param string $tableName
     *
     * @return array
     */
    public function describeTable($tableName)
    {

        $sql = 'DESCRIBE ' . $this->quoteIdentifier($tableName, true);

        $stmt = $this->query($sql);

        // Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
        $result = $stmt->fetchAll(\PDO::FETCH_NUM);

        $field = 0;
        $type = 1;
        $null = 2;
        $key = 3;
        $default = 4;
        $extra = 5;

        $desc = array();
        $i = 1;
        $p = 1;

        foreach ($result as $row) {
            list($length, $scale, $precision, $unsigned, $primary, $primaryPosition, $identity)
                = array(null, null, null, null, false, null, false);
            if (preg_match('/unsigned/', $row[$type])) {
                $unsigned = true;
            }
            if (preg_match('/^((?:var)?char)\((\d+)\)/', $row[$type], $matches)) {
                $row[$type] = $matches[1];
                $length = $matches[2];
            }
            else if (preg_match('/^decimal\((\d+),(\d+)\)/', $row[$type], $matches)) {
                $row[$type] = 'decimal';
                $precision = $matches[1];
                $scale = $matches[2];
            }
            else if (preg_match('/^float\((\d+),(\d+)\)/', $row[$type], $matches)) {
                $row[$type] = 'float';
                $precision = $matches[1];
                $scale = $matches[2];
            }
            else if (preg_match('/^((?:big|medium|small|tiny)?int)\((\d+)\)/', $row[$type], $matches)) {
                $row[$type] = $matches[1];
            }

            if (strtoupper($row[$key]) == 'PRI') {
                $primary = true;
                $primaryPosition = $p;
                if ($row[$extra] == 'auto_increment') {
                    $identity = true;
                }
                else {
                    $identity = false;
                }
                $p++;
            }

            $desc[$row[$field]] = array(
                'TABLE_NAME'       => $tableName,
                'COLUMN_NAME'      => $row[$field],
                'COLUMN_POSITION'  => $i,
                'DATA_TYPE'        => $row[$type],
                'DEFAULT'          => $row[$default],
                'NULLABLE'         => (bool)($row[$null] == 'YES'),
                'LENGTH'           => $length,
                'SCALE'            => $scale,
                'PRECISION'        => $precision,
                'UNSIGNED'         => $unsigned,
                'PRIMARY'          => $primary,
                'PRIMARY_POSITION' => $primaryPosition,
                'IDENTITY'         => $identity
            );

            $i++;
        }

        return $desc;
    }

    /**
     * Adds an adapter-specific LIMIT clause to the SELECT statement.
     *
     * @param  string  $sql
     * @param  integer $count
     * @param  integer $offset OPTIONAL
     *
     * @throws \InvalidArgumentException
     * @return string
     */
    public function limit($sql, $count, $offset = 0)
    {
        $count = intval($count);
        if ($count <= 0) {
            /** @see Zend_Db_Adapter_Exception */
            throw new \InvalidArgumentException("LIMIT argument count=$count is not valid");
        }

        $offset = intval($offset);
        if ($offset < 0) {
            throw new \InvalidArgumentException("LIMIT argument offset=$offset is not valid");
        }

        $sql .= " LIMIT $count";
        if ($offset > 0) {
            $sql .= " OFFSET $offset";
        }

        return $sql;
    }

}