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