blob: 62f1f5a4790000e3c175513f8c8c32f03445c98e (
plain) (
tree)
|
|
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();
}
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;
}
}
}
|