Different ways to set up a database test in Perl

• Use a DBI wrapper subroutine to code up each SQL statement:
• pros: all the data is kept inside each test, and you can put Perl comments next to the SQL statements
• cons: you have to translate the SQL statements into Perl and back for testing/debugging

subtest 'a' {

    clear_db;
    db_wrapper( db => 'db1' sql =>'INSERT INTO table_a SET x = ?, y = ?', bind_values => [1, 2]);
    # do something
}

subtest 'b' {

    clear_db;
    db_wrapper( db => 'db1' sql =>'INSERT INTO table_a SET x = ?, y = ?', bind_values => [1, 3]);

    # do something

}

• Put all the statements inline, in the DATA section, and use a special subroutine to read them all:
• pros: the database statements stay in SQL format
• cons: you have to use different ID numbers for each test, 

setup_db_once( data => \*DATA );
...

subtest 'a' { # do something with first row }

subtest 'b' { # do something with second row }

...
__DATA__

INSERT INTO table_a SET x = 1, y = 2

INSERT INTO table_a SET x = 5, y = 6

• Use a DBI wrapper subroutine to read SQL statements from an inline heredoc:
• all the pros and none of the cons

subtest 'a' {

    clear_db;
    setup_db_per_test( data => <<EOM

INSERT INTO table_a SET x = 1, y = 2

EOM;
    # do something
}

subtest 'b' {

    clear_db;

    setup_db_per_test( data => <<EOM

INSERT INTO table_a SET x = 1, y = 3

EOM;

    # do something

}