Can SQL queries be Unit Tested?

Discussion of testing theory and practice, including methodologies (such as TDD, BDD, DDD, Agile, XP) and software - anything to do with testing goes here. (Formerly "The Testing Side of Development")

Moderator: General Moderators

Post Reply
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Can SQL queries be Unit Tested?

Post by Ambush Commander »

Can SQL queries be Unit Tested?

Or are SQL methods "black boxes" and should be seperated as much as possible?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Sure. The way I'd do that would go like this:

(1) create a db/tables in setUp()
(2) if you're testing a slect statement, insert some sample data in the test method (or maybe do this in setUp if it's the same each test)
(3) make assertions against the data access class methods
(4) drop the db/tables in tearDown

Using setUp and tearDown clears means that each test method starts with a clean slate.

Be careful with database connections - see viewtopic.php?t=35892
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

I'm reading the other topic, but as Nielsene commented, I'm trying to digest it. I get the basic point about closing db connections though.

Well, time for more coding. Unit tests are a lot of work!
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

The unit tests make you think about the little details. That's more work but failing to deal with them isn't really an option. In the long run I think you definitely save time. Maintenance, refactoring & deployment is easier with unit tests to back you up and debugging is drastically reduced.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

PS: testing can show up deficiences in the OOP design. Testing smells can sometimes suggest better designs. Cohesive objects which do just one thing and don't cut across application layers are much easier to test.

It's much easier to learn with a mentor. Once you see some good examples of OOP code it helps a lot. Good examples are hard to find in the world of php. Also, if you've got a local XP group it might be worth going along to a meeting.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

PPS: I just realised that I do "cheat" myself at times. For example I usually define error messages as object properties:

Code: Select all

class MysqlDatabase // implements Database
{
    var $failed_conn_error = 'Failed to connect to database server [%s].';
    // this would be output in a trigger_error call elsewhere in the class
    // etc
And then access them directly in the test:

Code: Select all

function testWithFailedConnection() 
    {
        $server = 'mirage';
        $db =& new MysqlDatabase($server,'', '');
        $this->assertIdentical($db->getError(), sprintf($db->failed_conn_error, $server));
I wouldn't want to add a getter just for testing. Also I don't want to assert a string since, if I edit the error message, I also have to edit the test.

Apart from that, I stick rigidly to testing the object via its interface.
Last edited by McGruff on Thu Aug 04, 2005 8:48 pm, edited 1 time in total.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

I'm trying to finish up the refactoring of my messy code, then I'll come back to my DB tests. I'll definitely share my experiences and helper code, in case that helps you.

I'm predominately interested in testing the proper construction of dynamic queries, more than testing the results of them. But I think to get full end-to-end unit tests I will test the query results as well. My schema is large and cumbersome, so I'm not sure how well I'll do.
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

nielsene wrote:I'm predominately interested in testing the proper construction of dynamic queries, more than testing the results of them. But I think to get full end-to-end unit tests I will test the query results as well. My schema is large and cumbersome, so I'm not sure how well I'll do.
So you'd do a lot of informal testing of a query before you implement it, but then when time came to build the unit test, it would be a less important thing to do?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Well I have a lot of Data Mappers in my application, ie places where I'm building up query strings as needed from lots of little pieces. I need to test that the correct query is assembled given the current inputs.

Otherwise lots of my queries are stored pre-packaged in a PhraseBook.

I have about 100 tabes, soon to grow to about 150. With cascading and restricting updates/deletes on the foreign keys that thread throughout the schema. I have about 15 internal to the database functions/triggers that propogate data into other audit tables, etc. I desperately need to test all of this; and I need to make sure that queries trigger the proper actions.

However just loading the full schema takes several seconds. While I have a DB Test Case now, that creates a test DB, loads the schema, populates it with enough data, etc. Its going to quickly get to the point where the DB tests bring the entire suite to a standstill... If its tacking many minutes to run, and I'm testing after 10-45 seconds of coding, my productivity is going to be shot.

If I try to isolate more "subsets" of the database to only load partially, I'm afrais I'll miss some possible ripple points and lose database integrity.

So I need to test the DB and the schema, I want to test them, I have some tools to do so, but I think its going to become unfeasible quickly..... :(
Post Reply