|
|
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();
}
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;
}
}
private static void addHasUsbAccessField() 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("hasusbaccess")) {
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()) {
MysqlStatement tablesStmt = connection.prepareStatement("SHOW TABLES");
ResultSet tables = tablesStmt.executeQuery();
while (tables.next()) {
if (tables.getString(1).equals("actionlog")) {
return; // Table exists, don't do anything
}
}
// 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()) {
MysqlStatement checkStmt = connection.prepareStatement("DESCRIBE user");
ResultSet cols = checkStmt.executeQuery();
while (cols.next()) {
if (cols.getString("Field").equals("email")
&& "varchar(100)".equalsIgnoreCase(cols.getString("Type"))) {
return; // Field exists and has proper type, 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;
}
}
}
|