6 eval "use DBD::SQLite";
8 ? ( skip_all => 'needs DBD::SQLite for testing' )
16 # test the abstract join => SQL generator
17 my $sa = new DBIC::SQL::Abstract;
20 { child => 'person' },
21 [ { father => 'person' }, { 'father.person_id' => 'child.father_id' }, ],
22 [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ],
24 my $match = 'person child JOIN person father ON ( father.person_id = '
25 . 'child.father_id ) JOIN person mother ON ( mother.person_id '
26 . '= child.mother_id )'
28 is( $sa->_recurse_from(@j), $match, 'join 1 ok' );
31 { mother => 'person' },
32 [ [ { child => 'person' },
33 [ { father => 'person' },
34 { 'father.person_id' => 'child.father_id' }
37 { 'mother.person_id' => 'child.mother_id' }
40 $match = 'person mother JOIN (person child JOIN person father ON ('
41 . ' father.person_id = child.father_id )) ON ( mother.person_id = '
44 is( $sa->_recurse_from(@j2), $match, 'join 2 ok' );
47 { child => 'person' },
48 [ { father => 'person', -join_type => 'inner' }, { 'father.person_id' => 'child.father_id' }, ],
49 [ { mother => 'person', -join_type => 'inner' }, { 'mother.person_id' => 'child.mother_id' } ],
51 $match = 'person child INNER JOIN person father ON ( father.person_id = '
52 . 'child.father_id ) INNER JOIN person mother ON ( mother.person_id '
53 . '= child.mother_id )'
56 is( $sa->_recurse_from(@j3), $match, 'join 3 (inner join) ok');
58 my $rs = DBICTest::CD->search(
59 { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' },
60 { from => [ { 'me' => 'cd' },
62 { artist => 'artist' },
63 { 'me.artist' => 'artist.artistid' }
67 cmp_ok( $rs->count, '==', 1, "Single record in resultset");
69 is($rs->first->title, 'Forkful of bees', 'Correct record returned');
71 $rs = DBICTest::CD->search(
72 { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' },
73 { join => 'artist' });
75 cmp_ok( $rs->count, '==', 1, "Single record in resultset");
77 is($rs->first->title, 'Forkful of bees', 'Correct record returned');
79 $rs = DBICTest::CD->search(
80 { 'artist.name' => 'We Are Goth',
81 'liner_notes.notes' => 'Kill Yourself!' },
82 { join => [ qw/artist liner_notes/ ] });
84 cmp_ok( $rs->count, '==', 1, "Single record in resultset");
86 is($rs->first->title, 'Come Be Depressed With Us', 'Correct record returned');
88 $rs = DBICTest::Artist->search(
89 { 'liner_notes.notes' => 'Kill Yourself!' },
90 { join => { 'cds' => 'liner_notes' } });
92 cmp_ok( $rs->count, '==', 1, "Single record in resultset");
94 is($rs->first->name, 'We Are Goth', 'Correct record returned');
96 DBICTest::Schema::CD->add_relationship(
97 artist => 'DBICTest::Schema::Artist',
98 { 'foreign.artistid' => 'self.artist' },
99 { accessor => 'filter' },
102 DBICTest::Schema::CD->add_relationship(
103 liner_notes => 'DBICTest::Schema::LinerNotes',
104 { 'foreign.liner_id' => 'self.cdid' },
105 { join_type => 'LEFT', accessor => 'single' });
107 $rs = DBICTest::CD->search(
108 { 'artist.name' => 'Caterwauler McCrae' },
109 { prefetch => [ qw/artist liner_notes/ ],
110 order_by => 'me.cdid' });
112 cmp_ok($rs->count, '==', 3, 'Correct number of records returned');
114 # start test for prefetch SELECT count
115 unlink 't/var/dbic.trace' if -e 't/var/dbic.trace';
116 DBI->trace(1, 't/var/dbic.trace');
120 is($cd[0]->title, 'Spoonful of bees', 'First record returned ok');
122 ok(!exists $cd[0]->{_relationship_data}{liner_notes}, 'No prefetch for NULL LEFT JOIN');
124 is($cd[1]->{_relationship_data}{liner_notes}->notes, 'Buy Whiskey!', 'Prefetch for present LEFT JOIN');
126 is($cd[2]->{_inflated_column}{artist}->name, 'Caterwauler McCrae', 'Prefetch on parent object ok');
131 my $trace = IO::File->new('t/var/dbic.trace', '<')
132 or die "Unable to read trace file";
134 $selects++ if /SELECT/;
137 unlink 't/var/dbic.trace';
138 is($selects, 1, 'prefetch ran only 1 select statement');
140 my ($artist) = DBICTest::Artist->search({ 'cds.year' => 2001 },
141 { order_by => 'artistid DESC', join => 'cds' });
143 is($artist->name, 'Random Boy Band', "Join search by object ok");
145 my @cds = DBICTest::CD->search({ 'liner_notes.notes' => 'Buy Merch!' },
146 { join => 'liner_notes' });
148 cmp_ok(scalar @cds, '==', 1, "Single CD retrieved via might_have");
150 is($cds[0]->title, "Generic Manufactured Singles", "Correct CD retrieved");
152 my @artists = DBICTest::Artist->search({ 'tags.tag' => 'Shiny' },
153 { join => { 'cds' => 'tags' } });
155 cmp_ok( @artists, '==', 2, "two-join search ok" );