Test setup for complex databases

Discussion of testing theory and practice, including methodologies (such as TDD, BDD, DDD, Agile, XP) and software - anything to do with testing goes here. (Formerly "The Testing Side of Development")

Moderator: General Moderators

Post Reply
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Test setup for complex databases

Post by matthijs »

Setting up the tests for my model layer I've come to this:

Alltests.php

Code: Select all

 
<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 1);
ini_set('log_errors', 'Off');
 
require_once dirname(__FILE__) . '/configure.php';
 
class DataStoreTestSuite extends TestSuite {
  public function __construct() {
    parent::__construct('All DataStore tests');
    
    $this->addFile(TEST_BASE . '/unit/DbPdoTest.php');
    $this->addFile(TEST_BASE . '/unit/AreaModelTest.php');
    $this->addFile(TEST_BASE . '/unit/RouteModelTest.php');
    $this->addFile(TEST_BASE . '/unit/RouteClimbModelTest.php');
    
  }
}
 
configure.php:

Code: Select all

 
<?php
define('TEST_BASE', dirname(__FILE__));
define('SIMPLETEST_BASE', TEST_BASE . '/lib/simpletest');
define('CLASS_BASE', TEST_BASE . '/../classes');
require_once SIMPLETEST_BASE . '/unit_tester.php';
require_once SIMPLETEST_BASE . '/autorun.php';
 
define('COUNTRY_MODEL_DLL', <<<EOS
CREATE TABLE `mc_countries` (
  `country_id` int(10) unsigned NOT NULL auto_increment,
  `country_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`country_id`)
) ENGINE=InnoDB;
EOS
);
 
define('COUNTRY_DATA_DLL', <<<EOS
INSERT INTO `mc_countries` VALUES (1, 'Afghanistan');
EOS
);
 
define('AREA_MODEL_DLL', <<<EOS
CREATE TABLE `mc_areas` (
  `area_id` int(10) unsigned NOT NULL auto_increment,
  `country_id` int(10) unsigned NOT NULL,
  `area_name` varchar(255) NOT NULL,
  `area_notes` text NOT NULL,
  PRIMARY KEY  (`area_id`),
  FOREIGN KEY (`country_id`) REFERENCES mc_countries (`country_id`)
) ENGINE=InnoDB;
EOS
);
 
define('ROUTE_MODEL_DLL', <<<EOS
CREATE TABLE `mc_routes` (
  `route_id` int(10) unsigned NOT NULL auto_increment,
  `route_name` varchar(255) NOT NULL,
  `route_grade` enum('3','4','5a','5a+','5b','5b+','5c','5c+','6a','6a+','6b','6b+','6c','6c+','7a','7a+','7b','7b+','7c','7c+','8a','8a+','8b','8b+','8c','8c+','9a','9a+','9b','9b+') NOT NULL,
  `route_notes` text NOT NULL,
  `area_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`route_id`),
  FOREIGN KEY (`area_id`) REFERENCES mc_areas (`area_id`)
)  ENGINE=InnoDB;
EOS
);
 
AreaModeltest.php:

Code: Select all

 
<?php
require_once dirname(__FILE__) . '/../configure.php';
require_once CLASS_BASE . '/AreaModel.php';
require_once CLASS_BASE . '/DbPdo.php';
 
class AreaModelTest extends UnitTestCase {
 
    protected $conn;
 
    function __construct($name=''){
        $this->conn = DbPdo::conn();
    }
    
    public function setup(){
        $this->conn->query('drop table if exists mc_routeclimbs');
        $this->conn->query('drop table if exists mc_routes');
        $this->conn->query('drop table if exists mc_areas');
        $this->conn->query('drop table if exists mc_countries');
        $this->conn->query(COUNTRY_MODEL_DLL);
        $this->conn->query(COUNTRY_DATA_DLL);
        $this->conn->query(AREA_MODEL_DLL);
    }
  
    // test methods ...
}
 
RouteModelTest.php:

Code: Select all

 
<?php
require_once dirname(__FILE__) . '/../configure.php';
require_once CLASS_BASE . '/AreaModel.php';
require_once CLASS_BASE . '/RouteModel.php';
require_once CLASS_BASE . '/DbPdo.php';
 
class RouteModelTest extends UnitTestCase {
 
