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