1Test::SQL::Data(3)    User Contributed Perl Documentation   Test::SQL::Data(3)
2
3
4

NAME

6       Test::SQL::Data - Helps running SQL tests: database preparing and
7       result matching
8

SYNOPSYS

10         use Test::More;
11         use Test::SQL::Data;
12
13         my $test = Test::SQL::Data->new() or exit;
14
15         use_ok('My::Module');
16         my $n_matches = 1;
17
18         $My::Module->run_something( dbh => $test->dbh );
19
20         $n_matches += $test->match_table('tablename','expected_tablename.sql');
21
22         done_testing($n_matches);
23

DESCRIPTION

25       The purpose of Test::SQL::Data is to give your module a clean database
26       to work with. When the module loads it prepares the database. You can
27       have it empty or pre-load some SQL code before running your tests. Then
28       you can use the module again to check if your expected results match
29       the contents of the tables of the database.
30
31   Clean Database for each test
32       This module gives a clean database connection for each test. It can be
33       completely empty or you can make it load some tables and rows to run
34       your code against.
35
36       You don't need to have a SQL server in the computer you run the tests.
37       It is backed on SQLite so you only need to install the module
38       DBD::SQLite to use it. It is the only driver supported by now.
39
40       The database contents are not removed after the test, so you can
41       inspect the results. It will only be erased the next time you run the
42       test.
43
44   Database test connection
45       After the creation of the database at the construction of the test, you
46       are given a connection to the test database. Both DBI and
47       DBIx::Connector object are available and you have to use one of them to
48       call to the module you want to test.
49
50   Table matching
51       If you want to match the results, you can either issue SQL select
52       statements or use the Test::SQL::Data API to match large amounts of
53       information. To do so, you have to provide expected SQL dumps of
54       tables. Each field of every row of the tables will be matched and raise
55       an ok or not_ok. The number of tests run will be returned.
56
57       At the end of the test you must call done_testing from Test::More with
58       the number of tests run.
59
60   REQUIREMENTS
61       •   DBD::SQLite
62
63       •   DBIx::Connector
64
65       •   Test::More
66

CONSTRUCTOR

68   new
69       Creates a new database file with SQLite and returns a new test object.
70       If some required module is missing it raises a skip.
71
72         my $test_sql = Test::SQL::Data->new();
73
74         my $test_sql = Test::SQL::Data->new( config => "t/etc/test_config.cfg");
75
76         my $test_sql = Test::SQL::Data->new( require => 'Some::Package');
77         my $test_sql = Test::SQL::Data->new( require => ['Some::Package'
78                                                   ,'Another::Package']);
79
80       See Test Configuration bellow to learn how to easily pre-load SQL data
81       in the empty database it creates.
82

METHODS

84   connect
85       Connects to database. It is not necessary to do this. It is executed at
86       new().
87
88           my $connection = connect();
89
90           my $connection = connect('t/file.db');
91
92   sql
93       Runs SQL in the internal test database.
94
95           $test_sql->sql('CREATE TABLE a (field_one int, ... )');
96
97   load_sql_file
98       Loads SQL statements into the temporary database from file.
99
100       parameters
101
102       •   SQL file
103
104   file_db
105       Returns the current database file we are using to store data. So far it
106       is a SQLite database.
107
108   dbh
109       Returns the current database handler DBI
110
111   connector
112       Returns the current DBIx::Connector
113
114   match_table
115       Matches all the fields and rows of a table with an expected SQL data.
116       The SQL file must have the tablename with the prefix expected, and
117       insert statements as rows to match against:
118
119       parameters
120
121       •   table
122
123       •   expected_sql_file
124
125       returns
126
127       number of ok matches
128
129       expected_sql_file example
130
131           /* etc/expected_something.sql; */
132           CREATE TABLE(expected_something) ( id integer, name char(10) );
133           INSERT INTO expected_something VALUES( 3,'foo' );
134
135       This will try to match the same rows in the table something and it will
136       return a 2 if succeded. That comes from
137       number_of_rows*number_of_fields.  One way to generate those files is
138       using the .dump command in sqlite and then edit the output:
139
140           sqlite t/db/something.db .dump > t/etc/expected_something.sql
141
142   dir_db
143       Returns the directory where it saves the temporary database
144

TESTS CONFIGURATION

146   One SQL file per test
147       On starting, it loads into the just created database the sql file
148       related to the test.  It searches for a file in the t/etc directory
149       called like the test but ended with the extension sql.
150
151       ie: t/35_foo.t -> t/etc/35_foo.sql
152
153       Multiple SQL statements can be declared in the sql file. So you can put
154       a CREATE TABLE, then do some inserts or whatever.
155
156   Multiple SQL files
157       Instead of a single sql file, sometimes you want to execute other sql
158       files from other tests. You can create a file in
159       t/etc/name_of_the_test.cfg. Add there a list of SQL files to run:
160
161       t/etc/40_bar.cfg
162
163       sql:
164         - 35_foo.sql
165         - 55_whoosa.sql
166
167       At the creation of the object it will search for a file called
168       t/etc/name_of_the_test.cfg and it will be used as a config. You can
169       also pass it to the constructor:
170
171         my $test_sql = Test::SQL::Data->new( config => "t/etc/another_config_file.cfg");
172

Dumping data from other DataBases

174   MySQL
175       Contents of MySQL tables can be dumped and used for the tests. After
176       the dump, you may have to manually edit the contents of the SQL file to
177       be loaded in the SQLite backend.
178
179         $ mysqldump --compatible=ansi --skip-extended-insert --compact database table
180
181       Manual Changes
182
183       •   Auto Increment field: id_foo integer primary key autoincrement
184
185       •   There is no unsigned int, use integer
186
187       •   There is no enum, use varchar
188
189       •   Timestamps:  "date_updated" datetime default current_timestamp
190
191       •   Escape ' with '' instead \'
192

Debugging

194       You can inspect the execution doing:
195
196         DEBUG=1 make test
197
198       The result SQLite database for each test is in t/db_name_of_test.db
199

SEE ALSO

201       •   Test::More
202

AUTHOR

204       Francesc Guasch <frankie@etsetb.upc.edu>
205
207       This software is copyright (c) 2013 by Francesc Guasch.
208
209       This is free software; you can redistribute it and/or modify it under
210       the same terms as the Perl 5 programming language system itself.
211
212
213
214perl v5.38.0                      2023-07-21                Test::SQL::Data(3)
Impressum