X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F11parser.t;h=5ce1a8021a4362f13e81c14e69828c003d690312;hb=08e1636016045f0f3881f5af287dcfd482845fe9;hp=7f4dae77e79a6cafe3a15e6c56bf3f32ac8406e9;hpb=0f9a26cb13f772fe0813987a0641baf193fa9782;p=dbsrgits%2FSQL-Abstract.git diff --git a/t/11parser.t b/t/11parser.t index 7f4dae7..5ce1a80 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,72 +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", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "[users_roles]" + ] + ], + [ + "-LITERAL", + [ + "[me]" + ] + ] + ] + ] + ] + ], + [ + "JOIN", [ - "FROM", [ + "-MISC", [ - "LITERAL", [ - "lolz" + "-LITERAL", + [ + "[roles]" + ] + ], + [ + "-LITERAL", + [ + "[role]" + ] ] ] ] ] ], [ - "WHERE", + "ON", [ [ - "AND", + "=", [ [ - "PAREN", + "-LITERAL", [ - [ - "=", - [ - [ - "LITERAL", - [ - "foo.a" - ] - ], - [ - "LITERAL", - [ - 1 - ] - ] - ] - ] + "[role].[id]" ] ], [ - "LIKE", + "-LITERAL", + [ + "[me].[role_id]" + ] + ] + ] + ] + ] + ], + [ + "JOIN", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "[roles_permissions]" + ] + ], + [ + "-LITERAL", + [ + "[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", + [ + [ + "-PAREN", + [ + [ + "=", [ [ - "LITERAL", + "-LITERAL", [ - "foo.b" + "[me].[user_id]" ] ], [ - "LITERAL", + "-PLACEHOLDER", [ - "'station'" + "?" ] ] ] @@ -263,311 +496,651 @@ cmp_deeply($sqlat->parse("SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE ' ] ] ] - ] -], '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]"), [ + ], [ + "GROUP BY", [ [ + "-LIST", [ [ + "-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]" - ] - ] - ] - ] - ] - ] - ], + "[screen].[id]" + ] + ], + [ + "-LITERAL", + [ + "[screen].[name]" + ] + ], + [ + "-LITERAL", + [ + "[screen].[section_id]" + ] + ], + [ + "-LITERAL", + [ + "[screen].[xtype]" + ] + ] + ] + ] + ] + ] +], 'real life statement 1 parsed correctly'); + +is_deeply($sqlat->parse("CASE WHEN FOO() > BAR()"), [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "CASE" + ] + ], + [ + "-LITERAL", + [ + "WHEN" + ] + ] + ] + ], + [ + ">", + [ + [ + "FOO", + [ + [ + "-PAREN", + [] + ] + ] + ], + [ + "BAR", + [ + [ + "-PAREN", + [] + ] + ] + ] + ] + ] +]); + + +is_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [ + [ + "SELECT", + [ + [ + "-LIST", + [ + [ + "-LITERAL", + [ + "x" + ] + ], + [ + "-LITERAL", + [ + "y" + ] + ] + ] + ] + ] + ], + [ + "FROM", + [ + [ + "-LITERAL", + [ + "foo" + ] + ] + ] + ], + [ + "WHERE", + [ + [ + "IN", + [ + [ + "-LITERAL", + [ + "x" + ] + ], + [ + "-PAREN", + [ + [ + "-LIST", + [ + [ + "-PLACEHOLDER", [ - "JOIN", - [ - [ - [ - "LITERAL", - [ - "[roles_permissions]" - ] - ], - [ - "LITERAL", - [ - "[role_permissions]" - ] - ] - ] - ] + "?" + ] + ], + [ + "-PLACEHOLDER", + [ + "?" ] ], [ - "ON", + "-PLACEHOLDER", + [ + "?" + ] + ], + [ + "-PLACEHOLDER", + [ + "?" + ] + ] + ] + ] + ] + ] + ] + ] + ] + ] +], '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 LIKE ? 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", + [ + [ + "LIKE", + [ + [ + "-LITERAL", + [ + "foo" + ] + ], + [ + "-PLACEHOLDER", + [ + "?" + ] + ], + ], + ], + ] + ], + [ + "-ASC", + [ + [ + "-LITERAL", + [ + "ickk" + ] + ] + ] + ] + ] + ] + ] + ] +], 'Crazy ORDER BY parsed correctly'); + +is_deeply( $sqlat->parse("META SELECT * * FROM (SELECT *, FROM foobar baz buzz) foo bar WHERE NOT NOT NOT EXISTS (SELECT 'cr,ap') AND foo.a = ? STUFF moar(stuff) and not (foo.b LIKE 'station') and x = y and z in ((1, 2)) and a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [ + [ + "-LITERAL", + [ + "META" + ] + ], + [ + "SELECT", + [ + [ + "-MISC", + [ + [ + "-LITERAL", + [ + "*" + ] + ], + [ + "-LITERAL", + [ + "*" + ] + ] + ] + ] + ] + ], + [ + "FROM", + [ + [ + "-MISC", + [ + [ + "-PAREN", + [ + [ + "SELECT", + [ + [ + "-LIST", [ [ - "=", + "-LITERAL", [ - [ - "LITERAL", - [ - "[role_permissions].[role_id]" - ] - ], - [ - "LITERAL", - [ - "[role].[id]" - ] - ] + "*" ] - ] + ], + [] ] ] - ], + ] + ], + [ + "FROM", [ - "JOIN", [ + "-MISC", [ [ - "LITERAL", + "-LITERAL", + [ + "foobar" + ] + ], + [ + "-LITERAL", [ - "[permissions]" + "baz" ] ], [ - "LITERAL", + "-LITERAL", [ - "[permission]" + "buzz" ] ] ] ] ] - ], + ] + ] + ], + [ + "-LITERAL", + [ + "foo" + ] + ], + [ + "-LITERAL", + [ + "bar" + ] + ] + ] + ] + ] + ], + [ + "WHERE", + [ + [ + "AND", + [ + [ + "NOT", + [] + ], + [ + "NOT", + [] + ], + [ + "NOT EXISTS", + [ [ - "ON", + "-PAREN", [ [ - "=", + "SELECT", [ [ - "LITERAL", - [ - "[permission].[id]" - ] - ], - [ - "LITERAL", + "-LIST", [ - "[role_permissions].[permission_id]" + [ + "-LITERAL", + [ + "'cr" + ] + ], + [ + "-LITERAL", + [ + "ap'" + ] + ] ] ] ] ] ] ] - ], + ] + ], + [ + "-MISC", [ - "JOIN", [ + "=", [ [ - "LITERAL", + "-LITERAL", [ - "[permissionscreens]" + "foo.a" ] ], [ - "LITERAL", + "-PLACEHOLDER", [ - "[permission_screens]" + "?" ] - ] + ], + ], + ], + [ + "-LITERAL", + [ + "STUFF" ] - ] - ] + ], + ], ], [ - "ON", + 'moar', [ [ - "=", + '-PAREN', [ [ - "LITERAL", + '-LITERAL', [ - "[permission_screens].[permission_id]" + 'stuff' ] - ], + ] + ] + ] + ] + ], + [ + "NOT", + [ + [ + "-PAREN", + [ [ - "LITERAL", + "LIKE", [ - "[permission].[id]" + [ + "-LITERAL", + [ + "foo.b" + ] + ], + [ + "-LITERAL", + [ + "'station'" + ] + ] ] ] ] ] ] - ] - ], - [ - "JOIN", + ], [ + "=", [ [ - "LITERAL", + "-LITERAL", [ - "[screens]" + "x" ] ], [ - "LITERAL", + "-LITERAL", [ - "[screen]" + "y" ] ] ] - ] - ] - ], - [ - "ON", - [ + ], [ - "=", + 'IN', [ [ - "LITERAL", + '-LITERAL', [ - "[screen].[id]" - ] + 'z', + ], ], [ - "LITERAL", + '-PAREN', [ - "[permission_screens].[screen_id]" - ] - ] - ] - ] - ] - ] - ], - [ - "WHERE", - [ - [ - "PAREN", + [ + '-PAREN', + [ + [ + '-LIST', + [ + [ + '-LITERAL', + [ + '1' + ] + ], + [ + '-LITERAL', + [ + '2' + ] + ], + ], + ], + ], + ], + ], + ], + ], + ], [ + "=", [ - "=", [ + "-LITERAL", [ - "LITERAL", - [ - "[me].[user_id]" - ] - ], + "a" + ] + ], + [ + "-LITERAL", [ - "PLACEHOLDER", - [ - "?" - ] + "b" ] ] ] @@ -580,114 +1153,91 @@ cmp_deeply($sqlat->parse( "SELECT [screen].[id], [screen].[name], [screen].[sect "GROUP BY", [ [ - "LIST", - [ - [ - "LITERAL", - [ - "[screen].[id]" - ] - ], - [ - "LITERAL", - [ - "[screen].[name]" - ] - ], - [ - "LITERAL", - [ - "[screen].[section_id]" - ] - ], - [ - "LITERAL", - [ - "[screen].[xtype]" - ] - ] - ] - ] - ] - ] -], 'real life statement 1 parsed correctly'); - -cmp_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [ - [ - [ - "SELECT", - [ - [ - "LIST", - [ - [ - "LITERAL", - [ - "x" - ] - ], - [ - "LITERAL", - [ - "y" - ] - ] - ] - ] - ] - ], - [ - "FROM", - [ + "-LIST", [ - "LITERAL", - [ - "foo" - ] + [], + [] ] ] ] ], [ - "WHERE", + "ORDER BY", [ [ - "IN", + "-LIST", [ [ - "LITERAL", - [ - "x" - ] - ], - [ - "PAREN", + "-ASC", [ [ - "LIST", + "-MISC", [ [ - "PLACEHOLDER", + "-LITERAL", [ - "?" + "x" ] ], [ - "PLACEHOLDER", + "-LITERAL", [ - "?" + "x1" ] ], [ - "PLACEHOLDER", + "-LITERAL", [ - "?" + "x2" ] ], [ - "PLACEHOLDER", + "-LITERAL", [ - "?" + "y" + ] + ] + ] + ], + ], + ], + [ + "-DESC", + [ + [ + "-MISC", + [ + [ + "-DESC", + [ + [ + "max", + [ + [ + "-PAREN", + [ + [ + "-LITERAL", + [ + "y" + ] + ] + ] + ] + ], + ] + ] + ], + [ + "-LITERAL", + [ + "x" + ] + ], + [ + "-LITERAL", + [ + "z" ] ] ] @@ -698,11 +1248,14 @@ cmp_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [ ] ] ] -], 'Lists parsed correctly'); +], '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 +1263,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 +1276,7 @@ warnings_are { "FROM", [ [ - "LITERAL", + "-LITERAL", [ "foo" ]