fix(core): ensure unique vcategoryfix/unique-vcategory

Signed-off-by: skjnldsv <skjnldsv@protonmail.com>

7 files changed, 149 insertions, 7 deletions

diff --git a/core/Listener/AddMissingIndicesListener.php b/core/Listener/AddMissingIndicesListener.php
index f54dc7e17fe..89c96648b99 100644
--- a/core/Listener/AddMissingIndicesListener.php
+++ b/core/Listener/AddMissingIndicesListener.php
@@ -210,5 +210,11 @@ class AddMissingIndicesListener implements IEventListener {
 			'systag_objecttype',
 			['objecttype']
 		);
+
+		$event->addMissingUniqueIndex(
+			'vcategory',
+			'unique_category_per_user',
+			['uid', 'type', 'category']
+		);
 	}
 }
diff --git a/core/Migrations/Version13000Date20170718121200.php b/core/Migrations/Version13000Date20170718121200.php
index d33d489c579..35c2d1730bc 100644
--- a/core/Migrations/Version13000Date20170718121200.php
+++ b/core/Migrations/Version13000Date20170718121200.php
@@ -658,6 +658,7 @@ class Version13000Date20170718121200 extends SimpleMigrationStep {
 			$table->addIndex(['uid'], 'uid_index');
 			$table->addIndex(['type'], 'type_index');
 			$table->addIndex(['category'], 'category_index');
+			$table->addUniqueIndex(['uid', 'type', 'category'], 'unique_category_per_user');
 		}
 
 		if (!$schema->hasTable('vcategory_to_object')) {
diff --git a/core/Migrations/Version32000Date20250731062008.php b/core/Migrations/Version32000Date20250731062008.php
new file mode 100644
index 00000000000..ba8df51fb15
--- /dev/null
+++ b/core/Migrations/Version32000Date20250731062008.php
@@ -0,0 +1,126 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * SPDX-FileCopyrightText: 2025 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+
+namespace OC\Core\Migrations;
+
+use Closure;
+use OCP\DB\ISchemaWrapper;
+use OCP\IDBConnection;
+use OCP\Migration\IOutput;
+use OCP\Migration\SimpleMigrationStep;
+use Override;
+
+/**
+ * Make sure vcategory entries are unique per user and type
+ * This migration will clean up existing duplicates
+ * and add a unique constraint to prevent future duplicates.
+ */
+class Version32000Date20250731062008 extends SimpleMigrationStep {
+	public function __construct(
+		private IDBConnection $connection,
+	) {
+	}
+
+	/**
+	 * @param IOutput $output
+	 * @param Closure(): ISchemaWrapper $schemaClosure
+	 * @param array $options
+	 */
+	#[Override]
+	public function preSchemaChange(IOutput $output, Closure $schemaClosure, array $options): void {
+		// Clean up duplicate categories before adding unique constraint
+		$this->cleanupDuplicateCategories($output);
+	}
+
+	/**
+	 * @param IOutput $output
+	 * @param Closure(): ISchemaWrapper $schemaClosure
+	 * @param array $options
+	 * @return null|ISchemaWrapper
+	 */
+	#[Override]
+	public function changeSchema(IOutput $output, Closure $schemaClosure, array $options): ?ISchemaWrapper {
+		return null;
+	}
+
+	/**
+	 * @param IOutput $output
+	 * @param Closure(): ISchemaWrapper $schemaClosure
+	 * @param array $options
+	 */
+	#[Override]
+	public function postSchemaChange(IOutput $output, Closure $schemaClosure, array $options): void {
+	}
+
+	/**
+	 * Clean up duplicate categories
+	 */
+	private function cleanupDuplicateCategories(IOutput $output) {
+		$output->info('Starting cleanup of duplicate vcategory records...');
+
+		// Find all categories, ordered to identify duplicates
+		$qb = $this->connection->getQueryBuilder();
+		$qb->select('id', 'uid', 'type', 'category')
+			->from('vcategory')
+			->orderBy('uid')
+			->addOrderBy('type')
+			->addOrderBy('category')
+			->addOrderBy('id');
+
+		$result = $qb->executeQuery();
+
+		$seen = [];
+		$duplicateCount = 0;
+
+		while ($category = $result->fetch()) {
+			$key = $category['uid'] . '|' . $category['type'] . '|' . $category['category'];
+			$categoryId = (int)$category['id'];
+
+			if (!isset($seen[$key])) {
+				// First occurrence - keep this one
+				$seen[$key] = $categoryId;
+				continue;
+			}
+
+			// Duplicate found
+			$keepId = $seen[$key];
+			$duplicateCount++;
+
+			$output->info("Found duplicate: keeping ID $keepId, removing ID $categoryId");
+
+			// Update object references
+			$updateQb = $this->connection->getQueryBuilder();
+			$updateQb->update('vcategory_to_object')
+				->set('categoryid', $updateQb->createNamedParameter($keepId))
+				->where($updateQb->expr()->eq('categoryid', $updateQb->createNamedParameter($categoryId)));
+
+			$affectedRows = $updateQb->executeStatement();
+			if ($affectedRows > 0) {
+				$output->info(" - Updated $affectedRows object references from category $categoryId to $keepId");
+			}
+
+			// Remove duplicate category record
+			$deleteQb = $this->connection->getQueryBuilder();
+			$deleteQb->delete('vcategory')
+				->where($deleteQb->expr()->eq('id', $deleteQb->createNamedParameter($categoryId)));
+
+			$deleteQb->executeStatement();
+			$output->info(" - Deleted duplicate category record ID $categoryId");
+
+		}
+
+		$result->closeCursor();
+
+		if ($duplicateCount === 0) {
+			$output->info('No duplicate categories found');
+		} else {
+			$output->info("Duplicate cleanup completed - processed $duplicateCount duplicates");
+		}
+	}
+}
diff --git a/lib/composer/composer/autoload_classmap.php b/lib/composer/composer/autoload_classmap.php
index 3f2dbe5edf9..5da1bc4ba30 100644
--- a/lib/composer/composer/autoload_classmap.php
+++ b/lib/composer/composer/autoload_classmap.php
@@ -1510,6 +1510,7 @@ return array(
     'OC\\Core\\Migrations\\Version31000Date20240814184402' => $baseDir . '/core/Migrations/Version31000Date20240814184402.php',
     'OC\\Core\\Migrations\\Version31000Date20250213102442' => $baseDir . '/core/Migrations/Version31000Date20250213102442.php',
     'OC\\Core\\Migrations\\Version32000Date20250620081925' => $baseDir . '/core/Migrations/Version32000Date20250620081925.php',
+    'OC\\Core\\Migrations\\Version32000Date20250731062008' => $baseDir . '/core/Migrations/Version32000Date20250731062008.php',
     'OC\\Core\\Notification\\CoreNotifier' => $baseDir . '/core/Notification/CoreNotifier.php',
     'OC\\Core\\ResponseDefinitions' => $baseDir . '/core/ResponseDefinitions.php',
     'OC\\Core\\Service\\LoginFlowV2Service' => $baseDir . '/core/Service/LoginFlowV2Service.php',
diff --git a/lib/composer/composer/autoload_static.php b/lib/composer/composer/autoload_static.php
index 233b667add9..87ef0dddb48 100644
--- a/lib/composer/composer/autoload_static.php
+++ b/lib/composer/composer/autoload_static.php
@@ -1551,6 +1551,7 @@ class ComposerStaticInit749170dad3f5e7f9ca158f5a9f04f6a2
         'OC\\Core\\Migrations\\Version31000Date20240814184402' => __DIR__ . '/../../..' . '/core/Migrations/Version31000Date20240814184402.php',
         'OC\\Core\\Migrations\\Version31000Date20250213102442' => __DIR__ . '/../../..' . '/core/Migrations/Version31000Date20250213102442.php',
         'OC\\Core\\Migrations\\Version32000Date20250620081925' => __DIR__ . '/../../..' . '/core/Migrations/Version32000Date20250620081925.php',
+        'OC\\Core\\Migrations\\Version32000Date20250731062008' => __DIR__ . '/../../..' . '/core/Migrations/Version32000Date20250731062008.php',
         'OC\\Core\\Notification\\CoreNotifier' => __DIR__ . '/../../..' . '/core/Notification/CoreNotifier.php',
         'OC\\Core\\ResponseDefinitions' => __DIR__ . '/../../..' . '/core/ResponseDefinitions.php',
         'OC\\Core\\Service\\LoginFlowV2Service' => __DIR__ . '/../../..' . '/core/Service/LoginFlowV2Service.php',
diff --git a/lib/private/Tags.php b/lib/private/Tags.php
index 0a37f4c9f4e..0d33424365e 100644
--- a/lib/private/Tags.php
+++ b/lib/private/Tags.php
@@ -268,15 +268,17 @@ class Tags implements ITags {
 	public function add(string $name) {
 		$name = trim($name);
 
-		if ($name === '') {
+		if (empty($name)) {
 			$this->logger->debug(__METHOD__ . ' Cannot add an empty tag', ['app' => 'core']);
 			return false;
 		}
+
+		// Prevent duplicate
 		if ($this->userHasTag($name, $this->user)) {
-			// TODO use unique db properties instead of an additional check
 			$this->logger->debug(__METHOD__ . ' Tag with name already exists', ['app' => 'core']);
 			return false;
 		}
+
 		try {
 			$tag = new Tag($this->user, $this->type, $name);
 			$tag = $this->mapper->insert($tag);
@@ -288,6 +290,7 @@ class Tags implements ITags {
 			]);
 			return false;
 		}
+
 		$this->logger->debug(__METHOD__ . ' Added an tag with ' . $tag->getId(), ['app' => 'core']);
 		return $tag->getId();
 	}
@@ -303,7 +306,7 @@ class Tags implements ITags {
 		$from = trim($from);
 		$to = trim($to);
 
-		if ($to === '' || $from === '') {
+		if (empty($to) || empty($from)) {
 			$this->logger->debug(__METHOD__ . 'Cannot use an empty tag names', ['app' => 'core']);
 			return false;
 		}
@@ -313,12 +316,14 @@ class Tags implements ITags {
 		} else {
 			$key = $this->getTagByName($from);
 		}
+
 		if ($key === false) {
 			$this->logger->debug(__METHOD__ . 'Tag ' . $from . 'does not exist', ['app' => 'core']);
 			return false;
 		}
 		$tag = $this->tags[$key];
 
+		// Prevent duplicate
 		if ($this->userHasTag($to, $tag->getOwner())) {
 			$this->logger->debug(__METHOD__ . 'A tag named' . $to . 'already exists for user' . $tag->getOwner(), ['app' => 'core']);
 			return false;
@@ -355,7 +360,7 @@ class Tags implements ITags {
 
 		$newones = [];
 		foreach ($names as $name) {
-			if (!$this->hasTag($name) && $name !== '') {
+			if (!$this->hasTag($name) && !empty($name)) {
 				$newones[] = new Tag($this->user, $this->type, $name);
 			}
 			if (!is_null($id)) {
@@ -504,13 +509,15 @@ class Tags implements ITags {
 	public function tagAs($objid, $tag, string $path = '') {
 		if (is_string($tag) && !is_numeric($tag)) {
 			$tag = trim($tag);
-			if ($tag === '') {
+			if (empty($tag)) {
 				$this->logger->debug(__METHOD__ . ', Cannot add an empty tag');
 				return false;
 			}
+
 			if (!$this->hasTag($tag)) {
 				$this->add($tag);
 			}
+
 			$tagId = $this->getTagId($tag);
 		} else {
 			$tagId = $tag;
@@ -547,7 +554,7 @@ class Tags implements ITags {
 	public function unTag($objid, $tag, string $path = '') {
 		if (is_string($tag) && !is_numeric($tag)) {
 			$tag = trim($tag);
-			if ($tag === '') {
+			if (empty($tag)) {
 				$this->logger->debug(__METHOD__ . ', Tag name is empty');
 				return false;
 			}
diff --git a/version.php b/version.php
index bb36c6f5831..c13cc6eebc2 100644
--- a/version.php
+++ b/version.php
@@ -9,7 +9,7 @@
 // between betas, final and RCs. This is _not_ the public version number. Reset minor/patch level
 // when updating major/minor version number.
 
-$OC_Version = [32, 0, 0, 1];
+$OC_Version = [32, 0, 0, 2];
 
 // The human-readable string
 $OC_VersionString = '32.0.0 dev';