Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

How to do automated tests of a database centric CPAN module

by jds17 (Pilgrim)
on Oct 20, 2012 at 23:25 UTC ( #1000157=perlquestion: print w/replies, xml ) Need Help??
jds17 has asked for the wisdom of the Perl Monks concerning the following question:


in a few days I want to put the first version of DBIx::Table::TestDataGenerator on CPAN. One of the issues I need to solve is how to provide meaningful tests. The module will try to support as many DBMSs as possible, currently Oracle and PostgreSQL are supported, there will be more and I try to make it easily extensible to other DBMSs I have not thought of. The tests I have currently written target Oracle and PostgreSQL, and to connect to the databases the tests pick up some environment variables. In case those variables are absent, they are asked from the user running 'build test'.

So, for interactively installing and running the tests, I am fine. But this obviously does not work when CPAN testers will run the tests.

Would it be feasible to bundle an SQLite database and run the tests against it in case no other database has been specified? I cannot think of a simpler database to test against, at least the thing would need to allow constraints such as primary and foreign keys as well as uniqueness constraints. I looked at DBD::Mock, but I don't see how this could help me since SQL code is not really being run against some sort of tables and one would need to mock the expected results too, which would not be a useful testing approach.

How would you handle such tests? I am sure not to be the first person to face this issue, but I could not yet find a good enough answer, e.g. when looking up the tests of other DBIx modules.

Update: Having had few experience with SQLite, after having read some documentation for DBD::SQLite, I was pleased to see how easy it is to create an in memory database, making it unnecessary to bundle a SQLite database with the module for testing purposes.

  • Comment on How to do automated tests of a database centric CPAN module

Replies are listed 'Best First'.
Re: How to do automated tests of a database centric CPAN module
by Khen1950fx (Canon) on Oct 21, 2012 at 01:28 UTC
      The cpan-testers-discuss mailing list would be the best place to ask about stuff like this. But yes, SQLite is a good choice. MySQL is a good second choice as it's fairly commonly installed. Anything more exotic probably won't get tested much, or, indeed, at all, because setting up things like Oracle is a pain in the arse.
      Thank you for these references, Khen1950fx, I will see how the testing was implemented there.
Re: How to do automated tests of a database centric CPAN module
by tobyink (Abbot) on Oct 21, 2012 at 08:11 UTC

    Yes, definitely add SQLite to your list of supported databases, and bundle a SQLite database for testing.

    And don't just test SQLite if Oracle/PostgreSQL can't be tested. Test it always. Add a test_requires dependency on DBD::SQLite. If I happen to have Oracle and PostgreSQL up and running, and the appropriate environment variables set, then I'd want the test suite to run on all three databases.

    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'

      I will definitely write an extension to support SQLite (as already planned) and bundle SQLite for testing, thank you for the reassurance regarding this question, tobyink.

      The tests are run against all supported databases during development, but I guess a user would target one specific DBMS. So, I think looking for environment variables specifying one database connection is enough.

Re: How to do automated tests of a database centric CPAN module
by salva (Abbot) on Oct 21, 2012 at 10:42 UTC

      Now that's interesting, I have not looked for temp PostgreSQL database creation since I would not have expected this to exist.

      I would like to include tests using Test::postgresql besides the tests against in-memory SQLite (in case no other database has been specified, see my other posts). I hope the non-patched version works for me since my module would obviously need to reference something on CPAN. Thank you for pointing to your patched version on GitHub.

        I believe Test::Database serves a similar purpose (create/drop temp databases).

Re: How to do automated tests of a database centric CPAN module
by sundialsvc4 (Abbot) on Oct 22, 2012 at 13:17 UTC

    There are two distinct things that you need to test:   first, the ability to connect to a database of the expected type; second, the ability to interact with the database and to demonstrate that the correct set of changes are applied to it.   The latter situation, I think, is likely to be “particular to the (usually...) known target type of database” that I personally would be less than willing to substitute another one.   Normally, I set up a testing-only database that is used only for automated tests, and the first step of that script is to run an external command that first drops and then reloads the database with known initial content.   Sometimes that script is re-run several times.   (In one commercial situation, we set up the first test to run an md5sum against the source-data script itself to guard against innocent but undocumented out-of-process changes to its “known” content.)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1000157]
Front-paged by davido
NodeReaper patrols the perimeter

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (1)
As of 2018-07-22 00:33 GMT
Find Nodes?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?

    Results (451 votes). Check out past polls.