Commit | Line | Data |
ac0c0825 |
1 | use strict; |
2 | use warnings; |
3 | use Test::More; |
4 | use lib qw(t/lib); |
a5a7bb73 |
5 | use DBICTest ':DiffSQL'; |
ac0c0825 |
6 | use DBIx::Class::SQLMaker::LimitDialects; |
7 | my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype; |
8 | my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype; |
9 | |
10 | my $schema = DBICTest->init_schema ( |
11 | storage_type => 'DBIx::Class::Storage::DBI::MSSQL', |
12 | no_deploy => 1, |
13 | quote_names => 1 |
14 | ); |
15 | # prime caches |
16 | $schema->storage->sql_maker; |
17 | |
18 | # more involved limit dialect torture testcase migrated from the |
19 | # live mssql tests |
20 | my $tests = { |
21 | pref_hm_and_page_and_group_rs => { |
22 | |
23 | rs => scalar $schema->resultset ('Owners')->search ( |
24 | { |
25 | 'books.id' => { '!=', undef }, |
26 | 'me.name' => { '!=', 'somebogusstring' }, |
27 | }, |
28 | { |
29 | prefetch => 'books', |
30 | order_by => [ { -asc => \['name + ?', [ test => 'xxx' ]] }, 'me.id' ], # test bindvar propagation |
31 | group_by => [ map { "me.$_" } $schema->source('Owners')->columns ], # the literal order_by requires an explicit group_by |
32 | rows => 3, |
33 | unsafe_subselect_ok => 1, |
34 | }, |
35 | )->page(3), |
36 | |
37 | result => { |
38 | Top => [ |
39 | '( |
40 | SELECT TOP 2147483647 [me].[id], [me].[name], |
41 | [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price] |
42 | FROM ( |
43 | SELECT TOP 2147483647 [me].[id], [me].[name] |
44 | FROM ( |
45 | SELECT TOP 3 [me].[id], [me].[name], [ORDER__BY__001] |
46 | FROM ( |
47 | SELECT TOP 9 [me].[id], [me].[name], name + ? AS [ORDER__BY__001] |
48 | FROM [owners] [me] |
49 | LEFT JOIN [books] [books] |
50 | ON [books].[owner] = [me].[id] |
51 | WHERE [books].[id] IS NOT NULL AND [me].[name] != ? |
52 | GROUP BY [me].[id], [me].[name] |
53 | ORDER BY name + ? ASC, [me].[id] |
54 | ) [me] |
55 | ORDER BY [ORDER__BY__001] DESC, [me].[id] DESC |
56 | ) [me] |
57 | ORDER BY [ORDER__BY__001] ASC, [me].[id] |
58 | ) [me] |
59 | LEFT JOIN [books] [books] |
60 | ON [books].[owner] = [me].[id] |
61 | WHERE [books].[id] IS NOT NULL AND [me].[name] != ? |
62 | ORDER BY name + ? ASC, [me].[id] |
63 | )', |
64 | [ |
65 | [ { dbic_colname => 'test' } |
66 | => 'xxx' ], |
67 | |
68 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' } |
69 | => 'somebogusstring' ], |
70 | |
71 | [ { dbic_colname => 'test' } => 'xxx' ], # the extra re-order bind |
72 | |
73 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' } |
74 | => 'somebogusstring' ], |
75 | |
76 | [ { dbic_colname => 'test' } |
77 | => 'xxx' ], |
78 | ], |
79 | ], |
80 | |
81 | RowNumberOver => [ |
82 | '( |
83 | SELECT TOP 2147483647 [me].[id], [me].[name], |
84 | [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price] |
85 | FROM ( |
86 | SELECT TOP 2147483647 [me].[id], [me].[name] |
87 | FROM ( |
88 | SELECT [me].[id], [me].[name], |
89 | ROW_NUMBER() OVER( ORDER BY [ORDER__BY__001] ASC, [me].[id] ) AS [rno__row__index] |
90 | FROM ( |
91 | SELECT [me].[id], [me].[name], name + ? AS [ORDER__BY__001] |
92 | FROM [owners] [me] |
93 | LEFT JOIN [books] [books] |
94 | ON [books].[owner] = [me].[id] |
95 | WHERE [books].[id] IS NOT NULL AND [me].[name] != ? |
96 | GROUP BY [me].[id], [me].[name] |
97 | ) [me] |
98 | ) [me] |
99 | WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
100 | ) [me] |
101 | LEFT JOIN [books] [books] |
102 | ON [books].[owner] = [me].[id] |
103 | WHERE [books].[id] IS NOT NULL AND [me].[name] != ? |
104 | ORDER BY name + ? ASC, [me].[id] |
105 | )', |
106 | [ |
107 | [ { dbic_colname => 'test' } |
108 | => 'xxx' ], |
109 | |
110 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' } |
111 | => 'somebogusstring' ], |
112 | |
113 | [ $OFFSET => 7 ], # parameterised RNO |
114 | |
115 | [ $TOTAL => 9 ], |
116 | |
117 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' } |
118 | => 'somebogusstring' ], |
119 | |
120 | [ { dbic_colname => 'test' } |
121 | => 'xxx' ], |
122 | ], |
123 | ], |
124 | } |
125 | }, |
126 | |
127 | pref_bt_and_page_and_group_rs => { |
128 | |
129 | rs => scalar $schema->resultset ('BooksInLibrary')->search ( |
130 | { |
131 | 'owner.name' => [qw/wiggle woggle/], |
132 | }, |
133 | { |
134 | distinct => 1, |
135 | having => \['1 = ?', [ test => 1 ] ], #test having propagation |
136 | prefetch => 'owner', |
137 | rows => 2, # 3 results total |
138 | order_by => [{ -desc => 'me.owner' }, 'me.id'], |
139 | unsafe_subselect_ok => 1, |
140 | }, |
141 | )->page(3), |
142 | |
143 | result => { |
144 | Top => [ |
145 | '( |
146 | SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], |
147 | [owner].[id], [owner].[name] |
148 | FROM ( |
149 | SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
150 | FROM ( |
151 | SELECT TOP 2 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
152 | FROM ( |
153 | SELECT TOP 6 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
154 | FROM [books] [me] |
155 | JOIN [owners] [owner] |
156 | ON [owner].[id] = [me].[owner] |
157 | WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? |
158 | GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
159 | HAVING 1 = ? |
160 | ORDER BY [me].[owner] DESC, [me].[id] |
161 | ) [me] |
162 | ORDER BY [me].[owner] ASC, [me].[id] DESC |
163 | ) [me] |
164 | ORDER BY [me].[owner] DESC, [me].[id] |
165 | ) [me] |
166 | JOIN [owners] [owner] |
167 | ON [owner].[id] = [me].[owner] |
168 | WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? |
169 | ORDER BY [me].[owner] DESC, [me].[id] |
170 | )', |
171 | [ |
172 | # inner |
173 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
174 | => 'wiggle' ], |
175 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
176 | => 'woggle' ], |
177 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
178 | => 'Library' ], |
179 | [ { dbic_colname => 'test' } |
180 | => '1' ], |
181 | |
182 | # outer |
183 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
184 | => 'wiggle' ], |
185 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
186 | => 'woggle' ], |
187 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
188 | => 'Library' ], |
189 | ], |
190 | ], |
191 | RowNumberOver => [ |
192 | '( |
193 | SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], |
194 | [owner].[id], [owner].[name] |
195 | FROM ( |
196 | SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
197 | FROM ( |
198 | SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], |
199 | ROW_NUMBER() OVER( ORDER BY [me].[owner] DESC, [me].[id] ) AS [rno__row__index] |
200 | FROM ( |
201 | SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
202 | FROM [books] [me] |
203 | JOIN [owners] [owner] |
204 | ON [owner].[id] = [me].[owner] |
205 | WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? |
206 | GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] |
207 | HAVING 1 = ? |
208 | ) [me] |
209 | ) [me] |
210 | WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
211 | ) [me] |
212 | JOIN [owners] [owner] |
213 | ON [owner].[id] = [me].[owner] |
214 | WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ? |
215 | ORDER BY [me].[owner] DESC, [me].[id] |
216 | )', |
217 | [ |
218 | # inner |
219 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
220 | => 'wiggle' ], |
221 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
222 | => 'woggle' ], |
223 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
224 | => 'Library' ], |
225 | [ { dbic_colname => 'test' } |
226 | => '1' ], |
227 | |
228 | [ $OFFSET => 5 ], |
229 | [ $TOTAL => 6 ], |
230 | |
231 | # outer |
232 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
233 | => 'wiggle' ], |
234 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' } |
235 | => 'woggle' ], |
236 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
237 | => 'Library' ], |
238 | ], |
239 | ], |
240 | }, |
241 | }, |
242 | }; |
243 | |
244 | for my $tname (keys %$tests) { |
245 | for my $limtype (keys %{$tests->{$tname}{result}} ) { |
246 | |
247 | delete $schema->storage->_sql_maker->{_cached_syntax}; |
248 | $schema->storage->_sql_maker->limit_dialect ($limtype); |
249 | |
250 | is_same_sql_bind( |
251 | $tests->{$tname}{rs}->as_query, |
252 | @{ $tests->{$tname}{result}{$limtype} }, |
253 | "Correct SQL for $limtype on $tname", |
254 | ); |
255 | } |
256 | } |
257 | |
258 | done_testing; |