jimbojsb/pseudo

A system for mocking PHP PDO connections

Pseudo

Pseudo is a system for mocking PHP's PDO database connections. When writing unit tests for PHP applications, one frequently has the need to test code that interacts with a database. However, in the true spirit of a unit test, the database should be abstracted, as we can assume with some degree of certainty that things like network links to the database server, the database connection drivers, and the database server and software itself are "going to work", and they are outside the scope of our unit tests.

Enter Pseudo. Pseudo allows you to have "fake" interactions with a database that produce predefined results every time. This has 2 main advantages over actually interacting with a database. First, it saves having to write data fixtures in another format, ensuring the data schema availability, loading the fixtures in, and then later cleaing and resetting them between tests. Second, and somewhat as a result of the first, tests can run significantly faster because they are essentially talking to an in-memory object structure rather than incurring all the overhead of connecting and interacting with an actual database.

Theory of Operation

The general idea is that Pseudo implements all of the classes in the PDO system by inheriting from them and then overriding their methods. During your test, at the point where you would inject a PDO object into your data layer, you can now inject a Pseudo\Pdo object transparently, giving yourself 100% flexibility to control what your application now thinks is the database. In your unit test, you can express the mocks for your test in terms of SQL statements and arrays of result data.

Simple Example

<?php
$p = new Pseudo\Pdo();
$results = [['id' => 1, 'foo' => 'bar']];
$p->mock("SELECT id FROM objects WHERE foo='bar'", $results);

// now use this $p object like you would any regular PDO
$results = $p->query("SELECT id FROM objects WHERE foo='bar'");
while ($result = $results->fetch(PDO::FETCH_ASSOC)) {
	echo $result["foo"];  // bar
}

Supported features

The internal storage of mocks and results are associatve arrays. Pseudo attempts to implement as much of the standard PDO feature set as possible, so varies different fetch modes, bindings, parameterized queries, etc all work as you'd expect them to.

Not implemented / wish-list items

• The transaction api is implemented to the point of managing current transaction state, but transactions have no actual effect
• Anything related to scrolling cursors has not been implemented, and this includes the fetch modes that might require them
• Pseudo can load and save serialized copies of it's mocked data, but in the future, it will be able to "record" a live PDO connection to a real database and then use that data to create mocks from your actual data
• Pseudo isn't strict-mode compatible, which means tests might fail due to unexpected errors with signatures and offsets, etc. (I'd happily accept a pull request to fix this!)

Tests

Pseudo has a fairly robust test suite written with PHPUnit. If you'd like to run the tests, simply run ./vendor/bin/phpunit in the root folder. The tests have no external library dependencies (other than phpunit) and should require no additional setup or bootstrapping to run.

Pseudo is also tested on Travis-CI

Requirements

Pseudo internals currently target PHP 5.4.0 and above. It has no external dependencies aside from the PDO extension, which seems rather obvious.

Pseudo is built and tested with error reporting set to E_ALL & ~(E_NOTICE | E_DEPRECATED | E_STRICT). If you are running in a stricter error reporting mode, your tests will most likely fail due to strict mode method signature violations. (This is on the known issues / to do list)

pseudo's Issues

addQuery ignores params

When calling mock on a Pdo object I can pass in $params that later get sent to addQuery.
However addQuery does not accept a third argument.

Idiorm Compatability

Have you explored the idea of merging this with the Idiorm ORM?

Problem when trying to test for an empty result on a parameterised query

In order to work around the problem mentioned in #17 I am now manually adding rows to results with addRow so that the Result will become parameterised:

$id = 'dummyId';
$res = new \Pseudo\Result();
$res->addRow([], [':id' => $id]);
$pdo = new \Pseudo\Pdo();
$pdo->mock('SELECT id FROM table WHERE id = :id');
$query = $pdo->prepare('SELECT id FROM table WHERE id = :id');
$query->execute([':id' => $id]);
$this->assertTrue(empty($query->fetchAll(\PDO::FETCH_ASSOC)));

however now it is impossible to test for empty results, as addRow always adds a row to the result, even if it is an empty one.
fetchAll returns an array with an empty array inside, thus making the assert on empty fail.

My suggestion would be to ignore empty rows inside addRow, but still toggle the isParameterized flag when necessary.
However, I used addRow only as a workaround for my problem and I'm not sure if it would make sense to have an addRow that doesn't actually add anything but is called just to set a flag.

Prepared statements execution is not affecting QueryLog

Pseudo\QueryLog instance associated with Pseudo\Pdo is only affected by calling Pseudo\Pdo::query directly. This is why any calls to Pseudo\Pdo::lastInsertId after queries built with Pseudo\Pdo::prepare -> Pseudo\PdoStatement::execute result in errors.

Test case illustrating this problem:

    public function testLastInsertIdPreparedStatement()
    {
        $sql = "SELECT * FROM test WHERE foo='bar'";
        $p = new Pseudo\Pdo();
        $r = new Pseudo\Result();
        $r->setInsertId(10);
        $p->mock($sql, $r);
        $statement = $p->prepare($sql);
        $statement->execute();
        $this->assertEquals(10, $p->lastInsertId());
    }

There was 1 error:

1) PdoTest::testLastInsertIdPreparedStatement
Undefined offset: 0

Declaration of Pseudo\Pdo::prepare() should be compatible with PDO::prepare($statement, $options = NULL)

https://travis-ci.org/rkrx/php-mysql-query-builder/jobs/60594547

Testing whether the code is calling an appropriate SQL statement

Should an empty $expectedResults array be treated as one with non-parameterized results?

A simple use-case, where the only thing I'm concerned about is whether the code runs the correct query:

$pdo = new \Pseudo\Pdo();
$pdo->mock('SELECT id FROM table WHERE id = :id');
$query = $pdo->prepare('SELECT id FROM table WHERE id = :id');
$query->execute([':id' => $someId]);
$this->assertTrue(!empty($query->fetchAll(\PDO::FETCH_ASSOC)));

results in an error:
Pseudo\Exception: Cannot get rows with parameters on a non-parameterized result

The reason being that execute passes $input_parameters to getRows, and the default Result (created when no $expectedResults are passed) does not have its isParameterized attribute set to true. Thus the condition at https://github.com/jimbojsb/pseudo/blob/master/src/Pseudo/Result.php#L49 does not pass and an exception is thrown.

$stmt->rowCount doesn't use actual results

When creating a mocked select result from an array, the rowCount stays at 0. e.g.

$p->mock($query, [['id' => 1],['id' => 2],['id' => 3]]);
$stmt = $p->query($query);
echo $stmt->rowCount(); // Outputs 0, expected 3

Assuming I'm not just doing something wrong, I'd be happy to submit a PR for this, though it'd be helpful to be pointed in the right direction 😃

Prevent warning if we don't have Select

Hi guys,

In this part of code:

pseudo/src/php-sql-parser.php

Line 1321 in 77282ca

foreach ($select as $clause) {

I've added this precaution:

#search to see if the expression matches an alias
if (is_array($select)) {
    foreach ($select as $clause) {
        if (!$clause['alias']) {
            continue;
        }
        if ($clause['alias']['name'] === $parseInfo['expr']) {
            $parseInfo['type'] = 'alias';
        }
    }
}

Because, when i don't have a select clause in my sql request, i had this warning:
Warning: Invalid argument supplied for foreach() ... on line 1321

Thx

Missing if-then-else clause in addRow

It seems to me that the conditional statements in addRow don't cover all the cases, e.g.:

$res = new \Pseudo\Result();
$res->addRow([]);
$res->addRow([], [':id' => $id]);

the first call will add a row, making $this->rows nonempty, the second will then get to this condition which will fail (the Result is not parameterised, but it also has a nonempty rows attribute).
In such cases addRow will neither add anything to the rows, nor will it throw any exception, silently failing to do its job.

missing support for FETCH_CLASS

Pseudo did great job except for when i tried testing FETCH_CLASS. I noticed that there is no support for it in the code. any chance for some help with it?

$this->result->nextRow(); returns null when using parameterised query

use Pseudo\Pdo;
use Pseudo\Result;
$p = new Pdo();
$expectedRows = new Result();
$expectedRows->addRow(
    ['id' => 1231, 'foo' => 'bar'], [':id' => 1231]
);
$p->mock("SELECT * FROM xyz WHERE id = :id", $expectedRows);

$sql = 'SELECT * FROM xyz WHERE id = :id';
$stmt = $conn->prepare($sql);
$stmt->execute([':id' => $listingId]);
return $stmt->fetch(); //returns NULL

this happens because of the following code in PdoStatement

$row = (isset($this->rows[$this->rowOffset])) ? $this->rows[$this->rowOffset] : null;

for paramerterised queries requires another vector of an array to be processed such as follows

$row = $this->rows[$this->stringifyParameterSet($this->params)][$this->rowOffset];