Automated Testing and Database

Many examples of elementary and intermediate levels in unit testing in any language show how easy it is to check the logic of your applications with the help of unit tests. However, not everything is so simple when testing applications in which the database plays a central role, and it is these that are the majority of web applications. Those who are engaged in unit testing of their applications, I think, have repeatedly encountered the problem of testing a database. Almost 2 years ago , an article on this topic was already on Habré, but I would like to reveal it more.

Dbunit

So, DbUnit. It was originally developed for JUnit (a framework for unit testing Java applications) for a database setup before running tests. As a result, the extension developed and migrated to other xUnit frameworks, in particular to PHPUnit. Currently supported MySql, PostgreSql, Oracle and Sqlite.

Why DbUnit?

To test the interaction of your application with the database, you need to additionally perform the following steps:

• Consider the structure of the database and tables
• Explore the insertion of the original data, if necessary
• Conduct a database state check after performing any business logic operations.
• Clear the base and repeat again for each test (otherwise the subsequent tests will depend on the previous ones)

If you write this manually using SQL queries, then pretty soon you start cursing unit testing in principle. In addition, it does not correspond to one of the main principles of unit testing - tests should be minimally complex and as readable as possible.

In order

So, how should the base interaction test pass?

1. Clearing the base . When you first start we do not know what state the database is in, so we must “start from scratch”;
2. Insert the initial data (fixture) . Usually, the application needs any initial data that it extracts from the database for further processing. they must be inserted into the newly cleaned base;
3. Actually performing tests and checking the results . No comments.

PHPUnit Database Test Case

If in the case of a normal test case in PHPUnit you just have to inherit the PHPUnit_Framework_TestCase class, then in the case of testing a database, everything is somewhat more complicated:

require_once "PHPUnit/Extensions/Database/TestCase.php"; class MyTest extends PHPUnit_Extensions_Database_TestCase { public function getConnection() { $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'root', ''); return $this->createDefaultDBConnection($pdo, 'testdb'); } public function getDataSet() { return $this->createFlatXMLDataSet(dirname(__FILE__).'/_files/guestbook-init.xml'); } } 

')
You must implement two abstract methods, getConnection () and getDataSet (). The first is necessary for establishing a connection with the database, the second for filling the database with tables and filling in the actual tables.
It is important to note that getConnection () must use the PDO to connect to the database, but your application does not have to use the PDO to query the database. The connection established by the getConnection () method is used only for preparing the database for tests and assertions.
The initial database content is abstracted using the PHPUnit_Extensions_Database_DataSet_IDataSet and PHPUnit_Extensions_Database_DataSet_IDataTable interfaces. The getDataSet () method is called by the setUp () method to get and insert fixtures. In the example, we used the createFlatXMLDataSet () factory method to get the dataset from the XML representation.

DataTables & DataSets

So what is it? These are key concepts of the extension in question. DataTable and DataSet is an abstraction for tables and records in a real database. A fairly simple mechanism allows you to hide the real database behind objects, which in turn can be implemented in various ways.
Such an abstraction is necessary to compare the expected content of the base and real. The expected content can be presented in various forms due to abstraction - for example, XML, CSV, PHP arrays. The DataTable and DataSet interfaces allow comparing data from the expected source with the real ones from the database.
Also, the DataSet and DataTable are used to set the initial state of the database before running the test.
Below we will consider various options for datasets.

Flat XML DataSet

This is the most simple type of dataset. Each element inside the root represents one record from the database. The element name must correspond to the table name, and the attributes and values ​​- the fields and field values, respectively, for example:

 <?xml version="1.0" encoding="UTF-8" ?> <dataset> <post post_id="1" title="My First Post" date_created="2008-12-01 12:30:29" contents="This is my first post" rating="5" /> <post post_id="2" title="My Second Post" date_created="2008-12-04 15:35:25" contents="This is my second post" /> </dataset> 

This is equivalent to a post table in a DB with 2 entries.

