PHP Unit. Experience of testing classes working with the Database

To test whether or not classes interacting with the Database is a question much more holivarny than the dispute "to cover code with tests or not." After reviewing your project, it became obvious to us that the lion’s share of classes is based on interaction with the database. Therefore, it was definitely decided: "testing to be."
Next, I want to share the experience of writing unit tests for code that works with the database.

PHPUnit contains an extension to test the database. The extension performs the following functions:

• transfer of the database to a previously known state,
• performing the necessary data modifications,
• check that the expected entries are created in the database.

Unfortunately, there is no one feature we really need in this list - data recovery to the state in which they were before the tests. I want to offer 2 options for solving this problem. I also dwell on the problem of foreign keys, which often arises when rolling in incomplete test data on a “working” database.

So, I offer 2 options for solving the problem: how to restore the database to its initial state after conducting a unit test:
The first path is “Transactional”. The essence of which is reduced to the execution of the entire test in a single transaction, and the subsequent rollback transaction.
Second - Before performing the test, copy the structure of the “working” base and test it.

')

Implementation of the "transactional" path

In fact, we need to enclose in a transaction all operations with the database, which Unit performs the test, and subsequently roll it back.

The task is reduced to the use of a single connection to the database by all tests and the conclusion of all operations with the database in a single transaction. We encapsulate the connection to the database in a separate class from which all Unit tests will inherit. The class itself will be a descendant of PHPUnit_Extensions_Database_TestCase.

Using the DBUnit extension, you should override the getConnection method, which, as the name implies, is responsible for obtaining a reference to the connection to the database. I pay attention that this method should not create a new connection to the database every time, but should only return a link to the created connection.
The following code is not allowed, because transactions “live” only within one connection.

public function getConnection() { $pdo = new PDO("mysql:host=localhost;dbname=dbname", 'root', 'password'); return $this->createDefaultDBConnection($pdo, 'php'); } 

In this case, with each call to the method, connections to the database will be recreated. We post the creation of the connection object in the constructor, and the getConnection method will return a reference to the connection object:

 /** *  PDO connection    *  DefaultDBConnection  DBUnit    PDO * */ public function __construct($name = null, array $data = array(), $dataName = '') { parent::__construct($name, $data, $dataName); $this->pdo = new PDO("mysql:host=localhost;dbname=dbname", 'root', 'password'); $this->pdo->exec('SET foreign_key_checks = 0'); //    $this->con = $this->createDefaultDBConnection($this->pdo, 'php'); $this->pdo->beginTransaction(); } /** *  PDO * * @return PHPUnit_Extensions_Database_DB_DefaultDatabaseConnection */ public function getConnection() { return $this->con; } 

I’ll stay at the moment when foreign keys are disabled: before executing tests, DBUnit clears the database, sending truncate to each table. Often there is a situation, the code clears the table, which is referenced by data in a table that has not yet been cleared, thereby blocking data cleansing to avoid this, disable the checking of foreign keys for the duration of the test.

 SET foreign_key_checks = 0 

We also allowed tests to perform queries to the database via PDO ($ this-> pdo-> query ())

Now it’s easy to do: roll back the transaction after running the test in one test case:

 /** *  * *  ,        */ function __destruct() { $this->pdo->rollBack(); } 

The code looks quite working, but 2 pitfalls remain:
1) the transaction is aborted when the Truncate operation is performed, which is performed before each filling of the test data with the dbUnit extension.
2) If your DBMS is MySQL, then the long execution time for one transaction is affected by the error: “Lock wait timeout exceeded; try restarting transaction. ”The bug is described in the MySQL bug tracker .

We abandon the truncate operation as follows:
Having rummaged in DBUnit interiors we find the CLEAN_INSERT method in the PHPUnit_Extensions_Database_Operation_Factory class:

 /** * Returns a clean insert database operation. It will remove all contents * from the table prior to re-inserting rows. * * @param bool $cascadeTruncates Set to true to force truncates to cascade on databases that support this. * @return PHPUnit_Extensions_Database_Operation_IDatabaseOperation */ public static function CLEAN_INSERT($cascadeTruncates = FALSE) { return new PHPUnit_Extensions_Database_Operation_Composite(array( self::TRUNCATE($cascadeTruncates), self::INSERT() )); } 

It is called from PHPUnit_Framework_TestCase to clear the database

 protected function getSetUpOperation() { return PHPUnit_Extensions_Database_Operation_Factory::CLEAN_INSERT(); } 

