Page MenuHomeDevCentral
Files F11723648

No OneTemporary
Actions

View Options

diff --git a/README.md b/README.md
index fcf3657..bbead20 100644
--- a/README.md
+++ b/README.md
@@ -1,99 +1,115 @@
# keruald/database
This library offers a simple layer of abstraction for database operations.
## Configuration
To get a database instance, you need to pass configuration as an array.
The properties and values depend on the engine you want to use.
### MySQLi
-| Key | Value | |
-|----------|--------------------------------------|:--------:|
-| engine | MySQLiEngine class reference | |
-| host | The MySQL hostname, e.g. "localhost" | |
-| username | The MySQL user to use for connection | |
-| password | The clear text password to use | |
-| database | The default db to select for queries | optional |
+| Key | Value | |
+|------------|--------------------------------------|:---------------|
+| engine | MySQLiEngine class reference | |
+| host | The MySQL hostname, e.g. "localhost" | |
+| username | The MySQL user to use for connection | |
+| password | The clear text password to use | |
+| database | The default db to select for queries | (optional) |
+| fetch_mode | The default mode to fetch rows | `MYSQLI_ASSOC` |
For example:
```php
[
'engine' => Keruald\Database\Engines\MySQLiEngine::class,
'host' => 'localhost',
'username' => 'app',
'password' => 'someSecret',
'database' => 'app', // optional
]
```
+#### About fetch_mode parameter
+
+The `fetch_mode` parameter is used to determine how to represent results:
+
+ * `MYSQLI_ASSOC` will use column names
+ * `MYSQLI_NUM` will use an enumerated array (0, 1, 2, …)
+ * `MYSQLI_BOTH` will use both of them
+
+The code offers `MYSQLI_ASSOC` as default value to allow to directly represent
+a row result as API output and encourage to take care of the column names for
+better code maintenance. If you wish to switch to default MySQLi behavior,
+use `MYSQLI_BOTH` instead.
+
+Those constants are defined by the MySQLi extension.
+
## Legacy drivers
The mysql extension has been deprecated in PHP 5.7 and removed in PHP 7.
As such, this extension isn't supported anymore. You can use straightforwardly
replace 'MySQL' by 'MySQLi' as engine.
## Specialized drivers for tests
### Blackhole
The blackhole engine does nothing and always returns `true` as query result.
This engine can be used for mocks:
- directly, when database behavior does not matter
- to build a mock by overriding behavior of query() or any other method
It can also be used with the loader, without any engine-specific configuration:
```php
[
'engine' => Keruald\Database\Engines\BlackholeEngine::class,
]
```
### MockDatabaseEngine
The mock database is a simple implementation of the blackhole engine as mocking
service to use when you want to return a deterministic response to known
queries.
A benefit is you don't need a running database server for your unit tests.
You can pass to the `withQueries` method an array with one item per query:
- key: the SQL query
- value: an array with all rows for that query
-
For example:
```php
public function testGetFruits () : void {
$queries = [
"SELECT name, color FROM fruits" => [
[ "name" => "strawberry", "color" => "red" ],
[ "name" => "blueberry", "color" => "violet" ],
],
];
$db = (new MockDatabaseEngine())
->withQueries($queries);
// Inject $db to a class and test it
}
```
To return only one row, you can use `[[…]]` to represent an array of one array:
```php
$queries = [
"SELECT 1+1" => [[ "1+1" => 2 ]],
];
```
The queries results are then wrapped in the MockDatabaseResult class.
When the query doesn't exist, an exception is thrown.
We recommend a mixed approach of the Blackhole engine when results don't matter,
and of this class when you need some control on it.
diff --git a/src/DatabaseEngine.php b/src/DatabaseEngine.php
index ee2bffa..d6bf063 100644
--- a/src/DatabaseEngine.php
+++ b/src/DatabaseEngine.php
@@ -1,145 +1,154 @@
<?php
namespace Keruald\Database;
use Keruald\Database\Exceptions\SqlException;
use BadMethodCallException;
use Keruald\Database\Result\DatabaseResult;
use LogicException;
abstract class DatabaseEngine {
///
/// Traits
///
use WithLegacyMethods;
///
/// Methods the specific engine need to implement to access database
///
public abstract function escape (string $expression) : string;
public abstract function query (string $query);
public abstract function nextId () : int|string;
public abstract function countAffectedRows () : int;
protected abstract function getExceptionContext (): array;
/**
* Determines if the specified table or view exists.
*/
public abstract function isExistingTable (
string $database, string $table
) : bool;
///
/// Engine mechanics
///
public abstract static function load (array $config): DatabaseEngine;
public abstract function getUnderlyingDriver () : mixed;
///
/// Helpers we can use across all engines
///
/**
* Runs a query, then returns the first scalar element of result,
* ie the element in the first column of the first row.
*
* This is intended for queries with only one scalar result like
* 'SELECT count(*) FROM …' or 'SELECT value WHERE unique_key = …'.
*
* @param string $query The query to execute
* @return string the scalar result
* @throws SqlException
*/
public function queryScalar (string $query = '') : string {
if ($query === '') {
//No query, no value
return '';
}
$result = $this->query($query);
// If legacy mode is enabled, we have a MySQL error here.
if ($result === false) {
throw SqlException::fromQuery($query, $this->getExceptionContext());
}
// Ensure the query is SELECT / SHOW / DESCRIBE / EXPLAIN,
// so we have a scalar result to actually return.
//
// That allows to detect bugs where queryScalar() is used
// with the intent to fetch metadata information,
// e.g. the amount of rows updated or deleted.
if (is_bool($result)) {
throw new LogicException("The queryScalar method is intended
to be used with SELECT queries and assimilated");
}
// Fetches first row of the query, and return the first element
// If there isn't any result row, returns an empty string.
- return $result->fetchRow()[0] ?? "";
+ $row = $result->fetchRow();
+ if ($row === null) {
+ return "";
+ }
+
+ $key = array_key_first($row);
+ return match ($key) {
+ null => "",
+ default => $row[$key],
+ };
}
///
/// Compatibility with legacy code
///
/**
* @var bool Don't throw exceptions if a query doesn't succeed
* @deprecated Replace `if (!$result = $db->query(…))` by an error handler
*/
public bool $dontThrowExceptions = false;
/**
* Gets the number of rows affected or returned by a query.
*
* @return int the number of rows affected (delete/insert/update)
* or the number of rows in query result
* @deprecated Use $result->numRows or $db->countAffectedRows();
*/
public function numRows (DatabaseResult|bool $result = false) : int {
if ($result instanceof DatabaseResult) {
return $result->numRows();
}
return $this->countAffectedRows();
}
/**
* Fetches a row from the query result.
*
* @param DatabaseResult $result The query result
* @return array|null An associative array with the database result,
* or null if no more result is available.
*/
public function fetchRow (DatabaseResult $result) : ?array {
return $result->fetchRow();
}
/**
* Allows the legacy use of sql_query, sql_fetchrow, sql_escape, etc.
*
* @throws BadMethodCallException when the method name doesn't exist.
* @deprecated
*/
public function __call (string $name, array $arguments) {
if (str_starts_with($name, 'sql_')) {
return $this->callByLegacyMethodName($name, $arguments);
}
$className = get_class($this);
throw new BadMethodCallException(
"Method doesn't exist: $className::$name"
);
}
}
diff --git a/src/Engines/MySQLiEngine.php b/src/Engines/MySQLiEngine.php
index cac9d1c..b54a7fd 100644
--- a/src/Engines/MySQLiEngine.php
+++ b/src/Engines/MySQLiEngine.php
@@ -1,206 +1,216 @@
<?php
namespace Keruald\Database\Engines;
use Keruald\Database\DatabaseEngine;
use Keruald\Database\Exceptions\EngineSetupException;
use Keruald\Database\Exceptions\SqlException;
use Keruald\Database\Result\MySQLiDatabaseResult;
use RuntimeException;
use mysqli;
use mysqli_driver;
use mysqli_result;
use mysqli_sql_exception;
class MySQLiEngine extends DatabaseEngine {
use WithMySQL;
/**
* The connection identifier
*/
private mysqli $db;
/**
* The MySQL driver
*/
private mysqli_driver $driver;
+ private int $fetchMode = MYSQLI_ASSOC;
+
/**
* Initializes a new instance of the database abstraction class,
* for MySQLi engine.
*
* @param string $host The host of the MySQL server [optional, default: localhost]
* @param string $username The username used to connect [optional, default: root]
* @param string $password The password used to connect [optional, default: empty]
* @param string $database The database to select [optional]
*/
function __construct(
string $host = 'localhost',
string $username = 'root',
string $password = '',
string $database = ''
) {
// Checks extension requirement
if (!class_exists("mysqli")) {
throw new RuntimeException("You've chosen to use a MySQLi database engine, but the MySQLi extension is missing.");
}
// Connects to MySQL server
$this->driver = new mysqli_driver();
$this->db = new mysqli($host, $username, $password);
$this->setCharset('utf8mb4');
// Selects database
if ($database !== '') {
$this->db->select_db($database);
}
}
/**
* Sends a unique query to the database.
*
* @return mysqli_result|bool For successful SELECT, SHOW, DESCRIBE or
* EXPLAIN queries, a <b>mysqli_result</b> object; otherwise, true, or false
* on failure in legacy mode.
* @throws SqlException if legacy mode is disabled, and the query fails.
*/
function query (string $query) : MySQLiDatabaseResult|bool {
// Run query
try {
$result = $this->db->query($query);
} catch (mysqli_sql_exception $ex) {
if ($this->dontThrowExceptions) {
return false;
}
$this->throwException($ex, $query);
}
if (is_bool($result)) {
return $result;
}
- return new MySQLiDatabaseResult($result);
+ return new MySQLiDatabaseResult($result, $this->fetchMode);
}
/**
* Gets more information about the last SQL error.
*
* @return array an array with two keys, code and message, containing error information
* @deprecated The PHP drivers and our abstraction now throw exceptions when an error occur.
*/
function error () : array {
return [
'code' => $this->db->errno,
'message' => $this->db->error,
];
}
/**
* Gets the primary key value of the last query
* (works only in INSERT context)
*
* @return int|string the primary key value
*/
public function nextId () : int|string {
return $this->db->insert_id;
}
/**
* Escapes a SQL expression.
*
* @param string $expression The expression to escape
* @return string The escaped expression
*/
public function escape (string $expression) : string {
return $this->db->real_escape_string($expression);
}
public function countAffectedRows () : int {
return $this->db->affected_rows;
}
///
/// MySQL specific
///
/**
* Sets charset
*/
public function setCharset ($encoding) {
$this->db->set_charset($encoding);
}
+ public function setFetchMode (int $mode) {
+ $this->fetchMode = $mode;
+ }
+
///
/// Engine mechanics methods
///
private function throwException (mysqli_sql_exception $ex, string $query) {
$context = $this->getExceptionContext();
throw SqlException::fromException($ex, $query, $context);
}
protected function getExceptionContext () : array {
return [
'error' => $this->db->error,
'errno' => $this->db->errno,
'errors' => $this->db->error_list,
];
}
private static function getConfig (array $config) : array {
return $config + [
'host' => 'localhost',
'username' => '',
'password' => '',
'database' => '',
+ 'fetch_mode' => MYSQLI_ASSOC,
];
}
/**
* Loads a database instance, connected and ready to process queries.
*
* @throws EngineSetupException
*/
static function load (array $config): MySQLiEngine {
$config = self::getConfig($config);
// We need to return an exception if it fails.
// Switch report mode to the exception throwing one.
$driver = new mysqli_driver();
$configuredReportMode = $driver->report_mode;
$driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;
try {
$instance = new self(
$config['host'],
$config['username'],
$config['password'],
$config['database'],
);
} catch (mysqli_sql_exception $ex) {
throw new EngineSetupException(
$ex->getMessage(),
$ex->getCode(),
$ex
);
}
// Restore report mode as previously configured
$driver->report_mode = $configuredReportMode;
+ // Extra configuration
+ $instance->setFetchMode($config["fetch_mode"]);
+
return $instance;
}
/**
* @return mysqli Represents a connection between PHP and a MySQL database.
*/
public function getUnderlyingDriver (): mysqli {
return $this->db;
}
}
diff --git a/src/Result/MySQLiDatabaseResult.php b/src/Result/MySQLiDatabaseResult.php
index 2e7d1dd..0f49b1a 100644
--- a/src/Result/MySQLiDatabaseResult.php
+++ b/src/Result/MySQLiDatabaseResult.php
@@ -1,41 +1,41 @@
<?php
namespace Keruald\Database\Result;
use mysqli_result;
use Traversable;
class MySQLiDatabaseResult extends DatabaseResult {
///
/// Constructor
///
public function __construct (
private mysqli_result $result,
- private int $resultType = MYSQLI_BOTH
+ private int $resultType = MYSQLI_ASSOC
) { }
///
/// DatabaseResult implementation
///
public function numRows () : int {
return $this->result->num_rows;
}
public function fetchRow () : ?array {
return $this->result->fetch_array($this->resultType);
}
///
/// IteratorAggregate implementation
///
public function getIterator () : Traversable {
while ($row = $this->fetchRow()) {
yield $row;
}
}
}
diff --git a/tests/Engines/MySQLiEngineTest.php b/tests/Engines/MySQLiEngineTest.php
index e618d56..2b85137 100644
--- a/tests/Engines/MySQLiEngineTest.php
+++ b/tests/Engines/MySQLiEngineTest.php
@@ -1,208 +1,236 @@
<?php
namespace Keruald\Database\Tests\Engines;
use Keruald\Database\Engines\MySQLiEngine;
use Keruald\Database\Exceptions\EngineSetupException;
use Keruald\Database\Exceptions\SqlException;
use LogicException;
use PHPUnit\Framework\TestCase;
class MySQLiEngineTest extends TestCase {
private MySQLiEngine $db;
const DB_NAME = "test_keruald_db";
protected function setUp (): void {
$this->db = new MySQLiEngine('localhost', '', '', self::DB_NAME);
}
public function testLoad () {
$instance = MySQLiEngine::load([
'host' => 'localhost',
'username' => '',
'password' => '',
'database' => self::DB_NAME,
]);
$this->assertInstanceOf("mysqli", $instance->getUnderlyingDriver());
}
public function testLoadWithWrongPassword () {
$this->expectException(EngineSetupException::class);
$instance = MySQLiEngine::load([
'host' => 'localhost',
'username' => 'notexisting',
'password' => 'notexistingeither',
'database' => self::DB_NAME,
]);
}
public function testQueryScalar () {
$sql = "SELECT 1+1";
$this->assertEquals(2, $this->db->queryScalar($sql));
}
public function testQueryScalarWithoutQuery () {
$this->assertEquals("", $this->db->queryScalar(""));
}
public function testQueryScalarWithWrongQuery () {
$this->expectException(SqlException::class);
$sql = "DELETE FROM nonexisting";
$this->db->queryScalar($sql);
}
public function testQueryScalarWithNonSelectQuery () {
$this->expectException(LogicException::class);
$sql = "UPDATE numbers SET number = number * 2";
$this->db->queryScalar($sql);
}
public function testSetCharset () {
$expected = "binary";
$this->db->setCharset($expected);
$sql = "SELECT @@SESSION.character_set_connection";
$actual = $this->db->queryScalar($sql);
$this->assertEquals($expected, $actual);
}
public function testFetchRow () {
$sql = "SELECT 10 UNION SELECT 20 UNION SELECT 30";
$result = $this->db->query($sql);
// First, we get associative arrays like [0 => 10, 10u => 10]
// ^ position ^ column name
- $this->assertEquals(10, $this->db->fetchRow($result)[0]);
- $this->assertEquals(20, $this->db->fetchRow($result)[0]);
- $this->assertEquals(30, $this->db->fetchRow($result)[0]);
+ $this->assertEquals(10, $this->db->fetchRow($result)[10]);
+ $this->assertEquals(20, $this->db->fetchRow($result)[10]);
+ $this->assertEquals(30, $this->db->fetchRow($result)[10]);
// Then, we get a null value
$this->assertEquals(null, $this->db->fetchRow($result));
}
public function testArrayShapeForFetchRow () {
$sql = "SELECT 10 as score, 50 as `limit`";
$result = $this->db->query($sql);
+ $expected = [
+ // By column name
+ "score" => 10,
+ "limit" => 50
+ ];
+
+ $this->assertEquals($expected, $this->db->fetchRow($result));
+ }
+
+ public function testArrayShapeForFetchRowWithFetchModeBoth () {
+ $sql = "SELECT 10 as score, 50 as `limit`";
+ $this->db->setFetchMode(MYSQLI_BOTH);
+ $result = $this->db->query($sql);
+
$expected = [
// By position
0 => 10,
1 => 50,
// By column name
"score" => 10,
"limit" => 50
];
$this->assertEquals($expected, $this->db->fetchRow($result));
}
+ public function testArrayShapeForFetchRowWithFetchModeEnum () {
+ $sql = "SELECT 10 as score, 50 as `limit`";
+ $this->db->setFetchMode(MYSQLI_NUM);
+ $result = $this->db->query($sql);
+
+ $expected = [
+ // By position
+ 0 => 10,
+ 1 => 50,
+ ];
+
+ $this->assertEquals($expected, $this->db->fetchRow($result));
+ }
+
public function testQueryWhenItSucceeds () {
$result = $this->db->query("DELETE FROM numbers");
$this->assertTrue($result);
}
public function testQueryWhenItFailsWithoutException () {
mysqli_report(MYSQLI_REPORT_OFF);
$result = $this->db->query("TRUNCATE not_existing");
$this->assertFalse($result);
}
public function testQueryWhenItFailsWithException () {
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$this->expectException(SqlException::class);
$this->db->query("TRUNCATE not_existing_table");
}
public function testQueryWithWrongQueryInLegacyMode () {
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$this->db->dontThrowExceptions = true;
$result = $this->db->query("TRUNCATE not_existing");
$this->assertFalse($result);
}
public function testNextId () {
$this->db->query("TRUNCATE numbers");
$this->db->query("INSERT INTO numbers VALUES (1700, 42742)");
$this->db->query("INSERT INTO numbers (number) VALUES (666)");
$this->assertSame(1701, $this->db->nextId());
}
public function testEscape () {
$this->assertEquals("foo\')", $this->db->escape("foo')"));
}
public function testGetUnderlyingDriver () {
$this->assertInstanceOf("mysqli", $this->db->getUnderlyingDriver());
}
public function testNumRowsForSelect () {
$sql = "SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4";
$result = $this->db->query($sql);
$this->assertSame(4, $this->db->numRows($result));
}
public function testNumRowsForInsert () {
$sql = "INSERT INTO numbers (number) VALUES (1), (2), (3), (4), (5)";
$result = $this->db->query($sql);
$this->assertSame(5, $this->db->numRows($result));
}
public function testError () {
$expected = [
"code" => 1146,
"message" => "Table 'test_keruald_db.not_existing' doesn't exist",
];
mysqli_report(MYSQLI_REPORT_OFF);
$this->db->query("TRUNCATE not_existing");
$this->assertEquals($expected, $this->db->error());
}
public function testIsView () {
$this->assertTrue($this->db->isView(self::DB_NAME, "ships_count"));
}
public function testIsViewWhenItIsTable () {
$this->assertFalse($this->db->isView(self::DB_NAME, "ships"));
}
public function testIsViewWhenNotExisting () {
$this->assertFalse($this->db->isView(self::DB_NAME, "notexisting"));
}
public function testIsExisting () {
$this->assertTrue($this->db->isExistingTable(self::DB_NAME, "ships"));
}
public function testIsExistingWithView () {
$this->assertTrue($this->db->isExistingTable(
self::DB_NAME, "ships_count")
);
}
public function testIsExistingWhenNotExisting () {
$this->assertFalse($this->db->isExistingTable(
self::DB_NAME, "notexisting")
);
}
}
diff --git a/tests/Result/MySQLiDatabaseResultTest.php b/tests/Result/MySQLiDatabaseResultTest.php
index c1f85eb..1365e0c 100644
--- a/tests/Result/MySQLiDatabaseResultTest.php
+++ b/tests/Result/MySQLiDatabaseResultTest.php
@@ -1,54 +1,55 @@
<?php
namespace Keruald\Database\Tests\Result;
use Keruald\Database\Engines\MySQLiEngine;
use Keruald\Database\Result\MySQLiDatabaseResult;
use PHPUnit\Framework\TestCase;
class MySQLiDatabaseResultTest extends TestCase {
private MySQLiDatabaseResult $result;
protected function setUp () : void {
$db = new MySQLiEngine('localhost', '', '', 'test_keruald_db');
+ $db->setFetchMode(MYSQLI_BOTH);
$sql = "SELECT id, name, category FROM ships";
$this->result = $db->query($sql);
}
public function provideExpectedData () : array {
$data = [
// MYSQLI_NUM data
["1", "So Much For Subtlety", "GSV"],
["2", "Unfortunate Conflict Of Evidence", "GSV"],
["3", "Just Read The Instructions", "GCU"],
["4", "Just Another Victim Of The Ambient Morality", "GCU"],
];
return array_map(function ($row) {
// MYSQLI_ASSOC data
return $row + [
"id" => $row[0],
"name" => $row[1],
"category" => $row[2],
];
}, $data);
}
public function testGetIterator () {
$actual = iterator_to_array($this->result->getIterator());
$this->assertEquals($this->provideExpectedData(), $actual);
}
public function testFetchRow () {
$expected = $this->provideExpectedData()[0];
$this->assertEquals($expected, $this->result->fetchRow());
}
public function testNumRows () {
$this->assertEquals(4, $this->result->numRows());
}
}

File Metadata

Mime Type
text/x-diff
Expires
Thu, Sep 18, 10:51 (13 h, 19 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
2990577
Default Alt Text
(25 KB)

Event Timeline

Nasqueron DevCentral · If it had been much bigger the moon would have had a core of ice. · Powered by Phabricator