path: root/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/Updater.java

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) 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;
		}
	}
}