[server] Add ugly workaround to fix messed up userids from dozmod 1.0 import

2 files changed, 79 insertions, 46 deletions

diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/App.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/App.java
index 97029e55..931b2f23 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/App.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/App.java
@@ -3,10 +3,7 @@ package org.openslx.bwlp.sat;
 import java.io.IOException;
 import java.security.KeyManagementException;
 import java.security.NoSuchAlgorithmException;
-import java.sql.SQLException;
-import java.util.Date;
 import java.util.HashSet;
-import java.util.List;
 import java.util.Set;
 
 import javax.net.ssl.SSLContext;
@@ -16,8 +13,7 @@ import org.apache.log4j.ConsoleAppender;
 import org.apache.log4j.Logger;
 import org.apache.log4j.PatternLayout;
 import org.apache.thrift.transport.TTransportException;
-import org.openslx.bwlp.sat.database.Database;
-import org.openslx.bwlp.sat.database.mappers.DbImage;
+import org.openslx.bwlp.sat.database.mappers.DbUser;
 import org.openslx.bwlp.sat.fileserv.FileServer;
 import org.openslx.bwlp.sat.maintenance.DeleteOldImages;
 import org.openslx.bwlp.sat.maintenance.DeleteOldLectures;
@@ -30,15 +26,9 @@ import org.openslx.bwlp.sat.thrift.cache.VirtualizerList;
 import org.openslx.bwlp.sat.util.Configuration;
 import org.openslx.bwlp.sat.util.Identity;
 import org.openslx.bwlp.sat.web.WebServer;
-import org.openslx.bwlp.thrift.iface.ImageSummaryRead;
-import org.openslx.bwlp.thrift.iface.NetDirection;
-import org.openslx.bwlp.thrift.iface.NetRule;
-import org.openslx.bwlp.thrift.iface.UserInfo;
 import org.openslx.thrifthelper.ThriftManager;
 import org.openslx.thrifthelper.ThriftManager.ErrorCallback;
-import org.openslx.util.Json;
 import org.openslx.util.QuickTimer;
