src/Database/Database.php

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