fix(systemtags): case-insensitive search & prevent duplicates

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

3 files changed, 94 insertions, 35 deletions

diff --git a/cypress/e2e/systemtags/files-bulk-action.cy.ts b/cypress/e2e/systemtags/files-bulk-action.cy.ts
index 575b4da9c38..7ed9ad7fa7b 100644
--- a/cypress/e2e/systemtags/files-bulk-action.cy.ts
+++ b/cypress/e2e/systemtags/files-bulk-action.cy.ts
@@ -411,4 +411,58 @@ describe('Systemtags: Files bulk action', { testIsolation: false }, () => {
 			cy.runOccCommand('config:app:set systemtags restrict_creation_to_admin --value 0')
 		})
 	})
+
+	it('Can search for tags with insensitive case', () => {
+		let tagId: string
+		resetTags()
+
+		cy.runOccCommand('tag:add TESTTAG public --output json').then(({ stdout }) => {
+			const tag = JSON.parse(stdout)
+			tagId = tag.id
+		})
+
+		cy.createRandomUser().then((user1) => {
+			files.forEach((file) => {
+				cy.uploadContent(user1, new Blob([]), 'text/plain', '/' + file)
+			})
+
+			cy.login(user1)
+			cy.visit('/apps/files')
+
+			files.forEach((file) => {
+				getRowForFile(file).should('be.visible')
+			})
+			selectAllFiles()
+
+			triggerTagManagementDialogAction()
+
+			cy.findByRole('textbox', { name: 'Search or create tag' }).should('be.visible')
+			cy.findByRole('textbox', { name: 'Search tag' }).should('not.exist')
+
+			cy.get('[data-cy-systemtags-picker-input]').type('testtag')
+
+			cy.get('[data-cy-systemtags-picker-tag]').should('have.length', 1)
+			cy.get(`[data-cy-systemtags-picker-tag="${tagId}"]`).should('be.visible')
+				.findByRole('checkbox').should('not.be.checked')
+
+			// Assign the tag
+			cy.intercept('PROPFIND', '/remote.php/dav/systemtags/*/files').as('getTagData')
+			cy.intercept('PROPPATCH', '/remote.php/dav/systemtags/*/files').as('assignTagData')
+
+			cy.get(`[data-cy-systemtags-picker-tag="${tagId}"]`).should('be.visible')
+				.findByRole('checkbox').click({ force: true })
+			cy.get('[data-cy-systemtags-picker-button-submit]').click()
+
+			cy.wait('@getTagData')
+			cy.wait('@assignTagData')
+
+			expectInlineTagForFile('file1.txt', ['TESTTAG'])
+			expectInlineTagForFile('file2.txt', ['TESTTAG'])
+			expectInlineTagForFile('file3.txt', ['TESTTAG'])
+			expectInlineTagForFile('file4.txt', ['TESTTAG'])
+			expectInlineTagForFile('file5.txt', ['TESTTAG'])
+
+			cy.get('[data-cy-systemtags-picker]').should('not.exist')
+		})
+	})
 })
