1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
3 # Test to ensure we get a consistent result set wether or not we use the
4 # prefetch option in combination rows (LIMIT).
11 use DBICTest ':DiffSQL';
12 use DBIx::Class::SQLMaker::LimitDialects;
14 my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype;
16 my $schema = DBICTest->init_schema();
19 my $no_prefetch = $schema->resultset('Artist')->search(
20 [ # search deliberately contrived
21 { 'artwork.cd_id' => undef },
22 { 'tracks.title' => { '!=' => 'blah-blah-1234568' }}
24 { rows => 3, join => { cds => [qw/artwork tracks/] },
28 my $use_prefetch = $no_prefetch->search(
31 select => ['me.artistid', 'me.name'],
32 as => ['artistid', 'name'],
34 order_by => { -desc => 'name' },
38 # add an extra +select to make sure it does not throw things off
39 # we also expect it to appear in both selectors, as we can not know
40 # for sure which part of the query it applies to (may be order_by,
41 # maybe something else)
43 # we use a reference to the same array in bind vals, because
44 # is_deeply picks up this difference too (not sure if bug or
46 $use_prefetch = $use_prefetch->search({}, {
47 '+columns' => { monkeywrench => \[ 'me.artistid + ?', [ \ 'inTEger' => 1 ] ] },
50 my $bind_int_resolved = sub { [ { sqlt_datatype => 'inTEger' } => 1 ] };
51 my $bind_vc_resolved = sub { [
52 { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' }
53 => 'blah-blah-1234568'
56 $use_prefetch->as_query,
58 SELECT me.artistid + ?,
60 cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track
62 SELECT me.artistid + ?,
66 ON cds.artist = me.artistid
67 LEFT JOIN cd_artwork artwork
68 ON artwork.cd_id = cds.cdid
69 LEFT JOIN track tracks
70 ON tracks.cd = cds.cdid
71 WHERE artwork.cd_id IS NULL
73 GROUP BY me.artistid + ?, me.artistid, me.name
74 ORDER BY name DESC LIMIT ?
77 ON cds.artist = me.artistid
78 LEFT JOIN cd_artwork artwork
79 ON artwork.cd_id = cds.cdid
80 LEFT JOIN track tracks
81 ON tracks.cd = cds.cdid
82 WHERE artwork.cd_id IS NULL
87 $bind_int_resolved->(), # outer select
88 $bind_int_resolved->(), # inner select
89 $bind_vc_resolved->(), # inner where
90 $bind_int_resolved->(), # inner group_by
92 $bind_vc_resolved->(), # outer where
94 'Expected SQL on complex limited prefetch'
97 is($no_prefetch->count, $use_prefetch->count, '$no_prefetch->count == $use_prefetch->count');
99 scalar ($no_prefetch->all),
100 scalar ($use_prefetch->all),
101 "Amount of returned rows is right"
104 my $artist_many_cds = $schema->resultset('Artist')->search ( {}, {
106 group_by => 'me.artistid',
107 having => \ 'count(cds.cdid) > 1',
111 $no_prefetch = $schema->resultset('Artist')->search(
112 { artistid => $artist_many_cds->id },
116 $use_prefetch = $no_prefetch->search ({}, { prefetch => 'cds' });
118 my $normal_artist = $no_prefetch->single;
119 my $prefetch_artist = $use_prefetch->find({ name => $artist_many_cds->name });
120 my $prefetch2_artist = $use_prefetch->first;
123 $prefetch_artist->cds->count,
124 $normal_artist->cds->count,
125 "Count of child rel with prefetch + rows => 1 is right (find)"
128 $prefetch2_artist->cds->count,
129 $normal_artist->cds->count,
130 "Count of child rel with prefetch + rows => 1 is right (first)"
134 scalar ($prefetch_artist->cds->all),
135 scalar ($normal_artist->cds->all),
136 "Amount of child rel rows with prefetch + rows => 1 is right (find)"
139 scalar ($prefetch2_artist->cds->all),
140 scalar ($normal_artist->cds->all),
141 "Amount of child rel rows with prefetch + rows => 1 is right (first)"
145 sub { $use_prefetch->single },
146 qr/\Qsingle() can not be used on resultsets collapsing a has_many/,
147 'single() with multiprefetch is illegal',
152 $use_prefetch->search(
153 {'tracks.title' => { '!=' => 'foo' }},
154 { order_by => \ 'some oddball literal sql', join => { cds => 'tracks' } }
156 }, qr/Unable to programatically derive a required group_by from the supplied order_by criteria/,
159 my $artist = $use_prefetch->search({'cds.title' => $artist_many_cds->cds->first->title })->next;
160 is($artist->cds->count, 1, "count on search limiting prefetched has_many");
162 # try with double limit
163 my $artist2 = $use_prefetch->search({'cds.title' => { '!=' => $artist_many_cds->cds->first->title } })->slice (0,0)->next;
164 is($artist2->cds->count, 2, "count on search limiting prefetched has_many");
166 # make sure 1:1 joins do not force a subquery (no point to exercise the optimizer, if at all available)
167 # get cd's that have any tracks and their artists
168 my $single_prefetch_rs = $schema->resultset ('CD')->search (
169 { 'me.year' => 2010, 'artist.name' => 'foo' },
170 { prefetch => ['tracks', 'artist'], rows => 15 },
173 $single_prefetch_rs->as_query,
176 me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
177 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
178 artist.artistid, artist.name, artist.rank, artist.charfield
181 me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
183 JOIN artist artist ON artist.artistid = me.artist
184 WHERE ( ( artist.name = ? AND me.year = ? ) )
187 LEFT JOIN track tracks
188 ON tracks.cd = me.cdid
190 ON artist.artistid = me.artist
191 WHERE ( ( artist.name = ? AND me.year = ? ) )
194 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ],
195 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ],
197 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ],
198 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ],
200 'No grouping of non-multiplying resultsets',
203 my $many_one_many_rs = $schema->resultset('CD')->search({}, {
204 prefetch => { tracks => { lyrics => 'lyric_versions' } },
206 order_by => ['lyrics.track_id'],
210 $many_one_many_rs->as_query,
212 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
213 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
214 lyrics.lyric_id, lyrics.track_id, lyric_versions.id, lyric_versions.lyric_id, lyric_versions.text
216 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
218 LEFT JOIN track tracks
219 ON tracks.cd = me.cdid
220 LEFT JOIN lyrics lyrics
221 ON lyrics.track_id = tracks.trackid
222 GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
223 ORDER BY MIN(lyrics.track_id)
226 LEFT JOIN track tracks
227 ON tracks.cd = me.cdid
228 LEFT JOIN lyrics lyrics
229 ON lyrics.track_id = tracks.trackid
230 LEFT JOIN lyric_versions lyric_versions
231 ON lyric_versions.lyric_id = lyrics.lyric_id
232 ORDER BY lyrics.track_id
235 [ { sqlt_datatype => 'integer' } => 2 ]
237 'Correct SQL on indirectly multiplied orderer',
240 my $cond_on_multi_ord_by_single = $schema->resultset('CD')->search(
242 'tracks.position' => { '!=', 1 },
245 prefetch => [qw( tracks artist )],
246 order_by => 'artist.name',
252 $cond_on_multi_ord_by_single->as_query,
254 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
255 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at,
256 artist.artistid, artist.name, artist.rank, artist.charfield
258 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
260 LEFT JOIN track tracks
261 ON tracks.cd = me.cdid
263 ON artist.artistid = me.artist
264 WHERE tracks.position != ?
265 GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, artist.name
269 LEFT JOIN track tracks
270 ON tracks.cd = me.cdid
272 ON artist.artistid = me.artist
273 WHERE tracks.position != ?
277 [ { dbic_colname => "tracks.position", sqlt_datatype => "int" }
280 [ { sqlt_datatype => "integer" }
283 [ { dbic_colname => "tracks.position", sqlt_datatype => "int" }
287 'Correct SQl on prefetch with limit of restricting multi ordered by a single'