Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
Test::Database::TutoriUser)Contributed Perl DocumenTest::Database::Tutorial(3)

       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:

       o   using SQLite

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

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

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

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

       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)

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

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

       o   automatic detection of "file-based" database	engines	(typically,

       The rest	of this	document describes various use cases for

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

       o   either a handle to a	newly created database (created	especially at
	   the test script's request)

       o   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

   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

   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 one-liner:

	   $ 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

       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

       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:

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

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

       o   Finally, all	the collected Test::Database::Handle objects are

       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.

perl v5.32.1			  2014-05-24	   Test::Database::Tutorial(3)


Want to link to this manual page? Use this URL:

home | help