X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F10test.t;h=f3835a1a96d6a91bf1d4c73569cb81a3b30de0de;hb=0c2de280869928d9ff1ee95f36a9a45318766990;hp=d83dd652f43aacd44d18346a2c620a74447185b0;hpb=69cc1bd8159da049bc595c1b6ce95ab73ddf9e4e;p=scpubgit%2FQ-Branch.git diff --git a/t/10test.t b/t/10test.t index d83dd65..f3835a1 100644 --- a/t/10test.t +++ b/t/10test.t @@ -153,7 +153,7 @@ my @sql_tests = ( }, { equal => 0, - parenthesis_significant => 1, + opts => { parenthesis_significant => 1 }, statements => [ q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND c = 1/, q/SELECT foo FROM bar WHERE (a = 1 AND b = 1 AND c = 1)/, @@ -164,7 +164,7 @@ my @sql_tests = ( }, { equal => 0, - parenthesis_significant => 1, + opts => { parenthesis_significant => 1 }, statements => [ q/SELECT foo FROM bar WHERE a = 1 OR b = 1 OR c = 1/, q/SELECT foo FROM bar WHERE (a = 1 OR b = 1) OR c = 1/, @@ -174,7 +174,7 @@ my @sql_tests = ( }, { equal => 0, - parenthesis_significant => 1, + opts => { parenthesis_significant => 1 }, statements => [ q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1 OR c = 1 OR d = 1) AND (e = 1 AND f = 1)/, q/SELECT foo FROM bar WHERE a = 1 AND (b = 1 OR c = 1 OR d = 1) AND e = 1 AND (f = 1)/, @@ -260,7 +260,7 @@ my @sql_tests = ( }, { equal => 0, - parenthesis_significant => 1, + opts => { parenthesis_significant => 1 }, statements => [ q/SELECT foo FROM bar WHERE a IN (1,2,3)/, q/SELECT foo FROM bar WHERE a IN (1,3,2)/, @@ -282,6 +282,17 @@ my @sql_tests = ( ] }, + # IS NULL (special LHS-only op) + { + equal => 1, + statements => [ + q/WHERE a IS NOT NULL AND b IS NULL/, + q/WHERE (a IS NOT NULL) AND b IS NULL/, + q/WHERE a IS NOT NULL AND (b IS NULL)/, + q/WHERE (a IS NOT NULL) AND ((b IS NULL))/, + ], + }, + # JOIN condition - equal { equal => 1, @@ -581,6 +592,34 @@ my @sql_tests = ( ] }, + # order by + { + equal => 1, + statements => [ + q/SELECT * FROM foo ORDER BY bar/, + q/SELECT * FROM foo ORDER BY bar ASC/, + q/SELECT * FROM foo ORDER BY bar asc/, + ], + }, + { + equal => 1, + statements => [ + q/SELECT * FROM foo ORDER BY bar, baz ASC/, + q/SELECT * FROM foo ORDER BY bar ASC, baz/, + q/SELECT * FROM foo ORDER BY bar asc, baz ASC/, + q/SELECT * FROM foo ORDER BY bar, baz/, + ], + }, + { + equal => 0, + opts => { order_by_asc_significant => 1 }, + statements => [ + q/SELECT * FROM foo ORDER BY bar/, + q/SELECT * FROM foo ORDER BY bar ASC/, + q/SELECT * FROM foo ORDER BY bar desc/, + ], + }, + # list permutations { equal => 0, @@ -604,6 +643,20 @@ my @sql_tests = ( 'SELECT * FROM foo WHERE a IN (3,2,1)', ] }, + + # list consistency + { + equal => 0, + statements => [ + 'SELECT a,b FROM foo', + 'SELECT a,,b FROM foo', + 'SELECT a,b, FROM foo', + 'SELECT ,a,b, FROM foo', + 'SELECT ,a,,b, FROM foo', + ], + }, + + # misc func { equal => 0, statements => [ @@ -614,7 +667,6 @@ my @sql_tests = ( 'SELECT count(1) FROM foo', ] }, - # func { equal => 1, statements => [ @@ -635,6 +687,31 @@ my @sql_tests = ( { equal => 0, statements => [ + 'SELECT COUNT * FROM foo', + 'SELECT COUNT( * ) FROM foo', + ] + }, + # single ? of unknown funcs do not unroll unless + # explicitly allowed (e.g. Like) + { + equal => 0, + statements => [ + 'SELECT foo FROM bar WHERE bar > foo ?', + 'SELECT foo FROM bar WHERE bar > foo( ? )', + ] + }, + { + equal => 1, + statements => [ + 'SELECT foo FROM bar WHERE bar LIKE ?', + 'SELECT foo FROM bar WHERE bar LiKe (?)', + 'SELECT foo FROM bar WHERE bar lIkE( (?))', + ] + }, + # test multival + { + equal => 0, + statements => [ 'SELECT foo FROM bar WHERE foo IN (?, ?)', 'SELECT foo FROM bar WHERE foo IN ?, ?', ] @@ -655,6 +732,33 @@ my @sql_tests = ( 'SELECT * FROM foo WHERE bar = (baz( buzz ))', ] }, + # oddballs + { + equal => 1, + statements => [ + 'WHERE ( foo GLOB ? )', + 'WHERE foo GLOB ?', + ], + }, + { + equal => 1, + statements => [ + 'SELECT FIRST ? SKIP ? [me].[id], [me].[owner] + FROM [books] [me] + WHERE ( ( (EXISTS ( + SELECT FIRST ? SKIP ? [owner].[id] + FROM [owners] [owner] + WHERE ( [books].[owner] = [owner].[id] ) + )) AND [source] = ? ) )', + 'SELECT FIRST ? SKIP ? [me].[id], [me].[owner] + FROM [books] [me] + WHERE ( ( EXISTS ( + SELECT FIRST ? SKIP ? [owner].[id] + FROM [owners] [owner] + WHERE ( [books].[owner] = [owner].[id] ) + ) AND [source] = ? ) )', + ], + }, ); my @bind_tests = ( @@ -860,21 +964,30 @@ plan tests => 1 + map { scalar @{$_->{bindvals}} } @bind_tests ) + - 3; + 9; use_ok('SQL::Abstract::Test', import => [qw( eq_sql_bind eq_sql eq_bind is_same_sql_bind )]); for my $test (@sql_tests) { + + # this does not work on 5.8.8 and earlier :( + #local @{*SQL::Abstract::Test::}{keys %{$test->{opts}}} = map { \$_ } values %{$test->{opts}} + # if $test->{opts}; + + my %restore_globals; + + for (keys %{$test->{opts} || {} }) { + $restore_globals{$_} = ${${*SQL::Abstract::Test::}{$_}}; + ${*SQL::Abstract::Test::}{$_} = \ do { my $cp = $test->{opts}{$_} }; + } + my $statements = $test->{statements}; while (@$statements) { my $sql1 = shift @$statements; foreach my $sql2 (@$statements) { - no warnings qw/once/; # perl 5.10 is dumb - local $SQL::Abstract::Test::parenthesis_significant = $test->{parenthesis_significant} - if $test->{parenthesis_significant}; my $equal = eq_sql($sql1, $sql2); TODO: { @@ -888,17 +1001,20 @@ for my $test (@sql_tests) { if ($equal ^ $test->{equal}) { my ($ast1, $ast2) = map { SQL::Abstract::Test::parse ($_) } ($sql1, $sql2); - $_ = Dumper $_ for ($ast1, $ast2); - diag("sql1: $sql1"); - diag("sql2: $sql2"); - note("ast1: $ast1"); - note("ast2: $ast2"); + diag "sql1: $sql1"; + diag "sql2: $sql2"; + note $SQL::Abstract::Test::sql_differ; + note "ast1: $ast1"; + note "ast2: $ast2"; } } } } + + ${*SQL::Abstract::Test::}{$_} = \$restore_globals{$_} + for keys %restore_globals; } for my $test (@bind_tests) { @@ -942,3 +1058,36 @@ ok(!eq_sql_bind( ), "eq_sql_bind considers different SQL expressions and equal bind values different" ); + +# test diag string +ok (! eq_sql ( + 'SELECT owner_name FROM books me WHERE ( source = ? )', + 'SELECT owner_name FROM books me WHERE ( sUOrce = ? )', +)); +like( + $SQL::Abstract::Test::sql_differ, + qr/\Q[ source ] != [ sUOrce ]/, + 'expected debug of literal diff', +); + +ok (! eq_sql ( + 'SELECT owner_name FROM books me ORDER BY owner_name', + 'SELECT owner_name FROM books me GROUP BY owner_name', +)); +like( + $SQL::Abstract::Test::sql_differ, + qr/\QOP [ORDER BY] != [GROUP BY]/, + 'expected debug of op diff', +); + +ok (! eq_sql ( + 'SELECT owner_name FROM books WHERE ( source = ? )', + 'SELECT owner_name FROM books' +)); + +like( + $SQL::Abstract::Test::sql_differ, + qr|\Q[WHERE source = ?] != [N/A]|, + 'expected debug of missing branch', +); +