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