post_id	title	date_created	contents	rating
one	My first post	2008-12-01 12:30:29	This is my first post	five
2	My second post	2008-12-04 15:35:25	This is my second post	Null

In general, quite simple and clear.
An empty table is equivalent to an empty element, for example, an empty current_visitors table:

 <?xml version="1.0" encoding="UTF-8" ?> <dataset> <current_visitors /> </dataset> 

NULL values ​​for the record are represented as the absence of the corresponding attribute (see the blog example, the rating field), however, one point should be taken into account. For flat XML DataSet, the table structure is determined by the first element, i.e. if there are no attributes in the first element, and they are in the subsequent elements for the same table, then these attributes will be ignored. For example, if in the example with the blog table, the date_created attribute with its value is removed from the first element, then this attribute will not be taken into account in the second element and the date_created field will not be in the table.
Using the createFlatXmlDataSet () method:

 class MyTestCase extends PHPUnit_Extensions_Database_TestCase { public function getDataSet() { return $this->createFlatXmlDataSet('myFlatXmlFixture.xml'); } } 

XML DataSet

This version of the representation in XML has no Flat XML flaws, but is also somewhat more complicated:

 <?xml version="1.0" encoding="UTF-8" ?> <dataset> <table name="post"> <column>post_id</column> <column>title</column> <column>date_created</column> <column>contents</column> <column>rating</column> <row> <value>1</value> <value>My First Post</value> <value>2008-12-01 12:30:29</value> <value>This is my first post</value> <value>5</value> </row> <row> <value>2</value> <value>My Second Post</value> <value>2008-12-04 15:35:25</value> <value>This is my second post</value> <null /> </row> </table> </dataset> 

A table is fully represented by a <table> element, in which <column> is nested to define the fields of the table and <row> to represent the records. In turn, <value> can be nested with <value> to represent meaningful fields and <null /> for NULL values.

An empty table is represented as a table without <row> elements:

 <?xml version="1.0" encoding="UTF-8" ?> <dataset> <table name="current_visitors"> <column>current_visitors_id</column> <column>ip</column> </table> </dataset> 

Using the createXMLDataSet () method:

 class MyTestCase extends PHPUnit_Extensions_Database_TestCase { public function getDataSet() { return $this->createXMLDataSet('myFlatXmlFixture.xml'); } } 

CSV Data Set

Presentation of a table in CSV format (Comma Separated Values ​​is the simplest format for storing tables). Everything is pretty clear:
post_id,title,date_created,contents,rating
1,My First Post,2008-12-01 12:30:29,This is my first post,5
2,My Second Post,2008-12-04 15:35:25,This is my second post,

The use is somewhat more complicated than with XML:

 class MyTestCase extends PHPUnit_Extensions_Database_TestCase { public function getDataSet() { $dataSet = new PHPUnit_Extensions_Database_DataSet_CsvDataSet(); $dataSet->addTable('post', 'post.csv'); return $dataSet; } } 

To use it, we need to create an object of the PHPUnit_Extensions_Database_DataSet_CsvDataSet class. The constructor takes three arguments that define the CSV format:

 public function __construct($delimiter = ',', $enclosure = '"', $escape = '"'){} 

After that we add tables to the addTable method - one file - one table.

PHP arrays

Currently, there is no standard implementation of datasets using arrays, but it is easy to implement;)

Suppose that we need to store datasets in this format:

 array( 'post' => array( array( 'post_id' => 1, 'title' => 'My First Post', 'date_created' => '2008-12-01 12:30:29', 'contents' => 'This is my first post', 'rating' => 5 ), array( 'post_id' => 2, 'title' => 'My Second Post', 'date_created' => '2008-12-04 15:35:25', 'contents' => 'This is my second post', 'rating' => null ), ), ) 

