Database.php

Go to the documentation of this file.

<?php

namespace Elgg;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Driver\ServerInfoAwareConnection;
use Doctrine\DBAL\Result;
use Doctrine\DBAL\Query\QueryBuilder;
use Elgg\Cache\QueryCache;
use Elgg\Database\DbConfig;
use Elgg\Exceptions\DatabaseException;
use Elgg\Traits\Debug\Profilable;
use Elgg\Traits\Loggable;
use Psr\Log\LogLevel;

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->closeConnections();
        
        $this->config = $config;
        $this->table_prefix = $config->getTablePrefix();
        $this->query_cache->enable();
        $this->query_cache->clear();
    }
    
    public function closeConnections(): void {
        foreach ($this->connections as $connection) {
            $connection->close();
        }
        
        $this->connections = [];
    }

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

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

    public function connect(string $type = 'readwrite'): void {
        $conf = $this->config->getConnectionConfig($type);

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

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

            // https://github.com/Elgg/Elgg/issues/8121
            $sub_query = "SELECT REPLACE(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY', '')";
            $this->connections[$type]->executeStatement("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 = []) {
        if (!$query instanceof QueryBuilder) {
            $this->logDeprecatedMessage('The use of non ' . QueryBuilder::class . ' queries in ' . __METHOD__ . ' has been deprecated', '4.0');
        }

        return $this->getResults($query, $callback, false, $params);
    }

    public function getDataRow($query, $callback = null, array $params = []) {
        if (!$query instanceof QueryBuilder) {
            $this->logDeprecatedMessage('The use of non ' . QueryBuilder::class . ' queries in ' . __METHOD__ . ' has been deprecated', '4.0');
        }

        return $this->getResults($query, $callback, true, $params);
    }

    public function insertData($query, array $params = []): int {
        if (!$query instanceof QueryBuilder) {
            $this->logDeprecatedMessage('The use of non ' . QueryBuilder::class . ' queries in ' . __METHOD__ . ' has been deprecated', '4.0');
        }

        $sql = $query;
        $connection = $this->getConnection('write');
        if ($query instanceof QueryBuilder) {
            $params = $query->getParameters();
            $sql = $query->getSQL();
            $connection = $query->getConnection();
        }

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

        $this->query_cache->clear();

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

    public function updateData($query, bool $get_num_rows = false, array $params = []) {
        if (!$query instanceof QueryBuilder) {
            $this->logDeprecatedMessage('The use of non ' . QueryBuilder::class . ' queries in ' . __METHOD__ . ' has been deprecated', '4.0');
        }

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

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

        $this->query_cache->clear();

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

        return ($result instanceof Result) ? $result->rowCount() : $result;
    }

    public function deleteData($query, array $params = []): int {
        if (!$query instanceof QueryBuilder) {
            $this->logDeprecatedMessage('The use of non ' . QueryBuilder::class . ' queries in ' . __METHOD__ . ' has been deprecated', '4.0');
        }

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

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

        $this->query_cache->clear();

        $result = $this->executeQuery($query, $this->getConnection('write'), $params);
        return ($result instanceof Result) ? $result->rowCount() : $result;
    }

    protected function fingerprintCallback($callback): string {
        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, bool $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;
        }
        
        $this->getLogger()->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->fetchAssociative()) {
            $row_obj = (object) $row;
            if ($callback) {
                $row_obj = call_user_func($callback, $row_obj);
            }

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

        // 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();
        }
        
        foreach ($params as $param_key => $value) {
            if (substr($param_key, 0, 1) !== ':') {
                continue;
            }
            
            $this->getLogger()->warning('Unsupported colon detected in named param: ' . $param_key);
            
            unset($params[$param_key]);
            $params[substr($param_key, 1)] = $value;
        }
                
        try {
            $result = $this->trackQuery($sql, $params, function() use ($query, $params, $connection, $sql) {
                if ($query instanceof QueryBuilder) {
                    if ($query->getType() === QueryBuilder::SELECT) {
                        return $query->executeQuery();
                    } else {
                        return $query->executeStatement();
                    }
                } elseif (!empty($params)) {
                    return $connection->executeQuery($sql, $params);
                } else {
                    // faster
                    return $connection->executeQuery($sql);
                }
            });
        } catch (\Exception $e) {
            $ex = new DatabaseException($e->getMessage(), 0, $e);
            $ex->setParameters($params);
            $ex->setQuery($sql);

            throw $ex;
        }

        return $result;
    }
    
    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 = preg_replace('~\\s+~', ' ', trim($sql . '|' . serialize($params)));
        $this->beginTimer(['SQL', $timer_key]);
        
        $stop_timer = function() use ($timer_key) {
            $this->endTimer(['SQL', $timer_key]);
        };
        
        try {
            $result = $callback();
        } catch (\Exception $e) {
            $stop_timer();
            
            throw $e;
        }
        
        $stop_timer();
        
        return $result;
    }

    public function registerDelayedQuery($query, string $type, $callback = null, array $params = []): bool {
        if (!$query instanceof QueryBuilder) {
            $this->logDeprecatedMessage('The use of non ' . QueryBuilder::class . ' queries in ' . __METHOD__ . ' has been deprecated', '4.0');
        }

        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(): void {

        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) {
                // Suppress all exceptions since page already sent to requestor
                $this->getLogger()->error($e);
            }
        }

        $this->delayed_queries = [];
    }

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

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

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

    public function getServerVersion(string $type = DbConfig::READ_WRITE): string {
        $driver = $this->getConnection($type)->getWrappedConnection();
        if ($driver instanceof ServerInfoAwareConnection) {
            $version = $driver->getServerVersion();
            
            if ($this->isMariaDB($type)) {
                if (strpos($version, '5.5.5-') === 0) {
                    $version = substr($version, 6);
                }
            }
            
            return $version;
        }

        return '';
    }

    public function isMariaDB(string $type = DbConfig::READ_WRITE): bool {
        $driver = $this->getConnection($type)->getWrappedConnection();
        if ($driver instanceof ServerInfoAwareConnection) {
            $version = $driver->getServerVersion();
            
            return stristr($version, 'mariadb') !== false;
        }

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

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

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