diff --git a/lib/private/SystemTag/SystemTagManager.php b/lib/private/SystemTag/SystemTagManager.php
index e889ceff54e..4b421fa033a 100644
--- a/lib/private/SystemTag/SystemTagManager.php
+++ b/lib/private/SystemTag/SystemTagManager.php
@@ -108,7 +108,7 @@ class SystemTagManager implements ISystemTagManager {
 
 		if (!empty($nameSearchPattern)) {
 			$query->andWhere(
-				$query->expr()->like(
+				$query->expr()->iLike(
 					'name',
 					$query->createNamedParameter('%' . $this->connection->escapeLikeParameter($nameSearchPattern) . '%')
 				)
@@ -120,7 +120,7 @@ class SystemTagManager implements ISystemTagManager {
 			->addOrderBy('visibility', 'ASC')
 			->addOrderBy('editable', 'ASC');
 
-		$result = $query->execute();
+		$result = $query->executeQuery();
 		while ($row = $result->fetch()) {
 			$tags[$row['id']] = $this->createSystemTagFromRow($row);
 		}
@@ -156,6 +156,14 @@ class SystemTagManager implements ISystemTagManager {
 			throw new TagCreationForbiddenException();
 		}
 
+		// Check if tag already exists (case-insensitive)
+		$existingTags = $this->getAllTags(null, $tagName);
+		foreach ($existingTags as $existingTag) {
+			if (mb_strtolower($existingTag->getName()) === mb_strtolower($tagName)) {
+				throw new TagAlreadyExistsException('Tag ' . $tagName . ' already exists');
+			}
+		}
+
 		// Length of name column is 64
 		$truncatedTagName = substr($tagName, 0, 64);
 		$query = $this->connection->getQueryBuilder();
@@ -226,6 +234,15 @@ class SystemTagManager implements ISystemTagManager {
 			$color
 		);
 
+		// Check if tag already exists (case-insensitive)
+		$existingTags = $this->getAllTags(null, $truncatedNewName);
+		foreach ($existingTags as $existingTag) {
+			if (mb_strtolower($existingTag->getName()) === mb_strtolower($truncatedNewName)
+				&& $existingTag->getId() !== $tagId) {
+				throw new TagAlreadyExistsException('Tag ' . $truncatedNewName . ' already exists');
+			}
+		}
+
 		$query = $this->connection->getQueryBuilder();
 		$query->update(self::TAG_TABLE)
 			->set('name', $query->createParameter('name'))
diff --git a/tests/lib/SystemTag/SystemTagManagerTest.php b/tests/lib/SystemTag/SystemTagManagerTest.php
index 7bbe86aabe7..b443d7222b2 100644
--- a/tests/lib/SystemTag/SystemTagManagerTest.php
+++ b/tests/lib/SystemTag/SystemTagManagerTest.php
@@ -82,17 +82,6 @@ class SystemTagManagerTest extends TestCase {
 					['two', false, false],
 				]
 			],
-			[
-				// duplicate names, different flags
-				[
-					['one', false, false],
-					['one', true, false],
-					['one', false, true],
-					['one', true, true],
-					['two', false, false],
-					['two', false, true],
-				]
-			]
 		];
 	}
 
@@ -163,14 +152,14 @@ class SystemTagManagerTest extends TestCase {
 			[
 				[
 					['one', true, false],
-					['one', false, false],
+					['one_different', false, false],
 					['two', true, false],
 				],
 				null,
 				'on',
 				[
 					['one', true, false],
-					['one', false, false],
+					['one_different', false, false],
 				]
 			],
 			// filter by name pattern and visibility
@@ -179,7 +168,7 @@ class SystemTagManagerTest extends TestCase {
 				[
 					['one', true, false],
 					['two', true, false],
-					['one', false, false],
+					['one_different', false, false],
 				],
 				true,
 				'on',
@@ -246,6 +235,15 @@ class SystemTagManagerTest extends TestCase {
 		$this->tagManager->createTag($name, $userVisible, $userAssignable);
 	}
 
+	public function testCreateDuplicateWithDifferentFlags(): void {
+		$this->expectException(TagAlreadyExistsException::class);
+
+		// Create a tag with specific flags
+		$this->tagManager->createTag('duplicate', true, false);
+		// Try to create a tag with the same name but different flags - should fail
+		$this->tagManager->createTag('duplicate', false, true);
+	}
+
 	public function testCreateOverlongName(): void {
 		$tag = $this->tagManager->createTag('Zona circundante do Palácio Nacional da Ajuda (Jardim das Damas, Salão de Física, Torre Sineira, Paço Velho e Jardim Botânico)', true, true);
 		$this->assertSame('Zona circundante do Palácio Nacional da Ajuda (Jardim das Damas', $tag->getName()); // 63 characters but 64 bytes due to "á"
@@ -349,30 +347,20 @@ class SystemTagManagerTest extends TestCase {
 
 	}
 
-	#[\PHPUnit\Framework\Attributes\DataProvider('updateTagProvider')]
-	public function testUpdateTagDuplicate($tagCreate, $tagUpdated): void {
+	public function testUpdateTagToExistingName(): void {
 		$this->expectException(TagAlreadyExistsException::class);
 
-		$this->tagManager->createTag(
-			$tagCreate[0],
-			$tagCreate[1],
-			$tagCreate[2],
-			$tagCreate[3],
-		);
-		$tag2 = $this->tagManager->createTag(
-			$tagUpdated[0],
-			$tagUpdated[1],
-			$tagUpdated[2],
-			$tagUpdated[3],
-		);
+		// Create two different tags
+		$tag1 = $this->tagManager->createTag('first', true, true);
+		$tag2 = $this->tagManager->createTag('second', false, false);
 
-		// update to match the first tag
+		// Try to update tag2 to have the same name as tag1 - should fail
 		$this->tagManager->updateTag(
 			$tag2->getId(),
-			$tagCreate[0],
-			$tagCreate[1],
-			$tagCreate[2],
-			$tagCreate[3],
+			'first',
+			false,
+			false,
+			null
 		);
 	}