Database::showIndex() - Code Metrics - Inspection of "Add SHOW INDEX support" - symphonycms/symphony-2 - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed
Push — 3.0.x ( 7946ec...895393 )

by Nicolas
created 2018-05-14 19:52 UTC
Database::showIndex() A

↳ Parent: Project
Complexity

Conditions	1
Paths	1
Size

Total Lines	3
Code Lines	1
Duplication

Lines	0
Ratio	0 %
Importance

Changes
Metric	Value
cc	1
eloc	1
c	0
b	0
f	0
nc	1
nop	0
dl	0
loc	3
rs	10
<?php

/**
 * @package toolkit
 */

/**
 * The Database class acts as a wrapper for connecting to the Database
 * in Symphony.
 *
 * It provides many methods that maps directly to their PDO equivalent.
 * It also provides many factory methods to help developers creates instances
 * of `DatabaseStatement` and their specialized child classes.
 *
 * Symphony uses a prefix to namespace it's tables in a
 * database, allowing it play nice with other applications installed on the
 * database.
 *
 * An error that occur during a query throw a `DatabaseException`.
 * By default, Symphony logs all queries to be used for Profiling and Debug
 * devkit extensions when a Developer is logged in. When a developer is not
 * logged in, all queries and errors are made available with delegates.
 */
class Database
{
    /**
     * Constant to indicate whether the query is a write operation.
     *
     * @deprecated @since Symphony 3.0.0
     * @var int
     */
    const __WRITE_OPERATION__ = 0;

    /**
     * Constant to indicate whether the query is a write operation
     *
     * @deprecated @since Symphony 3.0.0
     * @var int
     */
    const __READ_OPERATION__ = 1;

    /**
     * An instance of the current PDO object
     *
     * @var PDO
     */
    private $conn = null;

    /**
     * The array of log messages
     *
     * @var array
     */
    private $log = [];

    /**
     * The number of queries this class has executed, defaults to 0.
     *
     * @var int
     */
    private $queryCount = 0;

    /**
     * The default configuration values
     *
     * @var array
     */
    private $config = [
        'host' => null,
        'port' => null,
        'user' => null,
        'password' => null,
        'db' => null,
        'driver' => null,
        'charset' => null,
        'collate' => null,
        'engine' => null,
        'tbl_prefix' => null,
        'query_caching' => null,
        'query_logging' => null,
        'options' => [],
    ];

    /**
     * The last executed query
     * @var string;
     */
    private $lastQuery;

    /**
     * The md5 hash of the last executed query
     * @var string;
     */
    private $lastQueryHash;

    /**
     * The values used with the last executed query
     * @var array
     */
    private $lastQueryValues;

    /**
     * The unsafe mode of the last executed query
     * @var bool
     */
    private $lastQuerySafe;

    /**
     * The version of the SQL server
     * @var string
     */
    private $version;

    /**
     * Creates a new Database object given an associative array of configuration
     * parameters in `$config`, which should include
     * `driver`, `host`, `port`, `user`, `password`, `db` and an optional
     * array of PDO options in `options`.
     *
     * @param array $config
     */
    public function __construct(array $config = [])
    {
        $this->config = array_merge($this->config, $config);
    }

    /**
     * Magic function that will flush the logs and close the underlying database
     * connection when the Database class is destroyed.
     *
     * @link http://php.net/manual/en/language.oop5.decon.php
     */
    public function __destruct()
    {
        unset($this->conn);
        $this->flush();
    }

    /**
     * Getter for all the log entries.
     *
     * @return array
     */
    public function getLogs()
    {
        return $this->log;
    }

    /**
     * Resets `$this->lastQuery`, `$this->lastQueryHash`, `$this->lastQueryValues` and
     * `$this->lastQuerySafe` to their empty values.
     * Called on each query and when the class is destroyed.
     *
     * @return Database
     *  The current instance.
     */
    public function flush()
    {
        $this->lastQuery = null;
        $this->lastQueryHash = null;
        $this->lastQueryValues = null;
        $this->lastQuerySafe = null;
        return $this;
    }

    /**
     * Based on the configuration values set in the constructor,
     * this method will properly format the values to get a valid DSN
     * connection string.
     *
     * @return string
     *  The generated DNS connection string
     */
    public function getDSN()
    {
        $config = &$this->config;
        if ($config['host'] === 'unix_socket') {
           return sprintf(
                '%s:unix_socket=%s;dbname=%s;charset=%s',
                $config['driver'],
                General::intval($config['port']) === -1 ? $config['port'] : '',
                $config['db'],
                $config['charset']
            );
        }
        return sprintf(
            '%s:dbname=%s;host=%s;port=%d;charset=%s',
            $config['driver'],
            $config['db'],
            $config['host'],
            General::intval($config['port']),
            $config['charset']
        );
    }

    /**
     * Getter for the version of the SQL server.
     *
     * @return string
     */
    public function getVersion()
    {
        return $this->version;
    }

    /**
     * Creates a PDO connection to the desired database given the current config.
     * This will also set the error mode to be exceptions,
     * which are handled by this class.
     *
     * @link http://www.php.net/manual/en/pdo.drivers.php
     * @param array $options
     * @return Database
     *  The current instance if connection was successful.
     * @throws DatabaseException
     */
    public function connect()
    {
        try {
            $config = $this->config;
            $this->conn = new PDO(
                $this->getDSN(),
                $config['user'],
                $config['password'],
                is_array($config['options']) ? $config['options'] : []
            );
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
            $this->version = $this->select(['VERSION()'])->execute()->variable(0);
        } catch (PDOException $ex) {
            $this->throwDatabaseError($ex);
        }

        return $this;
    }

