Using a test database

From Achievo/ATK Wiki

Contents 1 Preface 2 How it works 3 Configuration 4 Creating a simple test-case 5 Creating fixtures 6 Using fixtures in your test-case 7 Dynamic fixtures 8 Summary

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.
   */
  public 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.
   */
  public function tearDown()
  {
    parent::tearDown();
  }
 
  /**
   * Test if there is only one employee in the database.
   */
  public 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.
   */
  public 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/ subdirectory 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.

Using fixtures in your test-case

Once we've created some fixtures we can use them in our test-case. As you can see in the following example we don't need to manually insert employees anymore.

class test_example2 extends atkTestCase  
{
  /**
   * Inside the constructor we register the fixtures we
   * want to use inside this test-case. 
   */
  public function __construct()
  {
    // We prefix the fixture with the module name so ATK knows where to look for 
    // the fixture (e.g. modules/<module>/testcases/fixtures/<fixture>.yml). You
    // can also use a full ATK class path, so you can even reference fixtures that 
    // are not placed inside a module.
    $this->addFixture('example.employee');
 
    // You can optionally specify the database for which this fixture should be 
    // used. You should specify the name of your development/production database,
    // *not* your test database. This way it's easier to switch test databases.
    $this->addFixture('example.department', 'default');
 
    // The order in which you register fixtures is important. This is the same
    // order as they will be inserted in the database. So if you are using foreign
    // keys in your tables then records that are referenced by others records
    // should be inserted first.
    $this->addFixture('example.department_employee', 'default');
  }
 
  /**
   * Test if there are only two employees in the database.
   */
  public function test_employee_count()
  {
    $db = atkGetDb();
    list($row) = $db->getRows("SELECT COUNT(*) AS quantity FROM employee");
 
    $this->assertNotNull($row);
    $this->assertEqual(2, $row['quantity']);
  }
 
  /**
   * Test if we can add a new employee and if it's newly assigned id is larger
   * then the largest id we've used inside your fixtures file.
   */
  public function test_insertion_of_employee()
  {
    // We use the employee node to add the employee because it will automatically
    // take care of setting the id column to the next sequence value.
    $node = atkGetNode('example.employee');
 
    $record = $node->initial_values();
    $record['firstname'] = 'Nobody';
    $record['lastname'] = 'Nobody';
    $record['email'] = 'nobody@example.com';
 
    $this->assertEqual(2, $node->select()->rowCount());
    $this->assertTrue($node->addDb($record));
    $this->assertFalse(isset($record['atkerror']));
    $this->assertEqual(3, $node->select()->rowCount());
    $this->assertTrue($record['id'] > 2);
  } 
 
  /**
   * Test if we can select an employee that has been added using a fixture.
   */
  public function test_selection_of_employee()
  {
    // We first retrieve the in-memory representation of the employee 
    // using the magic employee method. If you use PHP5 you can always
    // retrieve a fixture record using a method which is named the same
    // as your fixtures file with as parameter the name of the fixture
    // you want to retrieve. This will return an in-memory representation
    // of the fixture, so the database won't be accessed. If you are using
    // PHP4 you can retrieve the fixture record using the special fixture 
    // method, e.g. $this->fixture('employee', 'John Doe');
    $employee = $this->employee('John Doe')
 
    $this->assertNotNull($employee);
    $this->assertEqual('John', $employee['firstname']);
 
    // Use the node to retrieve the employee row.
    $node = atkGetNode('example.employee');
    $selector = sprintf("employee.email = '%s'", $node->getDb()->escapeSQL($employee['email']));
    $row = $node->select($selector)->getFirstRow();
 
    $this->assertNotNull($row);
    $this->assertEqual($employee['id'], $row['id']);
    $this->assertEqual($employee['firstname'], $row['firstname']);
    $this->assertEqual($employee['lastname'], $row['lastname']);
    $this->assertNotNull($row['email']);
  }
}

Dynamic fixtures

If you look at the example above, fixtures might look pretty static. Luckily they aren't. You can, for example, use a simple for loop from inside your fixtures file to dynamically create a bunch of fixture records or you can set a value for a certain column dynamically. You can not only do this inside PHP fixtures, you can also use PHP code in a YAML fixtures file. For example:

John Doe:
  id: 1
  firstname: John
  lastname: Doe
  email: john.doe@example.com
  created_at: <?php echo date('Y-m-d'); ?>
<?php
  for ($i = 2; $i <= 100; $i++)
  {
?>
Employee <?php echo $i; ?>:
  id: <?php echo $i; ?>
  firstname: Employee <?php echo $i; ?>
  lastname: Dummy
  email: dummy<?php echo $i; ?>@example.com
  created_at: <?php echo date('Y-m-d'); ?>
<?php 
  }
?>

Inside your PHP code you have all of ATK at your disposal, so you can use the complete library just like your normally would. Using PHP inside your fixtures is supported out of the box, so you don't need to do anything special.

Summary

This howto has given you an overview of ATK's built-in test database support. Using a test database gives you a powerful way of testing your code without messing up your development or, even worse, production database.

Fixtures are a way to ease the insertion of test data in your test database. Using fixtures you can quickly create a (large) set of test data which can be used inside your test-cases. Because fixtures are separated from your test-cases you can reuse them in different test-cases.

Because ATK makes sure before each test method your test database is cleaned-up and all fixtured registered for your test-case are (re-)inserted in the database you always know exactly what data is available in your test database.