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

---
 modules-available/exams/install.inc.php | 39 ++++++++++++++++++++++++++++-----
 1 file changed, 33 insertions(+), 6 deletions(-)

(limited to 'modules-available/exams/install.inc.php')

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');
-- 
cgit v1.2.3-55-g7522