1use strict; 2use warnings; 3 4use Test::More; 5use lib qw(t/lib); 6use DBICTest; 7use DBIC::SqlMakerTest; 8 9my $schema = DBICTest->init_schema(); 10 11my $orig_debug = $schema->storage->debug; 12 13# test the abstract join => SQL generator 14my $sa = new DBIx::Class::SQLAHacks; 15 16my @j = ( 17 { child => 'person' }, 18 [ { father => 'person' }, { 'father.person_id' => 'child.father_id' }, ], 19 [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ], 20); 21my $match = 'person child JOIN person father ON ( father.person_id = ' 22 . 'child.father_id ) JOIN person mother ON ( mother.person_id ' 23 . '= child.mother_id )' 24 ; 25is_same_sql( 26 $sa->_recurse_from(@j), 27 $match, 28 'join 1 ok' 29); 30 31my @j2 = ( 32 { mother => 'person' }, 33 [ [ { child => 'person' }, 34 [ { father => 'person' }, 35 { 'father.person_id' => 'child.father_id' } 36 ] 37 ], 38 { 'mother.person_id' => 'child.mother_id' } 39 ], 40); 41$match = 'person mother JOIN (person child JOIN person father ON (' 42 . ' father.person_id = child.father_id )) ON ( mother.person_id = ' 43 . 'child.mother_id )' 44 ; 45is_same_sql( 46 $sa->_recurse_from(@j2), 47 $match, 48 'join 2 ok' 49); 50 51 52my @j3 = ( 53 { child => 'person' }, 54 [ { father => 'person', -join_type => 'inner' }, { 'father.person_id' => 'child.father_id' }, ], 55 [ { mother => 'person', -join_type => 'inner' }, { 'mother.person_id' => 'child.mother_id' } ], 56); 57$match = 'person child INNER JOIN person father ON ( father.person_id = ' 58 . 'child.father_id ) INNER JOIN person mother ON ( mother.person_id ' 59 . '= child.mother_id )' 60 ; 61 62is_same_sql( 63 $sa->_recurse_from(@j3), 64 $match, 65 'join 3 (inner join) ok' 66); 67 68my @j4 = ( 69 { mother => 'person' }, 70 [ [ { child => 'person', -join_type => 'left' }, 71 [ { father => 'person', -join_type => 'right' }, 72 { 'father.person_id' => 'child.father_id' } 73 ] 74 ], 75 { 'mother.person_id' => 'child.mother_id' } 76 ], 77); 78$match = 'person mother LEFT JOIN (person child RIGHT JOIN person father ON (' 79 . ' father.person_id = child.father_id )) ON ( mother.person_id = ' 80 . 'child.mother_id )' 81 ; 82is_same_sql( 83 $sa->_recurse_from(@j4), 84 $match, 85 'join 4 (nested joins + join types) ok' 86); 87 88my @j5 = ( 89 { child => 'person' }, 90 [ { father => 'person' }, { 'father.person_id' => \'!= child.father_id' }, ], 91 [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ], 92); 93$match = 'person child JOIN person father ON ( father.person_id != ' 94 . 'child.father_id ) JOIN person mother ON ( mother.person_id ' 95 . '= child.mother_id )' 96 ; 97is_same_sql( 98 $sa->_recurse_from(@j5), 99 $match, 100 'join 5 (SCALAR reference for ON statement) ok' 101); 102 103my @j6 = ( 104 { child => 'person' }, 105 [ { father => 'person' }, { 'father.person_id' => { '!=', '42' } }, ], 106 [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ], 107); 108$match = qr/HASH reference arguments are not supported in JOINS/; 109eval { $sa->_recurse_from(@j6) }; 110like( $@, $match, 'join 6 (HASH reference for ON statement dies) ok' ); 111 112my $rs = $schema->resultset("CD")->search( 113 { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' }, 114 { from => [ { 'me' => 'cd' }, 115 [ 116 { artist => 'artist' }, 117 { 'me.artist' => 'artist.artistid' } 118 ] ] } 119 ); 120 121is( $rs + 0, 1, "Single record in resultset"); 122 123is($rs->first->title, 'Forkful of bees', 'Correct record returned'); 124 125$rs = $schema->resultset("CD")->search( 126 { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' }, 127 { join => 'artist' }); 128 129is( $rs + 0, 1, "Single record in resultset"); 130 131is($rs->first->title, 'Forkful of bees', 'Correct record returned'); 132 133$rs = $schema->resultset("CD")->search( 134 { 'artist.name' => 'We Are Goth', 135 'liner_notes.notes' => 'Kill Yourself!' }, 136 { join => [ qw/artist liner_notes/ ] }); 137 138is( $rs + 0, 1, "Single record in resultset"); 139 140is($rs->first->title, 'Come Be Depressed With Us', 'Correct record returned'); 141 142# when using join attribute, make sure slice()ing all objects has same count as all() 143$rs = $schema->resultset("CD")->search( 144 { 'artist' => 1 }, 145 { join => [qw/artist/], order_by => 'artist.name' } 146); 147is( scalar $rs->all, scalar $rs->slice(0, $rs->count - 1), 'slice() with join has same count as all()' ); 148 149ok(!$rs->slice($rs->count+1000, $rs->count+1002)->count, 150 'Slicing beyond end of rs returns a zero count'); 151 152$rs = $schema->resultset("Artist")->search( 153 { 'liner_notes.notes' => 'Kill Yourself!' }, 154 { join => { 'cds' => 'liner_notes' } }); 155 156is( $rs->count, 1, "Single record in resultset"); 157 158is($rs->first->name, 'We Are Goth', 'Correct record returned'); 159 160 161{ 162 $schema->populate('Artist', [ 163 [ qw/artistid name/ ], 164 [ 4, 'Another Boy Band' ], 165 ]); 166 $schema->populate('CD', [ 167 [ qw/cdid artist title year/ ], 168 [ 6, 2, "Greatest Hits", 2001 ], 169 [ 7, 4, "Greatest Hits", 2005 ], 170 [ 8, 4, "BoyBandBlues", 2008 ], 171 ]); 172 $schema->populate('TwoKeys', [ 173 [ qw/artist cd/ ], 174 [ 2, 4 ], 175 [ 2, 6 ], 176 [ 4, 7 ], 177 [ 4, 8 ], 178 ]); 179 180 sub cd_count { 181 return $schema->resultset("CD")->count; 182 } 183 sub tk_count { 184 return $schema->resultset("TwoKeys")->count; 185 } 186 187 is(cd_count(), 8, '8 rows in table cd'); 188 is(tk_count(), 7, '7 rows in table twokeys'); 189 190 sub artist1 { 191 return $schema->resultset("CD")->search( 192 { 'artist.name' => 'Caterwauler McCrae' }, 193 { join => [qw/artist/]} 194 ); 195 } 196 sub artist2 { 197 return $schema->resultset("CD")->search( 198 { 'artist.name' => 'Random Boy Band' }, 199 { join => [qw/artist/]} 200 ); 201 } 202 203 is( artist1()->count, 3, '3 Caterwauler McCrae CDs' ); 204 ok( artist1()->delete, 'Successfully deleted 3 CDs' ); 205 is( artist1()->count, 0, '0 Caterwauler McCrae CDs' ); 206 is( artist2()->count, 2, '3 Random Boy Band CDs' ); 207 ok( artist2()->update( { 'artist' => 1 } ) ); 208 is( artist2()->count, 0, '0 Random Boy Band CDs' ); 209 is( artist1()->count, 2, '2 Caterwauler McCrae CDs' ); 210 211 # test update on multi-column-pk 212 sub tk1 { 213 return $schema->resultset("TwoKeys")->search( 214 { 215 'artist.name' => { like => '%Boy Band' }, 216 'cd.title' => 'Greatest Hits', 217 }, 218 { join => [qw/artist cd/] } 219 ); 220 } 221 sub tk2 { 222 return $schema->resultset("TwoKeys")->search( 223 { 'artist.name' => 'Caterwauler McCrae' }, 224 { join => [qw/artist/]} 225 ); 226 } 227 is( tk2()->count, 2, 'TwoKeys count == 2' ); 228 is( tk1()->count, 2, 'TwoKeys count == 2' ); 229 ok( tk1()->update( { artist => 1 } ) ); 230 is( tk1()->count, 0, 'TwoKeys count == 0' ); 231 is( tk2()->count, 4, '2 Caterwauler McCrae CDs' ); 232 ok( tk2()->delete, 'Successfully deleted 4 CDs' ); 233 is(cd_count(), 5, '5 rows in table cd'); 234 is(tk_count(), 3, '3 rows in table twokeys'); 235} 236 237done_testing; 238