phpunit database testing without affecting the database

Question

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.

Piotr Olaszewski · Accepted Answer · 2015-08-18 11:28:59Z

3

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?

In bootstrap test you should connect to your test database
PHPUnit calls setUp which starts a new transaction on database handle created in bootstrap
Then in your test case (testDeleteRule_legalDeletion) you modify database content and assert that data is as you expected
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.

edited Aug 18, 2015 at 11:28

answered Aug 18, 2015 at 11:17

Piotr Olaszewski

6,2385 gold badges40 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

phpunit database testing without affecting the database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related