Introduce IDBConnection::setValues()

setValues() attempts to insert a new row, or failing that, update an
existing row. The ability to set preconditions is also available.

4 files changed, 89 insertions, 42 deletions

diff --git a/lib/private/allconfig.php b/lib/private/allconfig.php
index af7ffa4168e..3c85bfacbb8 100644
--- a/lib/private/allconfig.php
+++ b/lib/private/allconfig.php
@@ -205,57 +205,28 @@ class AllConfig implements \OCP\IConfig {
 		// TODO - FIXME
 		$this->fixDIInit();
 
-		// Check if the key does exist
-		$sql  = 'SELECT `configvalue` FROM `*PREFIX*preferences` '.
-				'WHERE `userid` = ? AND `appid` = ? AND `configkey` = ?';
-		$result = $this->connection->executeQuery($sql, array($userId, $appName, $key));
-		$oldValue = $result->fetchColumn();
-		$result->closeCursor();
-		$exists = $oldValue !== false;
-
-		if($oldValue === strval($value)) {
-			// no changes
-			return;
+		$preconditionArray = [];
+		if (isset($preCondition)) {
+			$preconditionArray = [
+				'configvalue' => $preCondition,
+			];
 		}
 
-		$affectedRows = 0;
-		if (!$exists && $preCondition === null) {
-			$this->connection->insertIfNotExist('*PREFIX*preferences', [
-				'configvalue'	=> $value,
-				'userid'		=> $userId,
-				'appid'			=> $appName,
-				'configkey'		=> $key,
-			], ['configkey', 'userid', 'appid']);
-			$affectedRows = 1;
-		} elseif ($exists) {
-			$data = array($value, $userId, $appName, $key);
-
-			$sql  = 'UPDATE `*PREFIX*preferences` SET `configvalue` = ? '.
-					'WHERE `userid` = ? AND `appid` = ? AND `configkey` = ? ';
-
-			if($preCondition !== null) {
-				if($this->getSystemValue('dbtype', 'sqlite') === 'oci') {
-					//oracle hack: need to explicitly cast CLOB to CHAR for comparison
-					$sql .= 'AND to_char(`configvalue`) = ?';
-				} else {
-					$sql .= 'AND `configvalue` = ?';
-				}
-				$data[] = $preCondition;
-			}
-			$affectedRows = $this->connection->executeUpdate($sql, $data);
-		}
+		$this->connection->setValues('preferences', [
+			'userid' => $userId,
+			'appid' => $appName,
+			'configkey' => $key,
+		], [
+			'configvalue' => $value,
+		], $preconditionArray);
 
 		// only add to the cache if we already loaded data for the user
-		if ($affectedRows > 0 && isset($this->userCache[$userId])) {
+		if (isset($this->userCache[$userId])) {
 			if (!isset($this->userCache[$userId][$appName])) {
 				$this->userCache[$userId][$appName] = array();
 			}
 			$this->userCache[$userId][$appName][$key] = $value;
 		}
-
-		if ($preCondition !== null && $affectedRows === 0) {
-			throw new PreConditionNotMetException;
-		}
 	}
 
 	/**
diff --git a/lib/private/appframework/db/db.php b/lib/private/appframework/db/db.php
index 812649daa78..5fdc5d1066c 100644
--- a/lib/private/appframework/db/db.php
+++ b/lib/private/appframework/db/db.php
@@ -147,6 +147,21 @@ class Db implements IDb {
 	}
 
 	/**
+	 * Insert or update a row value
+	 *
+	 * @param string $table
+	 * @param array $keys (column name => value)
+	 * @param array $values (column name => value)
+	 * @param array $updatePreconditionValues ensure values match preconditions (column name => value)
+	 * @return int number of new rows
+	 * @throws \Doctrine\DBAL\DBALException
+	 * @throws PreconditionNotMetException
+	 */
+	public function setValues($table, array $keys, array $values, array $updatePreconditionValues = []) {
+		return $this->connection->setValues($table, $keys, $values, $updatePreconditionValues);
+	}
+
+	/**
 	 * Start a transaction
 	 */
 	public function beginTransaction() {
diff --git a/lib/private/db/connection.php b/lib/private/db/connection.php
index 28bf3b6e05b..b1c1c12cb49 100644
--- a/lib/private/db/connection.php
+++ b/lib/private/db/connection.php
@@ -32,6 +32,7 @@ use Doctrine\Common\EventManager;
 use OC\DB\QueryBuilder\ExpressionBuilder;
 use OC\DB\QueryBuilder\QueryBuilder;
 use OCP\IDBConnection;
+use OCP\PreconditionNotMetException;
 
 class Connection extends \Doctrine\DBAL\Connection implements IDBConnection {
 	/**
@@ -242,6 +243,52 @@ class Connection extends \Doctrine\DBAL\Connection implements IDBConnection {
 	}
 
 	/**
+	 * Insert or update a row value
+	 *
+	 * @param string $table
+	 * @param array $keys (column name => value)
+	 * @param array $values (column name => value)
+	 * @param array $updatePreconditionValues ensure values match preconditions (column name => value)
+	 * @return int number of new rows
+	 * @throws \Doctrine\DBAL\DBALException
+	 * @throws PreconditionNotMetException
+	 */
+	public function setValues($table, array $keys, array $values, array $updatePreconditionValues = []) {
+		try {
+			$insertQb = $this->getQueryBuilder();
+			$insertQb->insert($table)
+				->values(
+					array_map(function($value) use ($insertQb) {
+						return $insertQb->createNamedParameter($value);
+					}, array_merge($keys, $values))
+				);
+			return $insertQb->execute();
+		} catch (\Doctrine\DBAL\Exception\UniqueConstraintViolationException $e) {
+			// value already exists, try update
+			$updateQb = $this->getQueryBuilder();
+			$updateQb->update($table);
+			foreach ($values as $name => $value) {
+				$updateQb->set($name, $updateQb->createNamedParameter($value));
+			}
+			$where = $updateQb->expr()->andx();
+			$whereValues = array_merge($keys, $updatePreconditionValues);
+			foreach ($whereValues as $name => $value) {
+				$where->add($updateQb->expr()->eq(
+					$name, $updateQb->createNamedParameter($value)
+				));
+			}
+			$updateQb->where($where);
+			$affected = $updateQb->execute();
+
+			if ($affected === 0) {
+				throw new PreconditionNotMetException();
+			}
+
+			return 0;
+		}
+	}
+
+	/**
 	 * returns the error code and message as a string for logging
 	 * works with DoctrineException
 	 * @return string
diff --git a/lib/public/idbconnection.php b/lib/public/idbconnection.php
index 49856fb78a5..c5767e65a82 100644
--- a/lib/public/idbconnection.php
+++ b/lib/public/idbconnection.php
@@ -109,6 +109,20 @@ interface IDBConnection {
 	public function insertIfNotExist($table, $input, array $compare = null);
 
 	/**
+	 * Insert or update a row value
+	 *
+	 * @param string $table
+	 * @param array $keys (column name => value)
+	 * @param array $values (column name => value)
+	 * @param array $updatePreconditionValues ensure values match preconditions (column name => value)
+	 * @return int number of new rows
+	 * @throws \Doctrine\DBAL\DBALException
+	 * @throws PreconditionNotMetException
+	 * @since 9.0.0
+	 */
+	public function setValues($table, array $keys, array $values, array $updatePreconditionValues = []);
+
+	/**
 	 * Start a transaction
 	 * @since 6.0.0
 	 */