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',
159 my $o_sel = $ord_set->{exselect_outer}
160 ? ', ' . $ord_set->{exselect_outer}
163 my $i_sel = $ord_set->{exselect_inner}
164 ? ', ' . $ord_set->{exselect_inner}
169 $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}})->as_query,
170 "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
173 me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel
176 me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
178 JOIN owners owner ON owner.id = me.owner
180 ORDER BY $ord_set->{order_inner}
182 ORDER BY $ord_set->{order_outer}
184 ORDER BY $ord_set->{order_req}
186 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
193 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
194 '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name
196 SELECT me.id, me.source, me.owner, me.price, me.title
199 me.id, me.source, me.owner, me.price, me.title
202 me.id, me.source, me.owner, me.price, me.title
204 JOIN owners owner ON owner.id = me.owner
213 JOIN owners owner ON owner.id = me.owner
218 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
223 # test deprecated column mixing over join boundaries
224 my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
225 '+select' => ['owner.name'],
226 '+as' => ['owner_name'],
231 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
233 TOP 1 me.id, me.source, me.owner, me.title, me.price,
236 JOIN owners owner ON owner.id = me.owner
239 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
244 my $rs = $schema->resultset('Artist')->search({}, {
245 columns => 'artistid',
247 order_by => 'artistid',
249 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
252 ${$rs->as_query}->[0],
253 qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
254 'Newlines/spaces preserved in final sql',
259 my $subq = $schema->resultset('Owners')->search({
260 'books.owner' => { -ident => 'owner.id' },
261 }, { alias => 'owner', select => ['id'] } )->count_rs;
263 my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } );
266 $rs_selectas_rel->as_query,
267 '(SELECT TOP 1 me.id, me.owner FROM books me WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) ) )',
269 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
271 'Pagination with sub-query in WHERE works'