Commit | Line | Data |
70350518 |
1 | use strict; |
8273e845 |
2 | use warnings; |
70350518 |
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 | # test the abstract join => SQL generator |
d5dedbd6 |
14 | my $sa = new DBIx::Class::SQLMaker; |
0567538f |
15 | |
16 | my @j = ( |
17 | { child => 'person' }, |
18 | [ { father => 'person' }, { 'father.person_id' => 'child.father_id' }, ], |
19 | [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ], |
20 | ); |
21 | my $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 | ; |
af6aac2d |
25 | is_same_sql( |
26 | $sa->_recurse_from(@j), |
27 | $match, |
9b459129 |
28 | 'join 1 ok' |
29 | ); |
0567538f |
30 | |
31 | my @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 | ; |
af6aac2d |
45 | is_same_sql( |
46 | $sa->_recurse_from(@j2), |
47 | $match, |
9b459129 |
48 | 'join 2 ok' |
49 | ); |
50 | |
0567538f |
51 | |
52 | my @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 | |
af6aac2d |
62 | is_same_sql( |
63 | $sa->_recurse_from(@j3), |
64 | $match, |
9b459129 |
65 | 'join 3 (inner join) ok' |
66 | ); |
0567538f |
67 | |
ca7b9fdf |
68 | my @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 | ; |
af6aac2d |
82 | is_same_sql( |
83 | $sa->_recurse_from(@j4), |
84 | $match, |
9b459129 |
85 | 'join 4 (nested joins + join types) ok' |
86 | ); |
ca7b9fdf |
87 | |
635b9634 |
88 | my @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 | ; |
af6aac2d |
97 | is_same_sql( |
98 | $sa->_recurse_from(@j5), |
99 | $match, |
9b459129 |
100 | 'join 5 (SCALAR reference for ON statement) ok' |
101 | ); |
635b9634 |
102 | |
f9db5527 |
103 | my $rs = $schema->resultset("CD")->search( |
0567538f |
104 | { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' }, |
105 | { from => [ { 'me' => 'cd' }, |
106 | [ |
107 | { artist => 'artist' }, |
108 | { 'me.artist' => 'artist.artistid' } |
109 | ] ] } |
110 | ); |
111 | |
cb9b5b23 |
112 | is( $rs + 0, 1, "Single record in resultset"); |
0567538f |
113 | |
114 | is($rs->first->title, 'Forkful of bees', 'Correct record returned'); |
115 | |
f9db5527 |
116 | $rs = $schema->resultset("CD")->search( |
0567538f |
117 | { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' }, |
118 | { join => 'artist' }); |
119 | |
cb9b5b23 |
120 | is( $rs + 0, 1, "Single record in resultset"); |
0567538f |
121 | |
122 | is($rs->first->title, 'Forkful of bees', 'Correct record returned'); |
123 | |
f9db5527 |
124 | $rs = $schema->resultset("CD")->search( |
0567538f |
125 | { 'artist.name' => 'We Are Goth', |
126 | 'liner_notes.notes' => 'Kill Yourself!' }, |
127 | { join => [ qw/artist liner_notes/ ] }); |
128 | |
cb9b5b23 |
129 | is( $rs + 0, 1, "Single record in resultset"); |
0567538f |
130 | |
131 | is($rs->first->title, 'Come Be Depressed With Us', 'Correct record returned'); |
132 | |
8fe164b9 |
133 | # when using join attribute, make sure slice()ing all objects has same count as all() |
f9db5527 |
134 | $rs = $schema->resultset("CD")->search( |
8fe164b9 |
135 | { 'artist' => 1 }, |
136 | { join => [qw/artist/], order_by => 'artist.name' } |
137 | ); |
cb9b5b23 |
138 | is( scalar $rs->all, scalar $rs->slice(0, $rs->count - 1), 'slice() with join has same count as all()' ); |
8fe164b9 |
139 | |
2bd9c7c0 |
140 | ok(!$rs->slice($rs->count+1000, $rs->count+1002)->count, |
141 | 'Slicing beyond end of rs returns a zero count'); |
142 | |
f9db5527 |
143 | $rs = $schema->resultset("Artist")->search( |
0567538f |
144 | { 'liner_notes.notes' => 'Kill Yourself!' }, |
145 | { join => { 'cds' => 'liner_notes' } }); |
146 | |
cb9b5b23 |
147 | is( $rs->count, 1, "Single record in resultset"); |
0567538f |
148 | |
149 | is($rs->first->name, 'We Are Goth', 'Correct record returned'); |
150 | |
9f2d17e9 |
151 | |
0f6fc705 |
152 | { |
153 | $schema->populate('Artist', [ |
154 | [ qw/artistid name/ ], |
155 | [ 4, 'Another Boy Band' ], |
156 | ]); |
157 | $schema->populate('CD', [ |
158 | [ qw/cdid artist title year/ ], |
159 | [ 6, 2, "Greatest Hits", 2001 ], |
160 | [ 7, 4, "Greatest Hits", 2005 ], |
161 | [ 8, 4, "BoyBandBlues", 2008 ], |
162 | ]); |
163 | $schema->populate('TwoKeys', [ |
164 | [ qw/artist cd/ ], |
165 | [ 2, 4 ], |
166 | [ 2, 6 ], |
167 | [ 4, 7 ], |
168 | [ 4, 8 ], |
169 | ]); |
8273e845 |
170 | |
65d35121 |
171 | my $cd_count = sub { $schema->resultset("CD")->count }; |
172 | my $tk_count = sub { $schema->resultset("TwoKeys")->count }; |
173 | |
174 | is($cd_count->(), 8, '8 rows in table cd'); |
175 | is($tk_count->(), 7, '7 rows in table twokeys'); |
176 | |
177 | my $artist1_rs = $schema->resultset("CD")->search( |
178 | { 'artist.name' => 'Caterwauler McCrae' }, |
179 | { join => [qw/artist/]} |
180 | ); |
181 | |
182 | my $artist2_rs = $schema->resultset("CD")->search( |
183 | { 'artist.name' => 'Random Boy Band' }, |
184 | { join => [qw/artist/]} |
185 | ); |
186 | |
187 | is( $artist1_rs->count, 3, '3 Caterwauler McCrae CDs' ); |
188 | ok( $artist1_rs->delete, 'Successfully deleted 3 CDs' ); |
189 | is( $artist1_rs->count, 0, '0 Caterwauler McCrae CDs' ); |
190 | is( $artist2_rs->count, 2, '3 Random Boy Band CDs' ); |
191 | ok( $artist2_rs->update( { 'artist' => 1 } ) ); |
192 | is( $artist2_rs->count, 0, '0 Random Boy Band CDs' ); |
193 | is( $artist1_rs->count, 2, '2 Caterwauler McCrae CDs' ); |
0f6fc705 |
194 | |
195 | # test update on multi-column-pk |
65d35121 |
196 | my $tk1_rs = $schema->resultset("TwoKeys")->search( |
197 | { |
198 | 'artist.name' => { like => '%Boy Band' }, |
199 | 'cd.title' => 'Greatest Hits', |
200 | }, |
201 | { join => [qw/artist cd/] } |
202 | ); |
203 | |
204 | my $tk2_rs = $schema->resultset("TwoKeys")->search( |
205 | { 'artist.name' => 'Caterwauler McCrae' }, |
206 | { join => [qw/artist/]} |
207 | ); |
208 | |
209 | is( $tk2_rs->count, 2, 'TwoKeys count == 2' ); |
210 | is( $tk1_rs->count, 2, 'TwoKeys count == 2' ); |
211 | ok( $tk1_rs->update( { artist => 1 } ) ); |
212 | is( $tk1_rs->count, 0, 'TwoKeys count == 0' ); |
213 | is( $tk2_rs->count, 4, '2 Caterwauler McCrae CDs' ); |
214 | ok( $tk2_rs->delete, 'Successfully deleted 4 CDs' ); |
215 | is($cd_count->(), 5, '5 rows in table cd'); |
216 | is($tk_count->(), 3, '3 rows in table twokeys'); |
0f6fc705 |
217 | } |
56166f36 |
218 | |
219 | done_testing; |