-import org.openslx.util.QuickTimer.Task;
 
 public class App {
 
@@ -118,6 +108,8 @@ public class App {
 
 		// Start watch dog to ensure nobody else is messing with the vmstore
 		QuickTimer.scheduleAtFixedDelay(new StorageUseCheck(), 10000, 60000);
+		// Set a flag that we need to convert userids if applicable
+		DbUser.checkIfLegacyUsersExist();
 
 		// Set up maintenance tasks
 		DeleteOldImages.init();
@@ -138,29 +130,6 @@ public class App {
 		t = new Thread(new WebServer(9080));
 		t.setDaemon(true);
 		t.start();
-		// DEBUG
-		if (DEBUG) {
-			Database.printCharsetInformation();
-			List<ImageSummaryRead> allVisible;
-			try {
-				allVisible = DbImage.getAllVisible(new UserInfo("bla", "blu", null, null, null), null, 0);
-				LOGGER.info("Got " + allVisible.size());
-			} catch (SQLException e) {
-				LOGGER.warn("could not test query getallvisible");
-			}
-			QuickTimer.scheduleAtFixedDelay(new Task() {
-				@Override
-				public void fire() {
-					Database.printDebug();
-				}
-			}, 100, 5000);
-			NetRule nr = new NetRule(2, NetDirection.OUT, "dsafg", 1336);
-			String data = Json.serialize(nr);
-			LOGGER.info(data);
-			Json.registerThriftClass(NetRule.class);
-			NetRule nn = Json.deserializeThrift(data, NetRule.class);
-			LOGGER.info(nn);
-		}
 
 		Runtime.getRuntime().addShutdownHook(new Thread() {
 			@Override
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbUser.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbUser.java
index 85774dde..bc63894c 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbUser.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbUser.java
@@ -16,10 +16,14 @@ import org.openslx.bwlp.sat.database.models.LocalUser;
 import org.openslx.bwlp.thrift.iface.SatelliteUserConfig;
 import org.openslx.bwlp.thrift.iface.TNotFoundException;
 import org.openslx.bwlp.thrift.iface.UserInfo;
+import org.openslx.util.QuickTimer;
+import org.openslx.util.QuickTimer.Task;
 import org.openslx.util.TimeoutHashMap;
 
 public class DbUser {
 
+	private static boolean legacyUsersExist = false;
+
 	public static class User {
 		public final UserInfo ui;
 		public final LocalUser local;
@@ -119,24 +123,84 @@ public class DbUser {
 		if (ui.eMail.length() > 50) {
 			ui.eMail = ui.eMail.substring(0, 50);
 		}
+		boolean recheckLegacy = true;
 		try (MysqlConnection connection = Database.getConnection()) {
-			MysqlStatement stmt = connection.prepareStatement("INSERT INTO user"
-					+ " (userid, firstname, lastname, email, organizationid, lastlogin, canlogin, issuperuser)"
-					+ " VALUES"
-					+ " (:userid, :firstname, :lastname, :email, :organizationid, UNIX_TIMESTAMP(), 1, 0)"
-					+ " ON DUPLICATE KEY UPDATE lastlogin = UNIX_TIMESTAMP(), email = VALUES(email),"
-					+ " firstname = VALUES(firstname), lastname = VALUES(lastname), organizationid = VALUES(organizationid)");
-			stmt.setString("userid", ui.userId);
-			stmt.setString("firstname", ui.firstName);
-			stmt.setString("lastname", ui.lastName);
-			stmt.setString("email", ui.eMail);
-			stmt.setString("organizationid", ui.organizationId);
-			stmt.executeUpdate();
+			if (!legacyUsersExist || !tryLegacyUserUpdate(connection, ui)) {
+				// No legacy messed up account found - use normal way
+				MysqlStatement insUpStmt = connection.prepareStatement("INSERT INTO user"
+						+ " (userid, firstname, lastname, email, organizationid, lastlogin, canlogin, issuperuser)"
+						+ " VALUES"
+						+ " (:userid, :firstname, :lastname, :email, :organizationid, UNIX_TIMESTAMP(), 1, 0)"
+						+ " ON DUPLICATE KEY UPDATE lastlogin = UNIX_TIMESTAMP(), email = VALUES(email),"
+						+ " firstname = VALUES(firstname), lastname = VALUES(lastname), organizationid = VALUES(organizationid)");
+				insUpStmt.setString("userid", ui.userId);
+				insUpStmt.setString("firstname", ui.firstName);
+				insUpStmt.setString("lastname", ui.lastName);
+				insUpStmt.setString("email", ui.eMail);
+				insUpStmt.setString("organizationid", ui.organizationId);
+				insUpStmt.executeUpdate();
+				recheckLegacy = false;
+			}
 			connection.commit();
 		} catch (SQLException e) {
 			LOGGER.error("Query failed in DbUser.writeUserOnLogin()", e);
 			throw e;
 		}
+		if (recheckLegacy) {
+			// Run check again
+			checkIfLegacyUsersExist();
+		}
+	}
+
+	/**
+	 * Try to update a legacy imported user and fix the entry
+	 */
+	private static boolean tryLegacyUserUpdate(MysqlConnection connection, UserInfo ui) throws SQLException {
+		// Well... don't look at the code below. The old server had a bug where it wrote
+		// wrong user ids to the data base. If we imported old data, the user info table
+		// might contain a messed up entry for the given user. So instead of a nice
+		// INSERT ... ON DUPLICATE KEY we have to do this funny stuff.
+		MysqlStatement findStmt = connection.prepareStatement("SELECT userid FROM user"
+				+ " WHERE firstname = :firstname AND lastname = :lastname AND email = :email");
+		findStmt.setString("firstname", ui.firstName);
+		findStmt.setString("lastname", ui.lastName);
+		findStmt.setString("email", "@" + ui.eMail + "@");
+		ResultSet rs = findStmt.executeQuery();
+		if (!rs.next())
+			return false;
+		// We actually found an old imported entry - just update
+		String oldId = rs.getString("userid");
+		MysqlStatement insUpStmt = connection.prepareStatement("UPDATE IGNORE user"
+				+ " SET lastlogin = UNIX_TIMESTAMP(), email = :email, userid = :newuserid, organizationid = :organizationid"
+				+ " WHERE userid = :olduserid");
+		insUpStmt.setString("newuserid", ui.userId);
+		insUpStmt.setString("email", ui.eMail);
+		insUpStmt.setString("organizationid", ui.organizationId);
+		insUpStmt.setString("olduserid", oldId);
+		insUpStmt.executeUpdate();
+		// Be extra safe: in case the update failed (dup key?) we patch the old entry so it doesn't look like an old one anymore
+		MysqlStatement fixStmt = connection.prepareStatement("UPDATE user SET"
+				+ " email = 'void', emailnotifications = 0 WHERE userid = :olduserid");
+		fixStmt.setString("olduserid", oldId);
+		fixStmt.executeUpdate();
+		return true;
+	}
+
+	public static void checkIfLegacyUsersExist() {
+		QuickTimer.scheduleOnce(new Task() {
+			@Override
+			public void fire() {
+				try (MysqlConnection connection = Database.getConnection()) {
+					MysqlStatement stmt = connection.prepareStatement("SELECT userid FROM user"
+							+ " WHERE email = '@%@' LIMIT 1");
+					ResultSet rs = stmt.executeQuery();
+					legacyUsersExist = rs.next();
+				} catch (SQLException e) {
+					LOGGER.error("Query failed in DbUser.checkIfLegacyUsersExist()", e);
+				}
+				LOGGER.info("Imported legacy users exist: " + Boolean.toString(legacyUsersExist));
+			}
+		});
 	}
 
 	public static User getCached(String userId) throws SQLException, TNotFoundException {