src/Database/DBA.php

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