Sample PHPUnit test showing how to test actual queries

gistfile1.php

<?php

require_once '../lib/LieMapper.php';
require_once '../lib/LieEntity.php';
require_once './PDOMock.php';

class LieMapperTest extends PHPUnit_Framework_TestCase
{
    /**
     * @test
     */
    public function returnsLieCollection()
    {
        /**
         * Given I have a collection of expected lies
         * When I create a database connection
         * And pass it to my LieMapper
         * When I execute getAll()
         * Then I get my expected collection of lies
         */
 
        // Create our collection of Lies
        $lieInfo = array(
            array(
                'id' => uniqid(),
                'date' => time(),
                'description' => 'First test lie',
                'user_id' => uniqid(),
                'valid' => 1
            ),
            array(
                'id' => uniqid(),
                'date' => time(),
                'description' => 'Second test lie',
                'user_id' => uniqid(),
                'valid' => 1
            ),
            array(
                'id' => uniqid(),
                'date' => time(),
                'description' => 'Third test lie',
                'user_id' => uniqid(),
                'valid' => 0
            ),
        );

        // Create collection of Lie objects based on array info
        $expectedLies = array();
        $expectedLies[0] = new LieEntity();
        $expectedLies[1] = new LieEntity();
        $expectedLies[2] = new LieEntity();

        foreach ($lieInfo as $idx => $details) {
            $expectedLies[$idx]->id = $details['id'];
            $expectedLies[$idx]->date = $details['date'];
            $expectedLies[$idx]->description = $details['description'];
            $expectedLies[$idx]->user_id = $details['user_id'];
            $expectedLies[$idx]->valid = $details['valid'];
        }

        // Mock out our PDO statement
        $sth = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'fetchAll'))
            ->getMock();
        $sth->expects($this->once())
            ->method('fetchAll')
            ->will($this->returnValue($lieInfo));

        $db = $this->getMockBuilder('PDOMock')
            ->setMethods(array('prepare'))
            ->getMock();
        $db->expects($this->once())
            ->method('prepare')
            ->with($this->stringContains('SELECT id, date, description, user_id, valid FROM'))
            ->will($this->returnValue($sth));

        // create LieMapper, passing it our mocked DB
        $lieMapper = new LieMapper($db);

        // ask it to get all the Lies
        $lies = $lieMapper->getAll();

        // assert that collections are the same
        $this->assertEquals(
            $expectedLies,
            $lies,
            "LieMapper::getAll() did not return expected collection"
        );
    }

    /**
     * @test
     */
    public function getOneRecord()
    {
        // Create our raw Lie info
        $lieInfo = array(
            'id' => uniqid(),
            'date' => time(),
            'description' => 'First test lie',
            'user_id' => uniqid(),
            'valid' => 1
        );

        // Create a LieEntity object based on info
        $expectedLie = new LieEntity();

        foreach ($lieInfo as $key => $value) {
            $expectedLie->{$key} = $value;
        }

        // Mock our PDO statement
        $sth = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'fetch'))
            ->getMock();
        $sth->expects($this->once())
            ->method('fetch')
            ->will($this->returnValue($lieInfo));

        $db = $this->getMockBuilder('PDOMock')
            ->setMethods(array('prepare'))
            ->getMock();
        $db->expects($this->once())
            ->method('prepare')
            ->with($this->stringContains('SELECT id, date, description, user_id, valid FROM lies WHERE id ='))
            ->will($this->returnValue($sth));

        // create LieMapper, passing it our mocked DB
        $lieMapper = new LieMapper($db);

        $lie = $lieMapper->get($lieInfo['id']);

        // assert that collections are the same
        $this->assertEquals(
            $expectedLie,
            $lie,
            "LieMapper::getAll() did not return expected collection"
        );
    }

    /**
     * @test
     */
    public function getCorrectlyHandlesNotFindingLie()
    {
        // Mock our PDO statement
        $sth = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'fetch'))
            ->getMock();
        $sth->expects($this->once())
            ->method('fetch')
            ->will($this->returnValue(false));

        $db = $this->getMockBuilder('PDOMock')
            ->setMethods(array('prepare'))
            ->getMock();
        $db->expects($this->once())
            ->method('prepare')
            ->will($this->returnValue($sth));

        // create LieMapper, passing it our mocked DB
        $lieMapper = new LieMapper($db);
        $response = $lieMapper->get(uniqid());

        $this->assertFalse($response);
    }

    /**
     * @test
     */
    public function getValidLies()
    {
        $lieInfo = array(
            array(
                'id' => uniqid(),
                'date' => time(),
                'description' => 'First test lie',
                'user_id' => uniqid(),
                'valid' => 1
            ),
            array(
                'id' => uniqid(),
                'date' => time(),
                'description' => 'Second test lie',
                'user_id' => uniqid(),
                'valid' => 1
            ),
        );

        // Create collection of Lie objects based on array info
        $expectedLies = array();
        $expectedLies[0] = new LieEntity();
        $expectedLies[1] = new LieEntity();

        foreach ($lieInfo as $idx => $details) {
            $expectedLies[$idx]->id = $details['id'];
            $expectedLies[$idx]->date = $details['date'];
            $expectedLies[$idx]->description = $details['description'];
            $expectedLies[$idx]->user_id = $details['user_id'];
            $expectedLies[$idx]->valid = $details['valid'];
        }

        // Mock our query object
        $sth = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'fetchAll'))
            ->getMock();
        $sth->expects($this->once())
            ->method('fetchAll')
            ->will($this->returnValue($lieInfo));

        $db = $this->getMockBuilder('PDOMock')
            ->setMethods(array('prepare'))
            ->getMock();
        $db->expects($this->once())
            ->method('prepare')
            ->with("SELECT * FROM lies WHERE valid = 1")
            ->will($this->returnValue($sth));

        // create LieMapper, passing it our mocked DB
        $lieMapper = new LieMapper($db);

        // ask it to get all the Lies
        $lies = $lieMapper->getAllValid();

        // assert that collections are the same
        $this->assertEquals(
            $expectedLies,
            $lies,
            "LieMapper::getAll() did not return expected collection"
        );
    }

    /**
     * @test
     */
    public function createNewLie()
    {
        /**
         * Create a new LieEntity in a known state
         * Create a new LieMapper
         * Pass in a mocked DB object
         * Pass the LieEntity to a create() method
         * Verify via get() that our LieEntity matches
         */
        $lieInfo = array(
            'id' => uniqid(),
            'date' => time(),
            'description' => 'First test lie',
            'user_id' => uniqid(),
            'valid' => 1
        );

        // Create a LieEntity based on $lieInfo
        $expectedLie = new LieEntity();

        foreach ($lieInfo as $key => $value) {
            $expectedLie->{$key} = $value;
        }

        $sth = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute'))
            ->getMock();
        $sth->expects($this->once())
            ->method('execute')
            ->will($this->returnValue(true));

        $db = $this->getMockBuilder('PDOMock')
            ->setMethods(array('prepare'))
            ->getMock();
        $db->expects($this->once())
            ->method('prepare')
            ->with($this->stringContains('INSERT INTO lies'))
            ->will($this->returnValue($sth));

        // create LieMapper, passing it our mocked DB
        $lieMapper = new LieMapper($db);
        $response = $lieMapper->create($expectedLie);

        $this->assertTrue($response);
    }

    /**
     * @test
     */
    public function deleteKnownCreatedEntity()
    {
        /**
         * Given a known entity ID
         * Tell me if it was actually deleted
         */

        // Create our raw Lie info
        $lieInfo = array(
            'id' => uniqid(),
            'date' => time(),
            'description' => 'First test lie',
            'user_id' => uniqid(),
            'valid' => 1
        );

        // Create a LieEntity object based on info
        $expectedLie = new LieEntity();

        foreach ($lieInfo as $key => $value) {
            $expectedLie->{$key} = $value;
        }

        // Mock our PDO statement
        $sth = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'fetch'))
            ->getMock();
        $sth->expects($this->once())
            ->method('fetch')
            ->will($this->returnValue($lieInfo));

        $sth2 = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'rowCount'))
            ->getMock();
        $sth2->expects($this->once())
            ->method('rowCount')
            ->will($this->returnValue(1));

        $db = $this->getMockBuilder('PDOMock')
            ->setMethods(array('prepare'))
            ->getMock();
        $db->expects($this->at(0))
            ->method('prepare')
            ->with($this->stringContains('SELECT id, date, description, user_id, valid FROM lies WHERE id ='))
            ->will($this->returnValue($sth));
        $db->expects($this->at(1))
            ->method('prepare')
            ->with($this->stringContains('DELETE FROM lies WHERE id ='))
            ->will($this->returnValue($sth2));

        $lieMapper = new LieMapper($db);
        $response = $lieMapper->delete($expectedLie->id);

        $this->assertTrue(
            $response,
            "Deleted expected row"
        );
    }

    /**
     * @test
     */
    public function deleteCorrectlyHandlesNullLieEntityId()
    {
        $lieMapper = new LieMapper(new stdClass);
        $response = $lieMapper->delete(null);

        $this->assertFalse($response);
    }

    /**
     * @test
     */
    public function deleteHandlesNoDeletedRowsCorrectly()
    {
        // Create our raw Lie info
        $lieInfo = array(
            'id' => uniqid(),
            'date' => time(),
            'description' => 'First test lie',
            'user_id' => uniqid(),
            'valid' => 1
        );

        // Create a LieEntity object based on info
        $expectedLie = new LieEntity();

        foreach ($lieInfo as $key => $value) {
            $expectedLie->{$key} = $value;
        }

        // Mock our PDO statement
        $sth = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'fetch'))
            ->getMock();
        $sth->expects($this->once())
            ->method('fetch')
            ->will($this->returnValue($lieInfo));

        $sth2 = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'rowCount'))
            ->getMock();
        $sth2->expects($this->once())
            ->method('execute')
            ->will($this->returnValue(true));
        $sth2->expects($this->once())
            ->method('rowCount')
            ->will($this->returnValue(0));

        $db = $this->getMockBuilder('PDOMock')
            ->setMethods(array('prepare'))
            ->getMock();
        $db->expects($this->at(0))
            ->method('prepare')
            ->with($this->stringContains('SELECT id, date, description, user_id, valid FROM lies WHERE id ='))
            ->will($this->returnValue($sth));
        $db->expects($this->at(1))
            ->method('prepare')
            ->with($this->stringContains('DELETE FROM lies WHERE id ='))
            ->will($this->returnValue($sth2));

        $lieMapper = new LieMapper($db);
        $response = $lieMapper->delete(uniqid());

        $this->assertFalse($response);
    }

    /**
     * @test
     */
    public function deleteHandlesMissingEntityCorrectly()
    {
        /**
         * Create an entity ID
         * Mock a response from the database that returns false
         * pass mocked DB into LieMapper
         * assert delete() returned false
         */
        $lieEntityId = uniqid();

        $sth = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'fetch'))
            ->getMock();
        $sth->expects($this->once())
            ->method('fetch')
            ->will($this->returnValue(false));
        $db = $this->getMockBuilder('PDOMock')
            ->setMethods(array('prepare'))
            ->getMock();
        $db->expects($this->once())
            ->method('prepare')
            ->with($this->stringContains('SELECT id, date, description, user_id, valid FROM lies WHERE id ='))
            ->will($this->returnValue($sth));

        $lieMapper = new LieMapper($db);
        $this->assertFalse(
            $lieMapper->delete($lieEntityId),
            "LieMapper::delete() did not handle case where row for entity was not found"
        );
    }

    public function updateDataProvider()
    {
        return array(
            array(1, true),
            array(12, false)
        );
    }

    /**
     * @test
     * @dataProvider updateDataProvider
     * @param integer $rowCount
     * @param boolean $expectedResponse
     */
    public function updateKnownEntity($rowCount, $expectedResponse)
    {
        $lieInfo = array(
            'id' => uniqid(),
            'date' => time(),
            'description' => 'Lie to update',
            'user_id' => uniqid(),
            'valid' => 1 
        );

        $lie = new LieEntity();

        foreach ($lieInfo as $field => $value) {
            $lie->{$field} = $value;
        }

        // Mock our PDO statement
        $sth = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'fetch'))
            ->getMock();
        $sth->expects($this->once())
            ->method('fetch')
            ->will($this->returnValue($lieInfo));

        $sth2 = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'rowCount'))
            ->getMock();
        $sth2->expects($this->once())
            ->method('execute')
            ->will($this->returnValue(true));
        $sth2->expects($this->once())
            ->method('rowCount')
            ->will($this->returnValue($rowCount));

        $db = $this->getMockBuilder('PDOMock')
            ->setMethods(array('prepare'))
            ->getMock();
        $db->expects($this->at(0))
            ->method('prepare')
            ->with($this->stringContains('SELECT id, date, description, user_id, valid FROM lies WHERE id ='))
            ->will($this->returnValue($sth));
        $db->expects($this->at(1))
            ->method('prepare')
            ->with($this->stringContains('UPDATE lies'))
            ->will($this->returnValue($sth2));

        $lieMapper = new LieMapper($db);
        $response = $lieMapper->update($lie);

        $this->assertEquals(
            $expectedResponse,
            $response,
            "Did not get expected response from LieMapper::update()"
        );
    }

    /**
     * @test
     */
    public function updateCorrectlyHandlesBadLieEntity()
    {
        $lie = new LieEntity();
        $lieMapper = new LieMapper(new stdClass);
        $response = $lieMapper->update($lie);
        $this->assertFalse($response);
    }

    /**
     * @test
     */
    public function updateCorrectlyHandlesRejectingWrongLieUpdate()
    {
        /**
         * Create a lie entity
         * Mock query response to return false
         * pass DB object into LieMapper
         * assert that we get back false response
         */
        $lie = new LieEntity();
        $lie->id = uniqid();

        $sth = $this->getMockBuilder('stdClass')
            ->setMethods(array('execute', 'fetch'))
            ->getMock();
        $sth->expects($this->once())
            ->method('fetch')
            ->will($this->returnValue(false));

        $db = $this->getMockBuilder('PDOMock')
            ->setMethods(array('prepare'))
            ->getMock();
        $db->expects($this->once())
            ->method('prepare')
            ->with($this->stringContains('SELECT id, date, description, user_id, valid FROM lies WHERE id ='))
            ->will($this->returnValue($sth));

        $lieMapper = new LieMapper($db);

        $this->assertFalse(
            $lieMapper->update($lie),
            "LieMapper::update() did not reject attempt to update non-existant entity"
        );
    }
}