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