1 files changed, 73 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 8fcd70a9..26bb1856 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
@@ -19,6 +19,7 @@ public class Updater {
 		addLectureFilter();
 		addPredefinedFilters();
 		addPredefinedNetworkShares();
+		addPredefinedRunScripts();
 	}
 
 	private static void addLectureLocationMapTable() throws SQLException {
@@ -246,6 +247,78 @@ public class Updater {
 			throw e;
 		}
 	}
+	
+	/**
+	 * Add tables for predefined runscripts. There's the main table for the
+	 * scripts, then we need an n:m table to connect lectures to runscripts, and
+	 * finally another n:m table to define which operating systems a script is
+	 * suitable for.
+	 */
+	private static void addPredefinedRunScripts() throws SQLException {
+		try (MysqlConnection connection = Database.getConnection()) {
+			if (!tableExists(connection, "presetrunscript")) {
+				connection.prepareStatement("CREATE TABLE presetrunscript ("
+						+ "  runscriptid int(11) NOT NULL AUTO_INCREMENT,"
+						+ "  scriptname varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,"
+						+ "  content text COLLATE utf8mb4_unicode_ci NOT NULL,"
+						+ "  extension varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,"
+						+ "  visibility tinyint(1) NOT NULL COMMENT '0 = hidden, 1 = normal, 2 = minimized',"
+						+ "  passcreds tinyint(1) NOT NULL,"
+						+ "  isglobal tinyint(1) NOT NULL COMMENT 'Whether to apply this script to all lectures',"
+						+ "  PRIMARY KEY (runscriptid),"
+						+ "  KEY isglobal (isglobal)"
+						+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci")
+						.executeUpdate();
+				LOGGER.info("Updated database: Created table presetrunscript");
+			}
+			if (!tableExists(connection, "presetrunscript_x_operatingsystem")) {
+				connection.prepareStatement("CREATE TABLE presetrunscript_x_operatingsystem ("
+						+ "  runscriptid int(11) NOT NULL,"
+						+ "  osid int(11) NOT NULL,"
+						+ "  PRIMARY KEY (runscriptid, osid),"
+						+ "  KEY osid (osid)"
+						+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci")
+						.executeUpdate();
+				connection.prepareStatement(
+						"ALTER TABLE presetrunscript_x_operatingsystem"
+								+ "  ADD CONSTRAINT osid FOREIGN KEY (osid)"
+								+ "    REFERENCES operatingsystem (osid)"
+								+ "    ON DELETE CASCADE ON UPDATE CASCADE,"
+								+ "  ADD CONSTRAINT runscriptid FOREIGN KEY (runscriptid)"
+								+ "    REFERENCES presetrunscript (runscriptid)"
+								+ "    ON DELETE CASCADE ON UPDATE CASCADE")
+								.executeUpdate();
+				LOGGER.info("Updated database: Created presetrunscript_x_operatingsystem table + constraint");
+			}
+			if (!tableExists(connection, "lecture_x_runscript")) {
+				connection.prepareStatement("CREATE TABLE lecture_x_runscript ("
+						+ "  lectureid char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,"
+						+ "  runscriptid int(11) NOT NULL,"
+						+ "  PRIMARY KEY (lectureid,runscriptid),"
+						+ "  KEY runscriptid (runscriptid)"
+						+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci")
+						.executeUpdate();
+				connection.prepareStatement(
+						"ALTER TABLE lecture_x_runscript"
+								+ "  ADD CONSTRAINT lecture_runscript FOREIGN KEY (lectureid)"
+								+ "    REFERENCES lecture (lectureid)"
+								+ "    ON DELETE CASCADE ON UPDATE CASCADE,"
+								+ "  ADD CONSTRAINT lecture_x_runscript_ibfk_1 FOREIGN KEY (runscriptid)"
+								+ "    REFERENCES presetrunscript (runscriptid)"
+								+ "    ON DELETE CASCADE ON UPDATE CASCADE")
+								.executeUpdate();
+				LOGGER.info("Updated database: Created lecture_x_runscript table + constraint");
+			}
+			connection.commit();
+		} catch (SQLException e) {
+			LOGGER.error("Query failed in addPredefinedRunScripts()", e);
+			throw e;
+		}
+	}
+	
+	/*
+	 * Helper methods
+	 */
 
 	/**
 	 * Check whether given table exists.