Debian Codegraph @

t/cdbi-t-ocache/19-set_sql.t

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