src/Database/DBA.php

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