7 use DBIC::SqlMakerTest;
9 my $schema = DBICTest->init_schema();
11 my $orig_debug = $schema->storage->debug;
14 eval "use DBD::SQLite";
16 ? ( skip_all => 'needs DBD::SQLite for testing' )
20 # test the abstract join => SQL generator
21 my $sa = new DBIx::Class::SQLAHacks;
24 { child => 'person' },
25 [ { father => 'person' }, { 'father.person_id' => 'child.father_id' }, ],
26 [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ],
28 my $match = 'person child JOIN person father ON ( father.person_id = '
29 . 'child.father_id ) JOIN person mother ON ( mother.person_id '
30 . '= child.mother_id )'
33 $sa->_recurse_from(@j),
39 { mother => 'person' },
40 [ [ { child => 'person' },
41 [ { father => 'person' },
42 { 'father.person_id' => 'child.father_id' }
45 { 'mother.person_id' => 'child.mother_id' }
48 $match = 'person mother JOIN (person child JOIN person father ON ('
49 . ' father.person_id = child.father_id )) ON ( mother.person_id = '
53 $sa->_recurse_from(@j2),
60 { child => 'person' },
61 [ { father => 'person', -join_type => 'inner' }, { 'father.person_id' => 'child.father_id' }, ],
62 [ { mother => 'person', -join_type => 'inner' }, { 'mother.person_id' => 'child.mother_id' } ],
64 $match = 'person child INNER JOIN person father ON ( father.person_id = '
65 . 'child.father_id ) INNER JOIN person mother ON ( mother.person_id '
66 . '= child.mother_id )'
70 $sa->_recurse_from(@j3),
72 'join 3 (inner join) ok'
76 { mother => 'person' },
77 [ [ { child => 'person', -join_type => 'left' },
78 [ { father => 'person', -join_type => 'right' },
79 { 'father.person_id' => 'child.father_id' }
82 { 'mother.person_id' => 'child.mother_id' }
85 $match = 'person mother LEFT JOIN (person child RIGHT JOIN person father ON ('
86 . ' father.person_id = child.father_id )) ON ( mother.person_id = '
90 $sa->_recurse_from(@j4),
92 'join 4 (nested joins + join types) ok'
96 { child => 'person' },
97 [ { father => 'person' }, { 'father.person_id' => \'!= child.father_id' }, ],
98 [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ],
100 $match = 'person child JOIN person father ON ( father.person_id != '
101 . 'child.father_id ) JOIN person mother ON ( mother.person_id '
102 . '= child.mother_id )'
105 $sa->_recurse_from(@j5),
107 'join 5 (SCALAR reference for ON statement) ok'
111 { child => 'person' },
112 [ { father => 'person' }, { 'father.person_id' => { '!=', '42' } }, ],
113 [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ],
115 $match = qr/HASH reference arguments are not supported in JOINS/;
116 eval { $sa->_recurse_from(@j6) };
117 like( $@, $match, 'join 6 (HASH reference for ON statement dies) ok' );
119 my $rs = $schema->resultset("CD")->search(
120 { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' },
121 { from => [ { 'me' => 'cd' },
123 { artist => 'artist' },
124 { 'me.artist' => 'artist.artistid' }
128 is( $rs + 0, 1, "Single record in resultset");
130 is($rs->first->title, 'Forkful of bees', 'Correct record returned');
132 $rs = $schema->resultset("CD")->search(
133 { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' },
134 { join => 'artist' });
136 is( $rs + 0, 1, "Single record in resultset");
138 is($rs->first->title, 'Forkful of bees', 'Correct record returned');
140 $rs = $schema->resultset("CD")->search(
141 { 'artist.name' => 'We Are Goth',
142 'liner_notes.notes' => 'Kill Yourself!' },
143 { join => [ qw/artist liner_notes/ ] });
145 is( $rs + 0, 1, "Single record in resultset");
147 is($rs->first->title, 'Come Be Depressed With Us', 'Correct record returned');
149 # when using join attribute, make sure slice()ing all objects has same count as all()
150 $rs = $schema->resultset("CD")->search(
152 { join => [qw/artist/], order_by => 'artist.name' }
154 is( scalar $rs->all, scalar $rs->slice(0, $rs->count - 1), 'slice() with join has same count as all()' );
156 ok(!$rs->slice($rs->count+1000, $rs->count+1002)->count,
157 'Slicing beyond end of rs returns a zero count');
159 $rs = $schema->resultset("Artist")->search(
160 { 'liner_notes.notes' => 'Kill Yourself!' },
161 { join => { 'cds' => 'liner_notes' } });
163 is( $rs->count, 1, "Single record in resultset");
165 is($rs->first->name, 'We Are Goth', 'Correct record returned');
169 $schema->populate('Artist', [
170 [ qw/artistid name/ ],
171 [ 4, 'Another Boy Band' ],
173 $schema->populate('CD', [
174 [ qw/cdid artist title year/ ],
175 [ 6, 2, "Greatest Hits", 2001 ],
176 [ 7, 4, "Greatest Hits", 2005 ],
177 [ 8, 4, "BoyBandBlues", 2008 ],
179 $schema->populate('TwoKeys', [
188 return $schema->resultset("CD")->count;
191 return $schema->resultset("TwoKeys")->count;
194 is(cd_count(), 8, '8 rows in table cd');
195 is(tk_count(), 7, '7 rows in table twokeys');
198 return $schema->resultset("CD")->search(
199 { 'artist.name' => 'Caterwauler McCrae' },
200 { join => [qw/artist/]}
204 return $schema->resultset("CD")->search(
205 { 'artist.name' => 'Random Boy Band' },
206 { join => [qw/artist/]}
210 is( artist1()->count, 3, '3 Caterwauler McCrae CDs' );
211 ok( artist1()->delete, 'Successfully deleted 3 CDs' );
212 is( artist1()->count, 0, '0 Caterwauler McCrae CDs' );
213 is( artist2()->count, 2, '3 Random Boy Band CDs' );
214 ok( artist2()->update( { 'artist' => 1 } ) );
215 is( artist2()->count, 0, '0 Random Boy Band CDs' );
216 is( artist1()->count, 2, '2 Caterwauler McCrae CDs' );
218 # test update on multi-column-pk
220 return $schema->resultset("TwoKeys")->search(
222 'artist.name' => { like => '%Boy Band' },
223 'cd.title' => 'Greatest Hits',
225 { join => [qw/artist cd/] }
229 return $schema->resultset("TwoKeys")->search(
230 { 'artist.name' => 'Caterwauler McCrae' },
231 { join => [qw/artist/]}
234 is( tk2()->count, 2, 'TwoKeys count == 2' );
235 is( tk1()->count, 2, 'TwoKeys count == 2' );
236 ok( tk1()->update( { artist => 1 } ) );
237 is( tk1()->count, 0, 'TwoKeys count == 0' );
238 is( tk2()->count, 4, '2 Caterwauler McCrae CDs' );
239 ok( tk2()->delete, 'Successfully deleted 4 CDs' );
240 is(cd_count(), 5, '5 rows in table cd');
241 is(tk_count(), 3, '3 rows in table twokeys');