1 package SQL::Abstract::Test; # see doc at end of file
5 use base qw/Test::Builder::Module Exporter/;
8 use SQL::Abstract::Tree;
10 our @EXPORT_OK = qw/&is_same_sql_bind &is_same_sql &is_same_bind
11 &eq_sql_bind &eq_sql &eq_bind
12 $case_sensitive $sql_differ/;
14 my $sqlat = SQL::Abstract::Tree->new;
16 our $case_sensitive = 0;
17 our $parenthesis_significant = 0;
18 our $sql_differ; # keeps track of differing portion between SQLs
19 our $tb = __PACKAGE__->builder;
21 # All of these keywords allow their parameters to be specified with or without parenthesis without changing the semantics
22 my @unrollable_ops = (
30 sub is_same_sql_bind {
31 my ($sql1, $bind_ref1, $sql2, $bind_ref2, $msg) = @_;
34 my $same_sql = eq_sql($sql1, $sql2);
35 my $same_bind = eq_bind($bind_ref1, $bind_ref2);
37 # call Test::Builder::ok
38 my $ret = $tb->ok($same_sql && $same_bind, $msg);
42 _sql_differ_diag($sql1, $sql2);
45 _bind_differ_diag($bind_ref1, $bind_ref2);
48 # pass ok() result further
53 my ($sql1, $sql2, $msg) = @_;
56 my $same_sql = eq_sql($sql1, $sql2);
58 # call Test::Builder::ok
59 my $ret = $tb->ok($same_sql, $msg);
63 _sql_differ_diag($sql1, $sql2);
66 # pass ok() result further
71 my ($bind_ref1, $bind_ref2, $msg) = @_;
74 my $same_bind = eq_bind($bind_ref1, $bind_ref2);
76 # call Test::Builder::ok
77 my $ret = $tb->ok($same_bind, $msg);
81 _bind_differ_diag($bind_ref1, $bind_ref2);
84 # pass ok() result further
88 sub _sql_differ_diag {
89 my ($sql1, $sql2) = @_;
91 $tb->diag("SQL expressions differ\n"
94 ."differing in :\n$sql_differ\n"
98 sub _bind_differ_diag {
99 my ($bind_ref1, $bind_ref2) = @_;
101 $tb->diag("BIND values differ\n"
102 ." got: " . Dumper($bind_ref1)
103 ."expected: " . Dumper($bind_ref2)
108 my ($sql1, $bind_ref1, $sql2, $bind_ref2) = @_;
110 return eq_sql($sql1, $sql2) && eq_bind($bind_ref1, $bind_ref2);
115 my ($bind_ref1, $bind_ref2) = @_;
117 local $Data::Dumper::Useqq = 1;
118 local $Data::Dumper::Sortkeys = 1;
120 return Dumper($bind_ref1) eq Dumper($bind_ref2);
124 my ($sql1, $sql2) = @_;
127 my $tree1 = $sqlat->parse($sql1);
128 my $tree2 = $sqlat->parse($sql2);
130 return 1 if _eq_sql($tree1, $tree2);
134 my ($left, $right) = @_;
136 # one is defined the other not
137 if ( (defined $left) xor (defined $right) ) {
140 # one is undefined, then so is the other
141 elsif (not defined $left) {
144 # different amount of elements
145 elsif (@$left != @$right) {
146 $sql_differ = sprintf ("left: %s\nright: %s\n", map { $sqlat->unparse ($_) } ($left, $right) );
149 # one is empty - so is the other
150 elsif (@$left == 0) {
153 # one is a list, the other is an op with a list
154 elsif (ref $left->[0] xor ref $right->[0]) {
155 $sql_differ = sprintf ("left: %s\nright: %s\n", map { $sqlat->unparse ($_) } ($left, $right) );
158 # one is a list, so is the other
159 elsif (ref $left->[0]) {
160 for (my $i = 0; $i <= $#$left or $i <= $#$right; $i++ ) {
161 return 0 if (not _eq_sql ($left->[$i], $right->[$i]) );
165 # both are an op-list combo
168 # unroll parenthesis if possible/allowed
169 _parenthesis_unroll ($_) for ($left, $right);
171 # if operators are different
172 if ( $left->[0] ne $right->[0] ) {
173 $sql_differ = sprintf "OP [$left->[0]] != [$right->[0]] in\nleft: %s\nright: %s\n",
174 $sqlat->unparse($left),
175 $sqlat->unparse($right);
178 # elsif operators are identical, compare operands
180 if ($left->[0] eq 'LITERAL' ) { # unary
181 (my $l = " $left->[1][0] " ) =~ s/\s+/ /g;
182 (my $r = " $right->[1][0] ") =~ s/\s+/ /g;
183 my $eq = $case_sensitive ? $l eq $r : uc($l) eq uc($r);
184 $sql_differ = "[$l] != [$r]\n" if not $eq;
188 my $eq = _eq_sql($left->[1], $right->[1]);
189 $sql_differ ||= sprintf ("left: %s\nright: %s\n", map { $sqlat->unparse ($_) } ($left, $right) ) if not $eq;
196 sub _parenthesis_unroll {
199 return if $parenthesis_significant;
200 return unless (ref $ast and ref $ast->[1]);
207 for my $child (@{$ast->[1]}) {
208 # the current node in this loop is *always* a PAREN
209 if (not ref $child or not $child->[0] eq 'PAREN') {
210 push @children, $child;
214 # unroll nested parenthesis
215 while ( @{$child->[1]} && $child->[1][0][0] eq 'PAREN') {
216 $child = $child->[1][0];
220 # if the parenthesis are wrapped around an AND/OR matching the parent AND/OR - open the parenthesis up and merge the list
222 ( $ast->[0] eq 'AND' or $ast->[0] eq 'OR')
224 $child->[1][0][0] eq $ast->[0]
226 push @children, @{$child->[1][0][1]};
230 # if the parent operator explcitly allows it nuke the parenthesis
231 elsif ( grep { $ast->[0] =~ /^ $_ $/xi } @unrollable_ops ) {
232 push @children, $child->[1][0];
236 # only *ONE* LITERAL element
238 @{$child->[1]} == 1 && $child->[1][0][0] eq 'LITERAL'
240 push @children, $child->[1][0];
244 # only one element in the parenthesis which is a binary op
245 # and has exactly two grandchildren
246 # the only time when we can *not* unroll this is when both
247 # the parent and the child are mathops (in which case we'll
248 # break precedence) or when the child is BETWEEN (special
253 $child->[1][0][0] =~ SQL::Abstract::Tree::_binary_op_re()
255 $child->[1][0][0] ne 'BETWEEN'
257 @{$child->[1][0][1]} == 2
260 $child->[1][0][0] =~ SQL::Abstract::Tree::_math_op_re()
262 $ast->[0] =~ SQL::Abstract::Tree::_math_op_re()
265 push @children, $child->[1][0];
269 # a function binds tighter than a mathop - see if our ancestor is a
270 # mathop, and our content is a single non-mathop child with a single
271 # PAREN grandchild which would indicate mathop ( nonmathop ( ... ) )
275 @{$child->[1][0][1]} == 1
277 $child->[1][0][1][0][0] eq 'PAREN'
279 $ast->[0] =~ SQL::Abstract::Tree::_math_op_re()
281 $child->[1][0][0] !~ SQL::Abstract::Tree::_math_op_re
283 push @children, $child->[1][0];
288 # otherwise no more mucking for this pass
290 push @children, $child;
294 $ast->[1] = \@children;
300 sub parse { $sqlat->parse(@_) }
308 SQL::Abstract::Test - Helper function for testing SQL::Abstract
314 use SQL::Abstract::Test import => [qw/
315 is_same_sql_bind is_same_sql is_same_bind
316 eq_sql_bind eq_sql eq_bind
319 my ($sql, @bind) = SQL::Abstract->new->select(%args);
321 is_same_sql_bind($given_sql, \@given_bind,
322 $expected_sql, \@expected_bind, $test_msg);
324 is_same_sql($given_sql, $expected_sql, $test_msg);
325 is_same_bind(\@given_bind, \@expected_bind, $test_msg);
327 my $is_same = eq_sql_bind($given_sql, \@given_bind,
328 $expected_sql, \@expected_bind);
330 my $sql_same = eq_sql($given_sql, $expected_sql);
331 my $bind_same = eq_bind(\@given_bind, \@expected_bind);
335 This module is only intended for authors of tests on
336 L<SQL::Abstract|SQL::Abstract> and related modules;
337 it exports functions for comparing two SQL statements
338 and their bound values.
340 The SQL comparison is performed on I<abstract syntax>,
341 ignoring differences in spaces or in levels of parentheses.
342 Therefore the tests will pass as long as the semantics
343 is preserved, even if the surface syntax has changed.
345 B<Disclaimer> : the semantic equivalence handling is pretty limited.
346 A lot of effort goes into distinguishing significant from
347 non-significant parenthesis, including AND/OR operator associativity.
348 Currently this module does not support commutativity and more
349 intelligent transformations like Morgan laws, etc.
351 For a good overview of what this test framework is capable of refer
356 =head2 is_same_sql_bind
358 is_same_sql_bind($given_sql, \@given_bind,
359 $expected_sql, \@expected_bind, $test_msg);
361 Compares given and expected pairs of C<($sql, \@bind)>, and calls
362 L<Test::Builder/ok> on the result, with C<$test_msg> as message. If the test
363 fails, a detailed diagnostic is printed. For clients which use L<Test::More>,
364 this is the one of the three functions (L</is_same_sql_bind>, L</is_same_sql>,
365 L</is_same_bind>) that needs to be imported.
369 is_same_sql($given_sql, $expected_sql, $test_msg);
371 Compares given and expected SQL statements, and calls L<Test::Builder/ok> on
372 the result, with C<$test_msg> as message. If the test fails, a detailed
373 diagnostic is printed. For clients which use L<Test::More>, this is the one of
374 the three functions (L</is_same_sql_bind>, L</is_same_sql>, L</is_same_bind>)
375 that needs to be imported.
379 is_same_bind(\@given_bind, \@expected_bind, $test_msg);
381 Compares given and expected bind values, and calls L<Test::Builder/ok> on the
382 result, with C<$test_msg> as message. If the test fails, a detailed diagnostic
383 is printed. For clients which use L<Test::More>, this is the one of the three
384 functions (L</is_same_sql_bind>, L</is_same_sql>, L</is_same_bind>) that needs
389 my $is_same = eq_sql_bind($given_sql, \@given_bind,
390 $expected_sql, \@expected_bind);
392 Compares given and expected pairs of C<($sql, \@bind)>. Similar to
393 L</is_same_sql_bind>, but it just returns a boolean value and does not print
394 diagnostics or talk to L<Test::Builder>.
398 my $is_same = eq_sql($given_sql, $expected_sql);
400 Compares the abstract syntax of two SQL statements. Similar to L</is_same_sql>,
401 but it just returns a boolean value and does not print diagnostics or talk to
402 L<Test::Builder>. If the result is false, the global variable L</$sql_differ>
403 will contain the SQL portion where a difference was encountered; this is useful
404 for printing diagnostics.
408 my $is_same = eq_sql(\@given_bind, \@expected_bind);
410 Compares two lists of bind values, taking into account the fact that some of
411 the values may be arrayrefs (see L<SQL::Abstract/bindtype>). Similar to
412 L</is_same_bind>, but it just returns a boolean value and does not print
413 diagnostics or talk to L<Test::Builder>.
415 =head1 GLOBAL VARIABLES
417 =head2 $case_sensitive
419 If true, SQL comparisons will be case-sensitive. Default is false;
421 =head2 $parenthesis_significant
423 If true, SQL comparison will preserve and report difference in nested
424 parenthesis. Useful for testing the C<-nest> modifier. Defaults to false;
428 When L</eq_sql> returns false, the global variable
429 C<$sql_differ> contains the SQL portion
430 where a difference was encountered.
435 L<SQL::Abstract>, L<Test::More>, L<Test::Builder>.
439 Laurent Dami, E<lt>laurent.dami AT etat geneve chE<gt>
441 Norbert Buchmuller <norbi@nix.hu>
443 Peter Rabbitson <ribasushi@cpan.org>
445 =head1 COPYRIGHT AND LICENSE
447 Copyright 2008 by Laurent Dami.
449 This library is free software; you can redistribute it and/or modify
450 it under the same terms as Perl itself.