From 8dc2b92d667f1401ab9f1315a36add61658f899c Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Thu, 6 May 2021 10:26:09 +0200
Subject: 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
---
 install.php | 48 ++++++++++++++++++++++++++++++++++++++++++++----
 1 file changed, 44 insertions(+), 4 deletions(-)

(limited to 'install.php')

diff --git a/install.php b/install.php
index f0bb1c72..60cf9495 100644
--- a/install.php
+++ b/install.php
@@ -84,7 +84,7 @@ function tableGetDescribeColumn($table, $column, $what)
 	$table = preg_replace('/\W/', '', $table);
 	$res = Database::simpleQuery("DESCRIBE `$table`", array(), true);
 	if ($res !== false) {
-		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+		foreach ($res as $row) {
 			if ((is_array($column) && in_array($row['Field'], $column)) || (is_string($column) && $row['Field'] === $column))
 				return $row[$what];
 		}
@@ -101,7 +101,7 @@ function tableGetIndex($table, $index)
 	$res = Database::simpleQuery("SHOW INDEX FROM `$table`", array(), true);
 	if ($res !== false) {
 		$matches = [];
-		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+		foreach ($res as $row) {
 			$i = $row['Seq_in_index'] - 1;
 			if (isset($index[$i]) && $index[$i] === $row['Column_name']) {
 				if (!isset($matches[$row['Key_name']])) {
@@ -124,7 +124,7 @@ function tableDropColumn($table, $column)
 	$column = preg_replace('/\W/', '', $column);
 	$res = Database::simpleQuery("DESCRIBE `$table`", array(), true);
 	if ($res !== false) {
-		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+		foreach ($res as $row) {
 			if ((is_array($column) && in_array($row['Field'], $column)) || (is_string($column) && $row['Field'] === $column))
 				Database::exec("ALTER TABLE `$table` DROP `{$row['Field']}`");
 		}
@@ -345,6 +345,45 @@ function runUpdateScript($module)
 	require_once $module->getDir() . '/install.inc.php';
 }
 
+// Update collation/encoding etc
+$charsetUpdate = '';
+$COLLATION = 'utf8mb4_unicode_520_ci';
+$res = Database::queryFirst("SELECT @@character_set_database, @@collation_database");
+if ($res['@@character_set_database'] !== 'utf8mb4' || $res['@@collation_database'] !== $COLLATION) {
+	if (!preg_match('/dbname=(\w+)/', CONFIG_SQL_DSN, $out)) {
+		$charsetUpdate = 'Cannot update charset: DB Name unknown';
+	} else {
+		$db = $out[1];
+		$columns = Database::simpleQuery("SELECT
+				TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, EXTRA, COLUMN_COMMENT
+				FROM information_schema.COLUMNS
+				WHERE TABLE_SCHEMA = :db AND COLLATION_NAME LIKE 'utf8%' AND COLLATION_NAME <> :collation",
+			['db' => $db, 'collation' => $COLLATION]);
+		foreach ($columns as $c) {
+			$args = [];
+			$str = $c['COLUMN_TYPE'] . ' CHARACTER SET utf8mb4 ' . $c['EXTRA'];
+			if ($c['IS_NULLABLE'] === 'NO') {
+				$str .= ' NOT NULL';
+			}
+			if (!($c['IS_NULLABLE'] === 'NO' && $c['COLUMN_DEFAULT'] === null)) {
+				$str .= ' DEFAULT ' . ($c['COLUMN_DEFAULT'] ?? 'NULL');
+			}
+			if (!empty($c['COLUMN_COMMENT'])) {
+				$str .= ' COMMENT :comment';
+				$args['comment'] = $c['COLUMN_COMMENT'];
+			}
+			$str .= ' COLLATE ' . $COLLATION;
+			if (Database::exec("ALTER TABLE {$c['TABLE_NAME']} MODIFY {$c['COLUMN_NAME']} $str", $args) === false) {
+				$charsetUpdate .= "\n+++ {$c['TABLE_NAME']}.{$c['COLUMN_NAME']} failed: " . Database::lastError();
+				$charsetUpdate .= "\nALTER TABLE {$c['TABLE_NAME']} MODIFY {$c['COLUMN_NAME']} $str";
+			}
+		}
+		if (empty($charsetUpdate) && Database::exec("ALTER DATABASE $db CHARACTER SET utf8mb4 COLLATE $COLLATION") === false) {
+			$charsetUpdate .= "\nCannot update database charset or collation: " . Database::lastError();
+		}
+	}
+} // End utf8 stuff
+
 // Build dependency tree
 Module::init();
 $modules = Module::getEnabled();
@@ -411,7 +450,7 @@ if (DIRECT_MODE) {
 		<title>Install/Update SLXadmin</title>
 		<meta charset="utf-8"> 
 		<meta http-equiv="X-UA-Compatible" content="IE=edge">
-		<style type="text/css">
+		<style>
 		body, html {
 			color: #000;
 			background: #fff;
@@ -428,6 +467,7 @@ if (DIRECT_MODE) {
 		</style>
 	</head>
 	<body>
+		<pre>$charsetUpdate</pre>
 		<h1>Modules</h1>
 		<button onclick="slxRunInstall()" class="install-btn">Install/Upgrade</button>
 		<br>
-- 
cgit v1.2.3-55-g7522