Beefy Boxes and Bandwidth Generously Provided by pair Networks Cowboy Neal with Hat
Welcome to the Monastery
 
PerlMonks  

Setting up database connection in tests

by McA (Deacon)
on Sep 13, 2012 at 13:41 UTC ( #993495=perlquestion: print w/ replies, xml ) Need Help??
McA has asked for the wisdom of the Perl Monks concerning the following question:

Hi all

what are your recommendations for setting up a database connection for perl module tests?

In detail:

  • What is the best way to let an installing person define the dsn and credentials for a database to connect to for testing?
  • What is the best way to encapsulate the functionality of reading this information and connecting to that database for providing a dbh to test routines? I want to use this functionality in several test files.
  • Is there a way to connect only once to a database for all tests in the t-directory of a distribution? A kind of test suite global.

Tips and code snippets appreciated.

Best regards
McA

Comment on Setting up database connection in tests
Re: Setting up database connection in tests
by MidLifeXis (Prior) on Sep 13, 2012 at 14:23 UTC

    • What is the best way to let an installing person define the dsn and credentials for a database to connect to for testing? - I am not sure about 'best', but I have used an environment variable, and have a skip_all call if the variable is not set or cannot be used for some other reason. The test uses the credentials when setting up the thing under test when DB credentials are absolutely necessary. If, however, the DB stuff can be mocked out, I do that.
    • What is the best way to encapsulate the functionality of reading this information and connecting to that database for providing a dbh to test routines? I want to use this functionality in several test files. Unless this is a test that is specifically addressing the database side of things, I would recommend mocking as much of the DB stuff away as possible. I have been doing a bit of this lately as I touch code written when I was "less experienced" (What frobnitz wrote this?). It seems to me that any time you pull data from the database, you should instead abstract that out to a function call that returns data, and possibly out to its own module. The function can then be mocked in testing, and you only need to test the DB function for what it is explicitly supposed to do. This is a generalization, and should be taken with the right amount of skepticism :-).
    • Is there a way to connect only once to a database for all tests in the t-directory of a distribution? A kind of test suite global. No(*). Each .t script is run in its own process.

    * - I suppose something could be attempted on some operating systems of opening the DB handle in a wrapper process, setting it up to be passed through some IPC method, and launching your test script once that setup is complete. It seems to me that this would be very fragile and too clever to maintain.

    --MidLifeXis

      I suppose something could be attempted on some operating systems of opening the DB handle in a wrapper process, setting it up to be passed through some IPC method, and launching your test script once that setup is complete. It seems to me that this would be very fragile and too clever to maintain.

      Actually, that pretty much sums up DBD::Proxy. Not fragile at all, but precisely a piece of the core DBI package. And, for extra example code, see Coro::DBI (which cannot install via cpan only because the only release is a "developer" release though it has worked fine for me with the CB stats).

        Is DBD::Proxy setting up a single connection and then passing it to each test script, as asked by the OP (thinking along the lines of passing a file handle between various scripts), or is it setting up a meta-connection, where a connection to a DBI::Proxy connection is talking to another DBI connection through, well, a proxy?

        This approach is different than what I was considering, and does make some good sense. An approach that I had not considered!

        Update: Reading through the documentation for DBI::Proxy, I am not certain that it meets the needs of the OP's third requirement - connecting once to the database and being able to run all of the test scripts. Perhaps it is just my unfamiliarity with the Coro method of doing things. Would you have a more complete example that implements something along what the OP was asking?

        --MidLifeXis

Re: Setting up database connection in tests
by sundialsvc4 (Monsignor) on Sep 13, 2012 at 14:57 UTC

    Certainly, since the testing frameworks are Perl programs, you should in those tests use a single module whose purpose is to establish the database-connection and to return the handles needed by all tests.   Once the module has been constructed and verified, it does not itself need to be the subject of a test.

    As previously suggested, it can also be a “mock-up” which is known to consistently produce the same test-data.   You can even go one step further by designing a subroutine that returns a “letter-bomb $dbh.”   This returns an object that has the same necessary-for-your-app methods as a real database-handle, but which methods are contrived in some way to “blow up,” thereby predictably and consistently simulating error conditions without actually having to jimmy-up code that actually throws the desired error for any genuine reasons.

Re: Setting up database connection in tests
by tobyink (Abbot) on Sep 13, 2012 at 16:51 UTC

    Does your module support SQLite? If so, just create a SQLite database in a temp file and test against that with DBD::SQLite.

    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'
      Or don't even bother with the temp file and attach SQLite to the file :memory: for an in-memory database.

        Indeed, but that couldn't be used to persist across multiple ".t" files, which was one of the criteria the OP mentioned.

        Obviously if you're using very advanced SQL features, you may not be able to support SQLite. But if you're almost able to support SQLite already, then it's certainly worth putting in the extra effort to support it fully. The benefits to your test suite will pay back your effort.

        perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'
Re: Setting up database connection in tests
by chromatic (Archbishop) on Sep 13, 2012 at 19:20 UTC

    I use DBICx::TestDatabase and have a single test library I use from all of my .t files. This works so exceedingly well for all of my projects that use DBIx::Class that I almost never think about it.

Re: Setting up database connection in tests
by McA (Deacon) on Sep 14, 2012 at 00:41 UTC

    Thank you all for your suggestions.

    In the requested case I have to test against MySQL with some specialities so that using SQLite as an easy to create database is not feasible.

    Best regards
    McA
Re: Setting up database connection in tests
by erix (Priest) on Sep 14, 2012 at 07:52 UTC

    Perhaps Test::Database can do what you want? I think it can do database setup and tear-down for testing.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://993495]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (10)
As of 2014-04-17 23:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (459 votes), past polls