1Test::SQL::Data(3) User Contributed Perl Documentation Test::SQL::Data(3)
2
6 Test::SQL::Data - Helps running SQL tests: database preparing and
7 result matching
8
10 use Test::More;
11 use Test::SQL::Data;
12 my $test = Test::SQL::Data->new() or exit;
14 use_ok('My::Module');
16 my $n_matches = 1;
17 $My::Module->run_something( dbh => $test->dbh );
19 $n_matches += $test->match_table('tablename','expected_tablename.sql');
21 done_testing($n_matches);
23
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 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 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 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 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 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 At the end of the test you must call done_testing from Test::More with
58 the number of tests run.
59 REQUIREMENTS
61 • DBD::SQLite
62 • DBIx::Connector
64 • Test::More
66
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 my $test_sql = Test::SQL::Data->new();
73 my $test_sql = Test::SQL::Data->new( config => "t/etc/test_config.cfg");
75 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 See Test Configuration bellow to learn how to easily pre-load SQL data
81 in the empty database it creates.
82
84 connect
85 Connects to database. It is not necessary to do this. It is executed at
86 new().
87 my $connection = connect();
89 my $connection = connect('t/file.db');
91 sql
93 Runs SQL in the internal test database.
94 $test_sql->sql('CREATE TABLE a (field_one int, ... )');
96 load_sql_file
98 Loads SQL statements into the temporary database from file.
99 parameters
101 • SQL file
103 file_db
105 Returns the current database file we are using to store data. So far it
106 is a SQLite database.
107 dbh
109 Returns the current database handler DBI
110 connector
112 Returns the current DBIx::Connector
113 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 parameters
120 • table
122 • expected_sql_file
124 returns
126 number of ok matches
128 expected_sql_file example
130 /* etc/expected_something.sql; */
132 CREATE TABLE(expected_something) ( id integer, name char(10) );
133 INSERT INTO expected_something VALUES( 3,'foo' );
134 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 sqlite t/db/something.db .dump > t/etc/expected_something.sql
141 dir_db
143 Returns the directory where it saves the temporary database
144
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 ie: t/35_foo.t -> t/etc/35_foo.sql
152 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 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 t/etc/40_bar.cfg
162 sql:
164 - 35_foo.sql
165 - 55_whoosa.sql
166 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 my $test_sql = Test::SQL::Data->new( config => "t/etc/another_config_file.cfg");
172
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 $ mysqldump --compatible=ansi --skip-extended-insert --compact database table
180 Manual Changes
182 • Auto Increment field: id_foo integer primary key autoincrement
184 • There is no unsigned int, use integer
186 • There is no enum, use varchar
188 • Timestamps: "date_updated" datetime default current_timestamp
190 • Escape ' with '' instead \'
192
194 You can inspect the execution doing:
195 DEBUG=1 make test
197 The result SQLite database for each test is in t/db_name_of_test.db
199
201 • Test::More
202
204 Francesc Guasch <frankie@etsetb.upc.edu>
205
207 This software is copyright (c) 2013 by Francesc Guasch.
208 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.
211perl v5.38.0 2023-07-21 Test::SQL::Data(3)