X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F11unparse.t;h=e61416bcc79b803a43db0e98e71ae0f0834d8ff3;hb=958f1ce453b1065f3c152a791611cc688a354d6e;hp=2b926238c130ea51424924115d0312f1d0f25a73;hpb=1bb3956e85e58f2cbbd2d54545b99dd071ef9441;p=dbsrgits%2FSQL-Abstract.git diff --git a/t/11unparse.t b/t/11unparse.t index 2b92623..e61416b 100644 --- a/t/11unparse.t +++ b/t/11unparse.t @@ -2,255 +2,701 @@ use strict; use warnings; use Test::More; +use Test::Deep; use SQL::Abstract::Tree; -subtest no_formatting => sub { - my $sqlat = SQL::Abstract::Tree->new; - - { - my $sql = "SELECT a, b, c FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"; - my $expected_sql = - "SELECT a, b, c FROM foo WHERE foo.a = 1 AND foo.b LIKE 'station' "; - is($sqlat->format($sql), $expected_sql, - 'simple statement formatted correctly' - ); - } - - { - my $sql = "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"; - my $expected_sql = - "SELECT * FROM (SELECT * FROM foobar ) WHERE foo.a = 1 AND foo.b LIKE 'station' "; - is($sqlat->format($sql), $expected_sql, - 'subquery statement formatted correctly' - ); - } - - { - my $sql = "SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"; - my $expected_sql = - "SELECT * FROM lolz WHERE (foo.a = 1) AND foo.b LIKE 'station' "; - - is($sqlat->format($sql), $expected_sql, - 'simple statement with parens in where formatted correctly' - ); - } - - { - my $sql = "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]"; - my $expected_sql = - "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] "; - - is($sqlat->format($sql), $expected_sql, - 'real life statement 1 formatted correctly' - ); - } - done_testing; -}; - -subtest console_monochrome => sub { - my $sqlat = SQL::Abstract::Tree->new({ - profile => 'console_monochrome', - }); - - { - my $sql = "SELECT a, b, c FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"; - my $expected_sql = - qq{SELECT a, b, c \n} . - qq{ FROM foo \n} . - qq{ WHERE foo.a = 1 AND foo.b LIKE 'station' }; - is($sqlat->format($sql), $expected_sql, - 'simple statement formatted correctly' - ); - } - - { - my $sql = "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"; - my $expected_sql = - qq{SELECT * \n} . - qq{ FROM (\n} . - qq{ SELECT * \n} . - qq{ FROM foobar \n} . - qq{ ) \n} . - qq{ WHERE foo.a = 1 AND foo.b LIKE 'station' }; - - is($sqlat->format($sql), $expected_sql, - 'subquery statement formatted correctly' - ); - } - - { - my $sql = "SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"; - my $expected_sql = - qq{SELECT * \n} . - qq{ FROM lolz \n} . - qq{ WHERE (foo.a = 1) AND foo.b LIKE 'station' }; - - is($sqlat->format($sql), $expected_sql, - 'simple statement with parens in where formatted correctly' - ); - } - - { - my $sql = "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]"; - my $expected_sql = - "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] \n". - " FROM [users_roles] [me] \n" . - " JOIN [roles] [role] \n" . - " ON [role].[id] = [me].[role_id] \n" . - " JOIN [roles_permissions] [role_permissions] \n" . - " ON [role_permissions].[role_id] = [role].[id] \n" . - " JOIN [permissions] [permission] \n" . - " ON [permission].[id] = [role_permissions].[permission_id] \n" . - " JOIN [permissionscreens] [permission_screens] \n" . - " ON [permission_screens].[permission_id] = [permission].[id] \n" . - " JOIN [screens] [screen] \n" . - " ON [screen].[id] = [permission_screens].[screen_id] \n" . - " WHERE ([me].[user_id] = ?) \n" . - " GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] "; - - my $gotten = $sqlat->format($sql); - is($gotten, $expected_sql, 'real life statement 1 formatted correctly'); - } - done_testing; -}; - -subtest html => sub { - my $sqlat = SQL::Abstract::Tree->new({ - profile => 'html', - }); - - { - my $sql = "SELECT a, b, c FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"; - my $expected_sql = - qq{SELECT a, b, c
\n} . - qq{  FROM foo
\n} . - qq{  WHERE foo.a = 1 AND foo.b LIKE 'station' }; - is($sqlat->format($sql), $expected_sql, - 'simple statement formatted correctly' - ); - } - - { - my $sql = "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"; - my $expected_sql = - qq{SELECT *
\n} . - qq{  FROM (
\n} . - qq{    SELECT *
\n} . - qq{      FROM foobar
\n} . - qq{  )
\n} . - qq{  WHERE foo.a = 1 AND foo.b LIKE 'station' }; - - is($sqlat->format($sql), $expected_sql, - 'subquery statement formatted correctly' - ); - } - - { - my $sql = "SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"; - my $expected_sql = - qq{SELECT *
\n} . - qq{  FROM lolz
\n} . - qq{  WHERE (foo.a = 1) AND foo.b LIKE 'station' }; - - is($sqlat->format($sql), $expected_sql, - 'simple statement with parens in where formatted correctly' - ); - } - - { - my $sql = "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]"; - my $expected_sql = - qq{SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]
\n}. - qq{  FROM [users_roles] [me]
\n}. - qq{  JOIN [roles] [role]
\n}. - qq{    ON [role].[id] = [me].[role_id]
\n}. - qq{  JOIN [roles_permissions] [role_permissions]
\n}. - qq{    ON [role_permissions].[role_id] = [role].[id]
\n}. - qq{  JOIN [permissions] [permission]
\n}. - qq{    ON [permission].[id] = [role_permissions].[permission_id]
\n}. - qq{  JOIN [permissionscreens] [permission_screens]
\n}. - qq{    ON [permission_screens].[permission_id] = [permission].[id]
\n}. - qq{  JOIN [screens] [screen]
\n}. - qq{    ON [screen].[id] = [permission_screens].[screen_id]
\n}. - qq{  WHERE ([me].[user_id] = ?)
\n}. - qq{  GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] }; - - my $gotten = $sqlat->format($sql); - is($gotten, $expected_sql, 'real life statement 1 formatted correctly'); - } - done_testing; -}; - -subtest configuration => sub { - my $sqlat = SQL::Abstract::Tree->new({ - profile => 'console_monochrome', - indent_string => "\t", - indent_amount => 1, - newline => "\r\n", - }); - - { - my $sql = "SELECT a, b, c FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"; - my $expected_sql = - qq{SELECT a, b, c \r\n} . - qq{\tFROM foo \r\n} . - qq{\tWHERE foo.a = 1 AND foo.b LIKE 'station' }; - is($sqlat->format($sql), $expected_sql, - 'simple statement formatted correctly' - ); - } - - { - my $sql = "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"; - my $expected_sql = - qq{SELECT * \r\n} . - qq{\tFROM (\r\n} . - qq{\t\tSELECT * \r\n} . - qq{\t\t\tFROM foobar \r\n} . - qq{\t) \r\n} . - qq{\tWHERE foo.a = 1 AND foo.b LIKE 'station' }; - - is($sqlat->format($sql), $expected_sql, - 'subquery statement formatted correctly' - ); - } - - { - my $sql = "SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"; - my $expected_sql = - qq{SELECT * \r\n} . - qq{\tFROM lolz \r\n} . - qq{\tWHERE (foo.a = 1) AND foo.b LIKE 'station' }; - - is($sqlat->format($sql), $expected_sql, - 'simple statement with parens in where formatted correctly' - ); - } - - { - my $sql = "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]"; - my $expected_sql = - "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] \r\n". - "\tFROM [users_roles] [me] \r\n" . - "\tJOIN [roles] [role] \r\n" . - "\t\tON [role].[id] = [me].[role_id] \r\n" . - "\tJOIN [roles_permissions] [role_permissions] \r\n" . - "\t\tON [role_permissions].[role_id] = [role].[id] \r\n" . - "\tJOIN [permissions] [permission] \r\n" . - "\t\tON [permission].[id] = [role_permissions].[permission_id] \r\n" . - "\tJOIN [permissionscreens] [permission_screens] \r\n" . - "\t\tON [permission_screens].[permission_id] = [permission].[id] \r\n" . - "\tJOIN [screens] [screen] \r\n" . - "\t\tON [screen].[id] = [permission_screens].[screen_id] \r\n" . - "\tWHERE ([me].[user_id] = ?) \r\n" . - "\tGROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] "; - - my $gotten = $sqlat->format($sql); - is($gotten, $expected_sql, 'real life statement 1 formatted correctly'); - } - done_testing; -}; +my $sqlat = SQL::Abstract::Tree->new; + +cmp_deeply($sqlat->parse("SELECT a, b, c FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [ + [ + [ + "SELECT", + [ + [ + "LIST", + [ + [ + "LITERAL", + [ + "a" + ] + ], + [ + "LITERAL", + [ + "b" + ] + ], + [ + "LITERAL", + [ + "c" + ] + ] + ] + ] + ] + ], + [ + "FROM", + [ + [ + "LITERAL", + [ + "foo" + ] + ] + ] + ] + ], + [ + "WHERE", + [ + [ + "AND", + [ + [ + "=", + [ + [ + "LITERAL", + [ + "foo.a" + ] + ], + [ + "LITERAL", + [ + 1 + ] + ] + ] + ], + [ + "LIKE", + [ + [ + "LITERAL", + [ + "foo.b" + ] + ], + [ + "LITERAL", + [ + "'station'" + ] + ] + ] + ] + ] + ] + ] + ] +], 'simple statement parsed correctly'); + +cmp_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"), [ + [ + [ + "SELECT", + [ + [ + "LITERAL", + [ + "*" + ] + ] + ] + ], + [ + "FROM", + [ + [ + "PAREN", + [ + [ + [ + "SELECT", + [ + [ + "LITERAL", + [ + "*" + ] + ] + ] + ], + [ + "FROM", + [ + [ + "LITERAL", + [ + "foobar" + ] + ] + ] + ] + ] + ] + ] + ] + ] + ], + [ + "WHERE", + [ + [ + "AND", + [ + [ + "=", + [ + [ + "LITERAL", + [ + "foo.a" + ] + ], + [ + "LITERAL", + [ + 1 + ] + ] + ] + ], + [ + "LIKE", + [ + [ + "LITERAL", + [ + "foo.b" + ] + ], + [ + "LITERAL", + [ + "'station'" + ] + ] + ] + ] + ] + ] + ] + ] +], 'subquery statement parsed correctly'); + +cmp_deeply($sqlat->parse("SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"), [ + [ + [ + "SELECT", + [ + [ + "LITERAL", + [ + "*" + ] + ] + ] + ], + [ + "FROM", + [ + [ + "LITERAL", + [ + "lolz" + ] + ] + ] + ] + ], + [ + "WHERE", + [ + [ + "AND", + [ + [ + "PAREN", + [ + [ + "=", + [ + [ + "LITERAL", + [ + "foo.a" + ] + ], + [ + "LITERAL", + [ + 1 + ] + ] + ] + ] + ] + ], + [ + "LIKE", + [ + [ + "LITERAL", + [ + "foo.b" + ] + ], + [ + "LITERAL", + [ + "'station'" + ] + ] + ] + ] + ] + ] + ] + ] +], '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]"), [ + [ + [ + [ + [ + [ + [ + [ + [ + [ + [ + [ + [ + [ + "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]" + ] + ] + ] + ] + ] + ], + [ + "ON", + [ + [ + "=", + [ + [ + "LITERAL", + [ + "[permission_screens].[permission_id]" + ] + ], + [ + "LITERAL", + [ + "[permission].[id]" + ] + ] + ] + ] + ] + ] + ], + [ + "JOIN", + [ + [ + [ + "LITERAL", + [ + "[screens]" + ] + ], + [ + "LITERAL", + [ + "[screen]" + ] + ] + ] + ] + ] + ], + [ + "ON", + [ + [ + "=", + [ + [ + "LITERAL", + [ + "[screen].[id]" + ] + ], + [ + "LITERAL", + [ + "[permission_screens].[screen_id]" + ] + ] + ] + ] + ] + ] + ], + [ + "WHERE", + [ + [ + "PAREN", + [ + [ + "=", + [ + [ + "LITERAL", + [ + "[me].[user_id]" + ] + ], + [ + "PLACEHOLDER", + [ + "?" + ] + ] + ] + ] + ] + ] + ] + ] + ], + [ + "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", + [ + [ + "LITERAL", + [ + "foo" + ] + ] + ] + ] + ], + [ + "WHERE", + [ + [ + "IN", + [ + [ + "LITERAL", + [ + "x" + ] + ], + [ + "PAREN", + [ + [ + "LIST", + [ + [ + "PLACEHOLDER", + [ + "?" + ] + ], + [ + "PLACEHOLDER", + [ + "?" + ] + ], + [ + "PLACEHOLDER", + [ + "?" + ] + ], + [ + "PLACEHOLDER", + [ + "?" + ] + ] + ] + ] + ] + ] + ] + ] + ] + ] +], 'Lists parsed correctly'); done_testing; -# stuff we want: -# Max Width