diff options
Diffstat (limited to 'dozentenmodulserver/src')
-rw-r--r-- | dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Updater.java | 118 |
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; + } } |