1 package SQL::Abstract::Test; # see doc at end of file
5 use base qw(Test::Builder::Module);
8 use SQL::Abstract::Tree;
10 if (my $class = $ENV{SQL_ABSTRACT_TEST_AGAINST}) {
11 my $mod = join('/', split '::', $class).".pm";
13 eval qq{sub SQL::Abstract () { "\Q${class}\E" }; 1}
14 or die "Failed to create const sub for ${class}: $@";
18 is_same_sql_bind is_same_sql is_same_bind
19 eq_sql_bind eq_sql eq_bind dumper diag_where
20 $case_sensitive $sql_differ
23 my $sqlat = SQL::Abstract::Tree->new;
25 our $case_sensitive = 0;
26 our $parenthesis_significant = 0;
27 our $order_by_asc_significant = 0;
29 our $sql_differ; # keeps track of differing portion between SQLs
30 our $tb; # not documented, but someone might be overriding it anyway
32 sub _unpack_arrayrefref {
38 if (ref $chunk eq 'REF' and ref $$chunk eq 'ARRAY') {
39 my ($sql, @bind) = @$$chunk;
40 push @args, ($sql, \@bind);
43 push @args, $chunk, shift @_;
48 # maybe $msg and ... stuff
54 sub is_same_sql_bind {
55 my ($sql1, $bind_ref1, $sql2, $bind_ref2, $msg) = &_unpack_arrayrefref;
58 my $same_sql = eq_sql($sql1, $sql2);
59 my $same_bind = eq_bind($bind_ref1, $bind_ref2);
61 # call Test::Builder::ok
62 my $tb = $tb || __PACKAGE__->builder;
63 my $ret = $tb->ok($same_sql && $same_bind, $msg);
67 _sql_differ_diag($sql1, $sql2);
70 _bind_differ_diag($bind_ref1, $bind_ref2);
73 # pass ok() result further
78 my ($sql1, $sql2, $msg) = @_;
81 my $same_sql = eq_sql($sql1, $sql2);
83 # call Test::Builder::ok
84 my $tb = $tb || __PACKAGE__->builder;
85 my $ret = $tb->ok($same_sql, $msg);
89 _sql_differ_diag($sql1, $sql2);
92 # pass ok() result further
97 my ($bind_ref1, $bind_ref2, $msg) = @_;
100 my $same_bind = eq_bind($bind_ref1, $bind_ref2);
102 # call Test::Builder::ok
103 my $tb = $tb || __PACKAGE__->builder;
104 my $ret = $tb->ok($same_bind, $msg);
108 _bind_differ_diag($bind_ref1, $bind_ref2);
111 # pass ok() result further
117 # if we save the instance, we will end up with $VARx references
118 # no time to figure out how to avoid this (Deepcopy is *not* an option)
119 require Data::Dumper;
120 Data::Dumper->new([])->Terse(1)->Indent(1)->Useqq(1)->Deparse(1)->Quotekeys(0)->Sortkeys(1)->Maxdepth(0)
121 ->Values([@_])->Dump;
125 my $tb = $tb || __PACKAGE__->builder;
126 $tb->diag("Search term:\n" . &dumper);
129 sub _sql_differ_diag {
130 my $sql1 = shift || '';
131 my $sql2 = shift || '';
133 my $tb = $tb || __PACKAGE__->builder;
135 if (my $profile = $ENV{SQL_ABSTRACT_TEST_TREE_PROFILE}) {
136 my $sqlat = SQL::Abstract::Tree->new(profile => $profile);
137 $_ = $sqlat->format($_) for ($sql1, $sql2);
140 $tb->${\($tb->in_todo ? 'note' : 'diag')} (
141 "SQL expressions differ\n"
144 ."\nmismatch around\n$sql_differ\n"
148 sub _bind_differ_diag {
149 my ($bind_ref1, $bind_ref2) = @_;
151 my $tb = $tb || __PACKAGE__->builder;
152 $tb->${\($tb->in_todo ? 'note' : 'diag')} (
153 "BIND values differ " . dumper({ got => $bind_ref1, want => $bind_ref2 })
158 my ($sql1, $bind_ref1, $sql2, $bind_ref2) = &_unpack_arrayrefref;
160 return eq_sql($sql1, $sql2) && eq_bind($bind_ref1, $bind_ref2);
164 sub eq_bind { goto &Test::Deep::eq_deeply };
167 my ($sql1, $sql2) = @_;
170 my $tree1 = $sqlat->parse($sql1);
171 my $tree2 = $sqlat->parse($sql2);
174 return 1 if _eq_sql($tree1, $tree2);
178 my ($left, $right) = @_;
180 # one is defined the other not
181 if ((defined $left) xor (defined $right)) {
182 $sql_differ = sprintf ("[%s] != [%s]\n", map { defined $_ ? $sqlat->unparse($_) : 'N/A' } ($left, $right) );
186 # one is undefined, then so is the other
187 elsif (not defined $left) {
192 elsif (@$left == 0 and @$right == 0) {
197 if (@$left == 0 or @$right == 0) {
198 $sql_differ = sprintf ("left: %s\nright: %s\n", map { @$_ ? $sqlat->unparse($_) : 'N/A'} ($left, $right) );
202 # one is a list, the other is an op with a list
203 elsif (ref $left->[0] xor ref $right->[0]) {
204 $sql_differ = sprintf ("[%s] != [%s]\nleft: %s\nright: %s\n", map
205 { ref $_ ? $sqlat->unparse($_) : $_ }
206 ($left->[0], $right->[0], $left, $right)
212 elsif (ref $left->[0]) {
213 for (my $i = 0; $i <= $#$left or $i <= $#$right; $i++ ) {
214 if (not _eq_sql ($left->[$i], $right->[$i]) ) {
215 if (! $sql_differ or $sql_differ !~ /left\:\s .+ right:\s/xs) {
217 $sql_differ .= "\n" unless $sql_differ =~ /\n\z/;
218 $sql_differ .= sprintf ("left: %s\nright: %s\n", map { $sqlat->unparse($_) } ($left, $right) );
229 # unroll parenthesis if possible/allowed
230 unless ($parenthesis_significant) {
231 $sqlat->_parenthesis_unroll($_) for $left, $right;
234 # unroll ASC order by's
235 unless ($order_by_asc_significant) {
236 $sqlat->_strip_asc_from_order_by($_) for $left, $right;
239 if ($left->[0] ne $right->[0]) {
240 $sql_differ = sprintf "OP [$left->[0]] != [$right->[0]] in\nleft: %s\nright: %s\n",
241 $sqlat->unparse($left),
242 $sqlat->unparse($right)
247 # literals have a different arg-sig
248 elsif ($left->[0] eq '-LITERAL') {
249 (my $l = " $left->[1][0] " ) =~ s/\s+/ /g;
250 (my $r = " $right->[1][0] ") =~ s/\s+/ /g;
251 my $eq = $case_sensitive ? $l eq $r : uc($l) eq uc($r);
252 $sql_differ = "[$l] != [$r]\n" if not $eq;
256 # if operators are identical, compare operands
258 my $eq = _eq_sql($left->[1], $right->[1]);
259 $sql_differ ||= sprintf ("left: %s\nright: %s\n", map { $sqlat->unparse($_) } ($left, $right) ) if not $eq;
265 sub parse { $sqlat->parse(@_) }
273 SQL::Abstract::Test - Helper function for testing SQL::Abstract
279 use SQL::Abstract::Test import => [qw/
280 is_same_sql_bind is_same_sql is_same_bind
281 eq_sql_bind eq_sql eq_bind
284 my ($sql, @bind) = SQL::Abstract->new->select(%args);
286 is_same_sql_bind($given_sql, \@given_bind,
287 $expected_sql, \@expected_bind, $test_msg);
289 is_same_sql($given_sql, $expected_sql, $test_msg);
290 is_same_bind(\@given_bind, \@expected_bind, $test_msg);
292 my $is_same = eq_sql_bind($given_sql, \@given_bind,
293 $expected_sql, \@expected_bind);
295 my $sql_same = eq_sql($given_sql, $expected_sql);
296 my $bind_same = eq_bind(\@given_bind, \@expected_bind);
300 This module is only intended for authors of tests on
301 L<SQL::Abstract|SQL::Abstract> and related modules;
302 it exports functions for comparing two SQL statements
303 and their bound values.
305 The SQL comparison is performed on I<abstract syntax>,
306 ignoring differences in spaces or in levels of parentheses.
307 Therefore the tests will pass as long as the semantics
308 is preserved, even if the surface syntax has changed.
310 B<Disclaimer> : the semantic equivalence handling is pretty limited.
311 A lot of effort goes into distinguishing significant from
312 non-significant parenthesis, including AND/OR operator associativity.
313 Currently this module does not support commutativity and more
314 intelligent transformations like L<De Morgan's laws
315 |http://en.wikipedia.org/wiki/De_Morgan's_laws>, etc.
317 For a good overview of what this test framework is currently capable of refer
322 =head2 is_same_sql_bind
325 $given_sql, \@given_bind,
326 $expected_sql, \@expected_bind,
331 \[$given_sql, @given_bind],
332 \[$expected_sql, @expected_bind],
338 $expected_sql, \@expected_bind,
342 Compares given and expected pairs of C<($sql, \@bind)> by unpacking C<@_>
343 as shown in the examples above and passing the arguments to L</eq_sql> and
344 L</eq_bind>. Calls L<Test::Builder/ok> with the combined result, with
345 C<$test_msg> as message.
346 If the test fails, a detailed diagnostic is printed.
356 Compares given and expected SQL statements via L</eq_sql>, and calls
357 L<Test::Builder/ok> on the result, with C<$test_msg> as message.
358 If the test fails, a detailed diagnostic is printed.
368 Compares given and expected bind values via L</eq_bind>, and calls
369 L<Test::Builder/ok> on the result, with C<$test_msg> as message.
370 If the test fails, a detailed diagnostic is printed.
374 my $is_same = eq_sql_bind(
375 $given_sql, \@given_bind,
376 $expected_sql, \@expected_bind,
379 my $is_same = eq_sql_bind(
380 \[$given_sql, @given_bind],
381 \[$expected_sql, @expected_bind],
384 my $is_same = eq_sql_bind(
386 $expected_sql, \@expected_bind,
389 Unpacks C<@_> depending on the given arguments and calls L</eq_sql> and
390 L</eq_bind>, returning their combined result.
394 my $is_same = eq_sql($given_sql, $expected_sql);
396 Compares the abstract syntax of two SQL statements. Similar to L</is_same_sql>,
397 but it just returns a boolean value and does not print diagnostics or talk to
398 L<Test::Builder>. If the result is false, the global variable L</$sql_differ>
399 will contain the SQL portion where a difference was encountered; this is useful
400 for printing diagnostics.
404 my $is_same = eq_sql(\@given_bind, \@expected_bind);
406 Compares two lists of bind values, taking into account the fact that some of
407 the values may be arrayrefs (see L<SQL::Abstract/bindtype>). Similar to
408 L</is_same_bind>, but it just returns a boolean value and does not print
409 diagnostics or talk to L<Test::Builder>.
411 =head1 GLOBAL VARIABLES
413 =head2 $case_sensitive
415 If true, SQL comparisons will be case-sensitive. Default is false;
417 =head2 $parenthesis_significant
419 If true, SQL comparison will preserve and report difference in nested
420 parenthesis. Useful while testing C<IN (( x ))> vs C<IN ( x )>.
423 =head2 $order_by_asc_significant
425 If true SQL comparison will consider C<ORDER BY foo ASC> and
426 C<ORDER BY foo> to be different. Default is false;
430 When L</eq_sql> returns false, the global variable
431 C<$sql_differ> contains the SQL portion
432 where a difference was encountered.
436 L<SQL::Abstract>, L<Test::More>, L<Test::Builder>.
440 Laurent Dami <laurent.dami AT etat geneve ch>
442 Norbert Buchmuller <norbi@nix.hu>
444 Peter Rabbitson <ribasushi@cpan.org>
446 =head1 COPYRIGHT AND LICENSE
448 Copyright 2008 by Laurent Dami.
450 This library is free software; you can redistribute it and/or modify
451 it under the same terms as Perl itself.