The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
Test-DBUnit-0.20
River stage one • 1 direct dependent • 1 total dependent
1 ++
/ DBUnit

NAME

DBUnit - Database testing API

SYNOPSIS

    use DBUnit ':all';

    my $dbunit = DBUnit->new(connection_name => 'test');
    $dbunit->reset_schema($script);
    $dbunit->populate_schema($script);

    $dbunit->dataset(
        emp   => [empno => 1, ename => 'scott', deptno => 10],
        emp   => [empno => 2, ename => 'john', deptno => 10],
        bonus => [ename => 'scott', job => 'consultant', sal => 30],
    );
    #business logic here

    my $differences = $dbunit->expected_dataset(
        emp   => [empno => 1, ename => 'scott', deptno => 10],
        emp   => [empno => 2, ename => 'John'],
        emp   => [empno => 2, ename => 'Peter'],
    );

    $dbunit->reset_sequence('emp_seq');

    $dbunit->xml_dataset('t/file.xml');

    $dbunit->expected_xml_dataset('t/file.xml');

LOBs support (Large Object)

This code snippet will populate database blob_content column with the binary data pointed by file attribute, size of the lob will be stored in size_column

    $dbunit->dataset(
        emp   => [empno => 1, ename => 'scott', deptno => 10],
        image  => [id => 1, name => 'Moon'
            blob_content => {file => 'data/image1.jpg', size_column => 'doc_size'}
        ]
    );

This code snippet will validate database binary data with expected content pointed by file attribute,

    $dbunit->expected_dataset(
        emp   => [empno => 1, ename => 'scott', deptno => 10],
        image => [id => 1, name => 'Moon'
            blob_content => {file => 'data/image1.jpg', size_column => 'doc_size'}
        ]
    );
    or xml
    <dataset>
        <emp .../>
        <image id=>"1" name="Moon">
            <blob_content  file="t/bin/data1.bin" size_column="doc_size" />
        </image>
    </dataset>

DESCRIPTION

Database test framework to verify that your database data match expected set of values. It has ability to populate dataset and expected set from xml files.

EXPORT

None by default. reset_schema populate_schema expected_dataset expected_xml_dataset dataset xml_dataset by tag 'all'

ATTRIBUTES

connection_name
load_strategy

INSERT_LOAD_STRATEGY(default) Deletes all data from tables that are present in test dataset in reverse order unless empty table without attribute is stated, that force deletion in occurrence order In this strategy expected dataset is also tested against number of rows for all used tables.

REFRESH_LOAD_STRATEGY Merges (update/insert) data to the given dataset snapshot. In this scenario only rows in expected dataset are tested.

primary_key_definition_cache

This option is stored as hash_ref: the key is the table name with the schema prefix and value is stored as array ref of primary key column names.

METHODS

reset_schema

Resets schema

    $dbunit->reset_schema;
populate_schema

Populates database schema.

dataset

