Page MenuHomeDevCentral
Files F12319008

D3804.diff
No OneTemporary
Actions

D3804.diff
View Options

diff --git a/composer.json b/composer.json
--- a/composer.json
+++ b/composer.json
@@ -27,6 +27,7 @@
"require-dev": {
"ext-mbstring": "*",
"ext-mysqli": "*",
+ "ext-pdo": "*",
"ext-xmlwriter": "*",
"nasqueron/codestyle": "^0.1.2",
"phan/phan": "^5.3.1",
diff --git a/database/src/Engines/PDOEngine.php b/database/src/Engines/PDOEngine.php
new file mode 100644
--- /dev/null
+++ b/database/src/Engines/PDOEngine.php
@@ -0,0 +1,176 @@
+<?php
+
+namespace Keruald\Database\Engines;
+
+use Keruald\Database\DatabaseEngine;
+
+use Keruald\Database\Exceptions\EngineSetupException;
+use Keruald\Database\Exceptions\NotImplementedException;
+use Keruald\Database\Exceptions\SqlException;
+use Keruald\Database\Result\PDODatabaseResult;
+
+use PDO;
+use PDOException;
+use RuntimeException;
+
+abstract class PDOEngine extends DatabaseEngine {
+
+ protected PDO $db;
+
+ private int $fetchMode = PDO::FETCH_ASSOC;
+
+ protected ?PDOException $lastException = null;
+
+ /**
+ * Sends a unique query to the database.
+ *
+ * @param string $query
+ *
+ * @return PDODatabaseResult|bool
+ */
+ public function query (string $query) : PDODatabaseResult|bool {
+ try {
+ $result = $this->db->query($query);
+ } catch (PDOException $ex) {
+ if ($this->dontThrowExceptions) {
+ return false;
+ }
+
+ $this->lastException = $ex;
+ $this->onQueryError($query);
+ }
+
+ return new PDODatabaseResult($result, $this->fetchMode);
+ }
+
+ public function nextId () : int|string {
+ return $this->db->lastInsertId();
+ }
+
+ protected function getExceptionContext () : array {
+ $info = match ($this->lastException) {
+ null => $this->db->errorInfo(),
+ default => $this->lastException->errorInfo,
+ };
+
+ return self::parseErrorInfo($info);
+ }
+
+ private static function parseErrorInfo(array $info) : array {
+ $context = [];
+
+ // SQLSTATE error code
+ if ((int)$info[0] > 0) {
+ $context["errno"] = $info[0];
+ }
+
+ // Driver-specific error message
+ if ($info[2] !== null) {
+ $context["error"] = $info[2];
+ }
+
+ return $context;
+ }
+
+ public static function load (array $config) : DatabaseEngine {
+ $config = self::getConfig($config);
+
+ try {
+ $instance = new static(
+ $config['host'],
+ $config['username'],
+ $config['password'],
+ $config['database'],
+ );
+ } catch (RuntimeException $ex) {
+ throw new EngineSetupException(
+ $ex->getMessage(),
+ $ex->getCode(),
+ $ex
+ );
+ }
+
+ // Extra configuration
+ $instance->fetchMode = (int)$config["fetch_mode"];
+
+ return $instance;
+ }
+
+ private static function getConfig (array $config) : array {
+ return $config + [
+ 'host' => 'localhost',
+ 'username' => '',
+ 'password' => '',
+ 'database' => '',
+ 'fetch_mode' => PDO::FETCH_ASSOC,
+ ];
+ }
+
+ public function getUnderlyingDriver () : PDO {
+ return $this->db;
+ }
+
+ public function error () : array {
+ return self::parseErrorInfo($this->db->errorInfo());
+ }
+
+ ///
+ /// Events
+ ///
+
+ protected function onCantConnectToHost () : void {
+ $previous = $this->lastException;
+
+ $code = match($previous) {
+ null => 0,
+ default => $previous->getCode(),
+ };
+
+ $appendToMessage = match($previous) {
+ null => "",
+ default => ": " . $previous->getMessage(),
+ };
+
+ $ex = new RuntimeException(
+ "Can't connect to SQL server" . $appendToMessage,
+ $code,
+ $previous,
+ );
+
+ if (!class_exists(self::EVENTS_PROPAGATION_CLASS)) {
+ throw $ex;
+ }
+
+ $callable = [self::EVENTS_PROPAGATION_CLASS, "callOrThrow"];
+ $callable($this->cantConnectToHostEvents, [$this, $ex], $ex);
+ }
+
+ protected function onQueryError (string $query) : void {
+ $ex = SqlException::fromQuery(
+ $query,
+ $this->getExceptionContext(),
+ );
+
+ if (!class_exists(self::EVENTS_PROPAGATION_CLASS)) {
+ throw $ex;
+ }
+
+ $callable = [self::EVENTS_PROPAGATION_CLASS, "callOrThrow"];
+ $callable($this->queryErrorEvents, [$this, $query, $ex], $ex);
+ }
+
+ ///
+ /// Not implemented features
+ ///
+
+ /**
+ * @throws NotImplementedException
+ */
+ public function countAffectedRows () : int {
+ throw new NotImplementedException(<<<END
+ With PDO drivers, you can get the number of affected rows
+ for any SQL query using PDODatabaseResult::numRows().
+ END);
+ }
+
+}
diff --git a/database/src/Engines/PgsqlPDOEngine.php b/database/src/Engines/PgsqlPDOEngine.php
new file mode 100644
--- /dev/null
+++ b/database/src/Engines/PgsqlPDOEngine.php
@@ -0,0 +1,63 @@
+<?php
+
+namespace Keruald\Database\Engines;
+
+use Keruald\Database\Exceptions\NotImplementedException;
+use PDO;
+use Pdo\Pgsql;
+use RuntimeException;
+
+final class PgsqlPDOEngine extends PDOEngine {
+
+ use WithPDOPostgreSQL;
+
+ const string PDO_CLASS = Pgsql::class;
+
+ ///
+ /// Constructor
+ ///
+
+ public function __construct (
+ string $host = 'localhost',
+ string $username = 'root',
+ string $password = '',
+ string $database = ''
+ ) {
+ // Checks extension requirement
+ if (!class_exists(self::PDO_CLASS)) {
+ throw new RuntimeException("This engine requires PHP 8.4+ Pdo\Pgsql PostgreSQL PDO driver.");
+ }
+
+ // Connects to PostgreSQL server
+ $dsn = "pgsql:host=$host";
+ if ($database !== "") {
+ $dsn .= ";dbname=$database";
+ }
+
+ $this->db = new Pgsql($dsn, $username, $password);
+ $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+ }
+
+ ///
+ /// Not implemented features
+ ///
+
+ /**
+ * @throws NotImplementedException
+ */
+ public function escape (string $expression) : string {
+ throw new NotImplementedException(<<<END
+ This PDO engine does not support escape for literals.
+ Placeholders are recommended instead for PDO operations.
+ END);
+ }
+
+ ///
+ /// Engine-specific methods
+ ///
+
+ public function escapeIdentifier (string $expression) : string {
+ return $this->db->escapeIdentifier($expression);
+ }
+
+}
diff --git a/database/src/Engines/PostgreSQLPDOEngine.php b/database/src/Engines/PostgreSQLPDOEngine.php
new file mode 100644
--- /dev/null
+++ b/database/src/Engines/PostgreSQLPDOEngine.php
@@ -0,0 +1,60 @@
+<?php
+
+namespace Keruald\Database\Engines;
+
+use Keruald\Database\Exceptions\NotImplementedException;
+
+use PDO;
+use PDOException;
+use RuntimeException;
+
+class PostgreSQLPDOEngine extends PDOEngine {
+
+ use WithPDOPostgreSQL;
+
+ const string PDO_CLASS = PDO::class;
+
+ public function __construct (
+ string $host = 'localhost',
+ string $username = 'root',
+ string $password = '',
+ string $database = ''
+ ) {
+ // Checks extension requirement
+ if (!class_exists(self::PDO_CLASS)) {
+ throw new RuntimeException("This engine requires PDO extension.");
+ }
+
+ // Connects to PostgreSQL server
+ $dsn = "pgsql:host=$host";
+ if ($database !== "") {
+ $dsn .= ";dbname=$database";
+ }
+
+ try {
+ $this->db = new PDO($dsn, $username, $password);
+ } catch (PDOException $ex) {
+ $this->lastException = $ex;
+ $this->onCantConnectToHost();
+
+ return;
+ }
+
+ $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+ }
+
+ ///
+ /// Not implemented features
+ ///
+
+ /**
+ * @throws NotImplementedException
+ */
+ public function escape (string $expression) : string {
+ throw new NotImplementedException(<<<END
+ This PDO engine does not support escape for literals.
+ Placeholders are recommended instead for PDO operations.
+ END);
+ }
+
+}
diff --git a/database/src/Engines/WithPDOPostgreSQL.php b/database/src/Engines/WithPDOPostgreSQL.php
new file mode 100644
--- /dev/null
+++ b/database/src/Engines/WithPDOPostgreSQL.php
@@ -0,0 +1,45 @@
+<?php
+
+namespace Keruald\Database\Engines;
+
+use LogicException;
+use PDOException;
+
+trait WithPDOPostgreSQL {
+
+ public function isExistingTable (string $database, string $table) : bool {
+ /** @var \PDO $db */
+ $db = $this->getUnderlyingDriver();
+
+ $sql = "SELECT EXISTS (
+ SELECT FROM pg_tables
+ WHERE schemaname = ?
+ AND tablename = ?)::int;";
+
+ $stmt = $db->prepare($sql);
+ $stmt->execute([$database, $table]);
+
+ $result = $stmt->fetchColumn();
+ return (bool)$result;
+ }
+
+ public function queryScalar (string $query = "") : string {
+ if ($query === "") {
+ return "";
+ }
+
+ $result = $this->query($query);
+
+ $row = $result->fetchRow();
+ if ($row !== null) {
+ foreach ($row as $value) {
+ return (string)$value;
+ }
+ }
+
+ // No item
+ throw new LogicException("The queryScalar method is intended
+ to be used with SELECT queries and assimilated");
+ }
+
+}
diff --git a/database/src/Exceptions/NotImplementedException.php b/database/src/Exceptions/NotImplementedException.php
new file mode 100644
--- /dev/null
+++ b/database/src/Exceptions/NotImplementedException.php
@@ -0,0 +1,9 @@
+<?php
+
+namespace Keruald\Database\Exceptions;
+
+use RuntimeException;
+
+class NotImplementedException extends RuntimeException {
+
+}
diff --git a/database/src/Result/PDODatabaseResult.php b/database/src/Result/PDODatabaseResult.php
new file mode 100644
--- /dev/null
+++ b/database/src/Result/PDODatabaseResult.php
@@ -0,0 +1,45 @@
+<?php
+
+namespace Keruald\Database\Result;
+
+use PDO;
+use PDOStatement;
+
+use Traversable;
+
+class PDODatabaseResult extends DatabaseResult {
+
+ public function __construct (
+ private readonly PDOStatement $statement,
+ private readonly int $fetchMode = PDO::FETCH_ASSOC,
+ ) { }
+
+ ///
+ /// DatabaseResult implementation
+ ///
+
+ public function numRows () : int {
+ return $this->statement->rowCount();
+ }
+
+ public function fetchRow () : ?array {
+ $row = $this->statement->fetch($this->fetchMode);
+
+ if ($row === false) {
+ return null;
+ }
+
+ return $row;
+ }
+
+ ///
+ /// IteratorAggregate implementation
+ ///
+
+ public function getIterator () : Traversable {
+ while ($row = $this->fetchRow()) {
+ yield $row;
+ }
+ }
+
+}
diff --git a/database/tests/Engines/BasePDOPostgreSQLTestCase.php b/database/tests/Engines/BasePDOPostgreSQLTestCase.php
new file mode 100644
--- /dev/null
+++ b/database/tests/Engines/BasePDOPostgreSQLTestCase.php
@@ -0,0 +1,94 @@
+<?php
+
+namespace Keruald\Database\Tests\Engines;
+
+use Keruald\Database\Exceptions\NotImplementedException;
+use Keruald\Database\Exceptions\SqlException;
+use Keruald\Database\Result\PDODatabaseResult;
+
+abstract class BasePDOPostgreSQLTestCase extends BasePDOTestCase {
+
+ const string DB_NAME = "test_keruald_db";
+
+ public function testQueryScalarWithNonSelectQuery(): void {
+ $this->expectException(\LogicException::class);
+
+ $sql = "UPDATE numbers SET number = number * 2";
+ $this->db->queryScalar($sql);
+ }
+
+ public function testFetchRow(): void {
+ $sql = "SELECT 10 UNION SELECT 20 UNION SELECT 30 ORDER BY 1";
+ $result = $this->db->query($sql);
+
+ // PostgreSQL uses "?column?" as default column name for unnamed columns
+ $this->assertEquals(10, $result->fetchRow()['?column?']);
+ $this->assertEquals(20, $result->fetchRow()['?column?']);
+ $this->assertEquals(30, $result->fetchRow()['?column?']);
+
+ // Then, we get a null value
+ $this->assertEquals(null, $result->fetchRow());
+ }
+
+ public function testArrayShapeForFetchRow(): void {
+ $sql = "SELECT 10 as score, 50 as \"limit\"";
+ $result = $this->db->query($sql);
+
+ $expected = [
+ // By column name
+ "score" => 10,
+ "limit" => 50
+ ];
+
+ $this->assertEquals($expected, $result->fetchRow());
+ }
+
+ public function testQueryWhenItSucceeds(): void {
+ $result = $this->db->query("DELETE FROM numbers");
+
+ $this->assertInstanceOf(PDODatabaseResult::class, $result);
+ }
+
+ public function testQueryWhenItFailsWithException(): void {
+ $this->expectException(SqlException::class);
+ $this->db->query("TRUNCATE not_existing_table");
+ }
+
+ public function testQueryWithWrongQueryInLegacyMode(): void {
+ $this->db->dontThrowExceptions = true;
+
+ $result = $this->db->query("TRUNCATE not_existing");
+
+ $this->assertFalse($result);
+ }
+
+ public function testNextId(): void {
+ // Use a transaction to isolate this test
+ // Arcanist creates a race condition on this when running from `arc diff`
+ $this->db->query("BEGIN");
+
+ try {
+ // PostgreSQL uses sequences for auto-increment
+ $this->db->query("TRUNCATE numbers RESTART IDENTITY");
+ $this->db->query("INSERT INTO numbers (id, number) VALUES (1700, 42742)");
+ $this->db->query("INSERT INTO numbers (number) VALUES (666)");
+
+ // Get the last inserted ID
+ $lastId = $this->db->nextId();
+ $this->assertGreaterThan(0, $lastId);
+
+ $this->db->query("ROLLBACK"); // No need to commit, tests done
+ } catch (\Exception $ex) {
+ $this->db->query("ROLLBACK");
+ $this->fail($ex->getMessage());
+ }
+ }
+
+ public function testEscapeNotImplemented(): void {
+ $this->expectException(NotImplementedException::class);
+ $this->expectExceptionMessage('This PDO engine does not support escape for literals');
+
+ $this->db->escape("test'string");
+ }
+
+}
diff --git a/database/tests/Engines/BasePDOTestCase.php b/database/tests/Engines/BasePDOTestCase.php
new file mode 100644
--- /dev/null
+++ b/database/tests/Engines/BasePDOTestCase.php
@@ -0,0 +1,35 @@
+<?php
+
+namespace Keruald\Database\Tests\Engines;
+
+use Keruald\Database\Engines\PDOEngine;
+
+use Keruald\Database\Exceptions\SqlException;
+use PHPUnit\Framework\TestCase;
+
+abstract class BasePDOTestCase extends TestCase {
+
+ protected PDOEngine $db;
+
+ protected static abstract function buildEngine () : PDOEngine;
+
+ protected function setUp (): void {
+ $this->db = static::buildEngine();
+ }
+
+ public function testQueryScalar(): void {
+ $sql = "SELECT 1+1";
+ $this->assertEquals(2, $this->db->queryScalar($sql));
+ }
+
+ public function testQueryScalarWithoutQuery(): void {
+ $this->assertEquals("", $this->db->queryScalar(""));
+ }
+
+ public function testQueryScalarWithWrongQuery(): void {
+ $this->expectException(SqlException::class);
+
+ $sql = "DELETE FROM nonexisting";
+ $this->db->queryScalar($sql);
+ }
+}
diff --git a/database/tests/Engines/PgsqlPDOEngineTest.php b/database/tests/Engines/PgsqlPDOEngineTest.php
new file mode 100644
--- /dev/null
+++ b/database/tests/Engines/PgsqlPDOEngineTest.php
@@ -0,0 +1,90 @@
+<?php
+
+namespace Keruald\Database\Tests\Engines;
+
+use Keruald\Database\Engines\PDOEngine;
+use Keruald\Database\Engines\PgsqlPDOEngine;
+use Keruald\Database\Exceptions\EngineSetupException;
+
+use PDO;
+use Pdo\Pgsql;
+use PHPUnit\Framework\Attributes\DataProvider;
+
+class PgsqlPDOEngineTest extends BasePDOPostgreSQLTestCase {
+
+ protected function setUp () : void {
+ // Skip if not on PHP 8.4+
+ if (version_compare(PHP_VERSION, '8.4.0', '<')) {
+ $this->markTestSkipped('Pdo\Pgsql requires PHP version >= 8.4.0');
+ }
+
+ parent::setUp();
+ }
+
+ protected static function buildEngine () : PDOEngine {
+ return new PgsqlPDOEngine(
+ 'localhost',
+ 'keruald',
+ 'keruald',
+ self::DB_NAME,
+ );
+ }
+
+ public function testLoad () : void {
+ $instance = PgsqlPDOEngine::load([
+ 'host' => 'localhost',
+ 'username' => 'keruald',
+ 'password' => 'keruald',
+ 'database' => self::DB_NAME,
+ ]);
+
+ $this->assertInstanceOf(PDO::class, $instance->getUnderlyingDriver());
+ }
+
+ public function testLoadWithWrongPassword(): void {
+ $this->expectException(EngineSetupException::class);
+
+ PgsqlPDOEngine::load([
+ 'host' => 'localhost',
+ 'username' => 'notexisting',
+ 'password' => 'notexistingeither',
+ 'database' => self::DB_NAME,
+ ]);
+ }
+
+ public function testLoadWithFetchMode(): void {
+ $instance = PgsqlPDOEngine::load([
+ 'host' => 'localhost',
+ 'username' => 'keruald',
+ 'password' => 'keruald',
+ 'database' => self::DB_NAME,
+ 'fetch_mode' => PDO::FETCH_BOTH,
+ ]);
+
+ $result = $instance->query("SELECT 1 as num");
+ $row = $result->fetchRow();
+
+ // Should have both numeric and associative keys
+ $this->assertArrayHasKey(0, $row);
+ $this->assertArrayHasKey('num', $row);
+ }
+
+ ///
+ /// Engine-specific methods
+ ///
+
+ public static function provideIdentifiers () : iterable {
+ yield "Regular name" => ["foo", '"foo"'];
+ yield "No special escape for apostrophe" => ["y'sul", '"y\'sul"'];
+ yield "Reserved keyword" => ["user", '"user"'];
+ }
+
+ #[DataProvider("provideIdentifiers")]
+ public function testEscapeIdentifier ($toEscape, $expected) : void {
+ /** @var PgsqlPDOEngine $db */
+ $db = $this->db;
+
+ $actual = $db->escapeIdentifier($toEscape);
+ $this->assertEquals($expected, $actual);
+ }
+}
diff --git a/database/tests/Engines/PostgreSQLPDOEngineTest.php b/database/tests/Engines/PostgreSQLPDOEngineTest.php
new file mode 100644
--- /dev/null
+++ b/database/tests/Engines/PostgreSQLPDOEngineTest.php
@@ -0,0 +1,60 @@
+<?php
+
+namespace Keruald\Database\Tests\Engines;
+
+use Keruald\Database\Engines\PDOEngine;
+use Keruald\Database\Engines\PostgreSQLPDOEngine;
+use Keruald\Database\Exceptions\EngineSetupException;
+
+use PDO;
+
+class PostgreSQLPDOEngineTest extends BasePDOPostgreSQLTestCase {
+
+ protected static function buildEngine () : PDOEngine {
+ return new PostgreSQLPDOEngine(
+ 'localhost',
+ 'keruald',
+ 'keruald',
+ self::DB_NAME,
+ );
+ }
+
+ public function testLoad () : void {
+ $instance = PostgreSQLPDOEngine::load([
+ 'host' => 'localhost',
+ 'username' => 'keruald',
+ 'password' => 'keruald',
+ 'database' => self::DB_NAME,
+ ]);
+
+ $this->assertInstanceOf(PDO::class, $instance->getUnderlyingDriver());
+ }
+
+ public function testLoadWithWrongPassword(): void {
+ $this->expectException(EngineSetupException::class);
+
+ PostgreSQLPDOEngine::load([
+ 'host' => 'localhost',
+ 'username' => 'notexisting',
+ 'password' => 'notexistingeither',
+ 'database' => self::DB_NAME,
+ ]);
+ }
+
+ public function testLoadWithFetchMode(): void {
+ $instance = PostgreSQLPDOEngine::load([
+ 'host' => 'localhost',
+ 'username' => 'keruald',
+ 'password' => 'keruald',
+ 'database' => self::DB_NAME,
+ 'fetch_mode' => PDO::FETCH_BOTH,
+ ]);
+
+ $result = $instance->query("SELECT 1 as num");
+ $row = $result->fetchRow();
+
+ // Should have both numeric and associative keys
+ $this->assertArrayHasKey(0, $row);
+ $this->assertArrayHasKey('num', $row);
+ }
+}
diff --git a/database/tests/data/postgresql.sql b/database/tests/data/postgresql.sql
new file mode 100644
--- /dev/null
+++ b/database/tests/data/postgresql.sql
@@ -0,0 +1,27 @@
+DROP TABLE IF EXISTS numbers CASCADE;
+CREATE TABLE numbers
+(
+ id SERIAL PRIMARY KEY,
+ number INTEGER NULL
+);
+
+DROP TABLE IF EXISTS ships CASCADE;
+CREATE TABLE ships
+(
+ id SERIAL PRIMARY KEY,
+ name VARCHAR(255) NULL,
+ category VARCHAR(3) NULL
+);
+
+INSERT INTO ships VALUES
+ (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');
+
+DROP VIEW IF EXISTS ships_count CASCADE;
+CREATE VIEW ships_count AS
+SELECT category,
+ COUNT(category) AS "count(category)"
+FROM ships
+GROUP BY category;

File Metadata

Mime Type
text/plain
Expires
Fri, Oct 24, 08:10 (20 h, 4 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
3100300
Default Alt Text
D3804.diff (21 KB)

Event Timeline

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