    protected $conn;
    function __construct($name=''){
        $this->conn = DbPdo::conn();
    }
    public function setup(){
        $this->conn->query('drop table if exists mc_routeclimbs');
        $this->conn->query('drop table if exists mc_routes');
        $this->conn->query('drop table if exists mc_areas');
        $this->conn->query('drop table if exists mc_countries');
        $this->conn->query(COUNTRY_MODEL_DLL);
        $this->conn->query(COUNTRY_DATA_DLL);
        $this->conn->query(AREA_MODEL_DLL);
        $this->conn->query(ROUTE_MODEL_DLL);
        
    }
 
    // test methods ...
 
}
 
RouteClimbModelTest.php:

Code: Select all

<?php
require_once dirname(__FILE__) . '/../configure.php';
require_once CLASS_BASE . '/RouteClimbModel.php';
require_once CLASS_BASE . '/DbPdo.php';
 
class RouteClimbModelTest extends UnitTestCase {
 
    protected $conn;
 
    function __construct($name=''){
        $this->conn = DbPdo::conn();
    }
 
    public function setup(){
        $this->conn->query('drop table if exists mc_routeclimbs');
        $this->conn->query('drop table if exists mc_routes');
        $this->conn->query('drop table if exists mc_areas');
        $this->conn->query('drop table if exists mc_countries');
        $this->conn->query('drop table if exists mc_users');
        $this->conn->query(COUNTRY_MODEL_DLL);
        $this->conn->query(COUNTRY_DATA_DLL);
        $this->conn->query(AREA_MODEL_DLL);
        $this->conn->query(ROUTE_MODEL_DLL);
        $this->conn->query(USERS_DLL);
        $this->conn->query(ROUTECLIMB_MODEL_DLL);
    }
}
 
I'm using simpletest. I know I also could mock the model classes instead of dumping and creating all the tables each time. But this seems easier.

What do you think? It seems to work so far, but maybe there are things I could do different? One thing I see is that there's a lot of duplication in the setup methods.

And also some dependencies, as tables are not deleted after use. And because I use foreign key restraints between the tables, I had to make sure to drop all the tables before creating them. For example, AreaModelTest does not really use the tables mc_routes and mc_routeclimbs, but I still have to dump them in the setup, otherwise I get exceptions due to the foreign key restraints. Maybe it's better to drop the complete database before each test?

Last, I'm sure that as soon as I have a few hundred tests, running the testsuite might take a lot of time. But at the moment that's not my most important worry. I just want to have a good setup before I go on.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Test setup for complex databases

Post by onion2k »

Wouldn't the "DROP TABLE" code be better placed in a teardown() method? Seems more logical to me to return the test state to how it was before the test ran rather than assume it's in the wrong state (with the tables existing) when you start.
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Test setup for complex databases

Post by matthijs »

Yes indeed. Forgot about that, thanks for the tip

And besides that, something else?
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Test setup for complex databases

Post by matthijs »

Actually, it seems better to add the drop table's to the setup method. Because if, for some reason, the database was not empty before running the tests, having the drop tables in a tearDown() method still leads to errors.

Or maybe I should even have them in both places, just to be sure.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: Test setup for complex databases

Post by Chris Corbyn »

matthijs wrote:Actually, it seems better to add the drop table's to the setup method. Because if, for some reason, the database was not empty before running the tests, having the drop tables in a tearDown() method still leads to errors.

Or maybe I should even have them in both places, just to be sure.
Right :) If one of your tests were to cause a fatal error then your tearDown() wouldn't run. This is more of a problem with interpreted languages where a compile error can effectively happen at run time. The logical place to put it is tearDown() however... it just doesn't stand up very well in PHP.

Another solution to CREATE and DROP table is to:

a) TRUNCATE all tables instead
b) BEGIN a transaction in setUp() then ROLLBACK in tearDown() - This makes some assumptions about how transactions are handled within the app though.

Both (a) and (b) are faster then creating and dropping tables ;)
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Test setup for complex databases

Post by matthijs »

Chris Corbyn wrote: Another solution to CREATE and DROP table is to:

a) TRUNCATE all tables instead
b) BEGIN a transaction in setUp() then ROLLBACK in tearDown() - This makes some assumptions about how transactions are handled within the app though.

Both (a) and (b) are faster then creating and dropping tables ;)
Ok, cool, thanks. TRUNCATE sounds good. Not sure about the transactions, as I might use transactions already in the app. And if I'm correct you can't nest transactions.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Re: Test setup for complex databases

Post by Jenk »

You could always do a IF EXISTS DROP
Post Reply