    /**
     * Checks if the connection was already made successfully.
     *
     * @return boolean
     *  true if the connection was made, false otherwise
     */
    public function isConnected()
    {
        return $this->conn && $this->conn instanceof PDO;
    }

    /**
     * Issues a call to `connect()` if the current instance is not already
     * connected. Does nothing if already connected.
     *
     * @see isConnected()
     * @return Database
     *  The current instance.
     * @throws DatabaseException
     */
    private function autoConnect()
    {
        if (!$this->isConnected()) {
            $this->connect();
        }
        return $this;
    }

    /**
     * Returns the number of queries that has been executed since
     * the creation of the object.
     *
     * @return int
     *  The total number of query executed.
     */
    public function queryCount()
    {
        return $this->queryCount;
    }

    /**
     * Sets query caching to true. This will prepend all SELECT
     * queries with SQL_CACHE. Symphony by default enables caching. It
     * can be turned off by setting the query_cache parameter to 'off' in the
     * Symphony config file.
     *
     * @link https://dev.mysql.com/doc/refman/5.1/en/query-cache.html
     * @deprecated The query cache is deprecated as of MySQL 5.7.20,
     * and is removed in MySQL 8.0.
     * @link https://dev.mysql.com/doc/refman/5.7/en/query-cache-in-select.html
     * @return Database
     *  The current instance
     */
    public function enableCaching()
    {
        $this->config['query_caching'] = true;
        return $this;
    }

    /**
     * Sets query caching to false. This will prepend all SELECT
     * queries will SQL_NO_CACHE.
     *
     * @deprecated The query cache is deprecated as of MySQL 5.7.20,
     * and is removed in MySQL 8.0.
     * @link https://dev.mysql.com/doc/refman/5.7/en/query-cache-in-select.html
     * @return Database
     *  The current instance
     */
    public function disableCaching()
    {
        $this->config['query_caching'] = false;
        return $this;
    }

    /**
     * Returns boolean if query caching is enabled or not.
     *
     * @deprecated The query cache is deprecated as of MySQL 5.7.20,
     * and is removed in MySQL 8.0.
     * @link https://dev.mysql.com/doc/refman/5.7/en/query-cache-in-select.html
     * @return boolean
     */
    public function isCachingEnabled()
    {
        return in_array($this->config['query_caching'], ['on', true], true);
    }

    /**
     * Symphony uses a prefix for all it's database tables so it can live peacefully
     * on the same database as other applications. By default this is sym_, but it
     * can be changed when Symphony is installed.
     *
     * @param string $prefix
     *  The table prefix for Symphony, by default this is sym_
     * @return Database
     *  The current instance
     */
    public function setPrefix($prefix)
    {
        $this->config['tbl_prefix'] = $prefix;
        return $this;
    }

    /**
     * Returns the prefix used by Symphony for this Database instance.
     *
     * @see __construct()
     * @since Symphony 2.4
     * @return string
     */
    public function getPrefix()
    {
        return $this->config['tbl_prefix'];
    }

    /**
     * Sets query logging to true.
     *
     * @return Database
     *  The current instance
     */
    public function enableLogging()
    {
        $this->config['query_logging'] = true;
        return $this;
    }

    /**
     * Sets query logging to false.
     *
     * @return Database
     *  The current instance
     */
    public function disableLogging()
    {
        $this->config['query_logging'] = false;
        return $this;
    }

    /**
     * Returns true if logging of queries is enabled.
     *
     * @return boolean
     */
    public function isLoggingEnabled()
    {
        return in_array($this->config['query_logging'], ['on', true], true);
    }

    /**
     * Sets the Database connection to use this timezone instead of the default
     * Database server timezone.
     *
     * @throws DatabaseException
     * @link https://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html
     * @link https://github.com/symphonycms/symphony-2/issues/1726
     * @since Symphony 2.3.3
     * @param string $timezone
     *  PHP's Human readable timezone, such as Australia/Brisbane.
     * @return boolean
     */
    public function setTimeZone($timezone = null)
    {
        // This should throw, default value should be removed
        if (!$timezone) {
            return true;
        }

        // What is the time now in the install timezone
        $symphony_date = new DateTime('now', new DateTimeZone($timezone));

        // MySQL wants the offset to be in the format +/-H:I, getOffset returns offset in seconds
        $utc = new DateTime('now ' . $symphony_date->getOffset() . ' seconds', new DateTimeZone("UTC"));

        // Get the difference between the symphony install timezone and UTC
        $offset = $symphony_date->diff($utc)->format('%R%H:%I');

        return $this->set('time_zone')
                    ->value((string)$offset)
                    ->execute()
                    ->success();
    }

    /**
     * This function will clean a string using the `PDO::quote` function
     * taking into account the current database character encoding.
     *
     * If not connected to the database, it will default to PHP's `addslashes`.
     * This is useful for unit tests.
     *
     * This function does not encode _ or %.
     *
     * This function should not be used. Instead, pass your data in the proper
     * function that will delegate to SQL parameters.
     *
     * @deprecated @since Symphony 3.0.0
     * @see DatabaseStatement::appendValues()
     * @param string $value
     *  The string to be encoded into an escaped SQL string
     * @return string
     *  The escaped SQL string
     */
    public function quote($value)
    {
        if (!$this->isConnected()) {
            return "'" . addslashes($value) . "'";
        }
        return $this->conn->quote($value);
    }

