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