Unit testing a class that returns SQL

Question

I'm trying to use PHPUnit to unit test some class methods that return SQL. These classes should work with any Zend_Db adapter, so I would like the tests to do the same. My tests look a little like this:

public function testEtcGeneratesCorrectSQL()
{
    $model = new ClassBeingTested();

    // do some stuff

    $sql = $model->__toString();

    $this->assertEquals('SELECT foo.* FROM foo WHERE bar = 1', $sql);
}

the problem is differences in escaping between adapters. If I run this test using Pdo_Mysql, I'll get an error like this:

--- Expected
+++ Actual
@@ @@
-SELECT foo.* FROM foo WHERE bar = 1
+SELECT `foo`.* FROM `foo` WHERE `bar` = 1

if I use the Sqlite adapter:

--- Expected
+++ Actual
@@ @@
-SELECT foo.* FROM foo WHERE bar = 1
+SELECT "foo".* FROM "foo" WHERE "bar" = 1

so what's the right thing to do here? Is there a way to disable the escaping in Zend_Db I can turn on just for the purpose of these tests? Do I hardcode in the adapter type and then adjust my expected output to match? Or strip out the different quote characters before doing the assertion?

Answer 1

Use a constant rather than hardcoding either set of quotes, so for MySQL:

$this->assertEquals('SELECT ' . DB_QUOTE . 'foo' . DB_QUOTE . '.* FROM '
                    . DB_QUOTE . 'foo' . DB_QUOTE . ' WHERE '
                    . DB_QUOTE . 'bar' . DB_QUOTE . ' = 1');

Which looks absolutely horrid, but it will work if you set DB_QUOTE depending on the driver you're using.