[server] Implement scanning for soon-expiring lectures and images

7 files changed, 234 insertions, 24 deletions

diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/App.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/App.java
index 54a25de3..62000490 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/App.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/App.java
@@ -16,6 +16,7 @@ import org.openslx.bwlp.sat.database.Database;
 import org.openslx.bwlp.sat.database.mappers.DbImage;
 import org.openslx.bwlp.sat.fileserv.FileServer;
 import org.openslx.bwlp.sat.maintenance.DeleteOldImages;
+import org.openslx.bwlp.sat.maintenance.SendExpireWarning;
 import org.openslx.bwlp.sat.thrift.BinaryListener;
 import org.openslx.bwlp.sat.thrift.cache.OperatingSystemList;
 import org.openslx.bwlp.sat.thrift.cache.OrganizationList;
@@ -93,10 +94,11 @@ public class App {
 			LOGGER.error("Could not start internal file server.");
 			return;
 		}
-		
+
 		// Set up maintenance tasks
 		DeleteOldImages.init();
-		
+		SendExpireWarning.init();
+
 		// Start Thrift Server
 		Thread t;
 		// Plain
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbImage.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbImage.java
index 9aee00b8..0952223b 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbImage.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbImage.java
@@ -151,7 +151,7 @@ public class DbImage {
 			MysqlStatement stmt = connection.prepareStatement("SELECT"
 					+ " imageversionid, imagebaseid, filepath, filesize, createtime, expiretime, isvalid"
 					+ " FROM imageversion WHERE expiretime < :deadline");
-			stmt.setLong("deadline", (System.currentTimeMillis() / 1000) + (maxRemainingDays * 86400));
+			stmt.setLong("deadline", Util.unixTime() + (maxRemainingDays * 86400));
 			ResultSet rs = stmt.executeQuery();
 			List<LocalImageVersion> list = new ArrayList<>();
 			while (rs.next()) {
@@ -518,14 +518,14 @@ public class DbImage {
 		}
 	}
 
-	public static void markValid(boolean valid, boolean async, LocalImageVersion... imageVersion)
+	public static void markValid(boolean valid, boolean async, LocalImageVersion... imageVersions)
 			throws SQLException {
-		if (imageVersion == null || imageVersion.length == 0)
+		if (imageVersions == null || imageVersions.length == 0)
 			return;
 		try (MysqlConnection connection = Database.getConnection()) {
-			markValid(connection, valid, imageVersion);
+			markValid(connection, valid, imageVersions);
 			if (!async) {
-				updateLatestVersion(connection, imageVersion);
+				updateLatestVersion(connection, imageVersions);
 			}
 			connection.commit();
 		} catch (SQLException e) {
@@ -533,12 +533,13 @@ public class DbImage {
 			throw e;
 		}
 		if (async) {
-			updateLatestVersionAsync(imageVersion);
+			updateLatestVersionAsync(imageVersions);
 		}
 	}
 
 	public static void deletePermanently(LocalImageVersion image) throws SQLException {
 		try (MysqlConnection connection = Database.getConnection()) {
+			DbLecture.deletePermanently(connection, image);
 			MysqlStatement stmt = connection.prepareStatement("DELETE FROM imageversion"
 					+ " WHERE imageversionid = :imageversionid");
 			stmt.setString("imageversionid", image.imageVersionId);
@@ -671,7 +672,7 @@ public class DbImage {
 				+ " WHERE imagebaseid = :imagebaseid");
 		latestStmt.setString("newversionid", latest == null ? null : latest.imageVersionId);
 		latestStmt.setString("imagebaseid", imageBaseId);
-		// If nothing changed, or the latest version was set to NULL, bail out
+		// If nothing changed (because the deleted version was not the latest), bail out
 		if (latestStmt.executeUpdate() == 0)
 			return false;
 		// It there is no valid version, bail out as a shortcut - queries below wouldn't do anything
@@ -697,4 +698,34 @@ public class DbImage {
 		return true;
 	}
 
+	public static int deleteOrphanedBases() throws SQLException {
+		try (MysqlConnection connection = Database.getConnection()) {
+			// Get all image base entries which have no image version
+			MysqlStatement sel = connection.prepareStatement("SELECT i.imagebaseid FROM imagebase i"
+					+ " LEFT JOIN imageversion v USING (imagebaseid)"
+					+ " WHERE i.updatetime < :cutoff AND v.imageversionid IS NULL");
+			sel.setLong("cutoff", Util.unixTime() - 86400 * 14);
+			ResultSet rs = sel.executeQuery();
+			// Now delete them all
+			MysqlStatement stmt = connection.prepareStatement("DELETE FROM imagebase"
+					+ " WHERE imagebaseid = :imagebaseid");
+			int ret = 0;
+			while (rs.next()) {
+				String baseId = null;
+				try {
+					baseId = rs.getString("imagebaseid");
+					stmt.setString("imagebaseid", baseId);
+					ret += stmt.executeUpdate();
+				} catch (SQLException e) {
+					LOGGER.warn("Could not delete base image " + baseId, e);
+				}
+			}
+			connection.commit();
+			return ret;
+		} catch (SQLException e) {
+			LOGGER.error("Query failed in DbImage.deleteOrphanedBases()", e);
+			throw e;
+		}
+	}
+
 }
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java
index f6431b46..c9f1c6df 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java
@@ -16,6 +16,7 @@ import org.openslx.bwlp.sat.database.models.LocalImageVersion;
 import org.openslx.bwlp.sat.maintenance.Mailer;
 import org.openslx.bwlp.sat.permissions.User;
 import org.openslx.bwlp.sat.util.Json;
+import org.openslx.bwlp.sat.util.Util;
 import org.openslx.bwlp.thrift.iface.LectureRead;
 import org.openslx.bwlp.thrift.iface.LectureSummary;
 import org.openslx.bwlp.thrift.iface.LectureWrite;
@@ -198,8 +199,8 @@ public class DbLecture {
 		}
 	}
 
-	protected static List<LectureSummary> getAllUsingImageBase(MysqlConnection connection, String imageBaseId, boolean autoUpdateOnly)
-			throws SQLException {
+	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);
@@ -376,4 +377,28 @@ public class DbLecture {
 		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;
+		}
+	}
+
 }
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/maintenance/DeleteOldImages.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/maintenance/DeleteOldImages.java
index c3168253..929fbb2a 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/maintenance/DeleteOldImages.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/maintenance/DeleteOldImages.java
@@ -16,17 +16,17 @@ import org.openslx.util.QuickTimer.Task;
  * Delete old image versions (images that reached their expire time).
  */
 public class DeleteOldImages implements Runnable {
-	
+
 	private static final Logger LOGGER = Logger.getLogger(DeleteOldImages.class);
-	
+
 	private static final DeleteOldImages instance = new DeleteOldImages();
-	
+
 	private static long blockedUntil = 0;
 
 	/**
 	 * Initialize the delete task. This schedules a timer that runs
 	 * every 5 minutes. If the hour of day reaches 3, it will fire
-	 * the task, and block it from running for the next 12 hours. 
+	 * the task, and block it from running for the next 12 hours.
 	 */
 	public synchronized static void init() {
 		if (blockedUntil != 0)
@@ -40,27 +40,39 @@ public class DeleteOldImages implements Runnable {
 				DateTime now = DateTime.now();
 				if (now.getHourOfDay() != 3 || now.getMinuteOfHour() > 15)
 					return;
-				if (Maintenance.trySubmit(instance)) {
-					blockedUntil = System.currentTimeMillis() + TimeUnit.HOURS.toMillis(12);
-				}
+				start();
 			}
 		}, TimeUnit.MINUTES.toMillis(5), TimeUnit.MINUTES.toMillis(5));
 	}
 	