    /**
     * This function will apply the `quote` function to an associative
     * array of data, encoding only the value, not the key. This function
     * can handle recursive arrays. This function manipulates the given
     * parameter by reference.
     *
     * This function should not be used. Instead, pass your data in the proper
     * function that will delegate to SQL parameters.
     *
     * @deprecated @since Symphony 3.0.0
     * @see quote
     * @param array $array
     *  The associative array of data to encode, this parameter is manipulated
     *  by reference.
     */
    public function quoteFields(array &$array)
    {
        foreach ($array as $key => $val) {
            // Handle arrays with more than 1 level
            if (is_array($val)) {
                $this->quoteFields($val);
                /** @scrutinizer ignore-deprecated */ $this->quoteFields($val);
            } elseif (!$val || strlen(trim($val)) === 0) {
                $array[$key] = 'NULL';
            } else {
                $array[$key] = $this->quote($val);
                $array[$key] = /** @scrutinizer ignore-deprecated */ $this->quote($val);
            }
        }
    }

    /**
     * This function takes `$table` and `$field` names and returns true
     * if the `$table` contains a column named `$field`.
     *
     * @since Symphony 2.3
     * @see describe
     * @link  https://dev.mysql.com/doc/refman/en/describe.html
     * @param string $table
     *  The table name
     * @param string $field
     *  The field name
     * @throws DatabaseException
     * @return boolean
     *  true if `$table` contains `$field`, false otherwise
     */
    public function tableContainsField($table, $field)
    {
        return $this->describe($table)
                    ->field($field)
                    ->execute()
                    ->next() !== null;
    }

    /**
     * This function takes `$table` and returns boolean
     * if it exists or not.
     *
     * @since Symphony 2.3.4
     * @see show
     * @link  https://dev.mysql.com/doc/refman/en/show-tables.html
     * @param string $table
     *  The table name
     * @throws DatabaseException
     * @return boolean
     *  true if `$table` exists, false otherwise
     */
    public function tableExists($table)
    {
        return $this->show()
                    ->like($table)
                    ->execute()
                    ->next() !== null;
    }

    /**
     * Factory method that creates a new, empty statement.
     *
     * @param string $action
     *  The SQL clause name. Default to empty string.
     * @return DatabaseStatement
     */
    public function statement($action = '')
    {
        return new DatabaseStatement($this, $action);
    }

    /**
     * Factory method that creates a new `SELECT ...` statement.
     *
     * @param array $projection
     *  The columns to select.
     *  If no projection gets added, it defaults to `DatabaseQuery::getDefaultProjection()`.
     * @return DatabaseQuery
     */
    public function select(array $projection = [])
    {
        return new DatabaseQuery($this, $projection);
    }

    /**
     * Factory method that creates a new `SHOW TABLES` statement.
     *
     * @return DatabaseShow
     */
    public function show()
    {
        return new DatabaseShow($this);
    }

    /**
     * Factory method that creates a new `SHOW COLUMNS` statement.
     *
     * @return DatabaseShow
     */
    public function showColumns()
    {
        return new DatabaseShow($this, 'COLUMNS');
    }

    /**
     * Factory method that creates a new `SHOW INDEX` statement.
     *
     * @return DatabaseShow
     */
    public function showIndex()
    {
        return new DatabaseShow($this, 'INDEX');
    }

    /**
     * Factory method that creates a new `INSERT` statement.
     *
     * @param string $table
     *  The name of the table to act on, including the tbl prefix which will be changed
     *  to the Database table prefix.
     *  @deprecated Symphony 3.0.0
     *  If $table is an array, it is treated as the fields values
     *  Use DatabaseInsert::values()
     * @param string $table
     *  The name of the table to act on, including the tbl prefix which will be changed
     *  to the Database table prefix.
     *  @deprecated Symphony 3.0.0
     *  This parameter is deprecated and will be removed.
     *  Use the first parameter and DatabaseInsert::values()
     * @param bool $updateOnDuplicate
     *  If set to true, data will updated if any key constraints are found that cause
     *  conflicts. Defaults to false
     *  @deprecated Symphony 3.0.0
     *  This parameter is deprecated and will be removed.
     *  Use DatabaseInsert::updateOnDuplicateKey()
     * @return DatabaseInsert
     */
    public function insert($table, ...$oldParams)
    {
        // Compat layer
        if (is_array($table)) {

            if (isset($oldParams[0]) && isset($oldParams[1])) {
                return $this->_insert($table, $oldParams[0], $oldParams[1]);
            }
            return $this->_insert($table, $oldParams[0]);
        }
        return new DatabaseInsert($this, $table);
    }

    /**
     * Returns the last insert ID from the previous query. This is
     * the value from an auto_increment field.
     * If the lastInsertId is empty or not a valid integer, -1 is returned.
     *
     * @return int
     *  The last interested row's ID
     */
    public function getInsertID()
    {
        return General::intval($this->conn->lastInsertId());
    }

    /**
     * Factory method that creates a new `UPDATE` statement.
     *
     * @param string $table
     *  The name of the table to act on, including the tbl prefix which will be changed
     *  to the Database table prefix.
     * @param string $where
     *  An unsanitized WHERE condition.
     *  @deprecated Symphony 3.0.0
     *  This parameter is deprecated and will be removed.
     *  Use DatabaseUpdate::where()
     * @return DatabaseUpdate
     */
    public function update($table, ...$oldParams)
    {
        // Compat layer
        if (is_array($table)) {

            if (isset($oldParams[0]) && isset($oldParams[1])) {
                return $this->_update($table, $oldParams[0], $oldParams[1]);
            }
            return $this->_update($table, $oldParams[0]);
        }
        return new DatabaseUpdate($this, $table);
    }

