Automated Testing and Databases

    Many examples of elementary and intermediate levels of unit testing in any language show how simple it is to check the logic of your applications using unit tests. However, not everything is so simple when testing applications in which the central role is played by the database, namely most of these among web applications. Those who are engaged in unit testing of their applications, I think, have repeatedly faced the problem of testing the database. Almost 2 years ago there was already an article on this subject 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 setting up a database before running tests. As a result, the extension developed and migrated to other xUnit frameworks, in particular to PHPUnit. MySql, PostgreSql, Oracle and Sqlite are currently supported.

    Why DbUnit?


    To test the interaction of your application with the database, you need to additionally perform the following steps:
    • Take into account the structure of the database and tables
    • Fill in the insertion of the original data, if necessary
    • Perform a DB status check after performing any business logic operations
    • Clear the database and repeat again for each test (otherwise subsequent tests will depend on previous ones)

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

    In order


    So, how should the test of interaction with the database pass correctly?
    1. Base cleaning . At the first start, we do not know what state the database is in, so we are obligated to “start from scratch”;
    2. Insert initial data (fixtures) . Typically, an application needs some initial data that it retrieves from the database for further processing. they must be inserted into the just cleaned base;
    3. Actually performing tests and checking the results . No comments.

    PHPUnit Database Test Case


    If in the case of a regular test case in PHPUnit you just need to inherit the PHPUnit_Framework_TestCase class, then in the case of database testing, 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 to establish a connection with the database, the second to fill the database with tables and fill the actual tables.
    It is important to note that getConnection () must use PDO to connect to the database, but your application is not required to use PDO to query the database. The connection established by the getConnection () method is used only to prepare the database for tests and assertions.
    The initial contents of the database are abstracted using the interfaces PHPUnit_Extensions_Database_DataSet_IDataSet and PHPUnit_Extensions_Database_DataSet_IDataTable. 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 the key concepts of the extension in question. DataTable and DataSet is an abstraction for tables and records in a real database. A rather 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 database and the real one. The expected content can be represented in various forms due to abstraction - for example, XML, CSV, PHP arrays. Interfaces DataTable and DataSet allow you to compare data from the source expected with real from the database.
    Also, DataSet and DataTable are used to set the initial state of the database before running the test.
    Below we consider various options for datasets.

    Flat XML DataSet


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

    This is equivalent to a post table in a 2-entry database
    post_idtitledate_createdcontentsrating
    1My first post2008-12-01 12:30:29This is my first post5
    2My second post2008-12-04 15:35:25This is my second postNull

    In general, it's pretty simple and straightforward.
    An empty table is equivalent to an empty element, for example, an empty table current_visitors:

    NULL values ​​for the entry are represented as the absence of the corresponding attribute (see the example with blog, rating field), however, one thing should be taken into account here. For flat XML DataSet, the structure of the table is determined by the first element, i.e. if in the first element there are no attributes, and in subsequent elements for the same table they are, 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 with the createFlatXmlDataSet () method:
    class MyTestCase extends PHPUnit_Extensions_Database_TestCase
    {
        public function getDataSet()
        {
            return $this->createFlatXmlDataSet('myFlatXmlFixture.xml');
        }
    }
    


    XML DataSet


    This version of XML is free from the disadvantages of Flat XML, but it is also somewhat more complicated:
    post_idtitledate_createdcontentsrating1My First Post2008-12-01 12:30:29This is my first post52My Second Post2008-12-04 15:35:25This is my second post

    The table is completely represented by the element in which to define table fields and to represent records. In turn in can be nested to represent meaningful fields and for null values.

    An empty table appears as a table without items:
    current_visitors_idip

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


    CSV Data Set


    Presentation of the table in CSV format (Comma Separated Values ​​- the simplest format for storing tables). Everything is pretty clear: Using is somewhat more complicated than with XML:
    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,


    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, we need to create an object of class PHPUnit_Extensions_Database_DataSet_CsvDataSet. The constructor takes three arguments that define the CSV format:
    public function __construct($delimiter = ',', $enclosure = '"', $escape = '"'){}
    

    After that, add the tables to the dataset using the addTable method - one file - one table.

    Php arrays


    At the moment, there is no standard implementation of datasets using arrays, but it is not difficult to implement it;)

    Suppose 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);
        }
    }
    

    A few comments - for our dataset we inherit an abstract dataset (which flat XML, XML, CSV and others inherit from). In the constructor, we pass the previously agreed 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 can explicitly specify NULL values. The structure, by the way, is determined by creating the PHPUnit_Extensions_Database_DataSet_DefaultTableMetaData object. After that, we create the table itself, passing the structure to it and add records to the table using the addRow () method. We also need to implement the createIterator abstract method, but there is nothing complicated :)

    Use:
    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 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 using explicit request:
    $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 a 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 the dataset will be created only from these tables.

    Replacement DataSet


    I already mentioned the problem of NULL values ​​for flat XML dataset (for CSV the problem is the same - it is impossible to explicitly set a NULL value in the fixture). This can be solved using 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 indicate a NULL value:


    Data Filtering


    For large datasets, you can apply filtering using 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 in the dataset and the contents of its records only for the post_id and title fields. In the second, we 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.

    Composition of datasets


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

    dataset-2.xml

    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 entries in a table. This can be done using regular 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 tables, the assertTablesEqual () method is used:
    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 set date in the fixture and the current time is written to the database, you will receive a file if these dates do not match. Therefore, dates are often removed from the expected result and, accordingly, the receipt of a real dataset is changed:
    $queryTable = $this->getConnection()->createQueryTable('post', 'SELECT post_id, title, date_created, contents, rating FROM post');
    

    Finally, you can directly compare datasets using 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 on the basis of Benjamin Eberlei's article “The Ultimate Guide to Database-Testing with PHPUnit” , and of course the official manual .

    Also popular now: