src/Database/Database.php

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