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