Setting up Database testing with Kohana UnitTest module

Kohana Unittest module has made testing in Kohana v3 easy and fun. Lysender wrote a cool, straight forward post about how to set it up and running. I’ll just talk here about how I have set up database testing with Kohana Unittest for my personal use. Not saying “How to set up”. Because it’s obviously not the best way to do this.

The important thing I need to ensure for testing database is, It’s in a known state before running a test. And, changes on database made by one test will not impact on another test. So, I’ll have a separate database for testing. Which will be loaded with a predefined dataset before running each test and will be truncated after test is done. Also, I have to be careful about that, my models are executing quires on test database in testing environment. Here is how I’ve done it? –

• Created a database with the same structure of project database. lets say it “project_test”. Add it’s configuration to database module as instance name ‘test’.
• Created 2 files named setup.sql and teardown.sql in Kohana’s application/tests/data directory. Wrote this template in both of these files.

  [sql]
  START TRANSACTION;
  SET FOREIGN_KEY_CHECKS=0;

  — My SQL here —

  SET FOREIGN_KEY_CHECKS=1;
  COMMIT;
  [/sql]

• In setup.sql, wrote the sql for inserting some logical, related data that will be used as my “known” data state. Yes, of course by exporting data project database and altering. And, in teardown.sql, wrote sql for truncating all tables. (Though the recommended way is using DbUnit Extension and loading dataset from XML.)
• Added a function to Kohana_Unittest_TestCase class –
  [php]
  public function runSchema($schema)
  {
  $testDb = Kohana::config(‘database.test’);

  if(is_null($testDb)){
  return false;
  }

  $command = "-u{$testDb[‘connection’][‘username’]} -p{$testDb[‘connection’][‘password’]} {$testDb[‘connection’][‘database’]}";
  $filePath = APPPATH . ‘tests/data/’ . $schema;

  exec("mysql $command < $filePath ");
  }
  [/php]

• Now, I can load my dataset anytime in my testing and also can clear it. I am doing it in setUp() and tearDown().
  [php]
  // In setUp()
  $this->runSchema(‘setup.sql’);

  // In teardown()
  $this->runSchema(‘teardown.sql’);
  [/php]

Thats all, Now I can test using database manipulations. Please note 2 important things, –
– When initializing models, I have to pass the db instance name ‘test’. This name can be set in phpunit config file and be used from that config.
– The execute() function is not using this db instance name for executing queries. I had to tell him again to use this instance name. So, all execute()s are now execute($this->_db).

OK. Now my Kohana v3 is ready for Database Testing.