X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F10test.t;h=9c6934374e61395818939c0b1e6605ac584968e3;hb=b6c0db745a49a449dc55b0ad075bfd369d96723e;hp=1aa26f21ca9adc3a716815cff839396fdf975452;hpb=b6893a0f914f78846f5600162200ed9e4bcd1f56;p=dbsrgits%2FSQL-Abstract.git diff --git a/t/10test.t b/t/10test.t index 1aa26f2..9c69343 100644 --- a/t/10test.t +++ b/t/10test.t @@ -6,17 +6,9 @@ use List::Util qw(sum); use Test::More; -# equivalent to $Module::Install::AUTHOR -my $author = ( - ( not -d './inc' ) - or - ( -e ($^O eq 'VMS' ? './inc/_author' : './inc/.author') ) -); - -if (not $author and not $ENV{SQLATEST_TESTER} and not $ENV{AUTOMATED_TESTING}) { - plan skip_all => 'Skipping resource intensive self-tests, use SQLATEST_TESTER=1 to run'; -} - +use Data::Dumper; +$Data::Dumper::Terse = 1; +$Data::Dumper::Sortkeys = 1; my @sql_tests = ( # WHERE condition - equal @@ -290,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, @@ -542,6 +545,13 @@ my @sql_tests = ( { equal => 0, statements => [ + q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM (SELECT * FROM cd me WHERE ( year != ? ) GROUP BY me.cdid) me WHERE ( year != ? ) ) )/, + q/DELETE FROM cd WHERE ( cdid IN ( SELECT me.cdid FROM cd me WHERE ( year != ? ) GROUP BY me.cdid ) )/, + ], + }, + { + equal => 0, + statements => [ q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/, q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/, q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/, @@ -581,36 +591,127 @@ my @sql_tests = ( q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/, ] }, + + # list permutations + { + equal => 0, + statements => [ + 'SELECT a,b,c FROM foo', + 'SELECT a,c,b FROM foo', + 'SELECT b,a,c FROM foo', + 'SELECT b,c,a FROM foo', + 'SELECT c,a,b FROM foo', + 'SELECT c,b,a FROM foo', + ], + }, + { + equal => 0, + statements => [ + 'SELECT * FROM foo WHERE a IN (1,2,3)', + 'SELECT * FROM foo WHERE a IN (1,3,2)', + 'SELECT * FROM foo WHERE a IN (2,1,3)', + 'SELECT * FROM foo WHERE a IN (2,3,1)', + 'SELECT * FROM foo WHERE a IN (3,1,2)', + '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 => [ + 'SELECT count(*) FROM foo', + 'SELECT count(*) AS bar FROM foo', + 'SELECT count(*) AS "bar" FROM foo', + 'SELECT count(a) FROM foo', + 'SELECT count(1) FROM foo', + ] + }, + { + equal => 1, + statements => [ + 'SELECT foo() bar FROM baz', + 'SELECT foo ( )bar FROM baz', + 'SELECT foo (())bar FROM baz', + 'SELECT foo(( ) ) bar FROM baz', + ] + }, { - equal => 0, - statements => [ - 'SELECT a,b,c FROM foo', - 'SELECT a,c,b FROM foo', - 'SELECT b,a,c FROM foo', - 'SELECT b,c,a FROM foo', - 'SELECT c,a,b FROM foo', - 'SELECT c,b,a FROM foo', - ] + equal => 0, + statements => [ + 'SELECT foo() FROM bar', + 'SELECT foo FROM bar', + 'SELECT foo FROM bar ()', + ] + }, + { + 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 => 0, - statements => [ - 'SELECT count(*) FROM foo', - 'SELECT count(a) FROM foo', - 'SELECT count(1) FROM 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 * FROM foo WHERE a IN (1,2,3)', - 'SELECT * FROM foo WHERE a IN (1,3,2)', - 'SELECT * FROM foo WHERE a IN (2,1,3)', - 'SELECT * FROM foo WHERE a IN (2,3,1)', - 'SELECT * FROM foo WHERE a IN (3,1,2)', - 'SELECT * FROM foo WHERE a IN (3,2,1)', - ] + equal => 0, + statements => [ + 'SELECT foo FROM bar WHERE foo IN (?, ?)', + 'SELECT foo FROM bar WHERE foo IN ?, ?', + ] }, + # math + { + equal => 0, + statements => [ + 'SELECT * FROM foo WHERE 1 = ( a > b)', + 'SELECT * FROM foo WHERE 1 = a > b', + 'SELECT * FROM foo WHERE (1 = a) > b', + ] + }, + { + equal => 1, + statements => [ + 'SELECT * FROM foo WHERE bar = baz(buzz)', + 'SELECT * FROM foo WHERE bar = (baz( buzz ))', + ] + }, + # oddballs + { + equal => 1, + statements => [ + 'WHERE ( foo GLOB ? )', + 'WHERE foo GLOB ?', + ], + } ); my @bind_tests = ( @@ -816,7 +917,7 @@ 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 @@ -843,8 +944,14 @@ for my $test (@sql_tests) { } if ($equal ^ $test->{equal}) { - diag("sql1: $sql1"); - diag("sql2: $sql2"); + my ($ast1, $ast2) = map { SQL::Abstract::Test::parse ($_) } ($sql1, $sql2); + $_ = Dumper $_ for ($ast1, $ast2); + + diag "sql1: $sql1"; + diag "sql2: $sql2"; + note $SQL::Abstract::Test::sql_differ; + note "ast1: $ast1"; + note "ast2: $ast2"; } } } @@ -892,3 +999,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', +); +