1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
8 use DBICTest ':DiffSQL';
10 my $schema = DBICTest->init_schema;
12 # Trick the sqlite DB to use Top limit emulation
13 # We could test all of this via $sq->$op directly,
14 # but some conditions need a $rsrc
15 delete $schema->storage->_sql_maker->{_cached_syntax};
16 $schema->storage->_sql_maker->limit_dialect ('Top');
18 my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, {
19 prefetch => 'owner', rows => 2, offset => 3,
20 columns => [ grep { $_ ne 'title' } $schema->source('BooksInLibrary')->columns ],
30 my $rs = $books_45_and_owners->search ({}, {order_by => $null_order });
34 me.id, me.source, me.owner, me.price, owner__id, owner__name
37 me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name
39 JOIN owners owner ON owner.id = me.owner
45 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
51 my $subq = $schema->resultset('Owners')->search({
52 'count.id' => { -ident => 'owner.id' },
53 }, { alias => 'owner' })->count_rs;
55 my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, {
57 { owner_name => 'owner.name' },
58 { owner_books => $subq->as_query },
66 $rs_selectas_rel->as_query,
68 SELECT TOP 2 owner_name, owner_books
70 SELECT TOP 5 owner.name AS owner_name,
73 WHERE ( count.id = owner.id )
76 JOIN owners owner ON owner.id = me.owner
82 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
84 'pagination with subqueries works'
91 order_by => \'title DESC',
92 order_inner => 'title DESC',
93 order_outer => 'ORDER__BY__001 ASC',
94 order_req => 'ORDER__BY__001 DESC',
95 exselect_outer => 'ORDER__BY__001',
96 exselect_inner => 'title AS ORDER__BY__001',
99 order_by => { -asc => 'title' },
100 order_inner => 'title ASC',
101 order_outer => 'ORDER__BY__001 DESC',
102 order_req => 'ORDER__BY__001 ASC',
103 exselect_outer => 'ORDER__BY__001',
104 exselect_inner => 'title AS ORDER__BY__001',
107 order_by => { -desc => 'title' },
108 order_inner => 'title DESC',
109 order_outer => 'ORDER__BY__001 ASC',
110 order_req => 'ORDER__BY__001 DESC',
111 exselect_outer => 'ORDER__BY__001',
112 exselect_inner => 'title AS ORDER__BY__001',
116 order_inner => 'title',
117 order_outer => 'ORDER__BY__001 DESC',
118 order_req => 'ORDER__BY__001',
119 exselect_outer => 'ORDER__BY__001',
120 exselect_inner => 'title AS ORDER__BY__001',
123 order_by => [ qw{ title me.owner} ],
124 order_inner => 'title, me.owner',
125 order_outer => 'ORDER__BY__001 DESC, me.owner DESC',
126 order_req => 'ORDER__BY__001, me.owner',
127 exselect_outer => 'ORDER__BY__001',
128 exselect_inner => 'title AS ORDER__BY__001',
131 order_by => ['title', { -desc => 'bar' } ],
132 order_inner => 'title, bar DESC',
133 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC',
134 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC',
135 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
136 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
139 order_by => { -asc => [qw{ title bar }] },
140 order_inner => 'title ASC, bar ASC',
141 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 DESC',
142 order_req => 'ORDER__BY__001 ASC, ORDER__BY__002 ASC',
143 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
144 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
149 { -desc => [qw{bar}] },
150 { -asc => [qw{me.owner sensors}]},
152 order_inner => 'title, bar DESC, me.owner ASC, sensors ASC',
153 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC, me.owner DESC, ORDER__BY__003 DESC',
154 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC, me.owner ASC, ORDER__BY__003 ASC',
155 exselect_outer => 'ORDER__BY__001, ORDER__BY__002, ORDER__BY__003',
156 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002, sensors AS ORDER__BY__003',
160 { -asc => 'title', -nulls => 'first' },
161 { -desc => 'bar', -nulls => 'last' },
163 order_inner => 'title ASC NULLS FIRST, bar DESC NULLS LAST',
164 order_outer => 'ORDER__BY__001 DESC NULLS LAST, ORDER__BY__002 ASC NULLS FIRST',
165 order_req => 'ORDER__BY__001 ASC NULLS FIRST, ORDER__BY__002 DESC NULLS LAST',
166 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
167 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
170 my $o_sel = $ord_set->{exselect_outer}
171 ? ', ' . $ord_set->{exselect_outer}
174 my $i_sel = $ord_set->{exselect_inner}
175 ? ', ' . $ord_set->{exselect_inner}
180 $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}})->as_query,
181 "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
184 me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel
187 me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
189 JOIN owners owner ON owner.id = me.owner
191 ORDER BY $ord_set->{order_inner}
193 ORDER BY $ord_set->{order_outer}
195 ORDER BY $ord_set->{order_req}
197 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
204 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
205 '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name
207 SELECT me.id, me.source, me.owner, me.price, me.title
210 me.id, me.source, me.owner, me.price, me.title
213 me.id, me.source, me.owner, me.price, me.title
215 JOIN owners owner ON owner.id = me.owner
224 JOIN owners owner ON owner.id = me.owner
229 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
234 # test deprecated column mixing over join boundaries
235 my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
236 '+select' => ['owner.name'],
237 '+as' => ['owner_name'],
242 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
244 TOP 1 me.id, me.source, me.owner, me.title, me.price,
247 JOIN owners owner ON owner.id = me.owner
250 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
255 my $rs = $schema->resultset('Artist')->search({}, {
256 columns => 'artistid',
258 order_by => 'artistid',
260 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
263 ${$rs->as_query}->[0],
264 qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
265 'Newlines/spaces preserved in final sql',
270 my $subq = $schema->resultset('Owners')->search({
271 'books.owner' => { -ident => 'owner.id' },
272 }, { alias => 'owner', select => ['id'] } )->count_rs;
274 my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } );
277 $rs_selectas_rel->as_query,
278 '(SELECT TOP 1 me.id, me.owner FROM books me WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) ) )',
280 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
282 'Pagination with sub-query in WHERE works'