Database.php

Go to the documentation of this file.

<?php

namespace Elgg;

use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Driver\Statement;
use Doctrine\DBAL\Driver\ServerInfoAwareConnection;
use Doctrine\DBAL\Query\QueryBuilder;
use Elgg\Database\DbConfig;
use Psr\Log\LogLevel;
use Elgg\Cache\QueryCache;

class Database {
    use Profilable;
    use Loggable;

    const DELAYED_QUERY = 'q';
    const DELAYED_TYPE = 't';
    const DELAYED_HANDLER = 'h';
    const DELAYED_PARAMS = 'p';

    private $table_prefix;

    private $connections = [];

    private $query_count = 0;

    protected $query_cache;

    protected $delayed_queries = [];

    private $config;

    public function __construct(DbConfig $config, QueryCache $query_cache) {
        $this->query_cache = $query_cache;
        
        $this->resetConnections($config);
    }

    public function resetConnections(DbConfig $config) {
        $this->connections = [];
        $this->config = $config;
        $this->table_prefix = $config->getTablePrefix();
        $this->query_cache->enable();
        $this->query_cache->clear();
        
    }

    public function getConnection($type) {
        if (isset($this->connections[$type])) {
            return $this->connections[$type];
        } else if (isset($this->connections['readwrite'])) {
            return $this->connections['readwrite'];
        } else {
            $this->setupConnections();
            return $this->getConnection($type);
        }
    }

    public function setupConnections() {
        if ($this->config->isDatabaseSplit()) {
            $this->connect('read');
            $this->connect('write');
        } else {
            $this->connect('readwrite');
        }
    }

    public function connect($type = "readwrite") {
        $conf = $this->config->getConnectionConfig($type);

        $params = [
            'dbname' => $conf['database'],
            'user' => $conf['user'],
            'password' => $conf['password'],
            'host' => $conf['host'],
            'charset' => $conf['encoding'],
            'driver' => 'pdo_mysql',
        ];

        try {
            $this->connections[$type] = DriverManager::getConnection($params);
            $this->connections[$type]->setFetchMode(\PDO::FETCH_OBJ);

            // https://github.com/Elgg/Elgg/issues/8121
            $sub_query = "SELECT REPLACE(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY', '')";
            $this->connections[$type]->exec("SET SESSION sql_mode=($sub_query);");
        } catch (\Exception $e) {
            // http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html
            $this->log(LogLevel::ERROR, $e);

            if ($e->getCode() == 1102 || $e->getCode() == 1049) {
                $msg = "Elgg couldn't select the database '{$conf['database']}'. "
                    . "Please check that the database is created and you have access to it.";
            } else {
                $msg = "Elgg couldn't connect to the database using the given credentials. Check the settings file.";
            }
            throw new \DatabaseException($msg);
        }
    }

    public function getData($query, $callback = null, array $params = []) {
        return $this->getResults($query, $callback, false, $params);
    }

    public function getDataRow($query, $callback = null, array $params = []) {
        return $this->getResults($query, $callback, true, $params);
    }

    public function insertData($query, array $params = []) {

        if ($query instanceof QueryBuilder) {
            $params = $query->getParameters();
            $query = $query->getSQL();
        }

        if ($this->logger) {
            $this->logger->info("DB insert query $query (params: " . print_r($params, true) . ")");
        }

        $connection = $this->getConnection('write');

        $this->query_cache->clear();

        $this->executeQuery($query, $connection, $params);
        return (int) $connection->lastInsertId();
    }

    public function updateData($query, $get_num_rows = false, array $params = []) {

        if ($query instanceof QueryBuilder) {
            $params = $query->getParameters();
            $query = $query->getSQL();
        }

        if ($this->logger) {
            $this->logger->info("DB update query $query (params: " . print_r($params, true) . ")");
        }

        $this->query_cache->clear();

        $stmt = $this->executeQuery($query, $this->getConnection('write'), $params);
        if ($get_num_rows) {
            return $stmt->rowCount();
        } else {
            return true;
        }
    }

    public function deleteData($query, array $params = []) {

        if ($query instanceof QueryBuilder) {
            $params = $query->getParameters();
            $query = $query->getSQL();
        }

        if ($this->logger) {
            $this->logger->info("DB delete query $query (params: " . print_r($params, true) . ")");
        }

        $connection = $this->getConnection('write');

        $this->query_cache->clear();

        $stmt = $this->executeQuery("$query", $connection, $params);
        return (int) $stmt->rowCount();
    }

    protected function fingerprintCallback($callback) {
        if (is_string($callback)) {
            return $callback;
        }
        if (is_object($callback)) {
            return spl_object_hash($callback) . "::__invoke";
        }
        if (is_array($callback)) {
            if (is_string($callback[0])) {
                return "{$callback[0]}::{$callback[1]}";
            }
            return spl_object_hash($callback[0]) . "::{$callback[1]}";
        }
        // this should not happen
        return "";
    }

    protected function getResults($query, $callback = null, $single = false, array $params = []) {

        if ($query instanceof QueryBuilder) {
            $params = $query->getParameters();
            $sql = $query->getSQL();
        } else {
            $sql = $query;
        }
        
        // Since we want to cache results of running the callback, we need to
        // namespace the query with the callback and single result request.
        // https://github.com/elgg/elgg/issues/4049
        $extras = (int) $single . '|';
        if ($callback) {
            if (!is_callable($callback)) {
                throw new \RuntimeException('$callback must be a callable function. Given '
                                            . _elgg_services()->handlers->describeCallable($callback));
            }
            $extras .= $this->fingerprintCallback($callback);
        }
        
        $hash = $this->query_cache->getHash($sql, $params, $extras);

        $cached_results = $this->query_cache->get($hash);
        if (isset($cached_results)) {
            return $cached_results;
        }
        
        if ($this->logger) {
            $this->logger->info("DB select query $sql (params: " . print_r($params, true) . ")");
        }
        
        $return = [];

        if ($query instanceof QueryBuilder) {
            $stmt = $this->executeQuery($query, $query->getConnection());
        } else {
            $stmt = $this->executeQuery($query, $this->getConnection('read'), $params);
        }
        
        while ($row = $stmt->fetch()) {
            if ($callback) {
                $row = call_user_func($callback, $row);
            }

            if ($single) {
                $return = $row;
                break;
            } else {
                $return[] = $row;
            }
        }

        // Cache result
        $this->query_cache->set($hash, $return);
                
        return $return;
    }

    protected function executeQuery($query, Connection $connection, array $params = []) {
        if ($query == null) {
            throw new \DatabaseException("Query cannot be null");
        }

        $sql = $query;
        if ($query instanceof QueryBuilder) {
            $params = $query->getParameters();
            $sql = $query->getSQL();
        }
                
        try {
            $value = $this->trackQuery($sql, $params, function() use ($query, $params, $connection, $sql) {
                if ($query instanceof \Elgg\Database\QueryBuilder) {
                    return $query->execute(false);
                } elseif ($query instanceof QueryBuilder) {
                    return $query->execute();
                } elseif (!empty($params)) {
                    return $connection->executeQuery($sql, $params);
                } else {
                    // faster
                    return $connection->query($sql);
                }
            });
        } catch (\Exception $e) {
            $ex = new \DatabaseException($e->getMessage());
            $ex->setParameters($params);
            $ex->setQuery($sql);

            throw $ex;
        }

        return $value;
    }
    
    public function trackQuery($query, array $params, callable $callback) {
    
        $sql = $query;
        if ($query instanceof QueryBuilder) {
            $params = $query->getParameters();
            $sql = $query->getSQL();
        }

        $this->query_count++;

        $timer_key = false;
        if ($this->timer) {
            $timer_key = preg_replace('~\\s+~', ' ', trim($sql . '|' . serialize($params)));
            $this->timer->begin(['SQL', $timer_key]);
        }
        
        $stop_timer = function() use ($timer_key) {
            if ($timer_key) {
                $this->timer->end(['SQL', $timer_key]);
            }
        };
        
        try {
            $result = $callback();
        } catch (\Exception $e) {
            $stop_timer();
            
            throw $e;
        }
        
        $stop_timer();
        
        return $result;
    }

