Database Testing
for Fun and Profit

Matthew Turland

(Use arrow keys to navigate between slides.)

Welcome!

"Good way of ensure worst bug is escape in
production is have 100% test coverage."
@DEVOPS_BORAT

There (Will) Be Slides

Publications section of http://matthewturland.com

I Co/Wrote These

http://phparch.com

http://sitepoint.com

I've Contributed to These

http://php.net

http://phpunit.de

http://framework.zend.com

http://phing.info

http://phergie.org

I Work For This Company

• Ask and answer questions
• See real-time visualizations of everyone's answers
• Still in private beta - launching soon

Ask ALL THE QUESTIONS!

Got Tests?

Database Tests != Unit Tests

• Unit tests test code in isolation
• Executing database queries is not isolated
• Tests executing database queries aren't unit tests

The Right Way™

• Unit tests verify interactions between your code and database dependencies (e.g. PDO), not results
• Database tests are a form of integration testing: they verify that queries run by your code do what you think they do

PHPUnit

• De facto standard framework for PHP unit tests
• Lead developer is Sebastian Bergmann
• Hopefully you caught his talk on PHPUnit Best Practices

Database Extension

• Port of JUnit's DbUnit extension for PHPUnit
• Created by Mike Lively
• Has its own PHPUnit manual section

Supported Servers

• MySQL
• PostgreSQL
• Oracle
• SQLite
• IBM DB2^*
• Microsoft SQL Server^*

^* Requires Zend Framework or Doctrine 2

Requirements

• Testing environment must have the PDO extension installed
  • Your code being tested does not need to use PDO
• Database schema and privileges must already exist
  • Database extension tests data changes, not schema changes

Installing the Database Extension

pear channel-discover pear.phpunit.de
pear install phpunit/DbUnit

{
  "require": {
    "phpunit/dbunit": "1.3.*"
  }
}

How Database Tests Work

1. Put the database into a known state
2. Run code that performs database operations
3. Verify that actual and expected states are the same

An Example

1. Make a table empty
2. Run code to insert a record into that table
3. Verify that the table now contains only that record

Starting from a Known State

1. Connect to the database
2. Truncate one or more tables
3. Populate those tables with a given data set

Database Test Case

• The PHPUnit_Extension_Database_TestCase class handles doing some of this for you
• All you have to do is...
  • Extend it
  • Implement a getConnection() method that returns a database connection object
  • Implement a getDataSet() method that returns a data set object used to seed the database before each test

Call Me Maybe

• PHPUnit_Extension_Database_TestCase uses setUp() and tearDown() to seed the database and do cleanup operations
• If you extend it and implement your own setUp() and tearDown() methods, be sure to call the parent implementations or You're Gonna Have a Bad Time™

Getting a Connection

• getConnection() must return an object that implements the PHPUnit_Extensions_Database_DB_IDatabaseConnection interface
• The createDefaultDBConnection() method of the PHPUnit_Extensions_Database_TestCase class accepts a PDO instance and wraps it in an instance of PHPUnit_Extensions_Database_DB_DefaultDatabaseConnection, which implements this interface

class FooTest extends \PHPUnit_Extensions_Database_TestCase {
  protected function getConnection() {
    return $this->createDefaultDBConnection(new PDO('...'));
  }
}

Why Connections Work Like This

• The Database extension implements connections using an approach called design by contract
• This allows third-party connection implementations to be interoperable with the Database extension so long as they implement the same interface

Connection Tips

• To have tests not use PDO, implement the PHPUnit_Extensions_Database_DB_IDatabaseConnection interface in a class and have getConnection() return an instance of that class instead
• Put your getConnection() implementation in a subclass of PHPUnit_Extensions_Database_TestCase and make your test cases extend that subclass to stay DRY
• To limit database connections to one per test case class, store it in a static variable

Reusing Connections

class FooTest extends \PHPUnit_Extensions_Database_TestCase {
  protected $connection;
  protected static $pdo;
  protected function getConnection() {
    if (!$this->connection) {
      if (!self::$pdo) {
        self::$pdo = new PDO('...');
      }
      $this->connection = $this->createDefaultDBConnection(self::$pdo);
    }
    return $this->connection;
  }
}

Data Sets

• A data set is an object representation of a subset of data from a database
• There are three types of data sets:
  • File-based
  • Query-based
  • Derived

File-Based Data Sets

These are used to represent expected results in assertions.

Query-Based Data Sets

These are typically used to represent actual results in assertions.

Derived Data Sets

These are transformations involving data sets of other types.

Getting Data Sets

Entire database:

class FooTest extends \PHPUnit_Extensions_Database_TestCase {
  protected function getDataSet() {
    return $this->createMySQLXMLDataSet('/path/to/mysql.xml');
  }
}

Multiple individual tables as a composite:

class FooTest extends \PHPUnit_Extensions_Database_TestCase {
  protected function getDataSet() {
    $dataset = new PHPUnit_Extensions_Database_DataSet_CompositeDataSet();
    $dataset->addDataSet($this->createMySQLXMLDataSet('/path/to/table1.xml'));
    $dataset->addDataSet($this->createMySQLXMLDataSet('/path/to/table2.xml'));
    return $dataset;
  }
}

Assertions

The Database extension provides database-specific assertions and methods to get data usable for assertions

Putting It All Together

class FooTest extends \PHPUnit_Extensions_Database_TestCase {
  protected function setUp() {
    parent::setUp(); // <-- Call me. No, seriously, CALL ME.
    // ... Initialize self::$pdo with PDO instance if needed ...
    $this->foo = new Foo(self::$pdo);
  }
  public function testInsertWithValidRecord() {
    $this->foo->insert(array('bar' => 'foobar', 'baz' => 'foobaz'));
    $actual = $this->getConnection()->createQueryTable('foo',
      'SELECT * FROM `foo`');
    $expected = $this->getDataSetFromFile('expected')->getTable('foo');
    $this->assertTablesEqual($actual, $expected);
  }
}

Seeding the Database in Other Tests

• Need to seed your database before running Selenium tests?
• PHPUnit_Extensions_Database_DefaultTester encapsulates the logic executed in setUp() and tearDown()
• PHPUnit_Extensions_Database_TestCase is just a convenient test case wrapper around this code

class FooTest extends \PHPUnit_Extensions_SeleniumTestCase {
  protected function setUp() {
    // ... Initialize self::$pdo with PDO instance if needed ...
    $this->tester =
      new \PHPUnit_Extensions_Database_DefaultTester(self::$pdo);
    $this->tester->onSetUp();
  }
  protected function tearDown() {
    $this->tester->onTearDown();
  }
}

Upcoming Development

• Merging pull requests - response time is generally a few days
• Resolving issues - mostly on hold until 2014
• Changes to dbunit.php CLI tool
  • Fixing export-dataset command
  • Adding new features like integration with Faker for data set generation

Feedback

Please rate my talk!

http://joind.in/9955

And check out the joind.in mobile apps!

http://joind.in/help

That's All, Folks

• Questions? Comments? Discussion?
• http://www.blopboard.com
• http://matthewturland.com (slides will be posted here!)
• me@matthewturland.com
• @elazar on Twitter
• Elazar on Freenode
• Go forth and test!