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

package org.openslx.bwlp.sat.database.mappers;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.UUID;
import java.util.concurrent.TimeUnit;

import org.apache.log4j.Logger;
import org.openslx.bwlp.sat.database.Database;
import org.openslx.bwlp.sat.database.MysqlConnection;
import org.openslx.bwlp.sat.database.MysqlStatement;
import org.openslx.bwlp.sat.database.models.LocalImageVersion;
import org.openslx.bwlp.sat.mail.MailGenerator;
import org.openslx.bwlp.sat.permissions.User;
import org.openslx.bwlp.sat.thrift.cache.OperatingSystemList;
import org.openslx.bwlp.sat.util.Util;
import org.openslx.bwlp.sat.web.VmChooserEntryXml;
import org.openslx.bwlp.sat.web.VmChooserListXml;
import org.openslx.bwlp.thrift.iface.LectureRead;
import org.openslx.bwlp.thrift.iface.LectureSummary;
import org.openslx.bwlp.thrift.iface.LectureWrite;
import org.openslx.bwlp.thrift.iface.NetRule;
import org.openslx.bwlp.thrift.iface.TNotFoundException;
import org.openslx.bwlp.thrift.iface.UserInfo;
import org.openslx.util.Json;
import org.openslx.util.vm.VmMetaData;
import org.openslx.util.vm.VmwareMetaData;

import com.google.gson.JsonParseException;

public class DbLecture {

	private static final Logger LOGGER = Logger.getLogger(DbLecture.class);

	private static void setWriteFields(MysqlStatement stmt, String lectureId, LectureWrite lecture,
			UserInfo updatingUser) throws SQLException {
		if (lecture.lectureName.length() > 100) {
			lecture.lectureName = lecture.lectureName.substring(0, 100);
		}
		String nicsJson = null;
		if (lecture.nics != null && !lecture.nics.isEmpty()) {
			for (;;) {
				nicsJson = Json.serialize(lecture.nics);
				if (nicsJson.length() < 200)
					break;
				lecture.nics.remove(0);
			}
		}
		String netruleJson;
		if (lecture.networkExceptions == null) {
			netruleJson = null;
		} else {
			netruleJson = Json.serialize(lecture.networkExceptions);
		}
		stmt.setString("lectureid", lectureId);
		stmt.setString("displayname", lecture.lectureName);
		stmt.setString("description", lecture.description);
		stmt.setString("imageversionid", lecture.imageVersionId);
		stmt.setBoolean("autoupdate", lecture.autoUpdate);
		stmt.setBoolean("isenabled", lecture.isEnabled);
		stmt.setLong("starttime", lecture.startTime);
		stmt.setLong("endtime", lecture.endTime);
		stmt.setString("updaterid", updatingUser.userId);
		stmt.setString("runscript", lecture.runscript);
		stmt.setString("nics", nicsJson);
		stmt.setString("netrules", netruleJson);
		stmt.setBoolean("isexam", lecture.isExam);
		stmt.setBoolean("hasinternetaccess", lecture.hasInternetAccess);
		stmt.setBoolean("caneditdefault", lecture.defaultPermissions.edit);
		stmt.setBoolean("canadmindefault", lecture.defaultPermissions.admin);
	}

