Can SQL queries be Unit Tested?
Moderator: General Moderators
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
Can SQL queries be Unit Tested?
Can SQL queries be Unit Tested?
Or are SQL methods "black boxes" and should be seperated as much as possible?
Or are SQL methods "black boxes" and should be seperated as much as possible?
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
(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
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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.
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.
PPS: I just realised that I do "cheat" myself at times. For example I usually define error messages as object properties:
And then access them directly in the test:
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.
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
// etcCode: Select all
function testWithFailedConnection()
{
$server = 'mirage';
$db =& new MysqlDatabase($server,'', '');
$this->assertIdentical($db->getError(), sprintf($db->failed_conn_error, $server));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.
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.
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.
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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?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.
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.....
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.....