From 7dc7a49e3704b52c4a40909050bf831826b3c41b Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Tue, 20 Feb 2018 15:20:26 +0100
Subject: [permissionmanager] Ensure uniqueness in role_x_location table,
 consistent table naming, drop unused id field

---
 .../permissionmanager/install.inc.php              | 34 +++++++++++++++++-----
 1 file changed, 27 insertions(+), 7 deletions(-)

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

diff --git a/modules-available/permissionmanager/install.inc.php b/modules-available/permissionmanager/install.inc.php
index 71ee7a1e..afa5dd7e 100644
--- a/modules-available/permissionmanager/install.inc.php
+++ b/modules-available/permissionmanager/install.inc.php
@@ -8,17 +8,23 @@ $res[] = tableCreate('role', "
 	PRIMARY KEY (roleid)
 ");
 
-$res[] = tableCreate('user_x_role', "
+if (tableExists('user_x_role')) {
+	if (tableExists('role_x_user')) {
+		Database::exec('DROP TABLE user_x_role');
+	} else {
+		$res[] = tableRename('user_x_role', 'role_x_user');
+	}
+}
+$res[] = tableCreate('role_x_user', "
 	userid int(10) unsigned NOT NULL,
 	roleid int(10) unsigned NOT NULL,
 	PRIMARY KEY (userid, roleid)
 ");
 
 $res[] = tableCreate('role_x_location', "
-	id int(10) unsigned NOT NULL AUTO_INCREMENT,
 	roleid int(10) unsigned NOT NULL,
 	locationid int(11),
-	PRIMARY KEY (id)
+	CONSTRAINT role_loc UNIQUE (roleid, locationid) 
 ");
 
 $res[] = tableCreate('role_x_permission', "
@@ -27,24 +33,38 @@ $res[] = tableCreate('role_x_permission', "
 	PRIMARY KEY (roleid, permissionid)
 ");
 
+if (tableHasColumn('role_x_location', 'id')) {
+	$cnt = Database::exec('DELETE a FROM role_x_location a, role_x_location b
+			WHERE a.roleid = b.roleid AND (a.locationid = b.locationid OR (a.locationid IS NULL AND b.locationid IS NULL))
+			AND a.id > b.id');
+	$ret = Database::exec('ALTER TABLE role_x_location DROP COLUMN id,
+			ADD CONSTRAINT role_loc UNIQUE (roleid, locationid)');
+	if ($ret === false) {
+		$res[] = UPDATE_NOOP;
+	} else {
+		$res[] = UPDATE_DONE;
+	}
+
+}
+
 if (!tableExists('user') || !tableExists('location')) {
 	finalResponse(UPDATE_RETRY, 'Cannot add constraint yet. Please retry.');
 } else {
-	$c = tableGetContraints('user_x_role', 'userid', 'user', 'userid');
+	$c = tableGetContraints('role_x_user', 'userid', 'user', 'userid');
 	if ($c === false)
 		finalResponse(UPDATE_FAILED, 'Cannot get constraints of user table: ' . Database::lastError());
 	if (empty($c)) {
-		$alter = Database::exec('ALTER TABLE user_x_role ADD FOREIGN KEY (userid) REFERENCES user (userid) ON DELETE CASCADE ON UPDATE CASCADE');
+		$alter = Database::exec('ALTER TABLE role_x_user ADD FOREIGN KEY (userid) REFERENCES user (userid) ON DELETE CASCADE ON UPDATE CASCADE');
 		if ($alter === false)
 			finalResponse(UPDATE_FAILED, 'Cannot add userid constraint referencing user table: ' . Database::lastError());
 		$res[] = UPDATE_DONE;
 	}
 
-	$c = tableGetContraints('user_x_role', 'roleid', 'role', 'roleid');
+	$c = tableGetContraints('role_x_user', 'roleid', 'role', 'roleid');
 	if ($c === false)
 		finalResponse(UPDATE_FAILED, 'Cannot get constraints of role table: ' . Database::lastError());
 	if (empty($c)) {
-		$alter = Database::exec('ALTER TABLE user_x_role ADD FOREIGN KEY (roleid) REFERENCES role (roleid) ON DELETE CASCADE ON UPDATE CASCADE');
+		$alter = Database::exec('ALTER TABLE role_x_user ADD FOREIGN KEY (roleid) REFERENCES role (roleid) ON DELETE CASCADE ON UPDATE CASCADE');
 		if ($alter === false)
 			finalResponse(UPDATE_FAILED, 'Cannot add roleid constraint referencing role table: ' . Database::lastError());
 		$res[] = UPDATE_DONE;
-- 
cgit v1.2.3-55-g7522