Add an dba_pdo::update method

This is a convenience funcition to make it easier to update an existing
row in a database table.

2 files changed, 84 insertions, 6 deletions

diff --git a/include/dba/dba_pdo.php b/include/dba/dba_pdo.php
index 7a1dfb48a..a12629e19 100644
--- a/include/dba/dba_pdo.php
+++ b/include/dba/dba_pdo.php
@@ -185,6 +185,43 @@ class dba_pdo extends dba_driver {
 		return $res;
 	}
 
+	/**
+	 * Update an existing row in a table.
+	 *
+	 * The `$data` argument is an array of key/value pairs of the columns to
+	 * update, where the key is the column name. Values are automatically
+	 * escaped if needed, and should be provided unescaped to this function.
+	 *
+	 * @note it is the callers responsibility to ensure that only valid
+	 * column names are passed as keys in the array.
+	 *
+	 * The row to be updated is identified by `$idcol` and `$idval` as the
+	 * column name and value respectively. This should normally be the unique
+	 * id column of the table, but can in theory be any column with a unique
+	 * value that identifies a specific row.
+	 *
+	 * @param string $table		The table to update.
+	 * @param array $data		The columns to update as key => value pairs.
+	 * @param string $idcol		The name of the id column to check $idval against.
+	 * @param mixed $idval		The id of the row to update.
+	 *
+	 * @return bool		True if the update succeeded, false otherwise.
+	 */
+	public function update(string $table, array $data, string $idcol, mixed $idval): bool {
+		$set_statements = [];
+
+		foreach ($data as $k => $v) {
+			$set_statements[] = "set {$k}=" . (is_numeric($v) ? $v : "'" . dbesc($v) . "'");
+		}
+
+		$query = "UPDATE {$table} "
+			. implode(', ', $set_statements)
+			. " WHERE {$idcol} = {$idval}";
+
+		$res = $this->q($query);
+
+		return is_a($res, PDOStatement::class);
+	}
 
 	function escape($str) {
 		if($this->db && $this->connected) {
diff --git a/tests/unit/includes/dba/DbaPdoTest.php b/tests/unit/includes/dba/DbaPdoTest.php
index 7eb66b791..5d85a1604 100644
--- a/tests/unit/includes/dba/DbaPdoTest.php
+++ b/tests/unit/includes/dba/DbaPdoTest.php
@@ -20,14 +20,9 @@ class DbaPdoTest extends UnitTestCase
 {
 	public function testInsertingRowWithRturningClauseReturnsInsertedRow(): void
 	{
-		$driver = DBA::$dba->db->getAttribute(PDO::ATTR_DRIVER_NAME);
-		$version = DBA::$dba->db->getAttribute(PDO::ATTR_SERVER_VERSION);
-
 		// MySQL does not support the `returning` clause, so we skip the test
 		// for that DB backend.
-		if ($driver === 'mysql' && stripos($version, 'mariadb') === false) {
-			$this->markTestSkipped("RETURNING clause not supported for {$driver}");
-		}
+		$this->skipIfMySQL();
 
 		// Let's manually insert a row in the config table.
 		// This is just because it's a conventient table to test
@@ -96,4 +91,50 @@ class DbaPdoTest extends UnitTestCase
 			],
 		];
 	}
+
+	public function testUpdateRow(): void
+	{
+		// Let's fetch a row from the config table
+		$res = q('SELECT * FROM config WHERE cat = "system" AND k = "baseurl"');
+
+		$this->assertIsArray($res);
+		$this->assertIsArray($res[0]);
+
+		$row = $res[0];
+
+		// Update the baseurl
+		$updated = DBA::$dba->update(
+			'config',
+			[ 'v' => 'https://some.other_site.test/' ],
+			'id',
+			$row['id']
+		);
+
+		$this->assertTrue($updated);
+
+		// Verify that the record was updated
+		$updated_res = q('SELECT * FROM config WHERE cat = "system" AND k = "baseurl"');
+		$this->assertIsArray($updated_res);
+
+		$updated_row = $updated_res[0];
+
+		$this->assertIsArray($updated_row);
+		$this->assertEquals($row['id'], $updated_row['id']);
+		$this->assertEquals('system', $updated_row['cat']);
+		$this->assertEquals('baseurl', $updated_row['k']);
+		$this->assertEquals('https://some.other_site.test/', $updated_row['v']);
+	}
+
+	/**
+	 * Mark the test as skipped if the current db is MySQL.
+	 */
+	private function skipIfMySQL(): void {
+		$driver = DBA::$dba->db->getAttribute(PDO::ATTR_DRIVER_NAME);
+		$version = DBA::$dba->db->getAttribute(PDO::ATTR_SERVER_VERSION);
+
+		if ($driver === 'mysql' && stripos($version, 'mariadb') === false) {
+			$this->markTestSkipped("RETURNING clause not supported for {$driver}");
+		}
+
+	}
 }