Commit | Line | Data |
c0329273 |
1 | BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) } |
2 | |
327368bc |
3 | use strict; |
4 | use warnings; |
5 | |
6 | use Test::More; |
7 | |
c0329273 |
8 | |
a5a7bb73 |
9 | use DBICTest ':DiffSQL'; |
fcb7fcbb |
10 | use DBIx::Class::SQLMaker::LimitDialects; |
11 | |
cccd1876 |
12 | my ($TOTAL, $OFFSET, $ROWS) = ( |
fcb7fcbb |
13 | DBIx::Class::SQLMaker::LimitDialects->__total_bindtype, |
14 | DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype, |
cccd1876 |
15 | DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype, |
fcb7fcbb |
16 | ); |
327368bc |
17 | |
18 | my $s = DBICTest->init_schema (no_deploy => 1, ); |
19 | $s->storage->sql_maker->limit_dialect ('RowNum'); |
20 | |
8b31f62e |
21 | my $rs = $s->resultset ('CD')->search({ id => 1 }); |
22 | |
95e41036 |
23 | # important for a test below, never traversed |
24 | $rs->result_source->add_relationship( |
25 | ends_with_me => 'DBICTest::Schema::Artist', sub {} |
26 | ); |
27 | |
28 | |
8b31f62e |
29 | my $where_bind = [ { dbic_colname => 'id' }, 1 ]; |
327368bc |
30 | |
6a6394f1 |
31 | for my $test_set ( |
32 | { |
33 | name => 'Rownum subsel aliasing works correctly', |
34 | rs => $rs->search_rs(undef, { |
35 | rows => 1, |
36 | offset => 3, |
37 | columns => [ |
38 | { id => 'foo.id' }, |
95e41036 |
39 | { 'artist.id' => 'bar.id' }, |
6a6394f1 |
40 | { bleh => \'TO_CHAR (foo.womble, "blah")' }, |
41 | ] |
42 | }), |
43 | sql => '( |
95e41036 |
44 | SELECT id, artist__id, bleh |
327368bc |
45 | FROM ( |
b775fa8e |
46 | SELECT id, artist__id, bleh, ROWNUM AS rownum__index |
6a6394f1 |
47 | FROM ( |
95e41036 |
48 | SELECT foo.id AS id, bar.id AS artist__id, TO_CHAR (foo.womble, "blah") AS bleh |
8b31f62e |
49 | FROM cd me |
50 | WHERE id = ? |
6a6394f1 |
51 | ) me |
52 | ) me WHERE rownum__index BETWEEN ? AND ? |
53 | )', |
54 | binds => [ |
8b31f62e |
55 | $where_bind, |
6a6394f1 |
56 | [ $OFFSET => 4 ], |
57 | [ $TOTAL => 4 ], |
58 | ], |
59 | }, { |
60 | name => 'Rownum subsel aliasing works correctly with unique order_by', |
61 | rs => $rs->search_rs(undef, { |
62 | rows => 1, |
63 | offset => 3, |
64 | columns => [ |
65 | { id => 'foo.id' }, |
95e41036 |
66 | { 'artist.id' => 'bar.id' }, |
6a6394f1 |
67 | { bleh => \'TO_CHAR (foo.womble, "blah")' }, |
68 | ], |
69 | order_by => [qw( artist title )], |
70 | }), |
71 | sql => '( |
95e41036 |
72 | SELECT id, artist__id, bleh |
6a6394f1 |
73 | FROM ( |
b775fa8e |
74 | SELECT id, artist__id, bleh, ROWNUM AS rownum__index |
6a6394f1 |
75 | FROM ( |
95e41036 |
76 | SELECT foo.id AS id, bar.id AS artist__id, TO_CHAR(foo.womble, "blah") AS bleh |
8b31f62e |
77 | FROM cd me |
78 | WHERE id = ? |
6a6394f1 |
79 | ORDER BY artist, title |
80 | ) me |
fcb7fcbb |
81 | WHERE ROWNUM <= ? |
327368bc |
82 | ) me |
6a6394f1 |
83 | WHERE rownum__index >= ? |
84 | )', |
85 | binds => [ |
8b31f62e |
86 | $where_bind, |
6a6394f1 |
87 | [ $TOTAL => 4 ], |
88 | [ $OFFSET => 4 ], |
89 | ], |
7cec4356 |
90 | }, |
91 | { |
92 | name => 'Rownum subsel aliasing works correctly with non-unique order_by', |
93 | rs => $rs->search_rs(undef, { |
94 | rows => 1, |
95 | offset => 3, |
96 | columns => [ |
97 | { id => 'foo.id' }, |
95e41036 |
98 | { 'artist.id' => 'bar.id' }, |
7cec4356 |
99 | { bleh => \'TO_CHAR (foo.womble, "blah")' }, |
100 | ], |
101 | order_by => 'artist', |
102 | }), |
103 | sql => '( |
95e41036 |
104 | SELECT id, artist__id, bleh |
7cec4356 |
105 | FROM ( |
b775fa8e |
106 | SELECT id, artist__id, bleh, ROWNUM AS rownum__index |
7cec4356 |
107 | FROM ( |
95e41036 |
108 | SELECT foo.id AS id, bar.id AS artist__id, TO_CHAR(foo.womble, "blah") AS bleh |
7cec4356 |
109 | FROM cd me |
110 | WHERE id = ? |
111 | ORDER BY artist |
112 | ) me |
113 | ) me |
114 | WHERE rownum__index BETWEEN ? and ? |
115 | )', |
116 | binds => [ |
117 | $where_bind, |
118 | [ $OFFSET => 4 ], |
119 | [ $TOTAL => 4 ], |
120 | ], |
6a6394f1 |
121 | }, { |
122 | name => 'Rownum subsel aliasing #2 works correctly', |
123 | rs => $rs->search_rs(undef, { |
124 | rows => 2, |
125 | offset => 3, |
126 | columns => [ |
127 | { id => 'foo.id' }, |
128 | { 'ends_with_me.id' => 'ends_with_me.id' }, |
129 | ] |
130 | }), |
131 | sql => '( |
132 | SELECT id, ends_with_me__id |
f8583f8f |
133 | FROM ( |
b775fa8e |
134 | SELECT id, ends_with_me__id, ROWNUM AS rownum__index |
6a6394f1 |
135 | FROM ( |
136 | SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id |
8b31f62e |
137 | FROM cd me |
138 | WHERE id = ? |
6a6394f1 |
139 | ) me |
140 | ) me WHERE rownum__index BETWEEN ? AND ? |
141 | )', |
142 | binds => [ |
8b31f62e |
143 | $where_bind, |
6a6394f1 |
144 | [ $OFFSET => 4 ], |
145 | [ $TOTAL => 5 ], |
146 | ], |
147 | }, { |
148 | name => 'Rownum subsel aliasing #2 works correctly with unique order_by', |
149 | rs => $rs->search_rs(undef, { |
150 | rows => 2, |
151 | offset => 3, |
152 | columns => [ |
153 | { id => 'foo.id' }, |
154 | { 'ends_with_me.id' => 'ends_with_me.id' }, |
155 | ], |
4e9fc3f3 |
156 | order_by => [qw( year artist title )], |
6a6394f1 |
157 | }), |
158 | sql => '( |
159 | SELECT id, ends_with_me__id |
160 | FROM ( |
b775fa8e |
161 | SELECT id, ends_with_me__id, ROWNUM AS rownum__index |
6a6394f1 |
162 | FROM ( |
163 | SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id |
8b31f62e |
164 | FROM cd me |
165 | WHERE id = ? |
4e9fc3f3 |
166 | ORDER BY year, artist, title |
6a6394f1 |
167 | ) me |
fcb7fcbb |
168 | WHERE ROWNUM <= ? |
f8583f8f |
169 | ) me |
6a6394f1 |
170 | WHERE rownum__index >= ? |
171 | )', |
172 | binds => [ |
8b31f62e |
173 | $where_bind, |
6a6394f1 |
174 | [ $TOTAL => 5 ], |
175 | [ $OFFSET => 4 ], |
176 | ], |
177 | } |
178 | ) { |
179 | is_same_sql_bind( |
180 | $test_set->{rs}->as_query, |
181 | $test_set->{sql}, |
182 | $test_set->{binds}, |
183 | $test_set->{name}); |
184 | } |
f8583f8f |
185 | |
d7632687 |
186 | { |
69d3c270 |
187 | my $subq = $s->resultset('Owners')->search({ |
188 | 'count.id' => { -ident => 'owner.id' }, |
189 | }, { alias => 'owner' })->count_rs; |
190 | |
191 | my $rs_selectas_rel = $s->resultset('BooksInLibrary')->search ({}, { |
192 | columns => [ |
193 | { owner_name => 'owner.name' }, |
194 | { owner_books => $subq->as_query }, |
195 | ], |
196 | join => 'owner', |
197 | rows => 2, |
198 | offset => 3, |
199 | }); |
200 | |
201 | is_same_sql_bind( |
202 | $rs_selectas_rel->as_query, |
203 | '( |
204 | SELECT owner_name, owner_books |
205 | FROM ( |
b775fa8e |
206 | SELECT owner_name, owner_books, ROWNUM AS rownum__index |
69d3c270 |
207 | FROM ( |
208 | SELECT owner.name AS owner_name, |
209 | ( SELECT COUNT( * ) FROM owners owner WHERE (count.id = owner.id)) AS owner_books |
210 | FROM books me |
211 | JOIN owners owner ON owner.id = me.owner |
212 | WHERE ( source = ? ) |
213 | ) me |
69d3c270 |
214 | ) me |
6a6394f1 |
215 | WHERE rownum__index BETWEEN ? AND ? |
69d3c270 |
216 | )', |
217 | [ |
218 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
219 | => 'Library' ], |
69d3c270 |
220 | [ $OFFSET => 4 ], |
6a6394f1 |
221 | [ $TOTAL => 5 ], |
69d3c270 |
222 | ], |
223 | |
224 | 'pagination with subquery works' |
225 | ); |
226 | |
227 | } |
228 | |
229 | { |
d7632687 |
230 | $rs = $s->resultset('Artist')->search({}, { |
231 | columns => 'name', |
232 | offset => 1, |
233 | order_by => 'name', |
234 | }); |
235 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
236 | |
237 | like ( |
238 | ${$rs->as_query}->[0], |
239 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
240 | 'Newlines/spaces preserved in final sql', |
241 | ); |
242 | } |
243 | |
f74d22e2 |
244 | { |
245 | my $subq = $s->resultset('Owners')->search({ |
246 | 'books.owner' => { -ident => 'owner.id' }, |
247 | }, { alias => 'owner', select => ['id'] } )->count_rs; |
248 | |
249 | my $rs_selectas_rel = $s->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } ); |
250 | |
251 | is_same_sql_bind( |
252 | $rs_selectas_rel->as_query, |
6a6394f1 |
253 | '( |
90ed89cb |
254 | SELECT me.id, me.owner FROM ( |
cccd1876 |
255 | SELECT me.id, me.owner FROM books me WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) ) |
256 | ) me |
257 | WHERE ROWNUM <= ? |
6a6394f1 |
258 | )', |
f74d22e2 |
259 | [ |
260 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
cccd1876 |
261 | [ $ROWS => 1 ], |
f74d22e2 |
262 | ], |
263 | 'Pagination with sub-query in WHERE works' |
264 | ); |
265 | |
266 | } |
267 | |
327368bc |
268 | done_testing; |