From c65817a89f132129d9cca3d7a9cdcd5bb500f7c6 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Joshua=20R=C3=BCsweg?= <ruesweg@woltlab.com>
Date: Mon, 29 Jul 2019 19:04:43 +0200
Subject: [PATCH] Add foreign key system check See #2977

---
 .../files/acp/templates/systemCheck.tpl       | 12 +++++
 .../lib/acp/page/SystemCheckPage.class.php    | 53 ++++++++++++++++++-
 wcfsetup/install/lang/de.xml                  |  2 +
 wcfsetup/install/lang/en.xml                  |  2 +
 4 files changed, 68 insertions(+), 1 deletion(-)

diff --git a/wcfsetup/install/files/acp/templates/systemCheck.tpl b/wcfsetup/install/files/acp/templates/systemCheck.tpl
index 4a2109d46d..f72c9e7454 100644
--- a/wcfsetup/install/files/acp/templates/systemCheck.tpl
+++ b/wcfsetup/install/files/acp/templates/systemCheck.tpl
@@ -129,6 +129,18 @@
 			<small>{lang}wcf.acp.systemCheck.mysql.innodb.description{/lang}</small>
 		</dd>
 	</dl>
+	
+	<dl{if !$results[mysql][foreignKeys]} class="formError"{/if}>
+		<dt>{lang}wcf.acp.systemCheck.mysql.foreignKeys{/lang}</dt>
+		<dd>
+			{if $results[mysql][foreignKeys]}
+				{@$statusOk} {lang}wcf.acp.systemCheck.pass{/lang}
+			{else}
+				{@$statusInsufficient} {lang}wcf.acp.systemCheck.notSupported{/lang}
+			{/if}
+			<small>{lang}wcf.acp.systemCheck.mysql.foreignKeys.description{/lang}</small>
+		</dd>
+	</dl>
 </section>
 
 <section class="section">
diff --git a/wcfsetup/install/files/lib/acp/page/SystemCheckPage.class.php b/wcfsetup/install/files/lib/acp/page/SystemCheckPage.class.php
index a66275191c..3a14e3ef2a 100644
--- a/wcfsetup/install/files/lib/acp/page/SystemCheckPage.class.php
+++ b/wcfsetup/install/files/lib/acp/page/SystemCheckPage.class.php
@@ -3,6 +3,7 @@ namespace wcf\acp\page;
 use wcf\data\application\Application;
 use wcf\page\AbstractPage;
 use wcf\system\exception\SystemException;
+use wcf\system\Regex;
 use wcf\system\WCF;
 use wcf\util\FileUtil;
 
@@ -79,6 +80,39 @@ class SystemCheckPage extends AbstractPage {
 		'recommended' => ['7.1', '7.2', '7.3'],
 	];
 	
