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

1 files changed, 38 insertions, 7 deletions

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