    /**
     * Factory method that creates a new `DELETE` statement.
     *
     * @param string $table
     *  The name of the table to act on, including the tbl prefix which will be changed
     *  to the Database table prefix.
     * @param string $where
     *  An unsanitized WHERE condition.
     *  @deprecated Symphony 3.0.0
     *  This parameter is deprecated and will be removed.
     *  Use DatabaseDelete::where()
     * @return DatabaseDelete
     */
    public function delete($table, $where = null)
    {
        $stm = new DatabaseDelete($this, $table);
        // Compat layer
        if ($where) {
            $where = $stm->replaceTablePrefix($where);
            $stm->unsafe()->unsafeAppendSQLPart('where', "WHERE $where");
            return $stm->execute()->success();

        }
        return $stm;
    }

    /**
     * Factory method that creates a new `DROP` statement.
     *
     * @param string $table
     * @return DatabaseDrop
     */
    public function drop($table)
    {
        return new DatabaseDrop($this, $table);
    }

    /**
     * Factory method that creates a new `DESCRIBE` statement.
     *
     * @param string $table
     * @return DatabaseDescribe
     */
    public function describe($table)
    {
        return new DatabaseDescribe($this, $table);
    }

    /**
     * Factory method that creates a new `CREATE TABLE` statement.
     * Also sets the charset, collate and engine values using the
     * instance configuration.
     *
     * @param string $table
     * @return DatabaseCreate
     */
    public function create($table)
    {
        return (new DatabaseCreate($this, $table))
            ->charset($this->config['charset'])
            ->collate($this->config['collate'])
            ->engine($this->config['engine']);
    }

    /**
     * Factory method that creates a new `ALTER TABLE` statement.
     * Also sets the collate value using the instance configuration.
     *
     * @param string $table
     * @return DatabaseAlter
     */
    public function alter($table)
    {
        return (new DatabaseAlter($this, $table))
            ->collate($this->config['collate']);
    }

    /**
     * Factory method that creates a new `OPTIMIZE TABLE` statement.
     *
     * @param string $table
     * @return DatabaseOptimize
     */
    public function optimize($table)
    {
        return new DatabaseOptimize($this, $table);
    }

    /**
     * Factory method that creates a new `TRUNCATE TABLE` statement.
     *
     * @param string $table
     * @return DatabaseTruncate
     */
    public function truncate($table)
    {
        return new DatabaseTruncate($this, $table);
    }

    /**
     * Factory method that creates a new `SET` statement.
     *
     * @param string $variable
     * @return DatabaseSet
     */
    public function set($variable)
    {
        return new DatabaseSet($this, $variable);
    }

    /**
     * Begins a new transaction.
     * This method calls `autoConnect()` before forwarding the call to PDO.
     *
     * @return boolean
     */
    public function beginTransaction()
    {
        $this->autoConnect();
        return $this->conn->beginTransaction();
    }

    /**
     * Commits the lastly created transaction.
     * This method calls `autoConnect()` before forwarding the call to PDO.
     *
     * @return boolean
     */
    public function commit()
    {
        $this->autoConnect();
        return $this->conn->commit();
    }

    /**
     * Rollbacks the lastly created transaction.
     * This method calls `autoConnect()` before forwarding the call to PDO.
     *
     * @return boolean
     */
    public function rollBack()
    {
        $this->autoConnect();
        return $this->conn->rollBack();
    }

    /**
     * Check if we are currently in a transaction.
     * This method calls `autoConnect()` before forwarding the call to PDO.
     *
     * @return boolean
     */
    public function inTransaction()
    {
        $this->autoConnect();
        return $this->conn->inTransaction();
    }

    /**
     * Factory method that creates a new DatabaseTransaction object.
     * $tx will be called with a single parameter: the instance of the current Database object.
     *
     * @param callable $tx
     *  The code to execute in the transaction
     * @return DatabaseTransaction
     */
    public function transaction($tx)
    {
        return new DatabaseTransaction($this, $tx);
    }

    /**
     * @internal
     * Finds the best possible PDO::PARAM_* value to bind with, based on the PHP type.
     *
     * @param mixed $value
     *  The value on which to deduce its PDO type
     * @return int
     *  Either PDO::PARAM_NULL, PDO::PARAM_INT, PDO::PARAM_BOOL or PDO::PARAM_STR
     */
    public function deducePDOParamType($value)
    {
        if ($value === null) {
            return PDO::PARAM_NULL;
        } elseif (is_numeric($value)) {
            return PDO::PARAM_INT;
        } elseif (is_bool($value)) {
            return PDO::PARAM_BOOL;
        }
        return PDO::PARAM_STR;
    }

    /**
     * Given a DatabaseStatement, it will execute it and return
     * its result, by calling `DatabaseStatement::result()`.
     * Any error will throw a DatabaseException.
     *
     * Developers are encouraged to call `DatabaseStatement::execute()` instead,
     * because it will make sure to set required state properly.
     *
     * @see validateSQLQuery()
     * @see DatabaseStatement::execute()
     * @see DatabaseStatement::result()
     * @param string $query
     * @return DatabaseStatementResult
     * @throws DatabaseException
     */
    public function execute(DatabaseStatement $stm)
    {
        $this->autoConnect();

        if ($this->isLoggingEnabled()) {
            $start = precision_timer();
        }

        $query = $stm->generateSQL();
        $values = $stm->getValues();
        $result = null;


        // Cleanup from last time, set some logging parameters
        $this->flush();
        $this->lastQuery = $stm->generateFormattedSQL();
        $this->lastQueryHash = md5($query . $start);

        $this->lastQueryValues = $values;
        $this->lastQuerySafe = $stm->isSafe();

        try {
            // Validate the query
            $this->validateSQLQuery($query, $stm->isSafe());
            // Prepare the query
            $pstm = $this->conn->prepare($query);
            // Bind all values
            foreach ($values as $param => $value) {
                if (General::intval($param) !== -1) {
                    $param = $param + 1;
                } else {
                    $param = ":$param";
                }
                $pstm->bindValue($param, $value, $this->deducePDOParamType($value));
            }
            // Execute it
            $result = $pstm->execute();
            $this->queryCount++;
        } catch (PDOException $ex) {
            $this->throwDatabaseError($ex);
            return;
        }

        // Check for errors
        if ($this->conn->errorCode() !== PDO::ERR_NONE) {
            $this->throwDatabaseError();
            return;
        }

        // Log the query
        if ($this->isLoggingEnabled()) {
            $this->logLastQuery(precision_timer('stop', $start));
        }

        return $stm->results($result, $pstm);
        return $stm->results($result, /** @scrutinizer ignore-type */ $pstm);
    }

