Automated Testing With Database

Introduction

Testing code that uses a database connection to interact with the database requires some additional preparations. This is caused by the fact, that in order to ensure reproducible results, you need the exact same dataset each and every time you run a test. Luckily, Joomla's testing suite has a few helpers in place that helps us with these task.

A Side Note

If your goal is to write a real unit test - so you want to test one single piece of code without relying on any dependencies - it might be a better approach to mock JDatabase and therefore avoid a real database connection altogether. This is possible because JFactory uses a public static property to store the current database instance and therefore can be overwritten within your test to inject your mock object. Whoever, there are situations where mocking JDatabase requires to much code and effort and you have to rely on a real connection.

Testing an Example Method

Let's assume we want to test this method:

class Foo
{
	public function bar($from, $limit = null)
	{
		$db = JFactory::getDbo();

		$query = $db->getQuery(true);
		$query->select(array('id', 'title'))
			->from($db->quoteName($from));

		$db->setQuery($query, 0, $limit);

		return $db->loadObjectList();
	}
}

It returns a list of rows from a database table, that can be passed in the first argument and limits the amount of rows returned with an optional parameter passed to it.

Now, let's assume we want to write a test that checks if the limit is applied correctly. To do so, we need to:

• set up a connection to a test database
• insert our test data
• run the test
• delete our test data again to have clean environment

Now the good news: this comes out of box in the Joomla testing suite! Here's our example test:

class FooTest extends TestCaseDatabase()
{
	public function testBarAppliesLimit()
	{
		$object = new Foo;

		$this->assertCount(3, $object->bar('#__dbtest', 3));
	}
}

So, what's happening here? By extending our test class from TestCaseDatabase we inherit it's default behavior that includes:

• set up a connection to an in-memory sqlite database
• insert test data defined as XML in tests/unit/stubs/database.xml into a table called jos_dbtest for every test executed
• purges the data from the database and closes the connection after every test

So, without any additional code, we can run tests against this default dataset.

Working with tables not included in the default dataset

But what to do when it's not possible to define which table is used in a query and so the default dataset isn't enough? Take this method for example:

class Foo
{
	public function bar($limit = null)
	{
		$db = JFactory::getDbo();

		$query = $db->getQuery(true);
		$query->select(array('id', 'title'))
			->from('#__categories');

		$db->setQuery($query, 0, $limit);

		return $db->loadObjectList();
	}
}

It has a hardcoded depency for the categories table and therefore, we have to create this table and insert a test dataset. To do so, we only have to slightly modify our test class:

class FooTest extends TestCaseDatabase()
{
	protected function getDataSet()
	{
		$dataSet = new PHPUnit_Extensions_Database_DataSet_CsvDataSet(',', "'", '\\');

		$dataSet->addTable('jos_categories', JPATH_TEST_DATABASE . '/jos_categories.csv');

		return $dataSet;
	}

	public function testBarAppliesLimit()
	{
		$object = new Foo;

		$this->assertCount(3, $object->bar(3));
	}
}

By overriding the getDataSet method, we can add a table and sample data for jos_categories. In our current test suite, test data is available for all core tables. These are defined as CSV files in the directory tests/unit/stubs/database/.

Troubleshooting

In case you have problems with a dataset, that isn't inserted into the database, make sure that you, if you have overriden the setUp() method in your test class, call parent::setUp() because otherwise the code to populate the dataset, isn't called.