Automated Testing With Database

Introduction

Testing code that uses a database connection to interact with the database requires some additional preparations. In order to ensure reproducible results, you need the same dataset every time you run a test. Luckily, Joomla's testing suite has helpers in place that aids with these tasks.

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. However, there are situations where mocking JDatabase requires too 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 number 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 its default behavior that includes:

• setting up a connection to an in-memory SQLite database
• inserting test data defined as XML in tests/unit/stubs/database.xml into a table called jos_dbtest for every test executed
• purging the data from the database and closing the connection after every test

Without additional code, we can run tests against this default dataset.

Working with Tables Not Included in the Default Dataset

What shall we 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 hard-coded dependency 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 overridden the setUp() method in your test class, call parent::setUp(). Otherwise the code to populate the dataset isn't called.