    /**
     * @internal
     * This method checks for common pattern of SQL injection, like `--`, `'` and `;`.
     *
     * @see execute()
     * @param string $query
     *  The query to test.
     * @param boolean $strict
     *  Perform extra validation. True by default.
     * @return void
     * @throws DatabaseStatementException
     */
    final public function validateSQLQuery($query, $strict = true)
    {
        if (strpos($query, '\'--;') !== false) {
            throw (new DatabaseStatementException('Query contains SQL injection.'))->sql($query);
        } elseif ($strict && strpos($query, '--') !== false) {
            throw (new DatabaseStatementException('Query contains illegal characters: `--`.'))->sql($query);
        } elseif ($strict && strpos($query, '\'') !== false) {
            throw (new DatabaseStatementException('Query contains illegal character: `\'`.'))->sql($query);
        } elseif ($strict && strpos($query, ';') !== false) {
            throw (new DatabaseStatementException('Query contains illegal character: `;`.'))->sql($query);
        }
    }

    /**
     * Convenience function to allow you to execute multiple SQL queries at once
     * by providing a string with the queries delimited with a `;`
     *
     * @throws DatabaseException
     * @throws Exception
     * @param string $sql
     *  A string containing SQL queries delimited by `;`
     * @param boolean $force_engine
     *  @deprecated @since Symphony 3.0.0
     *  The default engine is now InnoDb.
     *  The import script should use InnoDb as well.
     *  The import script is also run in a transaction, resulting in a all or nothing import.
     *  Before 3.0.0:
     *  If set to true, this will set MySQL's default storage engine to MyISAM.
     *  Defaults to false, which will use MySQL's default storage engine when
     *  tables don't explicitly define which engine they should be created with
     * @return boolean
     *  If one of the queries fails, false will be returned and no further queries
     *  will be executed, otherwise true will be returned.
     */
    public function import($sql, $force_engine = false)
    {
        General::ensureType([
            'sql' => ['var' => $sql, 'type' => 'string'],
        ]);
        $queries = preg_split('/;[\\r\\n]+/', $sql, -1, PREG_SPLIT_NO_EMPTY);

        if (!is_array($queries) || empty($queries) || count($queries) <= 0) {
            throw new Exception('The SQL string contains no queries.');
        }

        return $this->transaction(function (Database $db) use ($queries) {
            foreach ($queries as $sql) {
                if (trim($sql) !== '') {
                    $stm = $db->statement();
                    $sql = $stm->replaceTablePrefix($sql);
                    $stm->unsafe()->unsafeAppendSQLPart('statement', $sql);
                    if (!$stm->execute()->success()) {
                        throw new DatabaseException('Failed to execute import statement');
                    }
                }
            }
        })->execute()->success();
    }

    /**
     * Given an Exception, or called when an error occurs, this function will
     * fire the `QueryExecutionError` delegate and then raise a `DatabaseException`
     *
     * @uses QueryExecutionError
     * @throws DatabaseException
     * @param Exception $ex
     *  The exception thrown while doing something with the Database
     */
    private function throwDatabaseError(Exception $ex = null)
    {
        if (isset($ex) && $ex) {
            $msg = $ex->getMessage();
            $errornum = (int)$ex->getCode();
        } else {
            $error = $this->conn->errorInfo();
            $msg = $error[2];
            $errornum = $error[0];
        }

        /**
         * After a query execution has failed this delegate will provide the query,
         * query hash, error message and the error number.
         *
         * Note that this function only starts logging once the `ExtensionManager`
         * is available, which means it will not fire for the first couple of
         * queries that set the character set.
         *
         * @since Symphony 2.3
         * @delegate QueryExecutionError
         * @param string $context
         * '/frontend/' or '/backend/'
         * @param string $query
         *  The query that has just been executed
         * @param string $query_hash
         *  The hash used by Symphony to uniquely identify this query
         * @param string $msg
         *  The error message provided by MySQL which includes information on why the execution failed
         * @param int $num
         *  The error number that corresponds with the MySQL error message
         * @param Exception $exception
         *  @since Symphony 3.0.0
         *  The raised exception, if any
         */
        if (Symphony::ExtensionManager() instanceof ExtensionManager) {

            Symphony::ExtensionManager()->notifyMembers(
                'QueryExecutionError',
                class_exists('Administration') ? '/backend/' : '/frontend/',
                [
                    'query' => $this->lastQuery,
                    'query_hash' => $this->lastQueryHash,
                    'msg' => $msg,
                    'num' => $errornum,
                    'exception' => $ex,
                ]
            );
        }

        throw new DatabaseException(
            __(
                'Database Error (%1$s): %2$s in query:%4$s%3$s',
                [$errornum, $msg, $this->lastQuery, PHP_EOL]
            ),
            [
                'msg' => $msg,
                'num' => $errornum,
                'query' => $this->lastQuery
            ],
            $ex
        );
    }