Implementation:

 require_once 'PHPUnit/Extensions/Database/DataSet/AbstractDataSet.php'; require_once 'PHPUnit/Extensions/Database/DataSet/DefaultTableIterator.php'; require_once 'PHPUnit/Extensions/Database/DataSet/DefaultTable.php'; require_once 'PHPUnit/Extensions/Database/DataSet/DefaultTableMetaData.php'; class ArrayDataSet extends PHPUnit_Extensions_Database_DataSet_AbstractDataSet { protected $tables = array(); public function __construct(array $data) { foreach ($data as $tableName => $rows) { $columns = array(); if (isset($rows[0])) { $columns = array_keys($rows[0]); } $metaData = new PHPUnit_Extensions_Database_DataSet_DefaultTableMetaData($tableName, $columns); $table = new PHPUnit_Extensions_Database_DataSet_DefaultTable($metaData); foreach ($rows as $row) { $table->addRow($row); } $this->tables[$tableName] = $table; } } protected function createIterator($reverse = FALSE) { return new PHPUnit_Extensions_Database_DataSet_DefaultTableIterator($this->tables, $reverse); } } 

Few comments - for our dataset, we inherit abstract datasets (which flat XML, XML, CSV and others inherit from). In the constructor, we pass the previously specified array. As in the case of flat XML, the table structure is determined by the first record, but in this case it is not critical, because we have the ability to explicitly specify NULL values. The structure, by the way, is determined by creating a PHPUnit_Extensions_Database_DataSet_DefaultTableMetaData object. After that we create the actual table, passing the structure to it and adding the records to the table using the addRow () method. We also need to implement the abstract createIterator method, but there is nothing complicated about it :)

Using:

 class MyTestCase extends PHPUnit_Extensions_Database_TestCase { public function getDataSet() { return new ArrayDataSet(array( 'post' => array( array( 'post_id' => 1, 'title' => 'My First Post', 'date_created' => '2008-12-01 12:30:29', 'contents' => 'This is my first post', 'rating' => 5 ), array( 'post_id' => 2, 'title' => 'My Second Post', 'date_created' => '2008-12-04 15:35:25', 'contents' => 'This is my second post', 'rating' => null ), ), )); } } 

Query / Database Dataset

For assertions, we will need not only the expected datasets, but also real ones from the database. QueryDataSet will help us with this.

 $ds = new PHPUnit_Extensions_Database_DataSet_QueryDataSet($this->getConnection()); $ds->addTable('post'); 

or with explicit use of the query:

 $ds->addTable('post', 'SELECT * FROM post ORDER BY post_id'); 

You can also use an existing connection to automatically retrieve datasets from existing tables using the PHPUnit_Extensions_Database_DB_DefaultDatabaseConnection :: createDataSet () method (this is an object created in getConnection ()). If you do not pass the parameter to createDataSet (), then the dataset will be created from all existing tables. If you pass an array with the names of tables in the database as a parameter, then only data from these tables will be created.

Replacement DataSet

I have already mentioned the problem of NULL values ​​for flat XML datasets (for CSV, the problem is the same - it is impossible to explicitly set a NULL value in a fixture). This can be solved with the help of a special decorator - ReplacementDataSet:

 require_once 'PHPUnit/Extensions/Database/DataSet/ReplacementDataSet.php'; class MyTestCase extends PHPUnit_Extensions_Database_TestCase { public function getDataSet() { $ds = $this->createFlatXmlDataSet('myFlatXmlFixture.xml'); $rds = new PHPUnit_Extensions_Database_DataSet_ReplacementDataSet($ds); $rds->addFullReplacement('##NULL##', null); return $rds; } } 

Now we can use ## NULL ## in XML to denote NULL values:

 <?xml version="1.0" encoding="UTF-8" ?> <dataset> <post post_id="1" title="My First Post" date_created="2008-12-01 12:30:29" contents="This is my first post" rating="5" /> <post post_id="2" title="My Second Post" date_created="2008-12-04 15:35:25" contents="This is my second post" rating="##NULL##" /> </dataset> 

Dataset filtering

