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