.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.40)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "Test::Database::Tutorial 3pm"
.TH Test::Database::Tutorial 3pm "2021-11-12" "perl v5.32.1" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
Test::Database::Tutorial \- How to use Test::Database
.SH "INTRODUCTION"
.IX Header "INTRODUCTION"
The goal of the Test::Database module is to provide easy to use test
databases for test scripts that need them.
.SS "The problem"
.IX Subsection "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.
.PP
Most of those methods have pros and cons:
.IP "\(bu" 4
using SQLite
.Sp
No setup needed, but the test script can only use SQLite's dialect of \s-1SQL.\s0
So much for portability across database engines.
.IP "\(bu" 4
using environment variables
.Sp
The environment variables are different for every module to test, and
usually only the main developers/testers know about them. Since most of
the \s-1CPAN\s0 testers probably don't bother setting them up, these modules
are most certainly undertested.
.IP "\(bu" 4
using default credentials
.Sp
Typically using \f(CW\*(Aqroot\*(Aq\fR and \f(CW\*(Aq\*(Aq\fR to connect to the \f(CW\*(C`test\*(C'\fR 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).
.IP "\(bu" 4
setting up the database by itself
.Sp
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!
.SS "A solution: Test::Database"
.IX Subsection "A solution: Test::Database"
Many modules use a database to store their data, and often support
several database engines.
.PP
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?
.PP
This is the goal of the Test::Database module. It supports:
.IP "\(bu" 4
getting \s-1DSN\s0 information from a list of pre-configured database and engines
.IP "\(bu" 4
automatic detection of \*(L"file-based\*(R" database engines (typically, SQLite).
.PP
The rest of this document describes various use cases for Test::Database.
.SH "MODULE AND TEST AUTHOR"
.IX Header "MODULE AND TEST AUTHOR"
Test::Database has a single interface for test authors:
.PP
.Vb 1
\&    my @handles = Test::Database\->handles( @requests );
.Ve
.PP
\&\f(CW@request\fR is a list of \*(L"requests\*(R" for databases handles. Requests
must declare the \s-1DBD\s0 they expect, and can optionally add version-based
limitations (only available for drivers supported by Test::Database).
.PP
The handles returned are objects of the Test::Database::Handle class.
.PP
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.
.PP
Test::Database can return two types of databases handles:
.IP "\(bu" 4
either a handle to a newly created database (created especially at the
test script's request)
.IP "\(bu" 4
or a handle to an already existing database
.PP
There is no way for the test script to tell the difference.
.PP
In any case, the database is assumed to provide \f(CW\*(C`DROP TABLE\*(C'\fR and
\&\f(CW\*(C`CREATE TABLE\*(C'\fR rights, and the test script is by definition allowed
to do whatever it pleases with the tables that exist in the database.
.PP
Note that Test::Database supports any \s-1DSN,\s0 not just those for which
it has a driver. If your module supports Oracle, you can add \f(CW\*(AqOracle\*(Aq\fR
to your list of requests, and if the host owner configured a \f(CW\*(C`dsn\*(C'\fR
pointing at an Oracle database, then it will be available for your tests.
.SS "Specific database support"
.IX Subsection "Specific database support"
It is possible to request specific versions of a database engine.
.PP
.Vb 1
\&    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 => \*(AqSQLite\*(Aq },
\&        { dbd => \*(AqSQLite2\*(Aq },
\&        { dbd => \*(Aqmysql\*(Aq, min_version => \*(Aq4.0\*(Aq },
\&    );
.Ve
.PP
See Test::Database documentation for details about how
to write a request.
.SS "Testing on a development box"
.IX Subsection "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.
.PP
A typical \fI~/.test\-database\fR configuration file would look like this:
.PP
.Vb 2
\&    dsn      = dbi:mysql:database=test
\&    username = root
\&
\&    dsn      = dbi:Pg:database=test
\&    username = postgres
\&
\&    dsn      = dbi:Oracle:test
.Ve
.PP
There is no need to add \f(CW\*(C`dsn\*(C'\fR 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.
.PP
To find out which of the \s-1DBD\s0 that Test::Database supports are
installed, use the following one-liner:
.PP
.Vb 4
\&    $ perl \-MTest::Database \-le \*(Aqprint for Test::Database\->list_drivers("available")\*(Aq
\&    DBM
\&    SQLite
\&    mysql
.Ve
.PP
With no parameter, it will return the list of configured ones:
.PP
.Vb 3
\&    $ perl \-MTest::Database \-le \*(Aqprint for Test::Database\->list_drivers()\*(Aq
\&    DBM
\&    SQLite
.Ve
.SH "CPAN TESTER"
.IX Header "CPAN TESTER"
The main goal of Test::Database from the point of view of a tester
is: \*(L"configure once, test everything\*(R".
.PP
As a \s-1CPAN\s0 tester, once you have installed Test::Database, you
should edit the local equivalent of \fI~/.test\-database\fR for the
user that will be running the \s-1CPAN\s0 test suites.
.ie n .SS """dsn"" versus ""driver_dsn"""
.el .SS "\f(CWdsn\fP versus \f(CWdriver_dsn\fP"
.IX Subsection "dsn versus driver_dsn"
\&\f(CW\*(C`dsn\*(C'\fR 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.
.PP
\&\f(CW\*(C`driver_dsn\*(C'\fR sections define the information needed to connect to a
database engine (a \*(L"driver\*(R") with sufficient rights to run a
\&\f(CW\*(C`CREATE DATABASE\*(C'\fR command. This allows Test::Database to create the
databases on demand, thus ensuring every test suite will get a specific
database.
.PP
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.
.PP
Other database engines like \f(CW\*(C`mysql\*(C'\fR and \f(CW\*(C`Pg\*(C'\fR require a little more
configuration. For example, here's the content of my \fI~/.test\-database\fR
configuration file:
.PP
.Vb 2
\&    driver_dsn  = dbi:mysql:
\&    username    = root
\&    
\&    driver_dsn  = dbi:Pg:
\&    username    = postgres
.Ve
.PP
For \f(CW\*(C`Pg\*(C'\fR, I had to edit the \fIpg_hba.cong\fR file in \fI/etc\fR to make sure
anyone would be able to connect as the \f(CW\*(C`postgres\*(C'\fR user, for example.
.SS "Several test hosts accessing the same database engine"
.IX Subsection "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.
.PP
Databases created by Test::Database::Driver (using a configured
\&\f(CW\*(C`driver_dsn\*(C'\fR have a name built after the following template:
\&\f(CW\*(C`tdd_\f(CIdriver\f(CW_\f(CIlogin\f(CW_\f(CIn\f(CW\*(C'\fR, where \fIdriver\fR is the \s-1DBD\s0 name, \fIlogin\fR
is the login of the user running Test::Database and \fIn\fR a number that
.PP
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 \f(CW\*(C`key\*(C'\fR section
to the \fI~/.test\-database\fR configuration file.
.PP
If the \f(CW\*(C`key\*(C'\fR entry exists, the template used by Test::Database::Driver
to create new databases is \f(CW\*(C`tdd_\f(CIdriver\f(CW_\f(CIlogin\f(CW_\f(CIkey\f(CW_\f(CIn\f(CW\*(C'\fR.
.SS "Cleaning the test drivers"
.IX Subsection "Cleaning the test drivers"
When given a \f(CW\*(C`driver_dsn\*(C'\fR, 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).
.PP
After a while, your database engine may fill up with unused test databases.
.PP
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).
.PP
The following one-liner will list all the existing databases that were
created by Test::Database in your configured drivers:
.PP
.Vb 1
\&    perl \-MTest::Database \-le \*(Aqprint join "\en  ", $_\->name, $_\->databases for Test::Database\->drivers\*(Aq
.Ve
.PP
Example output:
.PP
.Vb 12
\&    CSV
\&      tdd_csv_book_0
\&      tdd_csv_book_1
\&    DBM
\&    SQLite
\&      tdd_sqlite_book_0
\&      tdd_sqlite_book_1
\&    SQLite2
\&      tdd_sqlite2_book_0
\&    mysql
\&      tdd_mysql_book_0
\&      tdd_mysql_book_1
.Ve
.PP
The following one-liner will drop them all:
.PP
.Vb 1
\&    perl \-MTest::Database \-le \*(Aqfor$d(Test::Database\->drivers){$d\->drop_database($_)for$d\->databases}\*(Aq
.Ve
.PP
If a \f(CW\*(C`key\*(C'\fR has been defined in the configuration, only the databases
corresponding to that key will be dropped.
.SH "ADDING SUPPORT FOR A NEW DATABASE ENGINE"
.IX Header "ADDING SUPPORT FOR A NEW DATABASE ENGINE"
Test::Database currently supports the following \s-1DBD\s0 drivers:
\&\f(CW\*(C`CSV\*(C'\fR, \f(CW\*(C`DBM\*(C'\fR, \f(CW\*(C`mysql\*(C'\fR, \f(CW\*(C`Pg\*(C'\fR, \f(CW\*(C`SQLite2\*(C'\fR, \f(CW\*(C`SQLite\*(C'\fR.
.PP
Adding a new driver requires writing a corresponding
Test::Database::Driver subclass, having the same name as the original
\&\f(CW\*(C`DBD\*(C'\fR driver.
.PP
An example module is provided in \fIeg/MyDriver.pm\fR, and the other
drivers can also be used as an example. See also the \fI\s-1WRITING A
DRIVER FOR YOUR DATABASE OF CHOICE\s0\fR section in the documentation for
Test::Database::Driver.
.SH "WHERE DO DSN COME FROM?"
.IX Header "WHERE DO DSN COME FROM?"
The following ASCII-art graph shows where the Test::Database::Handle
objects returned by the \f(CW\*(C`handles()\*(C'\fR method come from:
.PP
.Vb 10
\&    ,\-\-\-\-\-\-\-\-\-\-\-\-\-,      ,\-\-\-\-\-\-\-\-\-\-\-\-\-,      ,\-\-\-\-\-\-\-\-\-\-\-\-\-\-,
\&    |   DSN from  |      | File\-based  |      | Drivers from |
\&    | config file |      |   drivers   |      |  config file |
\&    \*(Aq\-\-\-\-\-\-\-\-\-\-\-\-\-\*(Aq      \*(Aq\-\-\-\-\-\-\-\-\-\-\-\-\-\*(Aq      \*(Aq\-\-\-\-\-\-\-\-\-\-\-\-\-\-\*(Aq
\&           |                   |                      |
\&           |                   |    ,\-\-\-\-\-\-\-\-\-\-\-,     |
\&           |                   \*(Aq\-\-\->| Available |<\-\-\-\-\*(Aq
\&           |                        |  drivers  |
\&           |                        \*(Aq\-\-\-\-\-\-\-\-\-\-\-\*(Aq
\&           |                              |
\&           |              ,\-\-\-\-\-\-\-\-\-\-\-,   |
\&           \*(Aq\-\-\-\-\-\-\-\-\-\-\-\-\->| Available |<\-\-\*(Aq
\&                          |    DSN    |
\&                          \*(Aq\-\-\-\-\-\-\-\-\-\-\-\*(Aq
.Ve
.PP
Here are a few details about the \f(CW\*(C`handles()\*(C'\fR method works:
.IP "\(bu" 4
Test::Database maintains a list of Test::Database::Handle objects
computed from the \s-1DSN\s0 listed in the configuration.
.Sp
The handles matching the request are selected.
.IP "\(bu" 4
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.
.Sp
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.
.IP "\(bu" 4
Finally, all the collected Test::Database::Handle objects are returned.
.PP
So, without any configuration, Test::Database will only be able to
provide file-based databases. It is also recommended to \fBnot\fR put \s-1DSN\s0
or driver information for the file-based database engines that have
a corresponding Test::Database::Driver class, since it will cause
\&\f(CW\*(C`handles()\*(C'\fR to return several handles for the same database engine.
.SH "AUTHOR"
.IX Header "AUTHOR"
Philippe Bruhat (BooK), \f(CW\*(C`<book@cpan.org>\*(C'\fR
.SH "COPYRIGHT"
.IX Header "COPYRIGHT"
Copyright 2009\-2010 Philippe Bruhat (BooK), all rights reserved.
.SH "LICENSE"
.IX Header "LICENSE"
You can redistribute this tutorial and/or modify it under the same terms
as Perl itself.