Synchronizes/populates database to the passed in dataset.

    $dbunit->dataset(
        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

Validates database schema against passed in dataset. Return differences report or undef is there are not discrepancies.

    my $differences = $dbunit->expected_dataset(
        table1 => [col1 => 'va1', col2 => 'val2'],
        table1 => [col1 => 'xval1', col2 => 'xval2'],
    );
reset_sequence

Resets passed in sequence

    $dbunit->reset_sequence('emp_seq');
throws

Returns errorcode, error message for the specified sql or plsql code.

    my ($error_code, $error_message) = $dbunit->throws(
        "INSERT INTO emp(empno, ename) VALUES (NULL, 'Smith')"
    );
execute

Returns hash reference where keys are the bind variables

    my $plsql = "SELECT NOW() INTO :var";
    my $result = $dbunit->execute($plsql);
    my $result = $dbunit->execute($plsql, $bind_variables_definition);

See DBIx::Connection for more detail

SCHEMA TEST METHODS

    The following methods check for existence.of the particular database
    schema objects like table, column, index, triggers,
    function, procedures packages.
has_table

Returns true if the specified table exists.

    $dbunit->has_table($schema, $table);
    $dbunit->has_table($table);
has_view

Returns true if the specified view exists.

    $dbunit->has_view($schema, $view);
    $dbunit->hasnt_table($view);
has_column

Returns true if the specified column for given table exists.

    $dbunit->has_column($schema, $table, $columm);
    $dbunit->has_column($table, $columm);
has_columns

Returns true if all specified columns exist for given table otherwise undef. Check additionally failed_test_info method.

    my $columms = ['id', 'name']
    $dbunit->has_columns($schema, $table, $columms);
    $dbunit->has_column($table, $columms);
column_is_null

Returns true if the specified column for given table can be nullable.

    $dbunit->column_is_null($schema, $table, $columm);
    $dbunit->column_is_null($table, $columm);
column_is_not_null

Returns true if the specified column for given table cant be nullable.

    $dbunit->column_is_not_null($schema, $table, $columm);
    $dbunit->column_is_not_null($table, $columm);
_check_type_family

Checks data type families, tests if the specified testes type belongs to the same group as db_type (or dbi type) There are currently the following synonyms for the families

    - 'TEXT', 'VARCHAR', 'CHARACTER VARYING', 'VARCHAR2'
    - 'BPCHAR', 'CHAR', 'CHARACTER'
    - 'NUMERIC', 'FLOAT'
_data_type_aliases
_match_data_type

Returns undef if the specified data type matches underlying database type otherwise type name

column_type_is

Returns true if the specified column's type for given table matches underlying column type otherwise undef; Check additionally failed_test_info method.

    $dbunit->column_type_is($schema, $table, $columm, $type);
    $dbunit->column_type_is($table, $columm, $type);
column_default_is

Returns true if the specified column's default value matches database definition otherwise undef. Check additionally failed_test_info.

    $dbunit->column_default_is($schema, $table, $columm, $default);
    $dbunit->column_default_is($table, $columm, $default);
column_is_unique

Returns true if the specified column for given table has unique constraint.

    $dbunit->column_is_unique($schema, $table, $column);
    $dbunit->column_is_unique($table, $column);
has_pk

Returns true if the specified column or columns are part of the primary key for the given table.

    my $columns = ['id']; #or my $columns = ['master_id', 'seq_no']; 

    $dbunit->has_pk($table, $columns);
    $dbunit->has_pk($schema, $table, $columns);


    $dbunit->has_pk($table, $column);
    $dbunit->has_pk($schema, $table, $column);

    $dbunit->has_pk($table);
    $dbunit->has_pk($schema, $table);
has_fk

Returns true if the specified column or columns for given table are part of the foreign key for the referenced table.

    my $columns = ['id']; #or my $columns = ['master_id', 'seq_no']; 
    $dbunit->has_fk($schema, $table, $columns, $referenced_schema, $referenced_table);
    $dbunit->has_fk($table, $columns, $referenced_table);
has_index

Returns true if the specified column or columns are part of the index for the given table.

    my $columns = ['id']; #or my $columns = ['master_id', 'seq_no']; 

    $dbunit->has_index($table, $index, $column_or_expressions);
    $dbunit->has_index($schema, $table, $index, $column_or_expressions);

    $dbunit->has_index($table, $index, $columns);
    $dbunit->has_index($schema, $table, $index, $columns);    
    
    $dbunit->has_index($table, $index);
    $dbunit->has_index($schema, $table, $index);
index_is_unique

Returns true if the specified index is unique.

    $dbunit->index_is_unique($schema, $table, $index);
    $dbunit->index_is_unique($table, $index);
index_is_primary

Returns true if the specified index is primary key.

    $dbunit->index_is_primary($schema, $table, $index);
    $dbunit->index_is_primary($table, $index);
index_is_type

Returns true if the specified index's type is the index type from underlying database, otherwise undef. Check additionally failed_test_info method.

    $dbunit->index_is_type($schema, $table, $index, $type);
    $dbunit->index_is_type($table, $index, $type);
has_trigger

Returns true if the specified trigger exists for the given table.

    $dbunit->has_trigger($schema, $table, $trigger);
    $dbunit->has_trigger($table, $trigger);
has_sequence

Returns true if the specified sequence exists.

trigger_is

Returns true if the specified trigger body matches the trigger body (or function in case of postgresql) for given table, otherwise undef check additionally failed_test_info method.

    $dbunit->trigger_is($schema, $table, $trigger, $trigger_body);
    $dbunit->trigger_is($table, $trigger, $trigger_body);
has_routine

Returns true if the specified routine exists and have matched prototype

    my $args = ['type1', 'type2', 'return_type'];
    or
    my $args = ['IN type1', 'OUT type2', 'type3'];
    or
    my $args = ['name1 type1', 'name2 type2', 'return type3'];
    or
    my $args = ['IN name1 type1', 'INOUT name2 type2', 'return type3'];
    
    $dbunit->has_routine($schema, $function);
    $dbunit->has_routine($function);
    $dbunit->has_routine($schema, $function, $args);
    $dbunit->has_routine($function, $args);

In case of testing function arguments, the last one is the function return type. Check additionally failed_test_info method.

_validate_routine_argument
_set_failed_test_info
failed_test_info

Stores the last failed test detail.

routine_is

Returns true if the specified function matches passed in body

    $dbunit->has_routine($schema, $function, $args, $routine_body);
    $dbunit->has_routine($function, $args. $routine_body);
xml_dataset

Loads xml file to dataset and populates/synchronizes it to the database schema. Takes xml file as parameter.

    <dataset load_strategy="INSERT_LOAD_STRATEGY" reset_sequences="emp_seq">
        <emp ename="scott" deptno="10" job="project manager" />
        <emp ename="john"  deptno="10" job="engineer" />
        <emp ename="mark"  deptno="10" job="sales assistant" />
        <bonus ename="scott" job="project manager" sal="20" />
    </dataset>
expected_xml_dataset

Takes xml file as parameter. Return differences report or undef is there are not discrepancies.

apply_properties

Sets properties for this object.

PRIVATE METHODS

rows_to_insert
drop_objects

Removes existing schema

create_tables
objects_to_create

Returns list of pairs values('object_type object_name', create_sql, ..., 'object_typeN object_nameN', create_sqlN)

insert

Inserts data

merge

Merges passed in data

update

Updates table values.

has_primary_key_values

Returns true if passed in dataset have primary key values

primary_key_values

Returns primary key values, Takes table name, hash ref as fields of values, db connection object.

delete_data

Deletes data from passed in tables.

tables_to_delete

Returns list of tables to delete.

empty_tables_to_delete

Returns list of table that are part of dataset table and are represented by table without attributes

  table1 => [],

  or in xml file

  <table1 />
expected_dataset_for_insert_load_strategy

Validates expected dataset for the insert load strategy.

_update_lobs

Updates lobs.

_exp_table_with_column

Return hash ref of the tables with it columns.

_extract_column_values
_extract_column_values
_process_lob
validate_number_of_rows

Validates number of rows.

validate_dataset

Validates passed exp dataset against fetched rows. Return undef if there are not difference otherwise returns validation error.

validate_lobs

Validates lob values

expected_dataset_for_refresh_load_strategy

Validates expected dataset for the refresh load strategy.

count_table_rows

Return number of the table rows,

    my $no_rows = $dbunit->has_empty_table($table, $connection);
validate_expexted_dataset

Validates passed exp dataset against database schema Return undef if there is not difference otherwise returns validation error.

compare_datasets

Compares two dataset hashes using passed in keys Returns undef if there is not difference, otherwise difference details.

format_values

Converts passed in list to string.

retrive_tables_data

Returns retrieved data for passed in tables

retrive_table_data

Returns retrieved data for passed in table.

primary_key_hash_value

Returns primary key values hash.

xml_dataset_handler
_exists_in_database

Check is rows exists in database. Takes table name, hash ref of field values, connection object

load_xml

Loads xml

_load_file_content

TODO

Extend detection for complex plsql blocks in the objects_to_create method.

COPYRIGHT AND LICENSE

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

SEE ALSO

DBIx::Connection

AUTHOR

Adrian Witas, adrian@webapp.strefa.pl