Add an insert method to dba_pdo

A common use case is to insert a record into a database table, but also
instantiate an object from the inserted data. This requires that we know
the value of any default or calculated columns that is filled in by the
database when the row is inserter.

This patch adds a `insert` method to pda_dbo that will insert a row, and
immediately fetch the row back from the database – including the default
and calculated values not specified by the insert itself.

1 files changed, 95 insertions, 0 deletions

diff --git a/tests/unit/includes/dba/DbaPdoTest.php b/tests/unit/includes/dba/DbaPdoTest.php
new file mode 100644
index 000000000..39fdbe3e5
--- /dev/null
+++ b/tests/unit/includes/dba/DbaPdoTest.php
@@ -0,0 +1,95 @@
+<?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
+	{
+		$driver = DBA::$dba->db->getAttribute(PDO::ATTR_DRIVER_NAME);
+		if ($driver === 'mysql') {
+			$this->markTestSkipped("RETURNING clause not supported for {$driver}");
+		}
+
+		// 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',
+			],
+		];
+	}
+}