Re: Testing database updates.

[Corion]

in reply to Testing database updates.

Personally, I really like to use DBD::SQLite with a :memory: database for each test. For each test case, I load the database mostly using DBIx::RunSQL, then run the test modifications against it and then run query statements as tests that check the database status for the "interesting" results.

A typical test file looks like this:

AUFGABENBEDARFZEITRAUM Werte durch Trigger
---
SELECT
    Aufgabe,
    Anfang,
    Ende,
    id as aufgabenbedarf,
    werktage,
    tagesbedarf,
    tagesbedarf_mak
FROM t_aufgabenbedarf --AUFGABENBEDARFZEITRAUM_V
order by aufgabe
---
[
[
  '301',
  '2011-10-01 00:00:00',
  '2011-10-31 00:00:00',
  '141',
  '20',
  '28080',
  '1.04545454545455'
],
[
  '302',
  '2011-10-01 00:00:00',
  '2011-10-31 00:00:00',
  '142',
  '20',
  '14040',
  '0.522727272727273'
],
[
  '303',
  '2011-10-01 00:00:00',
  '2011-10-31 00:00:00',
  '143',
  '20',
  '14040',
  '0.522727272727273'
]]

It is basically the test title, test query and the expected results, as I have to test many views that should remain constant.

The driver for these tests is (abridged) the following program, with some Oracle-to-SQLite converter ripped out for brevity:

#!perl -w
use strict;
use DBD::SQLite;
use File::Glob qw(bsd_glob);
use Getopt::Long;
use DBIx::RunSQL;
use Test::More;
use Data::Dumper;

GetOptions(
    'verbose|v' => \my $verbose,
    'db:s' => \my @ora,
    'commit-prepare' => \my $do_commit,
    'sqlite:s' => \my $dbfile,
);
$dbfile ||= ':memory:';

my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", undef, undef,
    { AutoCommit => 0, PrintError => 0, RaiseError => 1 }
);
$dbh->do('PRAGMA synchronous = OFF;');

print "# Using DBD::SQLite $DBD::SQLite::VERSION on Perl $^V\n"
    if $verbose;

my (@sql_files) = @ARGV;
@sql_files = sort map { bsd_glob $_ } @sql_files;

# First read the SQL files that set up this test case
my @setup = grep /\.sql$/, @sql_files;
push @setup, 'tests/compat-triggers.sql';

# And separate out the test files that run against this test case
my @tests =  grep /\.t$/, @sql_files;

# Also find some global invariant tests that should hold true for
# any test case. These are mostly sanity tests that the database
# was constructed correctly
my @invarianten = glob "tests/*.invariante";

if (@tests) {
    unshift @tests, @invarianten;
    plan tests => 0+@tests;
};

if (@ora) {
    load_oracle($_) for @ora;
    
    create_views();
    $dbh->commit;
};

for my $prepare (@setup){
    DBIx::RunSQL->run_sql_file(
        dbh => $dbh,
        sql => $prepare,
        verbose => $verbose,
    );
    $dbh->commit if $do_commit;
};

for my $test (@tests) {
    open my $fh, '<', $test
        or die "$test: $!";
    local $/ = "---";
    chomp( my ($description,$sql,$expected) = <$fh>);
    $description =~ s/\s+$//;
    
    
    my $res = eval { $dbh->selectall_arrayref($sql) };
    if( $@ ) {
        die "$@ when running \n$sql";
    };
    if(! is_deeply($res, eval $expected, $description)) {
        diag Dumper eval $expected;
        diag Dumper $res;
    };
}