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 | ], |
82 | }, { |
83 | name => 'Rownum subsel aliasing #2 works correctly', |
84 | rs => $rs->search_rs(undef, { |
85 | rows => 2, |
86 | offset => 3, |
87 | columns => [ |
88 | { id => 'foo.id' }, |
89 | { 'ends_with_me.id' => 'ends_with_me.id' }, |
90 | ] |
91 | }), |
92 | sql => '( |
93 | SELECT id, ends_with_me__id |
f8583f8f |
94 | FROM ( |
95 | SELECT id, ends_with_me__id, ROWNUM rownum__index |
6a6394f1 |
96 | FROM ( |
97 | SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id |
8b31f62e |
98 | FROM cd me |
99 | WHERE id = ? |
6a6394f1 |
100 | ) me |
101 | ) me WHERE rownum__index BETWEEN ? AND ? |
102 | )', |
103 | binds => [ |
8b31f62e |
104 | $where_bind, |
6a6394f1 |
105 | [ $OFFSET => 4 ], |
106 | [ $TOTAL => 5 ], |
107 | ], |
108 | }, { |
109 | name => 'Rownum subsel aliasing #2 works correctly with unique order_by', |
110 | rs => $rs->search_rs(undef, { |
111 | rows => 2, |
112 | offset => 3, |
113 | columns => [ |
114 | { id => 'foo.id' }, |
115 | { 'ends_with_me.id' => 'ends_with_me.id' }, |
116 | ], |
117 | order_by => [qw( artist title )], |
118 | }), |
119 | sql => '( |
120 | SELECT id, ends_with_me__id |
121 | FROM ( |
122 | SELECT id, ends_with_me__id, ROWNUM rownum__index |
123 | FROM ( |
124 | SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id |
8b31f62e |
125 | FROM cd me |
126 | WHERE id = ? |
6a6394f1 |
127 | ORDER BY artist, title |
128 | ) me |
fcb7fcbb |
129 | WHERE ROWNUM <= ? |
f8583f8f |
130 | ) me |
6a6394f1 |
131 | WHERE rownum__index >= ? |
132 | )', |
133 | binds => [ |
8b31f62e |
134 | $where_bind, |
6a6394f1 |
135 | [ $TOTAL => 5 ], |
136 | [ $OFFSET => 4 ], |
137 | ], |
138 | } |
139 | ) { |
140 | is_same_sql_bind( |
141 | $test_set->{rs}->as_query, |
142 | $test_set->{sql}, |
143 | $test_set->{binds}, |
144 | $test_set->{name}); |
145 | } |
f8583f8f |
146 | |
d7632687 |
147 | { |
69d3c270 |
148 | my $subq = $s->resultset('Owners')->search({ |
149 | 'count.id' => { -ident => 'owner.id' }, |
150 | }, { alias => 'owner' })->count_rs; |
151 | |
152 | my $rs_selectas_rel = $s->resultset('BooksInLibrary')->search ({}, { |
153 | columns => [ |
154 | { owner_name => 'owner.name' }, |
155 | { owner_books => $subq->as_query }, |
156 | ], |
157 | join => 'owner', |
158 | rows => 2, |
159 | offset => 3, |
160 | }); |
161 | |
162 | is_same_sql_bind( |
163 | $rs_selectas_rel->as_query, |
164 | '( |
165 | SELECT owner_name, owner_books |
166 | FROM ( |
167 | SELECT owner_name, owner_books, ROWNUM rownum__index |
168 | FROM ( |
169 | SELECT owner.name AS owner_name, |
170 | ( SELECT COUNT( * ) FROM owners owner WHERE (count.id = owner.id)) AS owner_books |
171 | FROM books me |
172 | JOIN owners owner ON owner.id = me.owner |
173 | WHERE ( source = ? ) |
174 | ) me |
69d3c270 |
175 | ) me |
6a6394f1 |
176 | WHERE rownum__index BETWEEN ? AND ? |
69d3c270 |
177 | )', |
178 | [ |
179 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
180 | => 'Library' ], |
69d3c270 |
181 | [ $OFFSET => 4 ], |
6a6394f1 |
182 | [ $TOTAL => 5 ], |
69d3c270 |
183 | ], |
184 | |
185 | 'pagination with subquery works' |
186 | ); |
187 | |
188 | } |
189 | |
190 | { |
d7632687 |
191 | $rs = $s->resultset('Artist')->search({}, { |
192 | columns => 'name', |
193 | offset => 1, |
194 | order_by => 'name', |
195 | }); |
196 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
197 | |
198 | like ( |
199 | ${$rs->as_query}->[0], |
200 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
201 | 'Newlines/spaces preserved in final sql', |
202 | ); |
203 | } |
204 | |
f74d22e2 |
205 | { |
206 | my $subq = $s->resultset('Owners')->search({ |
207 | 'books.owner' => { -ident => 'owner.id' }, |
208 | }, { alias => 'owner', select => ['id'] } )->count_rs; |
209 | |
210 | my $rs_selectas_rel = $s->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } ); |
211 | |
212 | is_same_sql_bind( |
213 | $rs_selectas_rel->as_query, |
6a6394f1 |
214 | '( |
215 | SELECT id, owner FROM ( |
216 | SELECT id, owner, ROWNUM rownum__index FROM ( |
217 | SELECT me.id, me.owner FROM books me WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) ) |
218 | ) me |
219 | ) me WHERE rownum__index BETWEEN ? AND ? |
220 | )', |
f74d22e2 |
221 | [ |
222 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
6a6394f1 |
223 | [ $OFFSET => 1 ], |
f74d22e2 |
224 | [ $TOTAL => 1 ], |
225 | ], |
226 | 'Pagination with sub-query in WHERE works' |
227 | ); |
228 | |
229 | } |
230 | |
d7632687 |
231 | |
327368bc |
232 | done_testing; |