Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Testing & Databases

by domm (Chaplain)
on Jan 28, 2004 at 11:15 UTC ( #324639=perlquestion: print w/replies, xml ) Need Help??

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


I finally convinced myself to write some test for an app I'm currently developing (or rather starting to develop). It's a run-of-the-mill webbased DB-thing, using mod_perl, TT, Class::DBI as it's main components.

I know basic testing stuff, but there's one thing I haven't got a decent solution for, which is why I'm seeking help here.

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.

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...

Another idea was to have testing DBs on the dev-boxes and the 'proper' DB on the production box. But this way I couldn't run the test suite on the production box, which seems not perfect, either.

So, has anybody got some ideas / pointers on how to solve this kind of testing problems?

-- #!/usr/bin/perl for(ref bless{},just'another'perl'hacker){s-:+-$"-g&&print$_.$/}

Replies are listed 'Best First'.
Re: Testing & Databases
by Corion (Patriarch) on Jan 28, 2004 at 11:29 UTC

    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

    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; ...

    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
Re: Testing & Databases
by adrianh (Chancellor) on Jan 28, 2004 at 11:25 UTC
    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.

Re: Testing & Databases
by Abigail-II (Bishop) on Jan 28, 2004 at 11:59 UTC
    There are various ways to deal with this. Ideally, you don't have to change anything in your script, and you don't make any if ($ENV {TEST}) style decisions either.

    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.


Re: Testing & Databases
by BrowserUk (Patriarch) on Jan 28, 2004 at 13:04 UTC

    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
    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:
    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.

    Examine what is said, not who speaks.
    "Efficiency is intelligent laziness." -David Dunham
    "Think for yourself!" - Abigail
    Timing (and a little luck) are everything!

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

Re: Testing & Databases
by l3nz (Friar) on Jan 28, 2004 at 11:58 UTC
    I think you should use a separate database; and that the database connection string should be held only in one place in your code. This will make it much simpler to connect to a different database for some useful purpouse but testing (say, having a parallel database running after a restore, or testing different versions of your app with different database schemas, and so on).

    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.

Re: Testing & Databases
by blue_cowdawg (Monsignor) on Jan 28, 2004 at 13:57 UTC

        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

    As you pointed out you an always use your environmental values to drive your application's behavior. You showed one way, but here's an alternative:
    # .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....
    The above ripped from some code I'm maintaining. The actual values changed to protect the guilty.

    Configuration Files

    There are two general methods I've seen for doing this. One uses an external configuration file that you either parse yourself or you can use one of several AppConfig family of CPAN modules to do your dirty work.

    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;

    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!

    Peter L. Berghold -- Unix Professional
    Peter at Berghold dot Net
       Dog trainer, dog agility exhibitor, brewer of fine Belgian style ales. Happiness is a warm, tired, contented dog curled up at your side and a good Belgian ale in your chalice.
Re: Testing & Databases
by hardburn (Abbot) on Jan 28, 2004 at 14:24 UTC

    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).

    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    : () { :|:& };:

    Note: All code is untested, unless otherwise stated

Re: Testing & Databases
by Old_Gray_Bear (Bishop) on Jan 28, 2004 at 16:37 UTC
    One other thing to recommend -- build a positive way to tell which system you are running, test or production. As a matter of policy here, all pages have a common header form, the bottom of the header displays the logical name of the database in a contrasting banner line. The configuration file contains the line
    This line appears on the banner, repeated as many times as will fit.

    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....

    I Go Back to Sleep, Now.


Re: Testing & Databases
by tilly (Archbishop) on Jan 28, 2004 at 19:05 UTC
    In addition to what everyone else said, test suites are a great purpose for a rollback. In all of your test suites make sure that you have an END block that does a rollback of your database changes. I wouldn't trust that on production, but if you accidentally ran your test suite against production, it could save you.

    (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.)

Re: Testing & Databases
by thor (Priest) on Jan 28, 2004 at 13:46 UTC
    In addition to everything that everyone else has said, make sure that you have sufficient separation between your testing environment and your production envrionment. In this case, you could do a number of things:
    • Have the name of the database be configurable at run time. Or perhaps just overridable (i.e. provide a reasonable default and a switch that you provide on the command line to override it).
    • Have your testing environment be oblivious to the production environment (i.e. it cannot connect to
    • Use different user ids, and giver permissions on the server and the database based on the user id.
    ...and the list goes on.


Re: Testing & Databases
by diskcrash (Hermit) on Jan 28, 2004 at 21:09 UTC
    Dear domm,

    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.


Re: Testing & Databases
by hmerrill (Friar) on Jan 28, 2004 at 13:42 UTC
    I'm not sure I understand the problem - are you saying that you want to develop a test suite specifically to test this one new web app? Others have already covered suggestions for this.

    Or would a generic test suite suffice, that would verify that your webserver is up and running, and verify that a web app can interact with a database? If this is the case, then you could create a new database, and a new "testing" app to make sure the webserver is functioning properly and to make sure the web app can interact with the testing database.

Re: Testing & Databases
by jk2addict (Chaplain) on Jan 28, 2004 at 21:54 UTC

    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.

    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.

Re: Testing & Databases
by cyocum (Curate) on Jan 28, 2004 at 23:44 UTC

    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).

Re: Testing & Databases
by DapperDan (Pilgrim) on Jan 31, 2004 at 17:02 UTC
    Anyone interested in testing and databases should read this:

    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.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://324639]
Approved by dorko
Front-paged by broquaint
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (5)
As of 2022-10-05 11:43 GMT
Find Nodes?
    Voting Booth?
    My preferred way to holiday/vacation is:

    Results (23 votes). Check out past polls.