For large datasets, you can apply filtering with the DataSetFilter:

 require_once 'PHPUnit/Extensions/Database/DataSet/ReplacementDataSet.php'; class MyTestCase extends PHPUnit_Extensions_Database_TestCase { public function testIncludeFilteredPost() { $dataSet = $this->getConnection()->createDataSet(); $filterDataSet = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($dataSet); $filterDataSet->addIncludeTables(array('post')); $filterDataSet->setIncludeColumnsForTable('post', array('post_id', 'title')); // .. } public function testExcludeFilteredPost() { $dataSet = $this->getConnection()->createDataSet(); $filterDataSet = new PHPUnit_Extensions_Database_DataSet_DataSetFilter($dataSet); $filterDataSet->addExcludeTables(array('foo', 'bar', 'baz')); $filterDataSet->setExcludeColumnsForTable('post', array('date_created', 'rating')); // .. } } 

In the first case, we left only the post table and the contents of its records for the post_id and title fields in the dataset. In the second, we have excluded the tables 'foo', 'bar' and 'baz' from the dataset, and removed the values ​​for the 'date_created' and 'rating' fields from the post table entries.

Dataset composition

We can connect several datasets into one. If the datasets have the same tables, then the records will be added to them, for example:
dataset-1.xml

 <?xml version="1.0" encoding="UTF-8" ?> <dataset> <post post_id="1" title="My First Post" date_created="2008-12-01 12:30:29" contents="This is my first post" rating="5" /> </dataset> 

dataset-2.xml

 <?xml version="1.0" encoding="UTF-8" ?> <dataset> <post post_id="2" title="My Second Post" date_created="2008-12-04 15:35:25" contents="This is my second post" /> </dataset> 

We aggregate them:

 class MyTestCase extends PHPUnit_Extensions_Database_TestCase { public function getDataSet() { $ds1 = $this->createFlatXmlDataSet('dataset-1.xml'); $ds2 = $this->createFlatXmlDataSet('dataset-2.xml'); $compositeDs = new PHPUnit_Extensions_Database_DataSet_CompositeDataSet(); $compositeDs->addDataSet($ds1); $compositeDs->addDataSet($ds2); return $compositeDs; } } 

Asserts

It is often necessary to check the number of records in a table. This can be done using the usual assertEquals:

 class MyTestCase extends PHPUnit_Extensions_Database_TestCase { public function testAddEntry() { $this->assertEquals(2, $this->getConnection()->getRowCount('post')); $blog = new Blog(); $blog->addPost("My third post.", "This is my third post."); $this->assertEquals(3, $this->getConnection()->getRowCount('post')); } } 

The getRowCount () method returns the number of records in the specified table.

To compare the tables, use the assertTablesEqual () method:

 class MyTestCase extends PHPUnit_Extensions_Database_TestCase { public function testTables() { $queryTable = $this->getConnection()->createQueryTable('post', 'SELECT * FROM post'); $expectedTable = $this->createFlatXmlDataSet("myFlatXmlFixture.xml")->getTable("post"); $this->assertTablesEqual($expectedTable, $queryTable); } } 

It must be remembered that the test can fail when checking the date - if you have a fixed date in the fixture, and the current time is recorded in the base, you will receive a file if these dates do not match. Therefore, dates are often removed from the expected result and, accordingly, change the receipt of a real dataset:

 $queryTable = $this->getConnection()->createQueryTable('post', 'SELECT post_id, title, date_created, contents, rating FROM post'); 

Finally, you can directly compare datasets with assertDataSetsEqual ():

 class MyTestCase extends PHPUnit_Extensions_Database_TestCase { public function testTables() { $dataSet = $this->getConnection()->createDataSet(array('post')); $expectedDataSet = $this->createFlatXmlDataSet("myFlatXmlFixture.xml"); $this->assertDataSetsEqual($expectedDataSet, $dataSet); } } 

The article is largely written based on the Benjamin Eberlei article “The Ultimate Guide to Database-Testing with PHPUnit” , and of course the official manual .