    /**
     * Function is called every time a query is executed to log it for
     * basic profiling/debugging purposes
     *
     * @uses PostQueryExecution
     * @param int $stop
     */
    private function logLastQuery($stop)
    {
        /**
         * After a query has successfully executed, that is it was considered
         * valid SQL, this delegate will provide the query, the query_hash and
         * the execution time of the query.
         *
         * Note that this function only starts logging once the ExtensionManager
         * is available, which means it will not fire for the first couple of
         * queries that set the character set.
         *
         * @since Symphony 2.3
         * @delegate PostQueryExecution
         * @param string $context
         * '/frontend/' or '/backend/'
         * @param string $query
         *  The query that has just been executed
         * @param string $query_hash
         *  The hash used by Symphony to uniquely identify this query
         * @param array $query_values
         *  @since Symphony 3.0.0
         *  The values passed by Symphony to the database
         * @param bool $query_safe
         *  @since Symphony 3.0.0
         *  If the query was using the unsafe mode
         * @param float $execution_time
         *  The time that it took to run `$query`
         */
        if (Symphony::ExtensionManager() instanceof ExtensionManager) {

            // TODO: Log unlogged queries
            Symphony::ExtensionManager()->notifyMembers(
                'PostQueryExecution',
                class_exists('Administration', false) ? '/backend/' : '/frontend/',
                [
                    'query' => $this->lastQuery, // TODO: Format
                    'query_hash' => $this->lastQueryHash,
                    'query_values' => $this->lastQueryValues,
                    'query_safe' => $this->lastQuerySafe,
                    'execution_time' => $stop
                ]
            );
        }

        // Keep internal log for easy debugging
        $this->log[] = [
            'query' => $this->lastQuery, // TODO: Format
            'query_hash' => $this->lastQueryHash,
            'query_values' => $this->lastQueryValues,
            'query_safe' => $this->lastQuerySafe,
            'execution_time' => $stop
        ];
    }

    /**
     * Returns some basic statistics from the Database class about the
     * number of queries, the time it took to query and any slow queries.
     * A slow query is defined as one that took longer than 0.0999 seconds
     * This function is used by the Profile devkit
     *
     * @return array
     *  An associative array with the number of queries, an array of slow
     *  queries and the total query time.
     */
    public function getStatistics()
    {
        $stats = [];

        $query_timer = 0.0;
        $slow_queries = [];

        foreach ($this->log as $key => $val) {
            $query_timer += $val['execution_time'];
            if ($val['execution_time'] > 0.0999) {
                $slow_queries[] = $val;
            }
        }

        return [
            'queries' => $this->queryCount(),
            'slow-queries' => $slow_queries,
            'total-query-time' => number_format($query_timer, 5, '.', '')
        ];
    }

    //--------------------------------------------------------------------------
    // COMPAT LAYER
    // All the following methods are deprecated and are there for
    // backward compatibility only.
    //--------------------------------------------------------------------------

    /**
     * Returns all the log entries by type. There are two valid types,
     * error and debug. If no type is given, the entire log is returned,
     * otherwise only log messages for that type are returned
     *
     * @deprecated @since Symphony 3.0.0
     * @see Database::getLogs()
     * @param null|string $type
     * @return array
     *  An array of associative array's. Log entries of the error type
     *  return the query the error occurred on and the error number and
     *  message from MySQL. Log entries of the debug type return the
     *  the query and the start/stop time to indicate how long it took
     *  to run
     */
    public function debug($type = null)
    {
        return $this->getLogs();
    }

    /**
     * This function will clean a string using the `PDO::quote` function
     * taking into account the current database character encoding. Note that this
     * function does not encode _ or %.
     *
     * @deprecated @since Symphony 3.0.0
     * @see quote
     * @param string $value
     *  The string to be encoded into an escaped SQL string
     * @return string
     *  The escaped SQL string
     */
    public function cleanValue($value)
    {
        if (Symphony::Log()) {
            Symphony::Log()->pushDeprecateWarningToLog('Database::cleanValue()', 'The new API');
        }
        return trim($this->quote($value), "'");
        return trim(/** @scrutinizer ignore-deprecated */ $this->quote($value), "'");
    }

    /**
     * This function will apply the `quote` function to an associative
     * array of data, encoding only the value, not the key. This function
     * can handle recursive arrays. This function manipulates the given
     * parameter by reference.
     *
     * @deprecated @since Symphony 3.0.0
     * @see quoteFields
     * @param array $array
     *  The associative array of data to encode, this parameter is manipulated
     *  by reference.
     */
    public function cleanFields(array &$array)
    {
        $this->quoteFields($array);
        /** @scrutinizer ignore-deprecated */ $this->quoteFields($array);
    }

