FixtureTestCase.php

<?php
// we're loading the Database TestCase here
require 'PHPUnit' . DIRECTORY_SEPARATOR . 'Extensions' .
DIRECTORY_SEPARATOR . 'Database'  . DIRECTORY_SEPARATOR .
'TestCase.php';

class FixtureTestCase extends PHPUnit_Extensions_Database_TestCase {

  public $fixtures = array(
		'posts',
		'postmeta',
		'options'
	);

	private $conn = null;

	public function setUp() {
		$conn = $this->getConnection();
		$pdo = $conn->getConnection();

		// set up tables
		$fixtureDataSet = $this->getDataSet($this->fixtures);
		foreach ($fixtureDataSet->getTableNames() as $table) {
			// drop table
			$pdo->exec("DROP TABLE IF EXISTS `$table`;");
			// recreate table
			$meta = $fixtureDataSet->getTableMetaData($table);
			$create = "CREATE TABLE IF NOT EXISTS `$table` ";
			$cols = array();
			foreach ($meta->getColumns() as $col) {
				$cols[] = "`$col` VARCHAR(200)";
			}
			$create .= '('.implode(',', $cols).');';
			$pdo->exec($create);
		}

		parent::setUp();
	}

	public function tearDown() {
		$allTables =
		$this->getDataSet($this->fixtures)->getTableNames();
		foreach ($allTables as $table) {
			// drop table
			$conn = $this->getConnection();
			$pdo = $conn->getConnection();
			$pdo->exec("DROP TABLE IF EXISTS `$table`;");
		}

		parent::tearDown();
	}

	public function getConnection() {
		if ($this->conn === null) {
			try {
				$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
				$this->conn = $this->createDefaultDBConnection($pdo, 'test');
			} catch (PDOException $e) {
				echo $e->getMessage();
			}
		}
		return $this->conn;
	}

	public function getDataSet($fixtures = array()) {
		if (empty($fixtures)) {
			$fixtures = $this->fixtures;
		}
		$compositeDs = new
		PHPUnit_Extensions_Database_DataSet_CompositeDataSet(array());
		$fixturePath = dirname(__FILE__) . DIRECTORY_SEPARATOR . 'fixtures';

		foreach ($fixtures as $fixture) {
			$path =  $fixturePath . DIRECTORY_SEPARATOR . "$fixture.xml";
			$ds = $this->createMySQLXMLDataSet($path);
			$compositeDs->addDataSet($ds);
		}
		return $compositeDs;
	}

	public function loadDataSet($dataSet) {
		// set the new dataset
		$this->getDatabaseTester()->setDataSet($dataSet);
		// call setUp whateverhich adds the rows
		$this->getDatabaseTester()->onSetUp();
	}
}

MyTestCase.php

<?php
require 'FixtureTestCase.php';

class MyTestCase extends FixtureTestCase {

  public $fixtures = array(
		'posts',
		'postmeta',
		'options'
	);

	function testReadDatabase() {
		$conn = $this->getConnection()->getConnection();
		
		// fixtures auto loaded, let's read some data
		$query = $conn->query('SELECT * FROM posts');
		$results = $query->fetchAll(PDO::FETCH_COLUMN);
		$this->assertEquals(2, count($results));

		// now delete them
		$conn->query('TRUNCATE posts');

		$query = $conn->query('SELECT * FROM posts');
		$results = $query->fetchAll(PDO::FETCH_COLUMN);
		$this->assertEquals(0, count($results));

		// now reload them
		$ds = $this->getDataSet(array('posts'));
		$this->loadDataSet($ds);

		$query = $conn->query('SELECT * FROM posts');
		$results = $query->fetchAll(PDO::FETCH_COLUMN);
		$this->assertEquals(2, count($results));
	}

}

options.xml

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="test">
  <table_data name="prefix_options">
		<row>
			<field name="option_id">1</field>
			<field name="option_name">siteurl</field>
			<field name="option_value">http://wordpress.local</field>
		</row>
		<row>
			<field name="option_id">2</field>
			<field name="option_name">home</field>
			<field name="option_value">http://wordpress.local</field>
		</row>
	</table_data>
</database>
</mysqldump>

postmeta.xml

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="test">
  <table_data name="postmeta">
		<row>
			<field name="meta_id">1</field>
			<field name="post_id">1</field>
			<field name="meta_key">Something</field>
			<field name="meta_value">For Nothing</field>
		</row>
		<row>
			<field name="meta_id">2</field>
			<field name="post_id">2</field>
			<field name="meta_key">MetaKey</field>
			<field name="meta_value">MetaValue</field>
		</row>
		<row>
			<field name="meta_id">3</field>
			<field name="post_id">1000</field>
			<field name="meta_key">More</field>
			<field name="meta_value">Value</field>
		</row>
		<row>
			<field name="meta_id">4</field>
			<field name="post_id">1001</field>
			<field name="meta_key">Even More</field>
			<field name="meta_value">Value</field>
		</row>
	</table_data>
</database>
</mysqldump>

posts.xml

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="test">
  <table_data name="posts">
		<row>
			<field name="ID">1</field>
			<field name="post_content">Welcome to WordPress. This is your first post. Edit or delete it, then start blogging!</field>
			<field name="post_title">Hello world!</field>
			<field name="post_name">hello-world</field>
			<field name="guid">http://wordpress.local/?p=1</field>
		</row>
		<row>
			<field name="ID">2</field>
			<field name="post_content">This is an example of a WordPress page, you could edit this to put information about yourself or your site so readers know where you are coming from. You can create as many pages like this one or sub-pages as you like and manage all of your content inside of WordPress.</field>
			<field name="post_title">About</field>
			<field name="post_name">about</field>
			<field name="guid">http://wordpress.local/?page_id=2</field>
		</row>
	</table_data>
</database>
</mysqldump>