I have been writing a lot of integration tests lately and wanted to share a quick tip about how to deal with datetime and timestamp columns in the table you are testing.  When comparing a dataset with the current state of a table it is hard to deal with modification dates, etc.  So  the best thing to do is to filter out those dates and not try to compare them.

Here is a simple example.

The database:

  bug_id            INTEGER NOT NULL PRIMARY KEY,
  bug_description   VARCHAR(100),
  bug_status        VARCHAR(20),
  create_date       DATETIME,
  mod_date          TIMESTAMP

The expected result: (Note the missing mod_date column.)

<?xml version="1.0" encoding="UTF-8" ?>
  <table name="bugs">
	<value>this is a new bug</value>
	<value>2010-10-15 08:06:53</value>

Now for the test:

public function testSetAcl() {
	$model->addBug('this is a new bug');

	$expected = $this->createXmlDataSet('expected.xml');
	$actual = new PHPUnit_Extensions_Database_DataSet_QueryDataSet($this->getConnection());
	$actual->addTable('bugs', 'SELECT bug_id, bug_description, bug_status FROM bugs');
	$this->assertDataSetsEqual($expected, $actual);

The key to this test passing, is the 2nd paramter being passed to $actual->addTable. The default for $actual->addTable is to do a ‘SELECT * FROM bugs’ but that then brings back the mod_date, which you cannot deal with. So specifying the columns to return with ‘SELECT bug_id, bug_description, bug_status FROM bugs’ will remove mod_date from the test and make your life easier.

Related Posts