1 package SQL::Abstract::Test; # see doc at end of file
3 use SQL::Abstract::_TempExtlib;
7 use base qw(Test::Builder::Module Exporter);
11 use SQL::Abstract::Tree;
14 is_same_sql_bind is_same_sql is_same_bind
15 eq_sql_bind eq_sql eq_bind dumper diag_where
16 $case_sensitive $sql_differ
19 my $sqlat = SQL::Abstract::Tree->new;
21 our $case_sensitive = 0;
22 our $parenthesis_significant = 0;
23 our $order_by_asc_significant = 0;
25 our $sql_differ; # keeps track of differing portion between SQLs
26 our $tb = __PACKAGE__->builder;
28 sub is_same_sql_bind {
29 my ($sql1, $bind_ref1, $sql2, $bind_ref2, $msg) = @_;
32 my $same_sql = eq_sql($sql1, $sql2);
33 my $same_bind = eq_bind($bind_ref1, $bind_ref2);
35 # call Test::Builder::ok
36 my $ret = $tb->ok($same_sql && $same_bind, $msg);
40 _sql_differ_diag($sql1, $sql2);
43 _bind_differ_diag($bind_ref1, $bind_ref2);
46 # pass ok() result further
51 my ($sql1, $sql2, $msg) = @_;
54 my $same_sql = eq_sql($sql1, $sql2);
56 # call Test::Builder::ok
57 my $ret = $tb->ok($same_sql, $msg);
61 _sql_differ_diag($sql1, $sql2);
64 # pass ok() result further
69 my ($bind_ref1, $bind_ref2, $msg) = @_;
72 my $same_bind = eq_bind($bind_ref1, $bind_ref2);
74 # call Test::Builder::ok
75 my $ret = $tb->ok($same_bind, $msg);
79 _bind_differ_diag($bind_ref1, $bind_ref2);
82 # pass ok() result further
87 Data::Dumper->new([])->Terse(1)->Indent(1)->Useqq(1)->Deparse(1)->Quotekeys(0)->Sortkeys(1)->Maxdepth(0)->Values([@_])->Dump;
91 $tb->diag( "Search term:\n" . &dumper );
94 sub _sql_differ_diag {
95 my ($sql1, $sql2) = @_;
97 $tb->${\( $tb->in_todo ? 'note' : 'diag')} (
98 "SQL expressions differ\n"
101 ."differing in :\n$sql_differ\n"
105 sub _bind_differ_diag {
106 my ($bind_ref1, $bind_ref2) = @_;
108 $tb->${\( $tb->in_todo ? 'note' : 'diag')} (
109 "BIND values differ\n"
110 ." got: " . dumper($bind_ref1)
111 ."expected: " . dumper($bind_ref2)
116 my ($sql1, $bind_ref1, $sql2, $bind_ref2) = @_;
118 return eq_sql($sql1, $sql2) && eq_bind($bind_ref1, $bind_ref2);
122 sub eq_bind { goto &Test::Deep::eq_deeply };
125 my ($sql1, $sql2) = @_;
128 my $tree1 = $sqlat->parse($sql1);
129 my $tree2 = $sqlat->parse($sql2);
132 return 1 if _eq_sql($tree1, $tree2);
136 my ($left, $right) = @_;
138 # one is defined the other not
139 if ( (defined $left) xor (defined $right) ) {
140 $sql_differ = sprintf ("[%s] != [%s]\n", map { defined $_ ? $sqlat->unparse ($_) : 'N/A' } ($left, $right) );
144 # one is undefined, then so is the other
145 elsif (not defined $left) {
150 elsif (@$left == 0 and @$right == 0) {
155 if (@$left == 0 or @$right == 0) {
156 $sql_differ = sprintf ("left: %s\nright: %s\n", map { @$_ ? $sqlat->unparse ($_) : 'N/A'} ($left, $right) );
160 # one is a list, the other is an op with a list
161 elsif (ref $left->[0] xor ref $right->[0]) {
162 $sql_differ = sprintf ("[%s] != [%s]\nleft: %s\nright: %s\n", map
163 { ref $_ ? $sqlat->unparse ($_) : $_ }
164 ($left->[0], $right->[0], $left, $right)
170 elsif (ref $left->[0]) {
171 for (my $i = 0; $i <= $#$left or $i <= $#$right; $i++ ) {
172 if (not _eq_sql ($left->[$i], $right->[$i]) ) {
173 if (! $sql_differ or $sql_differ !~ /left\:\s .+ right:\s/xs) {
175 $sql_differ .= "\n" unless $sql_differ =~ /\n\z/;
176 $sql_differ .= sprintf ("left: %s\nright: %s\n", map { $sqlat->unparse ($_) } ($left, $right) );
187 # unroll parenthesis if possible/allowed
188 unless ( $parenthesis_significant ) {
189 $sqlat->_parenthesis_unroll($_) for $left, $right;
192 # unroll ASC order by's
193 unless ($order_by_asc_significant) {
194 $sqlat->_strip_asc_from_order_by($_) for $left, $right;
197 if ( $left->[0] ne $right->[0] ) {
198 $sql_differ = sprintf "OP [$left->[0]] != [$right->[0]] in\nleft: %s\nright: %s\n",
199 $sqlat->unparse($left),
200 $sqlat->unparse($right)
205 # literals have a different arg-sig
206 elsif ($left->[0] eq '-LITERAL') {
207 (my $l = " $left->[1][0] " ) =~ s/\s+/ /g;
208 (my $r = " $right->[1][0] ") =~ s/\s+/ /g;
209 my $eq = $case_sensitive ? $l eq $r : uc($l) eq uc($r);
210 $sql_differ = "[$l] != [$r]\n" if not $eq;
214 # if operators are identical, compare operands
216 my $eq = _eq_sql($left->[1], $right->[1]);
217 $sql_differ ||= sprintf ("left: %s\nright: %s\n", map { $sqlat->unparse ($_) } ($left, $right) ) if not $eq;
223 sub parse { $sqlat->parse(@_) }
231 SQL::Abstract::Test - Helper function for testing SQL::Abstract
237 use SQL::Abstract::Test import => [qw/
238 is_same_sql_bind is_same_sql is_same_bind
239 eq_sql_bind eq_sql eq_bind
242 my ($sql, @bind) = SQL::Abstract->new->select(%args);
244 is_same_sql_bind($given_sql, \@given_bind,
245 $expected_sql, \@expected_bind, $test_msg);
247 is_same_sql($given_sql, $expected_sql, $test_msg);
248 is_same_bind(\@given_bind, \@expected_bind, $test_msg);
250 my $is_same = eq_sql_bind($given_sql, \@given_bind,
251 $expected_sql, \@expected_bind);
253 my $sql_same = eq_sql($given_sql, $expected_sql);
254 my $bind_same = eq_bind(\@given_bind, \@expected_bind);
258 This module is only intended for authors of tests on
259 L<SQL::Abstract|SQL::Abstract> and related modules;
260 it exports functions for comparing two SQL statements
261 and their bound values.
263 The SQL comparison is performed on I<abstract syntax>,
264 ignoring differences in spaces or in levels of parentheses.
265 Therefore the tests will pass as long as the semantics
266 is preserved, even if the surface syntax has changed.
268 B<Disclaimer> : the semantic equivalence handling is pretty limited.
269 A lot of effort goes into distinguishing significant from
270 non-significant parenthesis, including AND/OR operator associativity.
271 Currently this module does not support commutativity and more
272 intelligent transformations like Morgan laws, etc.
274 For a good overview of what this test framework is capable of refer
279 =head2 is_same_sql_bind
281 is_same_sql_bind($given_sql, \@given_bind,
282 $expected_sql, \@expected_bind, $test_msg);
284 Compares given and expected pairs of C<($sql, \@bind)>, and calls
285 L<Test::Builder/ok> on the result, with C<$test_msg> as message. If the test
286 fails, a detailed diagnostic is printed. For clients which use L<Test::More>,
287 this is the one of the three functions (L</is_same_sql_bind>, L</is_same_sql>,
288 L</is_same_bind>) that needs to be imported.
292 is_same_sql($given_sql, $expected_sql, $test_msg);
294 Compares given and expected SQL statements, and calls L<Test::Builder/ok> on
295 the result, with C<$test_msg> as message. If the test fails, a detailed
296 diagnostic is printed. For clients which use L<Test::More>, this is the one of
297 the three functions (L</is_same_sql_bind>, L</is_same_sql>, L</is_same_bind>)
298 that needs to be imported.
302 is_same_bind(\@given_bind, \@expected_bind, $test_msg);
304 Compares given and expected bind values, and calls L<Test::Builder/ok> on the
305 result, with C<$test_msg> as message. If the test fails, a detailed diagnostic
306 is printed. For clients which use L<Test::More>, this is the one of the three
307 functions (L</is_same_sql_bind>, L</is_same_sql>, L</is_same_bind>) that needs
312 my $is_same = eq_sql_bind($given_sql, \@given_bind,
313 $expected_sql, \@expected_bind);
315 Compares given and expected pairs of C<($sql, \@bind)>. Similar to
316 L</is_same_sql_bind>, but it just returns a boolean value and does not print
317 diagnostics or talk to L<Test::Builder>.
321 my $is_same = eq_sql($given_sql, $expected_sql);
323 Compares the abstract syntax of two SQL statements. Similar to L</is_same_sql>,
324 but it just returns a boolean value and does not print diagnostics or talk to
325 L<Test::Builder>. If the result is false, the global variable L</$sql_differ>
326 will contain the SQL portion where a difference was encountered; this is useful
327 for printing diagnostics.
331 my $is_same = eq_sql(\@given_bind, \@expected_bind);
333 Compares two lists of bind values, taking into account the fact that some of
334 the values may be arrayrefs (see L<SQL::Abstract/bindtype>). Similar to
335 L</is_same_bind>, but it just returns a boolean value and does not print
336 diagnostics or talk to L<Test::Builder>.
338 =head1 GLOBAL VARIABLES
340 =head2 $case_sensitive
342 If true, SQL comparisons will be case-sensitive. Default is false;
344 =head2 $parenthesis_significant
346 If true, SQL comparison will preserve and report difference in nested
347 parenthesis. Useful while testing C<IN (( x ))> vs C<IN ( x )>.
350 =head2 $order_by_asc_significant
352 If true SQL comparison will consider C<ORDER BY foo ASC> and
353 C<ORDER BY foo> to be different. Default is false;
357 When L</eq_sql> returns false, the global variable
358 C<$sql_differ> contains the SQL portion
359 where a difference was encountered.
364 L<SQL::Abstract>, L<Test::More>, L<Test::Builder>.
368 Laurent Dami, E<lt>laurent.dami AT etat geneve chE<gt>
370 Norbert Buchmuller <norbi@nix.hu>
372 Peter Rabbitson <ribasushi@cpan.org>
374 =head1 COPYRIGHT AND LICENSE
376 Copyright 2008 by Laurent Dami.
378 This library is free software; you can redistribute it and/or modify
379 it under the same terms as Perl itself.