PHP Developers Network
http://forums.devnetwork.net/

PHPUnit: Testing code that uses mysql queries
http://forums.devnetwork.net/viewtopic.php?f=39&t=113010
Page 1 of 1

Author:  Delpheno [ Wed Feb 17, 2010 2:24 pm ]
Post subject:  PHPUnit: Testing code that uses mysql queries

I have a global variable that is a database, and it's inside most of my classes, and the functions use that variable to extract or insert data. The functions do not return anything. Unfortunately I'm using mysql, and PHPUnit encourages us to use sqlite3, so I can't create a table by memory.

I feel like PHPUnit's database extension isn't fleshed out yet, considering their tutorial on Operations and Database Best Practices are blank.

Is there a proper way to test functions in classes using mysql queries? Should I be using mock objects instead? It seems like I need to have a test table and just use that to test these functions that use queries. If the functions do not return a value, is it possible to unit test it?

Thank you.

Author:  josh [ Wed Feb 17, 2010 8:29 pm ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries

I agree with you, its' database extension is of little value.

Including Queries in the harness
- I just create a startTransaction() and rollbock() calls in my setup & tear down methods. (transaction rollback)
- Another way to do it is truncate() on teardown (setup/tear down clean up)

Isolate the business logic from the query
Another way is to subclass your class and test that subclass. Move the queries into protected methods that all start with the word "find", "delete", etc.. and group them together at the bottom of the class. Null out those methods on the test specific extension.

Isolating the business logic from the querying is a highly desirable trait of a system. It means your system can be re-used in more scenarios. But on the other hand I think including your queries in the harness itself is a huge benefit, however the two methods are not mutually exclusive.

Author:  Jenk [ Thu Feb 18, 2010 10:09 am ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries

I'll refrain from posting my thoughts on this topic again :P

Author:  josh [ Thu Feb 18, 2010 3:41 pm ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries

What Jenk does is assert on the SQL itself. That is probably going to yield tests that fit the definition of "unit test" better, however the down side that I can see is it makes it easier to have a test be wrong (and therefore give me a false sense of security).

Also for every rule of thumb theres a counter rule. If your black box behavior does not change, but the querying logic/underlying schema changes, then asserting on the SQL is a bad idea. If your black box behavior changes but the underlying schema stays the same asserting on the SQL is the best idea.

There's no right or wrong paths to go down.

Author:  McGruff [ Fri Feb 19, 2010 12:22 pm ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries

Actually I'd argue that it is essential to . Mocked connection objects cast a shadow which can hide all kinds of unpleasantness in the data access layer. Data access code must be tested too - perhaps more so than anything else. Bad application logic can always be reworked but bad, damaged data is bad forever.

Author:  Delpheno [ Mon Feb 22, 2010 2:01 am ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries


Author:  Jenk [ Mon Feb 22, 2010 9:48 am ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries


Author:  Eran [ Mon Feb 22, 2010 9:51 am ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries

I think he referred to loss / damage to data with a malformed query. Tests should and could prevent (some of) it.

Author:  Jenk [ Mon Feb 22, 2010 10:04 am ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries

But you'd still lose that data. Just running the test for the first time will do that, if you are connected to a database. Of course, it'll be on a test/sandbox database and noticed long before it touches live.

I'm not advocating you don't functionally test, but functional test should be done by hand at least once before deployment. I.e. you try and login, you try and add something to something else, you try and delete something, etc. Automated tests are there to prove your concept, and to perform quick and frequent checks as you progress. They are not a replacement for actual, physical testing.

It's just occurred to me that it is somewhat ironic that testing against a string of characters (SQL query) is being noted as a bad thing to do by some (all but me?) and yet.. testing against a string of characters (code, asserts, etc, also written by the developer) is being heavily relied upon by those same people... both are doing the same thing.

Author:  Eran [ Mon Feb 22, 2010 10:09 am ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries


Author:  josh [ Mon Feb 22, 2010 12:01 pm ]
Post subject:  Why not just tie a ribbon around your finger

Testing the queries does not mean you are re-testing mysql.You are in fact testing the SQL, which is part of your code. You are not just testing that the query runs and returns some data, you are testing that that data works with the code.

I write tests to facilitate change.

"Axiom 1" If theres a bug in the test and the test passes, you've also got a bug in production (almost always).
"Axiom 2" If the test is simply doing a string assertion then the probability of a test bug and production regression occurring together is 100%, when developers want to change something they simply change the SQL in both places.
"Axiom 3" If the tests don't provide any benefit that simply running the query manually before & after changing it would provide, then there is no reason to write said test.

I think this is sound logic. Jenk your database testing strategy is basically to run the query and eyeball the results. If thats what works for you then great, but I think functional tests can & should test the database sometimes. What you are doing, asserting on the SQL string, provides no benefit other then a reminder for you to do the manual testing. Why not just tie a ribbon around your finger, it would be more cost effective :D

I've heard you argue not to use "private". The same argument applies here I think. You're just disabling yourself from changing your production code as easily. You now have to make edits in two places, and worst of all developers just start editing the tests every time a change is needed (making the test pointless because eventually it wouldn't be testing anything but the presence of bugs)

Author:  Jenk [ Tue Feb 23, 2010 6:39 am ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries

Not sure what you meant by your last point, you're supposed to edit your tests when changes are needed. Refactoring your tests to suit new requirements is the first point of call :)

I can see your points regarding the rest. :)

Author:  McGruff [ Tue Feb 23, 2010 4:00 pm ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries


Author:  josh [ Tue Feb 23, 2010 6:24 pm ]
Post subject:  Re: PHPUnit: Testing code that uses mysql queries


Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/