PHP Unit. Testing experience of classes working with the Database

Whether or not to test classes interacting with the Database is a much more holistic question than the debate “whether to cover the 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 unequivocally decided: "to be tested."
Next, I want to share my experience in writing unit tests for code that works with a database.

PHPUnit contains an extension for testing the database. The extension performs the following functions:
  • transferring the database to a previously known state,
  • performing the necessary data modifications,
  • checking that the expected records are created in the database.

Unfortunately, this feature lacks one feature we really need - restoring data to the state it was in before the tests. I want to offer 2 solutions to this problem. I will also dwell on the problem of foreign keys, which often arises when rolling incomplete test data onto a “working” database.

So, I offer 2 options for solving the problem: how to return the database to its original state after conducting a unit test:
The first way is “Transactional”. The essence of which is to complete the entire test within a single transaction, and the subsequent rollback of the transaction.
Second - Before executing the test, copy the structure of the “working” base and conduct testing on it.


The implementation of the "transactional" path



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

The task boils down to the use of a single database connection by all tests and the conclusion of all database operations in a single transaction. Encapsulate the connection to the database in a separate class from which all Unit tests will be inherited. 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 link to the database connection. I draw attention to the fact that this method should not create a new connection to the database each time, but should only return a link to the created connection.
The following code is not valid, because the 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, at each call to the method, database connections will be recreated. Take out the creation of the connection object in the constructor, and the getConnection method will return a link 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 will dwell on the moment of disabling foreign keys: before running the tests, DBUnit cleans the database by sending truncate to each table. A situation is often encountered, the code is cleared for the table referenced by the data in the table that has not yet been cleared, thereby blocking data cleansing to avoid this, we disable foreign key checking for the duration of the test.

SET foreign_key_checks = 0

We also made it possible for tests to execute queries to the database via PDO ($ this-> pdo-> query ())

Now the thing is small: roll back the transaction after the test is completed within one test case:
 
/**
 * Деструктор
 *
 * Откатывает транзакцию, чтобы изменения не отражались на боевой базе
 */
function __destruct()
{
    $this->pdo->rollBack();
}

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

We abandon the truncate operation as follows:
After digging into the internals of DBUnit, 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()
    ));
}

which 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 TRUNCATE function with DELETE_ALL. In the bowels of PHPUnit to do such a bad manners. Fortunately, you can override this behavior in an 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()
            )
        );
    }
}


Did everyone guess that the “parent for tests” class should inherit from TrunsactionFiendlyDatabaseTestCase ()? Or to combine 2 of these classes, it is 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 presented in the diagram: The
image

“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 data operations within one test are executed in a transaction, which is rolled back after the test is completed.

Implementation of copying a database structure


The idea floats to the surface: get another database for tests. But in this case, both databases will have to be kept up to date. And a situation can easily happen when the tests pass with success, but the system does not work on a “combat” base.
Let's try to automate the process of copying the database structure before running the tests. DbUnit will do the rest perfectly.

Obviously, the base must be copied before the first test case.
PHPUnit allows you to run a “bootstrap file,” aka bootstrap, before running tests. It is set in the phpunit.xml settings file as follows:

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

Let’s take a look at the steps of cloning a database structure.

First, delete the test database, if suddenly it is already created

DROP DATABASE IF EXISTS `testDB`

and create it again

CREATE DATABASE `testDB`

Of course, you can not delete the database, if it is already available, but simply clean it, but in this case there may be a mismatch between the structure of the test base and the working, simply put, 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 tables in the test database:

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

Discarding the excess, we get approximately 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 the structure of the test database, which is "identical to the natural one".
It remains to remember to set DbUnit on the test database. No standard DbUnit behavior needs to be redefined.

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

Transactionally, surprisingly, it works quite quickly, even though the transaction has to be rolled back after each test, and not the entire test case.
But how much it will turn out to be fast when launching many test cases is a mystery to me. Because The total execution time of this auxiliary operation grows in direct proportion to the number of tests. And with the work of test dependencies, they also depend, 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 user zim32
Also, this method imposes restrictions on database queries generated by the test. In particular, the transaction must be completed before using the expressions: ALTER TABLE, CREATE INDEX, DROP INDEX, DROP TABLE, RENAME TABLE.

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

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

Conclusion: When running single test cases, you should use the "transactional" approach. When performing a large number of test cases, you should prefer the option of cloning the database structure.

I would like to hear what problems you encountered when using DbUnit and how to solve them.

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

Also popular now: