Established convention for testing using mysql?

leocharre has asked for the wisdom of the Perl Monks concerning the following question:

I have some modules that make use of a database. I'm biased towards mysql, but at least for testing purposes, using sqlite is absolutely wonderful.

I'm a sucker for tests. I think it actually speeds up development, a lot.

I have some situations where mysql and sqlite behaviour is different. Where one SQL call is not going to work the same way for both database 'formats' (is that what you call them?)

I want to offer to test both mysql and sqlite.

I noticed for example with DBD::mysql, you need to have a local mysqld server daemon running to install without force.

I also noticed that the tests do not ask what database name, user and password to use.

Is it going behind my back and creating these? And then deleting them? Maybe it's not making any inserts/creates? doubt it..

In my tests that require mysql. Should I prompt the user for a testing database name, user and password? Or should I create those and then delete them? I would probably rather not, it feels intrusive on my code's part.

Is there an established convention for a mysql testing database, like 'guest_database', 'guest_user', 'guest_password' ?

What would be the right thing to do?

Prompt user for these parameters (assuming that they had already created the testing databse).
Skip the mysql tests, keep them there, but by default only use sqlite tests.
Create a testing database (checking that i'm running as root and mysqld is running) and then drop it upon 99_cleanup.t ?

Comment on Established convention for testing using mysql?

Replies are listed 'Best First'.
Re: Established convention for testing using mysql? by dsheroh (Monsignor) on Jan 13, 2008 at 08:27 UTC
What I do is to have my app's database initialization code create two (structurally) identical databases, appname and appname_test. My tests are then written to run against appname_test, which shouldn't have anything else using it, so there's no concern that I might clobber meaningful data. This also allows the tests to use the same connection information (modulo appending a _test to the db name) and credentials as the real app, which both simplifies things and will also cause the tests to catch any problems that may come up with connecting to the db in the first place. As far as the MySQL vs. SQLite concerns, I get the impression that you're only including SQLite for testing purposes and using MySQL for the real app. If this is accurate, then I'd say to drop SQLite. Tests work best when they're running as much of the same code as the real app as possible and setting up a duplicate _test db allows them to safely use the exact same db handling code.	[reply]
Re^2: Established convention for testing using mysql? by roboticus (Chancellor) on Jan 13, 2008 at 12:55 UTC
I (often) use a variation of this. I create a version of the database loaded with all my test cases, then copy that database on top of the test database at the beginning of each test run, so the tests may be as destructive as they like to the database. When I discover a new data variation in the production data, I create a test case in the prototype database.... (Yeah, I know I should use Mock objects, but I've never been comfortable with them...) ...roboticus	[reply]
Re: Established convention for testing using mysql? by starbolin (Hermit) on Jan 13, 2008 at 07:08 UTC
Could be that the complication of testing both SQL and SGLlite means that the support needs to be in separate modules. I wouldn't be comfortable testing with the user's database even if the user supplies the database name. Could be the users base is misconfigured or the user doesn't recognize the risk. If the user is installing your code on his system I don't see any extra imposition in installing a temporary db. s//----->\t/;$~="JAPH";s//\r<$~~/;{s\|~$~-\|-~$~\|\|\|s \|-$~~\|$~~-\|\|\|s,<$~~,<~$~,,s,~$~>,$~~>,, $\|=1,select$,,$,,$,,1e-1;print;redo}	[reply] [d/l]