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