Page 1 of 1

Test setup for complex databases

Posted: Sun May 18, 2008 4:05 am
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.

Re: Test setup for complex databases

Posted: Sun May 18, 2008 5:09 am
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.

Re: Test setup for complex databases

Posted: Sun May 18, 2008 6:58 am
by matthijs
Yes indeed. Forgot about that, thanks for the tip

And besides that, something else?

Re: Test setup for complex databases

Posted: Sun May 18, 2008 10:19 am
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.

Re: Test setup for complex databases

Posted: Mon May 19, 2008 1:49 am
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 ;)

Re: Test setup for complex databases

Posted: Mon May 19, 2008 3:32 am
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.

Re: Test setup for complex databases

Posted: Mon May 19, 2008 6:33 am
by Jenk
You could always do a IF EXISTS DROP