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