	public static String create(UserInfo user, LectureWrite lecture) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("INSERT INTO lecture"
					+ " (lectureid, displayname, description, imageversionid, autoupdate,"
					+ "  isenabled, starttime, endtime, createtime, updatetime,"
					+ "  ownerid, updaterid, runscript, nics, netrules, isexam,"
					+ "  hasinternetaccess, caneditdefault, canadmindefault)"
					+ "         VALUES             "
					+ " (:lectureid, :displayname, :description, :imageversionid, :autoupdate,"
					+ "  :isenabled, :starttime, :endtime, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(),"
					+ "  :ownerid, :updaterid, :runscript, :nics, :netrules, :isexam,"
					+ "  :hasinternetaccess, :caneditdefault, :canadmindefault)");
			String lectureId = UUID.randomUUID().toString();
			setWriteFields(stmt, lectureId, lecture, user);
			stmt.setString("ownerid", user.userId);
			stmt.executeUpdate();
			connection.commit();
			return lectureId;
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbLecture.create()", e);
			throw e;
		}
	}

	private static void update(MysqlConnection connection, UserInfo user, String lectureId,
			LectureWrite lecture) throws SQLException {
		MysqlStatement stmt = connection.prepareStatement("UPDATE lecture SET "
				+ " displayname = :displayname, description = :description, imageversionid = :imageversionid,"
				+ " autoupdate = :autoupdate, isenabled = :isenabled, starttime = :starttime,"
				+ " endtime = :endtime, updatetime = UNIX_TIMESTAMP(),"
				+ " updaterid = :updaterid, runscript = :runscript, nics = :nics,"
				+ " netrules = :netrules, isexam = :isexam, hasinternetaccess = :hasinternetaccess,"
				+ " caneditdefault = :caneditdefault, canadmindefault = :canadmindefault"
				+ " WHERE lectureid = :lectureid");
		setWriteFields(stmt, lectureId, lecture, user);
		stmt.executeUpdate();
	}

	public static void update(UserInfo user, String lectureId, LectureWrite lecture) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			update(connection, user, lectureId, lecture);
			connection.commit();
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbLecture.update()", e);
			throw e;
		}
	}

	public static void setOwner(UserInfo user, String lectureId, String newOwnerId) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("UPDATE lecture"
					+ " SET ownerid = :ownerid, updaterid = :updaterid, updatetime = UNIX_TIMESTAMP()"
					+ " WHERE lectureid = :lectureid");
			stmt.setString("ownerid", newOwnerId);
			stmt.setString("updaterid", user.userId);
			stmt.setString("lectureid", lectureId);
			stmt.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbLecture.setOwner()", e);
			throw e;
		}
	}

	private static LectureSummary fillSummary(UserInfo user, ResultSet rs) throws SQLException {
		LectureSummary lecture = new LectureSummary();
		lecture.setLectureId(rs.getString("lectureid"));
		lecture.setLectureName(rs.getString("lecturename"));
		lecture.setImageVersionId(rs.getString("imageversionid"));
		lecture.setImageBaseId(rs.getString("imagebaseid"));
		lecture.setIsEnabled(rs.getBoolean("isenabled"));
		lecture.setStartTime(rs.getLong("starttime"));
		lecture.setEndTime(rs.getLong("endtime"));
		lecture.setLastUsed(rs.getLong("lastused"));
		lecture.setUseCount(rs.getInt("usecount"));
		lecture.setOwnerId(rs.getString("ownerid"));
		lecture.setUpdaterId(rs.getString("updaterid"));
		lecture.setIsExam(rs.getBoolean("isexam"));
		lecture.setHasInternetAccess(rs.getBoolean("hasinternetaccess"));
		lecture.setDefaultPermissions(DbLecturePermissions.fromResultSetDefault(rs));
		lecture.setUserPermissions(DbLecturePermissions.fromResultSetUser(rs));
		lecture.setIsImageVersionUsable(rs.getBoolean("imgvalid"));
		if (user != null) {
			User.setCombinedUserPermissions(lecture, user);
		}
		return lecture;
	}

	private static final String summaryBaseSql = "SELECT"
			+ " l.lectureid, l.displayname AS lecturename, l.imageversionid, i.imagebaseid,"
			+ " l.isenabled, l.starttime, l.endtime, l.lastused, l.usecount, l.ownerid, l.updaterid,"
			+ " l.isexam, l.hasinternetaccess, l.caneditdefault, l.canadmindefault,"
			+ " i.isvalid AS imgvalid, perm.canedit, perm.canadmin"
			+ "                      FROM lecture l                 "
			+ " LEFT JOIN imageversion i USING (imageversionid)"
			+ " LEFT JOIN lecturepermission perm ON (perm.lectureid = l.lectureid AND perm.userid = :userid)";

	public static LectureSummary getLectureSummary(UserInfo user, String lectureId) throws SQLException,
			TNotFoundException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement(summaryBaseSql
					+ " WHERE l.lectureid = :lectureid");
			stmt.setString("lectureid", lectureId);
			stmt.setString("userid", user == null ? "-" : user.userId);
			ResultSet rs = stmt.executeQuery();
			if (!rs.next())
				throw new TNotFoundException();
			return fillSummary(user, rs);
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbLecture.getLectureSummary()", e);
			throw e;
		}
	}

	public static List<LectureSummary> getAll(UserInfo user, int page) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement(summaryBaseSql
					+ (User.isStudent(user) ? " WHERE i.isrestricted = 0" : ""));
			stmt.setString("userid", user == null ? "-" : user.userId);
			ResultSet rs = stmt.executeQuery();
			List<LectureSummary> list = new ArrayList<>(100);
			while (rs.next()) {
				list.add(fillSummary(user, rs));
			}
			return list;
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbLecture.getAll()", e);
			throw e;
		}
	}

	protected static List<LectureSummary> getAllUsingImageBase(MysqlConnection connection,
			String imageBaseId, boolean autoUpdateOnly) throws SQLException {
		MysqlStatement stmt = connection.prepareStatement(summaryBaseSql
				+ " WHERE imagebaseid = :imagebaseid" + (autoUpdateOnly ? " AND autoupdate = 1" : ""));
		stmt.setString("imagebaseid", imageBaseId);
		stmt.setString("userid", "-");
		ResultSet rs = stmt.executeQuery();
		List<LectureSummary> list = new ArrayList<>();
		while (rs.next()) {
			list.add(fillSummary(null, rs));
		}
		return list;
	}

	protected static List<LectureSummary> getAllUsingImageVersion(MysqlConnection connection,
			String imageVersionId) throws SQLException {
		MysqlStatement stmt = connection.prepareStatement(summaryBaseSql
				+ " WHERE imageversionid = :imageversionid");
		stmt.setString("imageversionid", imageVersionId);
		stmt.setString("userid", "-");
		ResultSet rs = stmt.executeQuery();
		List<LectureSummary> list = new ArrayList<>();
		while (rs.next()) {
			list.add(fillSummary(null, rs));
		}
		return list;
	}

	public static LectureRead getLectureDetails(UserInfo user, String lectureId) throws SQLException,
			TNotFoundException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("SELECT"
					+ " l.lectureid, l.displayname AS lecturename, l.description, l.imageversionid, i.imagebaseid,"
					+ " l.autoupdate, l.isenabled, l.starttime, l.endtime, l.lastused, l.usecount, l.createtime,"
					+ " l.updatetime, l.ownerid, l.updaterid, l.runscript, l.nics, l.netrules, l.isexam,"
					+ " l.hasinternetaccess, l.caneditdefault, l.canadmindefault, p.canedit, p.canadmin"
					+ "                   FROM lecture l            "
					+ " LEFT JOIN imageversion i USING (imageversionid)"
					+ " LEFT JOIN lecturepermission p ON (l.lectureid = p.lectureid AND p.userid = :userid)"
					+ " WHERE l.lectureid = :lectureid LIMIT 1");
			stmt.setString("userid", user.userId);
			stmt.setString("lectureid", lectureId);
			ResultSet rs = stmt.executeQuery();
			if (!rs.next())
				throw new TNotFoundException();
			LectureRead lecture = new LectureRead();
			lecture.setLectureId(rs.getString("lectureid"));
			lecture.setLectureName(rs.getString("lecturename"));
			lecture.setDescription(rs.getString("description"));
			lecture.setImageVersionId(rs.getString("imageversionid"));
			lecture.setImageBaseId(rs.getString("imagebaseid"));
			lecture.setAutoUpdate(rs.getBoolean("autoupdate"));
			lecture.setIsEnabled(rs.getBoolean("isenabled"));
			lecture.setStartTime(rs.getLong("starttime"));
			lecture.setEndTime(rs.getLong("endtime"));
			lecture.setLastUsed(rs.getLong("lastused"));
			lecture.setUseCount(rs.getInt("usecount"));
			lecture.setCreateTime(rs.getLong("createtime"));
			lecture.setUpdateTime(rs.getLong("updatetime"));
			lecture.setOwnerId(rs.getString("ownerid"));
			lecture.setUpdaterId(rs.getString("updaterid"));
			lecture.setRunscript(rs.getString("runscript"));
			lecture.setNics(null); // TODO fill nics
			String netrules = rs.getString("netrules");
			if (netrules != null) {
				try {
					NetRule[] rules = Json.deserializeThrift(netrules, NetRule[].class);
					if (rules != null) {
						lecture.setNetworkExceptions(Arrays.asList(rules));
					}
				} catch (JsonParseException e) {
					LOGGER.warn("Could not deserialize netrules for lecture " + lectureId, e);
				}
			}
			lecture.setIsExam(rs.getBoolean("isexam"));
			lecture.setHasInternetAccess(rs.getBoolean("hasinternetaccess"));
			lecture.setAllowedUsers(getAllowedUsers(connection, lectureId));
			lecture.setDefaultPermissions(DbLecturePermissions.fromResultSetDefault(rs));
			lecture.setUserPermissions(DbLecturePermissions.fromResultSetUser(rs));
			User.setCombinedUserPermissions(lecture, user);
			return lecture;
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbLecture.getLectureDetails()", e);
			throw e;
		}
	}

	public static List<String> getAllowedUsers(MysqlConnection connection, String lectureId)
			throws SQLException {
		MysqlStatement stmt = connection.prepareStatement("SELECT" + " userlogin FROM lectureuser"
				+ " WHERE lectureid = :lectureid");
		stmt.setString("lectureid", lectureId);
		ResultSet rs = stmt.executeQuery();
		List<String> list = new ArrayList<>();
		while (rs.next()) {
			list.add(rs.getString("userlogin"));
		}
		return list;
	}

	public static boolean delete(String lectureId) throws TNotFoundException, SQLException {
		int affected;
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("DELETE FROM lecture WHERE lectureid = :lectureid");
			stmt.setString("lectureid", lectureId);
			affected = stmt.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbLecture.delete()", e);
			throw e;
		}
		return affected == 1;
	}

	/**
	 * Called when a new version for an image is uploaded. Update all lectures
	 * using the same base image which have the autoUpdate-flag set.
	 * 
	 * @param connection mysql connection to use
	 * @param imageBaseId base image that got a new version
	 * @param newVersion the latest (valid) version
	 * @throws SQLException
	 */
	protected static void autoUpdateUsedImage(MysqlConnection connection, String imageBaseId,
			LocalImageVersion newVersion) throws SQLException {
		if (newVersion == null)
			return;
		List<LectureSummary> lectures = getAllUsingImageBase(connection, imageBaseId, true);
		if (lectures.isEmpty())
			return;
		// Remove lectures that are already on the given latest version from the list...
		for (Iterator<LectureSummary> it = lectures.iterator(); it.hasNext();) {
			LectureSummary lecture = it.next();
			if (lecture.imageVersionId.equals(newVersion.imageVersionId))
				it.remove();
		}
		// Update lectures in DB
		MysqlStatement stmt = connection.prepareStatement("UPDATE lecture l, imageversion v SET"
				+ " l.imageversionid = :imageversionid"
				+ " WHERE v.imageversionid = l.imageversionid AND v.imagebaseid = :imagebaseid"
				+ " AND l.autoupdate = 1");
		stmt.setString("imageversionid", newVersion.imageVersionId);
		stmt.setString("imagebaseid", imageBaseId);
		stmt.executeUpdate();
		// Send informative mail to lecture admins
		MailGenerator.lectureAutoUpdate(lectures, newVersion);
	}

	/**
	 * Called when an image version is deleted or marked for deletion, so that
	 * linking lectures switch over to other available versions.
	 */
	protected static void forcefullySwitchUsedImage(MysqlConnection connection, LocalImageVersion oldVersion,
			LocalImageVersion newVersion) throws TNotFoundException, SQLException {
		if (oldVersion == newVersion
				|| (newVersion != null && newVersion.imageVersionId.equals(oldVersion.imageVersionId)))
			return;
		// First, get list of lectures using the image version to switch away from
		List<LectureSummary> lectures = getAllUsingImageVersion(connection, oldVersion.imageVersionId);
		if (lectures.isEmpty())
			return;
		MysqlStatement stmt;
		if (newVersion == null) {
			stmt = connection.prepareStatement("UPDATE lecture SET isenabled = 0 WHERE imageversionid = :oldversionid");
			stmt.setString("oldversionid", oldVersion.imageVersionId);
			MailGenerator.lectureDeactivated(lectures);
		} else {
			// Update and send info mail
			stmt = connection.prepareStatement("UPDATE lecture SET imageversionid = :newversionid"
					+ " WHERE imageversionid = :oldversionid");
			stmt.setString("oldversionid", oldVersion.imageVersionId);
			stmt.setString("newversionid", newVersion.imageVersionId);
			MailGenerator.lectureForcedUpdate(lectures, newVersion);
		}
		stmt.executeUpdate();
	}

	protected static void deletePermanently(MysqlConnection connection, LocalImageVersion image)
			throws SQLException {
		MysqlStatement stmt = connection.prepareStatement("DELETE FROM lecture WHERE imageversionid = :imageversionid");
		stmt.setString("imageversionid", image.imageVersionId);
		stmt.executeUpdate();
	}

	public static List<LectureSummary> getExpiringLectures(int maxRemainingDays) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement(summaryBaseSql + " WHERE endtime < :deadline");
			stmt.setString("userid", "-");
			stmt.setLong("deadline", Util.unixTime() + (maxRemainingDays * 86400));
			ResultSet rs = stmt.executeQuery();
			List<LectureSummary> list = new ArrayList<>();
			while (rs.next()) {
				list.add(fillSummary(null, rs));
			}
			return list;
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbLecture.getExpiringLectures()", e);
			throw e;
		}
	}

	public static VmChooserListXml getUsableListXml(boolean exams) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("SELECT"
					+ " l.lectureid, l.displayname AS lecturename, l.description,"
					+ " l.endtime, l.usecount, o.displayname AS osname, v.virtname, b.istemplate"
					+ "                      FROM lecture l                 "
					+ " INNER JOIN imageversion i USING (imageversionid)"
					+ " INNER JOIN imagebase b USING (imagebaseid)"
					+ " INNER JOIN operatingsystem o USING (osid)"
					+ " INNER JOIN virtualizer v USING (virtid)"
					+ " WHERE l.isenabled = 1 AND l.isprivate = 0 AND l.isexam = :isexam"
					+ " AND l.starttime < UNIX_TIMESTAMP() AND l.endtime > UNIX_TIMESTAMP() AND i.isvalid = 1");
			stmt.setBoolean("isexam", exams);
			ResultSet rs = stmt.executeQuery();
			VmChooserListXml list = new VmChooserListXml(true);
			while (rs.next()) {
				String prio = rs.getBoolean("istemplate") ? "10" : "100";
				list.add(new VmChooserEntryXml(prio, "-", rs.getString("lecturename"),
						rs.getString("description"), rs.getString("lectureid"), rs.getString("virtname"),
						rs.getString("osname"), ""));
			}
			return list;
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbLecture.getUsableList()", e);
			throw e;
		}
	}

	public static VmMetaData getClientLaunchData(String lectureId) throws SQLException, TNotFoundException {
		try (MysqlConnection connection = Database.getConnection()) {
			// Get required data about lecture and used image
			MysqlStatement stmt = connection.prepareStatement("SELECT"
					+ " l.displayname AS lecturename, l.starttime, l.endtime, l.isenabled,"
					+ " o.virtoskeyword, i.filepath, i.virtualizerconfig"
					+ "                      FROM lecture l                 "
					+ " INNER JOIN imageversion i USING (imageversionid)"
					+ " INNER JOIN imagebase b USING (imagebaseid)"
					+ " INNER JOIN os_x_virt o USING (osid, virtid)" + " WHERE l.lectureid = :lectureid");
			stmt.setString("lectureid", lectureId);
			ResultSet rs = stmt.executeQuery();
			long now = Util.unixTime();
			if (!rs.next() || !rs.getBoolean("isenabled") || rs.getLong("starttime") > now
					|| rs.getLong("endtime") < now) {
				throw new TNotFoundException();
			}
			byte[] config = rs.getBytes("virtualizerconfig");
			if (config == null) {
				return null;
			}
			VmwareMetaData meta = new VmwareMetaData(OperatingSystemList.get(), config, config.length);
			meta.addDisplayName(rs.getString("lecturename"));
			if (meta.getOs() == null) {
				meta.setOs(rs.getString("virtoskeyword"));
			}
			meta.addHddTemplate("%VM_DISK_PATH%");
			// Everything worked so far, update statistics counters
			MysqlStatement upStmt = connection.prepareStatement("UPDATE"
					+ " lecture SET lastused = UNIX_TIMESTAMP(), usecount = usecount + 1"
					+ " WHERE lectureid = :lectureid");
			upStmt.setString("lectureid", lectureId);
			upStmt.executeUpdate();
			connection.commit();
			return meta;
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbLecture.getClientLaunchData()", e);
			throw e;
		}
	}

	public static void deleteOld(int minAgeDays) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("DELETE FROM lecture WHERE endtime < :cutoff");
			stmt.setLong("cutoff", Util.unixTime() - TimeUnit.DAYS.toSeconds(minAgeDays));
			stmt.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbLecture.deleteOld()", e);
			throw e;
		}
	}

}