NAME

Database test framework.

SYNOPSIS

use DBIx::Connection;

use Test::More tests => $tests; use Test::DBUnit connection_name => 'test'; DBIx::Connection->new( name => 'test', dsn => $ENV{DB_TEST_CONNECTION}, username => $ENV{DB_TEST_USERNAME}, password => $ENV{DB_TEST_PASSWORD}, );

use Test::More; use Test::DBUnit dsn => 'dbi:Oracle:localhost:1521/ORACLE_INSTANCE', username => 'user', password => 'password'; plan tests => $tests;

my $connection = test_connection(); my $dbh = test_dbh();

reset_schema_ok('t/sql/create_schema.sql');

populate_schema_ok('t/sql/create_schema.sql');

xml_dataset_ok('test1');

#you database operations here $connection->execute_statement("UPDATE ....");

expected_xml_dataset_ok('test1');

reset_sequence_ok('table1_seq1');

dataset_ok( table1 => [column1 => 'x', column1 => 'y'], table1 => [column1 => 'x1_X', column1 => 'y1_X'], ... table2 => [column1 => 'x2, column1 => 'y2'], table2 => [column1 => 'x1_N', column1 => 'y1_N'], );

#you database operations here $connection->execute_statement("UPDATE ....");

expected_dataset_ok( table1 => [column1 => 'z', column1 => 'y'], )

DESCRIPTION

Database test framework to verify that your database data match expected set of values.

Managing test data

Database tests should giving you complete and fine grained control over the test data that is used.

use Test::DBUnit dsn => $dsn, username => $username, password => $password;
reset_schema_ok('t/sql/create_schema.sql');
populate_schema_ok('t/sql/create_schema.sql');
reset_sequence('emp_seq');

Loading test data sets

Before you want to test your business logic it is essential to have repeatable snapshot of your
tables you want to test, so this module allows you populate/synchronize your database with
the passed in data structure or with the xml content.

dataset_ok(
    emp => [ename => "john", deptno => "10", job => "project manager"],
    emp => [ename => "scott", deptno => "10", job => "project manager"],
    bonus => [ename => "scott", job => "project manager", sal => "20"],
);
or
xml_dataset_ok('test1');
t/test_unit.test1.xml #given that you testing module is t/test_unit.t
<?xml version='1.0' encoding='UTF-8'?>
<dataset load_strategy="INSERT_LOAD_STRATEGY">
    <emp empno="1" ename="scott" deptno="10" job="project manager" />
    <emp empno="2" ename="john"  deptno="10" job="engineer" />
    <bonus ename="scott" job="project manager" sal="20" />
</dataset>

Getting connection to test database

my $connection = test_connection();
#business logic that change tested data comes here
....

Verifying test results

It can be useful to use data sets for checking the contents of a database after is has been modified by a test.
You may want to check the result of a update/insert/delete method or a stored procedure.

expected_dataset_ok(
    emp   => [empno => "1", ename => "Scott", deptno => "10", job => "project manager"],
    emp   => [empno => "2", ename => "John",  deptno => "10", job => "engineer"],
    emp   => [empno => "3", ename => "Mark",  deptno => "10", job => "sales assistant"],
    bonus => [ename => "scott", job => "project manager", sal => "20"],
)
or
xml_expected_dataset_ok('test1');
t/test_unit.test1-result.xml #given that you testing module is t/test_unit.t
<?xml version='1.0' encoding='UTF-8'?>
<dataset>
    <emp empno="1" ename="Scott" deptno="10" job="project manager" />
    <emp empno="2" ename="John"  deptno="10" job="engineer" />
    <emp empno="3" ename="Mark"  deptno="10" job="sales assistant" />
    <bonus ename="scott" job="project manager" sal="20" />
</dataset>

Configuring the dataset load strategy By default, datasets are loaded into the database using an insert load strategy. This means that all data in the tables that are present in the dataset is deleted, after which the test data records are inserted. Order in with all data is deleted depends on reverse table occurrence in the dataset, however you may force order of data by specifying empty table:

    table1 => [],  #this fore delete operation in occurrence order
    table1 => [col1 => 1, col2 => 'some data'],    
    or in xml file
    <table1 />
    <table1 col1="1" col2="some data"/>

In this strategy number of rows will be validated against datasets in (xml_)expexted_dataset_ok method.
Load strategy behavior is configurable,
it can be modified by calling:

set_insert_load_strategy();
or in XML
<?xml version='1.0' encoding='UTF-8'?>
<dataset load_strategy="INSERT_LOAD_STRATEGY">
    <emp empno="1" ename="Scott" deptno="10" job="project manager" />
    ....
</dataset>

set_refresh_load_strategy();
or in XML
<?xml version='1.0' encoding='UTF-8'?>
<dataset load_strategy="REFRESH_LOAD_STRATEGY">
    <emp empno="1" ename="Scott" deptno="10" job="project manager" />
    ....
</dataset>

The alternative to the insert load strategy is refresh load strategy.
In this case update on existing rows will take place or insert occurs if rows are missing.

Working with sequences You may use sequences or autogenerated features, so this module allows you handle that.

reset_sequence_ok('emp_seq');
or for MySQL
reset_sequence_ok('test_table_name')

#the ALTER TABLE test_table_name AUTO_INCREMENT = 1 will be issued
#Note that for MySQL the test_table_name must be empty.
or in XML
<?xml version='1.0' encoding='UTF-8'?>
<dataset reset_sequences="emp_seq, dept_seq">
    <emp empno="1" ename="Scott" deptno="10" job="project manager" />
    ....
</dataset>

Sequence tests with Oracle

t/sql/create_schema.sql
CREATE SEQUENCE emp_seq;
CREATE TABLE emp(
 empno      NUMBER NOT NULL,
 ename      VARCHAR2(10),
 job        VARCHAR2(20),
 mgr        NUMBER(4),
 hiredate   DATE,
 sal        NUMBER(7,2),
 comm       NUMBER(7,2),
 deptno     NUMBER(2),
 CONSTRAINT emp_pk PRIMARY KEY(empno),
 FOREIGN KEY (deptno) REFERENCES dept (deptno) 
);
CREATE OR REPLACE TRIGGER emp_autogen
BEFORE INSERT ON emp FOR EACH ROW
BEGIN
    IF :new.empno is null then
        SELECT emp_seq.nextval INTO :new.empno FROM dual;
    END IF;
END;

#unit test
reset_sequence_ok('emp_seq');

dataset_ok(
    emp => [ename => "John", deptno => "10", job => "project manager"],
    emp => [ename => "Scott", deptno => "10", job => "project manager"]
);

.... 

expected_dataset_ok(
    emp => [empno => 1, ename => "John", deptno => "10", job => "project manager"],
    emp => [empno => 2, ename => "Scott", deptno => "10", job => "project manager"]
)

Sequence tests with PostgreSQL

 t/sql/create_schema.sql
 CREATE SEQUENCE emp_seq;
 CREATE TABLE emp(
 empno      INT4 DEFAULT nextval('emp_seq') NOT NULL,
 ename      VARCHAR(10),
 job        VARCHAR(20),
 mgr        NUMERIC(4),
 hiredate   DATE,
 sal        NUMERIC(7,2),
 comm       NUMERIC(7,2),
 deptno     NUMERIC(2),
 CONSTRAINT emp_pk PRIMARY KEY(empno),
 FOREIGN KEY (deptno) REFERENCES dept (deptno) 
);

 #unit test
 reset_sequence_ok('emp_seq');
 ....

Autogenerated fileds tests with MySQL

 t/sql/create_schema.sql
 CREATE TABLE emp(
 empno     MEDIUMINT AUTO_INCREMENT, 
 ename      VARCHAR(10),
 job        VARCHAR(20),
 mgr        NUMERIC(4),
 hiredate   DATE,
 sal        NUMERIC(7,2),
 comm       NUMERIC(7,2),
 deptno     NUMERIC(2),
 CONSTRAINT emp_pk PRIMARY KEY(empno),
 FOREIGN KEY (deptno) REFERENCES dept (empno) 
);

 #unit test
 reset_sequence_ok('emp');

 dataset_ok(
     emp => [ename => "John", deptno => "10", job => "project manager"],
     emp => [ename => "Scott", deptno => "10", job => "project manager"]
 );

 .... 

 expected_dataset_ok(
     emp => [empno => 1, ename => "John", deptno => "10", job => "project manager"],
     emp => [empno => 2, ename => "Scott", deptno => "10", job => "project manager"]
 )

EXPORT

expected_data_set_ok dataset_ok expected_xml_dataset_ok xml_dataset_ok reset_schema_ok populate_schema_ok reset_sequence_ok set_refresh_load_strateg set_insert_load_strateg connnection dbh

by default.

METHODS

connection_name

reset_schema_ok

Tests database schema reset using sql file. Takes file name as parameter.

use Test::More tests => $tests; 
use Test::DBUnit dsn => $dsn, username => $username, password => $password;

...

reset_schema_ok('t/sql/create_schema.sql');

populate_schema_ok

Tests database schema population using sql file. Takes file name as parameter.

use Test::More tests => $tests; 
use Test::DBUnit dsn => $dsn, username => $username, password => $password;

...

populate_schema_ok('t/sql/populate_schema.sql');

reset_sequence_ok

Resets database sequence. Takes sequence name as parameter.
use Test::More tests => $tests; 
use Test::DBUnit dsn => $dsn, username => $username, password => $password;

...

reset_sequnce('table_seq1');

xml_dataset_ok

Tests database schema population/synch to the content of the xml file. Takes test unit name, that is used to resolve xml file name. Xml file name that will be loaded is build as follow <test_file>.<unit_name>.xml for instance the following invocation xml_dataset_ok('test1') from t/sub_dir/001_test.t file will expect t/sub_dir/001_test.test1.xml file.

expected_xml_dataset_ok

Validates expected database loaded from xml file against database schema. Takes test unit name, that is used to resolve xml file name. Xml file name that will be loaded is build as follow <test_file>.<unit_name>.xml unless you pass full xml file name. for instance the following invocation xml_dataset_ok('test1') from t/sub_dir/001_test.t file will expect t/sub_dir/001_test.test1.xml file.

dataset_ok

Tests database schema population/synch to the passed in dataset.

dataset_ok( table1 => [], #this deletes all data from table1 (DELETE FROM table1) table2 => [], #this deletes all data from table2 (DELETE FROM table2) table1 => [col1 => 'va1', col2 => 'val2'], #this insert or update depend on strategy table1 => [col1 => 'xval1', col2 => 'xval2'], )

expected_dataset_ok

Validates database schema against passed in dataset. expected_dataset_ok( table1 => [col1 => 'va1', col2 => 'val2'], )

test_connection

Returns test connection object.

test_dbh

Returns test database handler.

set_insert_load_strategy

Sets insert as the load strategy

set_refresh_load_strategy

Sets refresh as the load strategy

_xml_test_file

Returns xml file prefix to test

COPYRIGHT

The Test::DBUnit module is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.

AUTHOR

Adrian Witas, <adrian@webapp.strefa.pl</gt>

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)