PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Tue Jun 19, 2018 3:34 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 9 posts ] 
Author Message
PostPosted: Wed Aug 03, 2005 7:53 pm 
Offline
DevNet Master
User avatar

Joined: Mon Oct 25, 2004 9:29 pm
Posts: 3698
Location: New Jersey, US
Can SQL queries be Unit Tested?

Or are SQL methods "black boxes" and should be seperated as much as possible?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Aug 03, 2005 8:24 pm 
Offline
DevNet Master

Joined: Thu Jan 30, 2003 9:26 pm
Posts: 2893
Location: Glasgow, Scotland
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 http://forums.devnetwork.net/viewtopic.php?t=35892


Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 04, 2005 6:10 pm 
Offline
DevNet Master
User avatar

Joined: Mon Oct 25, 2004 9:29 pm
Posts: 3698
Location: New Jersey, US
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!


Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 04, 2005 6:33 pm 
Offline
DevNet Master

Joined: Thu Jan 30, 2003 9:26 pm
Posts: 2893
Location: Glasgow, Scotland
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 04, 2005 7:00 pm 
Offline
DevNet Master

Joined: Thu Jan 30, 2003 9:26 pm
Posts: 2893
Location: Glasgow, Scotland
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 04, 2005 7:55 pm 
Offline
DevNet Master

Joined: Thu Jan 30, 2003 9:26 pm
Posts: 2893
Location: Glasgow, Scotland
PPS: I just realised that I do "cheat" myself at times. For example I usually define error messages as object properties:

Syntax: [ Download ] [ Hide ]
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:

Syntax: [ Download ] [ Hide ]
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.

Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 04, 2005 8:14 pm 
Offline
DevNet Resident
User avatar

Joined: Fri Aug 16, 2002 8:57 am
Posts: 1834
Location: Watertown, MA
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 04, 2005 8:21 pm 
Offline
DevNet Master
User avatar

Joined: Mon Oct 25, 2004 9:29 pm
Posts: 3698
Location: New Jersey, US
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?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Aug 04, 2005 8:28 pm 
Offline
DevNet Resident
User avatar

Joined: Fri Aug 16, 2002 8:57 am
Posts: 1834
Location: Watertown, MA
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..... :(


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 9 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group