1 package SQL::Abstract::Test; # see doc at end of file
5 use base qw/Test::Builder::Module Exporter/;
9 use Test::Deep qw(eq_deeply);
11 our @EXPORT_OK = qw/&is_same_sql_bind &is_same_sql &is_same_bind
12 &eq_sql_bind &eq_sql &eq_bind
13 $case_sensitive $sql_differ/;
15 our $case_sensitive = 0;
16 our $parenthesis_significant = 0;
17 our $sql_differ; # keeps track of differing portion between SQLs
18 our $tb = __PACKAGE__->builder;
20 # Parser states for _recurse_parse()
21 use constant PARSE_TOP_LEVEL => 0;
22 use constant PARSE_IN_EXPR => 1;
23 use constant PARSE_IN_PARENS => 2;
24 use constant PARSE_RHS => 3;
26 # These SQL keywords always signal end of the current expression (except inside
27 # of a parenthesized subexpression).
28 # Format: A list of strings that will be compiled to extended syntax (ie.
29 # /.../x) regexes, without capturing parentheses. They will be automatically
30 # anchored to word boundaries to match the whole token).
31 my @expression_terminator_sql_keywords = (
36 (?: \b (?: LEFT | RIGHT | FULL ) \s+ )?
37 (?: \b (?: CROSS | INNER | OUTER ) \s+ )?
54 # These are binary operator keywords always a single LHS and RHS
55 # * AND/OR are handled separately as they are N-ary
56 # * so is NOT as being unary
57 # * BETWEEN without paranthesis around the ANDed arguments (which
58 # makes it a non-binary op) is detected and accomodated in
60 my $stuff_around_mathops = qr/[\w\s\`\'\)]/;
61 my @binary_op_keywords = (
63 { " (?<= $stuff_around_mathops) " . quotemeta $_ . "(?= $stuff_around_mathops )" }
67 { '\b (?: NOT \s+)?' . $_ . '\b' }
72 my $tokenizer_re_str = join("\n\t|\n",
73 ( map { '\b' . $_ . '\b' } @expression_terminator_sql_keywords, 'AND', 'OR', 'NOT'),
77 my $tokenizer_re = qr/ \s* ( \( | \) | \? | $tokenizer_re_str ) \s* /xi;
79 # All of these keywords allow their parameters to be specified with or without parenthesis without changing the semantics
80 my @unrollable_ops = (
88 sub is_same_sql_bind {
89 my ($sql1, $bind_ref1, $sql2, $bind_ref2, $msg) = @_;
92 my $same_sql = eq_sql($sql1, $sql2);
93 my $same_bind = eq_bind($bind_ref1, $bind_ref2);
95 # call Test::Builder::ok
96 my $ret = $tb->ok($same_sql && $same_bind, $msg);
100 _sql_differ_diag($sql1, $sql2);
103 _bind_differ_diag($bind_ref1, $bind_ref2);
106 # pass ok() result further
111 my ($sql1, $sql2, $msg) = @_;
114 my $same_sql = eq_sql($sql1, $sql2);
116 # call Test::Builder::ok
117 my $ret = $tb->ok($same_sql, $msg);
121 _sql_differ_diag($sql1, $sql2);
124 # pass ok() result further
129 my ($bind_ref1, $bind_ref2, $msg) = @_;
132 my $same_bind = eq_bind($bind_ref1, $bind_ref2);
134 # call Test::Builder::ok
135 my $ret = $tb->ok($same_bind, $msg);
139 _bind_differ_diag($bind_ref1, $bind_ref2);
142 # pass ok() result further
146 sub _sql_differ_diag {
147 my ($sql1, $sql2) = @_;
149 $tb->diag("SQL expressions differ\n"
152 ."differing in :\n$sql_differ\n"
156 sub _bind_differ_diag {
157 my ($bind_ref1, $bind_ref2) = @_;
159 $tb->diag("BIND values differ\n"
160 ." got: " . Dumper($bind_ref1)
161 ."expected: " . Dumper($bind_ref2)
166 my ($sql1, $bind_ref1, $sql2, $bind_ref2) = @_;
168 return eq_sql($sql1, $sql2) && eq_bind($bind_ref1, $bind_ref2);
173 my ($bind_ref1, $bind_ref2) = @_;
175 return eq_deeply($bind_ref1, $bind_ref2);
179 my ($sql1, $sql2) = @_;
182 my $tree1 = parse($sql1);
183 my $tree2 = parse($sql2);
185 return 1 if _eq_sql($tree1, $tree2);
189 my ($left, $right) = @_;
191 # one is defined the other not
192 if ( (defined $left) xor (defined $right) ) {
195 # one is undefined, then so is the other
196 elsif (not defined $left) {
199 # one is a list, the other is an op with a list
200 elsif (ref $left->[0] xor ref $right->[0]) {
201 $sql_differ = sprintf ("left: %s\nright: %s\n", map { unparse ($_) } ($left, $right) );
204 # one is a list, so is the other
205 elsif (ref $left->[0]) {
206 for (my $i = 0; $i <= $#$left or $i <= $#$right; $i++ ) {
207 return 0 if (not _eq_sql ($left->[$i], $right->[$i]) );
211 # both are an op-list combo
214 # unroll parenthesis if possible/allowed
215 _parenthesis_unroll ($_) for ($left, $right);
217 # if operators are different
218 if ($left->[0] ne $right->[0]) {
219 $sql_differ = sprintf "OP [$left->[0]] != [$right->[0]] in\nleft: %s\nright: %s\n",
224 # elsif operators are identical, compare operands
226 if ($left->[0] eq 'EXPR' ) { # unary operator
227 (my $l = " $left->[1][0] " ) =~ s/\s+/ /g;
228 (my $r = " $right->[1][0] ") =~ s/\s+/ /g;
229 my $eq = $case_sensitive ? $l eq $r : uc($l) eq uc($r);
230 $sql_differ = "[$l] != [$r]\n" if not $eq;
234 my $eq = _eq_sql($left->[1], $right->[1]);
235 $sql_differ ||= sprintf ("left: %s\nright: %s\n", map { unparse ($_) } ($left, $right) ) if not $eq;
245 # tokenize string, and remove all optional whitespace
247 foreach my $token (split $tokenizer_re, $s) {
249 $token =~ s/\s+([^\w\s])/$1/g;
250 $token =~ s/([^\w\s])\s+/$1/g;
251 push @$tokens, $token if length $token;
254 my $tree = _recurse_parse($tokens, PARSE_TOP_LEVEL);
259 my ($tokens, $state) = @_;
262 while (1) { # left-associative parsing
264 my $lookahead = $tokens->[0];
265 if ( not defined($lookahead)
267 ($state == PARSE_IN_PARENS && $lookahead eq ')')
269 ($state == PARSE_IN_EXPR && grep { $lookahead =~ /^ $_ $/xi } ('\)', @expression_terminator_sql_keywords ) )
271 ($state == PARSE_RHS && grep { $lookahead =~ /^ $_ $/xi } ('\)', @expression_terminator_sql_keywords, @binary_op_keywords, 'AND', 'OR', 'NOT' ) )
276 my $token = shift @$tokens;
278 # nested expression in ()
280 my $right = _recurse_parse($tokens, PARSE_IN_PARENS);
281 $token = shift @$tokens or croak "missing closing ')' around block " . unparse ($right);
282 $token eq ')' or croak "unexpected token '$token' terminating block " . unparse ($right);
283 $left = $left ? [@$left, [PAREN => [$right] ]]
284 : [PAREN => [$right] ];
287 elsif ($token =~ /^ (?: OR | AND ) $/xi ) {
289 my $right = _recurse_parse($tokens, PARSE_IN_EXPR);
291 # Merge chunks if logic matches
292 if (ref $right and $op eq $right->[0]) {
293 $left = [ (shift @$right ), [$left, map { @$_ } @$right] ];
296 $left = [$op => [$left, $right]];
299 # binary operator keywords
300 elsif (grep { $token =~ /^ $_ $/xi } @binary_op_keywords ) {
302 my $right = _recurse_parse($tokens, PARSE_RHS);
304 # A between with a simple EXPR for a 1st RHS argument needs a
305 # rerun of the search to (hopefully) find the proper AND construct
306 if ($op eq 'BETWEEN' and $right->[0] eq 'EXPR') {
307 unshift @$tokens, $right->[1][0];
308 $right = _recurse_parse($tokens, PARSE_IN_EXPR);
311 $left = [$op => [$left, $right] ];
313 # expression terminator keywords (as they start a new expression)
314 elsif (grep { $token =~ /^ $_ $/xi } @expression_terminator_sql_keywords ) {
316 my $right = _recurse_parse($tokens, PARSE_IN_EXPR);
317 $left = $left ? [@$left, [$op => [$right] ]]
318 : [[ $op => [$right] ]];
320 # NOT (last as to allow all other NOT X pieces first)
321 elsif ( $token =~ /^ not $/ix ) {
323 my $right = _recurse_parse ($tokens, PARSE_RHS);
324 $left = $left ? [ @$left, [$op => [$right] ]]
325 : [[ $op => [$right] ]];
330 $left = $left ? [@$left, [EXPR => [$token] ] ]
331 : [ EXPR => [$token] ];
336 sub _parenthesis_unroll {
339 return if $parenthesis_significant;
340 return unless (ref $ast and ref $ast->[1]);
347 for my $child (@{$ast->[1]}) {
348 if (not ref $child or not $child->[0] eq 'PAREN') {
349 push @children, $child;
353 # unroll nested parenthesis
354 while ($child->[1][0][0] eq 'PAREN') {
355 $child = $child->[1][0];
359 # if the parenthesis are wrapped around an AND/OR matching the parent AND/OR - open the parenthesis up and merge the list
361 ( $ast->[0] eq 'AND' or $ast->[0] eq 'OR')
363 $child->[1][0][0] eq $ast->[0]
365 push @children, @{$child->[1][0][1]};
369 # if the parent operator explcitly allows it nuke the parenthesis
370 elsif ( grep { $ast->[0] =~ /^ $_ $/xi } @unrollable_ops ) {
371 push @children, $child->[1][0];
375 # only one EXPR element in the parenthesis
377 @{$child->[1]} == 1 && $child->[1][0][0] eq 'EXPR'
379 push @children, $child->[1][0];
383 # only one element in the parenthesis which is a binary op with two EXPR sub-children
387 grep { $child->[1][0][0] =~ /^ $_ $/xi } (@binary_op_keywords)
389 $child->[1][0][1][0][0] eq 'EXPR'
391 $child->[1][0][1][1][0] eq 'EXPR'
393 push @children, $child->[1][0];
397 # otherwise no more mucking for this pass
399 push @children, $child;
403 $ast->[1] = \@children;
415 elsif (ref $tree->[0]) {
416 return join (" ", map { unparse ($_) } @$tree);
418 elsif ($tree->[0] eq 'EXPR') {
419 return $tree->[1][0];
421 elsif ($tree->[0] eq 'PAREN') {
422 return sprintf '(%s)', join (" ", map {unparse($_)} @{$tree->[1]});
424 elsif ($tree->[0] eq 'OR' or $tree->[0] eq 'AND' or (grep { $tree->[0] =~ /^ $_ $/xi } @binary_op_keywords ) ) {
425 return join (" $tree->[0] ", map {unparse($_)} @{$tree->[1]});
428 return sprintf '%s %s', $tree->[0], unparse ($tree->[1]);
440 SQL::Abstract::Test - Helper function for testing SQL::Abstract
446 use SQL::Abstract::Test import => [qw/
447 is_same_sql_bind is_same_sql is_same_bind
448 eq_sql_bind eq_sql eq_bind
451 my ($sql, @bind) = SQL::Abstract->new->select(%args);
453 is_same_sql_bind($given_sql, \@given_bind,
454 $expected_sql, \@expected_bind, $test_msg);
456 is_same_sql($given_sql, $expected_sql, $test_msg);
457 is_same_bind(\@given_bind, \@expected_bind, $test_msg);
459 my $is_same = eq_sql_bind($given_sql, \@given_bind,
460 $expected_sql, \@expected_bind);
462 my $sql_same = eq_sql($given_sql, $expected_sql);
463 my $bind_same = eq_bind(\@given_bind, \@expected_bind);
467 This module is only intended for authors of tests on
468 L<SQL::Abstract|SQL::Abstract> and related modules;
469 it exports functions for comparing two SQL statements
470 and their bound values.
472 The SQL comparison is performed on I<abstract syntax>,
473 ignoring differences in spaces or in levels of parentheses.
474 Therefore the tests will pass as long as the semantics
475 is preserved, even if the surface syntax has changed.
477 B<Disclaimer> : the semantic equivalence handling is pretty limited.
478 A lot of effort goes into distinguishing significant from
479 non-significant parenthesis, including AND/OR operator associativity.
480 Currently this module does not support commutativity and more
481 intelligent transformations like Morgan laws, etc.
483 For a good overview of what this test framework is capable of refer
488 =head2 is_same_sql_bind
490 is_same_sql_bind($given_sql, \@given_bind,
491 $expected_sql, \@expected_bind, $test_msg);
493 Compares given and expected pairs of C<($sql, \@bind)>, and calls
494 L<Test::Builder/ok> on the result, with C<$test_msg> as message. If the test
495 fails, a detailed diagnostic is printed. For clients which use L<Test::More>,
496 this is the one of the three functions (L</is_same_sql_bind>, L</is_same_sql>,
497 L</is_same_bind>) that needs to be imported.
501 is_same_sql($given_sql, $expected_sql, $test_msg);
503 Compares given and expected SQL statements, and calls L<Test::Builder/ok> on
504 the result, with C<$test_msg> as message. If the test fails, a detailed
505 diagnostic is printed. For clients which use L<Test::More>, this is the one of
506 the three functions (L</is_same_sql_bind>, L</is_same_sql>, L</is_same_bind>)
507 that needs to be imported.
511 is_same_bind(\@given_bind, \@expected_bind, $test_msg);
513 Compares given and expected bind values, and calls L<Test::Builder/ok> on the
514 result, with C<$test_msg> as message. If the test fails, a detailed diagnostic
515 is printed. For clients which use L<Test::More>, this is the one of the three
516 functions (L</is_same_sql_bind>, L</is_same_sql>, L</is_same_bind>) that needs
521 my $is_same = eq_sql_bind($given_sql, \@given_bind,
522 $expected_sql, \@expected_bind);
524 Compares given and expected pairs of C<($sql, \@bind)>. Similar to
525 L</is_same_sql_bind>, but it just returns a boolean value and does not print
526 diagnostics or talk to L<Test::Builder>.
530 my $is_same = eq_sql($given_sql, $expected_sql);
532 Compares the abstract syntax of two SQL statements. Similar to L</is_same_sql>,
533 but it just returns a boolean value and does not print diagnostics or talk to
534 L<Test::Builder>. If the result is false, the global variable L</$sql_differ>
535 will contain the SQL portion where a difference was encountered; this is useful
536 for printing diagnostics.
540 my $is_same = eq_sql(\@given_bind, \@expected_bind);
542 Compares two lists of bind values, taking into account the fact that some of
543 the values may be arrayrefs (see L<SQL::Abstract/bindtype>). Similar to
544 L</is_same_bind>, but it just returns a boolean value and does not print
545 diagnostics or talk to L<Test::Builder>.
547 =head1 GLOBAL VARIABLES
549 =head2 $case_sensitive
551 If true, SQL comparisons will be case-sensitive. Default is false;
553 =head2 $parenthesis_significant
555 If true, SQL comparison will preserve and report difference in nested
556 parenthesis. Useful for testing the C<-nest> modifier. Defaults to false;
560 When L</eq_sql> returns false, the global variable
561 C<$sql_differ> contains the SQL portion
562 where a difference was encountered.
567 L<SQL::Abstract>, L<Test::More>, L<Test::Builder>.
571 Laurent Dami, E<lt>laurent.dami AT etat geneve chE<gt>
573 Norbert Buchmuller <norbi@nix.hu>
575 Peter Rabbitson <ribasushi@cpan.org>
577 =head1 COPYRIGHT AND LICENSE
579 Copyright 2008 by Laurent Dami.
581 This library is free software; you can redistribute it and/or modify
582 it under the same terms as Perl itself.