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