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