From: Peter Rabbitson Date: Mon, 1 Apr 2013 06:25:46 +0000 (+0200) Subject: Test live mssql server generated SQL X-Git-Tag: v0.08250~27 X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FDBIx-Class.git;a=commitdiff_plain;h=fd1d12a9a9a46f22dd30cbd200c3515ff69919c9 Test live mssql server generated SQL --- diff --git a/t/746mssql.t b/t/746mssql.t index dafee69..b57d218 100644 --- a/t/746mssql.t +++ b/t/746mssql.t @@ -372,10 +372,59 @@ SQL }, ); - my ($sql, @bind) = @${$owners->page(3)->as_query}; - # not testing the SQL as it is quite different between top/rno - is_same_bind ( - \@bind, + is_same_sql_bind ( + $owners->page(3)->as_query, + $dialect eq 'Top' + ? '( + SELECT TOP 2147483647 [me].[id], [me].[name], + [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price] + FROM ( + SELECT TOP 2147483647 [me].[id], [me].[name] + FROM ( + SELECT TOP 3 [me].[id], [me].[name], [ORDER__BY__001] + FROM ( + SELECT TOP 9 [me].[id], [me].[name], name + ? AS [ORDER__BY__001] + FROM [owners] [me] + LEFT JOIN [books] [books] + ON [books].[owner] = [me].[id] + WHERE [books].[id] IS NOT NULL AND [me].[name] != ? + GROUP BY [me].[id], [me].[name] + ORDER BY name + ? ASC, [me].[id] + ) [me] + ORDER BY [ORDER__BY__001] DESC, [me].[id] DESC + ) [me] + ORDER BY [ORDER__BY__001] ASC, [me].[id] + ) [me] + LEFT JOIN [books] [books] + ON [books].[owner] = [me].[id] + WHERE [books].[id] IS NOT NULL AND [me].[name] != ? + ORDER BY name + ? ASC, [me].[id] + )' + : '( + SELECT TOP 2147483647 [me].[id], [me].[name], + [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price] + FROM ( + SELECT TOP 2147483647 [me].[id], [me].[name] + FROM ( + SELECT [me].[id], [me].[name], + ROW_NUMBER() OVER( ORDER BY [ORDER__BY__001] ASC, [me].[id] ) AS [rno__row__index] + FROM ( + SELECT [me].[id], [me].[name], name + ? AS [ORDER__BY__001] + FROM [owners] [me] + LEFT JOIN [books] [books] + ON [books].[owner] = [me].[id] + WHERE [books].[id] IS NOT NULL AND [me].[name] != ? + GROUP BY [me].[id], [me].[name] + ) [me] + ) [me] + WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? + ) [me] + LEFT JOIN [books] [books] + ON [books].[owner] = [me].[id] + WHERE [books].[id] IS NOT NULL AND [me].[name] != ? + ORDER BY name + ? ASC, [me].[id] + )' + , [ [ { dbic_colname => 'test' } => 'xxx' ], @@ -392,7 +441,7 @@ SQL [ { dbic_colname => 'test' } => 'xxx' ], ], - ); + ) if $quoted; is ($owners->page(1)->all, 3, "$test_type: has_many prefetch returns correct number of rows"); is ($owners->page(1)->count, 3, "$test_type: has-many prefetch returns correct count"); @@ -421,10 +470,61 @@ SQL }, ); - ($sql, @bind) = @${$books->page(3)->as_query}; - # not testing the SQL as it is quite different between top/rno - is_same_bind ( - \@bind, + is_same_sql_bind ( + $books->page(3)->as_query, + $dialect eq 'Top' + ? '( + SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], + [owner].[id], [owner].[name] + FROM ( + SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] + FROM ( + SELECT TOP 2 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] + FROM ( + SELECT TOP 6 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] + FROM [books] [me] + JOIN [owners] [owner] + ON [owner].[id] = [me].[owner] + WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? + GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] + HAVING 1 = ? + ORDER BY [me].[owner] DESC, [me].[id] + ) [me] + ORDER BY [me].[owner] ASC, [me].[id] DESC + ) [me] + ORDER BY [me].[owner] DESC, [me].[id] + ) [me] + JOIN [owners] [owner] + ON [owner].[id] = [me].[owner] + WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? + ORDER BY [me].[owner] DESC, [me].[id] + )' + : '( + SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], + [owner].[id], [owner].[name] + FROM ( + SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] + FROM ( + SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], + ROW_NUMBER() OVER( ORDER BY [me].[owner] DESC, [me].[id] ) AS [rno__row__index] + FROM ( + SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] + FROM [books] [me] + JOIN [owners] [owner] + ON [owner].[id] = [me].[owner] + WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? + GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] + HAVING 1 = ? + ) [me] + ) [me] + WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? + ) [me] + JOIN [owners] [owner] + ON [owner].[id] = [me].[owner] + WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? + ORDER BY [me].[owner] DESC, [me].[id] + )' + , [ # inner [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } @@ -436,8 +536,12 @@ SQL [ { dbic_colname => 'test' } => '1' ], - # rno(?) - $dialect ne 'Top' ? ( [ $OFFSET => 5 ], [ $TOTAL => 6 ] ) : (), + # top(?) + $dialect eq 'Top' + ? () + : ( [ $OFFSET => 5 ], [ $TOTAL => 6 ] ) + , + # outer [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } => 'wiggle' ], @@ -446,7 +550,7 @@ SQL [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], ], - ); + ) if $quoted; is ($books->page(1)->all, 2, "$test_type: Prefetched grouped search returns correct number of rows"); is ($books->page(1)->count, 2, "$test_type: Prefetched grouped search returns correct count");