1use strict; 2use Test::More; 3 4BEGIN { 5 eval "use DBIx::Class::CDBICompat;"; 6 if ($@) { 7 plan (skip_all => 'Class::Trigger and DBIx::ContextualFetch required'); 8 next; 9 } 10 eval "use DBD::SQLite"; 11 plan $@ ? (skip_all => 'needs DBD::SQLite for testing') : (tests => 20); 12} 13 14use lib 't/cdbi/testlib'; 15use Film; 16use Actor; 17 18{ # Check __ESSENTIAL__ expansion (RT#13038) 19 my @cols = Film->columns('Essential'); 20 is_deeply \@cols, ['title'], "1 Column in essential"; 21 is +Film->transform_sql('__ESSENTIAL__'), 'title', '__ESSENTIAL__ expansion'; 22 23 # This provides a more interesting test 24 Film->columns(Essential => qw(title rating)); 25 is +Film->transform_sql('__ESSENTIAL__'), 'title, rating', 26 'multi-col __ESSENTIAL__ expansion'; 27} 28 29my $f1 = Film->create({ title => 'A', director => 'AA', rating => 'PG' }); 30my $f2 = Film->create({ title => 'B', director => 'BA', rating => 'PG' }); 31my $f3 = Film->create({ title => 'C', director => 'AA', rating => '15' }); 32my $f4 = Film->create({ title => 'D', director => 'BA', rating => '18' }); 33my $f5 = Film->create({ title => 'E', director => 'AA', rating => '18' }); 34 35Film->set_sql( 36 pgs => qq{ 37 SELECT __ESSENTIAL__ 38 FROM __TABLE__ 39 WHERE __TABLE__.rating = 'PG' 40 ORDER BY title DESC 41} 42); 43 44{ 45 (my $sth = Film->sql_pgs())->execute; 46 my @pgs = Film->sth_to_objects($sth); 47 is @pgs, 2, "Execute our own SQL"; 48 is $pgs[0]->id, $f2->id, "get F2"; 49 is $pgs[1]->id, $f1->id, "and F1"; 50} 51 52{ 53 my @pgs = Film->search_pgs; 54 is @pgs, 2, "SQL creates search() method"; 55 is $pgs[0]->id, $f2->id, "get F2"; 56 is $pgs[1]->id, $f1->id, "and F1"; 57}; 58 59Film->set_sql( 60 rating => qq{ 61 SELECT __ESSENTIAL__ 62 FROM __TABLE__ 63 WHERE rating = ? 64 ORDER BY title DESC 65} 66); 67 68{ 69 my @pgs = Film->search_rating('18'); 70 is @pgs, 2, "Can pass parameters to created search()"; 71 is $pgs[0]->id, $f5->id, "F5"; 72 is $pgs[1]->id, $f4->id, "and F4"; 73}; 74 75{ 76 Film->set_sql( 77 by_id => qq{ 78 SELECT __ESSENTIAL__ 79 FROM __TABLE__ 80 WHERE __IDENTIFIER__ 81 } 82 ); 83 84 my $film = Film->retrieve_all->first; 85 my @found = Film->search_by_id($film->id); 86 is @found, 1; 87 is $found[0]->id, $film->id; 88} 89 90 91{ 92 Actor->has_a(film => "Film"); 93 Film->set_sql( 94 namerate => qq{ 95 SELECT __ESSENTIAL(f)__ 96 FROM __TABLE(=f)__, __TABLE(Actor=a)__ 97 WHERE __JOIN(a f)__ 98 AND a.name LIKE ? 99 AND f.rating = ? 100 ORDER BY title 101 } 102 ); 103 104 my $a1 = Actor->create({ name => "A1", film => $f1 }); 105 my $a2 = Actor->create({ name => "A2", film => $f2 }); 106 my $a3 = Actor->create({ name => "B1", film => $f1 }); 107 108 my @apg = Film->search_namerate("A_", "PG"); 109 is @apg, 2, "2 Films with A* that are PG"; 110 is $apg[0]->title, "A", "A"; 111 is $apg[1]->title, "B", "and B"; 112} 113 114{ # join in reverse 115 Actor->has_a(film => "Film"); 116 Film->set_sql( 117 ratename => qq{ 118 SELECT __ESSENTIAL(f)__ 119 FROM __TABLE(=f)__, __TABLE(Actor=a)__ 120 WHERE __JOIN(f a)__ 121 AND f.rating = ? 122 AND a.name LIKE ? 123 ORDER BY title 124 } 125 ); 126 127 my @apg = Film->search_ratename(PG => "A_"); 128 is @apg, 2, "2 Films with A* that are PG"; 129 is $apg[0]->title, "A", "A"; 130 is $apg[1]->title, "B", "and B"; 131} 132 133