Commit | Line | Data |
73b15388 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | use lib qw(t/lib); |
6 | use DBICTest; |
7 | use DBIC::SqlMakerTest; |
8 | |
a2e274f5 |
9 | my $schema = DBICTest->init_schema( |
10 | no_deploy => 1, |
11 | no_populate => 1, |
12 | storage_type => '::DBI::MSSQL', |
13 | ); |
73b15388 |
14 | |
15 | my $rs = $schema->resultset ('BooksInLibrary')->search ({}, { prefetch => 'owner', rows => 1, offset => 3 }); |
16 | |
17 | sub default_test_order { |
18 | my $order_by = shift; |
19 | is_same_sql_bind( |
20 | $rs->search ({}, {order_by => $order_by})->as_query, |
21 | "(SELECT me__id, me__source, me__owner, me__title, me__price, owner__id, owner__name FROM |
22 | ( |
23 | SELECT Q1.*, ROW_NUMBER() OVER( ) AS ROW_NUM FROM ( |
24 | SELECT me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name |
25 | FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( source = ? ) |
26 | ) Q1 |
27 | ) Q2 |
28 | WHERE ROW_NUM BETWEEN 4 and 4 |
29 | )", |
30 | [ [ source => 'Library' ] ], |
31 | ); |
32 | } |
33 | |
34 | sub test_order { |
35 | my $args = shift; |
36 | |
37 | my $req_order = $args->{order_req} |
38 | ? "ORDER BY $args->{order_req}" |
39 | : '' |
40 | ; |
41 | |
42 | is_same_sql_bind( |
43 | $rs->search ({}, {order_by => $args->{order_by}})->as_query, |
44 | "(SELECT me__id, me__source, me__owner, me__title, me__price, owner__id, owner__name FROM |
45 | ( |
46 | SELECT Q1.*, ROW_NUMBER() OVER( ) AS ROW_NUM FROM ( |
47 | SELECT me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name |
48 | FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( source = ? ) |
49 | $req_order |
50 | ) Q1 |
51 | ) Q2 |
52 | WHERE ROW_NUM BETWEEN 4 and 4 |
53 | )", |
54 | [ [ source => 'Library' ] ], |
55 | ); |
56 | } |
57 | |
58 | my @tests = ( |
59 | { |
60 | order_by => \'foo DESC', |
61 | order_req => 'foo DESC', |
62 | order_inner => 'foo DESC', |
63 | order_outer => 'foo ASC' |
64 | }, |
65 | { |
66 | order_by => { -asc => 'foo' }, |
67 | order_req => 'foo ASC', |
68 | order_inner => 'foo ASC', |
69 | order_outer => 'foo DESC', |
70 | }, |
71 | { |
72 | order_by => 'foo', |
73 | order_req => 'foo', |
74 | order_inner => 'foo ASC', |
75 | order_outer => 'foo DESC', |
76 | }, |
77 | { |
78 | order_by => [ qw{ foo bar} ], |
79 | order_req => 'foo, bar', |
80 | order_inner => 'foo ASC,bar ASC', |
81 | order_outer => 'foo DESC, bar DESC', |
82 | }, |
83 | { |
84 | order_by => { -desc => 'foo' }, |
85 | order_req => 'foo DESC', |
86 | order_inner => 'foo DESC', |
87 | order_outer => 'foo ASC', |
88 | }, |
89 | { |
90 | order_by => ['foo', { -desc => 'bar' } ], |
91 | order_req => 'foo, bar DESC', |
92 | order_inner => 'foo ASC, bar DESC', |
93 | order_outer => 'foo DESC, bar ASC', |
94 | }, |
95 | { |
96 | order_by => { -asc => [qw{ foo bar }] }, |
97 | order_req => 'foo ASC, bar ASC', |
98 | order_inner => 'foo ASC, bar ASC', |
99 | order_outer => 'foo DESC, bar DESC', |
100 | }, |
101 | { |
102 | order_by => [ |
103 | { -asc => 'foo' }, |
104 | { -desc => [qw{bar}] }, |
105 | { -asc => [qw{hello sensors}]}, |
106 | ], |
107 | order_req => 'foo ASC, bar DESC, hello ASC, sensors ASC', |
108 | order_inner => 'foo ASC, bar DESC, hello ASC, sensors ASC', |
109 | order_outer => 'foo DESC, bar ASC, hello DESC, sensors DESC', |
110 | }, |
111 | ); |
112 | |
113 | my @default_tests = ( undef, '', {}, [] ); |
114 | |
115 | plan (tests => scalar @tests + scalar @default_tests + 1); |
116 | |
117 | test_order ($_) for @tests; |
118 | default_test_order ($_) for @default_tests; |
119 | |
120 | |
121 | is_same_sql_bind ( |
122 | $rs->search ({}, { group_by => 'title', order_by => 'title' })->as_query, |
123 | '(SELECT |
124 | me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name FROM |
125 | ( SELECT |
126 | id, source, owner, title, price FROM |
127 | ( SELECT |
128 | TOP 1 id, source, owner, title, price FROM |
129 | ( SELECT |
130 | TOP 4 me.id, me.source, me.owner, me.title, me.price FROM |
131 | books me JOIN |
132 | owners owner ON owner.id = me.owner |
133 | WHERE ( source = ? ) |
134 | GROUP BY title |
135 | ORDER BY title ASC |
136 | ) me |
137 | ORDER BY title DESC |
138 | ) me |
139 | ORDER BY title |
140 | ) me JOIN |
141 | owners owner ON owner.id = me.owner WHERE |
142 | ( source = ? ) |
143 | ORDER BY title)' , |
144 | [ [ source => 'Library' ], [ source => 'Library' ] ], |
145 | ); |