From 79daf306a71b47bad3844582c22e0731dca9c4cd Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Tue, 22 Nov 2022 17:06:47 +0100
Subject: [inc/Database] Update with latest version from slx-admin

---
 inc/database.inc.php | 418 ++++++++++++++++++++++++++++++++++++++++++++++-----
 1 file changed, 383 insertions(+), 35 deletions(-)

(limited to 'inc')

diff --git a/inc/database.inc.php b/inc/database.inc.php
index f76c9e7..c1f9454 100644
--- a/inc/database.inc.php
+++ b/inc/database.inc.php
@@ -8,28 +8,43 @@ class Database
 {
 
 	/**
-	 *
 	 * @var \PDO Database handle
 	 */
 	private static $dbh = false;
-	private static $statements = array();
-	
-	
-		/**
+
+	private static $returnErrors;
+	private static $lastError = false;
+	private static $explainList = array();
+	private static $queryCount = 0;
+	private static $queryTime = 0;
+
+	/**
 	 * Connect to the DB if not already connected.
 	 */
-	private static function init()
+	public static function init(bool $returnErrors = false): bool
 	{
 		if (self::$dbh !== false)
-			return;
+			return true;
+		self::$returnErrors = $returnErrors;
 		try {
-			if (CONFIG_SQL_FORCE_UTF8)
-				self::$dbh = new PDO(CONFIG_SQL_DSN, CONFIG_SQL_USER, CONFIG_SQL_PASS, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
-			else
-				self::$dbh = new PDO(CONFIG_SQL_DSN, CONFIG_SQL_USER, CONFIG_SQL_PASS);
+			self::$dbh = new PDO(CONFIG_SQL_DSN, CONFIG_SQL_USER, CONFIG_SQL_PASS, [
+				PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
+				PDO::ATTR_EMULATE_PREPARES   => true,
+				PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4', // Somehow needed, even if charset=utf8mb4 is in DSN?
+			]);
 		} catch (PDOException $e) {
+			if (self::$returnErrors)
+				return false;
 			Util::traceError('Connecting to the local database failed: ' . $e->getMessage());
 		}
+		if (CONFIG_DEBUG) {
+			Database::exec("SET SESSION sql_mode='STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO'");
+			Database::exec("SET SESSION innodb_strict_mode=ON");
+			register_shutdown_function(function() {
+				self::examineLoggedQueries();
+			});
+		}
+		return true;
 	}
 
 	/**
@@ -37,25 +52,100 @@ class Database
 	 *
 	 * @return array|boolean Associative array representing row, or false if no row matches the query
 	 */
-	public static function queryFirst($query, $args = array(), $ignoreError = false)
+	public static function queryFirst(string $query, array $args = [], bool $ignoreError = null)
+	{
+		$res = self::simpleQuery($query, $args, $ignoreError);
+		if ($res === false)
+			return false;
+		return $res->fetch();
+	}
+
+	/**
+	 * If you need all rows for a query as plain array you can use this.
+	 * Don't use this if you want to do further processing of the data, to save some
+	 * memory.
+	 *
+	 * @return array|bool List of associative arrays representing rows, or false on error
+	 */
+	public static function queryAll(string $query, array $args = [], bool $ignoreError = null)
+	{
+		$res = self::simpleQuery($query, $args, $ignoreError);
+		if ($res === false)
+			return false;
+		return $res->fetchAll();
+	}
+
+	/**
+	 * Fetch the first column of the query as a plain list-of-values array.
+	 *
+	 * @return array|bool List of values representing first column of query
+	 */
+	public static function queryColumnArray(string $query, array $args = [], bool $ignoreError = null)
+	{
+		$res = self::simpleQuery($query, $args, $ignoreError);
+		if ($res === false)
+			return false;
+		return $res->fetchAll(PDO::FETCH_COLUMN, 0);
+	}
+
+	/**
+	 * Fetch two columns as key => value list.
+	 *
+	 * @return array|bool Associative array, first column is key, second column is value
+	 */
+	public static function queryKeyValueList(string $query, array $args = [], bool $ignoreError = null)
 	{
 		$res = self::simpleQuery($query, $args, $ignoreError);
 		if ($res === false)
 			return false;
-		return $res->fetch(PDO::FETCH_ASSOC);
+		return $res->fetchAll(PDO::FETCH_KEY_PAIR);
 	}
 
+	/**
+	 * Fetch and group by first column. First column is key, value is a list of rows with remaining columns.
+	 * [
+	 *    col1 => [
+	 *       [col2, col3],
+	 *       [col2, col3],
+	 *    ],
+	 *    ...,
+	 * ]
+	 *
+	 * @return array|bool Associative array, first column is key, remaining columns are array values
+	 */
+	public static function queryGroupList(string $query, array $args = [], bool $ignoreError = null)
+	{
+		$res = self::simpleQuery($query, $args, $ignoreError);
+		if ($res === false)
+			return false;
+		return $res->fetchAll(PDO::FETCH_GROUP);
+	}
+
+	/**
+	 * Fetch and use first column as key of returned array.
+	 * This is like queryGroup list, but it is assumed that the first column is unique, so
+	 * the remaining columns won't be wrapped in another array.
+	 *
+	 * @return array|bool Associative array, first column is key, remaining columns are array values
+	 */
+	public static function queryIndexedList(string $query, array $args = [], bool $ignoreError = null)
+	{
+		$res = self::simpleQuery($query, $args, $ignoreError);
+		if ($res === false)
+			return false;
+		return $res->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_UNIQUE);
+	}
 
 	/**
 	 * Execute the given query and return the number of rows affected.
 	 * Mostly useful for UPDATEs or INSERTs
-	 * 
+	 *
 	 * @param string $query Query to run
 	 * @param array $args Arguments to query
-	 * @param boolean $ignoreError Ignore query errors and just return false
+	 * @param ?bool $ignoreError Ignore query errors and just return false
 	 * @return int|boolean Number of rows affected, or false on error
 	 */
-	public static function exec($query, $args = array(), $ignoreError = false)
+	public static function exec(string $query, array $args = [], bool $ignoreError = null)
 	{
 		$res = self::simpleQuery($query, $args, $ignoreError);
 		if ($res === false)
@@ -64,41 +154,194 @@ class Database
 	}
 
 	/**
-	 * Get id (promary key) of last row inserted.
-	 * 
+	 * Get id (primary key) of last row inserted.
+	 *
 	 * @return int the id
 	 */
-	public static function lastInsertId()
+	public static function lastInsertId(): int
 	{
 		return self::$dbh->lastInsertId();
 	}
 
+	/**
+	 * @return string|false return last error returned by query
+	 */
+	public static function lastError()
+	{
+		return self::$lastError;
+	}
+
 	/**
 	 * Execute the given query and return the corresponding PDOStatement object
 	 * Note that this will re-use PDOStatements, so if you run the same
 	 * query again with different params, do not rely on the first PDOStatement
 	 * still being valid. If you need to do something fancy, use Database::prepare
-	 * @return \PDOStatement The query result object
+	 *
+	 * @return \PDOStatement|false The query result object
 	 */
-	public static function simpleQuery($query, $args = array(), $ignoreError = false)
+	public static function simpleQuery(string $query, array $args = [], bool $ignoreError = null)
 	{
 		self::init();
-		try {
-			if (!isset(self::$statements[$query])) {
-				self::$statements[$query] = self::$dbh->prepare($query);
-			} else {
-				self::$statements[$query]->closeCursor();
+		if (CONFIG_DEBUG && !isset(self::$explainList[$query]) && preg_match('/^\s*SELECT/is', $query)) {
+			self::$explainList[$query] = [$args];
+		}
+		// Support passing nested arrays for IN statements, automagically refactor
+		$oquery = $query;
+		self::handleArrayArgument($query, $args);
+		// Now turn any bools into 0 or 1, since PDO unfortunately only does (string)<bool>, which
+		// results in an empty string for false
+		foreach ($args as &$arg) {
+			if ($arg === false) {
+				$arg = '0';
+			} elseif ($arg === true) {
+				$arg = '1';
 			}
-			if (self::$statements[$query]->execute($args) === false) {
-				if ($ignoreError)
+		}
+		try {
+			$stmt = self::$dbh->prepare($query);
+			$start = microtime(true);
+			if ($stmt->execute($args) === false) {
+				self::$lastError = implode("\n", $stmt->errorInfo());
+				if ($ignoreError === true || ($ignoreError === null && self::$returnErrors))
 					return false;
-				Util::traceError("Database Error: \n" . implode("\n", self::$statements[$query]->errorInfo()));
+				Util::traceError("Database Error: \n" . self::$lastError);
 			}
-			return self::$statements[$query];
+			if (CONFIG_DEBUG) {
+				$duration = microtime(true) - $start;
+				self::$queryTime += $duration;
+				$duration = round($duration, 3);
+				if (isset(self::$explainList[$oquery])) {
+					self::$explainList[$oquery][] = $duration;
+				} elseif ($duration > 0.1) {
+					error_log('SLOW ****** ' . $duration . "s *********\n" . $query);
+				}
+				self::$queryCount += 1;
+			}
+			return $stmt;
 		} catch (Exception $e) {
-				if ($ignoreError)
-					return false;
-				Util::traceError("Database Error: \n" . $e->getMessage());
+			self::$lastError = '(' . $e->getCode() . ') ' . $e->getMessage();
+			if ($ignoreError === true || ($ignoreError === null && self::$returnErrors))
+				return false;
+			Util::traceError("Database Error: \n" . self::$lastError);
+		}
+		return false;
+	}
+
+	public static function examineLoggedQueries()
+	{
+		foreach (self::$explainList as $q => $a) {
+			self::explainQuery($q, $a);
+		}
+	}
+
+	private static function explainQuery(string $query, array $data)
+	{
+		$args = array_shift($data);
+		$slow = false;
+		$veryslow = false;
+		foreach ($data as &$ts) {
+			if ($ts > 0.004) {
+				$slow = true;
+				if ($ts > 0.015) {
+					$ts = "[$ts]";
+					$veryslow = true;
+				}
+			}
+		}
+		if (!$slow)
+			return;
+		unset($ts);
+		$res = self::simpleQuery('EXPLAIN ' . $query, $args, true);
+		if ($res === false)
+			return;
+		$rows = $res->fetchAll();
+		if (empty($rows))
+			return;
+		$log = $veryslow;
+		$lens = array();
+		foreach (array_keys($rows[0]) as $key) {
+			$lens[$key] = strlen($key);
+		}
+		foreach ($rows as $row) {
+			if (!$log && $row['rows'] > 20 && preg_match('/filesort|temporary/i', $row['Extra'])) {
+				$log = true;
+			}
+			foreach ($row as $key => $col) {
+				$l = strlen($col);
+				if ($l > $lens[$key]) {
+					$lens[$key] = $l;
+				}
+			}
+		}
+		if (!$log)
+			return;
+		error_log('Possible slow query: ' . $query);
+		error_log('Times: ' . implode(', ', $data));
+		$border = $head = '';
+		foreach ($lens as $key => $len) {
+			$border .= '+' . str_repeat('-', $len + 2);
+			$head .= '| ' . str_pad($key, $len) . ' ';
+		}
+		$border .= '+';
+		$head .= '|';
+		error_log("\n" . $border . "\n" . $head . "\n" . $border);
+		foreach ($rows as $row) {
+			$line = '';
+			foreach ($lens as $key => $len) {
+				$line .= '| '. str_pad($row[$key], $len) . ' ';
+			}
+			error_log($line . "|");
+		}
+		error_log($border);
+	}
+
+	/**
+	 * Convert nested array argument to multiple arguments.
+	 * If you have:
+	 * $query = 'SELECT * FROM tbl WHERE bcol = :bool AND col IN (:list)
+	 * $args = ( 'bool' => 1, 'list' => ('foo', 'bar') )
+	 * it results in:
+	 * $query = '...WHERE bcol = :bool AND col IN (:list_0, :list_1)
+	 * $args = ( 'bool' => 1, 'list_0' => 'foo', 'list_1' => 'bar' )
+	 *
+	 * @param string $query sql query string
+	 * @param array $args query arguments
+	 * @return void
+	 */
+	private static function handleArrayArgument(string &$query, array &$args)
+	{
+		$again = false;
+		foreach (array_keys($args) as $key) {
+			if (is_numeric($key) || $key === '?')
+				continue;
+			if (is_array($args[$key])) {
+				if (empty($args[$key])) {
+					// Empty list - what to do? We try to generate a query string that will not yield any result
+					$args[$key] = 'asdf' . mt_rand(0,PHP_INT_MAX) . mt_rand(0,PHP_INT_MAX)
+						. mt_rand(0,PHP_INT_MAX) . '@' . microtime(true);
+					continue;
+				}
+				$newkey = $key;
+				if ($newkey[0] !== ':') {
+					$newkey = ":$newkey";
+				}
+				$new = array();
+				foreach ($args[$key] as $subIndex => $sub) {
+					if (is_array($sub)) {
+						$new[] = '(' . $newkey . '_' . $subIndex . ')';
+						$again = true;
+					} else {
+						$new[] = $newkey . '_' . $subIndex;
+					}
+					$args[$newkey . '_' . $subIndex] = $sub;
+				}
+				unset($args[$key]);
+				$new = implode(',', $new);
+				$query = preg_replace('/' . $newkey . '\b/', $new, $query);
+			}
+		}
+		if ($again) {
+			self::handleArrayArgument($query, $args);
 		}
 	}
 
@@ -106,10 +349,115 @@ class Database
 	 * Simply calls PDO::prepare and returns the PDOStatement.
 	 * You must call PDOStatement::execute manually on it.
 	 */
-	public static function prepare($query)
+	public static function prepare(string $query)
 	{
-		self:init();
+		self::init();
+		self::$queryCount += 1; // Cannot know actual count
 		return self::$dbh->prepare($query);
 	}
 
+	/**
+	 * Insert row into table, returning the generated key.
+	 * This requires the table to have an AUTO_INCREMENT column and
+	 * usually requires the given $uniqueValues to span across a UNIQUE index.
+	 * The code first tries to SELECT the key for the given values without
+	 * inserting first. This means this function is best used for cases
+	 * where you expect that the entry already exists in the table, so
+	 * only one SELECT will run. For all the entries that do not exist,
+	 * an INSERT or INSERT IGNORE is run, depending on whether $additionalValues
+	 * is empty or not. Another reason we don't run the INSERT (IGNORE) first
+	 * is that it will increase the AUTO_INCREMENT value on InnoDB, even when
+	 * no INSERT took place. So if you expect a lot of collisions you might
+	 * use this function to prevent your A_I value from counting up too
+	 * quickly.
+	 * Other than that, this is just a dumb version of running INSERT and then
+	 * getting the LAST_INSERT_ID(), or doing a query for the existing ID in
+	 * case of a key collision.
+	 *
+	 * @param string $table table to insert into
+	 * @param string $aiKey name of the AUTO_INCREMENT column
+	 * @param array $uniqueValues assoc array containing columnName => value mapping
+	 * @param ?array $additionalValues assoc array containing columnName => value mapping
+	 * @return int AUTO_INCREMENT value matching the given unique values entry
+	 */
+	public static function insertIgnore(string $table, string $aiKey, array $uniqueValues, array $additionalValues = null): int
+	{
+		// Sanity checks
+		if (array_key_exists($aiKey, $uniqueValues)) {
+			Util::traceError("$aiKey must not be in \$uniqueValues");
+		}
+		if (is_array($additionalValues) && array_key_exists($aiKey, $additionalValues)) {
+			Util::traceError("$aiKey must not be in \$additionalValues");
+		}
+		// Simple SELECT first
+		$selectSql = 'SELECT ' . $aiKey . ' FROM ' . $table . ' WHERE 1';
+		foreach ($uniqueValues as $key => $value) {
+			$selectSql .= ' AND ' . $key . ' = :' . $key;
+		}
+		$selectSql .= ' LIMIT 1';
+		$res = self::queryFirst($selectSql, $uniqueValues);
+		if ($res !== false) {
+			// Exists
+			if (!empty($additionalValues)) {
+				// Simulate ON DUPLICATE KEY UPDATE ...
+				$updateSql = 'UPDATE ' . $table . ' SET ';
+				$first = true;
+				foreach ($additionalValues as $key => $value) {
+					if ($first) {
+						$first = false;
+					} else {
+						$updateSql .= ', ';
+					}
+					$updateSql .= $key . ' = :' . $key;
+				}
+				$updateSql .= ' WHERE ' . $aiKey . ' = :' . $aiKey;
+				$additionalValues[$aiKey] = $res[$aiKey];
+				Database::exec($updateSql, $additionalValues);
+			}
+			return $res[$aiKey];
+		}
+		// Does not exist:
+		if (empty($additionalValues)) {
+			$combined =& $uniqueValues;
+		} else {
+			$combined = $uniqueValues + $additionalValues;
+		}
+		// Aight, try INSERT or INSERT IGNORE
+		$insertSql = 'INTO ' . $table . ' (' . implode(', ', array_keys($combined))
+			. ') VALUES (:' . implode(', :', array_keys($combined)) . ')';
+		if (empty($additionalValues)) {
+			// Simple INSERT IGNORE
+			$insertSql = 'INSERT IGNORE ' . $insertSql;
+		} else {
+			// INSERT ... ON DUPLICATE (in case we have a race)
+			$insertSql = 'INSERT ' . $insertSql . ' ON DUPLICATE KEY UPDATE ';
+			$first = true;
+			foreach ($additionalValues as $key => $value) {
+				if ($first) {
+					$first = false;
+				} else {
+					$insertSql .= ', ';
+				}
+				$insertSql .= $key . ' =  VALUES(' . $key . ')';
+			}
+		}
+		self::exec($insertSql, $combined);
+		// Insert done, retrieve key again
+		$res = self::queryFirst($selectSql, $uniqueValues);
+		if ($res === false) {
+			Util::traceError('Could not find value in table ' . $table . ' that was just inserted');
+		}
+		return $res[$aiKey];
+	}
+
+	public static function getQueryCount(): int
+	{
+		return self::$queryCount;
+	}
+
+	public static function getQueryTime(): int
+	{
+		return self::$queryTime;
+	}
+
 }
-- 
cgit v1.2.3-55-g7522