Add support for LDAP lecture filters

1 files changed, 36 insertions, 0 deletions

diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Updater.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Updater.java
index 423428c0..003abc05 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Updater.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Updater.java
@@ -16,6 +16,7 @@ public class Updater {
 		addLogTable();
 		fixEmailFieldLength();
 		addNetworkShares();
+		addLectureFilter();
 	}
 
 	private static void addLectureLocationMapTable() throws SQLException {
@@ -182,4 +183,39 @@ public class Updater {
 			throw e;
 		}
 	}
+
+	private static void addLectureFilter() throws SQLException {
+		try (MysqlConnection connection = Database.getConnection()) {
+			MysqlStatement tablesStmt = connection.prepareStatement("SHOW TABLES");
+			ResultSet tables = tablesStmt.executeQuery();
+			while (tables.next()) {
+				if (tables.getString(1).equals("lecturefilter")) {
+					return; // Table exists, don't do anything
+				}
+			}
+			// Add table
+			MysqlStatement tableAddStmt = connection.prepareStatement(
+					"CREATE TABLE `lecturefilter` ("
+					+ "  `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,"
+					+ "  `filtertype` varchar(24) CHARACTER SET ascii NOT NULL,"
+					+ "  `filterkey` varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL,"
+					+ "  `filtervalue` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,"
+					+ "  KEY `lectureid` (`lectureid`,`filtertype`)"
+					+ " KEY `fk_lectureid_1` (`lectureid`)"
+					+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
+			tableAddStmt.executeUpdate();
+			// Add constraint
+			MysqlStatement constraintStmt = connection.prepareStatement(
+					"ALTER TABLE `lecturefilter` ADD "
+					+ " CONSTRAINT `lectureid` FOREIGN KEY (`lectureid`) REFERENCES `lecture` (`lectureid`)"
+					+ " ON DELETE CASCADE ON UPDATE CASCADE");
+			constraintStmt.executeUpdate();
+			connection.commit();
+			LOGGER.info("Updated database: Added lecture filter table");
+		} catch (SQLException e) {
+			LOGGER.error("Query failed in Updater.addLectureFilter()", e);
+			throw e;
+		}
+	}
+
 }