Unit testing legacy PHP class with heavy DB dependency

Question

I am trying to implement some unit tests into a legacy PHP application.

There have been a number of challenges with this, but in particular for this question, I am currently looking at a small class that manages the app config.

The class interface is pretty simple; it does the following:

The constructor calls a Populate method, that uses our Recordset class to load the config for the requested module from the database.
Get method, which returns a specified config value.
Set method, which sets the config value in memory.
Save method, which writes the config update(s) back to the DB.

Testing the Get/Set methods is straightforward; they map directly to private array, and work pretty much as you'd expect.

The problem I have is with testing the database handling. The class uses a number of fields on the config table (module name, language, etc) to determine which config items to load and in what priority. In order to do so, it constructs a series of elaborate SQL strings, and then makes direct calls to the DB to get the correct config data.

I have no idea how to go about writing a unit test for this. Aside from the Get/Set methods, the class consists pretty much entirely of building SQL strings and running them.

I can't see a way to test it sensibly without actually running it against a real DB, and all the issues that go with that -- if nothing else, the complexity of the config loader would mean I'd need at least seven or eight test databases populated with slightly different config. It seems like it would be unmanageable and fragile, which would defeat the point somewhat.

Can anyone suggest how I should proceed from here? Is it even possible to unit test this kind of class?

Many thanks.

score 1 · Accepted Answer · answered Jan 27 '13 at 21:24

I must say I'm not sure I agree that unit tests would be somewhat pointless without hitting the database here. My goal would be to get the business logic that produces the SQL under test, without involving your database. Here is an example of what I'm talking about:

class Foo {

    // ... Getters and setters for your config ...

    public function doSomeBusinessLogicThenHitDb()
    {
        $sql = 'SELECT * FROM mytable WHERE ';
        $sql .= $this->_doSomethingComplicatedThatInvolvesParsingTheConfig();
        $this->_queryDb($sql);
    }

    protected function _queryDb($sql)
    {
        // Do something with a PDO or whatever
    }
}

Having abstracted the _queryDb() bit to a separate function, you can then write this test:

    public function testMyClassUnderSomeCircumstances()
    {
        // Set up config
        $exampleConfig = // whatever

        // Set up expected result
        $whatTheSqlShouldLookLikeForThisConfig = 'SELECT ... WHERE ...';

        // Set up a partial mock that doesn't actually hit the DB
        $myPartialMockObject = $this->getMock('Foo', array('_queryDb'), array(), '');
        $myPartialMockObject->expects($this->once())
                            ->method('_queryDb')
                            ->with($whatTheSqlShouldLookLikeForThisConfig);

        // Exercise the class under test
        $myPartialMockObject->setConfig($exampleConfig);
        $myPartialMockObject->doSomeBusinessLogicThenHitTheDb();
    }

The point of this test is to test the business logic that produces your SQL - not to test your database itself. By putting the expectation in place that the resulting SQL must look like whatever it must look like, you are ensuring that your tests will fail if innocent refactoring of _doSomethingComplicatedThatInvolvesParsingTheConfig() accidentally breaks your code, by making it produce different SQL from what it used to.

If testing the whole application, including its database, is your goal, try a proper integration testing suite like Selenium. Unit tests monitor individual classes and tell you when they've stopped behaving as they're supposed to. You will face problems with speed of execution, and with error localisation (i.e. is the bug even in the code, let alone the class under test, or is it a DB thing?), if you let them overreach.

Thanks for the answer. Unexpected given the age of the question. :-) Since posting the question, I ended up doing a fairly large-scale refactoring exercise, largely in order to get all the code into a testable state. That was a while ago, and it's all gone pretty well. But the advice you've given is good; I'll give you the +1 and accept the answer. Thank you for making the effort to help. — SDC, Jan 28 '13 at 11:24

score 0 · Answer 2 · answered Oct 15 '12 at 13:16

One straight forward to better test these things is to give your config class an object to access the database, so you can run any config with the real database or just some mock of it that writes to memory instead or even lightweight files if you need persistence for example.

That can be done with creating an adapter with a defined interface. The first adapter you write is for your database.

When the config object is created, you pass in the adapter. As it has a defined interface, the config class can work with any adapter that has that interface. First of all the database.

Then you either mock an adapter or write an adapter of it's own for your tests. Inside your tests, you don't use the database adapter, but the test adapter.

You then can unit-test the config class independent of the database.

Thanks for the reply. I guess part of the problem is that the queries the class builds are really where the functionality is. Mocking the DB means we don't run the queries. Without running the queries, testing the class does become easier, but also somewhat pointless. The queries are in MySQL dialect, so probably won't work with a sqllite table, so that's not going to work either. :( — SDC, Oct 15 '12 at 13:40

Unit testing legacy PHP class with heavy DB dependency

2 Answers2