The decision between test and production should be made when creating your database handle. I suggest that you create one central subroutine that creates the database connection, and you overwrite the database name in your test scripts:

package My::DB;
use strict;
use DBI;

use vars qw( $dbname );
$dbname = 'dbi:mysql:production_db';

{ my $dbh;

sub dbh {
  $dbh ||= DBI->new($dbname);
  $dbh;
};

  sub disconnect {
    $dbh->disconnect
      if $dbh;
  };
};

package main;
use My::DB;

my $dbh = My::DB::dbh;

# ... do stuff
[download]

and in your test program(s) :

use strict;
use My::DB;
{
  no warnings 'redefine';
  $My::DB::dbname = 'dbi:mysql:test_db';
};

my $dbh = My::DB::dbh;

...
[download]

That way, you can easily change out the database connection to use, and you also can easily simulate failure conditions like the database being unavailable etc.

I would also consider using DBD::SQLite as database for some of the tests, as restoring a DBD::SQLite database is simply a matter of copying one file. You might get similar results with mysqldump though.

perl -MHTTP::Daemon -MHTTP::Response -MLWP::Simple -e ' ;    # The  
$d = new HTTP::Daemon and fork and getprint $d->url and exit;#spider
($c = $d->accept())->get_request(); $c->send_response( new   #in the
HTTP::Response(200,$_,$_,qq(Just another Perl hacker\n))); ' #  web
[download]

My first idea was to have a second DB to be used during testing. The problem is that to use the other DB, I have to intersperse my production code with snippets like $db="dbi:mysql:test_db" if $ENV{'test'} or likewise. Which seems not to elegant to me...

The fact that you have to have to have snippets is probably a sign that you need to add some kind of application configuration layer. Duplication is always a bad sign.

Once you're always fetching your DBI string from My::App::Configuration (or whatever) you only have one place where you need to tweak settings for the test environment and the problem becomes a lot simpler.

The following section from the DBI pod suggests one alternative for isolating the target DB against which you will run, from the code itself, using environment vars.

As a convenience, if the $data_source parameter is undefined or empty, the DBI will substitute the value of the environment variable DBI_DSN. If just the driver_name part is empty (i.e., the $data_source prefix is "dbi::"), the environment variable DBI_DRIVER is used. If neither variable is set, then connect dies.

Examples of $data_source values are:

  dbi:DriverName:database_name
  dbi:DriverName:database_name@hostname:port
  dbi:DriverName:database=database_name;host=hostname;port=port
[download]

There is no standard for the text following the driver name. Each driver is free to use whatever syntax it wants. The only requirement the DBI makes is that all the information is supplied in a single string. You must consult the documentation for the drivers you are using for a description of the syntax they require. (Where a driver author needs to define a syntax for the $data_source, it is recommended that they follow the ODBC style, shown in the last example above.)

If the environment variable DBI_AUTOPROXY is defined (and the driver in $data_source is not "Proxy") then the connect request will automatically be changed to:

  $ENV{DBI_AUTOPROXY};dsn=$data_source
[download]

DBI_AUTOPROXY is typically set as "dbi:Proxy:hostname=...;port=...". If $ENV{DBI_AUTOPROXY} doesn't begin with 'dbi:' then "dbi:Proxy:" will be prepended to it first. See the DBD::Proxy documentation for more details.

If $username or $password are undefined (rather than just empty), then the DBI will substitute the values of the DBI_USER and DBI_PASS environment variables, respectively. The DBI will warn if the environment variables are not defined. However, the everyday use of these environment variables is not recommended for security reasons. The mechanism is primarily intended to simplify testing. See below for alternative way to specify the username and password.

Edited by BazB - change to use blockquote and wrap code/variable sections only in code tags.

Realize that to connect to a database, you have to give some pieces of information - one of them the name to the database server. Suppose one of your database servers (logical) name is grapefruit. Then make it so that in your production environment grapefruit points to the production database, and in your testing environment, grapefruit points to your testing database.

The advantage is that you only have to do this once, and every program can benefit from it. It also prevents you from accidentely accessing a production database from a testing environment because you set the wrong environment variable, or used the wrong configuration file.

Abigail

The time it'll take to insulate the connection string to a variable will be abundantly worth it.

By the way, you can very likely automate the task of finding-and-replacing, given that I guess you won't be opening a connection now and again and that the connection string - that I guess will be always the same - can be substituted to a global variable.

I want to test DB operations etc, i.e. create some entries, update, delete them, etc. Now, my problem is that all this testing interfers with the 'real' data if I run the tests against the 'real' database.

