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 $sql_differ; # keeps track of differing portion between SQLs
17 our $tb = __PACKAGE__->builder;
19 # Parser states for _recurse_parse()
20 use constant PARSE_TOP_LEVEL => 0;
21 use constant PARSE_IN_EXPR => 1;
22 use constant PARSE_IN_PARENS => 2;
24 # These SQL keywords always signal end of the current expression (except inside
25 # of a parenthesized subexpression).
26 # Format: A list of strings that will be compiled to extended syntax (ie.
27 # /.../x) regexes, without capturing parentheses. They will be automatically
28 # anchored to word boundaries to match the whole token).
29 my @expression_terminator_sql_keywords = (
33 (?: \b (?: LEFT | RIGHT | FULL ) \s+ )?
34 (?: \b (?: CROSS | INNER | OUTER ) \s+ )?
51 my $tokenizer_re_str = join('|',
52 map { '\b' . $_ . '\b' }
53 @expression_terminator_sql_keywords, 'AND', 'OR'
56 my $tokenizer_re = qr/
69 sub is_same_sql_bind {
70 my ($sql1, $bind_ref1, $sql2, $bind_ref2, $msg) = @_;
73 my $same_sql = eq_sql($sql1, $sql2);
74 my $same_bind = eq_bind($bind_ref1, $bind_ref2);
76 # call Test::Builder::ok
77 $tb->ok($same_sql && $same_bind, $msg);
81 _sql_differ_diag($sql1, $sql2);
84 _bind_differ_diag($bind_ref1, $bind_ref2);
89 my ($sql1, $sql2, $msg) = @_;
92 my $same_sql = eq_sql($sql1, $sql2);
94 # call Test::Builder::ok
95 $tb->ok($same_sql, $msg);
99 _sql_differ_diag($sql1, $sql2);
104 my ($bind_ref1, $bind_ref2, $msg) = @_;
107 my $same_bind = eq_bind($bind_ref1, $bind_ref2);
109 # call Test::Builder::ok
110 $tb->ok($same_bind, $msg);
114 _bind_differ_diag($bind_ref1, $bind_ref2);
118 sub _sql_differ_diag {
119 my ($sql1, $sql2) = @_;
121 $tb->diag("SQL expressions differ\n"
124 ."differing in :\n$sql_differ\n"
128 sub _bind_differ_diag {
129 my ($bind_ref1, $bind_ref2) = @_;
131 $tb->diag("BIND values differ\n"
132 ." got: " . Dumper($bind_ref1)
133 ."expected: " . Dumper($bind_ref2)
138 my ($sql1, $bind_ref1, $sql2, $bind_ref2) = @_;
140 return eq_sql($sql1, $sql2) && eq_bind($bind_ref1, $bind_ref2);
145 my ($bind_ref1, $bind_ref2) = @_;
147 return eq_deeply($bind_ref1, $bind_ref2);
151 my ($sql1, $sql2) = @_;
154 my $tree1 = parse($sql1);
155 my $tree2 = parse($sql2);
157 return _eq_sql($tree1, $tree2);
161 my ($left, $right) = @_;
163 # ignore top-level parentheses
164 while ($left->[0] and $left->[0] eq 'PAREN') {$left = $left->[1] }
165 while ($right->[0] and $right->[0] eq 'PAREN') {$right = $right->[1]}
167 # if both are undef i.e. ()
168 if (not grep { defined $_ } ($left->[0], $right->[0]) ) {
171 # if operators are different
172 elsif ($left->[0] ne $right->[0]) {
173 $sql_differ = sprintf "OP [$left->[0]] != [$right->[0]] in\nleft: %s\nright: %s\n",
178 # elsif operators are identical, compare operands
180 if ($left->[0] eq 'EXPR' ) { # unary operator
181 (my $l = " $left->[1] " ) =~ s/\s+/ /g;
182 (my $r = " $right->[1] ") =~ s/\s+/ /g;
183 my $eq = $case_sensitive ? $l eq $r : uc($l) eq uc($r);
184 $sql_differ = "[$left->[1]] != [$right->[1]]\n" if not $eq;
187 else { # binary operator
188 return _eq_sql($left->[1][0], $right->[1][0]) # left operand
189 && _eq_sql($left->[1][1], $right->[1][1]); # right operand
198 # tokenize string, and remove all optional whitespace
200 foreach my $token (split $tokenizer_re, $s) {
202 $token =~ s/\s+([^\w\s])/$1/g;
203 $token =~ s/([^\w\s])\s+/$1/g;
204 push @$tokens, $token if $token !~ /^$/;
207 my $tree = _recurse_parse($tokens, PARSE_TOP_LEVEL);
212 my ($tokens, $state) = @_;
215 while (1) { # left-associative parsing
217 my $lookahead = $tokens->[0];
218 return $left if !defined($lookahead)
219 || ($state == PARSE_IN_PARENS && $lookahead eq ')')
220 || ($state == PARSE_IN_EXPR && grep { $lookahead =~ /^$_$/xi }
221 '\)', @expression_terminator_sql_keywords
224 my $token = shift @$tokens;
226 # nested expression in ()
228 my $right = _recurse_parse($tokens, PARSE_IN_PARENS);
229 $token = shift @$tokens or croak "missing ')'";
230 $token eq ')' or croak "unexpected token : $token";
231 $left = $left ? [CONCAT => [$left, [PAREN => $right]]]
235 elsif ($token eq 'AND' || $token eq 'OR') {
236 my $right = _recurse_parse($tokens, PARSE_IN_EXPR);
237 $left = [$token => [$left, $right]];
239 # expression terminator keywords (as they start a new expression)
240 elsif (grep { $token =~ /^$_$/xi } @expression_terminator_sql_keywords) {
241 my $right = _recurse_parse($tokens, PARSE_IN_EXPR);
242 $left = $left ? [CONCAT => [$left, [CONCAT => [[EXPR => $token], [PAREN => $right]]]]]
243 : [CONCAT => [[EXPR => $token], [PAREN => $right]]];
247 $left = $left ? [CONCAT => [$left, [EXPR => $token]]]
258 EXPR => sub {$tree->[1] },
259 PAREN => sub {"(" . unparse($tree->[1]) . ")" },
260 CONCAT => sub {join " ", map {unparse($_)} @{$tree->[1]}},
261 AND => sub {join " AND ", map {unparse($_)} @{$tree->[1]}},
262 OR => sub {join " OR ", map {unparse($_)} @{$tree->[1]}},
264 $dispatch->{$tree->[0]}->();
275 SQL::Abstract::Test - Helper function for testing SQL::Abstract
281 use SQL::Abstract::Test import => [qw/
282 is_same_sql_bind is_same_sql is_same_bind
283 eq_sql_bind eq_sql eq_bind
286 my ($sql, @bind) = SQL::Abstract->new->select(%args);
288 is_same_sql_bind($given_sql, \@given_bind,
289 $expected_sql, \@expected_bind, $test_msg);
291 is_same_sql($given_sql, $expected_sql, $test_msg);
292 is_same_bind(\@given_bind, \@expected_bind, $test_msg);
294 my $is_same = eq_sql_bind($given_sql, \@given_bind,
295 $expected_sql, \@expected_bind);
297 my $sql_same = eq_sql($given_sql, $expected_sql);
298 my $bind_same = eq_bind(\@given_bind, \@expected_bind);
302 This module is only intended for authors of tests on
303 L<SQL::Abstract|SQL::Abstract> and related modules;
304 it exports functions for comparing two SQL statements
305 and their bound values.
307 The SQL comparison is performed on I<abstract syntax>,
308 ignoring differences in spaces or in levels of parentheses.
309 Therefore the tests will pass as long as the semantics
310 is preserved, even if the surface syntax has changed.
312 B<Disclaimer> : this is only a half-cooked semantic equivalence;
313 parsing is simple-minded, and comparison of SQL abstract syntax trees
314 ignores commutativity or associativity of AND/OR operators, Morgan
319 =head2 is_same_sql_bind
321 is_same_sql_bind($given_sql, \@given_bind,
322 $expected_sql, \@expected_bind, $test_msg);
324 Compares given and expected pairs of C<($sql, \@bind)>, and calls
325 L<Test::Builder/ok> on the result, with C<$test_msg> as message. If the test
326 fails, a detailed diagnostic is printed. For clients which use L<Test::More>,
327 this is the one of the three functions (L</is_same_sql_bind>, L</is_same_sql>,
328 L</is_same_bind>) that needs to be imported.
332 is_same_sql($given_sql, $expected_sql, $test_msg);
334 Compares given and expected SQL statements, and calls L<Test::Builder/ok> on
335 the result, with C<$test_msg> as message. If the test fails, a detailed
336 diagnostic is printed. For clients which use L<Test::More>, this is the one of
337 the three functions (L</is_same_sql_bind>, L</is_same_sql>, L</is_same_bind>)
338 that needs to be imported.
342 is_same_bind(\@given_bind, \@expected_bind, $test_msg);
344 Compares given and expected bind values, and calls L<Test::Builder/ok> on the
345 result, with C<$test_msg> as message. If the test fails, a detailed diagnostic
346 is printed. For clients which use L<Test::More>, this is the one of the three
347 functions (L</is_same_sql_bind>, L</is_same_sql>, L</is_same_bind>) that needs
352 my $is_same = eq_sql_bind($given_sql, \@given_bind,
353 $expected_sql, \@expected_bind);
355 Compares given and expected pairs of C<($sql, \@bind)>. Similar to
356 L</is_same_sql_bind>, but it just returns a boolean value and does not print
357 diagnostics or talk to L<Test::Builder>.
361 my $is_same = eq_sql($given_sql, $expected_sql);
363 Compares the abstract syntax of two SQL statements. Similar to L</is_same_sql>,
364 but it just returns a boolean value and does not print diagnostics or talk to
365 L<Test::Builder>. If the result is false, the global variable L</$sql_differ>
366 will contain the SQL portion where a difference was encountered; this is useful
367 for printing diagnostics.
371 my $is_same = eq_sql(\@given_bind, \@expected_bind);
373 Compares two lists of bind values, taking into account the fact that some of
374 the values may be arrayrefs (see L<SQL::Abstract/bindtype>). Similar to
375 L</is_same_bind>, but it just returns a boolean value and does not print
376 diagnostics or talk to L<Test::Builder>.
378 =head1 GLOBAL VARIABLES
380 =head2 $case_sensitive
382 If true, SQL comparisons will be case-sensitive. Default is false;
386 When L</eq_sql> returns false, the global variable
387 C<$sql_differ> contains the SQL portion
388 where a difference was encountered.
393 L<SQL::Abstract>, L<Test::More>, L<Test::Builder>.
397 Laurent Dami, E<lt>laurent.dami AT etat geneve chE<gt>
399 Norbert Buchmuller <norbi@nix.hu>
401 =head1 COPYRIGHT AND LICENSE
403 Copyright 2008 by Laurent Dami.
405 This library is free software; you can redistribute it and/or modify
406 it under the same terms as Perl itself.