X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F11parser.t;h=202e5fa4d9916d86f9f38b5b115dd5f370d9d046;hb=1ec9b9e3261f37de1bd05b31fa3c88ab78ab1480;hp=7f4dae77e79a6cafe3a15e6c56bf3f32ac8406e9;hpb=0f9a26cb13f772fe0813987a0641baf193fa9782;p=dbsrgits%2FSQL-Abstract.git diff --git a/t/11parser.t b/t/11parser.t index 7f4dae7..202e5fa 100644 --- a/t/11parser.t +++ b/t/11parser.t @@ -2,50 +2,46 @@ use strict; use warnings; use Test::More; -use Test::Deep; use Test::Warn; use SQL::Abstract::Tree; my $sqlat = SQL::Abstract::Tree->new; - -cmp_deeply($sqlat->parse("SELECT a, b.*, * FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [ +is_deeply($sqlat->parse("SELECT a, b.*, * FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [ [ + "SELECT", [ - "SELECT", [ + "-LIST", [ - "LIST", [ + "-LITERAL", [ - "LITERAL", - [ - "a" - ] - ], + "a" + ] + ], + [ + "-LITERAL", [ - "LITERAL", - [ - "b.*" - ] - ], + "b.*" + ] + ], + [ + "-LITERAL", [ - "LITERAL", - [ - "*" - ] + "*" ] ] ] ] - ], + ] + ], + [ + "FROM", [ - "FROM", [ + "-LITERAL", [ - "LITERAL", - [ - "foo" - ] + "foo" ] ] ] @@ -60,13 +56,13 @@ cmp_deeply($sqlat->parse("SELECT a, b.*, * FROM foo WHERE foo.a =1 and foo.b LIK "=", [ [ - "LITERAL", + "-LITERAL", [ "foo.a" ] ], [ - "LITERAL", + "-LITERAL", [ 1 ] @@ -77,13 +73,13 @@ cmp_deeply($sqlat->parse("SELECT a, b.*, * FROM foo WHERE foo.a =1 and foo.b LIK "LIKE", [ [ - "LITERAL", + "-LITERAL", [ "foo.b" ] ], [ - "LITERAL", + "-LITERAL", [ "'station'" ] @@ -96,31 +92,32 @@ cmp_deeply($sqlat->parse("SELECT a, b.*, * FROM foo WHERE foo.a =1 and foo.b LIK ] ], 'simple statement parsed correctly'); -cmp_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"), [ +is_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) foo WHERE foo.a =1 and foo.b LIKE 'station'"), [ [ + "SELECT", [ - "SELECT", [ + "-LITERAL", [ - "LITERAL", - [ - "*" - ] + "*" ] ] - ], + ] + ], + [ + "FROM", [ - "FROM", [ + "-MISC", [ - "PAREN", [ + "-PAREN", [ [ "SELECT", [ [ - "LITERAL", + "-LITERAL", [ "*" ] @@ -131,7 +128,7 @@ cmp_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 a "FROM", [ [ - "LITERAL", + "-LITERAL", [ "foobar" ] @@ -139,6 +136,12 @@ cmp_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 a ] ] ] + ], + [ + "-LITERAL", + [ + "foo" + ] ] ] ] @@ -154,13 +157,13 @@ cmp_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 a "=", [ [ - "LITERAL", + "-LITERAL", [ "foo.a" ] ], [ - "LITERAL", + "-LITERAL", [ 1 ] @@ -171,13 +174,13 @@ cmp_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 a "LIKE", [ [ - "LITERAL", + "-LITERAL", [ "foo.b" ] ], [ - "LITERAL", + "-LITERAL", [ "'station'" ] @@ -190,384 +193,302 @@ cmp_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 a ] ], 'subquery statement parsed correctly'); -cmp_deeply($sqlat->parse("SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"), [ +is_deeply($sqlat->parse( "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ( [me].[user_id] = ? ) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]"), [ [ + "SELECT", [ - "SELECT", [ + "-LIST", [ - "LITERAL", [ - "*" + "-LITERAL", + [ + "[screen].[id]" + ] + ], + [ + "-LITERAL", + [ + "[screen].[name]" + ] + ], + [ + "-LITERAL", + [ + "[screen].[section_id]" + ] + ], + [ + "-LITERAL", + [ + "[screen].[xtype]" + ] ] ] ] - ], + ] + ], + [ + "FROM", [ - "FROM", [ + "-MISC", [ - "LITERAL", [ - "lolz" + "-LITERAL", + [ + "[users_roles]" + ] + ], + [ + "-LITERAL", + [ + "[me]" + ] ] ] ] ] ], [ - "WHERE", + "JOIN", [ [ - "AND", + "-MISC", [ [ - "PAREN", + "-LITERAL", [ - [ - "=", - [ - [ - "LITERAL", - [ - "foo.a" - ] - ], - [ - "LITERAL", - [ - 1 - ] - ] - ] - ] + "[roles]" ] ], [ - "LIKE", + "-LITERAL", [ - [ - "LITERAL", - [ - "foo.b" - ] - ], - [ - "LITERAL", - [ - "'station'" - ] - ] + "[role]" ] ] ] ] ] - ] -], 'simple statement with parens in where parsed correctly'); - -cmp_deeply($sqlat->parse( "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ( [me].[user_id] = ? ) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]"), [ + ], [ + "ON", [ [ + "=", [ [ + "-LITERAL", [ - [ - [ - [ - [ - [ - [ - [ - "SELECT", - [ - [ - "LIST", - [ - [ - "LITERAL", - [ - "[screen].[id]" - ] - ], - [ - "LITERAL", - [ - "[screen].[name]" - ] - ], - [ - "LITERAL", - [ - "[screen].[section_id]" - ] - ], - [ - "LITERAL", - [ - "[screen].[xtype]" - ] - ] - ] - ] - ] - ], - [ - "FROM", - [ - [ - [ - "LITERAL", - [ - "[users_roles]" - ] - ], - [ - "LITERAL", - [ - "[me]" - ] - ] - ] - ] - ] - ], - [ - "JOIN", - [ - [ - [ - "LITERAL", - [ - "[roles]" - ] - ], - [ - "LITERAL", - [ - "[role]" - ] - ] - ] - ] - ] - ], - [ - "ON", - [ - [ - "=", - [ - [ - "LITERAL", - [ - "[role].[id]" - ] - ], - [ - "LITERAL", - [ - "[me].[role_id]" - ] - ] - ] - ] - ] - ] - ], - [ - "JOIN", - [ - [ - [ - "LITERAL", - [ - "[roles_permissions]" - ] - ], - [ - "LITERAL", - [ - "[role_permissions]" - ] - ] - ] - ] - ] - ], - [ - "ON", - [ - [ - "=", - [ - [ - "LITERAL", - [ - "[role_permissions].[role_id]" - ] - ], - [ - "LITERAL", - [ - "[role].[id]" - ] - ] - ] - ] - ] - ] - ], - [ - "JOIN", - [ - [ - [ - "LITERAL", - [ - "[permissions]" - ] - ], - [ - "LITERAL", - [ - "[permission]" - ] - ] - ] - ] - ] - ], - [ - "ON", - [ - [ - "=", - [ - [ - "LITERAL", - [ - "[permission].[id]" - ] - ], - [ - "LITERAL", - [ - "[role_permissions].[permission_id]" - ] - ] - ] - ] - ] - ] - ], - [ - "JOIN", - [ - [ - [ - "LITERAL", - [ - "[permissionscreens]" - ] - ], - [ - "LITERAL", - [ - "[permission_screens]" - ] - ] - ] - ] + "[role].[id]" ] ], [ - "ON", + "-LITERAL", [ - [ - "=", - [ - [ - "LITERAL", - [ - "[permission_screens].[permission_id]" - ] - ], - [ - "LITERAL", - [ - "[permission].[id]" - ] - ] - ] - ] + "[me].[role_id]" ] ] - ], + ] + ] + ] + ], + [ + "JOIN", + [ + [ + "-MISC", [ - "JOIN", [ + "-LITERAL", [ - [ - "LITERAL", - [ - "[screens]" - ] - ], - [ - "LITERAL", - [ - "[screen]" - ] - ] + "[roles_permissions]" ] - ] - ] - ], - [ - "ON", - [ + ], [ - "=", + "-LITERAL", [ - [ - "LITERAL", - [ - "[screen].[id]" - ] - ], - [ - "LITERAL", - [ - "[permission_screens].[screen_id]" - ] - ] + "[role_permissions]" ] ] ] ] - ], + ] + ], + [ + "ON", + [ + [ + "=", + [ + [ + "-LITERAL", + [ + "[role_permissions].[role_id]" + ] + ], + [ + "-LITERAL", + [ + "[role].[id]" + ] + ] + ] + ] + ] + ], + [ + "JOIN", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "[permissions]" + ] + ], + [ + "-LITERAL", + [ + "[permission]" + ] + ] + ] + ] + ] + ], + [ + "ON", + [ + [ + "=", + [ + [ + "-LITERAL", + [ + "[permission].[id]" + ] + ], + [ + "-LITERAL", + [ + "[role_permissions].[permission_id]" + ] + ] + ] + ] + ] + ], + [ + "JOIN", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "[permissionscreens]" + ] + ], + [ + "-LITERAL", + [ + "[permission_screens]" + ] + ] + ] + ] + ] + ], + [ + "ON", + [ + [ + "=", + [ + [ + "-LITERAL", + [ + "[permission_screens].[permission_id]" + ] + ], + [ + "-LITERAL", + [ + "[permission].[id]" + ] + ] + ] + ] + ] + ], + [ + "JOIN", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "[screens]" + ] + ], + [ + "-LITERAL", + [ + "[screen]" + ] + ] + ] + ] + ] + ], + [ + "ON", + [ + [ + "=", + [ + [ + "-LITERAL", + [ + "[screen].[id]" + ] + ], + [ + "-LITERAL", + [ + "[permission_screens].[screen_id]" + ] + ] + ] + ] + ] + ], + [ + "WHERE", [ - "WHERE", [ + "-PAREN", [ - "PAREN", [ + "=", [ - "=", [ + "-LITERAL", [ - "LITERAL", - [ - "[me].[user_id]" - ] - ], + "[me].[user_id]" + ] + ], + [ + "-PLACEHOLDER", [ - "PLACEHOLDER", - [ - "?" - ] + "?" ] ] ] @@ -580,28 +501,28 @@ cmp_deeply($sqlat->parse( "SELECT [screen].[id], [screen].[name], [screen].[sect "GROUP BY", [ [ - "LIST", + "-LIST", [ [ - "LITERAL", + "-LITERAL", [ "[screen].[id]" ] ], [ - "LITERAL", + "-LITERAL", [ "[screen].[name]" ] ], [ - "LITERAL", + "-LITERAL", [ "[screen].[section_id]" ] ], [ - "LITERAL", + "-LITERAL", [ "[screen].[xtype]" ] @@ -612,38 +533,36 @@ cmp_deeply($sqlat->parse( "SELECT [screen].[id], [screen].[name], [screen].[sect ] ], 'real life statement 1 parsed correctly'); -cmp_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [ +is_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [ [ + "SELECT", [ - "SELECT", [ + "-LIST", [ - "LIST", [ + "-LITERAL", [ - "LITERAL", - [ - "x" - ] - ], + "x" + ] + ], + [ + "-LITERAL", [ - "LITERAL", - [ - "y" - ] + "y" ] ] ] ] - ], + ] + ], + [ + "FROM", [ - "FROM", [ + "-LITERAL", [ - "LITERAL", - [ - "foo" - ] + "foo" ] ] ] @@ -655,37 +574,37 @@ cmp_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [ "IN", [ [ - "LITERAL", + "-LITERAL", [ "x" ] ], [ - "PAREN", + "-PAREN", [ [ - "LIST", + "-LIST", [ [ - "PLACEHOLDER", + "-PLACEHOLDER", [ "?" ] ], [ - "PLACEHOLDER", + "-PLACEHOLDER", [ "?" ] ], [ - "PLACEHOLDER", + "-PLACEHOLDER", [ "?" ] ], [ - "PLACEHOLDER", + "-PLACEHOLDER", [ "?" ] @@ -700,9 +619,518 @@ cmp_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [ ] ], 'Lists parsed correctly'); +is_deeply($sqlat->parse('SELECT foo FROM bar ORDER BY x + ? DESC, oomph, y - ? DESC, unf, baz.g / ? ASC, buzz * 0 DESC, foo DESC, ickk ASC'), [ + [ + "SELECT", + [ + [ + "-LITERAL", + [ + "foo" + ] + ] + ] + ], + [ + "FROM", + [ + [ + "-LITERAL", + [ + "bar" + ] + ] + ] + ], + [ + "ORDER BY", + [ + [ + "-LIST", + [ + [ + "-DESC", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "x" + ] + ], + [ + "-LITERAL", + [ + "+" + ] + ] + ] + ], + [ + "-PLACEHOLDER", + [ + "?" + ] + ] + ] + ], + [ + "-LITERAL", + [ + "oomph" + ] + ], + [ + "-DESC", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "y" + ] + ], + [ + "-LITERAL", + [ + "-" + ] + ] + ] + ], + [ + "-PLACEHOLDER", + [ + "?" + ] + ] + ] + ], + [ + "-LITERAL", + [ + "unf" + ] + ], + [ + "-ASC", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "baz.g" + ] + ], + [ + "-LITERAL", + [ + "/" + ] + ] + ] + ], + [ + "-PLACEHOLDER", + [ + "?" + ] + ] + ] + ], + [ + "-DESC", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "buzz" + ] + ], + [ + "-LITERAL", + [ + "*" + ] + ], + [ + "-LITERAL", + [ + 0 + ] + ] + ] + ] + ] + ], + [ + "-DESC", + [ + [ + "-LITERAL", + [ + "foo" + ] + ] + ] + ], + [ + "-ASC", + [ + [ + "-LITERAL", + [ + "ickk" + ] + ] + ] + ] + ] + ] + ] + ] +], 'Crazy ORDER BY parsed correctly'); + + +is_deeply($sqlat->parse("SELECT * * FROM (SELECT *, FROM foobar baz buzz) foo bar WHERE NOT NOT NOT EXISTS (SELECT 'cr,ap') AND foo.a = ? and not (foo.b LIKE 'station') and x = y and a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [ + [ + "SELECT", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "*" + ] + ], + [ + "-LITERAL", + [ + "*" + ] + ] + ] + ] + ] + ], + [ + "FROM", + [ + [ + "-MISC", + [ + [ + "-PAREN", + [ + [ + "SELECT", + [ + [ + "-LIST", + [ + [ + "-LITERAL", + [ + "*" + ] + ], + [] + ] + ] + ] + ], + [ + "FROM", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "foobar" + ] + ], + [ + "-LITERAL", + [ + "baz" + ] + ], + [ + "-LITERAL", + [ + "buzz" + ] + ] + ] + ] + ] + ] + ] + ], + [ + "-LITERAL", + [ + "foo" + ] + ], + [ + "-LITERAL", + [ + "bar" + ] + ] + ] + ] + ] + ], + [ + "WHERE", + [ + [ + "AND", + [ + [ + "NOT", + [] + ], + [ + "NOT", + [] + ], + [ + "NOT EXISTS", + [ + [ + "-PAREN", + [ + [ + "SELECT", + [ + [ + "-LIST", + [ + [ + "-LITERAL", + [ + "'cr" + ] + ], + [ + "-LITERAL", + [ + "ap'" + ] + ] + ] + ] + ] + ] + ] + ] + ] + ], + [ + "=", + [ + [ + "-LITERAL", + [ + "foo.a" + ] + ], + [ + "-PLACEHOLDER", + [ + "?" + ] + ] + ] + ], + [ + "NOT", + [ + [ + "-PAREN", + [ + [ + "LIKE", + [ + [ + "-LITERAL", + [ + "foo.b" + ] + ], + [ + "-LITERAL", + [ + "'station'" + ] + ] + ] + ] + ] + ] + ] + ], + [ + "=", + [ + [ + "-LITERAL", + [ + "x" + ] + ], + [ + "-LITERAL", + [ + "y" + ] + ] + ] + ], + [ + "=", + [ + [ + "-LITERAL", + [ + "a" + ] + ], + [ + "-LITERAL", + [ + "b" + ] + ] + ] + ] + ] + ] + ] + ], + [ + "GROUP BY", + [ + [ + "-LIST", + [ + [], + [] + ] + ] + ] + ], + [ + "ORDER BY", + [ + [ + "-LIST", + [ + [ + "-ASC", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "x" + ] + ], + [ + "-LITERAL", + [ + "x1" + ] + ], + [ + "-LITERAL", + [ + "x2" + ] + ], + [ + "-LITERAL", + [ + "y" + ] + ] + ] + ], + ], + ], + [ + "max", + [ + [ + "-DESC", + [ + [ + "-MISC", + [ + [ + "-DESC", + [ + [ + "-PAREN", + [ + [ + "-LITERAL", + [ + "y" + ] + ] + ] + ] + ] + ], + [ + "-LITERAL", + [ + "x" + ] + ], + [ + "-LITERAL", + [ + "z" + ] + ] + ] + ] + ] + ] + ] + ] + ] + ] + ] + ] +], 'Deliberately malformed SQL parsed "correctly"'); + + # test for recursion warnings on huge selectors +my @lst = ('AA' .. 'zz'); +#@lst = ('AAA' .. 'zzz'); # if you really want to wait a while warnings_are { - my $sql = sprintf 'SELECT %s FROM foo', join (', ', map { qq|"$_"| } 'aa' .. 'zz' ); + my $sql = sprintf 'SELECT %s FROM foo', join (', ', (map { qq|( "$_" )| } @lst), (map { qq|"$_"| } @lst), (map { qq|"$_", ( "$_" )| } @lst) ); my $tree = $sqlat->parse($sql); is_deeply( $tree, [ @@ -710,9 +1138,11 @@ warnings_are { "SELECT", [ [ - "LIST", + "-LIST", [ - map { [ "LITERAL", [ qq|"$_"| ] ] } ('aa' .. 'zz') + (map { [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst), + (map { [ -LITERAL => [ qq|"$_"| ] ] } @lst), + (map { [ -LITERAL => [ qq|"$_"| ] ], [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst), ] ] ] @@ -721,7 +1151,7 @@ warnings_are { "FROM", [ [ - "LITERAL", + "-LITERAL", [ "foo" ]