[server] Updater: Create preset filters/netshares tables, constraints

1 files changed, 112 insertions, 6 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 0dcfac4f..d2afb224 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
@@ -17,6 +17,8 @@ public class Updater {
 		fixEmailFieldLength();
 		addNetworkShares();
 		addLectureFilter();
+		addPredefinedFilters();
+		addPredefinedNetworkShares();
 	}
 
 	private static void addLectureLocationMapTable() throws SQLException {
@@ -162,11 +164,10 @@ public class Updater {
 			// Add table
 			MysqlStatement tableAddStmt = connection.prepareStatement(
 					"CREATE TABLE `networkshare` ("
-					+ " `shareid` int(11) NOT NULL AUTO_INCREMENT,"
 					+ " `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,"
-					+ " `shareuid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,"
+					+ " `sharepresetid` int(11) NULL DEFAULT NULL,"
 					+ " `sharedata` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,"
-					+ " PRIMARY KEY (`shareid`),"
+					+ " KEY `shareid` (`shareid`),"
 					+ " KEY `fk_lectureid_1` (`lectureid`)"
 					+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
 			tableAddStmt.executeUpdate();
@@ -197,9 +198,9 @@ public class Updater {
 			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,"
+					+ "  `filtertype` varchar(24) CHARACTER SET ascii NULL DEFAULT NULL,"
+					+ "  `filterkey` varchar(24) NULL DEFAULT NULL,"
+					+ "  `filtervalue` varchar(200) NULL DEFAULT NULL,"
 					+ "  KEY `lectureid` (`lectureid`,`filtertype`)"
 					+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
 			tableAddStmt.executeUpdate();
@@ -216,5 +217,110 @@ public class Updater {
 			throw e;
 		}
 	}
+	
+	private static void addPredefinedFilters() throws SQLException {
+		try (MysqlConnection connection = Database.getConnection()) {
+			if (!tableExists(connection, "presetlecturefilter")) {
+				// Add table
+				MysqlStatement tableAddStmt = connection.prepareStatement("CREATE TABLE presetlecturefilter ("
+						+ "      filterid int(11) NOT NULL AUTO_INCREMENT,"
+						+ "      filtertype varchar(24) CHARACTER SET ascii NOT NULL,"
+						+ "      filtername varchar(100) NOT NULL,"
+						+ "      filterkey varchar(24) NOT NULL,"
+						+ "      filtervalue varchar(200) NOT NULL,"
+						+ "      PRIMARY KEY (filterid),"
+						+ "      KEY (filtertype, filtername)"
+						+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
+				tableAddStmt.executeUpdate();
+				LOGGER.info("Updated database: Added preset lecture filters");
+			}
+			if (getColumnType(connection, "lecturefilter", "filterpresetid") == null) {
+				// Add column and constraint to lecturefilter
+				MysqlStatement constraintStmt = connection.prepareStatement(
+						"ALTER TABLE lecturefilter "
+						+ " CHANGE filtertype filtertype VARCHAR(24) CHARACTER SET ascii NULL DEFAULT NULL,"
+						+ " CHANGE filterkey filterkey VARCHAR(24) NULL DEFAULT NULL,"
+						+ " CHANGE filtervalue filtervalue VARCHAR(200) NULL DEFAULT NULL,"
+						+ " ADD COLUMN filterpresetid int(11) NULL DEFAULT NULL AFTER lectureid, "
+						+ " ADD KEY filterpresetid (filterpresetid), "
+						+ " ADD CONSTRAINT `filterpresetid` FOREIGN KEY (`filterpresetid`) REFERENCES `presetlecturefilter` (`filterid`)"
+						+ "     ON DELETE CASCADE ON UPDATE CASCADE");
+				constraintStmt.executeUpdate();
+			}
+			connection.commit();
+		} catch (SQLException e) {
+			LOGGER.error("Query failed in Updater.addPredefinedFilters()", e);
+			throw e;
+		}
+	}
+	
+	private static void addPredefinedNetworkShares() throws SQLException {
+		try (MysqlConnection connection = Database.getConnection()) {
+			if (!tableExists(connection, "presetnetworkshare")) {
+				MysqlStatement addStmt = connection.prepareStatement("CREATE TABLE `presetnetworkshare` ("
+						+ "  `shareid` int(11) NOT NULL AUTO_INCREMENT,"
+						+ "  `sharename` varchar(100) NOT NULL,"
+						+ "  `sharedata` varchar(500) NOT NULL,"
+						+ "  `active` tinyint(1) NOT NULL DEFAULT '0',"
+						+ "  PRIMARY KEY (`shareid`)"
+						+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
+				addStmt.executeUpdate();
+				LOGGER.info("Updated database: Added preset network shares");
+			}
+			if (getColumnType(connection, "networkshare", "sharepresetid") == null) {
+				MysqlStatement alterStmt = connection.prepareStatement("ALTER TABLE networkshare"
+						+ " DROP COLUMN shareid, DROP COLUMN shareuid,"
+						+ " ADD COLUMN sharepresetid int(11) NULL DEFAULT NULL,"
+						+ " ADD KEY sharepresetid (sharepresetid),"
+						+ " ADD CONSTRAINT sharepresetid FOREIGN KEY (sharepresetid) REFERENCES presetnetworkshare (shareid)"
+						+ "     ON DELETE CASCADE ON UPDATE CASCADE");
+				alterStmt.executeUpdate();
+			}
+			connection.commit();
+		} catch (SQLException e) {
+			LOGGER.error("Query failed in Updater.addPredefinedNetworkShares()", e);
+			throw e;
+		}
+	}
+
+	/**
+	 * Check whether given table exists.
+	 * @throws SQLException
+	 */
+	private static boolean tableExists(MysqlConnection connection, String name) throws SQLException {
+		MysqlStatement tablesStmt = connection.prepareStatement("SHOW TABLES");
+		ResultSet tables = tablesStmt.executeQuery();
+		boolean exists = false;
+		while (tables.next()) {
+			if (tables.getString(1).equals(name)) {
+				exists = true;
+				break;
+			}
+		}
+		tablesStmt.close();
+		return exists;
+	}
+	
+	/**
+	 * Return Type string of given column. Returns null if column doesn't exist,
+	 * so this can also be used to check for column existence in a table. Note
+	 * that a nonexistent table is considered an error and generates an
+	 * exception.
+	 * 
+	 * @throws SQLException
+	 */
+	private static String getColumnType(MysqlConnection connection, String table, String column)
+			throws SQLException {
+		MysqlStatement checkStmt = connection.prepareStatement("DESCRIBE " + table);
+		ResultSet cols = checkStmt.executeQuery();
+		String ret = null;
+		while (cols.next()) {
+			if (cols.getString("Field").equals(column)) {
+				ret = cols.getString("Type");
+				break;
+			}
+		}
+		return ret;
+	}
 
 }