From 1e19566aa4fa6f08f01ebad8a7d21ebb0974ae01 Mon Sep 17 00:00:00 2001
From: Benjamin Gaussorgues <benjamin.gaussorgues@nextcloud.com>
Date: Wed, 5 Jun 2024 11:20:45 +0200
Subject: feat(dbal): add proper insert ignore conflict method for MySQL

Signed-off-by: Benjamin Gaussorgues <benjamin.gaussorgues@nextcloud.com>
---
 lib/private/DB/AdapterMySQL.php | 28 ++++++++++++++++++++++++++++
 1 file changed, 28 insertions(+)

(limited to 'lib')

diff --git a/lib/private/DB/AdapterMySQL.php b/lib/private/DB/AdapterMySQL.php
index 8e854769e1f..598dbc4de20 100644
--- a/lib/private/DB/AdapterMySQL.php
+++ b/lib/private/DB/AdapterMySQL.php
@@ -35,4 +35,32 @@ class AdapterMySQL extends Adapter {
 
 		return $this->collation;
 	}
+
+	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));
+		}
+
+		/*
+		 * We can't use ON DUPLICATE KEY UPDATE here because Nextcloud use the CLIENT_FOUND_ROWS flag
+		 * With this flag the MySQL returns the number of selected rows
+		 * instead of the number of affected/modified rows
+		 * It's impossible to change this behaviour at runtime or for a single query
+		 * Then, the result is 1 if a row is inserted and also 1 if a row is updated with same or different values
+		 *
+		 * With INSERT IGNORE, the result is 1 when a row is inserted, 0 otherwise
+		 *
+		 * Risk: it can also ignore other errors like type mismatch or truncated data…
+		 */
+		$res = $this->conn->executeStatement(
+			preg_replace('/^INSERT/i', 'INSERT IGNORE', $builder->getSQL()),
+			$builder->getParameters(),
+			$builder->getParameterTypes()
+		);
+
+		return $res;
+	}
 }
-- 
cgit v1.2.3


From b7243681dd70b25fdefc9fe62c63bab840691c94 Mon Sep 17 00:00:00 2001
From: Benjamin Gaussorgues <benjamin.gaussorgues@nextcloud.com>
Date: Wed, 12 Jun 2024 11:24:07 +0200
Subject: feat(dbal): add proper insert ignore conflict method for SQLite

Signed-off-by: Benjamin Gaussorgues <benjamin.gaussorgues@nextcloud.com>
---
 lib/private/DB/AdapterSqlite.php | 15 +++++++++
 tests/lib/DB/AdapterTest.php     | 66 ++++++++++++++++++++++++++++++++++++++++
 2 files changed, 81 insertions(+)
 create mode 100644 tests/lib/DB/AdapterTest.php

(limited to 'lib')

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();
+	}
+}
-- 
cgit v1.2.3