2 files changed, 106 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
new file mode 100644
index 00000000..89bbbf5c
--- /dev/null
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Updater.java
@@ -0,0 +1,67 @@
+package org.openslx.bwlp.sat.database;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+import org.apache.log4j.Logger;
+
+public class Updater {
+
+	private static final Logger LOGGER = Logger.getLogger(Updater.class);
+
+	public static void updateDatabase() throws SQLException {
+		addLocationPrivateField();
+		addLectureLocationMapTable();
+	}
+
+	private static void addLectureLocationMapTable() 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("lecture_x_location")) {
+					return; // Table exists, don't do anything
+				}
+			}
+			// Add table
+			MysqlStatement tableAddStmt = connection.prepareStatement("CREATE TABLE `lecture_x_location` ("
+					+ " `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,"
+					+ " `locationid` int(11) NOT NULL,"
+					+ " PRIMARY KEY (`lectureid`,`locationid`), KEY locationid (locationid)"
+					+ " ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
+			tableAddStmt.executeUpdate();
+			// Add constraint
+			MysqlStatement constraintStmt = connection.prepareStatement("ALTER TABLE `lecture_x_location`"
+					+ " ADD FOREIGN KEY ( `lectureid` ) REFERENCES `sat`.`lecture` (`lectureid`)"
+					+ " ON DELETE CASCADE ON UPDATE CASCADE");
+			constraintStmt.executeUpdate();
+			connection.commit();
+			LOGGER.info("Updated database: Added lecture-location mapping table");
+		} catch (SQLException e) {
+			LOGGER.error("Query failed in Updater.addLectureLocationMapTable()", e);
+			throw e;
+		}
+	}
+
+	private static void addLocationPrivateField() throws SQLException {
+		try (MysqlConnection connection = Database.getConnection()) {
+			MysqlStatement checkStmt = connection.prepareStatement("DESCRIBE lecture");
+			ResultSet cols = checkStmt.executeQuery();
+			while (cols.next()) {
+				if (cols.getString("Field").equals("islocationprivate")) {
+					return; // Field exists, don't do anything
+				}
+			}
+			// Add field to table
+			MysqlStatement columnAddStmt = connection.prepareStatement("ALTER TABLE lecture"
+					+ " ADD islocationprivate TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AFTER isprivate");
+			columnAddStmt.executeUpdate();
+			connection.commit();
+			LOGGER.info("Updated database: Added is location private field in lecture table");
+		} catch (SQLException e) {
+			LOGGER.error("Query failed in Updater.addLocationPrivateField()", e);
+			throw e;
+		}
+	}
+
+}
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLocation.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLocation.java
new file mode 100644
index 00000000..e14bcf0d
--- /dev/null
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLocation.java
@@ -0,0 +1,39 @@
+package org.openslx.bwlp.sat.database.mappers;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
+
+import org.apache.log4j.Logger;
+import org.openslx.bwlp.sat.database.Database;
+import org.openslx.bwlp.sat.database.MysqlConnection;
+import org.openslx.bwlp.sat.database.MysqlStatement;
+import org.openslx.bwlp.sat.util.Configuration;
+import org.openslx.bwlp.sat.util.Util;
+import org.openslx.bwlp.thrift.iface.Location;
+
+public class DbLocation {
+
+	private static final Logger LOGGER = Logger.getLogger(DbLocation.class);
+
+	public static final List<Location> getLocations() throws SQLException {
+		List<Location> list = new ArrayList<>();
+		String locationsTable = Configuration.getDbLocationTable();
+		if (Util.isEmptyString(locationsTable))
+			return list;
+		try (MysqlConnection connection = Database.getConnection()) {
+			MysqlStatement stmt = connection.prepareStatement("SELECT locationid, locationname FROM "
+					+ locationsTable);
+			ResultSet rs = stmt.executeQuery();
+			while (rs.next()) {
+				list.add(new Location(rs.getInt("locationid"), rs.getString("locationname")));
+			}
+		} catch (SQLException e) {
+			LOGGER.error("Query failed in DbLocation.getLocations()", e);
+			throw e;
+		}
+		return list;
+	}
+
+}