    /**
     * Determines whether this query is a read operation, or if it is a write operation.
     * A write operation is determined as any query that starts with CREATE, INSERT,
     * REPLACE, ALTER, DELETE, UPDATE, OPTIMIZE, TRUNCATE, DROP, LOCK or UNLOCK. Anything else is
     * considered to be a read operation which are subject to query caching.
     *
     * @deprecated @since Symphony 3.0.0
     * @param string $query
     * @return int
     *  `self::__WRITE_OPERATION__` or `self::__READ_OPERATION__`
     */
    public function determineQueryType($query)
    {
        return preg_match(
            '/^(create|insert|replace|alter|delete|update|optimize|truncate|drop|lock|unlock)/i',
            $query
        ) === 1 ? self::__WRITE_OPERATION__ : self::__READ_OPERATION__;
        ) === 1 ? /** @scrutinizer ignore-deprecated */ self::__WRITE_OPERATION__ : self::__READ_OPERATION__;
    }

    /**
     * Takes an SQL string and executes it. This function will apply query
     * caching if it is a read operation and if query caching is set. Symphony
     * will convert the `tbl_` prefix of tables to be the one set during installation.
     * A type parameter is provided to specify whether `$this->_lastResult` will be an array
     * of objects or an array of associative arrays. The default is objects. This
     * function will return boolean, but set `$this->_lastResult` to the result.
     *
     * @deprecated @since Symphony 3.0.0
     * @see select()
     * @see insert()
     * @see update()
     * @see delete()
     * @see create()
     * @see alter()
     * @see drop()
     * @see truncate()
     * @see optimize()
     * @see set()
     * @see autoConnect()
     * @uses PostQueryExecution
     * @param string $query
     *  The full SQL query to execute.
     * @param string $type
     *  Whether to return the result as objects or associative array. Defaults
     *  to OBJECT which will return objects. The other option is ASSOC. If $type
     *  is not either of these, it will return objects.
     * @throws DatabaseException
     * @return boolean|Traversable
     *  true if the query executed without errors, false otherwise
     */
    public function query($query, $type = "OBJECT")
    {
        General::ensureType([
            'query' => ['var' => $query, 'type' => 'string'],
            'type' => ['var' => $type, 'type' => 'string'],
        ]);
        $this->autoConnect();

        if ($this->isLoggingEnabled()) {
            $start = precision_timer();
        }

        $result = null;
        // Format SQL because PDO does not seem to like it
        $query = trim(str_replace(PHP_EOL, ' ', $query));
        $query = trim(str_replace('\t', ' ', $query));
        while (strpos($query, '  ') !== false) {
            $query = str_replace('  ', ' ', $query);
        }
        if ($this->getPrefix() !== 'tbl_') {
            $query = preg_replace('/tbl_(\S+?)([\s\.,]|$)/', $this->getPrefix().'\\1\\2', $query);
        }

        // Cleanup from last time, set some logging parameters
        $this->flush();
        $this->lastQuery = $query;
        $this->lastQueryHash = md5($query . $start);

        $this->lastQueryValues = null;
        $this->lastQuerySafe = false;
        $query_type = $this->determineQueryType($query);
        $query_type = /** @scrutinizer ignore-deprecated */ $this->determineQueryType($query);

        // TYPE is deprecated since MySQL 4.0.18, ENGINE is preferred
        if ($query_type == self::__WRITE_OPERATION__) {
        if ($query_type == /** @scrutinizer ignore-deprecated */ self::__WRITE_OPERATION__) {
            $query = preg_replace('/MyISAM/i', 'InnoDB', $query);
            $query = preg_replace('/TYPE=InnoDB/i', 'ENGINE=InnoDB', $query);
        } elseif ($query_type == self::__READ_OPERATION__) {
        } elseif ($query_type == /** @scrutinizer ignore-deprecated */ self::__READ_OPERATION__) {
            if (!preg_match('/^\s*SELECT\s+SQL(_NO)?_CACHE/i', $query)) {
                if ($this->isCachingEnabled()) {
                if (/** @scrutinizer ignore-deprecated */ $this->isCachingEnabled()) {
                    $query = preg_replace('/^SELECT\s+/i', 'SELECT SQL_CACHE ', $query);
                } else {
                    $query = preg_replace('/^SELECT\s+/i', 'SELECT SQL_NO_CACHE ', $query);
                }
            }
            $fetchType = $type == "OBJECT" ? PDO::FETCH_OBJ : PDO::FETCH_ASSOC;
        }

        try {
            $resultPdo = $this->conn->query($query);
            // Execute it
            if ($fetchType) {

                $result = $resultPdo->fetchAll($fetchType);
            } else {
                $result = $resultPdo->execute();
            }
            $resultPdo->closeCursor();
            $this->queryCount++;
            $this->_lastResult = $result;

        } catch (PDOException $ex) {
            $this->throwDatabaseError($ex);
            return;
        }

        // Check for errors
        if ($this->conn->errorCode() !== PDO::ERR_NONE) {
            $this->throwDatabaseError();
            return;
        }

        // Log the query
        if ($this->isLoggingEnabled()) {
            $this->logLastQuery(precision_timer('stop', $start));
        }

        return $result !== false;
    }

    /**
     * A convenience method to insert data into the Database. This function
     * takes an associative array of data to input, with the keys being the column
     * names and the table. An optional parameter exposes MySQL's ON DUPLICATE
     * KEY UPDATE functionality, which will update the values if a duplicate key
     * is found.
     *
     * @deprecated @since Symphony 3.0.0
     * @param array $fields
     *  An associative array of data to input, with the key's mapping to the
     *  column names. Alternatively, an array of associative array's can be
     *  provided, which will perform multiple inserts
     * @param string $table
     *  The table name, including the tbl prefix which will be changed
     *  to this Symphony's table prefix in the query function
     * @param boolean $updateOnDuplicate
     *  If set to true, data will updated if any key constraints are found that cause
     *  conflicts. By default this is set to false, which will not update the data and
     *  would return an SQL error
     * @throws DatabaseException
     * @return boolean
     */
    public function _insert(array $fields, $table, $updateOnDuplicate = false) // @codingStandardsIgnoreLine
    {
        $success = true;
        // Multiple inserts
        if (!is_array(current($fields))) {
            $fields = [$fields];
        }
        foreach ($fields as $field) {
            $stm = $this->insert($table)->values($field);
            $stm = /** @scrutinizer ignore-deprecated */ $this->insert($table)->values($field);
            if ($updateOnDuplicate) {
                $stm->updateOnDuplicateKey();
            }
            $success = $success && $stm->execute()->success();
        }
        return $success;
    }

    /**
     * A convenience method to update data that exists in the Database. This function
     * takes an associative array of data to input, with the keys being the column
     * names and the table. A WHERE statement can be provided to select the rows
     * to update
     *
     * @deprecated @since Symphony 3.0.0
     * @param array $fields
     *  An associative array of data to input, with the key's mapping to the
     *  column names.
     * @param string $table
     *  The table name, including the tbl prefix which will be changed
     *  to this Symphony's table prefix in the query function
     * @param string $where
     *  A WHERE statement for this UPDATE statement, defaults to null
     *  which will update all rows in the $table
     * @throws DatabaseException
     * @return boolean
     */
    public function _update(array $fields, $table, $where = null) // @codingStandardsIgnoreLine
    {
        $stm = $this->update($table)->set($fields);
        $stm = /** @scrutinizer ignore-deprecated */ $this->update($table)->set($fields);
        if ($where) {
            $stm->unsafeAppendSQLPart('where', "WHERE $where");
        }
        return $stm->execute()->success();
    }

    /**
     * Returns an associative array that contains the results of the
     * given `$query`. Optionally, the resulting array can be indexed
     * by a particular column.
     *
     * @deprecated @since Symphony 3.0.0
     * @param string $query
     *  The full SQL query to execute. Defaults to null, which will
     *  use the _lastResult
     * @param string $index_by_column
     *  The name of a column in the table to use it's value to index
     *  the result by. If this is omitted (and it is by default), an
     *  array of associative arrays is returned, with the key being the
     *  column names
     * @throws DatabaseException
     * @return array
     *  An associative array with the column names as the keys
     */
    public function fetch($query = null, $index_by_column = null)
    {
        if (!is_null($query)) {
            $this->query($query, "ASSOC");
            /** @scrutinizer ignore-deprecated */ $this->query($query, "ASSOC");
        } elseif (is_null($this->_lastResult)) {
            return array();
        }

        $result = $this->_lastResult;

        if (!is_null($index_by_column) && isset($result[0][$index_by_column])) {
            $n = array();

            foreach ($result as $ii) {
                $n[$ii[$index_by_column]] = $ii;
            }

            $result = $n;
        }

        return $result;
    }

    /**
     * Returns the row at the specified index from the given query. If no
     * query is given, it will use the `$this->_lastResult`. If no offset is provided,
     * the function will return the first row. This function does not imply any
     * LIMIT to the given `$query`, so for the more efficient use, it is recommended
     * that the `$query` have a LIMIT set.
     *
     * @deprecated @since Symphony 3.0.0
     * @param int $offset
     *  The row to return from the SQL query. For instance, if the second
     *  row from the result was required, the offset would be 1, because it
     *  is zero based.
     * @param string $query
     *  The full SQL query to execute. Defaults to null, which will
     *  use the `$this->_lastResult`
     * @throws DatabaseException
     * @return array
     *  If there is no row at the specified `$offset`, an empty array will be returned
     *  otherwise an associative array of that row will be returned.
     */
    public function fetchRow($offset = 0, $query = null)
    {
        $result = $this->fetch($query);
        $result = /** @scrutinizer ignore-deprecated */ $this->fetch($query);
        return (empty($result) ? array() : $result[$offset]);
    }

    /**
     * Returns an array of values for a specified column in a given query.
     * If no query is given, it will use the `$this->_lastResult`.
     *
     * @deprecated @since Symphony 3.0.0
     * @param string $column
     *  The column name in the query to return the values for
     * @param string $query
     *  The full SQL query to execute. Defaults to null, which will
     *  use the `$this->_lastResult`
     * @throws DatabaseException
     * @return array
     *  If there is no results for the `$query`, an empty array will be returned
     *  otherwise an array of values for that given `$column` will be returned
     */
    public function fetchCol($column, $query = null)
    {
        $result = $this->fetch($query);
        $result = /** @scrutinizer ignore-deprecated */ $this->fetch($query);

        if (empty($result)) {
            return array();
        }

        $rows = array();
        foreach ($result as $row) {
            $rows[] = $row[$column];
        }

        return $rows;
    }

    /**
     * Returns the value for a specified column at a specified offset. If no
     * offset is provided, it will return the value for column of the first row.
     * If no query is given, it will use the `$this->_lastResult`.
     *
     * @deprecated @since Symphony 3.0.0
     * @see select
     * @param string $column
     *  The column name in the query to return the values for
     * @param int $offset
     *  The row to use to return the value for the given `$column` from the SQL
     *  query. For instance, if `$column` form the second row was required, the
     *  offset would be 1, because it is zero based.
     * @param string $query
     *  The full SQL query to execute. Defaults to null, which will
     *  use the `$this->_lastResult`
     * @throws DatabaseException
     * @return string|null
     *  Returns the value of the given column, if it doesn't exist, null will be
     *  returned
     */
    public function fetchVar($column, $offset = 0, $query = null)
    {
        $result = $this->fetch($query);
        $result = /** @scrutinizer ignore-deprecated */ $this->fetch($query);
        return (empty($result) ? null : $result[$offset][$column]);
    }
}

/**
 * Compat layer: Recreate the old MySQL class by extending our new Database class
 */
class MySQL extends Database
{
    public function cleanValue($value)
    {
        return (new Database())->cleanValue($value);
        return /** @scrutinizer ignore-deprecated */ (new Database())->cleanValue($value);
    }
}

symphonycms / symphony-2

GitHub Access Token became invalid

Push — 3.0.x ( 7946ec...895393 )

Database::showIndex() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like
