Moderize Database handling

* Auto-convert to utf8mb4_unicode_520_ci
* Use foreach instead of while to loop over results
* Drop useless statement caching
* Keep emulated prepares, as we sometimes loop over nested queries

3 files changed, 15 insertions, 23 deletions

diff --git a/inc/database.inc.php b/inc/database.inc.php
index a4f7c1b9..a55555b4 100644
--- a/inc/database.inc.php
+++ b/inc/database.inc.php
@@ -11,10 +11,7 @@ class Database
 	 * @var \PDO Database handle
 	 */
 	private static $dbh = false;
-	/*
-	 * @var \PDOStatement[]
-	 */
-	private static $statements = array();
+
 	private static $returnErrors;
 	private static $lastError = false;
 	private static $explainList = array();
@@ -30,11 +27,10 @@ class Database
 			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,
+			]);
 		} catch (PDOException $e) {
 			if (self::$returnErrors)
 				return false;
@@ -59,7 +55,7 @@ class Database
 		$res = self::simpleQuery($query, $args, $ignoreError);
 		if ($res === false)
 			return false;
-		return $res->fetch(PDO::FETCH_ASSOC);
+		return $res->fetch();
 	}
 
 	/**
@@ -74,7 +70,7 @@ class Database
 		$res = self::simpleQuery($query, $args, $ignoreError);
 		if ($res === false)
 			return false;
-		return $res->fetchAll(PDO::FETCH_ASSOC);
+		return $res->fetchAll();
 	}
 
 	/**
@@ -165,14 +161,10 @@ class Database
 			}
 		}
 		try {
-			if (!isset(self::$statements[$query])) {
-				self::$statements[$query] = self::$dbh->prepare($query);
-			} else {
-				//self::$statements[$query]->closeCursor();
-			}
+			$stmt = self::$dbh->prepare($query);
 			$start = microtime(true);
-			if (self::$statements[$query]->execute($args) === false) {
-				self::$lastError = implode("\n", self::$statements[$query]->errorInfo());
+			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" . self::$lastError);
@@ -188,7 +180,7 @@ class Database
 				}
 				self::$queryCount += 1;
 			}
-			return self::$statements[$query];
+			return $stmt;
 		} catch (Exception $e) {
 			self::$lastError = '(' . $e->getCode() . ') ' . $e->getMessage();
 			if ($ignoreError === true || ($ignoreError === null && self::$returnErrors))
@@ -225,7 +217,7 @@ class Database
 		$res = self::simpleQuery('EXPLAIN ' . $query, $args, true);
 		if ($res === false)
 			return;
-		$rows = $res->fetchAll(PDO::FETCH_ASSOC);
+		$rows = $res->fetchAll();
 		if (empty($rows))
 			return;
 		$log = $veryslow;
diff --git a/inc/property.inc.php b/inc/property.inc.php
index 1b979982..e7f4cd25 100644
--- a/inc/property.inc.php
+++ b/inc/property.inc.php
@@ -21,7 +21,7 @@ class Property
 		if (self::$cache === false) {
 			$NOW = time();
 			$res = Database::simpleQuery("SELECT name, dateline, value FROM property");
-			while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			foreach ($res as $row) {
 				if ($row['dateline'] != 0 && $row['dateline'] < $NOW)
 					continue;
 				self::$cache[$row['name']] = $row['value'];
@@ -71,7 +71,7 @@ class Property
 		$res = Database::simpleQuery("SELECT dateline, value FROM property_list WHERE name = :key", compact('key'));
 		$NOW = time();
 		$return = array();
-		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+		foreach ($res as $row) {
 			if ($row['dateline'] != 0 && $row['dateline'] < $NOW)
 				continue;
 			$return[] = $row['value'];
diff --git a/inc/taskmanagercallback.inc.php b/inc/taskmanagercallback.inc.php
index 29bae262..84754de0 100644
--- a/inc/taskmanagercallback.inc.php
+++ b/inc/taskmanagercallback.inc.php
@@ -56,7 +56,7 @@ class TaskmanagerCallback
 		if ($res === false)
 			return array();
 		$retval = array();
-		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+		foreach ($res as $row) {
 			$retval[$row['taskid']][] = $row;
 		}
 		return $retval;