+	public synchronized static void start() {
+		if (blockedUntil > System.currentTimeMillis())
+			return;
+		if (Maintenance.trySubmit(instance)) {
+			blockedUntil = System.currentTimeMillis() + TimeUnit.HOURS.toMillis(12);
+		}
+	}
+
 	private DeleteOldImages() {
 	}
 
 	@Override
 	public void run() {
+		if (!FileSystem.isStorageMounted()) {
+			LOGGER.warn("Will not execute deletion of old images; store seems to be unmounted!");
+			return;
+		}
 		LOGGER.info("Looking for old image versions to delete");
 		List<LocalImageVersion> versions;
+		// First get a list of all image versions which reached their expire date,
+		// no matter if valid or invalid
 		try {
 			versions = DbImage.getExpiringLocalImageVersions(0);
 		} catch (SQLException e) {
 			LOGGER.error("Will not be able to clean up old image versions");
 			return;
 		}
-		// Mark all as invalid. This will also trigger emails.
+		// Mark all as invalid. This will also trigger mails if they have been valid before
 		try {
 			DbImage.markValid(false, false, versions.toArray(new LocalImageVersion[versions.size()]));
 		} catch (SQLException e) {
@@ -81,7 +93,15 @@ public class DeleteOldImages implements Runnable {
 				}
 			}
 		}
