From 6973ac45c1f9735df2a86754b6cd6ae9861f39d8 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Wed, 3 Jul 2019 09:57:16 +0200
Subject: [exams] Fix creating global exam

---
 install.php                             |  7 ++++--
 modules-available/exams/install.inc.php | 39 ++++++++++++++++++++++++++++-----
 modules-available/exams/page.inc.php    |  5 ++++-
 3 files changed, 42 insertions(+), 9 deletions(-)

diff --git a/install.php b/install.php
index 5f5fe369..38cc3a1e 100644
--- a/install.php
+++ b/install.php
@@ -154,7 +154,7 @@ function tableGetConstraints($table, $column, $refTable, $refColumn)
  * @param string $actions "ON xxx ON yyy" string
  * @return string UPDATE_* result code
  */
-function tableAddConstraint($table, $column, $refTable, $refColumn, $actions, $ignoreError = false)
+function tableAddConstraint($table, $column, $refTable, $refColumn, $actions, $ignoreError = false, $name = '')
 {
 	$test = tableExists($refTable) && tableHasColumn($refTable, $refColumn);
 	if ($test === false) {
@@ -194,7 +194,10 @@ function tableAddConstraint($table, $column, $refTable, $refColumn, $actions, $i
 				WHERE `$refTable`.`$refColumn` IS NULL");
 	}
 	// Need to create
-	$ret = Database::exec("ALTER TABLE `$table` ADD CONSTRAINT FOREIGN KEY (`$column`)
+	if (!empty($name)) {
+		$name = "`$name`";
+	}
+	$ret = Database::exec("ALTER TABLE `$table` ADD CONSTRAINT $name FOREIGN KEY (`$column`)
 			REFERENCES `$refTable` (`$refColumn`)
 			ON DELETE $delete ON UPDATE $update");
 	if ($ret === false) {
diff --git a/modules-available/exams/install.inc.php b/modules-available/exams/install.inc.php
index dfd0ec7c..eac1790b 100644
--- a/modules-available/exams/install.inc.php
+++ b/modules-available/exams/install.inc.php
@@ -15,8 +15,9 @@ $res[] = tableCreate('exams', '
 
 $res[] = tableCreate('exams_x_location', '
 	 `examid` int(11) NOT NULL,
-	 `locationid` int(11) NOT NULL,
-	 PRIMARY KEY (`examid`, `locationid`)
+	 `locationid` int(11) NULL,
+	 UNIQUE KEY (`examid`, `locationid`),
+	 KEY (`locationid`)
 ');
 
 if (!tableHasColumn('exams', 'lectureid')) {
@@ -34,12 +35,38 @@ if (!tableHasColumn('exams', 'autologin')) {
 	$res[] = UPDATE_DONE;
 }
 
-Database::exec("ALTER TABLE `exams` CHANGE `description` `description` varchar(500) DEFAULT NULL");
+Database::exec("ALTER TABLE `exams` CHANGE `description` `description` varchar(500) NULL DEFAULT NULL");
 
-$res[] = tableAddConstraint('exams_x_location', 'examid', 'exams', 'examid',
-		'ON DELETE CASCADE ON UPDATE CASCADE');
+$ex = tableGetConstraints('exams_x_location', 'locationid', 'location', 'locationid');
+
+// 2019-07-02: Fix messed up non-NULL constraint
+if ($ex !== false && $ex['CONSTRAINT_NAME'] !== 'exl_locid_null') {
+	tableDeleteConstraint('exams_x_location', $ex['CONSTRAINT_NAME']);
+	// Again for the other one
+	$ex = tableGetConstraints('exams_x_location', 'examid', 'exams', 'examid');
+	if ($ex !== false) {
+		tableDeleteConstraint('exams_x_location', $ex['CONSTRAINT_NAME']);
+	}
+	// Get rid of all keys so we can make locationid NULLable
+	Database::exec('ALTER TABLE `exams_x_location` DROP PRIMARY KEY');
+	$r = Database::simpleQuery('SHOW INDEX FROM exams_x_location');
+	while (($name = $r->fetchColumn(2)) !== false) {
+		Database::exec("ALTER TABLE `exams_x_location` DROP INDEX `$name`");
+	}
+	$ret = Database::exec("ALTER TABLE `exams_x_location` MODIFY `locationid` int(11) NULL");
+	if ($ret === false) {
+		finalResponse(UPDATE_FAILED, 'Making locationid NULLable failed: ' . Database::lastError());
+	}
+	// Ad our two keys; can't use PRIMARY as it doesn't allow columns that can be NULL
+	Database::exec('ALTER TABLE `exams_x_location` ADD UNIQUE KEY (`examid`, `locationid`), ADD KEY (`locationid`)');
+}
+
+// Constraints for locationid and examid
 $res[] = tableAddConstraint('exams_x_location', 'locationid', 'location', 'locationid',
-		'ON DELETE CASCADE ON UPDATE CASCADE');
+	'ON DELETE CASCADE ON UPDATE CASCADE', false, 'exl_locid_null');
+$res[] = tableAddConstraint('exams_x_location', 'examid', 'exams', 'examid',
+	'ON DELETE CASCADE ON UPDATE CASCADE');
+
 
 if (in_array(UPDATE_DONE, $res)) {
     finalResponse(UPDATE_DONE, 'Tables created successfully');
diff --git a/modules-available/exams/page.inc.php b/modules-available/exams/page.inc.php
index 31593273..23a5bc39 100644
--- a/modules-available/exams/page.inc.php
+++ b/modules-available/exams/page.inc.php
@@ -287,7 +287,7 @@ class Page_Exams extends Page
 		/* process form-data */
 		$locationids = Request::post('locations', [], "ARRAY");
 
-		/* global room has id 0 */
+		/* global room is 0/NULL */
 		if (empty($locationids)) {
 			$locationids[] = 0;
 		}
@@ -296,6 +296,9 @@ class Page_Exams extends Page
 			Message::addError('main.no-permission');
 			Util::redirect('?do=exams');
 		}
+		if ($locationids[0] === 0) {
+			$locationids[0] = null;
+		}
 
 		$examid = Request::post('examid', 0, 'int');
 		$starttime = strtotime(Request::post('starttime_date') . " " . Request::post('starttime_time'));
-- 
cgit v1.2.3-55-g7522