7 use DBIC::SqlMakerTest;
9 my $schema = DBICTest->init_schema;
11 # Trick the sqlite DB to use Top limit emulation
12 # We could test all of this via $sq->$op directly,
13 # but some conditions need a $rsrc
14 delete $schema->storage->_sql_maker->{_cached_syntax};
15 $schema->storage->_sql_maker->limit_dialect ('Top');
17 my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, {
18 prefetch => 'owner', rows => 2, offset => 3,
19 columns => [ grep { $_ ne 'title' } $schema->source('BooksInLibrary')->columns ],
29 my $rs = $books_45_and_owners->search ({}, {order_by => $null_order });
33 me.id, me.source, me.owner, me.price, owner__id, owner__name
36 me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name
38 JOIN owners owner ON owner.id = me.owner
44 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
50 my $subq = $schema->resultset('Owners')->search({
51 'count.id' => { -ident => 'owner.id' },
52 }, { alias => 'owner' })->count_rs;
54 my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, {
56 { owner_name => 'owner.name' },
57 { owner_books => $subq->as_query },
65 $rs_selectas_rel->as_query,
67 SELECT TOP 2 owner_name, owner_books
69 SELECT TOP 5 owner.name AS owner_name,
72 WHERE ( count.id = owner.id )
75 JOIN owners owner ON owner.id = me.owner
81 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
83 'pagination with subqueries works'
90 order_by => \'title DESC',
91 order_inner => 'title DESC',
92 order_outer => 'ORDER__BY__001 ASC',
93 order_req => 'ORDER__BY__001 DESC',
94 exselect_outer => 'ORDER__BY__001',
95 exselect_inner => 'title AS ORDER__BY__001',
98 order_by => { -asc => 'title' },
99 order_inner => 'title ASC',
100 order_outer => 'ORDER__BY__001 DESC',
101 order_req => 'ORDER__BY__001 ASC',
102 exselect_outer => 'ORDER__BY__001',
103 exselect_inner => 'title AS ORDER__BY__001',
106 order_by => { -desc => 'title' },
107 order_inner => 'title DESC',
108 order_outer => 'ORDER__BY__001 ASC',
109 order_req => 'ORDER__BY__001 DESC',
110 exselect_outer => 'ORDER__BY__001',
111 exselect_inner => 'title AS ORDER__BY__001',
115 order_inner => 'title',
116 order_outer => 'ORDER__BY__001 DESC',
117 order_req => 'ORDER__BY__001',
118 exselect_outer => 'ORDER__BY__001',
119 exselect_inner => 'title AS ORDER__BY__001',
122 order_by => [ qw{ title me.owner} ],
123 order_inner => 'title, me.owner',
124 order_outer => 'ORDER__BY__001 DESC, me.owner DESC',
125 order_req => 'ORDER__BY__001, me.owner',
126 exselect_outer => 'ORDER__BY__001',
127 exselect_inner => 'title AS ORDER__BY__001',
130 order_by => ['title', { -desc => 'bar' } ],
131 order_inner => 'title, bar DESC',
132 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC',
133 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC',
134 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
135 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
138 order_by => { -asc => [qw{ title bar }] },
139 order_inner => 'title ASC, bar ASC',
140 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 DESC',
141 order_req => 'ORDER__BY__001 ASC, ORDER__BY__002 ASC',
142 exselect_outer => 'ORDER__BY__001, ORDER__BY__002',
143 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002',
148 { -desc => [qw{bar}] },
149 { -asc => [qw{me.owner sensors}]},
151 order_inner => 'title, bar DESC, me.owner ASC, sensors ASC',
152 order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC, me.owner DESC, ORDER__BY__003 DESC',
153 order_req => 'ORDER__BY__001, ORDER__BY__002 DESC, me.owner ASC, ORDER__BY__003 ASC',
154 exselect_outer => 'ORDER__BY__001, ORDER__BY__002, ORDER__BY__003',
155 exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002, sensors AS ORDER__BY__003',
158 my $o_sel = $ord_set->{exselect_outer}
159 ? ', ' . $ord_set->{exselect_outer}
162 my $i_sel = $ord_set->{exselect_inner}
163 ? ', ' . $ord_set->{exselect_inner}
168 $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}})->as_query,
169 "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
172 me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel
175 me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
177 JOIN owners owner ON owner.id = me.owner
179 ORDER BY $ord_set->{order_inner}
181 ORDER BY $ord_set->{order_outer}
183 ORDER BY $ord_set->{order_req}
185 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
192 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
193 '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name
195 SELECT me.id, me.source, me.owner, me.price, me.title
198 me.id, me.source, me.owner, me.price, me.title
201 me.id, me.source, me.owner, me.price, me.title
203 JOIN owners owner ON owner.id = me.owner
212 JOIN owners owner ON owner.id = me.owner
217 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
222 # test deprecated column mixing over join boundaries
223 my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, {
224 '+select' => ['owner.name'],
225 '+as' => ['owner_name'],
230 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
232 TOP 1 me.id, me.source, me.owner, me.title, me.price,
235 JOIN owners owner ON owner.id = me.owner
238 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
243 my $rs = $schema->resultset('Artist')->search({}, {
244 columns => 'artistid',
246 order_by => 'artistid',
248 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
251 ${$rs->as_query}->[0],
252 qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
253 'Newlines/spaces preserved in final sql',
258 my $subq = $schema->resultset('Owners')->search({
259 'books.owner' => { -ident => 'owner.id' },
260 }, { alias => 'owner', select => ['id'] } )->count_rs;
262 my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } );
265 $rs_selectas_rel->as_query,
266 '(SELECT TOP 1 me.id, me.owner FROM books me WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) ) )',
268 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
270 'Pagination with sub-query in WHERE works'