There are as many ways of skinning that cat as there are folks trying to skin it. Here are some of the aproaches I've seen without passing judgement on any of them.

Environmental values

# .bashrc sniglet

export DBHOST="mytestdbhost.mydomain.tld" 
export DBDB="mytestdb"
export DBUSER="user"
export DBPASSWORD="SeCrEt"
export DBDRIVER="mysql"

------- 8<snip! 8<--------------------------

# in some perl code, far far away
use DBI;
my $dbhost = $ENV{'DBHOST'};
my $dbname = $ENV{'DBDB'};
my $dbuser = $ENV{'DBUSER'};
my $dbpassword = $ENV{'DBPASSWORD'};
my $dbdriver = $ENV{'DBDRIVER'};

my $DSN=sprintf("DBI:%s:host=%s:database=%s",
                $dbdriver,$dbhost,$dbnaem);
my $dbh=DBI->connect($DSN,$dbuser,$dbpassword)
    or die DBI->errstr;

... etc....
[download]

Configuration Files

The other method I've seen (and used myself) is to create your own module that encapsulates your database access configuration. The one I use over and over again I call (oddly enough) DbConfig.

use DBConfig;
use DBI;

my $dbh=DBI->connect(DBConfig::DSH,DBConfig::User,DBConfig::Password) 
+or die $DBI::errstr;
[download]

When I install DBConfig on my production boxes I make the appropriate changes to the module. Otherwise the code is identical.

As always in Perl TIMTOWTDI!

I solved this in a recent program by keeping all configuration information (including the name of the database driver) in a module. That module had an is_testing() subroutine, which was called in a BEGIN block by the test scripts. This subroutine would change the configuration params to things that made sense for testing (be careful not to change too much, or you'll be testing a system that doesn't reflect what will go on in the real program).

     PROD: my.data.base.name
[download]

    TEST: the.test.db.for.me
[download]

A glance at the top three lines on any screen in the application tells you know where you are. This has saved more than one person's sanity....

(This strategy is easier if your database supports nested transactions because then you can lessen your worries about whether the code being tested accidentally does its own commits.)

This may not be wise or applicable in your case, but here is what we did for a real time test. We wanted to assure ourselves that the live, production database was active and all connections to it were up, so we created the "A Test" or acceptance test. It ran from the client side systems and software, through all communications and then wrote rows in the database. We added a field that was a "test flag" to all rows, which is a minor amount of overhead. We could write a few rows, query them, check for turnaround time, then select and delete them by their "test flag" status.

Thus we could redo the test anytime and recover to a previous state. This is not good in all circumstances, but is very useful to assure the production database was up and in a good state.

Diskcrash

I've been working on a web project in which there are core components. tabligs to use those components, pages to use the taglibs, and a database to house the data.

When writing the tests for the package, I used Test::More to cover the core. Apache::Test to test the taglibs and pages in a true HTTP setting. For the database stuff, I decided on using DBD::SQLite. It's an all in one database module.

So the tests load the schema and data via SQL scripts in the terst suite, then I test the core components against that database and verify the results are what I expect.

Of course, this has a potential problem if you are using DB specific features not available in SQLite. But if you're sticking to simple SQL it should be ok.

Update:
For that matter, I chose DBD::SQLite rather than a seperate DB so those test could run in an automated way without and DB connectivity information in at the test location (think CPAN). If DBD::SQLite is installed on the test, tests just hum along without having to interact with the user.

This may or may not be relevant but you might want to look at DBUnit. This is of course written in java but you might be able to use it to tune your SQL code and then insert it into your perl program when you feel that you have it right. It may also be good for someone to port this project over to Perl so that we all can use it (and no it cannot be me right now since I am up to my neck in school work although I may try it in the future).

It uses an example every web/application programmer is familiar with: ye olde Shopping Cart.

A Catalog interface is used which decouples the database from the shopping cart code. That way you can test your code against the Catalog interface (using a simple implementation sans external/database dependencies) before worrying about the database (which is rightly dismissed as 'infrastructure').

You only directly test the database using DBUnit, or DBI/JDBC mock objects (my preferred option), or whatever, when you implement a Catalog that uses a database.

I'm not trying to say that testing the database is easy. Quite the opposite, external dependencies seem to be a total bitch. The point is that its not necessarily something to get hung up on when you're writing ShoppingCart. This has been a problem for me whenever I have tried to introduce unit tests to my projects, but I think this advice will be helpful next time I try.

(Yes, the article is about Java, but I think databases_and_testing issues are mostly language-agnostic. The rest of the article has decent advice on testing issues as well.)

Also, the testing weblog where I found this article is excellent.