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