fix(caldav): automatically delete outdated scheduling objects

Signed-off-by: Anna Larch <anna@nextcloud.com>

4 files changed, 112 insertions, 0 deletions

diff --git a/apps/dav/lib/BackgroundJob/DeleteOutdatedSchedulingObjects.php b/apps/dav/lib/BackgroundJob/DeleteOutdatedSchedulingObjects.php
new file mode 100644
index 00000000000..fa53a8be4f0
--- /dev/null
+++ b/apps/dav/lib/BackgroundJob/DeleteOutdatedSchedulingObjects.php
@@ -0,0 +1,35 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+namespace OCA\DAV\BackgroundJob;
+
+use OCA\DAV\CalDAV\CalDavBackend;
+use OCP\AppFramework\Utility\ITimeFactory;
+use OCP\BackgroundJob\TimedJob;
+use Psr\Log\LoggerInterface;
+
+class DeleteOutdatedSchedulingObjects extends TimedJob {
+	public function __construct(
+		private CalDavBackend $calDavBackend,
+		private LoggerInterface $logger,
+		ITimeFactory $timeFactory,
+	) {
+		parent::__construct($timeFactory);
+		$this->setInterval(23 * 60 * 60);
+		$this->setTimeSensitivity(self::TIME_INSENSITIVE);
+	}
+
+	/**
+	 * @param array $argument
+	 */
+	protected function run($argument): void {
+		$time = $this->time->getTime() - (60 * 60);
+		$this->calDavBackend->deleteOutdatedSchedulingObjects($time, 50000);
+		$this->logger->info("Removed outdated scheduling objects");
+	}
+}
diff --git a/apps/dav/lib/CalDAV/CalDavBackend.php b/apps/dav/lib/CalDAV/CalDavBackend.php
index d17c8a350fc..cc6c4344c3c 100644
--- a/apps/dav/lib/CalDAV/CalDavBackend.php
+++ b/apps/dav/lib/CalDAV/CalDavBackend.php
@@ -2776,6 +2776,44 @@ class CalDavBackend extends AbstractBackend implements SyncSupport, Subscription
 	}
 
 	/**
+	 * Deletes all scheduling objects last modified before $modifiedBefore from the inbox collection.
+	 *
+	 * @param int $modifiedBefore
+	 * @param int $limit
+	 * @return void
+	 */
+	public function deleteOutdatedSchedulingObjects(int $modifiedBefore, int $limit): void {
+		$query = $this->db->getQueryBuilder();
+		$query->select('id')
+			->from('schedulingobjects')
+			->where($query->expr()->lt('lastmodified', $query->createNamedParameter($modifiedBefore)))
+			->setMaxResults($limit);
+		$result = $query->executeQuery();
+		$count = $result->rowCount();
+		if($count === 0) {
+			return;
+		}
+		$ids = array_map(static function (array $id) {
+			return (int)$id[0];
+		}, $result->fetchAll(\PDO::FETCH_NUM));
+		$result->closeCursor();
+
+		$numDeleted = 0;
+		$deleteQuery = $this->db->getQueryBuilder();
+		$deleteQuery->delete('schedulingobjects')
+			->where($deleteQuery->expr()->in('id', $deleteQuery->createParameter('ids'), IQueryBuilder::PARAM_INT_ARRAY));
+		foreach(array_chunk($ids, 1000) as $chunk) {
+			$deleteQuery->setParameter('ids', $chunk, IQueryBuilder::PARAM_INT_ARRAY);
+			$numDeleted += $deleteQuery->executeStatement();
+		}
+
+		if($numDeleted === $limit) {
+			$this->logger->info("Deleted $limit scheduling objects, continuing with next batch");
+			$this->deleteOutdatedSchedulingObjects($modifiedBefore, $limit);
+		}
+	}
+
+	/**
 	 * Creates a new scheduling object. This should land in a users' inbox.
 	 *
 	 * @param string $principalUri
diff --git a/apps/dav/lib/Migration/DeleteSchedulingObjects.php b/apps/dav/lib/Migration/DeleteSchedulingObjects.php
new file mode 100644
index 00000000000..3919236788b
--- /dev/null
+++ b/apps/dav/lib/Migration/DeleteSchedulingObjects.php
@@ -0,0 +1,38 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+namespace OCA\DAV\Migration;
+
+use OCA\DAV\BackgroundJob\DeleteOutdatedSchedulingObjects;
+use OCA\DAV\CalDAV\CalDavBackend;
+use OCP\AppFramework\Utility\ITimeFactory;
+use OCP\BackgroundJob\IJobList;
+use OCP\Migration\IOutput;
+use OCP\Migration\IRepairStep;
+
+class DeleteSchedulingObjects implements IRepairStep {
+	public function __construct(private IJobList $jobList,
+		private ITimeFactory $time,
+		private CalDavBackend $calDavBackend
+	) {
+	}
+
+	public function getName(): string {
+		return 'Handle outdated scheduling events';
+	}
+
+	public function run(IOutput $output): void {
+		$output->info('Cleaning up old scheduling events');
+		$time = $this->time->getTime() - (60 * 60);
+		$this->calDavBackend->deleteOutdatedSchedulingObjects($time, 50000);
+		if (!$this->jobList->has(DeleteOutdatedSchedulingObjects::class, null)) {
+			$output->info('Adding background job to delete old scheduling objects');
+			$this->jobList->add(DeleteOutdatedSchedulingObjects::class, null);
+		}
+	}
+}
diff --git a/apps/dav/lib/Migration/Version1004Date20170825134824.php b/apps/dav/lib/Migration/Version1004Date20170825134824.php
index ef2aaf6af36..0aaca7f60de 100644
--- a/apps/dav/lib/Migration/Version1004Date20170825134824.php
+++ b/apps/dav/lib/Migration/Version1004Date20170825134824.php
@@ -363,6 +363,7 @@ class Version1004Date20170825134824 extends SimpleMigrationStep {
 			]);
 			$table->setPrimaryKey(['id']);
 			$table->addIndex(['principaluri'], 'schedulobj_principuri_index');
+			$table->addIndex(['lastmodified'], 'schedulobj_lastmodified_idx');
 		}
 
 		if (!$schema->hasTable('cards_properties')) {