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