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         protected $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                 $denylist = explode(',', $this->configCache->get('system', 'db_log_index_denylist'));
 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'], $denylist) || ($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, 4000) . "\n", FILE_APPEND);
 347                         }
 348                 }
 349         }
 350
 351         /**
 352          * Removes every not allowlisted 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');
 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, 4000) . "\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");
 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');
 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, array $param, bool $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          * Inserts a row with the provided data in the provided table.
1011          * If the data corresponds to an existing row through a UNIQUE or PRIMARY index constraints, it updates the row instead.
1012          *
1013          * @param string|array $table Table name or array [schema => table]
1014          * @param array        $param parameter array
1015          *
1016          * @return boolean was the insert successful?
1017          * @throws \Exception
1018          */
1019         public function replace($table, array $param)
1020         {
1021                 if (empty($table) || empty($param)) {
1022                         $this->logger->info('Table and fields have to be set');
1023                         return false;
1024                 }
1025
1026                 $table_string = DBA::buildTableString($table);
1027
1028                 $fields_string = implode(', ', array_map([DBA::class, 'quoteIdentifier'], array_keys($param)));
1029
1030                 $values_string = substr(str_repeat("?, ", count($param)), 0, -2);
1031
1032                 $sql = "REPLACE " . $table_string . " (" . $fields_string . ") VALUES (" . $values_string . ")";
1033
1034                 return $this->e($sql, $param);
1035         }
1036
1037         /**
1038          * Fetch the id of the last insert command
1039          *
1040          * @return integer Last inserted id
1041          */
1042         public function lastInsertId()
1043         {
1044                 switch ($this->driver) {
1045                         case 'pdo':
1046                                 $id = $this->connection->lastInsertId();
1047                                 break;
1048                         case 'mysqli':
1049                                 $id = $this->connection->insert_id;
1050                                 break;
1051                 }
1052                 return $id;
1053         }
1054
1055         /**
1056          * Locks a table for exclusive write access
1057          *
1058          * This function can be extended in the future to accept a table array as well.
1059          *
1060          * @param string|array $table Table name or array [schema => table]
1061          *
1062          * @return boolean was the lock successful?
1063          * @throws \Exception
1064          */
1065         public function lock($table)
1066         {
1067                 // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
1068                 if ($this->driver == 'pdo') {
1069                         $this->e("SET autocommit=0");
1070                         $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
1071                 } else {
1072                         $this->connection->autocommit(false);
1073                 }
1074
1075                 $success = $this->e("LOCK TABLES " . DBA::buildTableString($table) . " WRITE");
1076
1077                 if ($this->driver == 'pdo') {
1078                         $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, $this->pdo_emulate_prepares);
1079                 }
1080
1081                 if (!$success) {
1082                         if ($this->driver == 'pdo') {
1083                                 $this->e("SET autocommit=1");
1084                         } else {
1085                                 $this->connection->autocommit(true);
1086                         }
1087                 } else {
1088                         $this->in_transaction = true;
1089                 }
1090                 return $success;
1091         }
1092
1093         /**
1094          * Unlocks all locked tables
1095          *
1096          * @return boolean was the unlock successful?
1097          * @throws \Exception
1098          */
1099         public function unlock()
1100         {
1101                 // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
1102                 $this->performCommit();
1103
1104                 if ($this->driver == 'pdo') {
1105                         $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
1106                 }
1107
1108                 $success = $this->e("UNLOCK TABLES");
1109
1110                 if ($this->driver == 'pdo') {
1111                         $this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, $this->pdo_emulate_prepares);
1112                         $this->e("SET autocommit=1");
1113                 } else {
1114                         $this->connection->autocommit(true);
1115                 }
1116
1117                 $this->in_transaction = false;
1118                 return $success;
1119         }
1120
1121         /**
1122          * Starts a transaction
1123          *
1124          * @return boolean Was the command executed successfully?
1125          */
1126         public function transaction()
1127         {
1128                 if (!$this->performCommit()) {
1129                         return false;
1130                 }
1131
1132                 switch ($this->driver) {
1133                         case 'pdo':
1134                                 if (!$this->connection->inTransaction() && !$this->connection->beginTransaction()) {
1135                                         return false;
1136                                 }
1137                                 break;
1138
1139                         case 'mysqli':
1140                                 if (!$this->connection->begin_transaction()) {
1141                                         return false;
1142                                 }
1143                                 break;
1144                 }
1145
1146                 $this->in_transaction = true;
1147                 return true;
1148         }
1149
1150         protected function performCommit()
1151         {
1152                 switch ($this->driver) {
1153                         case 'pdo':
1154                                 if (!$this->connection->inTransaction()) {
1155                                         return true;
1156                                 }
1157
1158                                 return $this->connection->commit();
1159
1160                         case 'mysqli':
1161                                 return $this->connection->commit();
1162                 }
1163
1164                 return true;
1165         }
1166
1167         /**
1168          * Does a commit
1169          *
1170          * @return boolean Was the command executed successfully?
1171          */
1172         public function commit()
1173         {
1174                 if (!$this->performCommit()) {
1175                         return false;
1176                 }
1177                 $this->in_transaction = false;
1178                 return true;
1179         }
1180
1181         /**
1182          * Does a rollback
1183          *
1184          * @return boolean Was the command executed successfully?
1185          */
1186         public function rollback()
1187         {
1188                 $ret = false;
1189
1190                 switch ($this->driver) {
1191                         case 'pdo':
1192                                 if (!$this->connection->inTransaction()) {
1193                                         $ret = true;
1194                                         break;
1195                                 }
1196                                 $ret = $this->connection->rollBack();
1197                                 break;
1198
1199                         case 'mysqli':
1200                                 $ret = $this->connection->rollback();
1201                                 break;
1202                 }
1203                 $this->in_transaction = false;
1204                 return $ret;
1205         }
1206
1207         /**
1208          * Build the array with the table relations
1209          *
1210          * The array is build from the database definitions in DBStructure.php
1211          *
1212          * This process must only be started once, since the value is cached.
1213          */
1214         private function buildRelationData()
1215         {
1216                 $definition = DBStructure::definition($this->configCache->get('system', 'basepath'));
1217
1218                 foreach ($definition AS $table => $structure) {
1219                         foreach ($structure['fields'] AS $field => $field_struct) {
1220                                 if (isset($field_struct['relation'])) {
1221                                         foreach ($field_struct['relation'] AS $rel_table => $rel_field) {
1222                                                 $this->relation[$rel_table][$rel_field][$table][] = $field;
1223                                         }
1224                                 }
1225                         }
1226                 }
1227         }
1228
1229         /**
1230          * Delete a row from a table
1231          *
1232          * Note: this methods does NOT accept schema => table arrays because of the complex relation stuff.
1233          *
1234          * @param string $table      Table name
1235          * @param array  $conditions Field condition(s)
1236          * @param array  $options
1237          *                           - cascade: If true we delete records in other tables that depend on the one we're deleting through
1238          *                           relations (default: true)
1239          * @param array  $callstack  Internal use: prevent endless loops
1240          *
1241          * @return boolean was the delete successful?
1242          * @throws \Exception
1243          */
1244         public function delete($table, array $conditions, array $options = [], array &$callstack = [])
1245         {
1246                 if (empty($table) || empty($conditions)) {
1247                         $this->logger->info('Table and conditions have to be set');
1248                         return false;
1249                 }
1250
1251                 $commands = [];
1252
1253                 // Create a key for the loop prevention
1254                 $key = $table . ':' . json_encode($conditions);
1255
1256                 // We quit when this key already exists in the callstack.
1257                 if (isset($callstack[$key])) {
1258                         return true;
1259                 }
1260
1261                 $callstack[$key] = true;
1262
1263                 $commands[$key] = ['table' => $table, 'conditions' => $conditions];
1264
1265                 // Don't use "defaults" here, since it would set "false" to "true"
1266                 if (isset($options['cascade'])) {
1267                         $cascade = $options['cascade'];
1268                 } else {
1269                         $cascade = true;
1270                 }
1271
1272                 // To speed up the whole process we cache the table relations
1273                 if ($cascade && count($this->relation) == 0) {
1274                         $this->buildRelationData();
1275                 }
1276
1277                 // Is there a relation entry for the table?
1278                 if ($cascade && isset($this->relation[$table])) {
1279                         // We only allow a simple "one field" relation.
1280                         $field   = array_keys($this->relation[$table])[0];
1281                         $rel_def = array_values($this->relation[$table])[0];
1282
1283                         // Create a key for preventing double queries
1284                         $qkey = $field . '-' . $table . ':' . json_encode($conditions);
1285
1286                         // When the search field is the relation field, we don't need to fetch the rows
1287                         // This is useful when the leading record is already deleted in the frontend but the rest is done in the backend
1288                         if ((count($conditions) == 1) && ($field == array_keys($conditions)[0])) {
1289                                 foreach ($rel_def AS $rel_table => $rel_fields) {
1290                                         foreach ($rel_fields AS $rel_field) {
1291                                                 $this->delete($rel_table, [$rel_field => array_values($conditions)[0]], $options, $callstack);
1292                                         }
1293                                 }
1294                                 // We quit when this key already exists in the callstack.
1295                         } elseif (!isset($callstack[$qkey])) {
1296                                 $callstack[$qkey] = true;
1297
1298                                 // Fetch all rows that are to be deleted
1299                                 $data = $this->select($table, [$field], $conditions);
1300
1301                                 while ($row = $this->fetch($data)) {
1302                                         $this->delete($table, [$field => $row[$field]], $options, $callstack);
1303                                 }
1304
1305                                 $this->close($data);
1306
1307                                 // Since we had split the delete command we don't need the original command anymore
1308                                 unset($commands[$key]);
1309                         }
1310                 }
1311
1312                 // Now we finalize the process
1313                 $do_transaction = !$this->in_transaction;
1314
1315                 if ($do_transaction) {
1316                         $this->transaction();
1317                 }
1318
1319                 $compacted = [];
1320                 $counter   = [];
1321
1322                 foreach ($commands AS $command) {
1323                         $conditions = $command['conditions'];
1324                         reset($conditions);
1325                         $first_key = key($conditions);
1326
1327                         $condition_string = DBA::buildCondition($conditions);
1328
1329                         if ((count($command['conditions']) > 1) || is_int($first_key)) {
1330                                 $sql = "DELETE FROM " . DBA::quoteIdentifier($command['table']) . " " . $condition_string;
1331                                 $this->logger->info($this->replaceParameters($sql, $conditions), ['callstack' => System::callstack(6), 'internal_callstack' => $callstack]);
1332
1333                                 if (!$this->e($sql, $conditions)) {
1334                                         if ($do_transaction) {
1335                                                 $this->rollback();
1336                                         }
1337                                         return false;
1338                                 }
1339                         } else {
1340                                 $key_table     = $command['table'];
1341                                 $key_condition = array_keys($command['conditions'])[0];
1342                                 $value         = array_values($command['conditions'])[0];
1343
1344                                 // Split the SQL queries in chunks of 100 values
1345                                 // We do the $i stuff here to make the code better readable
1346                                 $i = isset($counter[$key_table][$key_condition]) ? $counter[$key_table][$key_condition] : 0;
1347                                 if (isset($compacted[$key_table][$key_condition][$i]) && count($compacted[$key_table][$key_condition][$i]) > 100) {
1348                                         ++$i;
1349                                 }
1350
1351                                 $compacted[$key_table][$key_condition][$i][$value] = $value;
1352                                 $counter[$key_table][$key_condition]               = $i;
1353                         }
1354                 }
1355                 foreach ($compacted AS $table => $values) {
1356                         foreach ($values AS $field => $field_value_list) {
1357                                 foreach ($field_value_list AS $field_values) {
1358                                         $sql = "DELETE FROM " . DBA::quoteIdentifier($table) . " WHERE " . DBA::quoteIdentifier($field) . " IN (" .
1359                                                substr(str_repeat("?, ", count($field_values)), 0, -2) . ");";
1360
1361                                         $this->logger->info($this->replaceParameters($sql, $field_values), ['callstack' => System::callstack(6), 'internal_callstack' => $callstack]);
1362
1363                                         if (!$this->e($sql, $field_values)) {
1364                                                 if ($do_transaction) {
1365                                                         $this->rollback();
1366                                                 }
1367                                                 return false;
1368                                         }
1369                                 }
1370                         }
1371                 }
1372                 if ($do_transaction) {
1373                         $this->commit();
1374                 }
1375                 return true;
1376         }
1377
1378         /**
1379          * Updates rows
1380          *
1381          * Updates rows in the database. When $old_fields is set to an array,
1382          * the system will only do an update if the fields in that array changed.
1383          *
1384          * Attention:
1385          * Only the values in $old_fields are compared.
1386          * This is an intentional behaviour.
1387          *
1388          * Example:
1389          * We include the timestamp field in $fields but not in $old_fields.
1390          * Then the row will only get the new timestamp when the other fields had changed.
1391          *
1392          * When $old_fields is set to a boolean value the system will do this compare itself.
1393          * When $old_fields is set to "true" the system will do an insert if the row doesn't exists.
1394          *
1395          * Attention:
1396          * Only set $old_fields to a boolean value when you are sure that you will update a single row.
1397          * When you set $old_fields to "true" then $fields must contain all relevant fields!
1398          *
1399          * @param string|array  $table      Table name or array [schema => table]
1400          * @param array         $fields     contains the fields that are updated
1401          * @param array         $condition  condition array with the key values
1402          * @param array|boolean $old_fields array with the old field values that are about to be replaced (true = update on duplicate)
1403          *
1404          * @return boolean was the update successfull?
1405          * @throws \Exception
1406          */
1407         public function update($table, $fields, $condition, $old_fields = [])
1408         {
1409                 if (empty($table) || empty($fields) || empty($condition)) {
1410                         $this->logger->info('Table, fields and condition have to be set');
1411                         return false;
1412                 }
1413
1414                 if (is_bool($old_fields)) {
1415                         $do_insert = $old_fields;
1416
1417                         $old_fields = $this->selectFirst($table, [], $condition);
1418
1419                         if (is_bool($old_fields)) {
1420                                 if ($do_insert) {
1421                                         $values = array_merge($condition, $fields);
1422                                         return $this->insert($table, $values, $do_insert);
1423                                 }
1424                                 $old_fields = [];
1425                         }
1426                 }
1427
1428                 foreach ($old_fields AS $fieldname => $content) {
1429                         if (isset($fields[$fieldname]) && !is_null($content) && ($fields[$fieldname] == $content)) {
1430                                 unset($fields[$fieldname]);
1431                         }
1432                 }
1433
1434                 if (count($fields) == 0) {
1435                         return true;
1436                 }
1437
1438                 $table_string = DBA::buildTableString($table);
1439
1440                 $condition_string = DBA::buildCondition($condition);
1441
1442                 $sql = "UPDATE " . $table_string . " SET "
1443                         . implode(" = ?, ", array_map([DBA::class, 'quoteIdentifier'], array_keys($fields))) . " = ?"
1444                         . $condition_string;
1445
1446                 // Combines the updated fields parameter values with the condition parameter values
1447                 $params  = array_merge(array_values($fields), $condition);
1448
1449                 return $this->e($sql, $params);
1450         }
1451
1452         /**
1453          * Retrieve a single record from a table and returns it in an associative array
1454          *
1455          * @param string|array $table
1456          * @param array        $fields
1457          * @param array        $condition
1458          * @param array        $params
1459          *
1460          * @return bool|array
1461          * @throws \Exception
1462          * @see   $this->select
1463          */
1464         public function selectFirst($table, array $fields = [], array $condition = [], $params = [])
1465         {
1466                 $params['limit'] = 1;
1467                 $result          = $this->select($table, $fields, $condition, $params);
1468
1469                 if (is_bool($result)) {
1470                         return $result;
1471                 } else {
1472                         $row = $this->fetch($result);
1473                         $this->close($result);
1474                         return $row;
1475                 }
1476         }
1477
1478         /**
1479          * Select rows from a table and fills an array with the data
1480          *
1481          * @param string|array $table     Table name or array [schema => table]
1482          * @param array        $fields    Array of selected fields, empty for all
1483          * @param array        $condition Array of fields for condition
1484          * @param array        $params    Array of several parameters
1485          *
1486          * @return array Data array
1487          * @throws \Exception
1488          * @see   self::select
1489          */
1490         public function selectToArray($table, array $fields = [], array $condition = [], array $params = [])
1491         {
1492                 return $this->toArray($this->select($table, $fields, $condition, $params));
1493         }
1494
1495         /**
1496          * Select rows from a table
1497          *
1498          *
1499          * Example:
1500          * $table = 'item';
1501          * or:
1502          * $table = ['schema' => 'table'];
1503          * @see DBA::buildTableString()
1504          *
1505          * $fields = ['id', 'uri', 'uid', 'network'];
1506          *
1507          * $condition = ['uid' => 1, 'network' => 'dspr', 'blocked' => true];
1508          * or:
1509          * $condition = ['`uid` = ? AND `network` IN (?, ?)', 1, 'dfrn', 'dspr'];
1510          * @see DBA::buildCondition()
1511          *
1512          * $params = ['order' => ['id', 'received' => true, 'created' => 'ASC'), 'limit' => 10];
1513          * @see DBA::buildParameter()
1514          *
1515          * $data = DBA::select($table, $fields, $condition, $params);
1516          *
1517          * @param string|array $table     Table name or array [schema => table]
1518          * @param array        $fields    Array of selected fields, empty for all
1519          * @param array        $condition Array of fields for condition
1520          * @param array        $params    Array of several parameters
1521          * @return boolean|object
1522          * @throws \Exception
1523          */
1524         public function select($table, array $fields = [], array $condition = [], array $params = [])
1525         {
1526                 if (empty($table)) {
1527                         return false;
1528                 }
1529
1530                 if (count($fields) > 0) {
1531                         $select_string = implode(', ', array_map([DBA::class, 'quoteIdentifier'], $fields));
1532                 } else {
1533                         $select_string = '*';
1534                 }
1535
1536                 $table_string = DBA::buildTableString($table);
1537
1538                 $condition_string = DBA::buildCondition($condition);
1539
1540                 $param_string = DBA::buildParameter($params);
1541
1542                 $sql = "SELECT " . $select_string . " FROM " . $table_string . $condition_string . $param_string;
1543
1544                 $result = $this->p($sql, $condition);
1545
1546                 return $result;
1547         }
1548
1549         /**
1550          * Counts the rows from a table satisfying the provided condition
1551          *
1552          * @param string|array $table     Table name or array [schema => table]
1553          * @param array        $condition Array of fields for condition
1554          * @param array        $params    Array of several parameters
1555          *
1556          * @return int
1557          *
1558          * Example:
1559          * $table = "item";
1560          *
1561          * $condition = ["uid" => 1, "network" => 'dspr'];
1562          * or:
1563          * $condition = ["`uid` = ? AND `network` IN (?, ?)", 1, 'dfrn', 'dspr'];
1564          *
1565          * $count = DBA::count($table, $condition);
1566          * @throws \Exception
1567          */
1568         public function count($table, array $condition = [], array $params = [])
1569         {
1570                 if (empty($table)) {
1571                         return false;
1572                 }
1573
1574                 $table_string = DBA::buildTableString($table);
1575
1576                 $condition_string = DBA::buildCondition($condition);
1577
1578                 if (empty($params['expression'])) {
1579                         $expression = '*';
1580                 } elseif (!empty($params['distinct'])) {
1581                         $expression = "DISTINCT " . DBA::quoteIdentifier($params['expression']);
1582                 } else {
1583                         $expression = DBA::quoteIdentifier($params['expression']);
1584                 }
1585
1586                 $sql = "SELECT COUNT(" . $expression . ") AS `count` FROM " . $table_string . $condition_string;
1587
1588                 $row = $this->fetchFirst($sql, $condition);
1589
1590                 return $row['count'];
1591         }
1592
1593         /**
1594          * Fills an array with data from a query
1595          *
1596          * @param object $stmt statement object
1597          * @param bool   $do_close
1598          *
1599          * @return array Data array
1600          */
1601         public function toArray($stmt, $do_close = true)
1602         {
1603                 if (is_bool($stmt)) {
1604                         return [];
1605                 }
1606
1607                 $data = [];
1608                 while ($row = $this->fetch($stmt)) {
1609                         $data[] = $row;
1610                 }
1611
1612                 if ($do_close) {
1613                         $this->close($stmt);
1614                 }
1615
1616                 return $data;
1617         }
1618
1619         /**
1620          * Returns the error number of the last query
1621          *
1622          * @return string Error number (0 if no error)
1623          */
1624         public function errorNo()
1625         {
1626                 return $this->errorno;
1627         }
1628
1629         /**
1630          * Returns the error message of the last query
1631          *
1632          * @return string Error message ('' if no error)
1633          */
1634         public function errorMessage()
1635         {
1636                 return $this->error;
1637         }
1638
1639         /**
1640          * Closes the current statement
1641          *
1642          * @param object $stmt statement object
1643          *
1644          * @return boolean was the close successful?
1645          */
1646         public function close($stmt)
1647         {
1648
1649                 $stamp1 = microtime(true);
1650
1651                 if (!is_object($stmt)) {
1652                         return false;
1653                 }
1654
1655                 switch ($this->driver) {
1656                         case 'pdo':
1657                                 $ret = $stmt->closeCursor();
1658                                 break;
1659                         case 'mysqli':
1660                                 // MySQLi offers both a mysqli_stmt and a mysqli_result class.
1661                                 // We should be careful not to assume the object type of $stmt
1662                                 // because DBA::p() has been able to return both types.
1663                                 if ($stmt instanceof mysqli_stmt) {
1664                                         $stmt->free_result();
1665                                         $ret = $stmt->close();
1666                                 } elseif ($stmt instanceof mysqli_result) {
1667                                         $stmt->free();
1668                                         $ret = true;
1669                                 } else {
1670                                         $ret = false;
1671                                 }
1672                                 break;
1673                 }
1674
1675                 $this->profiler->saveTimestamp($stamp1, 'database');
1676
1677                 return $ret;
1678         }
1679
1680         /**
1681          * Return a list of database processes
1682          *
1683          * @return array
1684          *      'list' => List of processes, separated in their different states
1685          *      'amount' => Number of concurrent database processes
1686          * @throws \Exception
1687          */
1688         public function processlist()
1689         {
1690                 $ret  = $this->p("SHOW PROCESSLIST");
1691                 $data = $this->toArray($ret);
1692
1693                 $processes = 0;
1694                 $states    = [];
1695                 foreach ($data as $process) {
1696                         $state = trim($process["State"]);
1697
1698                         // Filter out all non blocking processes
1699                         if (!in_array($state, ["", "init", "statistics", "updating"])) {
1700                                 ++$states[$state];
1701                                 ++$processes;
1702                         }
1703                 }
1704
1705                 $statelist = "";
1706                 foreach ($states as $state => $usage) {
1707                         if ($statelist != "") {
1708                                 $statelist .= ", ";
1709                         }
1710                         $statelist .= $state . ": " . $usage;
1711                 }
1712                 return (["list" => $statelist, "amount" => $processes]);
1713         }
1714
1715         /**
1716          * Fetch a database variable
1717          *
1718          * @param string $name
1719          * @return string content
1720          */
1721         public function getVariable(string $name)
1722         {
1723                 $result = $this->fetchFirst("SHOW GLOBAL VARIABLES WHERE `Variable_name` = ?", $name);
1724                 return $result['Value'] ?? null;
1725         }
1726
1727         /**
1728          * Checks if $array is a filled array with at least one entry.
1729          *
1730          * @param mixed $array A filled array with at least one entry
1731          *
1732          * @return boolean Whether $array is a filled array or an object with rows
1733          */
1734         public function isResult($array)
1735         {
1736                 // It could be a return value from an update statement
1737                 if (is_bool($array)) {
1738                         return $array;
1739                 }
1740
1741                 if (is_object($array)) {
1742                         return $this->numRows($array) > 0;
1743                 }
1744
1745                 return (is_array($array) && (count($array) > 0));
1746         }
1747
1748         /**
1749          * Callback function for "esc_array"
1750          *
1751          * @param mixed   $value         Array value
1752          * @param string  $key           Array key
1753          * @param boolean $add_quotation add quotation marks for string values
1754          *
1755          * @return void
1756          */
1757         private function escapeArrayCallback(&$value, $key, $add_quotation)
1758         {
1759                 if (!$add_quotation) {
1760                         if (is_bool($value)) {
1761                                 $value = ($value ? '1' : '0');
1762                         } else {
1763                                 $value = $this->escape($value);
1764                         }
1765                         return;
1766                 }
1767
1768                 if (is_bool($value)) {
1769                         $value = ($value ? 'true' : 'false');
1770                 } elseif (is_float($value) || is_integer($value)) {
1771                         $value = (string)$value;
1772                 } else {
1773                         $value = "'" . $this->escape($value) . "'";
1774                 }
1775         }
1776
1777         /**
1778          * Escapes a whole array
1779          *
1780          * @param mixed   $arr           Array with values to be escaped
1781          * @param boolean $add_quotation add quotation marks for string values
1782          *
1783          * @return void
1784          */
1785         public function escapeArray(&$arr, $add_quotation = false)
1786         {
1787                 array_walk($arr, [$this, 'escapeArrayCallback'], $add_quotation);
1788         }
1789 }