Provided by: libtest-database-perl_1.113-1_all bug


       Test::Database::Tutorial - How to use Test::Database


       The goal of the Test::Database module is to provide easy to use test databases for test
       scripts that need them.

   The problem
       Until now, when a test script needed a database, it either used SQLite (or some other easy
       to setup database), or required some environment variables to be present, or used default
       credentials, or even set up the database by itself.

       Most of those methods have pros and cons:

       ·   using SQLite

           No setup needed, but the test script can only use SQLite's dialect of SQL.  So much
           for portability across database engines.

       ·   using environment variables

           The environment variables are different for every module to test, and usually only the
           main developers/testers know about them. Since most of the CPAN testers probably don't
           bother setting them up, these modules are most certainly undertested.

       ·   using default credentials

           Typically using 'root' and '' to connect to the "test" MySQL database, these test
           script assume a default installation on the host system. These credentials often
           provide full access to the database engine, which is a security risk in itself (see

       ·   setting up the database by itself

           This method usually uses the default credentials to access an account with enough
           privileges to create a database. The host system data may be at risk!

   A solution: Test::Database
       Many modules use a database to store their data, and often support several database

       Wouldn't it be nice to be able to test on all the supported databases that are available
       on the test system? Without breaking (into) anything?

       This is the goal of the Test::Database module. It supports:

       ·   getting DSN information from a list of pre-configured database and engines

       ·   automatic detection of "file-based" database engines (typically, SQLite).

       The rest of this document describes various use cases for Test::Database.


       Test::Database has a single interface for test authors:

           my @handles = Test::Database->handles( @requests );

       @request is a list of "requests" for databases handles. Requests must declare the DBD they
       expect, and can optionally add version-based limitations (only available for drivers
       supported by Test::Database).

       The handles returned are objects of the Test::Database::Handle class.

       The data contained in the database is never destroyed or cleaned up by Test::Database, so
       it's perfectly fine to have a startup script that will setup the necessary tables and test
       data, several tests scripts that will build and update the data, and a eventually a
       teardown script that will drop all created tables.

       Test::Database can return two types of databases handles:

       ·   either a handle to a newly created database (created especially at the test script's

       ·   or a handle to an already existing database

       There is no way for the test script to tell the difference.

       In any case, the database is assumed to provide "DROP TABLE" and "CREATE TABLE" rights,
       and the test script is by definition allowed to do whatever it pleases with the tables
       that exist in the database.

       Note that Test::Database supports any DSN, not just those for which it has a driver. If
       your module supports Oracle, you can add 'Oracle' to your list of requests, and if the
       host owner configured a "dsn" pointing at an Oracle database, then it will be available
       for your tests.

   Specific database support
       It is possible to request specific versions of a database engine.

           use Test::Database;

           # request database handles for all available databases
           my @handles = Test::Database->handles();

           # or for only the databases we support
           my @handles = Test::Database->handles(
               { dbd => 'SQLite' },
               { dbd => 'SQLite2' },
               { dbd => 'mysql', min_version => '4.0' },

       See Test::Database documentation for details about how to write a request.

   Testing on a development box
       The first systems on which you are going to test your module are the ones you own. On
       these system, it's up to you to configure the databases you want to make available.

       A typical ~/.test-database configuration file would look like this:

           dsn      = dbi:mysql:database=test
           username = root

           dsn      = dbi:Pg:database=test
           username = postgres

           dsn      = dbi:Oracle:test

       There is no need to add "dsn" sections for file-based drivers (at least the ones that have
       a corresponding Test::Database::Driver), since the module will automatically detect the
       available ones and create databases as needed.

       To find out which of the DBD that Test::Database supports are installed, use the following

           $ perl -MTest::Database -le 'print for Test::Database->list_drivers("available")'

       With no parameter, it will return the list of configured ones:

           $ perl -MTest::Database -le 'print for Test::Database->list_drivers()'


       The main goal of Test::Database from the point of view of a tester is: "configure once,
       test everything".

       As a CPAN tester, once you have installed Test::Database, you should edit the local
       equivalent of ~/.test-database for the user that will be running the CPAN test suites.

   "dsn" versus "driver_dsn"
       "dsn" sections define the information needed to connect to a single database. Any database
       listed here can be used by any test script that requests it.

       "driver_dsn" sections define the information needed to connect to a database engine (a
       "driver") with sufficient rights to run a "CREATE DATABASE" command. This allows
       Test::Database to create the databases on demand, thus ensuring every test suite will get
       a specific database.

       If you have file-based database engine, there is nothing to setup, as Test::Database is
       able to detect available file-based engines and use them as needed.

       Other database engines like "mysql" and "Pg" require a little more configuration. For
       example, here's the content of my ~/.test-database configuration file:

           driver_dsn  = dbi:mysql:
           username    = root

           driver_dsn  = dbi:Pg:
           username    = postgres

       For "Pg", I had to edit the pg_hba.cong file in /etc to make sure anyone would be able to
       connect as the "postgres" user, for example.

   Several test hosts accessing the same database engine
       If you have a large scale testing setup, you may want to setup a single MySQL or Postgres
       instance for all your test hosts, rather than one per test host.

       Databases created by Test::Database::Driver (using a configured "driver_dsn" have a name
       built after the following template: "tdd_driver_login_n", where driver is the DBD name,
       login is the login of the user running Test::Database and n a number that

       If the same database server is used by several host running Test::Database from the same
       user account, there is a race condition during with two different host may try to create
       the a database with the same name. A simple trick to avoid this is to add a "key" section
       to the ~/.test-database configuration file.

       If the "key" entry exists, the template used by Test::Database::Driver to create new
       databases is "tdd_driver_login_key_n".

   Cleaning the test drivers
       When given a "driver_dsn", Test::Database will use it to create a database for each test
       suite that requests one. Some mapping information is created to ensure the same test suite
       always receives a handle to the same database. (The mapping of test suite to database is
       based on the current working directory when Test::Database is loaded).

       After a while, your database engine may fill up with unused test databases.

       All drivers store their mapping information in the system's temporary directory, so the
       mapping information is relatively volatile, which implies more unused test databases (at
       least for non file-based drivers, since the file-based drivers store their database files
       in the system's temporary directory too).

       The following one-liner will list all the existing databases that were created by
       Test::Database in your configured drivers:

           perl -MTest::Database -le 'print join "\n  ", $_->name, $_->databases for Test::Database->drivers'

       Example output:


       The following one-liner will drop them all:

           perl -MTest::Database -le 'for$d(Test::Database->drivers){$d->drop_database($_)for$d->databases}'

       If a "key" has been defined in the configuration, only the databases corresponding to that
       key will be dropped.


       Test::Database currently supports the following DBD drivers: "CSV", "DBM", "mysql", "Pg",
       "SQLite2", "SQLite".

       Adding a new driver requires writing a corresponding Test::Database::Driver subclass,
       having the same name as the original "DBD" driver.

       An example module is provided in eg/, and the other drivers can also be used as
       an example. See also the WRITING A DRIVER FOR YOUR DATABASE OF CHOICE section in the
       documentation for Test::Database::Driver.


       The following ASCII-art graph shows where the Test::Database::Handle objects returned by
       the "handles()" method come from:

           ,-------------,      ,-------------,      ,--------------,
           |   DSN from  |      | File-based  |      | Drivers from |
           | config file |      |   drivers   |      |  config file |
           '-------------'      '-------------'      '--------------'
                  |                   |                      |
                  |                   |    ,-----------,     |
                  |                   '--->| Available |<----'
                  |                        |  drivers  |
                  |                        '-----------'
                  |                              |
                  |              ,-----------,   |
                  '------------->| Available |<--'
                                 |    DSN    |

       Here are a few details about the "handles()" method works:

       ·   Test::Database maintains a list of Test::Database::Handle objects computed from the
           DSN listed in the configuration.

           The handles matching the request are selected.

       ·   Test::Database also maintains a list of Test::Database::Driver objects computed from
           the list of supported file-based drivers that are locally available and from the list
           in the configuration file.

           The list of matching drivers is computed from the requests.  Each driver is then
           requested to provide an existing database (using its existing mapping information) or
           to create one if needed, and returns the corresponding Test::Database::Handle objects.

       ·   Finally, all the collected Test::Database::Handle objects are returned.

       So, without any configuration, Test::Database will only be able to provide file-based
       databases. It is also recommended to not put DSN or driver information for the file-based
       database engines that have a corresponding Test::Database::Driver class, since it will
       cause "handles()" to return several handles for the same database engine.


       Philippe Bruhat (BooK), "<>"


       Copyright 2009-2010 Philippe Bruhat (BooK), all rights reserved.


       You can redistribute this tutorial and/or modify it under the same terms as Perl itself.