aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--include/dba/dba_pdo.php112
-rw-r--r--tests/unit/includes/dba/DbaPdoTest.php140
2 files changed, 250 insertions, 2 deletions
diff --git a/include/dba/dba_pdo.php b/include/dba/dba_pdo.php
index c8a1b6c85..a12629e19 100644
--- a/include/dba/dba_pdo.php
+++ b/include/dba/dba_pdo.php
@@ -10,6 +10,8 @@ class dba_pdo extends dba_driver {
public $driver_dbtype = null;
+ private string $server_version = '';
+
/**
* {@inheritDoc}
* @see dba_driver::connect()
@@ -37,6 +39,7 @@ class dba_pdo extends dba_driver {
try {
$this->db = new PDO($dsn,$user,$pass);
$this->db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
+ $this->server_version = $this->db->getAttribute(PDO::ATTR_SERVER_VERSION);
}
catch(PDOException $e) {
if(file_exists('dbfail.out')) {
@@ -73,9 +76,9 @@ class dba_pdo extends dba_driver {
}
}
- $result = null;
+ $result = false;
$this->error = '';
- $select = ((stripos($sql, 'select') === 0) ? true : false);
+ $select = stripos($sql, 'select') === 0 || stripos($sql, 'returning ') > 0;
try {
$result = $this->db->query($sql, PDO::FETCH_ASSOC);
@@ -115,6 +118,111 @@ class dba_pdo extends dba_driver {
return (($this->error) ? false : $r);
}
+ /**
+ * Insert a row into a table.
+ *
+ * The `$data` argument is an array of key/value pairs of the columns to
+ * insert, 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 inserted row will be returned.
+ *
+ * @param string $table The table to insert the row into.
+ * @param array $data The data to insert as an array of column name => value pairs.
+ * @param string $idcol The column name for the primary key of the table. We need to
+ * specify this since we don't have a consistent naming of primary
+ * id for tables.
+ *
+ * @return array|bool The complete record as read back from the database, or false if we
+ * could not fetch it.
+ */
+ public function insert(string $table, array $data, string $idcol): array|bool {
+ $keys = array_keys($data);
+ $values = array_map(
+ fn ($v) => is_numeric($v) ? $v : "'" . dbesc($v) . "'",
+ array_values($data)
+ );
+
+ $query = "INSERT INTO {$table} ("
+ . implode(', ', $keys) . ') VALUES ('
+ . implode(', ', $values) . ')';
+
+ // MySQL is the only supported DB that don't support the returning
+ // clause. Since the driver type is 'mysql' also for MariaDB, we need
+ // to check the actual server version to be sure we only exclude actual
+ // MySQL systems.
+ if ($this->driver_dbtype !== 'mysql' || stripos($this->server_version, 'mariadb') !== false) {
+ $query .= ' RETURNING *';
+ }
+
+ $res = $this->q($query);
+
+ if (is_a($res, PDOStatement::class)) {
+ //
+ // Calling PDO::lastInsertId should be safe here.
+ // The last inserted id is kept for each connection, so we're not risking
+ // a race condition wrt inserts by other requests that happen simultaneously.
+ //
+ $id = $this->db->lastInsertId($table);
+
+ $res = $this->q("SELECT * FROM {$table} WHERE {$idcol} = {$id}");
+
+ if (is_a($res, PDOStatement::class)) {
+ db_logger('dba_pdo: PDOStatement returned, did not expect that.');
+ return false;
+ }
+ }
+
+ if (is_array($res)) {
+ // Since we should never have more than one result, unwrap the array
+ // so we only have the resulting row.
+ $res = $res[0];
+ }
+
+ 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) {
return substr(substr(@$this->db->quote($str),1),0,-1);
diff --git a/tests/unit/includes/dba/DbaPdoTest.php b/tests/unit/includes/dba/DbaPdoTest.php
new file mode 100644
index 000000000..8a1a2b197
--- /dev/null
+++ b/tests/unit/includes/dba/DbaPdoTest.php
@@ -0,0 +1,140 @@
+<?php
+/**
+ * Tests for `includes/dba_pdo.php`.
+ *
+ * SPDX-FileCopyrightText: 2024 Hubzilla Community
+ * SPDX-FileContributor: Harald Eilertsen
+ *
+ * SPDX-License-Identifier: MIT
+ */
+
+namespace Zotlabs\Tests\Unit\includes;
+
+use DBA;
+use PDO;
+use PDOStatement;
+use PHPUnit\Framework\Attributes\DataProvider;
+use Zotlabs\Tests\Unit\UnitTestCase;
+
+class DbaPdoTest extends UnitTestCase
+{
+ public function testInsertingRowWithRturningClauseReturnsInsertedRow(): void
+ {
+ // MySQL does not support the `returning` clause, so we skip the test
+ // for that DB backend.
+ $this->skipIfMySQL();
+
+ // Let's manually insert a row in the config table.
+ // This is just because it's a conventient table to test
+ // against
+ $res = q(<<<SQL
+ INSERT INTO config (cat, k, v)
+ VALUES ('test', 'a key', 'A value')
+ RETURNING *
+ SQL);
+
+ $this->assertIsArray($res);
+ $this->assertIsArray($res[0]);
+ $this->assertTrue($res[0]['id'] > 0);
+ $this->assertEquals('test', $res[0]['cat']);
+ $this->assertEquals('a key', $res[0]['k']);
+ $this->assertEquals('A value', $res[0]['v']);
+ }
+
+ #[DataProvider('insertRowProvider')]
+ public function testInsertRow(string $table, array $data, string $id): void
+ {
+ $res = DBA::$dba->insert($table, $data, $id);
+
+ $this->assertIsArray($res);
+
+ // Make sure the result contains the expected id
+ $this->assertArrayHasKey($id, $res);
+
+ foreach ($data as $key => $value) {
+ $this->assertEquals($value, $res[$key]);
+ }
+ }
+
+ #[DataProvider('insertRowProvider')]
+ public function testInsertShouldReturnFalseIfInsertFails(
+ string $table,
+ array $data,
+ string $id
+ ): void
+ {
+ $res1 = DBA::$dba->insert($table, $data, $id);
+ $this->assertIsArray($res1);
+
+ // Inserting the same row again should fail.
+ $res2 = DBA::$dba->insert($table, $data, $id);
+ $this->assertFalse($res2);
+ }
+
+ /**
+ * Dataprovider for testInertRow.
+ *
+ * @return array An array of [ $table, $data, $id ] elements.
+ */
+ public static function insertRowProvider(): array
+ {
+ return [
+ 'table with numeric primary id' => [
+ 'config',
+ [ 'cat' => 'test', 'k' => 'a key', 'v' => 'A value' ],
+ 'id',
+ ],
+ 'table with text primary id' => [
+ 'cache',
+ [ 'k' => 'some key', 'v' => 'cached value', 'updated' => date('Y-m-d H:i:s')],
+ 'k',
+ ],
+ ];
+ }
+
+ 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}");
+ }
+
+ }
+}
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-06 04:44:45 +0000