1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
8 use DBICTest ':DiffSQL';
10 my $schema = DBICTest->init_schema;
13 delete $schema->storage->_sql_maker->{_cached_syntax};
14 $schema->storage->_sql_maker->limit_dialect ('FetchFirst');
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 ],
28 my $rs = $books_45_and_owners->search ({}, {order_by => $null_order });
31 '(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
33 SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name
35 JOIN owners owner ON owner.id = me.owner
38 FETCH FIRST 5 ROWS ONLY
41 FETCH FIRST 2 ROWS ONLY
43 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
51 order_by => \'title DESC',
52 order_inner => 'title DESC',
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',
59 order_by => { -asc => 'title' },
60 order_inner => 'title ASC',
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',
67 order_by => { -desc => 'title' },
68 order_inner => 'title DESC',
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',
76 order_inner => 'title',
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',
83 order_by => [ qw{ title me.owner} ],
84 order_inner => 'title, me.owner',
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',
91 order_by => ['title', { -desc => 'bar' } ],
92 order_inner => 'title, bar DESC',
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',
99 order_by => { -asc => [qw{ title bar }] },
100 order_inner => 'title ASC, bar ASC',
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',
109 { -desc => [qw{bar}] },
110 { -asc => [qw{me.owner sensors}]},
112 order_inner => 'title, bar DESC, me.owner ASC, sensors ASC',
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',
123 order_inner => 'name',
124 order_outer => 'name DESC',
128 my $o_sel = $ord_set->{exselect_outer}
129 ? ', ' . $ord_set->{exselect_outer}
132 my $i_sel = $ord_set->{exselect_inner}
133 ? ', ' . $ord_set->{exselect_inner}
137 my $rs = $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}});
139 # query actually works
140 ok( defined $rs->count, 'Query actually works' );
144 "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name
146 SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel
148 SELECT me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel
150 JOIN owners owner ON owner.id = me.owner
152 ORDER BY $ord_set->{order_inner}
153 FETCH FIRST 5 ROWS ONLY
155 ORDER BY $ord_set->{order_outer}
156 FETCH FIRST 2 ROWS ONLY
158 ORDER BY $ord_set->{order_req}
160 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
168 $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query,
169 '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name
171 SELECT me.id, me.source, me.owner, me.price, me.title
173 SELECT me.id, me.source, me.owner, me.price, me.title
175 SELECT me.id, me.source, me.owner, me.price, me.title
177 JOIN owners owner ON owner.id = me.owner
181 FETCH FIRST 5 ROWS ONLY
184 FETCH FIRST 2 ROWS ONLY
188 JOIN owners owner ON owner.id = me.owner
193 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
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'],
206 is_same_sql_bind( $rs_selectas_top->search({})->as_query,
208 me.id, me.source, me.owner, me.title, me.price, owner.name
210 JOIN owners owner ON owner.id = me.owner
212 FETCH FIRST 1 ROWS ONLY
214 [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
219 my $rs = $schema->resultset('Artist')->search({}, {
220 columns => 'artistid',
222 order_by => 'artistid',
224 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
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',