CODEWITHSUNDEEP
phpdatabasephpunit
Donoven Rally
Donoven Rally

Reputation: 1700

phpunit database testing without affecting the database

I am using phpunit for a few days now and got to the point i need to test a database queries. i followed the phpunit official documentation but i think im missing the main idea behind it. i implemented the two abstract function getConnection and getDataSet like this:

  /**
   * @return PHPUnit_Extensions_Database_DB_IDatabaseConnection
   */
  public function getConnection()
  {
      $pdo = new PDO('mysql:host=localhost;dbname=testdb', $this->config['mysql_usr'], $this->config['mysql_pass']);

      return $this->createDefaultDBConnection($pdo, 'testdb');
  }

  /**
   * @return PHPUnit_Extensions_Database_DataSet_IDataSet
   */
  public function getDataSet()
  {
      return $this->createXMLDataSet(dirname(__FILE__).'/../dbTest/fixtures/data.xml');
  }

so ok, now im interacting with my local database but then why do i need to create the XML dataset (do i need it for other than just comparison)? i find myself only interacting with the data in the database. right now for example,i want to test a function that deletes from the DB:

  function deleteRule($id){
    $DBH = $this->getDbh();

    $query = "SELECT id from ads where group_id=$id";
    $STH = $DBH->query($query);
    if ($STH->rowCount() > 0){
      throw new Exception("Rule has {$STH->rowCount()} ads, can't delete", 400);
    } else {
      $query = "DELETE FROM rules WHERE id=:id limit 1";
      $stmt = $DBH->prepare($query);
      $stmt->execute(array(':id' => $id));
    }
  }

so i started writing some tests for it, one of them looks like this:

public function testDeleteRule_legalDeletion()
{
  $id = 3;
  $pdo = $this->getConnection();
  $fixture = new AdRules();
  $fixture->setDbh($pdo);
  $res = $fixture->deleteRule($id);

  $DBH = $fixture->getDbh();

  $query = "SELECT * FROM rules WHERE id=3";
  $stmt = $DBH->prepare($query);
  $stmt->execute();
  $rows = $stmt->rowCount();

  $this->assertEquals($rows,0);

}

but now, after deleting from the database how can i bring it back to the its initial state? i know i can simply execute mysql statement but im guessing it misses the all point of mocking the PDO object and avoiding major changes in the database. anyway, im very confused about this and any guidance will be highly appreciated,thx.

Upvotes: 3

Views: 1969

Answers (1)

Piotr Olaszewski
Piotr Olaszewski

Reputation: 6204

It's recommend to use other database for testing, because you can as you say break your production data. When you have other database you can use transaction mechanism to test your queries.

How this works?

  1. In bootstrap test you should connect to your test database
  2. PHPUnit calls setUp which starts a new transaction on database handle created in bootstrap
  3. Then in your test case (testDeleteRule_legalDeletion) you modify database content and assert that data is as you expected
  4. PHPUnit calls tearDown method which rollback transaction and clean your table (for test database handle)

So for those actions you'll probably need some general database test case, e.g. DbTransactionalTestCase and in this class you must override methods setUp and tearDown.

PS. If you interesting in framework which automates this process look how we do it in Ouzo Framework. Here are docs for your question.

Upvotes: 3

Related Questions