diff options
| -rw-r--r-- | include/dba/dba_pdo.php | 65 | ||||
| -rw-r--r-- | tests/unit/includes/dba/DbaPdoTest.php | 95 |
2 files changed, 159 insertions, 1 deletions
diff --git a/include/dba/dba_pdo.php b/include/dba/dba_pdo.php index c8a1b6c85..fc10fee56 100644 --- a/include/dba/dba_pdo.php +++ b/include/dba/dba_pdo.php @@ -75,7 +75,7 @@ class dba_pdo extends dba_driver { $result = null; $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 +115,69 @@ 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) + ); + + $res = $this->q("INSERT INTO {$table} (" + . implode(', ', $keys) . ') VALUES (' + . implode(', ', $values) . ')' + ); + + 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; + } + } elseif ($res === null) { + // While `q` should never return null, that's exactly what it + // does when the insert fails. Let's turn it to a false instead. + $res = 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; + } + + 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..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', + ], + ]; + } +} |