[dbsrgits/DBIx-Class.git] / t / cdbi-t / 19-set_sql.t

use strict;
use Test::More;

BEGIN {
  eval "use DBIx::Class::CDBICompat;";
  if ($@) {
    plan (skip_all => 'Class::Trigger and DBIx::ContextualFetch required');
    next;
  }
  eval "use DBD::SQLite";
  plan $@ ? (skip_all => 'needs DBD::SQLite for testing') : (tests => 17);
}

use lib 't/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';
}

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";
};

{
	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";
}

#} # end SKIP block
Commit	Line	Data
510ca912	1	use strict;
	2	use Test::More;
	3
	4	BEGIN {
289ba852	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 => 17);
510ca912	12	}
	13
	14	use lib 't/testlib';
	15	use Film;
	16	use 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
	24	my $f1 = Film->create({ title => 'A', director => 'AA', rating => 'PG' });
	25	my $f2 = Film->create({ title => 'B', director => 'BA', rating => 'PG' });
	26	my $f3 = Film->create({ title => 'C', director => 'AA', rating => '15' });
	27	my $f4 = Film->create({ title => 'D', director => 'BA', rating => '18' });
	28	my $f5 = Film->create({ title => 'E', director => 'AA', rating => '18' });
	29
	30	Film->set_sql(
	31	pgs => qq{
	32	SELECT __ESSENTIAL__
	33	FROM __TABLE__
	34	WHERE __TABLE__.rating = 'PG'
	35	ORDER BY title DESC
	36	}
	37	);
	38
	39	{
	40	(my $sth = Film->sql_pgs())->execute;
	41	my @pgs = Film->sth_to_objects($sth);
	42	is @pgs, 2, "Execute our own SQL";
	43	is $pgs[0]->id, $f2->id, "get F2";
	44	is $pgs[1]->id, $f1->id, "and F1";
	45	}
	46
	47	{
	48	my @pgs = Film->search_pgs;
	49	is @pgs, 2, "SQL creates search() method";
	50	is $pgs[0]->id, $f2->id, "get F2";
	51	is $pgs[1]->id, $f1->id, "and F1";
	52	};
	53
	54	Film->set_sql(
	55	rating => qq{
	56	SELECT __ESSENTIAL__
	57	FROM __TABLE__
	58	WHERE rating = ?
	59	ORDER BY title DESC
	60	}
	61	);
	62
	63	{
	64	my @pgs = Film->search_rating('18');
	65	is @pgs, 2, "Can pass parameters to created search()";
	66	is $pgs[0]->id, $f5->id, "F5";
	67	is $pgs[1]->id, $f4->id, "and F4";
	68	};
	69
	70	{
	71	Actor->has_a(film => "Film");
	72	Film->set_sql(
	73	namerate => qq{
	74	SELECT __ESSENTIAL(f)__
	75	FROM __TABLE(=f)__, __TABLE(Actor=a)__
76	WHERE __JOIN(a f)__
77	AND a.name LIKE ?
78	AND f.rating = ?
79	ORDER BY title
80	}
81	);
82
83	my $a1 = Actor->create({ name => "A1", film => $f1 });
84	my $a2 = Actor->create({ name => "A2", film => $f2 });
85	my $a3 = Actor->create({ name => "B1", film => $f1 });
86
87	my @apg = Film->search_namerate("A_", "PG");
88	is @apg, 2, "2 Films with A* that are PG";
89	is $apg[0]->title, "A", "A";
90	is $apg[1]->title, "B", "and B";
91	}
92
93	{ # join in reverse
94	Actor->has_a(film => "Film");
95	Film->set_sql(
96	ratename => qq{
97	SELECT __ESSENTIAL(f)__
98	FROM __TABLE(=f)__, __TABLE(Actor=a)__
99	WHERE __JOIN(f a)__
100	AND f.rating = ?
101	AND a.name LIKE ?
102	ORDER BY title
103	}
104	);
105
106	my @apg = Film->search_ratename(PG => "A_");
107	is @apg, 2, "2 Films with A* that are PG";
108	is $apg[0]->title, "A", "A";
109	is $apg[1]->title, "B", "and B";
110	}
111
12bbb339	112	#} # end SKIP block