src/Database/Database.php

   1 <?php
   2 /**
   3  * @copyright Copyright (C) 2020, Friendica
   4  *
   5  * @license GNU AGPL version 3 or any later version
   6  *
   7  * This program is free software: you can redistribute it and/or modify
   8  * it under the terms of the GNU Affero General Public License as
   9  * published by the Free Software Foundation, either version 3 of the
  10  * License, or (at your option) any later version.
  11  *
  12  * This program is distributed in the hope that it will be useful,
  13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15  * GNU Affero General Public License for more details.
  16  *
  17  * You should have received a copy of the GNU Affero General Public License
  18  * along with this program.  If not, see <https://www.gnu.org/licenses/>.
  19  *
  20  */
  21
  22 namespace Friendica\Database;
  23
  24 use Exception;
  25 use Friendica\Core\Config\Cache;
  26 use Friendica\Core\System;
  27 use Friendica\DI;
  28 use Friendica\Network\HTTPException\InternalServerErrorException;
  29 use Friendica\Util\DateTimeFormat;
  30 use Friendica\Util\Profiler;
  31 use mysqli;
  32 use mysqli_result;
  33 use mysqli_stmt;
  34 use PDO;
  35 use PDOException;
  36 use PDOStatement;
  37 use Psr\Log\LoggerInterface;
  38
  39 /**
  40  * This class is for the low level database stuff that does driver specific things.
  41  */
  42 class Database
  43 {
  44         protected $connected = false;
  45
  46         /**
  47          * @var Cache
  48          */
  49         protected $configCache;
  50         /**
  51          * @var Profiler
  52          */
  53         protected $profiler;
  54         /**
  55          * @var LoggerInterface
  56          */
  57         protected $logger;
  58         protected $server_info    = '';
  59         /** @var PDO|mysqli */
  60         protected $connection;
  61         protected $driver;
  62         private $emulate_prepares = false;
  63         private $error          = false;
  64         private $errorno        = 0;
  65         private $affected_rows  = 0;
  66         protected $in_transaction = false;
  67         protected $in_retrial     = false;
  68         protected $testmode       = false;
  69         private $relation       = [];
  70
  71         public function __construct(Cache $configCache, Profiler $profiler, LoggerInterface $logger, array $server = [])
  72         {
  73                 // We are storing these values for being able to perform a reconnect
  74                 $this->configCache   = $configCache;
  75                 $this->profiler      = $profiler;
  76                 $this->logger        = $logger;
  77
  78                 $this->readServerVariables($server);
  79                 $this->connect();
  80
  81                 if ($this->isConnected()) {
  82                         // Loads DB_UPDATE_VERSION constant
  83                         DBStructure::definition($configCache->get('system', 'basepath'), false);
  84                 }
  85         }
  86
  87         private function readServerVariables(array $server)
  88         {
  89                 // Use environment variables for mysql if they are set beforehand
  90                 if (!empty($server['MYSQL_HOST'])
  91                     && (!empty($server['MYSQL_USERNAME'] || !empty($server['MYSQL_USER'])))
  92                     && $server['MYSQL_PASSWORD'] !== false
  93                     && !empty($server['MYSQL_DATABASE']))
  94                 {
  95                         $db_host = $server['MYSQL_HOST'];
  96                         if (!empty($server['MYSQL_PORT'])) {
  97                                 $db_host .= ':' . $server['MYSQL_PORT'];
  98                         }
  99                         $this->configCache->set('database', 'hostname', $db_host);
 100                         unset($db_host);
 101                         if (!empty($server['MYSQL_USERNAME'])) {
 102                                 $this->configCache->set('database', 'username', $server['MYSQL_USERNAME']);
 103                         } else {
 104                                 $this->configCache->set('database', 'username', $server['MYSQL_USER']);
 105                         }
 106                         $this->configCache->set('database', 'password', (string) $server['MYSQL_PASSWORD']);
 107                         $this->configCache->set('database', 'database', $server['MYSQL_DATABASE']);
 108                 }
 109         }
 110
 111         public function connect()
 112         {
 113                 if (!is_null($this->connection) && $this->connected()) {
 114                         return $this->connected;
 115                 }
 116
 117                 // Reset connected state
 118                 $this->connected = false;
 119
 120                 $port       = 0;
 121                 $serveraddr = trim($this->configCache->get('database', 'hostname'));
 122                 $serverdata = explode(':', $serveraddr);
 123                 $server     = $serverdata[0];
 124                 if (count($serverdata) > 1) {
 125                         $port = trim($serverdata[1]);
 126                 }
 127                 $server  = trim($server);
 128                 $user    = trim($this->configCache->get('database', 'username'));
 129                 $pass    = trim($this->configCache->get('database', 'password'));
 130                 $db      = trim($this->configCache->get('database', 'database'));
 131                 $charset = trim($this->configCache->get('database', 'charset'));
 132
 133                 if (!(strlen($server) && strlen($user))) {
 134                         return false;
 135                 }
 136
 137                 $this->emulate_prepares = (bool)$this->configCache->get('database', 'emulate_prepares');
 138                 $this->pdo_emulate_prepares = (bool)$this->configCache->get('database', 'pdo_emulate_prepares');
 139
 140                 if (!$this->configCache->get('database', 'disable_pdo') && class_exists('\PDO') && in_array('mysql', PDO::getAvailableDrivers())) {
 141                         $this->driver = 'pdo';
 142                         $connect      = "mysql:host=" . $server . ";dbname=" . $db;
 143
 144                         if ($port > 0) {
 145                                 $connect .= ";port=" . $port;
 146                         }
 147
 148                         if ($charset) {
 149                                 $connect .= ";charset=" . $charset;
 150                         }
 151
 152                         try {
 153                                 $this->connection = @new PDO($connect, $user, $pass);
 154                                 $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, $this->pdo_emulate_prepares);
 155                                 $this->connected = true;
 156                         } catch (PDOException $e) {
 157                                 $this->connected = false;
 158                         }
 159                 }
 160
 161                 if (!$this->connected && class_exists('\mysqli')) {
 162                         $this->driver = 'mysqli';
 163
 164                         if ($port > 0) {
 165                                 $this->connection = @new mysqli($server, $user, $pass, $db, $port);
 166                         } else {
 167                                 $this->connection = @new mysqli($server, $user, $pass, $db);
 168                         }
 169
 170                         if (!mysqli_connect_errno()) {
 171                                 $this->connected = true;
 172
 173                                 if ($charset) {
 174                                         $this->connection->set_charset($charset);
 175                                 }
 176                         }
 177                 }
 178
 179                 // No suitable SQL driver was found.
 180                 if (!$this->connected) {
 181                         $this->driver     = null;
 182                         $this->connection = null;
 183                 }
 184
 185                 return $this->connected;
 186         }
 187
 188         public function setTestmode(bool $test)
 189         {
 190                 $this->testmode = $test;
 191         }
 192         /**
 193          * Sets the logger for DBA
 194          *
 195          * @note this is necessary because if we want to load the logger configuration
 196          *       from the DB, but there's an error, we would print out an exception.
 197          *       So the logger gets updated after the logger configuration can be retrieved
 198          *       from the database
 199          *
 200          * @param LoggerInterface $logger
 201          */
 202         public function setLogger(LoggerInterface $logger)
 203         {
 204                 $this->logger = $logger;
 205         }
 206
 207         /**
 208          * Sets the profiler for DBA
 209          *
 210          * @param Profiler $profiler
 211          */
 212         public function setProfiler(Profiler $profiler)
 213         {
 214                 $this->profiler = $profiler;
 215         }
 216         /**
 217          * Disconnects the current database connection
 218          */
 219         public function disconnect()
 220         {
 221                 if (!is_null($this->connection)) {
 222                         switch ($this->driver) {
 223                                 case 'pdo':
 224                                         $this->connection = null;
 225                                         break;
 226                                 case 'mysqli':
 227                                         $this->connection->close();
 228                                         $this->connection = null;
 229                                         break;
 230                         }
 231                 }
 232
 233                 $this->driver    = null;
 234                 $this->connected = false;
 235         }
 236
 237         /**
 238          * Perform a reconnect of an existing database connection
 239          */
 240         public function reconnect()
 241         {
 242                 $this->disconnect();
 243                 return $this->connect();
 244         }
 245
 246         /**
 247          * Return the database object.
 248          *
 249          * @return PDO|mysqli
 250          */
 251         public function getConnection()
 252         {
 253                 return $this->connection;
 254         }
 255
 256         /**
 257          * Returns the MySQL server version string
 258          *
 259          * This function discriminate between the deprecated mysql API and the current
 260          * object-oriented mysqli API. Example of returned string: 5.5.46-0+deb8u1
 261          *
 262          * @return string
 263          */
 264         public function serverInfo()
 265         {
 266                 if ($this->server_info == '') {
 267                         switch ($this->driver) {
 268                                 case 'pdo':
 269                                         $this->server_info = $this->connection->getAttribute(PDO::ATTR_SERVER_VERSION);
 270                                         break;
 271                                 case 'mysqli':
 272                                         $this->server_info = $this->connection->server_info;
 273                                         break;
 274                         }
 275                 }
 276                 return $this->server_info;
 277         }
 278
 279         /**
 280          * Returns the selected database name
 281          *
 282          * @return string
 283          * @throws \Exception
 284          */
 285         public function databaseName()
 286         {
 287                 $ret  = $this->p("SELECT DATABASE() AS `db`");
 288                 $data = $this->toArray($ret);
 289                 return $data[0]['db'];
 290         }
 291
 292         /**
 293          * Analyze a database query and log this if some conditions are met.
 294          *
 295          * @param string $query The database query that will be analyzed
 296          *
 297          * @throws \Exception
 298          */
 299         private function logIndex($query)
 300         {
 301
 302                 if (!$this->configCache->get('system', 'db_log_index')) {
 303                         return;
 304                 }
 305
 306                 // Don't explain an explain statement
 307                 if (strtolower(substr($query, 0, 7)) == "explain") {
 308                         return;
 309                 }
 310
 311                 // Only do the explain on "select", "update" and "delete"
 312                 if (!in_array(strtolower(substr($query, 0, 6)), ["select", "update", "delete"])) {
 313                         return;
 314                 }
 315
 316                 $r = $this->p("EXPLAIN " . $query);
 317                 if (!$this->isResult($r)) {
 318                         return;
 319                 }
 320
 321                 $watchlist = explode(',', $this->configCache->get('system', 'db_log_index_watch'));
 322                 $blacklist = explode(',', $this->configCache->get('system', 'db_log_index_blacklist'));
 323
 324                 while ($row = $this->fetch($r)) {
 325                         if ((intval($this->configCache->get('system', 'db_loglimit_index')) > 0)) {
 326                                 $log = (in_array($row['key'], $watchlist) &&
 327                                         ($row['rows'] >= intval($this->configCache->get('system', 'db_loglimit_index'))));
 328                         } else {
 329                                 $log = false;
 330                         }
 331
 332                         if ((intval($this->configCache->get('system', 'db_loglimit_index_high')) > 0) && ($row['rows'] >= intval($this->configCache->get('system', 'db_loglimit_index_high')))) {
 333                                 $log = true;
 334                         }
 335
 336                         if (in_array($row['key'], $blacklist) || ($row['key'] == "")) {
 337                                 $log = false;
 338                         }
 339
 340                         if ($log) {
 341                                 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
 342                                 @file_put_contents($this->configCache->get('system', 'db_log_index'), DateTimeFormat::utcNow() . "\t" .
 343                                                                                                       $row['key'] . "\t" . $row['rows'] . "\t" . $row['Extra'] . "\t" .
 344                                                                                                       basename($backtrace[1]["file"]) . "\t" .
 345                                                                                                       $backtrace[1]["line"] . "\t" . $backtrace[2]["function"] . "\t" .
 346                                                                                                       substr($query, 0, 2000) . "\n", FILE_APPEND);
 347                         }
 348                 }
 349         }
 350
 351         /**
 352          * Removes every not whitelisted character from the identifier string
 353          *
 354          * @param string $identifier
 355          *
 356          * @return string sanitized identifier
 357          * @throws \Exception
 358          */
 359         private function sanitizeIdentifier($identifier)
 360         {
 361                 return preg_replace('/[^A-Za-z0-9_\-]+/', '', $identifier);
 362         }
 363
 364         public function escape($str)
 365         {
 366                 if ($this->connected) {
 367                         switch ($this->driver) {
 368                                 case 'pdo':
 369                                         return substr(@$this->connection->quote($str, PDO::PARAM_STR), 1, -1);
 370
 371                                 case 'mysqli':
 372                                         return @$this->connection->real_escape_string($str);
 373                         }
 374                 } else {
 375                         return str_replace("'", "\\'", $str);
 376                 }
 377         }
 378
 379         public function isConnected()
 380         {
 381                 return $this->connected;
 382         }
 383
 384         public function connected()
 385         {
 386                 $connected = false;
 387
 388                 if (is_null($this->connection)) {
 389                         return false;
 390                 }
 391
 392                 switch ($this->driver) {
 393                         case 'pdo':
 394                                 $r = $this->p("SELECT 1");
 395                                 if ($this->isResult($r)) {
 396                                         $row       = $this->toArray($r);
 397                                         $connected = ($row[0]['1'] == '1');
 398                                 }
 399                                 break;
 400                         case 'mysqli':
 401                                 $connected = $this->connection->ping();
 402                                 break;
 403                 }
 404
 405                 return $connected;
 406         }
 407
 408         /**
 409          * Replaces ANY_VALUE() function by MIN() function,
 410          * if the database server does not support ANY_VALUE().
 411          *
 412          * Considerations for Standard SQL, or MySQL with ONLY_FULL_GROUP_BY (default since 5.7.5).
 413          * ANY_VALUE() is available from MySQL 5.7.5 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html
 414          * A standard fall-back is to use MIN().
 415          *
 416          * @param string $sql An SQL string without the values
 417          *
 418          * @return string The input SQL string modified if necessary.
 419          */
 420         public function anyValueFallback($sql)
 421         {
 422                 $server_info = $this->serverInfo();
 423                 if (version_compare($server_info, '5.7.5', '<') ||
 424                     (stripos($server_info, 'MariaDB') !== false)) {
 425                         $sql = str_ireplace('ANY_VALUE(', 'MIN(', $sql);
 426                 }
 427                 return $sql;
 428         }
 429
 430         /**
 431          * Replaces the ? placeholders with the parameters in the $args array
 432          *
 433          * @param string $sql  SQL query
 434          * @param array  $args The parameters that are to replace the ? placeholders
 435          *
 436          * @return string The replaced SQL query
 437          */
 438         private function replaceParameters($sql, $args)
 439         {
 440                 $offset = 0;
 441                 foreach ($args AS $param => $value) {
 442                         if (is_int($args[$param]) || is_float($args[$param]) || is_bool($args[$param])) {
 443                                 $replace = intval($args[$param]);
 444                         } elseif (is_null($args[$param])) {
 445                                 $replace = 'NULL';
 446                         } else {
 447                                 $replace = "'" . $this->escape($args[$param]) . "'";
 448                         }
 449
 450                         $pos = strpos($sql, '?', $offset);
 451                         if ($pos !== false) {
 452                                 $sql = substr_replace($sql, $replace, $pos, 1);
 453                         }
 454                         $offset = $pos + strlen($replace);
 455                 }
 456                 return $sql;
 457         }
 458
 459         /**
 460          * Executes a prepared statement that returns data
 461          *
 462          * @usage Example: $r = p("SELECT * FROM `item` WHERE `guid` = ?", $guid);
 463          *
 464          * Please only use it with complicated queries.
 465          * For all regular queries please use DBA::select or DBA::exists
 466          *
 467          * @param string $sql SQL statement
 468          *
 469          * @return bool|object statement object or result object
 470          * @throws \Exception
 471          */
 472         public function p($sql)
 473         {
 474
 475                 $stamp1 = microtime(true);
 476
 477                 $params = DBA::getParam(func_get_args());
 478
 479                 // Renumber the array keys to be sure that they fit
 480                 $i    = 0;
 481                 $args = [];
 482                 foreach ($params AS $param) {
 483                         // Avoid problems with some MySQL servers and boolean values. See issue #3645
 484                         if (is_bool($param)) {
 485                                 $param = (int)$param;
 486                         }
 487                         $args[++$i] = $param;
 488                 }
 489
 490                 if (!$this->connected) {
 491                         return false;
 492                 }
 493
 494                 if ((substr_count($sql, '?') != count($args)) && (count($args) > 0)) {
 495                         // Question: Should we continue or stop the query here?
 496                         $this->logger->warning('Query parameters mismatch.', ['query' => $sql, 'args' => $args, 'callstack' => System::callstack()]);
 497                 }
 498
 499                 $sql = DBA::cleanQuery($sql);
 500                 $sql = $this->anyValueFallback($sql);
 501
 502                 $orig_sql = $sql;
 503
 504                 if ($this->configCache->get('system', 'db_callstack') !== null) {
 505                         $sql = "/*" . System::callstack() . " */ " . $sql;
 506                 }
 507
 508                 $is_error            = false;
 509                 $this->error         = '';
 510                 $this->errorno       = 0;
 511                 $this->affected_rows = 0;
 512
 513                 // We have to make some things different if this function is called from "e"
 514                 $trace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 2);
 515
 516                 if (isset($trace[1])) {
 517                         $called_from = $trace[1];
 518                 } else {
 519                         // We use just something that is defined to avoid warnings
 520                         $called_from = $trace[0];
 521                 }
 522                 // We are having an own error logging in the function "e"
 523                 $called_from_e = ($called_from['function'] == 'e');
 524
 525                 if (!isset($this->connection)) {
 526                         throw new InternalServerErrorException('The Connection is empty, although connected is set true.');
 527                 }
 528
 529                 switch ($this->driver) {
 530                         case 'pdo':
 531                                 // If there are no arguments we use "query"
 532                                 if ($this->emulate_prepares || count($args) == 0) {
 533                                         if (!$retval = $this->connection->query($this->replaceParameters($sql, $args))) {
 534                                                 $errorInfo     = $this->connection->errorInfo();
 535                                                 $this->error   = $errorInfo[2];
 536                                                 $this->errorno = $errorInfo[1];
 537                                                 $retval        = false;
 538                                                 $is_error      = true;
 539                                                 break;
 540                                         }
 541                                         $this->affected_rows = $retval->rowCount();
 542                                         break;
 543                                 }
 544
 545                                 /** @var $stmt mysqli_stmt|PDOStatement */
 546                                 if (!$stmt = $this->connection->prepare($sql)) {
 547                                         $errorInfo     = $this->connection->errorInfo();
 548                                         $this->error   = $errorInfo[2];
 549                                         $this->errorno = $errorInfo[1];
 550                                         $retval        = false;
 551                                         $is_error      = true;
 552                                         break;
 553                                 }
 554
 555                                 foreach ($args AS $param => $value) {
 556                                         if (is_int($args[$param])) {
 557                                                 $data_type = PDO::PARAM_INT;
 558                                         } else {
 559                                                 $data_type = PDO::PARAM_STR;
 560                                         }
 561                                         $stmt->bindParam($param, $args[$param], $data_type);
 562                                 }
 563
 564                                 if (!$stmt->execute()) {
 565                                         $errorInfo     = $stmt->errorInfo();
 566                                         $this->error   = $errorInfo[2];
 567                                         $this->errorno = $errorInfo[1];
 568                                         $retval        = false;
 569                                         $is_error      = true;
 570                                 } else {
 571                                         $retval              = $stmt;
 572                                         $this->affected_rows = $retval->rowCount();
 573                                 }
 574                                 break;
 575                         case 'mysqli':
 576                                 // There are SQL statements that cannot be executed with a prepared statement
 577                                 $parts           = explode(' ', $orig_sql);
 578                                 $command         = strtolower($parts[0]);
 579                                 $can_be_prepared = in_array($command, ['select', 'update', 'insert', 'delete']);
 580
 581                                 // The fallback routine is called as well when there are no arguments
 582                                 if ($this->emulate_prepares || !$can_be_prepared || (count($args) == 0)) {
 583                                         $retval = $this->connection->query($this->replaceParameters($sql, $args));
 584                                         if ($this->connection->errno) {
 585                                                 $this->error   = $this->connection->error;
 586                                                 $this->errorno = $this->connection->errno;
 587                                                 $retval        = false;
 588                                                 $is_error      = true;
 589                                         } else {
 590                                                 if (isset($retval->num_rows)) {
 591                                                         $this->affected_rows = $retval->num_rows;
 592                                                 } else {
 593                                                         $this->affected_rows = $this->connection->affected_rows;
 594                                                 }
 595                                         }
 596                                         break;
 597                                 }
 598
 599                                 $stmt = $this->connection->stmt_init();
 600
 601                                 if (!$stmt->prepare($sql)) {
 602                                         $this->error   = $stmt->error;
 603                                         $this->errorno = $stmt->errno;
 604                                         $retval        = false;
 605                                         $is_error      = true;
 606                                         break;
 607                                 }
 608
 609                                 $param_types = '';
 610                                 $values      = [];
 611                                 foreach ($args AS $param => $value) {
 612                                         if (is_int($args[$param])) {
 613                                                 $param_types .= 'i';
 614                                         } elseif (is_float($args[$param])) {
 615                                                 $param_types .= 'd';
 616                                         } elseif (is_string($args[$param])) {
 617                                                 $param_types .= 's';
 618                                         } else {
 619                                                 $param_types .= 'b';
 620                                         }
 621                                         $values[] = &$args[$param];
 622                                 }
 623
 624                                 if (count($values) > 0) {
 625                                         array_unshift($values, $param_types);
 626                                         call_user_func_array([$stmt, 'bind_param'], $values);
 627                                 }
 628
 629                                 if (!$stmt->execute()) {
 630                                         $this->error   = $this->connection->error;
 631                                         $this->errorno = $this->connection->errno;
 632                                         $retval        = false;
 633                                         $is_error      = true;
 634                                 } else {
 635                                         $stmt->store_result();
 636                                         $retval              = $stmt;
 637                                         $this->affected_rows = $retval->affected_rows;
 638                                 }
 639                                 break;
 640                 }
 641
 642                 // See issue https://github.com/friendica/friendica/issues/8572
 643                 // Ensure that we always get an error message on an error.
 644                 if ($is_error && empty($this->errorno)) {
 645                         $this->errorno = -1;
 646                 }
 647
 648                 if ($is_error && empty($this->error)) {
 649                         $this->error = 'Unknown database error';
 650                 }
 651
 652                 // We are having an own error logging in the function "e"
 653                 if (($this->errorno != 0) && !$called_from_e) {
 654                         // We have to preserve the error code, somewhere in the logging it get lost
 655                         $error   = $this->error;
 656                         $errorno = $this->errorno;
 657
 658                         if ($this->testmode) {
 659                                 throw new Exception(DI::l10n()->t('Database error %d "%s" at "%s"', $errorno, $error, $this->replaceParameters($sql, $args)));
 660                         }
 661
 662                         $this->logger->error('DB Error', [
 663                                 'code'      => $errorno,
 664                                 'error'     => $error,
 665                                 'callstack' => System::callstack(8),
 666                                 'params'    => $this->replaceParameters($sql, $args),
 667                         ]);
 668
 669                         // On a lost connection we try to reconnect - but only once.
 670                         if ($errorno == 2006) {
 671                                 if ($this->in_retrial || !$this->reconnect()) {
 672                                         // It doesn't make sense to continue when the database connection was lost
 673                                         if ($this->in_retrial) {
 674                                                 $this->logger->notice('Giving up retrial because of database error', [
 675                                                         'code'  => $errorno,
 676                                                         'error' => $error,
 677                                                 ]);
 678                                         } else {
 679                                                 $this->logger->notice('Couldn\'t reconnect after database error', [
 680                                                         'code'  => $errorno,
 681                                                         'error' => $error,
 682                                                 ]);
 683                                         }
 684                                         exit(1);
 685                                 } else {
 686                                         // We try it again
 687                                         $this->logger->notice('Reconnected after database error', [
 688                                                 'code'  => $errorno,
 689                                                 'error' => $error,
 690                                         ]);
 691                                         $this->in_retrial = true;
 692                                         $ret              = $this->p($sql, $args);
 693                                         $this->in_retrial = false;
 694                                         return $ret;
 695                                 }
 696                         }
 697
 698                         $this->error   = $error;
 699                         $this->errorno = $errorno;
 700                 }
 701
 702                 $this->profiler->saveTimestamp($stamp1, 'database', System::callstack());
 703
 704                 if ($this->configCache->get('system', 'db_log')) {
 705                         $stamp2   = microtime(true);
 706                         $duration = (float)($stamp2 - $stamp1);
 707
 708                         if (($duration > $this->configCache->get('system', 'db_loglimit'))) {
 709                                 $duration  = round($duration, 3);
 710                                 $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
 711
 712                                 @file_put_contents($this->configCache->get('system', 'db_log'), DateTimeFormat::utcNow() . "\t" . $duration . "\t" .
 713                                                                                                 basename($backtrace[1]["file"]) . "\t" .
 714                                                                                                 $backtrace[1]["line"] . "\t" . $backtrace[2]["function"] . "\t" .
 715                                                                                                 substr($this->replaceParameters($sql, $args), 0, 2000) . "\n", FILE_APPEND);
 716                         }
 717                 }
 718                 return $retval;
 719         }
 720
 721         /**
 722          * Executes a prepared statement like UPDATE or INSERT that doesn't return data
 723          *
 724          * Please use DBA::delete, DBA::insert, DBA::update, ... instead
 725          *
 726          * @param string $sql SQL statement
 727          *
 728          * @return boolean Was the query successfull? False is returned only if an error occurred
 729          * @throws \Exception
 730          */
 731         public function e($sql)
 732         {
 733
 734                 $stamp = microtime(true);
 735
 736                 $params = DBA::getParam(func_get_args());
 737
 738                 // In a case of a deadlock we are repeating the query 20 times
 739                 $timeout = 20;
 740
 741                 do {
 742                         $stmt = $this->p($sql, $params);
 743
 744                         if (is_bool($stmt)) {
 745                                 $retval = $stmt;
 746                         } elseif (is_object($stmt)) {
 747                                 $retval = true;
 748                         } else {
 749                                 $retval = false;
 750                         }
 751
 752                         $this->close($stmt);
 753
 754                 } while (($this->errorno == 1213) && (--$timeout > 0));
 755
 756                 if ($this->errorno != 0) {
 757                         // We have to preserve the error code, somewhere in the logging it get lost
 758                         $error   = $this->error;
 759                         $errorno = $this->errorno;
 760
 761                         if ($this->testmode) {
 762                                 throw new Exception(DI::l10n()->t('Database error %d "%s" at "%s"', $errorno, $error, $this->replaceParameters($sql, $params)));
 763                         }
 764
 765                         $this->logger->error('DB Error', [
 766                                 'code'      => $errorno,
 767                                 'error'     => $error,
 768                                 'callstack' => System::callstack(8),
 769                                 'params'    => $this->replaceParameters($sql, $params),
 770                         ]);
 771
 772                         // On a lost connection we simply quit.
 773                         // A reconnect like in $this->p could be dangerous with modifications
 774                         if ($errorno == 2006) {
 775                                 $this->logger->notice('Giving up because of database error', [
 776                                         'code'  => $errorno,
 777                                         'error' => $error,
 778                                 ]);
 779                                 exit(1);
 780                         }
 781
 782                         $this->error   = $error;
 783                         $this->errorno = $errorno;
 784                 }
 785
 786                 $this->profiler->saveTimestamp($stamp, "database_write", System::callstack());
 787
 788                 return $retval;
 789         }
 790
 791         /**
 792          * Check if data exists
 793          *
 794          * @param string|array $table     Table name or array [schema => table]
 795          * @param array        $condition array of fields for condition
 796          *
 797          * @return boolean Are there rows for that condition?
 798          * @throws \Exception
 799          */
 800         public function exists($table, $condition)
 801         {
 802                 if (empty($table)) {
 803                         return false;
 804                 }
 805
 806                 $fields = [];
 807
 808                 if (empty($condition)) {
 809                         return DBStructure::existsTable($table);
 810                 }
 811
 812                 reset($condition);
 813                 $first_key = key($condition);
 814                 if (!is_int($first_key)) {
 815                         $fields = [$first_key];
 816                 }
 817
 818                 $stmt = $this->select($table, $fields, $condition, ['limit' => 1]);
 819
 820                 if (is_bool($stmt)) {
 821                         $retval = $stmt;
 822                 } else {
 823                         $retval = ($this->numRows($stmt) > 0);
 824                 }
 825
 826                 $this->close($stmt);
 827
 828                 return $retval;
 829         }
 830
 831         /**
 832          * Fetches the first row
 833          *
 834          * Please use DBA::selectFirst or DBA::exists whenever this is possible.
 835          *
 836          * Fetches the first row
 837          *
 838          * @param string $sql SQL statement
 839          *
 840          * @return array first row of query
 841          * @throws \Exception
 842          */
 843         public function fetchFirst($sql)
 844         {
 845                 $params = DBA::getParam(func_get_args());
 846
 847                 $stmt = $this->p($sql, $params);
 848
 849                 if (is_bool($stmt)) {
 850                         $retval = $stmt;
 851                 } else {
 852                         $retval = $this->fetch($stmt);
 853                 }
 854
 855                 $this->close($stmt);
 856
 857                 return $retval;
 858         }
 859
 860         /**
 861          * Returns the number of affected rows of the last statement
 862          *
 863          * @return int Number of rows
 864          */
 865         public function affectedRows()
 866         {
 867                 return $this->affected_rows;
 868         }
 869
 870         /**
 871          * Returns the number of columns of a statement
 872          *
 873          * @param object Statement object
 874          *
 875          * @return int Number of columns
 876          */
 877         public function columnCount($stmt)
 878         {
 879                 if (!is_object($stmt)) {
 880                         return 0;
 881                 }
 882                 switch ($this->driver) {
 883                         case 'pdo':
 884                                 return $stmt->columnCount();
 885                         case 'mysqli':
 886                                 return $stmt->field_count;
 887                 }
 888                 return 0;
 889         }
 890
 891         /**
 892          * Returns the number of rows of a statement
 893          *
 894          * @param PDOStatement|mysqli_result|mysqli_stmt Statement object
 895          *
 896          * @return int Number of rows
 897          */
 898         public function numRows($stmt)
 899         {
 900                 if (!is_object($stmt)) {
 901                         return 0;
 902                 }
 903                 switch ($this->driver) {
 904                         case 'pdo':
 905                                 return $stmt->rowCount();
 906                         case 'mysqli':
 907                                 return $stmt->num_rows;
 908                 }
 909                 return 0;
 910         }
 911
 912         /**
 913          * Fetch a single row
 914          *
 915          * @param mixed $stmt statement object
 916          *
 917          * @return array current row
 918          */
 919         public function fetch($stmt)
 920         {
 921
 922                 $stamp1 = microtime(true);
 923
 924                 $columns = [];
 925
 926                 if (!is_object($stmt)) {
 927                         return false;
 928                 }
 929
 930                 switch ($this->driver) {
 931                         case 'pdo':
 932                                 $columns = $stmt->fetch(PDO::FETCH_ASSOC);
 933                                 break;
 934                         case 'mysqli':
 935                                 if (get_class($stmt) == 'mysqli_result') {
 936                                         $columns = $stmt->fetch_assoc();
 937                                         break;
 938                                 }
 939
 940                                 // This code works, but is slow
 941
 942                                 // Bind the result to a result array
 943                                 $cols = [];
 944
 945                                 $cols_num = [];
 946                                 for ($x = 0; $x < $stmt->field_count; $x++) {
 947                                         $cols[] = &$cols_num[$x];
 948                                 }
 949
 950                                 call_user_func_array([$stmt, 'bind_result'], $cols);
 951
 952                                 if (!$stmt->fetch()) {
 953                                         return false;
 954                                 }
 955
 956                                 // The slow part:
 957                                 // We need to get the field names for the array keys
 958                                 // It seems that there is no better way to do this.
 959                                 $result = $stmt->result_metadata();
 960                                 $fields = $result->fetch_fields();
 961
 962                                 foreach ($cols_num AS $param => $col) {
 963                                         $columns[$fields[$param]->name] = $col;
 964                                 }
 965                 }
 966
 967                 $this->profiler->saveTimestamp($stamp1, 'database', System::callstack());
 968
 969                 return $columns;
 970         }
 971
 972         /**
 973          * Insert a row into a table
 974          *
 975          * @param string|array $table               Table name or array [schema => table]
 976          * @param array        $param               parameter array
 977          * @param bool         $on_duplicate_update Do an update on a duplicate entry
 978          *
 979          * @return boolean was the insert successful?
 980          * @throws \Exception
 981          */
 982         public function insert($table, $param, $on_duplicate_update = false)
 983         {
 984                 if (empty($table) || empty($param)) {
 985                         $this->logger->info('Table and fields have to be set');
 986                         return false;
 987                 }
 988
 989                 $table_string = DBA::buildTableString($table);
 990
 991                 $fields_string = implode(', ', array_map([DBA::class, 'quoteIdentifier'], array_keys($param)));
 992
 993                 $values_string = substr(str_repeat("?, ", count($param)), 0, -2);
 994
 995                 $sql = "INSERT INTO " . $table_string . " (" . $fields_string . ") VALUES (" . $values_string . ")";
 996
 997                 if ($on_duplicate_update) {
 998                         $fields_string = implode(' = ?, ', array_map([DBA::class, 'quoteIdentifier'], array_keys($param)));
 999
1000                         $sql .= " ON DUPLICATE KEY UPDATE " . $fields_string . " = ?";
1001
1002                         $values = array_values($param);
1003                         $param  = array_merge_recursive($values, $values);
1004                 }
1005
1006                 return $this->e($sql, $param);
1007         }
1008
1009         /**
1010          * Fetch the id of the last insert command
1011          *
1012          * @return integer Last inserted id
1013          */
1014         public function lastInsertId()
1015         {
1016                 switch ($this->driver) {
1017                         case 'pdo':
1018                                 $id = $this->connection->lastInsertId();
1019                                 break;
1020                         case 'mysqli':
1021                                 $id = $this->connection->insert_id;
1022                                 break;
1023                 }
1024                 return $id;
1025         }
1026
1027         /**
1028          * Locks a table for exclusive write access
1029          *
1030          * This function can be extended in the future to accept a table array as well.
1031          *
1032          * @param string|array $table Table name or array [schema => table]
1033          *
1034          * @return boolean was the lock successful?
1035          * @throws \Exception
1036          */
1037         public function lock($table)
1038         {
1039                 // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
1040                 if ($this->driver == 'pdo') {
1041                         $this->e("SET autocommit=0");
1042                         $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
1043                 } else {
1044                         $this->connection->autocommit(false);
1045                 }
1046
1047                 $success = $this->e("LOCK TABLES " . DBA::buildTableString($table) . " WRITE");
1048
1049                 if ($this->driver == 'pdo') {
1050                         $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, $this->pdo_emulate_prepares);
1051                 }
1052
1053                 if (!$success) {
1054                         if ($this->driver == 'pdo') {
1055                                 $this->e("SET autocommit=1");
1056                         } else {
1057                                 $this->connection->autocommit(true);
1058                         }
1059                 } else {
1060                         $this->in_transaction = true;
1061                 }
1062                 return $success;
1063         }
1064
1065         /**
1066          * Unlocks all locked tables
1067          *
1068          * @return boolean was the unlock successful?
1069          * @throws \Exception
1070          */
1071         public function unlock()
1072         {
1073                 // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
1074                 $this->performCommit();
1075
1076                 if ($this->driver == 'pdo') {
1077                         $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
1078                 }
1079
1080                 $success = $this->e("UNLOCK TABLES");
1081
1082                 if ($this->driver == 'pdo') {
1083                         $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, $this->pdo_emulate_prepares);
1084                         $this->e("SET autocommit=1");
1085                 } else {
1086                         $this->connection->autocommit(true);
1087                 }
1088
1089                 $this->in_transaction = false;
1090                 return $success;
1091         }
1092
1093         /**
1094          * Starts a transaction
1095          *
1096          * @return boolean Was the command executed successfully?
1097          */
1098         public function transaction()
1099         {
1100                 if (!$this->performCommit()) {
1101                         return false;
1102                 }
1103
1104                 switch ($this->driver) {
1105                         case 'pdo':
1106                                 if (!$this->connection->inTransaction() && !$this->connection->beginTransaction()) {
1107                                         return false;
1108                                 }
1109                                 break;
1110
1111                         case 'mysqli':
1112                                 if (!$this->connection->begin_transaction()) {
1113                                         return false;
1114                                 }
1115                                 break;
1116                 }
1117
1118                 $this->in_transaction = true;
1119                 return true;
1120         }
1121
1122         protected function performCommit()
1123         {
1124                 switch ($this->driver) {
1125                         case 'pdo':
1126                                 if (!$this->connection->inTransaction()) {
1127                                         return true;
1128                                 }
1129
1130                                 return $this->connection->commit();
1131
1132                         case 'mysqli':
1133                                 return $this->connection->commit();
1134                 }
1135
1136                 return true;
1137         }
1138
1139         /**
1140          * Does a commit
1141          *
1142          * @return boolean Was the command executed successfully?
1143          */
1144         public function commit()
1145         {
1146                 if (!$this->performCommit()) {
1147                         return false;
1148                 }
1149                 $this->in_transaction = false;
1150                 return true;
1151         }
1152
1153         /**
1154          * Does a rollback
1155          *
1156          * @return boolean Was the command executed successfully?
1157          */
1158         public function rollback()
1159         {
1160                 $ret = false;
1161
1162                 switch ($this->driver) {
1163                         case 'pdo':
1164                                 if (!$this->connection->inTransaction()) {
1165                                         $ret = true;
1166                                         break;
1167                                 }
1168                                 $ret = $this->connection->rollBack();
1169                                 break;
1170
1171                         case 'mysqli':
1172                                 $ret = $this->connection->rollback();
1173                                 break;
1174                 }
1175                 $this->in_transaction = false;
1176                 return $ret;
1177         }
1178
1179         /**
1180          * Build the array with the table relations
1181          *
1182          * The array is build from the database definitions in DBStructure.php
1183          *
1184          * This process must only be started once, since the value is cached.
1185          */
1186         private function buildRelationData()
1187         {
1188                 $definition = DBStructure::definition($this->configCache->get('system', 'basepath'));
1189
1190                 foreach ($definition AS $table => $structure) {
1191                         foreach ($structure['fields'] AS $field => $field_struct) {
1192                                 if (isset($field_struct['relation'])) {
1193                                         foreach ($field_struct['relation'] AS $rel_table => $rel_field) {
1194                                                 $this->relation[$rel_table][$rel_field][$table][] = $field;
1195                                         }
1196                                 }
1197                         }
1198                 }
1199         }
1200
1201         /**
1202          * Delete a row from a table
1203          *
1204          * Note: this methods does NOT accept schema => table arrays because of the complex relation stuff.
1205          *
1206          * @param string $table      Table name
1207          * @param array  $conditions Field condition(s)
1208          * @param array  $options
1209          *                           - cascade: If true we delete records in other tables that depend on the one we're deleting through
1210          *                           relations (default: true)
1211          * @param array  $callstack  Internal use: prevent endless loops
1212          *
1213          * @return boolean was the delete successful?
1214          * @throws \Exception
1215          */
1216         public function delete($table, array $conditions, array $options = [], array &$callstack = [])
1217         {
1218                 if (empty($table) || empty($conditions)) {
1219                         $this->logger->info('Table and conditions have to be set');
1220                         return false;
1221                 }
1222
1223                 $commands = [];
1224
1225                 // Create a key for the loop prevention
1226                 $key = $table . ':' . json_encode($conditions);
1227
1228                 // We quit when this key already exists in the callstack.
1229                 if (isset($callstack[$key])) {
1230                         return true;
1231                 }
1232
1233                 $callstack[$key] = true;
1234
1235                 $commands[$key] = ['table' => $table, 'conditions' => $conditions];
1236
1237                 // Don't use "defaults" here, since it would set "false" to "true"
1238                 if (isset($options['cascade'])) {
1239                         $cascade = $options['cascade'];
1240                 } else {
1241                         $cascade = true;
1242                 }
1243
1244                 // To speed up the whole process we cache the table relations
1245                 if ($cascade && count($this->relation) == 0) {
1246                         $this->buildRelationData();
1247                 }
1248
1249                 // Is there a relation entry for the table?
1250                 if ($cascade && isset($this->relation[$table])) {
1251                         // We only allow a simple "one field" relation.
1252                         $field   = array_keys($this->relation[$table])[0];
1253                         $rel_def = array_values($this->relation[$table])[0];
1254
1255                         // Create a key for preventing double queries
1256                         $qkey = $field . '-' . $table . ':' . json_encode($conditions);
1257
1258                         // When the search field is the relation field, we don't need to fetch the rows
1259                         // This is useful when the leading record is already deleted in the frontend but the rest is done in the backend
1260                         if ((count($conditions) == 1) && ($field == array_keys($conditions)[0])) {
1261                                 foreach ($rel_def AS $rel_table => $rel_fields) {
1262                                         foreach ($rel_fields AS $rel_field) {
1263                                                 $this->delete($rel_table, [$rel_field => array_values($conditions)[0]], $options, $callstack);
1264                                         }
1265                                 }
1266                                 // We quit when this key already exists in the callstack.
1267                         } elseif (!isset($callstack[$qkey])) {
1268                                 $callstack[$qkey] = true;
1269
1270                                 // Fetch all rows that are to be deleted
1271                                 $data = $this->select($table, [$field], $conditions);
1272
1273                                 while ($row = $this->fetch($data)) {
1274                                         $this->delete($table, [$field => $row[$field]], $options, $callstack);
1275                                 }
1276
1277                                 $this->close($data);
1278
1279                                 // Since we had split the delete command we don't need the original command anymore
1280                                 unset($commands[$key]);
1281                         }
1282                 }
1283
1284                 // Now we finalize the process
1285                 $do_transaction = !$this->in_transaction;
1286
1287                 if ($do_transaction) {
1288                         $this->transaction();
1289                 }
1290
1291                 $compacted = [];
1292                 $counter   = [];
1293
1294                 foreach ($commands AS $command) {
1295                         $conditions = $command['conditions'];
1296                         reset($conditions);
1297                         $first_key = key($conditions);
1298
1299                         $condition_string = DBA::buildCondition($conditions);
1300
1301                         if ((count($command['conditions']) > 1) || is_int($first_key)) {
1302                                 $sql = "DELETE FROM " . DBA::quoteIdentifier($command['table']) . " " . $condition_string;
1303                                 $this->logger->info($this->replaceParameters($sql, $conditions), ['callstack' => System::callstack(6), 'internal_callstack' => $callstack]);
1304
1305                                 if (!$this->e($sql, $conditions)) {
1306                                         if ($do_transaction) {
1307                                                 $this->rollback();
1308                                         }
1309                                         return false;
1310                                 }
1311                         } else {
1312                                 $key_table     = $command['table'];
1313                                 $key_condition = array_keys($command['conditions'])[0];
1314                                 $value         = array_values($command['conditions'])[0];
1315
1316                                 // Split the SQL queries in chunks of 100 values
1317                                 // We do the $i stuff here to make the code better readable
1318                                 $i = isset($counter[$key_table][$key_condition]) ? $counter[$key_table][$key_condition] : 0;
1319                                 if (isset($compacted[$key_table][$key_condition][$i]) && count($compacted[$key_table][$key_condition][$i]) > 100) {
1320                                         ++$i;
1321                                 }
1322
1323                                 $compacted[$key_table][$key_condition][$i][$value] = $value;
1324                                 $counter[$key_table][$key_condition]               = $i;
1325                         }
1326                 }
1327                 foreach ($compacted AS $table => $values) {
1328                         foreach ($values AS $field => $field_value_list) {
1329                                 foreach ($field_value_list AS $field_values) {
1330                                         $sql = "DELETE FROM " . DBA::quoteIdentifier($table) . " WHERE " . DBA::quoteIdentifier($field) . " IN (" .
1331                                                substr(str_repeat("?, ", count($field_values)), 0, -2) . ");";
1332
1333                                         $this->logger->info($this->replaceParameters($sql, $field_values), ['callstack' => System::callstack(6), 'internal_callstack' => $callstack]);
1334
1335                                         if (!$this->e($sql, $field_values)) {
1336                                                 if ($do_transaction) {
1337                                                         $this->rollback();
1338                                                 }
1339                                                 return false;
1340                                         }
1341                                 }
1342                         }
1343                 }
1344                 if ($do_transaction) {
1345                         $this->commit();
1346                 }
1347                 return true;
1348         }
1349
1350         /**
1351          * Updates rows
1352          *
1353          * Updates rows in the database. When $old_fields is set to an array,
1354          * the system will only do an update if the fields in that array changed.
1355          *
1356          * Attention:
1357          * Only the values in $old_fields are compared.
1358          * This is an intentional behaviour.
1359          *
1360          * Example:
1361          * We include the timestamp field in $fields but not in $old_fields.
1362          * Then the row will only get the new timestamp when the other fields had changed.
1363          *
1364          * When $old_fields is set to a boolean value the system will do this compare itself.
1365          * When $old_fields is set to "true" the system will do an insert if the row doesn't exists.
1366          *
1367          * Attention:
1368          * Only set $old_fields to a boolean value when you are sure that you will update a single row.
1369          * When you set $old_fields to "true" then $fields must contain all relevant fields!
1370          *
1371          * @param string|array  $table      Table name or array [schema => table]
1372          * @param array         $fields     contains the fields that are updated
1373          * @param array         $condition  condition array with the key values
1374          * @param array|boolean $old_fields array with the old field values that are about to be replaced (true = update on duplicate)
1375          *
1376          * @return boolean was the update successfull?
1377          * @throws \Exception
1378          */
1379         public function update($table, $fields, $condition, $old_fields = [])
1380         {
1381                 if (empty($table) || empty($fields) || empty($condition)) {
1382                         $this->logger->info('Table, fields and condition have to be set');
1383                         return false;
1384                 }
1385
1386                 if (is_bool($old_fields)) {
1387                         $do_insert = $old_fields;
1388
1389                         $old_fields = $this->selectFirst($table, [], $condition);
1390
1391                         if (is_bool($old_fields)) {
1392                                 if ($do_insert) {
1393                                         $values = array_merge($condition, $fields);
1394                                         return $this->insert($table, $values, $do_insert);
1395                                 }
1396                                 $old_fields = [];
1397                         }
1398                 }
1399
1400                 foreach ($old_fields AS $fieldname => $content) {
1401                         if (isset($fields[$fieldname]) && !is_null($content) && ($fields[$fieldname] == $content)) {
1402                                 unset($fields[$fieldname]);
1403                         }
1404                 }
1405
1406                 if (count($fields) == 0) {
1407                         return true;
1408                 }
1409
1410                 $table_string = DBA::buildTableString($table);
1411
1412                 $condition_string = DBA::buildCondition($condition);
1413
1414                 $sql = "UPDATE " . $table_string . " SET "
1415                         . implode(" = ?, ", array_map([DBA::class, 'quoteIdentifier'], array_keys($fields))) . " = ?"
1416                         . $condition_string;
1417
1418                 // Combines the updated fields parameter values with the condition parameter values
1419                 $params  = array_merge(array_values($fields), $condition);
1420
1421                 return $this->e($sql, $params);
1422         }
1423
1424         /**
1425          * Retrieve a single record from a table and returns it in an associative array
1426          *
1427          * @param string|array $table
1428          * @param array        $fields
1429          * @param array        $condition
1430          * @param array        $params
1431          *
1432          * @return bool|array
1433          * @throws \Exception
1434          * @see   $this->select
1435          */
1436         public function selectFirst($table, array $fields = [], array $condition = [], $params = [])
1437         {
1438                 $params['limit'] = 1;
1439                 $result          = $this->select($table, $fields, $condition, $params);
1440
1441                 if (is_bool($result)) {
1442                         return $result;
1443                 } else {
1444                         $row = $this->fetch($result);
1445                         $this->close($result);
1446                         return $row;
1447                 }
1448         }
1449
1450         /**
1451          * Select rows from a table and fills an array with the data
1452          *
1453          * @param string|array $table     Table name or array [schema => table]
1454          * @param array        $fields    Array of selected fields, empty for all
1455          * @param array        $condition Array of fields for condition
1456          * @param array        $params    Array of several parameters
1457          *
1458          * @return array Data array
1459          * @throws \Exception
1460          * @see   self::select
1461          */
1462         public function selectToArray($table, array $fields = [], array $condition = [], array $params = [])
1463         {
1464                 return $this->toArray($this->select($table, $fields, $condition, $params));
1465         }
1466
1467         /**
1468          * Select rows from a table
1469          *
1470          * @param string|array $table     Table name or array [schema => table]
1471          * @param array        $fields    Array of selected fields, empty for all
1472          * @param array        $condition Array of fields for condition
1473          * @param array        $params    Array of several parameters
1474          *
1475          * @return boolean|object
1476          *
1477          * Example:
1478          * $table = "item";
1479          * $fields = array("id", "uri", "uid", "network");
1480          *
1481          * $condition = array("uid" => 1, "network" => 'dspr');
1482          * or:
1483          * $condition = array("`uid` = ? AND `network` IN (?, ?)", 1, 'dfrn', 'dspr');
1484          *
1485          * $params = array("order" => array("id", "received" => true), "limit" => 10);
1486          *
1487          * $data = DBA::select($table, $fields, $condition, $params);
1488          * @throws \Exception
1489          */
1490         public function select($table, array $fields = [], array $condition = [], array $params = [])
1491         {
1492                 if (empty($table)) {
1493                         return false;
1494                 }
1495
1496                 if (count($fields) > 0) {
1497                         $select_string = implode(', ', array_map([DBA::class, 'quoteIdentifier'], $fields));
1498                 } else {
1499                         $select_string = '*';
1500                 }
1501
1502                 $table_string = DBA::buildTableString($table);
1503
1504                 $condition_string = DBA::buildCondition($condition);
1505
1506                 $param_string = DBA::buildParameter($params);
1507
1508                 $sql = "SELECT " . $select_string . " FROM " . $table_string . $condition_string . $param_string;
1509
1510                 $result = $this->p($sql, $condition);
1511
1512                 return $result;
1513         }
1514
1515         /**
1516          * Counts the rows from a table satisfying the provided condition
1517          *
1518          * @param string|array $table     Table name or array [schema => table]
1519          * @param array        $condition Array of fields for condition
1520          * @param array        $params    Array of several parameters
1521          *
1522          * @return int
1523          *
1524          * Example:
1525          * $table = "item";
1526          *
1527          * $condition = ["uid" => 1, "network" => 'dspr'];
1528          * or:
1529          * $condition = ["`uid` = ? AND `network` IN (?, ?)", 1, 'dfrn', 'dspr'];
1530          *
1531          * $count = DBA::count($table, $condition);
1532          * @throws \Exception
1533          */
1534         public function count($table, array $condition = [], array $params = [])
1535         {
1536                 if (empty($table)) {
1537                         return false;
1538                 }
1539
1540                 $table_string = DBA::buildTableString($table);
1541
1542                 $condition_string = DBA::buildCondition($condition);
1543
1544                 if (empty($params['expression'])) {
1545                         $expression = '*';
1546                 } elseif (!empty($params['distinct'])) {
1547                         $expression = "DISTINCT " . DBA::quoteIdentifier($params['expression']);
1548                 } else {
1549                         $expression = DBA::quoteIdentifier($params['expression']);
1550                 }
1551
1552                 $sql = "SELECT COUNT(" . $expression . ") AS `count` FROM " . $table_string . $condition_string;
1553
1554                 $row = $this->fetchFirst($sql, $condition);
1555
1556                 return $row['count'];
1557         }
1558
1559         /**
1560          * Fills an array with data from a query
1561          *
1562          * @param object $stmt statement object
1563          * @param bool   $do_close
1564          *
1565          * @return array Data array
1566          */
1567         public function toArray($stmt, $do_close = true)
1568         {
1569                 if (is_bool($stmt)) {
1570                         return [];
1571                 }
1572
1573                 $data = [];
1574                 while ($row = $this->fetch($stmt)) {
1575                         $data[] = $row;
1576                 }
1577
1578                 if ($do_close) {
1579                         $this->close($stmt);
1580                 }
1581
1582                 return $data;
1583         }
1584
1585         /**
1586          * Returns the error number of the last query
1587          *
1588          * @return string Error number (0 if no error)
1589          */
1590         public function errorNo()
1591         {
1592                 return $this->errorno;
1593         }
1594
1595         /**
1596          * Returns the error message of the last query
1597          *
1598          * @return string Error message ('' if no error)
1599          */
1600         public function errorMessage()
1601         {
1602                 return $this->error;
1603         }
1604
1605         /**
1606          * Closes the current statement
1607          *
1608          * @param object $stmt statement object
1609          *
1610          * @return boolean was the close successful?
1611          */
1612         public function close($stmt)
1613         {
1614
1615                 $stamp1 = microtime(true);
1616
1617                 if (!is_object($stmt)) {
1618                         return false;
1619                 }
1620
1621                 switch ($this->driver) {
1622                         case 'pdo':
1623                                 $ret = $stmt->closeCursor();
1624                                 break;
1625                         case 'mysqli':
1626                                 // MySQLi offers both a mysqli_stmt and a mysqli_result class.
1627                                 // We should be careful not to assume the object type of $stmt
1628                                 // because DBA::p() has been able to return both types.
1629                                 if ($stmt instanceof mysqli_stmt) {
1630                                         $stmt->free_result();
1631                                         $ret = $stmt->close();
1632                                 } elseif ($stmt instanceof mysqli_result) {
1633                                         $stmt->free();
1634                                         $ret = true;
1635                                 } else {
1636                                         $ret = false;
1637                                 }
1638                                 break;
1639                 }
1640
1641                 $this->profiler->saveTimestamp($stamp1, 'database', System::callstack());
1642
1643                 return $ret;
1644         }
1645
1646         /**
1647          * Return a list of database processes
1648          *
1649          * @return array
1650          *      'list' => List of processes, separated in their different states
1651          *      'amount' => Number of concurrent database processes
1652          * @throws \Exception
1653          */
1654         public function processlist()
1655         {
1656                 $ret  = $this->p("SHOW PROCESSLIST");
1657                 $data = $this->toArray($ret);
1658
1659                 $processes = 0;
1660                 $states    = [];
1661                 foreach ($data as $process) {
1662                         $state = trim($process["State"]);
1663
1664                         // Filter out all non blocking processes
1665                         if (!in_array($state, ["", "init", "statistics", "updating"])) {
1666                                 ++$states[$state];
1667                                 ++$processes;
1668                         }
1669                 }
1670
1671                 $statelist = "";
1672                 foreach ($states as $state => $usage) {
1673                         if ($statelist != "") {
1674                                 $statelist .= ", ";
1675                         }
1676                         $statelist .= $state . ": " . $usage;
1677                 }
1678                 return (["list" => $statelist, "amount" => $processes]);
1679         }
1680
1681         /**
1682          * Fetch a database variable
1683          *
1684          * @param string $name
1685          * @return string content
1686          */
1687         public function getVariable(string $name)
1688         {
1689                 $result = $this->fetchFirst("SHOW GLOBAL VARIABLES WHERE `Variable_name` = ?", $name);
1690                 return $result['Value'] ?? null;
1691         }
1692
1693         /**
1694          * Checks if $array is a filled array with at least one entry.
1695          *
1696          * @param mixed $array A filled array with at least one entry
1697          *
1698          * @return boolean Whether $array is a filled array or an object with rows
1699          */
1700         public function isResult($array)
1701         {
1702                 // It could be a return value from an update statement
1703                 if (is_bool($array)) {
1704                         return $array;
1705                 }
1706
1707                 if (is_object($array)) {
1708                         return $this->numRows($array) > 0;
1709                 }
1710
1711                 return (is_array($array) && (count($array) > 0));
1712         }
1713
1714         /**
1715          * Callback function for "esc_array"
1716          *
1717          * @param mixed   $value         Array value
1718          * @param string  $key           Array key
1719          * @param boolean $add_quotation add quotation marks for string values
1720          *
1721          * @return void
1722          */
1723         private function escapeArrayCallback(&$value, $key, $add_quotation)
1724         {
1725                 if (!$add_quotation) {
1726                         if (is_bool($value)) {
1727                                 $value = ($value ? '1' : '0');
1728                         } else {
1729                                 $value = $this->escape($value);
1730                         }
1731                         return;
1732                 }
1733
1734                 if (is_bool($value)) {
1735                         $value = ($value ? 'true' : 'false');
1736                 } elseif (is_float($value) || is_integer($value)) {
1737                         $value = (string)$value;
1738                 } else {
1739                         $value = "'" . $this->escape($value) . "'";
1740                 }
1741         }
1742
1743         /**
1744          * Escapes a whole array
1745          *
1746          * @param mixed   $arr           Array with values to be escaped
1747          * @param boolean $add_quotation add quotation marks for string values
1748          *
1749          * @return void
1750          */
1751         public function escapeArray(&$arr, $add_quotation = false)
1752         {
1753                 array_walk($arr, [$this, 'escapeArrayCallback'], $add_quotation);
1754         }
1755 }