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();
addHasUsbAccessField();
addLogTable();
fixEmailFieldLength();
addNetworkShares();
addLectureFilter();
addPredefinedFilters();
addPredefinedNetworkShares();
addPredefinedRunScripts();
addPredefinedNetworkRules();
}
private static void addLectureLocationMapTable() throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
if (tableExists(connection, "lecture_x_location"))
return;
// 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()) {
if (getColumnType(connection, "lecture", "islocationprivate") != null)
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;
}
}
private static void addHasUsbAccessField() throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
if (getColumnType(connection, "lecture", "hasusbaccess") != null)
return; // Field exists, don't do anything
// Add field to table
MysqlStatement columnAddStmt = connection.prepareStatement("ALTER TABLE lecture"
+ " ADD hasusbaccess TINYINT(1) UNSIGNED NOT NULL DEFAULT 1 AFTER hasinternetaccess");
columnAddStmt.executeUpdate();
connection.commit();
LOGGER.info("Updated database: Added 'hasusbaccess' private field in lecture table");
} catch (SQLException e) {
LOGGER.error("Query failed in Updater.addHasUsbAccessField()", e);
throw e;
}
}
private static void addLogTable() throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
if (tableExists(connection, "actionlog"))
return;
// Add table
MysqlStatement tableAddStmt = connection.prepareStatement("CREATE TABLE `actionlog` ("
+ " `actionid` int(11) NOT NULL AUTO_INCREMENT,"
+ " `dateline` bigint(20) NOT NULL,"
+ " `userid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,"
+ " `targetid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,"
+ " `description` varchar(500) NOT NULL,"
+ " PRIMARY KEY (`actionid`),"
+ " KEY userid (userid, dateline),"
+ " KEY targetid (targetid, dateline),"
+ " KEY dateline (dateline)"
+ " ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
tableAddStmt.executeUpdate();
// Add constraint
MysqlStatement constraintStmt = connection.prepareStatement("ALTER TABLE `actionlog`"
+ " ADD FOREIGN KEY ( `userid` ) REFERENCES `sat`.`user` (`userid`)"
+ " ON DELETE SET NULL ON UPDATE CASCADE");
constraintStmt.executeUpdate();
connection.commit();
LOGGER.info("Updated database: Added actionlog table");
} catch (SQLException e) {
LOGGER.error("Query failed in Updater.addLogTable()", e);
throw e;
}
}
/**
* Make email field longer. Was 50 chars, which is not enough in rare cases
* :)
*/
private static void fixEmailFieldLength() throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
if ("varchar(100)".equals(getColumnType(connection, "user", "email")))
return; // Already 100 chars long, don'T do anything
MysqlStatement upStmt = connection.prepareStatement("ALTER TABLE user CHANGE email"
+ " email VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL");
upStmt.executeUpdate();
connection.commit();
LOGGER.info("Updated database: Made email field longer");
} catch (SQLException e) {
LOGGER.error("Query failed in Updater.fixEmailFieldLength()", e);
throw e;
}
}
private static void addNetworkShares() throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
if (tableExists(connection, "networkshare"))
return;
// Add table
MysqlStatement tableAddStmt = connection.prepareStatement(
"CREATE TABLE `networkshare` ("
+ " `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,"
+ " `sharepresetid` int(11) NULL DEFAULT NULL,"
+ " `sharedata` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,"
+ " KEY `sharepresetid` (`sharepresetid`),"
+ " KEY `fk_lectureid_1` (`lectureid`)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
tableAddStmt.executeUpdate();
// Add constraint
MysqlStatement constraintStmt = connection.prepareStatement(
"ALTER TABLE `networkshare` ADD CONSTRAINT `fk_lectureid_1`"
+ " FOREIGN KEY (`lectureid`) REFERENCES `sat`.`lecture` (`lectureid`)"
+ " ON DELETE CASCADE ON UPDATE CASCADE");
constraintStmt.executeUpdate();
connection.commit();
LOGGER.info("Updated database: Added networkshare table");
} catch (SQLException e) {
LOGGER.error("Query failed in Updater.addNetworkShares()", e);
throw e;
}
}
private static void addLectureFilter() throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
if (tableExists(connection, "lecturefilter"))
return;
// 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 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();
// 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;
}
}
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`),"
+ " KEY sharename (`sharename`)"
+ ") 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;
}
}
/**
* 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),"
+ " KEY scriptname (scriptname)"
+ ") 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;
}
}
private static void addPredefinedNetworkRules() throws SQLException {
try (MysqlConnection connection = Database.getConnection()) {
if (!tableExists(connection, "presetnetworkrule")) {
MysqlStatement addStmt = connection.prepareStatement("CREATE TABLE `presetnetworkrule` ("
+ " `ruleid` int(11) NOT NULL AUTO_INCREMENT,"
+ " `rulename` varchar(100) NOT NULL,"
+ " `ruledata` text NOT NULL,"
+ " PRIMARY KEY (`ruleid`),"
+ " KEY rulename (`rulename`)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
addStmt.executeUpdate();
LOGGER.info("Updated database: Added preset network rules");
}
if (!tableExists(connection, "lecture_x_networkrule")) {
connection.prepareStatement("CREATE TABLE lecture_x_networkrule ("
+ " lectureid char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,"
+ " ruleid int(11) NOT NULL,"
+ " PRIMARY KEY (lectureid,ruleid),"
+ " KEY ruleid (ruleid)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci")
.executeUpdate();
connection.prepareStatement(
"ALTER TABLE lecture_x_networkrule"
+ " ADD CONSTRAINT lecture_x_networkrule_ibfk_1 FOREIGN KEY (lectureid)"
+ " REFERENCES lecture (lectureid)"
+ " ON DELETE CASCADE ON UPDATE CASCADE,"
+ " ADD CONSTRAINT lecture_x_networkrule_ibfk_2 FOREIGN KEY (ruleid)"
+ " REFERENCES presetnetworkrule (ruleid)"
+ " ON DELETE CASCADE ON UPDATE CASCADE")
.executeUpdate();
LOGGER.info("Updated database: Created lecture_x_networkrule table + constraint");
}
connection.commit();
} catch (SQLException e) {
LOGGER.error("Query failed in Updater.addPredefinedNetworkShares()", e);
throw e;
}
}
/*
* Helper methods
*/
/**
* 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;
}
}