+	public $foreignKeys = [
+		'wcf'. WCF_N .'_user' => [
+			'avatarID' => [
+				'referenceTable' => 'wcf'. WCF_N .'_user_avatar',
+				'referenceColumn' => 'avatarID'
+			]
+		],
+		'wcf'. WCF_N .'_comment' => [
+			'userID' => [
+				'referenceTable' => 'wcf'. WCF_N .'_user',
+				'referenceColumn' => 'userID'
+			],
+			'objectTypeID' => [
+				'referenceTable' => 'wcf'. WCF_N .'_object_type',
+				'referenceColumn' => 'objectTypeID'
+			]
+		],
+		'wcf'. WCF_N .'_moderation_queue' => [
+			'objectTypeID' => [
+				'referenceTable' => 'wcf'. WCF_N .'_object_type',
+				'referenceColumn' => 'objectTypeID'
+			],
+			'assignedUserID' => [
+				'referenceTable' => 'wcf'. WCF_N .'_user',
+				'referenceColumn' => 'userID'
+			],
+			'userID' => [
+				'referenceTable' => 'wcf'. WCF_N .'_user',
+				'referenceColumn' => 'userID'
+			]
+		]
+	];
+	
 	public $results = [
 		'directories' => [],
 		'mysql' => [
@@ -86,6 +120,7 @@ class SystemCheckPage extends AbstractPage {
 			'mariadb' => false,
 			'result' => false,
 			'version' => '0.0.0',
+			'foreignKeys' => false,
 		],
 		'php' => [
 			'extension' => [],
@@ -184,7 +219,23 @@ class SystemCheckPage extends AbstractPage {
 			}
 		}
 		
-		if ($this->results['mysql']['result'] && $this->results['mysql']['innodb']) {
+		// validate foreign keys
+		$this->results['mysql']['foreignKeys'] = true;
+		foreach ($this->foreignKeys as $table => $keys) {
+			$sql = "SHOW CREATE TABLE ". $table;
+			$statement = WCF::getDB()->prepareStatement($sql);
+			$statement->execute();
+			
+			$command = $statement->fetchSingleColumn(1);
+			foreach ($keys as $column => $reference) {
+				if (!Regex::compile('CONSTRAINT [`"]?(.)*[`"]? FOREIGN KEY \([`"]?'. $column .'[`"]?\) REFERENCES [`"]?'. $reference['referenceTable'] .'[`"]? \([`"]?'. $reference['referenceColumn'] .'[`"]?\)')->match($command)) {
+					$this->results['mysql']['foreignKeys'] = false;
+					break 2;
+				}
+			}
+		}
+		
+		if ($this->results['mysql']['result'] && $this->results['mysql']['innodb'] && $this->results['mysql']['foreignKeys']) {
 			$this->results['status']['mysql'] = true;
 		}
 	}
diff --git a/wcfsetup/install/lang/de.xml b/wcfsetup/install/lang/de.xml
index cf05a4951f..4950ba8a99 100644
--- a/wcfsetup/install/lang/de.xml
+++ b/wcfsetup/install/lang/de.xml
@@ -2704,6 +2704,8 @@ Kein Abschnitt darf leer sein und alle Abschnitten dürfen nur folgende Zeichen
 		<item name="wcf.acp.systemCheck.php.version.description"><![CDATA[Es wird mindestens PHP {$phpVersions[minimum]} benötigt, empfohlen werden die folgenden Versionsreihen von PHP: {implode from=$phpVersions[recommended] item=$recommended}{$recommended}{/implode}.]]></item>
 		<item name="wcf.acp.systemCheck.mysql.version"><![CDATA[MySQL]]></item>
 		<item name="wcf.acp.systemCheck.mysql.version.description"><![CDATA[Es wird mindestens MySQL {$mysqlVersions[mysql]} bzw. MariaDB {$mysqlVersions[mariadb][5]} oder MariaDB {$mysqlVersions[mariadb][10]} benötigt, grundsätzlich wird der Einsatz neuerer Version ausdrücklich empfohlen.]]></item>
+		<item name="wcf.acp.systemCheck.mysql.foreignKeys"><![CDATA[Fremdschlüssel]]></item>
+		<item name="wcf.acp.systemCheck.mysql.foreignKeys.description"><![CDATA[Fremdschlüssel werden mit der Installation in der Datenbank angelegt und werden für einen einwandfreien Betrieb benötigt.]]></item>
 		<item name="wcf.acp.systemCheck.php.extension"><![CDATA[Erweiterungen]]></item>
 		<item name="wcf.acp.systemCheck.php.extension.description"><![CDATA[Die offiziellen Erweiterungen von PHP stellen zusätzliche Funktionen bereit, die für den Betrieb der Software notwendig sind.]]></item>
 		<item name="wcf.acp.systemCheck.php.memoryLimit"><![CDATA[Verfügbarer Arbeitsspeicher („memory_limit“)]]></item>
diff --git a/wcfsetup/install/lang/en.xml b/wcfsetup/install/lang/en.xml
index fc9484e4bc..566be37cc2 100644
--- a/wcfsetup/install/lang/en.xml
+++ b/wcfsetup/install/lang/en.xml
@@ -2632,6 +2632,8 @@ If you have <strong>already bought the licenses for the listed apps</strong>, th
 		<item name="wcf.acp.systemCheck.php.version.description"><![CDATA[PHP {$phpVersions[minimum]} or newer is required, the following PHP series are strongly recommended: {implode from=$phpVersions[recommended] item=$recommended}{$recommended}{/implode}.]]></item>
 		<item name="wcf.acp.systemCheck.mysql.version"><![CDATA[MySQL]]></item>
 		<item name="wcf.acp.systemCheck.mysql.version.description"><![CDATA[MySQL {$mysqlVersions[mysql]} or MariaDB {$mysqlVersions[mariadb][5]} or MariaDB {$mysqlVersions[mariadb][10]} are required at minimum, but it’s strongly recommended to always use the latest available version.]]></item>
+		<item name="wcf.acp.systemCheck.mysql.foreignKeys"><![CDATA[Foreign Keys]]></item>
+		<item name="wcf.acp.systemCheck.mysql.foreignKeys.description"><![CDATA[Foreign Keys are created with the installation and are required for the software to work.]]></item>
 		<item name="wcf.acp.systemCheck.php.extension"><![CDATA[Extensions]]></item>
 		<item name="wcf.acp.systemCheck.php.extension.description"><![CDATA[The official PHP extensions provide additional capabilities and features that are required for the software to work.]]></item>
 		<item name="wcf.acp.systemCheck.php.memoryLimit"><![CDATA[Available Memory (“memory_limit”)]]></item>
-- 
2.20.1