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