All that is needed is to replace the function TRUNCATE with DELETE_ALL. In the depths of PHPUnit do this moveton. The benefit of redefining this behavior is possible in the inherited class:

 abstract class TrunsactionFiendlyDatabaseTestCase extends PHPUnit_Extensions_Database_TestCase { /** * Returns the database operation executed in test setup. * Return DeleteAll and Insert After this. * * @return PHPUnit_Extensions_Database_Operation_DatabaseOperation */ protected function getSetUpOperation() { return new PHPUnit_Extensions_Database_Operation_Composite( array ( PHPUnit_Extensions_Database_Operation_Factory::DELETE_ALL(), PHPUnit_Extensions_Database_Operation_Factory::INSERT() ) ); } } 

Everyone guessed that the “test-parent” class should be inherited from TrunsactionFiendlyDatabaseTestCase ()? Or combine these two classes, here a matter of taste and moral principles. But I preferred not to mix 2 levels of logic in one class. The resulting class hierarchy is shown in the diagram:


The “Lock wait timeout exceeded” problem was solved by rolling back the transaction after each test and starting a new one.

 /** *     ,   */ public function tearDown() { $this->pdo->rollBack(); $this->pdo->beginTransaction(); } 

As a result : all operations with data within one test are executed in a transaction, which is rolled back after completion of the test.

Implementation of copying the database structure

The idea floats on the surface: create another database for tests. But in this case it is necessary to keep both databases up to date. And a situation can easily happen when tests are passed with success, but the system does not work on a “combat” base.
We will try to automate the process of copying the database structure before running the tests. DbUnit will do the rest.

It is obvious that the base must be copied before the first test case is executed.
PHPUnit allows you to perform a “bootstrap file”, also known as bootstrap, before performing tests. It is set in the phpunit.xml settings file as follows:

 <phpunit bootstrap="./application/bootstrap.php" > 

It can perform many useful functions: connecting files, initializing environment variables. I propose in it to initiate the process of cloning the structure of the database.

Let us step by step the process of cloning the database structure.

To begin, remove the test database, if suddenly it has already been created

 DROP DATABASE IF EXISTS `testDB` 

and create it again

 CREATE DATABASE `testDB` 

Of course, you can not delete the database, if it already exists, but simply clear it, but in this case there may be a mismatch between the structure of the test base and the working one, simply speaking, the test base may become outdated.

Next we get a list of tables of the working base.

 SHOW TABLES FROM `developDB` 

and create in their image and likeness of the table in the test database:

 CREATE TABLE `$table` LIKE `developDB`.`$table`" 

Discarding too much, we get about the following code:

 $res = $this->pdo->exec("DROP DATABASE IF EXISTS `testDB`"); $res = $this->pdo->exec("CREATE DATABASE `testDB`"); $tables = $this->pdo->query("SHOW TABLES FROM `developDB`"); $tables = $tables->fetchAll(PDO::FETCH_COLUMN, 0); foreach ($tables as $table) { $res = $this->pdo->exec("CREATE TABLE `$table ` LIKE `developDB`.`$table`"); } 

At this stage, we have a test database structure that is “identical to the natural”
It remains to remember to set DbUnit on the test base. No standard DbUnit behavior is required.

I will try to consider the pros and cons of both approaches.

Transactional work surprisingly quickly, even though the transaction has to be rolled back after each test, and not the entire test case.
But how long it will turn out to be when running many test cases, it remains a mystery to me. Because The total execution time of this auxiliary operation grows in direct proportion to the number of tests. Yes, and with the work of dependencies of tests, they also depends, there will be problems.
The complexity of the algorithm can be represented as O (n), where n is the number of tests.
UPD . Thanks to zim32
Also this method imposes restrictions on database queries generated by the test. In particular, the transaction must be completed before the use of expressions: ALTER TABLE, CREATE INDEX, DROP INDEX, DROP TABLE, RENAME TABLE.

The variant with copying the structure of the database, on the contrary, requires a lot of time for its execution, but it is performed once. Those. Its running time does not depend on the number of tests run.

The complexity of this algorithm = const. How much this constant is large depends on the number of tables and their structure. For example, I can say that on 40 tables the cloning of the structure takes about 8 seconds.

Conclusion : When launching single test cases, you should use the “transactional” approach. When executing a large number of test cases, you should prefer the variant with cloning the database structure.

I would like to hear what problems you encountered while using DbUnit and how you solved them.

In preparing the material was used source: www.smartyit.ru/php