-		LOGGER.info("Deletion done. Soft: " + versions.size() + ", hard: " + hardDeleteCount);
+		// Delete base images which no image versions (including invalid ones)
+		int baseDeleteCount = 0;
+		try {
+			baseDeleteCount = DbImage.deleteOrphanedBases();
+		} catch (SQLException e) {
+			// Logging done in method
+		}
+		LOGGER.info("Deletion done. Soft: " + (versions.size() - hardDeleteCount) + ", hard: "
+				+ hardDeleteCount + ", base: " + baseDeleteCount);
 	}
 
 }
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/maintenance/Mailer.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/maintenance/Mailer.java
index d2241e37..23482514 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/maintenance/Mailer.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/maintenance/Mailer.java
@@ -148,8 +148,8 @@ public class Mailer {
 			} else {
 				message = "Old version (created " + Formatter.date(oldVersion.createTime) + ") deleted;";
 			}
-			message += " new version now in use was uploaded on "
-						+ Formatter.date(newVersion.createTime) + " by " + uploaderName;
+			message += " new version now in use was uploaded on " + Formatter.date(newVersion.createTime)
+					+ " by " + uploaderName;
 		}
 		LOGGER.debug("Mail: " + message);
 		List<UserInfo> relevantUsers = getUserToMail(image);
@@ -158,4 +158,27 @@ public class Mailer {
 		}
 	}
 
