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