    public function runSqlScript($scriptlocation) {
        $script = file_get_contents($scriptlocation);
        if ($script) {
            $errors = [];

            // Remove MySQL '-- ' and '# ' style comments
            $script = preg_replace('/^(?:--|#) .*$/m', '', $script);

            // Statements must end with ; and a newline
            $sql_statements = preg_split('/;[\n\r]+/', "$script\n");

            foreach ($sql_statements as $statement) {
                $statement = trim($statement);
                $statement = str_replace("prefix_", $this->table_prefix, $statement);
                if (!empty($statement)) {
                    try {
                        $this->updateData($statement);
                    } catch (\DatabaseException $e) {
                        $errors[] = $e->getMessage();
                    }
                }
            }
            if (!empty($errors)) {
                $errortxt = "";
                foreach ($errors as $error) {
                    $errortxt .= " {$error};";
                }

                $msg = "There were a number of issues: " . $errortxt;
                throw new \DatabaseException($msg);
            }
        } else {
            $msg = "Elgg couldn't find the requested database script at " . $scriptlocation . ".";
            throw new \DatabaseException($msg);
        }
    }

    public function registerDelayedQuery($query, $type, $callback = null, array $params = []) {
        if ($type != 'read' && $type != 'write') {
            return false;
        }

        $this->delayed_queries[] = [
            self::DELAYED_QUERY => $query,
            self::DELAYED_TYPE => $type,
            self::DELAYED_HANDLER => $callback,
            self::DELAYED_PARAMS => $params,
        ];

        return true;
    }

    public function executeDelayedQueries() {

        foreach ($this->delayed_queries as $set) {
            $query = $set[self::DELAYED_QUERY];
            $type = $set[self::DELAYED_TYPE];
            $handler = $set[self::DELAYED_HANDLER];
            $params = $set[self::DELAYED_PARAMS];

            try {
                $stmt = $this->executeQuery($query, $this->getConnection($type), $params);

                if (is_callable($handler)) {
                    call_user_func($handler, $stmt);
                }
            } catch (\Exception $e) {
                if ($this->logger) {
                    // Suppress all exceptions since page already sent to requestor
                    $this->logger->error($e);
                }
            }
        }

        $this->delayed_queries = [];
    }

    public function enableQueryCache() {
        $this->query_cache->enable();
    }

    public function disableQueryCache() {
        $this->query_cache->disable();
    }

    public function getQueryCount() {
        return $this->query_count;
    }

    public function sanitizeInt($value, $signed = true) {
        $value = (int) $value;

        if ($signed === false) {
            if ($value < 0) {
                $value = 0;
            }
        }

        return $value;
    }

    public function sanitizeString($value) {
        if (is_array($value)) {
            throw new \DatabaseException(__METHOD__ . '() and serialize_string() cannot accept arrays.');
        }
        $quoted = $this->getConnection('read')->quote($value);
        if ($quoted[0] !== "'" || substr($quoted, -1) !== "'") {
            throw new \DatabaseException("PDO::quote did not return surrounding single quotes.");
        }
        return substr($quoted, 1, -1);
    }

    public function getServerVersion($type) {
        $driver = $this->getConnection($type)->getWrappedConnection();
        if ($driver instanceof ServerInfoAwareConnection) {
            return $driver->getServerVersion();
        }

        return null;
    }

    public function __get($name) {
        if ($name === 'prefix') {
            return $this->table_prefix;
        }

        throw new \RuntimeException("Cannot read property '$name'");
    }

    public function __set($name, $value) {
        throw new \RuntimeException("Cannot write property '$name'");
    }
}