+	public static void sendDeletionReminder(LocalImageVersion version, int days) {
+		ImageDetailsRead image;
+		try {
+			image = DbImage.getImageDetails(null, version.imageBaseId);
+		} catch (TNotFoundException | SQLException e) {
+			LOGGER.warn("Could not get image details for image version " + version.imageVersionId);
+			return;
+		}
+		List<UserInfo> relevantUsers = getUserToMail(image);
+		for (UserInfo user : relevantUsers) {
+			LOGGER.debug("[img:" + image.imageName + "] Sending warning mail to "
+					+ Formatter.userFullName(user) + " (" + days + " days)");
+		}
+	}
+
+	public static void sendDeletionRemainder(LectureSummary lecture, int days) {
+		List<UserInfo> relevantUsers = getUserToMail(lecture);
+		for (UserInfo user : relevantUsers) {
+			LOGGER.debug("[lecture:" + lecture.lectureName + "] Sending warning mail to "
+					+ Formatter.userFullName(user) + " (" + days + " days)");
+		}
+	}
+
 }
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/maintenance/SendExpireWarning.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/maintenance/SendExpireWarning.java
new file mode 100644
index 00000000..96436471
--- /dev/null
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/maintenance/SendExpireWarning.java
@@ -0,0 +1,107 @@
+package org.openslx.bwlp.sat.maintenance;
+
+import java.sql.SQLException;
+import java.util.List;
+import java.util.concurrent.TimeUnit;
+
+import org.apache.log4j.Logger;
+import org.joda.time.DateTime;
+import org.openslx.bwlp.sat.database.mappers.DbImage;
+import org.openslx.bwlp.sat.database.mappers.DbLecture;
+import org.openslx.bwlp.sat.database.models.LocalImageVersion;
+import org.openslx.bwlp.sat.util.FileSystem;
+import org.openslx.bwlp.sat.util.Util;
+import org.openslx.bwlp.thrift.iface.LectureSummary;
+import org.openslx.util.QuickTimer;
+import org.openslx.util.QuickTimer.Task;
+
+public class SendExpireWarning implements Runnable {
+
+	private static final Logger LOGGER = Logger.getLogger(SendExpireWarning.class);
+
+	private static final SendExpireWarning instance = new SendExpireWarning();
+
+	private static long blockedUntil = 0;
+
+	/**
+	 * Initialize the task. This schedules a timer that runs
+	 * every 5 minutes. If the hour of day reaches 3, it will fire
+	 * the task, and block it from running for the next 12 hours.
+	 */
+	public synchronized static void init() {
+		if (blockedUntil != 0)
+			return;
+		blockedUntil = 1;
+		QuickTimer.scheduleAtFixedRate(new Task() {
+			@Override
+			public void fire() {
+				if (blockedUntil > System.currentTimeMillis())
+					return;
+				DateTime now = DateTime.now();
+				if (now.getHourOfDay() != 3 || now.getMinuteOfHour() > 15)
+					return;
+				start();
+			}
+		}, TimeUnit.MINUTES.toMillis(4), TimeUnit.MINUTES.toMillis(5));
+	}
+
+	public synchronized static void start() {
+		if (blockedUntil > System.currentTimeMillis())
+			return;
+		if (Maintenance.trySubmit(instance)) {
+			blockedUntil = System.currentTimeMillis() + TimeUnit.HOURS.toMillis(12);
+		}
+	}
+
+	@Override
+	public void run() {
+		checkImages();
+		checkLectures();
+	}
+
+	private void checkLectures() {
+		List<LectureSummary> lectures;
+		try {
+			lectures = DbLecture.getExpiringLectures(15);
+		} catch (SQLException e) {
+			LOGGER.warn("Could not get list of expiring lectures; skipping warning mails");
+			return;
+		}
+		LOGGER.info("Scanning expiring lectures to send mails to users");
+		final long now = Util.unixTime();
+		for (LectureSummary lecture : lectures) {
+			final int days = (int) ((lecture.endTime - now) / 86400);
+			LOGGER.debug(lecture.lectureName + " expires in " + days);
+			if ((lecture.isEnabled && (days == 14 || days == 1)) || (days == 7)) {
+				Mailer.sendDeletionRemainder(lecture, days);
+			}
+		}
+	}
+
+	private void checkImages() {
+		if (!FileSystem.isStorageMounted()) {
+			LOGGER.warn("Skipping sending warning mails about expiring images - storage seems unmounted");
+			return;
+		}
+		// Get all images that expire in 15 days or less
+		List<LocalImageVersion> versions;
+		try {
+			versions = DbImage.getExpiringLocalImageVersions(15);
+		} catch (SQLException e) {
+			LOGGER.warn("Could not determine expiring versions; skipping warning mails");
+			return;
+		}
+		LOGGER.info("Scanning for expiring images to send mails to users");
+		// Send reminder on certain days
+		final long now = Util.unixTime();
+		for (LocalImageVersion version : versions) {
+			final int days = (int) ((version.expireTime - now) / 86400);
+			LOGGER.debug(version.imageVersionId + " expires in " + days);
+			if ((version.isValid && (days == 14 || days == 7 || days == 1))
+					|| (!version.isValid && days == 3)) {
+				Mailer.sendDeletionReminder(version, days);
+			}
+		}
+	}
+
+}
diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/util/FileSystem.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/util/FileSystem.java
index a8a7151b..42f1056c 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/util/FileSystem.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/util/FileSystem.java
@@ -40,9 +40,11 @@ public class FileSystem {
 			@Override
 			public void fire() {
 				for (File file : files) {
+					if (file == null)
+						continue;
 					if (!file.exists()) {
-						LOGGER.info("deleteAsync called for nonexistent file " + file.getAbsolutePath());
-						return;
+						LOGGER.debug("deleteAsync called for nonexistent file " + file.getAbsolutePath());
+						continue;
 					}
 					if (!file.delete()) {
 						LOGGER.warn("Could not delete file " + file.getAbsolutePath());