feat(dbal): add proper insert ignore conflict method for SQLite

Signed-off-by: Benjamin Gaussorgues <benjamin.gaussorgues@nextcloud.com>

2 files changed, 81 insertions, 0 deletions

diff --git a/lib/private/DB/AdapterSqlite.php b/lib/private/DB/AdapterSqlite.php
index e84f62e8d80..24274cbcda6 100644
--- a/lib/private/DB/AdapterSqlite.php
+++ b/lib/private/DB/AdapterSqlite.php
@@ -76,4 +76,19 @@ class AdapterSqlite extends Adapter {
 			return 0;
 		}
 	}
+
+	public function insertIgnoreConflict(string $table, array $values): int {
+		$builder = $this->conn->getQueryBuilder();
+		$builder->insert($table);
+		$updates = [];
+		foreach ($values as $key => $value) {
+			$builder->setValue($key, $builder->createNamedParameter($value));
+		}
+
+		return $this->conn->executeStatement(
+			$builder->getSQL() . ' ON CONFLICT DO NOTHING',
+			$builder->getParameters(),
+			$builder->getParameterTypes()
+		);
+	}
 }
diff --git a/tests/lib/DB/AdapterTest.php b/tests/lib/DB/AdapterTest.php
new file mode 100644
index 00000000000..99b7cf4e099
--- /dev/null
+++ b/tests/lib/DB/AdapterTest.php
@@ -0,0 +1,66 @@
+<?php
+/**
+ * SPDX-FileCopyrightText: 2024 Nextcloud GmbH and Nextcloud contributors
+ * SPDX-License-Identifier: AGPL-3.0-or-later
+ */
+
+namespace Test\DB;
+
+use Test\TestCase;
+
+class AdapterTest extends TestCase {
+	private string $appId;
+	private $connection;
+
+	public function setUp(): void {
+		$this->connection = \OC::$server->getDatabaseConnection();
+		$this->appId = uniqid('test_db_adapter', true);
+	}
+
+	public function tearDown(): void {
+		$qb = $this->connection->getQueryBuilder();
+
+		$qb->delete('appconfig')
+			->from('appconfig')
+			->where($qb->expr()->eq('appid', $qb->createNamedParameter($this->appId)))
+			->execute();
+	}
+
+	public function testInsertIgnoreOnConflictDuplicate(): void {
+		$configKey = uniqid('key', true);
+		$expected = [
+			[
+				'configkey' => $configKey,
+				'configvalue' => '1',
+			]
+		];
+		$result = $this->connection->insertIgnoreConflict('appconfig', [
+			'appid' => $this->appId,
+			'configkey' => $configKey,
+			'configvalue' => '1',
+		]);
+		$this->assertEquals(1, $result);
+		$rows = $this->getRows($configKey);
+		$this->assertSame($expected, $rows);
+
+
+		$result = $this->connection->insertIgnoreConflict('appconfig', [
+			'appid' => $this->appId,
+			'configkey' => $configKey,
+			'configvalue' => '2',
+		]);
+		$this->assertEquals(0, $result);
+		$rows = $this->getRows($configKey);
+		$this->assertSame($expected, $rows);
+	}
+
+	private function getRows(string $configKey): array {
+		$qb = $this->connection->getQueryBuilder();
+		return $qb->select(['configkey', 'configvalue'])
+			->from('appconfig')
+			->where($qb->expr()->eq('appid', $qb->createNamedParameter($this->appId)))
+			->andWhere($qb->expr()->eq('configkey', $qb->createNamedParameter($configKey)))
+			->execute()
+			->fetchAll();
+	}
+}