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