Commit | Line | Data |
c0329273 |
1 | BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) } |
2 | |
96eacdb7 |
3 | use strict; |
4 | use warnings; |
5 | |
6 | use Test::More; |
c0329273 |
7 | |
a5a7bb73 |
8 | use DBICTest ':DiffSQL'; |
96eacdb7 |
9 | |
10 | my $schema = DBICTest->init_schema; |
11 | |
12 | # based on toplimit.t |
13 | delete $schema->storage->_sql_maker->{_cached_syntax}; |
14 | $schema->storage->_sql_maker->limit_dialect ('FetchFirst'); |
15 | |
86bb5a27 |
16 | my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, { |
17 | prefetch => 'owner', rows => 2, offset => 3, |
18 | columns => [ grep { $_ ne 'title' } $schema->source('BooksInLibrary')->columns ], |
19 | }); |
96eacdb7 |
20 | |
21 | for my $null_order ( |
22 | undef, |
23 | '', |
24 | {}, |
25 | [], |
26 | [{}], |
27 | ) { |
28 | my $rs = $books_45_and_owners->search ({}, {order_by => $null_order }); |
29 | is_same_sql_bind( |
30 | $rs->as_query, |
90ed89cb |
31 | '(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name |
96eacdb7 |
32 | FROM ( |
86bb5a27 |
33 | SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name |
96eacdb7 |
34 | FROM books me |
35 | JOIN owners owner ON owner.id = me.owner |
36 | WHERE ( source = ? ) |
37 | ORDER BY me.id |
38 | FETCH FIRST 5 ROWS ONLY |
39 | ) me |
40 | ORDER BY me.id DESC |
41 | FETCH FIRST 2 ROWS ONLY |
42 | )', |
43 | [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
44 | => 'Library' ] ], |
45 | ); |
46 | } |
47 | |
48 | |
49 | for my $ord_set ( |
50 | { |
86bb5a27 |
51 | order_by => \'title DESC', |
52 | order_inner => 'title DESC', |
08a1eaad |
53 | order_outer => 'ORDER__BY__001 ASC', |
54 | order_req => 'ORDER__BY__001 DESC', |
55 | exselect_outer => 'ORDER__BY__001', |
56 | exselect_inner => 'title AS ORDER__BY__001', |
96eacdb7 |
57 | }, |
58 | { |
86bb5a27 |
59 | order_by => { -asc => 'title' }, |
60 | order_inner => 'title ASC', |
08a1eaad |
61 | order_outer => 'ORDER__BY__001 DESC', |
62 | order_req => 'ORDER__BY__001 ASC', |
63 | exselect_outer => 'ORDER__BY__001', |
64 | exselect_inner => 'title AS ORDER__BY__001', |
96eacdb7 |
65 | }, |
66 | { |
86bb5a27 |
67 | order_by => { -desc => 'title' }, |
68 | order_inner => 'title DESC', |
08a1eaad |
69 | order_outer => 'ORDER__BY__001 ASC', |
70 | order_req => 'ORDER__BY__001 DESC', |
71 | exselect_outer => 'ORDER__BY__001', |
72 | exselect_inner => 'title AS ORDER__BY__001', |
96eacdb7 |
73 | }, |
74 | { |
86bb5a27 |
75 | order_by => 'title', |
76 | order_inner => 'title', |
08a1eaad |
77 | order_outer => 'ORDER__BY__001 DESC', |
78 | order_req => 'ORDER__BY__001', |
79 | exselect_outer => 'ORDER__BY__001', |
80 | exselect_inner => 'title AS ORDER__BY__001', |
96eacdb7 |
81 | }, |
82 | { |
86bb5a27 |
83 | order_by => [ qw{ title me.owner} ], |
84 | order_inner => 'title, me.owner', |
08a1eaad |
85 | order_outer => 'ORDER__BY__001 DESC, me.owner DESC', |
86 | order_req => 'ORDER__BY__001, me.owner', |
87 | exselect_outer => 'ORDER__BY__001', |
88 | exselect_inner => 'title AS ORDER__BY__001', |
96eacdb7 |
89 | }, |
90 | { |
86bb5a27 |
91 | order_by => ['title', { -desc => 'bar' } ], |
92 | order_inner => 'title, bar DESC', |
08a1eaad |
93 | order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC', |
94 | order_req => 'ORDER__BY__001, ORDER__BY__002 DESC', |
95 | exselect_outer => 'ORDER__BY__001, ORDER__BY__002', |
96 | exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002', |
96eacdb7 |
97 | }, |
98 | { |
86bb5a27 |
99 | order_by => { -asc => [qw{ title bar }] }, |
100 | order_inner => 'title ASC, bar ASC', |
08a1eaad |
101 | order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 DESC', |
102 | order_req => 'ORDER__BY__001 ASC, ORDER__BY__002 ASC', |
103 | exselect_outer => 'ORDER__BY__001, ORDER__BY__002', |
104 | exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002', |
96eacdb7 |
105 | }, |
106 | { |
107 | order_by => [ |
86bb5a27 |
108 | 'title', |
96eacdb7 |
109 | { -desc => [qw{bar}] }, |
110 | { -asc => [qw{me.owner sensors}]}, |
111 | ], |
86bb5a27 |
112 | order_inner => 'title, bar DESC, me.owner ASC, sensors ASC', |
08a1eaad |
113 | order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC, me.owner DESC, ORDER__BY__003 DESC', |
114 | order_req => 'ORDER__BY__001, ORDER__BY__002 DESC, me.owner ASC, ORDER__BY__003 ASC', |
115 | exselect_outer => 'ORDER__BY__001, ORDER__BY__002, ORDER__BY__003', |
116 | exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002, sensors AS ORDER__BY__003', |
96eacdb7 |
117 | }, |
66950d7a |
118 | |
119 | { |
120 | order_by => [ |
121 | 'name', |
122 | ], |
123 | order_inner => 'name', |
124 | order_outer => 'name DESC', |
125 | order_req => 'name', |
126 | }, |
96eacdb7 |
127 | ) { |
128 | my $o_sel = $ord_set->{exselect_outer} |
129 | ? ', ' . $ord_set->{exselect_outer} |
130 | : '' |
131 | ; |
132 | my $i_sel = $ord_set->{exselect_inner} |
133 | ? ', ' . $ord_set->{exselect_inner} |
134 | : '' |
135 | ; |
136 | |
66950d7a |
137 | my $rs = $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}}); |
138 | |
139 | # query actually works |
140 | ok( defined $rs->count, 'Query actually works' ); |
141 | |
96eacdb7 |
142 | is_same_sql_bind( |
66950d7a |
143 | $rs->as_query, |
90ed89cb |
144 | "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name |
96eacdb7 |
145 | FROM ( |
90ed89cb |
146 | SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel |
96eacdb7 |
147 | FROM ( |
86bb5a27 |
148 | SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel |
96eacdb7 |
149 | FROM books me |
150 | JOIN owners owner ON owner.id = me.owner |
151 | WHERE ( source = ? ) |
152 | ORDER BY $ord_set->{order_inner} |
153 | FETCH FIRST 5 ROWS ONLY |
154 | ) me |
155 | ORDER BY $ord_set->{order_outer} |
156 | FETCH FIRST 2 ROWS ONLY |
157 | ) me |
158 | ORDER BY $ord_set->{order_req} |
96eacdb7 |
159 | )", |
160 | [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
161 | => 'Library' ] ], |
162 | ); |
66950d7a |
163 | |
96eacdb7 |
164 | } |
165 | |
166 | # with groupby |
167 | is_same_sql_bind ( |
168 | $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query, |
86bb5a27 |
169 | '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name |
96eacdb7 |
170 | FROM ( |
97e130fa |
171 | SELECT me.id, me.source, me.owner, me.price, me.title |
96eacdb7 |
172 | FROM ( |
97e130fa |
173 | SELECT me.id, me.source, me.owner, me.price, me.title |
96eacdb7 |
174 | FROM ( |
97e130fa |
175 | SELECT me.id, me.source, me.owner, me.price, me.title |
96eacdb7 |
176 | FROM books me |
177 | JOIN owners owner ON owner.id = me.owner |
178 | WHERE ( source = ? ) |
179 | GROUP BY title |
180 | ORDER BY title |
181 | FETCH FIRST 5 ROWS ONLY |
182 | ) me |
97e130fa |
183 | ORDER BY title DESC |
96eacdb7 |
184 | FETCH FIRST 2 ROWS ONLY |
185 | ) me |
97e130fa |
186 | ORDER BY title |
96eacdb7 |
187 | ) me |
188 | JOIN owners owner ON owner.id = me.owner |
189 | WHERE ( source = ? ) |
190 | ORDER BY title |
191 | )', |
192 | [ map { [ |
193 | { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
194 | => 'Library' ] |
195 | } (1,2) ], |
196 | ); |
197 | |
198 | # test deprecated column mixing over join boundaries |
199 | my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, { |
200 | '+select' => ['owner.name'], |
201 | '+as' => ['owner_name'], |
202 | join => 'owner', |
8273e845 |
203 | rows => 1 |
96eacdb7 |
204 | }); |
205 | |
206 | is_same_sql_bind( $rs_selectas_top->search({})->as_query, |
207 | '(SELECT |
a66b662c |
208 | me.id, me.source, me.owner, me.title, me.price, owner.name |
96eacdb7 |
209 | FROM books me |
210 | JOIN owners owner ON owner.id = me.owner |
211 | WHERE ( source = ? ) |
96eacdb7 |
212 | FETCH FIRST 1 ROWS ONLY |
213 | )', |
214 | [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
215 | => 'Library' ] ], |
216 | ); |
217 | |
218 | { |
219 | my $rs = $schema->resultset('Artist')->search({}, { |
86bb5a27 |
220 | columns => 'artistid', |
96eacdb7 |
221 | offset => 1, |
86bb5a27 |
222 | order_by => 'artistid', |
96eacdb7 |
223 | }); |
224 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
225 | |
226 | like ( |
227 | ${$rs->as_query}->[0], |
228 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
229 | 'Newlines/spaces preserved in final sql', |
230 | ); |
231 | } |
232 | |
233 | done_testing; |