Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Full code for: http://someguyjeremy.com/blog/database-testing-with-phpunit Place fixtures in a `fixture` folder.
<?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();
}
}
<?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));
}
}
<?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>
<?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>
<?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>
@miguelxt

This comment has been minimized.

Copy link

miguelxt commented Apr 1, 2015

Very helpful. I come from a CakePHP background too and this add a little bit of sense to the PHPUnit docs.

@jdunham2

This comment has been minimized.

Copy link

jdunham2 commented Jun 12, 2015

Thank you! I haven't been able to find many start to finish examples. I'll have to spend some time digging through your code to learn a little more about dbunit tests, I would really like to get good at them. Any book suggestions? PHPUnit docs were def confusing...

@bar-yariv

This comment has been minimized.

Copy link

bar-yariv commented Aug 13, 2015

Thank you very much i've been looking for something like that for age's, but when i tried to run the test i got this error:
PHP Fatal error: Call to a member function getConnection() on a non-object in /home/al/adserver/adserver/test/dbTest/FixtureTestCase.php on line 13

why is that? i looked for mocking the getConnection but couldn't really find something...thx

@dustingraham

This comment has been minimized.

Copy link

dustingraham commented Apr 25, 2016

Could you provide the code you used to dump the mysql to xml?

Edit: Nevermind this seems to work mysqldump -u root -p testdb --xml -t

@ridwaanmanga

This comment has been minimized.

Copy link

ridwaanmanga commented Oct 23, 2016

Thank you very much it really help too much but what i want to know is did you load your actual database(real database) then imported to fixture or you are creating dump tables for testing then deleting it and how doesn't effect my real database?
lastly you can tell me **

mysqldump -u root -p testdb --xml -t

** this code what is?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.