fix: implement sharding compatible cleanup for various bits

Signed-off-by: Robin Appelman <robin@icewind.nl>

6 files changed, 75 insertions, 13 deletions

diff --git a/lib/private/DB/QueryBuilder/ExtendedQueryBuilder.php b/lib/private/DB/QueryBuilder/ExtendedQueryBuilder.php
index 8ed88198c19..c40cadfbdb5 100644
--- a/lib/private/DB/QueryBuilder/ExtendedQueryBuilder.php
+++ b/lib/private/DB/QueryBuilder/ExtendedQueryBuilder.php
@@ -289,8 +289,8 @@ abstract class ExtendedQueryBuilder implements IQueryBuilder {
 		return $this->builder->executeStatement($connection);
 	}
 
-	public function hintShardKey(string $column, mixed $value) {
-		$this->builder->hintShardKey($column, $value);
+	public function hintShardKey(string $column, mixed $value, bool $overwrite = false) {
+		$this->builder->hintShardKey($column, $value, $overwrite);
 		return $this;
 	}
 
diff --git a/lib/private/DB/QueryBuilder/QueryBuilder.php b/lib/private/DB/QueryBuilder/QueryBuilder.php
index 76fa9f42bab..5c7e273c9ec 100644
--- a/lib/private/DB/QueryBuilder/QueryBuilder.php
+++ b/lib/private/DB/QueryBuilder/QueryBuilder.php
@@ -1370,7 +1370,7 @@ class QueryBuilder implements IQueryBuilder {
 		return $this->connection->escapeLikeParameter($parameter);
 	}
 
-	public function hintShardKey(string $column, mixed $value) {
+	public function hintShardKey(string $column, mixed $value, bool $overwrite = false) {
 		return $this;
 	}
 
diff --git a/lib/private/DB/QueryBuilder/Sharded/ShardedQueryBuilder.php b/lib/private/DB/QueryBuilder/Sharded/ShardedQueryBuilder.php
index 6496453a1a6..650e414096e 100644
--- a/lib/private/DB/QueryBuilder/Sharded/ShardedQueryBuilder.php
+++ b/lib/private/DB/QueryBuilder/Sharded/ShardedQueryBuilder.php
@@ -296,7 +296,11 @@ class ShardedQueryBuilder extends ExtendedQueryBuilder {
 		];
 	}
 
-	public function hintShardKey(string $column, mixed $value) {
+	public function hintShardKey(string $column, mixed $value, bool $overwrite = false) {
+		if ($overwrite) {
+			$this->primaryKeys = [];
+			$this->shardKeys = [];
+		}
 		if ($this->shardDefinition?->isKey($column)) {
 			$this->primaryKeys[] = $value;
 		}
diff --git a/lib/private/Preview/BackgroundCleanupJob.php b/lib/private/Preview/BackgroundCleanupJob.php
index deadcd007b1..acf7bf22f52 100644
--- a/lib/private/Preview/BackgroundCleanupJob.php
+++ b/lib/private/Preview/BackgroundCleanupJob.php
@@ -16,6 +16,7 @@ use OCP\Files\IMimeTypeLoader;
 use OCP\Files\NotFoundException;
 use OCP\Files\NotPermittedException;
 use OCP\IDBConnection;
+use function Symfony\Component\Translation\t;
 
 class BackgroundCleanupJob extends TimedJob {
 	/** @var IDBConnection */
@@ -64,6 +65,11 @@ class BackgroundCleanupJob extends TimedJob {
 	}
 
 	private function getOldPreviewLocations(): \Iterator {
+		if ($this->connection->getShardDefinition('filecache')) {
+			// sharding is new enough that we don't need to support this
+			return;
+		}
+
 		$qb = $this->connection->getQueryBuilder();
 		$qb->select('a.name')
 			->from('filecache', 'a')
@@ -106,6 +112,15 @@ class BackgroundCleanupJob extends TimedJob {
 			return [];
 		}
 
+		if ($this->connection->getShardDefinition('filecache')) {
+			$chunks = $this->getAllPreviewIds($data['path'], 1000);
+			foreach ($chunks as $chunk) {
+				yield from $this->findMissingSources($chunk);
+			}
+
+			return;
+		}
+
 		/*
 		 * This lovely like is the result of the way the new previews are stored
 		 * We take the md5 of the name (fileid) and split the first 7 chars. That way
@@ -155,4 +170,46 @@ class BackgroundCleanupJob extends TimedJob {
 
 		$cursor->closeCursor();
 	}
+
+	private function getAllPreviewIds(string $previewRoot, int $chunkSize): \Iterator {
+		// See `getNewPreviewLocations` for some more info about the logic here
+		$like = $this->connection->escapeLikeParameter($previewRoot). '/_/_/_/_/_/_/_/%';
+
+		$qb = $this->connection->getQueryBuilder();
+		$qb->select('name', 'fileid')
+			->from('filecache')
+			->where(
+				$qb->expr()->andX(
+					$qb->expr()->eq('storage', $qb->createNamedParameter($this->previewFolder->getStorageId())),
+					$qb->expr()->like('path', $qb->createNamedParameter($like)),
+					$qb->expr()->eq('mimetype', $qb->createNamedParameter($this->mimeTypeLoader->getId('httpd/unix-directory'))),
+					$qb->expr()->gt('fileid', $qb->createParameter('min_id')),
+				)
+			)
+			->orderBy('fileid', 'ASC')
+			->setMaxResults($chunkSize);
+
+		$minId = 0;
+		while (true) {
+			$qb->setParameter('min_id', $minId);
+			$rows = $qb->executeQuery()->fetchAll();
+			if (count($rows) > 0) {
+				$minId = $rows[count($rows) - 1]['fileid'];
+				yield array_map(function ($row) {
+					return (int)$row['name'];
+				}, $rows);
+			} else {
+				break;
+			}
+		}
+	}
+
+	private function findMissingSources(array $ids): array {
+		$qb = $this->connection->getQueryBuilder();
+		$qb->select('fileid')
+			->from('filecache')
+			->where($qb->expr()->in('fileid', $qb->createNamedParameter($ids, IQueryBuilder::PARAM_INT_ARRAY)));
+		$found = $qb->executeQuery()->fetchAll(\PDO::FETCH_COLUMN);
+		return array_diff($ids, $found);
+	}
 }
diff --git a/lib/private/Repair/CleanTags.php b/lib/private/Repair/CleanTags.php
index 41ee111a20d..258808cb28a 100644
--- a/lib/private/Repair/CleanTags.php
+++ b/lib/private/Repair/CleanTags.php
@@ -107,7 +107,7 @@ class CleanTags implements IRepairStep {
 			$output,
 			'%d tags for delete files have been removed.',
 			'vcategory_to_object', 'objid',
-			'filecache', 'fileid', 'path_hash'
+			'filecache', 'fileid', 'fileid'
 		);
 	}
 
@@ -169,16 +169,17 @@ class CleanTags implements IRepairStep {
 			$orphanItems[] = (int)$row[$deleteId];
 		}
 
+		$deleteQuery = $this->connection->getQueryBuilder();
+		$deleteQuery->delete($deleteTable)
+			->where(
+				$deleteQuery->expr()->eq('type', $deleteQuery->expr()->literal('files'))
+			)
+			->andWhere($deleteQuery->expr()->in($deleteId, $deleteQuery->createParameter('ids')));
 		if (!empty($orphanItems)) {
 			$orphanItemsBatch = array_chunk($orphanItems, 200);
 			foreach ($orphanItemsBatch as $items) {
-				$qb->delete($deleteTable)
-					->where(
-						$qb->expr()->eq('type', $qb->expr()->literal('files'))
-					)
-					->andWhere($qb->expr()->in($deleteId, $qb->createParameter('ids')));
-				$qb->setParameter('ids', $items, IQueryBuilder::PARAM_INT_ARRAY);
-				$qb->execute();
+				$deleteQuery->setParameter('ids', $items, IQueryBuilder::PARAM_INT_ARRAY);
+				$deleteQuery->executeStatement();
 			}
 		}
 
diff --git a/lib/public/DB/QueryBuilder/IQueryBuilder.php b/lib/public/DB/QueryBuilder/IQueryBuilder.php
index df93a0b1ed5..c21793a2421 100644
--- a/lib/public/DB/QueryBuilder/IQueryBuilder.php
+++ b/lib/public/DB/QueryBuilder/IQueryBuilder.php
@@ -1036,7 +1036,7 @@ interface IQueryBuilder {
 	 * @return $this
 	 * @since 30.0.0
 	 */
-	public function hintShardKey(string $column, mixed $value);
+	public function hintShardKey(string $column, mixed $value, bool $overwrite = false);
 
 	/**
 	 * Set the query to run across all shards if sharding is enabled.