Using a test database

ATK Howto: Using a test database Complexity: Intermediate Author: Peter C. Verhage <peter@ibuildings.nl>

List of other Howto's

Preface

ATK has built-in test support for a while now. But until now this didn't include support for adding, updating and/or removing test data in such a way that the development/production database isn't littered with test data. You can make use of the mock database class, but then you can't really test if the query you wrote actually works the way it's supposed to work. And you can ofcourse remove the test data in your test by writing the necessary code to do so, but this means a lot of extra work.

Fortunately there's a third option, you can make use of ATK's built-in test database support. This feature has been heavily inspired by Ruby On Rails (http://www.rubyonrails.org) test database support and makes setting up test databases a breeze.

How it works

When the ATK test-suite starts it first clones the development/production database structure in a test database. Only the structure will be cloned, so you end up with an empty test database. After this the test-suite will start running the test-cases for your application.

Each test-case can consist of one or more test methods. Before each test method is called so called fixtures will be loaded into the database. Fixtures are a way of organizing your test data. They are a way of describing the records for your test database. After each test method has run the data will be rollbacked (or deleted depending on the capabilities of your database). Not only the data of the fixtures will be rollbacked, but also data that's been added inside the test method. This way you are in full control of the data inside the test database.

Configuration

You first need to configure a test database. You can do so by creating an entry called "test" in the $config_db section of the ATK configuration file. It might look like the following:

$config_db["test"]["driver"]                 = "mysqli";
$config_db["test"]["host"]                   = "localhost";
$config_db["test"]["db"]                     = "demo_test";
$config_db["test"]["user"]                   = "user";
$config_db["test"]["password"]               = "password"; 
$config_db["test"]["transactional_fixtures"] = true; 
$config_db["test"]["clone_structure"]        = true;

Nothing out of the ordinary, except for the "transactional_fixtures" and "clone_structure" configuration options. This "transactional_fixtures" option makes it possible to specify if your database supports transactions or not. This way the system knows it can clean-up the database using a simple rollback instead of having to delete all the data. If you don't specify this option ATK assumes by default that your database doesn't support transactions.

The second option, "clone_structure", controls whatever ATK should copy the database structure of your development/production database to your test database. If you set this option to true you will always have a test database which is up-to-date with the development/production database. Unfortunately, at this time, only the table structure is cloned. This means you will loose all foreign keys, constraints, views, triggers etc. If you don't want this or if you want total control over your test database it's best to set this option to false. If you don't specify this option a default of true is assumed.

If you use more then one database in your application you can specify which test database maps to which development/production database using the $config_test_db_mapping configuration option. For example:

$config_test_db_mapping = array('default' => 'test', 'shop' => 'shop_test');

By default this configuration option has the following mapping:

$config_test_db_mapping = array('default' => 'test');

So if you only have a single database you can simply configure a database named "test" and ATK will automatically know how to use it.

Warning: make sure that you really specify your test database for the "db" option or you might end up with an empty development or, even worse, an empty production database!

Creating a simple test-case

This is an example of a test-case using the test database. The test-case should be placed inside the testcases/ subdirectory of your module (just like any other unit test). As you can see we manually insert an employee record in this example and do some tests on it.

class test_example1 extends atkTestCase  
{
  /**
   * Before each test method the setUp method will be called.
   * You can use this method to add some data to the database
   * that needs to be available in each test method.
   */
  function setUp()
  {
    parent::setUp();  
      
    $query = "INSERT INTO employee (id firstname, lastname, email) 
              VALUES(1, 'John', 'Doe', 'john.doe@example.com')";

    $db = atkGetDb();      
    $db->query($sql);
  }
  
  /**
   * After each test method tearDown method will be called.
   * You can use this method to clean-up the database. Fortunately
   * ATK will do this automatically for you if you have specified
   * a test database in your configuration file. So we can simply
   * call the parent tearDown method, or even better no implement
   * this method at all.
   */
  function tearDown()
  {
    parent::tearDown();
  }
 
  /**
   * Test if there is only one employee in the database.
   */
  function test_employee_count()
  {
    $db = atkGetDb();
    list($row) = $db->getRows("SELECT COUNT(*) AS quantity FROM employee");

    $this->assertNotNull($row);
    $this->assertEqual(1, $row['quantity']);
  }

  /**
   * Test if we can find our newly inserted employee.
   */
  function test_employee_can_be_found()
  {
    $db = atkGetDb();
    list($row) = $db->getRows("SELECT * FROM employee e WHERE e.lastname = 'Doe'");

    $this->assertNotNull($row);
    $this->assertEqual('John', $row['firstname']);
    $this->assertEqual('Doe', $row['lastname']);
  }
}

Creating fixtures

Fixtures are a way to ease the insertion of test data in your test database. A fixtures file can contain zero or more records. The records will be literally added to the test database (so they won't be added using the addDb function of a node). This means that even sequence fields need to be assigned values. The reason for this is that this way it's much easier to create relationships between records, because you always know the exact key of the record(s) you want to reference. If you assign a value to a column that is normally filled using a sequence ATK will also make sure that the sequence will be set to the right value afterwards. This means you don't need to add records to the db_sequence table (if you are using MySQL or MySQLi) or set the sequence manually (if you are using Oracle, PostgreSQL etc.).

Fixtures are stored in files. At this time there are two kinds of fixtures ATK supports. First of all ATK supports YAML (http://www.yaml.org) fixtures and secondly ATK supports PHP fixtures. For each table in your database you can create a seperate fixtures file. Depending on the file extension (.yml or .php) ATK will automatically know how to process the fixtures file.

Fixtures can also be assigned a name, this makes it possible to retrieve the fixture data by name inside your test-case. In the future ATK might also support other kinds of fixtures, for example CSV (http://en.wikipedia.org/wiki/Comma-separated_values) fixtures.

Fixtures are placed in the testcases/fixtures/ directory of your module. The fixture gets the exact same name as the table in which you want to insert rows. E.g. if you want to fill a table named "employee", you create a fixtures file named "employee.yml", which may look like the following:

John Doe:
 id: 1
 firstname: John
 lastname: Doe
 email: john.doe@example.com

Lisa Doe:
 id: 2
 firstname: Lisa
 lastname: Doe
 email: lisa.doe@example.com

To use this fixture you have to register the fixture from within the constructor of your test-case. If you do so ATK will make sure that the fixture is loaded in your test database before each test method is called. Ofcourse it's also removed from your test database after the test method has been called, just like any other data that has been added to the database from within your test method or your custom setUp method.

Always make sure that if you override the setUp and/or tearDown methods of your test-case that you also call the parent method. If you don't ATK will not be able to insert the fixtures in your test database or clean-up afterwards.