Tag: phpunit

Database Unit Testing

When I first looked into unit testing, the concept seemed simple enough.  The canonical example seemed to be the sum function.

public function sum($x, $y)
    return $x + $y;

And the test.

public function testSum()
    $summer = new Summer();
    $this->assertEquals(7, $summer->sum(3, 4));
    // and after awhile, trying to break the 
    // function turned out to be part of the fun.
    $this->assertEquals(4, $summer->sum(7, -3));
    $this->assertEquals(4, $summer->sum(4, null));

In any case, this was nice and all for arithmetic, but like in any typical web app, I soon ran into the big problem: how do I test the database?

Now, there is a chapter on database testing in the PHPUnit manual with DBUnit for PHP.  But as I read about those, I saw a lot of documentation about syntax and handling of null values and empty tables. I wondered why I couldn’t just create a real database and test it with real queries?

Enter SQLite

SQLite is a self-contained, serverless, zero-configuration, ACID-compliant RDBMS.  Since SQLite requires no setup, and runs completely in memory, it makes the perfect database to run self-contained, serverless, automated unit tests against.

Database Access Abstraction To The Rescue

Being able to test seamlessly with a SQLite database in my unit tests and run against a MySQL database in live code required some careful thought about how I went about setting up my classes.  First of all, I created a database access interface with some standard functions like query(), getRows(), startTransaction(), commit(), etc.  (You are using a database access class, aren’t you?)  Then, I implemented the interface with a MySQL implementation, and an SQLite implementation.

Since there are places in the code that takes advantage of MySQL specific features, I had to emulate these features in my SQLite driver.  For example, there was no lpad() function in SQLite.  Fortunately, thanks to the magic of createFunction(), I could create a SQLite User-Defined Function.

public function lpad($value, $numchars, $char)
    return str_pad($value, $numchars, $char, STR_PAD_LEFT);
$sqlite->createFunction('lpad', 'lpad', 3);

Some of the MySQL bells and whistles are of course missing, such as ON DUPLICATE KEY UPDATE. With some fancy query re-writing, I got those MySQL queries working with SQLite.  Here is my current SQLite database access class in its entirety.

Dependency Injection FTW!

Now, that I had a MySQL database driver, and an equivalent SQLite database driver, I needed a way to get my business objects to use the SQLite version in a test, and the MySQL version during real execution.  If only there was a pattern that let me “inject” the proper database dependency into the object.  Well, this is exactly what Dependency Injection is!

Instead of instantiating a database driver within the class, I passed the database object in the constructor.  There are other ways to pass in the object, such as with a setter method, or with a global registry, but I like constructor the best.  So the class might look like this:

class Customer
    private $_db;
    public function __construct($db)
        $this->_db = $db;

Writing The Test

With the database access classes in place set to be injected into my classes, I could test functions that persisted data to the database, and then actually inspect the database.  There was no XML data files.  There was no database testing DSL.  There was only plain ol’ SQL.

Here is a simplified function that saves fictional customer data, and a test for it.

public function save($firstName, $lastName)
    $this->_db->query("INSERT INTO customers (first_name, last_name) VALUES (?, ?)",
        array($firstName, $lastName));
    return $this->_db->lastInsertId();

And the test.

class CustomerTest extends PHPUnit_Framework_TestCase
    private $_db;
    public function setUp()
        $this->_db = new Test_Database;
        $this->_db->query("CREATE TABLE customers (first_name, last_name_);");
    public function tearDown()
        $this->_db->query("DROP TABLE customers");
    public function testSave()
        $customer = new Customer($this->_db);
        $customer->save("Mickey", "Mouse");
        // test that customer is saved to DB
        $results = $this->_db->getRows("SELECT * FROM customers");
        $this->assertEquals("Mickey", $results[0]['first_name']);
        $this->assertEquals("Mouse", $results[0]['last_name']);
        $this->assertEquals(1, count($results));

One Last Thing

This method could get to be overkill in certain situations as you write tons of SQL to set up your tests.  But even then I find it preferable to setting up XML or CSV data.  As far as mocking out a database with some sort of Mock Object, I still maintain, why use a mock and a DSL when you can use the real thing?  This has worked well for me so far.

This past Thursday I gave a short talk at the monthly OINK-PUG meeting about how I discovered the Test-Driven Development Kool-Aid®.  I knew about TDD years before I actually started doing it.  I tried to start several times, and never kept it going.  About a year ago, while developing a web service with no screens, the light bulb went on and gave me the idea for this talk.

In which I give a short introduction to unit testing, and then outline the story that finally turned on the light bulb about real test-driven development and test first for me. The third part of the talk gives an introduction to Dependency Injection (DI), an integral part to unit testing.

If we are to do unit testing, then it would be nice to have a simple way to run all the tests at once.  And seeing the results in a command line is fine, but it would be nice to be able to generate some pretty reports I could view in a browser.  And running coverage tools from the command line to see exactly what we’re testing is fine, but it would be nice to be able to generate coverage reports I could view in a browser. It would be nice to be able to do all these things with one simple command.

The command: phing.

If you’re familiar with Ant for Java, phing is functionally identical to ant.  With a very simple build script, I can run all the unit tests, and generate test result reports and coverage reports in HTML automatically. Then I can fire up my browser and see if any tests failed, or whether we need new tests.

The following are examples of test results reports and coverage reports.

Unit test results

Code coverage report

Phing currently requires PHPUnit for unit testing and xdebug for coverage. Someone would have to write a task to use other frameworks such as SimpleTest.

The phing build file is a first step towards automating builds with a build server. A build server can periodically check the project for changes. If new code exists, it will run tests and generate reports for tests, coverage, lint, anything. It can put all this on the web for easy inspection. It can keep versions of the whole project that be can be readily deployed.  So if we have a staging and production server, and we stick to deploying ONLY bundles from the build server, it can make deployment and maintenance easier.