ken/DBIx-Custom at 1adbc52a2f206aeb42688aa39aad409d777fb8fa ・ Gitprep

DBIx-Custom / t / common.t /

Newer ■ ■ ■ ■ ■ ■ ■ ■ ■ ■ Older

3792 lines | 122.258kb

added common test executing ... bd35c82 Yuki Kimoto authored on 2011-08-07	1	use Test::More;
	2	use strict;
	3	use warnings;
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	4	use Encode qw/encode_utf8/;
cleanup test 41a84b1 Yuki Kimoto authored on 2011-08-10	5	use FindBin;
	6	use lib "$FindBin::Bin/common";
	7
added common test executing ... bd35c82 Yuki Kimoto authored on 2011-08-07	8	my $dbi;
	9
	10	plan skip_all => $ENV{DBIX_CUSTOM_SKIP_MESSAGE} \|\| 'common.t is always skipped'
	11	unless $ENV{DBIX_CUSTOM_TEST_RUN}
	12	&& eval { $dbi = DBIx::Custom->connect; 1 };
	13
	14	plan 'no_plan';
	15
test cleanup e5d6e22 Yuki Kimoto authored on 2011-08-10	16	use MyDBI1;
	17	{
	18	package MyDBI4;
	19
	20	use strict;
	21	use warnings;
	22
	23	use base 'DBIx::Custom';
	24
	25	sub connect {
	26	my $self = shift->SUPER::connect(@_);
	27
	28	$self->include_model(
	29	MyModel2 => [
	30	'book',
	31	{class => 'Company', name => 'company'}
	32	]
	33	);
	34	}
	35
	36	package MyModel2::Base1;
	37
	38	use strict;
	39	use warnings;
	40
	41	use base 'DBIx::Custom::Model';
	42
	43	package MyModel2::book;
	44
	45	use strict;
	46	use warnings;
	47
	48	use base 'MyModel2::Base1';
	49
	50	sub insert {
	51	my ($self, $param) = @_;
	52
	53	return $self->SUPER::insert(param => $param);
	54	}
	55
	56	sub list { shift->select; }
	57
	58	package MyModel2::Company;
	59
	60	use strict;
	61	use warnings;
	62
	63	use base 'MyModel2::Base1';
	64
	65	sub insert {
	66	my ($self, $param) = @_;
	67
	68	return $self->SUPER::insert(param => $param);
	69	}
	70
	71	sub list { shift->select; }
	72	}
	73	{
	74	package MyDBI5;
	75
	76	use strict;
	77	use warnings;
	78
	79	use base 'DBIx::Custom';
	80
	81	sub connect {
	82	my $self = shift->SUPER::connect(@_);
	83
	84	$self->include_model('MyModel4');
	85	}
	86	}
	87	{
	88	package MyDBI6;
	89
	90	use base 'DBIx::Custom';
	91
	92	sub connect {
	93	my $self = shift->SUPER::connect(@_);
	94
	95	$self->include_model('MyModel5');
	96
	97	return $self;
	98	}
	99	}
	100	{
	101	package MyDBI7;
	102
	103	use base 'DBIx::Custom';
	104
	105	sub connect {
	106	my $self = shift->SUPER::connect(@_);
	107
	108	$self->include_model('MyModel6');
	109
	110
	111	return $self;
	112	}
	113	}
	114	{
	115	package MyDBI8;
	116
	117	use base 'DBIx::Custom';
	118
	119	sub connect {
	120	my $self = shift->SUPER::connect(@_);
	121
	122	$self->include_model('MyModel7');
	123
	124	return $self;
	125	}
	126	}
	127
	128	{
	129	package MyDBI9;
	130
	131	use base 'DBIx::Custom';
	132
	133	sub connect {
	134	my $self = shift->SUPER::connect(@_);
	135
cleanup test ee32d7d Yuki Kimoto authored on 2011-08-10	136	$self->include_model('MyModel8');
test cleanup e5d6e22 Yuki Kimoto authored on 2011-08-10	137
	138	return $self;
	139	}
	140	}
	141
cleanup test de329a9 Yuki Kimoto authored on 2011-08-08	142	$SIG{__WARN__} = sub { warn $_[0] unless $_[0] =~ /DEPRECATED/};
	143	sub test { print "# $_[0]\n" }
	144
added common test executing ... bd35c82 Yuki Kimoto authored on 2011-08-07	145	# Constant
	146	my $create_table1 = $dbi->create_table1;
cleanup test 058b0fd Yuki Kimoto authored on 2011-08-08	147	my $create_table1_2 = $dbi->create_table1_2;
test cleanup 723ba3d Yuki Kimoto authored on 2011-08-10	148	my $create_table1_type = $dbi->create_table1_type;
test cleanup 0419ba5 Yuki Kimoto authored on 2011-08-10	149	my $create_table1_highperformance = $dbi->create_table1_highperformance;
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	150	my $create_table2 = $dbi->create_table2;
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	151	my $create_table2_2 = $dbi->create_table2_2;
	152	my $create_table3 = $dbi->create_table3;
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	153	my $create_table_reserved = $dbi->create_table_reserved;
cleanup test bf179eb Yuki Kimoto authored on 2011-08-10	154	my $q = substr($dbi->quote, 0, 1);
	155	my $p = substr($dbi->quote, 1, 1) \|\| $q;
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	156	my $date_typename = $dbi->date_typename;
	157	my $time_typename = $dbi->time_typename;
	158	my $datetime_typename = $dbi->datetime_typename;
	159	my $date_datatype = $dbi->date_datatype;
	160	my $time_datatype = $dbi->time_datatype;
	161	my $datetime_datatype = $dbi->datetime_datatype;
added common test executing ... bd35c82 Yuki Kimoto authored on 2011-08-07	162
	163	# Variable
cleanup test de329a9 Yuki Kimoto authored on 2011-08-08	164	# Variables
	165	my $builder;
	166	my $datas;
	167	my $sth;
	168	my $source;
	169	my @sources;
	170	my $select_source;
	171	my $insert_source;
	172	my $update_source;
	173	my $param;
	174	my $params;
	175	my $sql;
	176	my $result;
	177	my $row;
	178	my @rows;
	179	my $rows;
	180	my $query;
	181	my @queries;
	182	my $select_query;
	183	my $insert_query;
	184	my $update_query;
	185	my $ret_val;
	186	my $infos;
added common test executing ... bd35c82 Yuki Kimoto authored on 2011-08-07	187	my $model;
cleanup test de329a9 Yuki Kimoto authored on 2011-08-08	188	my $model2;
	189	my $where;
	190	my $update_param;
	191	my $insert_param;
	192	my $join;
cleanup test a86c18b Yuki Kimoto authored on 2011-08-10	193	my $binary;
added common test executing ... bd35c82 Yuki Kimoto authored on 2011-08-07	194
	195	# Drop table
	196	eval { $dbi->execute('drop table table1') };
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	197
	198	test 'type_rule into';
	199	$dbi = DBIx::Custom->connect;
	200	eval { $dbi->execute('drop table table1') };
	201	$dbi->execute($create_table1_type);
	202	$dbi->type_rule(
	203	into1 => {
	204	$date_typename => sub { '2010-' . $_[0] }
	205	}
	206	);
	207	$dbi->insert({key1 => '01-01'}, table => 'table1');
	208	$result = $dbi->select(table => 'table1');
	209	is($result->one->{key1}, '2010-01-01');
	210
	211	$dbi = DBIx::Custom->connect;
	212	eval { $dbi->execute('drop table table1') };
	213	$dbi->execute($create_table1_type);
	214	$dbi->type_rule(
	215	into1 => [
	216	[$date_typename, $datetime_typename] => sub {
	217	my $value = shift;
	218	$value =~ s/02/03/g;
	219	return $value;
	220	}
	221	]
	222	);
	223	$dbi->insert({key1 => '2010-01-02', key2 => '2010-01-01 01:01:02'}, table => 'table1');
	224	$result = $dbi->select(table => 'table1');
	225	$row = $result->one;
	226	is($row->{key1}, '2010-01-03');
	227	is($row->{key2}, '2010-01-01 01:01:03');
	228
	229	$dbi = DBIx::Custom->connect;
	230	eval { $dbi->execute('drop table table1') };
	231	$dbi->execute($create_table1_type);
	232	$dbi->insert({key1 => '2010-01-03', key2 => '2010-01-01 01:01:03'}, table => 'table1');
	233	$dbi->type_rule(
	234	into1 => [
	235	[$date_typename, $datetime_typename] => sub {
	236	my $value = shift;
	237	$value =~ s/02/03/g;
	238	return $value;
	239	}
	240	]
	241	);
	242	$result = $dbi->execute(
	243	"select * from table1 where key1 = :key1 and key2 = :table1.key2;",
	244	param => {key1 => '2010-01-03', 'table1.key2' => '2010-01-01 01:01:02'}
	245	);
	246	$row = $result->one;
	247	is($row->{key1}, '2010-01-03');
	248	is($row->{key2}, '2010-01-01 01:01:03');
	249
	250	$dbi = DBIx::Custom->connect;
	251	eval { $dbi->execute('drop table table1') };
	252	$dbi->execute($create_table1_type);
	253	$dbi->insert({key1 => '2010-01-03', key2 => '2010-01-01 01:01:03'}, table => 'table1');
	254	$dbi->type_rule(
	255	into1 => [
	256	[$date_typename, $datetime_typename] => sub {
	257	my $value = shift;
	258	$value =~ s/02/03/g;
	259	return $value;
	260	}
	261	]
	262	);
	263	$result = $dbi->execute(
	264	"select * from table1 where key1 = :key1 and key2 = :table1.key2;",
	265	param => {key1 => '2010-01-02', 'table1.key2' => '2010-01-01 01:01:02'},
	266	table => 'table1'
	267	);
	268	$row = $result->one;
	269	is($row->{key1}, '2010-01-03');
	270	is($row->{key2}, '2010-01-01 01:01:03');
	271
	272	$dbi = DBIx::Custom->connect;
	273	eval { $dbi->execute('drop table table1') };
	274	$dbi->execute($create_table1_type);
	275	$dbi->register_filter(convert => sub {
	276	my $value = shift;
	277	$value =~ s/02/03/;
	278	return $value;
	279	});
	280	$dbi->type_rule(
	281	from1 => {
	282	$date_datatype => 'convert',
	283	},
	284	into1 => {
	285	$date_typename => 'convert',
	286	}
	287	);
	288	$dbi->insert({key1 => '2010-02-02'}, table => 'table1');
	289	$result = $dbi->select(table => 'table1');
	290	is($result->fetch->[0], '2010-03-03');
	291
	292
	293
	294
	295
	296
	297
	298
	299
	300
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	301	test 'type_rule and filter order';
	302	$dbi = DBIx::Custom->connect;
	303	eval { $dbi->execute('drop table table1') };
	304	$dbi->execute($create_table1_type);
	305	$dbi->type_rule(
	306	into1 => {
	307	$date_typename => sub { my $v = shift; $v =~ s/4/5/; return $v }
	308	},
	309	into2 => {
	310	$date_typename => sub { my $v = shift; $v =~ s/5/6/; return $v }
	311	},
	312	from1 => {
	313	$date_datatype => sub { my $v = shift; $v =~ s/6/7/; return $v }
	314	},
	315	from2 => {
	316	$date_datatype => sub { my $v = shift; $v =~ s/7/8/; return $v }
	317	}
	318	);
	319	$dbi->insert({key1 => '2010-01-03'},
	320	table => 'table1', filter => {key1 => sub { my $v = shift; $v =~ s/3/4/; return $v }});
	321	$result = $dbi->select(table => 'table1');
	322	$result->filter(key1 => sub { my $v = shift; $v =~ s/8/9/; return $v });
	323	is($result->fetch_first->[0], '2010-01-09');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	324
	325
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	326	$dbi = DBIx::Custom->connect;
	327	eval { $dbi->execute('drop table table1') };
	328	$dbi->execute($create_table1_type);
	329	$dbi->type_rule(
	330	from1 => {
	331	$date_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v }
	332	},
	333	from2 => {
	334	$date_datatype => sub { my $v = shift; $v =~ s/4/5/; return $v }
	335	},
	336	);
	337	$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
	338	$result = $dbi->select(table => 'table1');
	339	$result->type_rule(
	340	from1 => {
	341	$date_datatype => sub { my $v = shift; $v =~ s/3/6/; return $v }
	342	},
	343	from2 => {
	344	$date_datatype => sub { my $v = shift; $v =~ s/6/8/; return $v }
	345	}
	346	);
	347	$result->filter(key1 => sub { my $v = shift; $v =~ s/8/9/; return $v });
	348	is($result->fetch_first->[0], '2010-01-09');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	349
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	350	test 'type_rule_off';
	351	$dbi = DBIx::Custom->connect;
	352	eval { $dbi->execute('drop table table1') };
	353	$dbi->execute($create_table1_type);
	354	$dbi->type_rule(
	355	from1 => {
	356	$date_datatype => sub { my $v = shift; $v =~ s/3/5/; return $v }
	357	},
	358	into1 => {
	359	$date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
	360	}
	361	);
	362	$dbi->insert({key1 => '2010-01-03'}, table => 'table1', type_rule_off => 1);
	363	$result = $dbi->select(table => 'table1', type_rule_off => 1);
	364	is($result->type_rule_off->fetch->[0], '2010-01-03');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	365
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	366	$dbi = DBIx::Custom->connect;
	367	eval { $dbi->execute('drop table table1') };
	368	$dbi->execute($create_table1_type);
	369	$dbi->type_rule(
	370	from1 => {
	371	$date_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v }
	372	},
	373	into1 => {
	374	$date_typename => sub { my $v = shift; $v =~ s/3/5/; return $v }
	375	}
	376	);
	377	$dbi->insert({key1 => '2010-01-03'}, table => 'table1', type_rule_off => 1);
	378	$result = $dbi->select(table => 'table1', type_rule_off => 1);
	379	is($result->one->{key1}, '2010-01-04');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	380
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	381	$dbi = DBIx::Custom->connect;
	382	eval { $dbi->execute('drop table table1') };
	383	$dbi->execute($create_table1_type);
	384	$dbi->type_rule(
	385	from1 => {
	386	$date_datatype => sub { my $v = shift; $v =~ s/4/5/; return $v }
	387	},
	388	into1 => {
	389	$date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
	390	}
	391	);
	392	$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
	393	$result = $dbi->select(table => 'table1');
	394	is($result->one->{key1}, '2010-01-05');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	395
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	396	$dbi = DBIx::Custom->connect;
	397	eval { $dbi->execute('drop table table1') };
	398	$dbi->execute($create_table1_type);
	399	$dbi->type_rule(
	400	from1 => {
	401	$date_datatype => sub { my $v = shift; $v =~ s/4/5/; return $v }
	402	},
	403	into1 => {
	404	$date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
	405	}
	406	);
	407	$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
	408	$result = $dbi->select(table => 'table1');
	409	is($result->fetch->[0], '2010-01-05');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	410
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	411	$dbi = DBIx::Custom->connect;
	412	eval { $dbi->execute('drop table table1') };
	413	$dbi->execute($create_table1_type);
	414	$dbi->register_filter(ppp => sub { my $v = shift; $v =~ s/3/4/; return $v });
	415	$dbi->type_rule(
	416	into1 => {
	417	$date_typename => 'ppp'
	418	}
	419	);
	420	$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
	421	$result = $dbi->select(table => 'table1');
	422	is($result->one->{key1}, '2010-01-04');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	423
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	424	eval{$dbi->type_rule(
	425	into1 => {
	426	$date_typename => 'pp'
	427	}
	428	)};
	429	like($@, qr/not registered/);
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	430
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	431	$dbi = DBIx::Custom->connect;
	432	eval { $dbi->execute('drop table table1') };
	433	$dbi->execute($create_table1_type);
	434	eval {
	435	$dbi->type_rule(
	436	from1 => {
	437	Date => sub { $_[0] * 2 },
	438	}
	439	);
	440	};
	441	like($@, qr/lower/);
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	442
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	443	eval {
	444	$dbi->type_rule(
	445	into1 => {
	446	Date => sub { $_[0] * 2 },
	447	}
	448	);
	449	};
	450	like($@, qr/lower/);
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	451
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	452	$dbi = DBIx::Custom->connect;
	453	eval { $dbi->execute('drop table table1') };
	454	$dbi->execute($create_table1_type);
	455	$dbi->type_rule(
	456	from1 => {
	457	$date_datatype => sub { my $v = shift; $v =~ s/4/5/; return $v }
	458	},
	459	into1 => {
	460	$date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
	461	}
	462	);
	463	$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
	464	$result = $dbi->select(table => 'table1');
	465	$result->type_rule_off;
	466	is($result->one->{key1}, '2010-01-04');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	467
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	468	$dbi = DBIx::Custom->connect;
	469	eval { $dbi->execute('drop table table1') };
	470	$dbi->execute($create_table1_type);
	471	$dbi->type_rule(
	472	from1 => {
	473	$date_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v },
	474	$datetime_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v }
	475	},
	476	);
	477	$dbi->insert({key1 => '2010-01-03', key2 => '2010-01-01 01:01:03'}, table => 'table1');
	478	$result = $dbi->select(table => 'table1');
	479	$result->type_rule(
	480	from1 => {
	481	$date_datatype => sub { my $v = shift; $v =~ s/3/5/; return $v }
	482	}
	483	);
	484	$row = $result->one;
	485	is($row->{key1}, '2010-01-05');
	486	is($row->{key2}, '2010-01-01 01:01:03');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	487
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	488	$result = $dbi->select(table => 'table1');
	489	$result->type_rule(
	490	from1 => {
	491	$date_datatype => sub { my $v = shift; $v =~ s/3/5/; return $v }
	492	}
	493	);
	494	$row = $result->one;
	495	is($row->{key1}, '2010-01-05');
	496	is($row->{key2}, '2010-01-01 01:01:03');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	497
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	498	$result = $dbi->select(table => 'table1');
	499	$result->type_rule(
	500	from1 => {
	501	$date_datatype => sub { my $v = shift; $v =~ s/3/5/; return $v }
	502	}
	503	);
	504	$row = $result->one;
	505	is($row->{key1}, '2010-01-05');
	506	is($row->{key2}, '2010-01-01 01:01:03');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	507
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	508	$result = $dbi->select(table => 'table1');
	509	$result->type_rule(
	510	from1 => [$date_datatype => sub { my $v = shift; $v =~ s/3/5/; return $v }]
	511	);
	512	$row = $result->one;
	513	is($row->{key1}, '2010-01-05');
	514	is($row->{key2}, '2010-01-01 01:01:03');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	515
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	516	$dbi->register_filter(five => sub { my $v = shift; $v =~ s/3/5/; return $v });
	517	$result = $dbi->select(table => 'table1');
	518	$result->type_rule(
	519	from1 => [$date_datatype => 'five']
	520	);
	521	$row = $result->one;
	522	is($row->{key1}, '2010-01-05');
	523	is($row->{key2}, '2010-01-01 01:01:03');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	524
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	525	$result = $dbi->select(table => 'table1');
	526	$result->type_rule(
	527	from1 => [$date_datatype => undef]
	528	);
	529	$row = $result->one;
	530	is($row->{key1}, '2010-01-03');
	531	is($row->{key2}, '2010-01-01 01:01:03');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	532
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	533	$dbi = DBIx::Custom->connect;
	534	eval { $dbi->execute('drop table table1') };
	535	$dbi->execute($create_table1_type);
	536	$dbi->type_rule(
	537	from1 => {
	538	$date_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v },
	539	},
	540	);
	541	$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
	542	$result = $dbi->select(table => 'table1');
	543	$result->filter(key1 => sub { my $v = shift; $v =~ s/4/5/; return $v });
	544	is($result->one->{key1}, '2010-01-05');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	545
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	546	$dbi = DBIx::Custom->connect;
	547	eval { $dbi->execute('drop table table1') };
	548	$dbi->execute($create_table1_type);
	549	$dbi->type_rule(
	550	from1 => {
	551	$date_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v }
	552	},
	553	);
	554	$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
	555	$result = $dbi->select(table => 'table1');
	556	$result->filter(key1 => sub { my $v = shift; $v =~ s/4/5/; return $v });
	557	is($result->fetch->[0], '2010-01-05');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	558
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	559	$dbi = DBIx::Custom->connect;
	560	eval { $dbi->execute('drop table table1') };
	561	$dbi->execute($create_table1_type);
	562	$dbi->type_rule(
	563	into1 => {
	564	$date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
	565	},
	566	into2 => {
	567	$date_typename => sub { my $v = shift; $v =~ s/3/5/; return $v }
	568	},
	569	from1 => {
	570	$date_datatype => sub { my $v = shift; $v =~ s/3/6/; return $v }
	571	},
	572	from2 => {
	573	$date_datatype => sub { my $v = shift; $v =~ s/(3\|6)/7/; return $v }
	574	}
	575	);
	576	$dbi->insert({key1 => '2010-01-03'}, table => 'table1', type_rule_off => 1);
	577	$result = $dbi->select(table => 'table1');
	578	is($result->type_rule_off->fetch_first->[0], '2010-01-03');
	579	$result = $dbi->select(table => 'table1');
	580	is($result->type_rule_on->fetch_first->[0], '2010-01-07');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	581
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	582	$dbi = DBIx::Custom->connect;
	583	eval { $dbi->execute('drop table table1') };
	584	$dbi->execute($create_table1_type);
	585	$dbi->type_rule(
	586	into1 => {
	587	$date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
	588	},
	589	into2 => {
	590	$date_typename => sub { my $v = shift; $v =~ s/3/5/; return $v }
	591	},
	592	from1 => {
	593	$date_datatype => sub { my $v = shift; $v =~ s/(3\|5)/6/; return $v }
	594	},
	595	from2 => {
	596	$date_datatype => sub { my $v = shift; $v =~ s/6/7/; return $v }
	597	}
	598	);
	599	$dbi->insert({key1 => '2010-01-03'}, table => 'table1', type_rule1_off => 1);
	600	$result = $dbi->select(table => 'table1');
	601	is($result->type_rule1_off->fetch_first->[0], '2010-01-05');
	602	$result = $dbi->select(table => 'table1');
	603	is($result->type_rule1_on->fetch_first->[0], '2010-01-07');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	604
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	605	$dbi = DBIx::Custom->connect;
	606	eval { $dbi->execute('drop table table1') };
	607	$dbi->execute($create_table1_type);
	608	$dbi->type_rule(
	609	into1 => {
	610	$date_typename => sub { my $v = shift; $v =~ s/3/5/; return $v }
	611	},
	612	into2 => {
	613	$date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
	614	},
	615	from1 => {
	616	$date_datatype => sub { my $v = shift; $v =~ s/5/6/; return $v }
	617	},
	618	from2 => {
	619	$date_datatype => sub { my $v = shift; $v =~ s/(3\|6)/7/; return $v }
	620	}
	621	);
	622	$dbi->insert({key1 => '2010-01-03'}, table => 'table1', type_rule2_off => 1);
	623	$result = $dbi->select(table => 'table1');
	624	is($result->type_rule2_off->fetch_first->[0], '2010-01-06');
	625	$result = $dbi->select(table => 'table1');
	626	is($result->type_rule2_on->fetch_first->[0], '2010-01-07');
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	627
	628
added common test executing ... bd35c82 Yuki Kimoto authored on 2011-08-07	629
	630	# Create table
test cleanup 1adbc52 Yuki Kimoto authored on 2011-08-10	631	$dbi = DBIx::Custom->connect;
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	632	eval { $dbi->execute('drop table table1') };
added common test executing ... bd35c82 Yuki Kimoto authored on 2011-08-07	633	$dbi->execute($create_table1);
	634	$model = $dbi->create_model(table => 'table1');
	635	$model->insert({key1 => 1, key2 => 2});
	636	is_deeply($model->select->all, [{key1 => 1, key2 => 2}]);
	637
cleanup test de329a9 Yuki Kimoto authored on 2011-08-08	638	test 'DBIx::Custom::Result test';
	639	$dbi->delete_all(table => 'table1');
	640	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	641	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	642	$source = "select key1, key2 from table1";
	643	$query = $dbi->create_query($source);
	644	$result = $dbi->execute($query);
	645
	646	@rows = ();
	647	while (my $row = $result->fetch) {
	648	push @rows, [@$row];
	649	}
	650	is_deeply(\@rows, [[1, 2], [3, 4]], "fetch");
	651
	652	$result = $dbi->execute($query);
	653	@rows = ();
	654	while (my $row = $result->fetch_hash) {
	655	push @rows, {%$row};
	656	}
	657	is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "fetch_hash");
	658
	659	$result = $dbi->execute($query);
	660	$rows = $result->fetch_all;
	661	is_deeply($rows, [[1, 2], [3, 4]], "fetch_all");
	662
	663	$result = $dbi->execute($query);
	664	$rows = $result->fetch_hash_all;
	665	is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "all");
	666
	667	test 'Insert query return value';
	668	$source = "insert into table1 {insert_param key1 key2}";
	669	$query = $dbi->execute($source, {}, query => 1);
	670	$ret_val = $dbi->execute($query, param => {key1 => 1, key2 => 2});
	671	ok($ret_val);
	672
	673	test 'Direct query';
	674	$dbi->delete_all(table => 'table1');
	675	$insert_source = "insert into table1 {insert_param key1 key2}";
	676	$dbi->execute($insert_source, param => {key1 => 1, key2 => 2});
	677	$result = $dbi->execute('select * from table1;');
	678	$rows = $result->all;
	679	is_deeply($rows, [{key1 => 1, key2 => 2}]);
	680
	681	test 'Filter basic';
	682	$dbi->delete_all(table => 'table1');
	683	$dbi->register_filter(twice => sub { $_[0] * 2},
	684	three_times => sub { $_[0] * 3});
	685
	686	$insert_source = "insert into table1 {insert_param key1 key2};";
	687	$insert_query = $dbi->execute($insert_source, {}, query => 1);
	688	$insert_query->filter({key1 => 'twice'});
	689	$dbi->execute($insert_query, param => {key1 => 1, key2 => 2});
	690	$result = $dbi->execute('select * from table1;');
	691	$rows = $result->filter({key2 => 'three_times'})->all;
	692	is_deeply($rows, [{key1 => 2, key2 => 6}], "filter fetch_filter");
	693
	694	test 'Filter in';
	695	$dbi->delete_all(table => 'table1');
	696	$insert_source = "insert into table1 {insert_param key1 key2};";
	697	$insert_query = $dbi->execute($insert_source, {}, query => 1);
	698	$dbi->execute($insert_query, param => {key1 => 2, key2 => 4});
	699	$select_source = "select * from table1 where {in table1.key1 2} and {in table1.key2 2}";
	700	$select_query = $dbi->execute($select_source,{}, query => 1);
	701	$select_query->filter({'table1.key1' => 'twice'});
	702	$result = $dbi->execute($select_query, param => {'table1.key1' => [1,5], 'table1.key2' => [2,4]});
	703	$rows = $result->all;
	704	is_deeply($rows, [{key1 => 2, key2 => 4}], "filter");
	705
cleanup test 08c3c54 Yuki Kimoto authored on 2011-08-08	706	test 'DBIx::Custom::SQLTemplate basic tag';
	707	$dbi->execute('drop table table1');
cleanup test 058b0fd Yuki Kimoto authored on 2011-08-08	708	$dbi->execute($create_table1_2);
cleanup test 08c3c54 Yuki Kimoto authored on 2011-08-08	709	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	710	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	711
	712	$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";
	713	$query = $dbi->execute($source, {}, query => 1);
	714	$result = $dbi->execute($query, param => {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
	715	$rows = $result->all;
	716	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");
	717
	718	$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";
	719	$query = $dbi->execute($source, {}, query => 1);
	720	$result = $dbi->execute($query, {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
	721	$rows = $result->all;
	722	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");
	723
	724	$source = "select * from table1 where {<= key1} and {like key2};";
	725	$query = $dbi->execute($source, {}, query => 1);
	726	$result = $dbi->execute($query, param => {key1 => 1, key2 => '%2%'});
	727	$rows = $result->all;
	728	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag2");
	729
	730	test 'DIB::Custom::SQLTemplate in tag';
	731	$dbi->execute('drop table table1');
cleanup test 058b0fd Yuki Kimoto authored on 2011-08-08	732	$dbi->execute($create_table1_2);
cleanup test 08c3c54 Yuki Kimoto authored on 2011-08-08	733	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	734	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	735
	736	$source = "select * from table1 where {in key1 2};";
	737	$query = $dbi->execute($source, {}, query => 1);
	738	$result = $dbi->execute($query, param => {key1 => [9, 1]});
	739	$rows = $result->all;
	740	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic");
	741
	742	test 'DBIx::Custom::SQLTemplate insert tag';
	743	$dbi->delete_all(table => 'table1');
	744	$insert_source = 'insert into table1 {insert_param key1 key2 key3 key4 key5}';
	745	$dbi->execute($insert_source, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	746
	747	$result = $dbi->execute('select * from table1;');
	748	$rows = $result->all;
	749	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic");
	750
	751	test 'DBIx::Custom::SQLTemplate update tag';
	752	$dbi->delete_all(table => 'table1');
	753	$insert_source = "insert into table1 {insert_param key1 key2 key3 key4 key5}";
	754	$dbi->execute($insert_source, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	755	$dbi->execute($insert_source, param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	756
	757	$update_source = 'update table1 {update_param key1 key2 key3 key4} where {= key5}';
	758	$dbi->execute($update_source, param => {key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5});
	759
	760	$result = $dbi->execute('select * from table1 order by key1;');
	761	$rows = $result->all;
	762	is_deeply($rows, [{key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5},
	763	{key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}], "basic");
	764
cleanup test 058b0fd Yuki Kimoto authored on 2011-08-08	765	test 'Named placeholder';
	766	$dbi->execute('drop table table1');
	767	$dbi->execute($create_table1_2);
	768	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	769	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	770
	771	$source = "select * from table1 where key1 = :key1 and key2 = :key2";
	772	$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
	773	$rows = $result->all;
	774	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
	775
	776	$source = "select * from table1 where key1 = \n:key1\n and key2 = :key2";
	777	$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
	778	$rows = $result->all;
	779	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
	780
	781	$source = "select * from table1 where key1 = :key1 or key1 = :key1";
	782	$result = $dbi->execute($source, param => {key1 => [1, 2]});
	783	$rows = $result->all;
	784	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
	785
	786	$source = "select * from table1 where key1 = :table1.key1 and key2 = :table1.key2";
	787	$result = $dbi->execute(
	788	$source,
	789	param => {'table1.key1' => 1, 'table1.key2' => 1},
	790	filter => {'table1.key2' => sub { $_[0] * 2 }}
	791	);
	792	$rows = $result->all;
	793	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
	794
	795	$dbi->execute('drop table table1');
	796	$dbi->execute($create_table1);
	797	$dbi->insert(table => 'table1', param => {key1 => '2011-10-14 12:19:18', key2 => 2});
	798	$source = "select * from table1 where key1 = '2011-10-14 12:19:18' and key2 = :key2";
	799	$result = $dbi->execute(
	800	$source,
	801	param => {'key2' => 2},
	802	);
	803
	804	$rows = $result->all;
	805	is_deeply($rows, [{key1 => '2011-10-14 12:19:18', key2 => 2}]);
	806
	807	$dbi->delete_all(table => 'table1');
	808	$dbi->insert(table => 'table1', param => {key1 => 'a:b c:d', key2 => 2});
	809	$source = "select * from table1 where key1 = 'a\\:b c\\:d' and key2 = :key2";
	810	$result = $dbi->execute(
	811	$source,
	812	param => {'key2' => 2},
	813	);
	814	$rows = $result->all;
	815	is_deeply($rows, [{key1 => 'a:b c:d', key2 => 2}]);
	816
test cleanup fcea70e Yuki Kimoto authored on 2011-08-10	817	test 'Error case';
	818	eval {DBIx::Custom->connect(dsn => 'dbi:SQLit')};
	819	ok($@, "connect error");
	820
	821	eval{$dbi->execute("{p }", {}, query => 1)};
	822	ok($@, "create_query invalid SQL template");
added EXPERIMENTAL DBIx::Cus... 38f4ef1 Yuki Kimoto authored on 2011-08-09	823
cleanup test 1c9417d Yuki Kimoto authored on 2011-08-10	824	test 'insert';
cleanup test bf179eb Yuki Kimoto authored on 2011-08-10	825	eval { $dbi->execute('drop table table1') };
	826	$dbi->execute($create_table1);
cleanup test 1c9417d Yuki Kimoto authored on 2011-08-10	827	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	828	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	829	$result = $dbi->execute('select * from table1;');
	830	$rows = $result->all;
	831	is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
	832
	833	$dbi->execute('delete from table1');
	834	$dbi->register_filter(
	835	twice => sub { $_[0] * 2 },
	836	three_times => sub { $_[0] * 3 }
	837	);
	838	$dbi->default_bind_filter('twice');
	839	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => 'three_times'});
	840	$result = $dbi->execute('select * from table1;');
	841	$rows = $result->all;
	842	is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
	843	$dbi->default_bind_filter(undef);
	844
cleanup test bf179eb Yuki Kimoto authored on 2011-08-10	845	$dbi->execute('drop table table1');
	846	$dbi->execute($create_table1);
cleanup test 1c9417d Yuki Kimoto authored on 2011-08-10	847	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, append => ' ');
	848	$rows = $dbi->select(table => 'table1')->all;
	849	is_deeply($rows, [{key1 => 1, key2 => 2}], 'insert append');
	850
	851	eval{$dbi->insert(table => 'table1', noexist => 1)};
	852	like($@, qr/noexist/, "invalid");
	853
	854	eval{$dbi->insert(table => 'table', param => {';' => 1})};
	855	like($@, qr/safety/);
	856
cleanup test bf179eb Yuki Kimoto authored on 2011-08-10	857	eval { $dbi->execute("drop table ${q}table$p") };
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	858	$dbi->execute($create_table_reserved);
cleanup test 1c9417d Yuki Kimoto authored on 2011-08-10	859	$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
	860	$dbi->insert(table => 'table', param => {select => 1});
cleanup test bf179eb Yuki Kimoto authored on 2011-08-10	861	$result = $dbi->execute("select * from ${q}table$p");
cleanup test 1c9417d Yuki Kimoto authored on 2011-08-10	862	$rows = $result->all;
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	863	is_deeply($rows, [{select => 2, update => undef}], "reserved word");
cleanup test 1c9417d Yuki Kimoto authored on 2011-08-10	864
cleanup test bf179eb Yuki Kimoto authored on 2011-08-10	865	eval { $dbi->execute('drop table table1') };
cleanup test 1c9417d Yuki Kimoto authored on 2011-08-10	866	$dbi->execute($create_table1);
	867	$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
	868	$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
	869	$result = $dbi->execute('select * from table1;');
	870	$rows = $result->all;
	871	is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
	872
cleanup test bf179eb Yuki Kimoto authored on 2011-08-10	873	eval { $dbi->execute('drop table table1') };
cleanup test 1c9417d Yuki Kimoto authored on 2011-08-10	874	$dbi->execute($create_table1);
	875	$dbi->insert(table => 'table1', param => {key1 => \"'1'", key2 => 2});
	876	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	877	$result = $dbi->execute('select * from table1;');
	878	$rows = $result->all;
	879	is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
	880
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	881	test 'update';
	882	eval { $dbi->execute('drop table table1') };
	883	$dbi->execute($create_table1_2);
	884	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	885	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	886	$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1});
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	887	$result = $dbi->execute('select * from table1 order by key1;');
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	888	$rows = $result->all;
	889	is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
	890	{key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}],
	891	"basic");
	892
	893	$dbi->execute("delete from table1");
	894	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	895	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	896	$dbi->update(table => 'table1', param => {key2 => 12}, where => {key2 => 2, key3 => 3});
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	897	$result = $dbi->execute('select * from table1 order by key1;');
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	898	$rows = $result->all;
	899	is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
	900	{key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}],
	901	"update key same as search key");
	902
	903	$dbi->update(table => 'table1', param => {key2 => [12]}, where => {key2 => 2, key3 => 3});
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	904	$result = $dbi->execute('select * from table1 order by key1;');
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	905	$rows = $result->all;
	906	is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
	907	{key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}],
	908	"update key same as search key : param is array ref");
	909
	910	$dbi->execute("delete from table1");
	911	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	912	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	913	$dbi->register_filter(twice => sub { $_[0] * 2 });
	914	$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1},
	915	filter => {key2 => sub { $_[0] * 2 }});
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	916	$result = $dbi->execute('select * from table1 order by key1;');
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	917	$rows = $result->all;
	918	is_deeply($rows, [{key1 => 1, key2 => 22, key3 => 3, key4 => 4, key5 => 5},
	919	{key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}],
	920	"filter");
	921
	922	$result = $dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1}, append => ' ');
	923
	924	eval{$dbi->update(table => 'table1', where => {key1 => 1}, noexist => 1)};
	925	like($@, qr/noexist/, "invalid");
	926
	927	eval{$dbi->update(table => 'table1')};
	928	like($@, qr/where/, "not contain where");
	929
	930	eval { $dbi->execute('drop table table1') };
	931	$dbi->execute($create_table1);
	932	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	933	$where = $dbi->where;
	934	$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
	935	$where->param({key1 => 1, key2 => 2});
	936	$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
	937	$result = $dbi->select(table => 'table1');
	938	is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
	939
	940	eval { $dbi->execute('drop table table1') };
	941	$dbi->execute($create_table1);
	942	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	943	$dbi->update(
	944	table => 'table1',
	945	param => {key1 => 3},
	946	where => [
	947	['and', 'key1 = :key1', 'key2 = :key2'],
	948	{key1 => 1, key2 => 2}
	949	]
	950	);
	951	$result = $dbi->select(table => 'table1');
	952	is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
	953
	954	eval { $dbi->execute('drop table table1') };
	955	$dbi->execute($create_table1);
	956	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	957	$where = $dbi->where;
	958	$where->clause(['and', 'key2 = :key2']);
	959	$where->param({key2 => 2});
	960	$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
	961	$result = $dbi->select(table => 'table1');
	962	is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
	963
	964	eval{$dbi->update(table => 'table1', param => {';' => 1})};
	965	like($@, qr/safety/);
	966
	967	eval{$dbi->update(table => 'table1', param => {'key1' => 1}, where => {';' => 1})};
	968	like($@, qr/safety/);
	969
	970	eval { $dbi->execute('drop table table1') };
	971	eval { $dbi->execute("drop table ${q}table$p") };
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	972	$dbi->execute($create_table_reserved);
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	973	$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
	974	$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
	975	$dbi->insert(table => 'table', param => {select => 1});
	976	$dbi->update(table => 'table', where => {select => 1}, param => {update => 2});
	977	$result = $dbi->execute("select * from ${q}table$p");
	978	$rows = $result->all;
	979	is_deeply($rows, [{select => 2, update => 6}], "reserved word");
	980
	981	eval {$dbi->update_all(table => 'table', param => {';' => 2}) };
	982	like($@, qr/safety/);
	983
	984	eval { $dbi->execute("drop table ${q}table$p") };
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	985	$dbi->execute($create_table_reserved);
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	986	$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
	987	$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
	988	$dbi->insert(table => 'table', param => {select => 1});
	989	$dbi->update(table => 'table', where => {'table.select' => 1}, param => {update => 2});
	990	$result = $dbi->execute("select * from ${q}table$p");
	991	$rows = $result->all;
	992	is_deeply($rows, [{select => 2, update => 6}], "reserved word");
	993
	994	eval { $dbi->execute('drop table table1') };
	995	$dbi->execute($create_table1_2);
	996	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	997	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	998	$dbi->update({key2 => 11}, table => 'table1', where => {key1 => 1});
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	999	$result = $dbi->execute('select * from table1 order by key1;');
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	1000	$rows = $result->all;
	1001	is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
	1002	{key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}],
	1003	"basic");
	1004
	1005	eval { $dbi->execute('drop table table1') };
	1006	$dbi->execute($create_table1_2);
	1007	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	1008	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	1009	$dbi->update(table => 'table1', param => {key2 => \"'11'"}, where => {key1 => 1});
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	1010	$result = $dbi->execute('select * from table1 order by key1;');
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	1011	$rows = $result->all;
	1012	is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
	1013	{key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}],
	1014	"basic");
	1015
	1016	test 'update_all';
	1017	eval { $dbi->execute('drop table table1') };
	1018	$dbi->execute($create_table1_2);
	1019	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	1020	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	1021	$dbi->register_filter(twice => sub { $_[0] * 2 });
	1022	$dbi->update_all(table => 'table1', param => {key2 => 10}, filter => {key2 => 'twice'});
	1023	$result = $dbi->execute('select * from table1;');
	1024	$rows = $result->all;
	1025	is_deeply($rows, [{key1 => 1, key2 => 20, key3 => 3, key4 => 4, key5 => 5},
	1026	{key1 => 6, key2 => 20, key3 => 8, key4 => 9, key5 => 10}],
	1027	"filter");
	1028
	1029
	1030	test 'delete';
	1031	eval { $dbi->execute('drop table table1') };
	1032	$dbi->execute($create_table1);
	1033	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1034	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	1035	$dbi->delete(table => 'table1', where => {key1 => 1});
	1036	$result = $dbi->execute('select * from table1;');
	1037	$rows = $result->all;
	1038	is_deeply($rows, [{key1 => 3, key2 => 4}], "basic");
	1039
	1040	$dbi->execute("delete from table1;");
	1041	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1042	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	1043	$dbi->register_filter(twice => sub { $_[0] * 2 });
	1044	$dbi->delete(table => 'table1', where => {key2 => 1}, filter => {key2 => 'twice'});
	1045	$result = $dbi->execute('select * from table1;');
	1046	$rows = $result->all;
	1047	is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
	1048
	1049	$dbi->delete(table => 'table1', where => {key1 => 1}, append => ' ');
	1050
	1051	$dbi->delete_all(table => 'table1');
	1052	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1053	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	1054	$dbi->delete(table => 'table1', where => {key1 => 1, key2 => 2});
	1055	$rows = $dbi->select(table => 'table1')->all;
	1056	is_deeply($rows, [{key1 => 3, key2 => 4}], "delete multi key");
	1057
	1058	eval{$dbi->delete(table => 'table1', where => {key1 => 1}, noexist => 1)};
	1059	like($@, qr/noexist/, "invalid");
	1060
	1061	eval { $dbi->execute('drop table table1') };
	1062	$dbi->execute($create_table1);
	1063	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1064	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	1065	$where = $dbi->where;
	1066	$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
	1067	$where->param({ke1 => 1, key2 => 2});
	1068	$dbi->delete(table => 'table1', where => $where);
	1069	$result = $dbi->select(table => 'table1');
	1070	is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
	1071
	1072	eval { $dbi->execute('drop table table1') };
	1073	$dbi->execute($create_table1);
	1074	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1075	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	1076	$dbi->delete(
	1077	table => 'table1',
	1078	where => [
	1079	['and', 'key1 = :key1', 'key2 = :key2'],
	1080	{ke1 => 1, key2 => 2}
	1081	]
	1082	);
	1083	$result = $dbi->select(table => 'table1');
	1084	is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
	1085
	1086	eval { $dbi->execute('drop table table1') };
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	1087	$dbi->execute($create_table1);
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	1088	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1089	$dbi->delete(table => 'table1', where => {key1 => 1}, prefix => ' ');
	1090	$result = $dbi->execute('select * from table1;');
	1091	$rows = $result->all;
	1092	is_deeply($rows, [], "basic");
	1093
	1094	test 'delete error';
	1095	eval { $dbi->execute('drop table table1') };
	1096	$dbi->execute($create_table1);
	1097	eval{$dbi->delete(table => 'table1')};
	1098	like($@, qr/"where" must be specified/,
	1099	"where key-value pairs not specified");
	1100
	1101	eval{$dbi->delete(table => 'table1', where => {';' => 1})};
	1102	like($@, qr/safety/);
	1103
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	1104	$dbi = undef;
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	1105	$dbi = DBIx::Custom->connect;
	1106	eval { $dbi->execute("drop table ${q}table$p") };
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	1107	$dbi->execute($create_table_reserved);
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	1108	$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
	1109	$dbi->insert(table => 'table', param => {select => 1});
	1110	$dbi->delete(table => 'table', where => {select => 1});
	1111	$result = $dbi->execute("select * from ${q}table$p");
	1112	$rows = $result->all;
	1113	is_deeply($rows, [], "reserved word");
	1114
	1115	test 'delete_all';
	1116	eval { $dbi->execute('drop table table1') };
	1117	$dbi->execute($create_table1);
	1118	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1119	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	1120	$dbi->delete_all(table => 'table1');
	1121	$result = $dbi->execute('select * from table1;');
	1122	$rows = $result->all;
	1123	is_deeply($rows, [], "basic");
	1124
	1125
	1126	test 'select';
	1127	eval { $dbi->execute('drop table table1') };
	1128	$dbi->execute($create_table1);
	1129	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1130	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	1131	$rows = $dbi->select(table => 'table1')->all;
	1132	is_deeply($rows, [{key1 => 1, key2 => 2},
	1133	{key1 => 3, key2 => 4}], "table");
	1134
	1135	$rows = $dbi->select(table => 'table1', column => ['key1'])->all;
	1136	is_deeply($rows, [{key1 => 1}, {key1 => 3}], "table and columns and where key");
	1137
	1138	$rows = $dbi->select(table => 'table1', where => {key1 => 1})->all;
	1139	is_deeply($rows, [{key1 => 1, key2 => 2}], "table and columns and where key");
	1140
	1141	$rows = $dbi->select(table => 'table1', column => ['key1'], where => {key1 => 3})->all;
	1142	is_deeply($rows, [{key1 => 3}], "table and columns and where key");
	1143
	1144	$rows = $dbi->select(table => 'table1', append => "order by key1 desc limit 1")->all;
	1145	is_deeply($rows, [{key1 => 3, key2 => 4}], "append statement");
	1146
	1147	$dbi->register_filter(decrement => sub { $_[0] - 1 });
	1148	$rows = $dbi->select(table => 'table1', where => {key1 => 2}, filter => {key1 => 'decrement'})
	1149	->all;
	1150	is_deeply($rows, [{key1 => 1, key2 => 2}], "filter");
	1151
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	1152	eval { $dbi->execute("drop table table2") };
	1153	$dbi->execute($create_table2);
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	1154	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
	1155	$rows = $dbi->select(
	1156	table => [qw/table1 table2/],
	1157	column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
	1158	where => {'table1.key2' => 2},
	1159	relation => {'table1.key1' => 'table2.key1'}
	1160	)->all;
	1161	is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : exists where");
	1162
	1163	$rows = $dbi->select(
	1164	table => [qw/table1 table2/],
	1165	column => ['table1.key1 as table1_key1', 'table2.key1 as table2_key1', 'key2', 'key3'],
	1166	relation => {'table1.key1' => 'table2.key1'}
	1167	)->all;
	1168	is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : no exists where");
	1169
	1170	eval{$dbi->select(table => 'table1', noexist => 1)};
	1171	like($@, qr/noexist/, "invalid");
	1172
	1173	$dbi = DBIx::Custom->connect;
test cleanup af035c8 Yuki Kimoto authored on 2011-08-10	1174	eval { $dbi->execute("drop table ${q}table$p") };
	1175	$dbi->execute($create_table_reserved);
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	1176	$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
	1177	$dbi->insert(table => 'table', param => {select => 1, update => 2});
	1178	$result = $dbi->select(table => 'table', where => {select => 1});
	1179	$rows = $result->all;
	1180	is_deeply($rows, [{select => 2, update => 2}], "reserved word");
	1181
	1182	test 'fetch filter';
	1183	eval { $dbi->execute('drop table table1') };
	1184	$dbi->register_filter(
	1185	twice => sub { $_[0] * 2 },
	1186	three_times => sub { $_[0] * 3 }
	1187	);
	1188	$dbi->default_fetch_filter('twice');
	1189	$dbi->execute($create_table1);
	1190	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1191	$result = $dbi->select(table => 'table1');
	1192	$result->filter({key1 => 'three_times'});
	1193	$row = $result->one;
	1194	is_deeply($row, {key1 => 3, key2 => 4}, "default_fetch_filter and filter");
	1195
	1196	test 'filters';
	1197	$dbi = DBIx::Custom->new;
	1198
	1199	is($dbi->filters->{decode_utf8}->(encode_utf8('あ')),
	1200	'あ', "decode_utf8");
	1201
	1202	is($dbi->filters->{encode_utf8}->('あ'),
	1203	encode_utf8('あ'), "encode_utf8");
	1204
cleanup test 163ab3d Yuki Kimoto authored on 2011-08-10	1205	test 'transaction1';
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	1206	$dbi = DBIx::Custom->connect;
	1207	eval { $dbi->execute('drop table table1') };
	1208	$dbi->execute($create_table1);
	1209	$dbi->dbh->begin_work;
	1210	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1211	$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
	1212	$dbi->dbh->commit;
	1213	$result = $dbi->select(table => 'table1');
	1214	is_deeply(scalar $result->all, [{key1 => 1, key2 => 2}, {key1 => 2, key2 => 3}],
	1215	"commit");
	1216
	1217	$dbi = DBIx::Custom->connect;
	1218	eval { $dbi->execute('drop table table1') };
	1219	$dbi->execute($create_table1);
	1220	$dbi->dbh->begin_work(0);
	1221	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1222	$dbi->dbh->rollback;
	1223
	1224	$result = $dbi->select(table => 'table1');
	1225	ok(! $result->fetch_first, "rollback");
	1226
	1227	test 'execute';
	1228	eval { $dbi->execute('drop table table1') };
	1229	$dbi->execute($create_table1);
	1230	{
	1231	local $Carp::Verbose = 0;
	1232	eval{$dbi->execute('select * frm table1')};
	1233	like($@, qr/\Qselect * frm table1;/, "fail prepare");
	1234	like($@, qr/\.t /, "fail : not verbose");
	1235	}
	1236	{
	1237	local $Carp::Verbose = 1;
	1238	eval{$dbi->execute('select * frm table1')};
	1239	like($@, qr/Custom.*\.t /s, "fail : verbose");
	1240	}
	1241
	1242	eval{$dbi->execute('select * from table1', no_exists => 1)};
	1243	like($@, qr/wrong/, "invald SQL");
	1244
	1245	$query = $dbi->execute('select * from table1 where key1 = :key1', {}, query => 1);
	1246	$dbi->dbh->disconnect;
	1247	eval{$dbi->execute($query, param => {key1 => {a => 1}})};
	1248	ok($@, "execute fail");
	1249
	1250	{
	1251	local $Carp::Verbose = 0;
	1252	eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
	1253	like($@, qr/\Q.t /, "caller spec : not vebose");
	1254	}
	1255	{
	1256	local $Carp::Verbose = 1;
	1257	eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
	1258	like($@, qr/QueryBuilder.*\.t /s, "caller spec : not vebose");
	1259	}
	1260
	1261
cleanup test 163ab3d Yuki Kimoto authored on 2011-08-10	1262	test 'transaction2';
test cleanup c05af26 Yuki Kimoto authored on 2011-08-10	1263	$dbi = DBIx::Custom->connect;
	1264	eval { $dbi->execute('drop table table1') };
	1265	$dbi->execute($create_table1);
	1266
	1267	$dbi->begin_work;
	1268
	1269	eval {
	1270	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1271	die "Error";
	1272	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	1273	};
	1274
	1275	$dbi->rollback if $@;
	1276
	1277	$result = $dbi->select(table => 'table1');
	1278	$rows = $result->all;
	1279	is_deeply($rows, [], "rollback");
	1280
	1281	$dbi->begin_work;
	1282
	1283	eval {
	1284	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1285	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	1286	};
	1287
	1288	$dbi->commit unless $@;
	1289
	1290	$result = $dbi->select(table => 'table1');
	1291	$rows = $result->all;
	1292	is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "commit");
	1293
	1294	$dbi->dbh->{AutoCommit} = 0;
	1295	eval{ $dbi->begin_work };
	1296	ok($@, "exception");
	1297	$dbi->dbh->{AutoCommit} = 1;
added EXPERIMENTAL DBIx::Cus... 38f4ef1 Yuki Kimoto authored on 2011-08-09	1298
test cleanup 723ba3d Yuki Kimoto authored on 2011-08-10	1299	test 'cache';
	1300	eval { $dbi->execute('drop table table1') };
	1301	$dbi->cache(1);
	1302	$dbi->execute($create_table1);
	1303	$source = 'select * from table1 where key1 = :key1 and key2 = :key2;';
	1304	$dbi->execute($source, {}, query => 1);
	1305	is_deeply($dbi->{_cached}->{$source},
	1306	{sql => "select * from table1 where key1 = ? and key2 = ?;", columns => ['key1', 'key2'], tables => []}, "cache");
	1307
	1308	eval { $dbi->execute('drop table table1') };
	1309	$dbi->execute($create_table1);
	1310	$dbi->{_cached} = {};
	1311	$dbi->cache(0);
	1312	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1313	is(scalar keys %{$dbi->{_cached}}, 0, 'not cache');
	1314
	1315	test 'execute';
	1316	eval { $dbi->execute('drop table table1') };
	1317	$dbi->execute($create_table1);
	1318	{
	1319	local $Carp::Verbose = 0;
	1320	eval{$dbi->execute('select * frm table1')};
	1321	like($@, qr/\Qselect * frm table1;/, "fail prepare");
	1322	like($@, qr/\.t /, "fail : not verbose");
	1323	}
	1324	{
	1325	local $Carp::Verbose = 1;
	1326	eval{$dbi->execute('select * frm table1')};
	1327	like($@, qr/Custom.*\.t /s, "fail : verbose");
	1328	}
	1329
	1330	eval{$dbi->execute('select * from table1', no_exists => 1)};
	1331	like($@, qr/wrong/, "invald SQL");
	1332
	1333	$query = $dbi->execute('select * from table1 where key1 = :key1', {}, query => 1);
	1334	$dbi->dbh->disconnect;
	1335	eval{$dbi->execute($query, param => {key1 => {a => 1}})};
	1336	ok($@, "execute fail");
	1337
	1338	{
	1339	local $Carp::Verbose = 0;
	1340	eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
	1341	like($@, qr/\Q.t /, "caller spec : not vebose");
	1342	}
	1343	{
	1344	local $Carp::Verbose = 1;
	1345	eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
	1346	like($@, qr/QueryBuilder.*\.t /s, "caller spec : not vebose");
	1347	}
	1348
	1349	test 'method';
	1350	$dbi->method(
	1351	one => sub { 1 }
	1352	);
	1353	$dbi->method(
	1354	two => sub { 2 }
	1355	);
	1356	$dbi->method({
	1357	twice => sub {
	1358	my $self = shift;
	1359	return $_[0] * 2;
	1360	}
	1361	});
	1362
	1363	is($dbi->one, 1, "first");
	1364	is($dbi->two, 2, "second");
	1365	is($dbi->twice(5), 10 , "second");
	1366
	1367	eval {$dbi->XXXXXX};
	1368	ok($@, "not exists");
	1369
	1370	test 'out filter';
	1371	$dbi = DBIx::Custom->connect;
	1372	eval { $dbi->execute('drop table table1') };
	1373	$dbi->execute($create_table1);
	1374	$dbi->register_filter(twice => sub { $_[0] * 2 });
	1375	$dbi->register_filter(three_times => sub { $_[0] * 3});
	1376	$dbi->apply_filter(
	1377	'table1', 'key1' => {out => 'twice', in => 'three_times'},
	1378	'key2' => {out => 'three_times', in => 'twice'});
	1379	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1380	$result = $dbi->execute('select * from table1;');
	1381	$row = $result->fetch_hash_first;
	1382	is_deeply($row, {key1 => 2, key2 => 6}, "insert");
	1383	$result = $dbi->select(table => 'table1');
	1384	$row = $result->one;
	1385	is_deeply($row, {key1 => 6, key2 => 12}, "insert");
	1386
	1387	$dbi = DBIx::Custom->connect;
	1388	eval { $dbi->execute('drop table table1') };
	1389	$dbi->execute($create_table1);
	1390	$dbi->register_filter(twice => sub { $_[0] * 2 });
	1391	$dbi->register_filter(three_times => sub { $_[0] * 3});
	1392	$dbi->apply_filter(
	1393	'table1', 'key1' => {out => 'twice', in => 'three_times'},
	1394	'key2' => {out => 'three_times', in => 'twice'});
	1395	$dbi->apply_filter(
	1396	'table1', 'key1' => {out => undef}
	1397	);
	1398	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1399	$result = $dbi->execute('select * from table1;');
	1400	$row = $result->one;
	1401	is_deeply($row, {key1 => 1, key2 => 6}, "insert");
	1402
	1403	$dbi = DBIx::Custom->connect;
	1404	eval { $dbi->execute('drop table table1') };
	1405	$dbi->execute($create_table1);
	1406	$dbi->register_filter(twice => sub { $_[0] * 2 });
	1407	$dbi->apply_filter(
	1408	'table1', 'key1' => {out => 'twice', in => 'twice'}
	1409	);
	1410	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => undef});
	1411	$dbi->update(table => 'table1', param => {key1 => 2}, where => {key2 => 2});
	1412	$result = $dbi->execute('select * from table1;');
	1413	$row = $result->one;
	1414	is_deeply($row, {key1 => 4, key2 => 2}, "update");
	1415
	1416	$dbi = DBIx::Custom->connect;
	1417	eval { $dbi->execute('drop table table1') };
	1418	$dbi->execute($create_table1);
	1419	$dbi->register_filter(twice => sub { $_[0] * 2 });
	1420	$dbi->apply_filter(
	1421	'table1', 'key1' => {out => 'twice', in => 'twice'}
	1422	);
	1423	$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1=> undef});
	1424	$dbi->delete(table => 'table1', where => {key1 => 1});
	1425	$result = $dbi->execute('select * from table1;');
	1426	$rows = $result->all;
	1427	is_deeply($rows, [], "delete");
	1428
	1429	$dbi = DBIx::Custom->connect;
	1430	eval { $dbi->execute('drop table table1') };
	1431	$dbi->execute($create_table1);
	1432	$dbi->register_filter(twice => sub { $_[0] * 2 });
	1433	$dbi->apply_filter(
	1434	'table1', 'key1' => {out => 'twice', in => 'twice'}
	1435	);
	1436	$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
	1437	$result = $dbi->select(table => 'table1', where => {key1 => 1});
	1438	$result->filter({'key2' => 'twice'});
	1439	$rows = $result->all;
	1440	is_deeply($rows, [{key1 => 4, key2 => 4}], "select");
	1441
	1442	$dbi = DBIx::Custom->connect;
	1443	eval { $dbi->execute('drop table table1') };
	1444	$dbi->execute($create_table1);
	1445	$dbi->register_filter(twice => sub { $_[0] * 2 });
	1446	$dbi->apply_filter(
	1447	'table1', 'key1' => {out => 'twice', in => 'twice'}
	1448	);
	1449	$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
	1450	$result = $dbi->execute("select * from table1 where key1 = :key1 and key2 = :key2;",
	1451	param => {key1 => 1, key2 => 2},
	1452	table => ['table1']);
	1453	$rows = $result->all;
	1454	is_deeply($rows, [{key1 => 4, key2 => 2}], "execute");
	1455
	1456	$dbi = DBIx::Custom->connect;
	1457	eval { $dbi->execute('drop table table1') };
	1458	$dbi->execute($create_table1);
	1459	$dbi->register_filter(twice => sub { $_[0] * 2 });
	1460	$dbi->apply_filter(
	1461	'table1', 'key1' => {out => 'twice', in => 'twice'}
	1462	);
	1463	$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
	1464	$result = $dbi->execute("select * from {table table1} where key1 = :key1 and key2 = :key2;",
	1465	param => {key1 => 1, key2 => 2});
	1466	$rows = $result->all;
	1467	is_deeply($rows, [{key1 => 4, key2 => 2}], "execute table tag");
	1468
	1469	$dbi = DBIx::Custom->connect;
	1470	eval { $dbi->execute('drop table table1') };
	1471	eval { $dbi->execute('drop table table2') };
	1472	$dbi->execute($create_table1);
	1473	$dbi->execute($create_table2);
	1474	$dbi->register_filter(twice => sub { $_[0] * 2 });
	1475	$dbi->register_filter(three_times => sub { $_[0] * 3 });
	1476	$dbi->apply_filter(
	1477	'table1', 'key2' => {out => 'twice', in => 'twice'}
	1478	);
	1479	$dbi->apply_filter(
	1480	'table2', 'key3' => {out => 'three_times', in => 'three_times'}
	1481	);
	1482	$dbi->insert(table => 'table1', param => {key1 => 5, key2 => 2}, filter => {key2 => undef});
	1483	$dbi->insert(table => 'table2', param => {key1 => 5, key3 => 6}, filter => {key3 => undef});
	1484	$result = $dbi->select(
	1485	table => ['table1', 'table2'],
	1486	column => ['key2', 'key3'],
	1487	where => {'table1.key2' => 1, 'table2.key3' => 2}, relation => {'table1.key1' => 'table2.key1'});
	1488
	1489	$result->filter({'key2' => 'twice'});
	1490	$rows = $result->all;
	1491	is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join");
	1492
	1493	$result = $dbi->select(
	1494	table => ['table1', 'table2'],
	1495	column => ['key2', 'key3'],
	1496	where => {'key2' => 1, 'key3' => 2}, relation => {'table1.key1' => 'table2.key1'});
	1497
	1498	$result->filter({'key2' => 'twice'});
	1499	$rows = $result->all;
	1500	is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join : omit");
	1501
	1502	test 'each_column';
	1503	$dbi = DBIx::Custom->connect;
	1504	eval { $dbi->execute("drop table ${q}table$p") };
	1505	eval { $dbi->execute('drop table table1') };
	1506	eval { $dbi->execute('drop table table2') };
test cleranup 5e1d777 Yuki Kimoto authored on 2011-08-10	1507	eval { $dbi->execute('drop table table3') };
test cleanup 723ba3d Yuki Kimoto authored on 2011-08-10	1508	$dbi->execute($create_table1_type);
	1509	$dbi->execute($create_table2);
	1510
	1511	$infos = [];
	1512	$dbi->each_column(sub {
	1513	my ($self, $table, $column, $cinfo) = @_;
	1514
	1515	if ($table =~ /^table\d/) {
	1516	my $info = [$table, $column, $cinfo->{COLUMN_NAME}];
	1517	push @$infos, $info;
	1518	}
	1519	});
	1520	$infos = [sort { $a->[0] cmp $b->[0] \|\| $a->[1] cmp $b->[1] } @$infos];
	1521	is_deeply($infos,
	1522	[
	1523	['table1', 'key1', 'key1'],
	1524	['table1', 'key2', 'key2'],
	1525	['table2', 'key1', 'key1'],
	1526	['table2', 'key3', 'key3']
	1527	]
	1528
	1529	);
	1530	test 'each_table';
	1531	$dbi = DBIx::Custom->connect;
	1532	eval { $dbi->execute('drop table table1') };
	1533	eval { $dbi->execute('drop table table2') };
	1534	$dbi->execute($create_table2);
	1535	$dbi->execute($create_table1_type);
	1536
	1537	$infos = [];
	1538	$dbi->each_table(sub {
	1539	my ($self, $table, $table_info) = @_;
	1540
	1541	if ($table =~ /^table\d/) {
	1542	my $info = [$table, $table_info->{TABLE_NAME}];
	1543	push @$infos, $info;
	1544	}
	1545	});
	1546	$infos = [sort { $a->[0] cmp $b->[0] \|\| $a->[1] cmp $b->[1] } @$infos];
	1547	is_deeply($infos,
	1548	[
	1549	['table1', 'table1'],
	1550	['table2', 'table2'],
	1551	]
	1552	);
	1553
	1554	test 'limit';
	1555	$dbi = DBIx::Custom->connect;
	1556	eval { $dbi->execute('drop table table1') };
	1557	$dbi->execute($create_table1);
	1558	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1559	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 4});
	1560	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 6});
	1561	$dbi->register_tag(
	1562	limit => sub {
	1563	my ($count, $offset) = @_;
	1564
	1565	my $s = '';
	1566	$s .= "limit $count";
	1567	$s .= " offset $offset" if defined $offset;
	1568
	1569	return [$s, []];
	1570	}
	1571	);
	1572	$rows = $dbi->select(
	1573	table => 'table1',
	1574	where => {key1 => 1},
	1575	append => "order by key2 {limit 1 0}"
	1576	)->all;
	1577	is_deeply($rows, [{key1 => 1, key2 => 2}]);
	1578	$rows = $dbi->select(
	1579	table => 'table1',
	1580	where => {key1 => 1},
	1581	append => "order by key2 {limit 2 1}"
	1582	)->all;
	1583	is_deeply($rows, [{key1 => 1, key2 => 4},{key1 => 1, key2 => 6}]);
	1584	$rows = $dbi->select(
	1585	table => 'table1',
	1586	where => {key1 => 1},
	1587	append => "order by key2 {limit 1}"
	1588	)->all;
	1589	is_deeply($rows, [{key1 => 1, key2 => 2}]);
	1590
	1591	test 'connect super';
test cleanup e5d6e22 Yuki Kimoto authored on 2011-08-10	1592	$dbi = DBIx::Custom->connect;
test cleanup 723ba3d Yuki Kimoto authored on 2011-08-10	1593	eval { $dbi->execute('drop table table1') };
	1594	$dbi->execute($create_table1);
	1595	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1596	is($dbi->select(table => 'table1')->one->{key1}, 1);
	1597
test cleanup e5d6e22 Yuki Kimoto authored on 2011-08-10	1598	$dbi = DBIx::Custom->new;
test cleanup 723ba3d Yuki Kimoto authored on 2011-08-10	1599	$dbi->connect;
	1600	eval { $dbi->execute('drop table table1') };
	1601	$dbi->execute($create_table1);
	1602	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1603	is($dbi->select(table => 'table1')->one->{key1}, 1);
	1604
test cleanup e5d6e22 Yuki Kimoto authored on 2011-08-10	1605	$dbi = DBIx::Custom->connect;
test cleanup 723ba3d Yuki Kimoto authored on 2011-08-10	1606	eval { $dbi->execute('drop table table1') };
	1607	$dbi->execute($create_table1);
	1608	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1609	is($dbi->select(table => 'table1')->one->{key1}, 1);
	1610
	1611	test 'end_filter';
	1612	$dbi = DBIx::Custom->connect;
	1613	eval { $dbi->execute('drop table table1') };
	1614	$dbi->execute($create_table1);
	1615	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1616	$result = $dbi->select(table => 'table1');
	1617	$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
	1618	$result->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 });
	1619	$row = $result->fetch_first;
	1620	is_deeply($row, [6, 40]);
	1621
	1622	$dbi = DBIx::Custom->connect;
	1623	eval { $dbi->execute('drop table table1') };
	1624	$dbi->execute($create_table1);
	1625	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1626	$result = $dbi->select(table => 'table1');
	1627	$result->filter([qw/key1 key2/] => sub { $_[0] * 2 });
	1628	$result->end_filter([[qw/key1 key2/] => sub { $_[0] * 3 }]);
	1629	$row = $result->fetch_first;
	1630	is_deeply($row, [6, 12]);
	1631
	1632	$dbi = DBIx::Custom->connect;
	1633	eval { $dbi->execute('drop table table1') };
	1634	$dbi->execute($create_table1);
	1635	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1636	$result = $dbi->select(table => 'table1');
	1637	$result->filter([[qw/key1 key2/] => sub { $_[0] * 2 }]);
	1638	$result->end_filter([qw/key1 key2/] => sub { $_[0] * 3 });
	1639	$row = $result->fetch_first;
	1640	is_deeply($row, [6, 12]);
	1641
	1642	$dbi->register_filter(five_times => sub { $_[0] * 5 });
	1643	$result = $dbi->select(table => 'table1');
	1644	$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
	1645	$result->end_filter({key1 => sub { $_[0] * 3 }, key2 => 'five_times' });
	1646	$row = $result->one;
	1647	is_deeply($row, {key1 => 6, key2 => 40});
	1648
	1649	$dbi->register_filter(five_times => sub { $_[0] * 5 });
	1650	$dbi->apply_filter('table1',
	1651	key1 => {end => sub { $_[0] * 3 } },
	1652	key2 => {end => 'five_times'}
	1653	);
	1654	$result = $dbi->select(table => 'table1');
	1655	$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
	1656	$row = $result->one;
	1657	is_deeply($row, {key1 => 6, key2 => 40}, 'apply_filter');
	1658
	1659	$dbi->register_filter(five_times => sub { $_[0] * 5 });
	1660	$dbi->apply_filter('table1',
	1661	key1 => {end => sub { $_[0] * 3 } },
	1662	key2 => {end => 'five_times'}
	1663	);
	1664	$result = $dbi->select(table => 'table1');
	1665	$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
	1666	$result->filter(key1 => undef);
	1667	$result->end_filter(key1 => undef);
	1668	$row = $result->one;
	1669	is_deeply($row, {key1 => 1, key2 => 40}, 'apply_filter overwrite');
	1670
test cleanup b7c037f Yuki Kimoto authored on 2011-08-10	1671	test 'remove_end_filter and remove_filter';
	1672	$dbi = DBIx::Custom->connect;
	1673	eval { $dbi->execute('drop table table1') };
	1674	$dbi->execute($create_table1);
	1675	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1676	$result = $dbi->select(table => 'table1');
	1677	$row = $result
	1678	->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 })
	1679	->remove_filter
	1680	->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 })
	1681	->remove_end_filter
	1682	->fetch_first;
	1683	is_deeply($row, [1, 2]);
	1684
	1685	test 'empty where select';
	1686	$dbi = DBIx::Custom->connect;
	1687	eval { $dbi->execute('drop table table1') };
	1688	$dbi->execute($create_table1);
	1689	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1690	$result = $dbi->select(table => 'table1', where => {});
	1691	$row = $result->one;
	1692	is_deeply($row, {key1 => 1, key2 => 2});
	1693
	1694	test 'select query option';
	1695	$dbi = DBIx::Custom->connect;
	1696	eval { $dbi->execute('drop table table1') };
	1697	$dbi->execute($create_table1);
	1698	$query = $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, query => 1);
	1699	is(ref $query, 'DBIx::Custom::Query');
	1700	$query = $dbi->update(table => 'table1', where => {key1 => 1}, param => {key2 => 2}, query => 1);
	1701	is(ref $query, 'DBIx::Custom::Query');
	1702	$query = $dbi->delete(table => 'table1', where => {key1 => 1}, query => 1);
	1703	is(ref $query, 'DBIx::Custom::Query');
	1704	$query = $dbi->select(table => 'table1', where => {key1 => 1, key2 => 2}, query => 1);
	1705	is(ref $query, 'DBIx::Custom::Query');
	1706
	1707	test 'where';
	1708	$dbi = DBIx::Custom->connect;
	1709	eval { $dbi->execute('drop table table1') };
	1710	$dbi->execute($create_table1);
	1711	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	1712	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	1713	$where = $dbi->where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
	1714	is("$where", "where ( key1 = :key1 and key2 = :key2 )", 'no param');
	1715
	1716	$where = $dbi->where
	1717	->clause(['and', 'key1 = :key1', 'key2 = :key2'])
	1718	->param({key1 => 1});
	1719
	1720	$result = $dbi->select(
	1721	table => 'table1',
	1722	where => $where
	1723	);
	1724	$row = $result->all;
	1725	is_deeply($row, [{key1 => 1, key2 => 2}]);
	1726
	1727	$result = $dbi->select(
	1728	table => 'table1',
	1729	where => [
	1730	['and', 'key1 = :key1', 'key2 = :key2'],
	1731	{key1 => 1}
	1732	]
	1733	);
	1734	$row = $result->all;
	1735	is_deeply($row, [{key1 => 1, key2 => 2}]);
	1736
	1737	$where = $dbi->where
	1738	->clause(['and', 'key1 = :key1', 'key2 = :key2'])
	1739	->param({key1 => 1, key2 => 2});
	1740	$result = $dbi->select(
	1741	table => 'table1',
	1742	where => $where
	1743	);
	1744	$row = $result->all;
	1745	is_deeply($row, [{key1 => 1, key2 => 2}]);
	1746
	1747	$where = $dbi->where
	1748	->clause(['and', 'key1 = :key1', 'key2 = :key2'])
	1749	->param({});
	1750	$result = $dbi->select(
	1751	table => 'table1',
	1752	where => $where,
	1753	);
	1754	$row = $result->all;
	1755	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
	1756
	1757	$where = $dbi->where
	1758	->clause(['and', ['or', 'key1 > :key1', 'key1 < :key1'], 'key2 = :key2'])
	1759	->param({key1 => [0, 3], key2 => 2});
	1760	$result = $dbi->select(
	1761	table => 'table1',
	1762	where => $where,
	1763	);
	1764	$row = $result->all;
	1765	is_deeply($row, [{key1 => 1, key2 => 2}]);
	1766
	1767	$where = $dbi->where;
	1768	$result = $dbi->select(
	1769	table => 'table1',
	1770	where => $where
	1771	);
	1772	$row = $result->all;
	1773	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
	1774
	1775	eval {
	1776	$where = $dbi->where
	1777	->clause(['uuu']);
	1778	$result = $dbi->select(
	1779	table => 'table1',
	1780	where => $where
	1781	);
	1782	};
	1783	ok($@);
	1784
	1785	$where = $dbi->where;
	1786	is("$where", '');
	1787
	1788	$where = $dbi->where
	1789	->clause(['or', ('key1 = :key1') x 2])
	1790	->param({key1 => [1, 3]});
	1791	$result = $dbi->select(
	1792	table => 'table1',
	1793	where => $where,
	1794	);
	1795	$row = $result->all;
	1796	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
	1797
	1798	$where = $dbi->where
	1799	->clause(['or', ('key1 = :key1') x 2])
	1800	->param({key1 => [1]});
	1801	$result = $dbi->select(
	1802	table => 'table1',
	1803	where => $where,
	1804	);
	1805	$row = $result->all;
	1806	is_deeply($row, [{key1 => 1, key2 => 2}]);
	1807
	1808	$where = $dbi->where
	1809	->clause(['or', ('key1 = :key1') x 2])
	1810	->param({key1 => 1});
	1811	$result = $dbi->select(
	1812	table => 'table1',
	1813	where => $where,
	1814	);
	1815	$row = $result->all;
	1816	is_deeply($row, [{key1 => 1, key2 => 2}]);
	1817
	1818	$where = $dbi->where
	1819	->clause('key1 = :key1')
	1820	->param({key1 => 1});
	1821	$result = $dbi->select(
	1822	table => 'table1',
	1823	where => $where,
	1824	);
	1825	$row = $result->all;
	1826	is_deeply($row, [{key1 => 1, key2 => 2}]);
	1827
	1828	$where = $dbi->where
	1829	->clause('key1 = :key1 key2 = :key2')
	1830	->param({key1 => 1});
	1831	eval{$where->to_string};
	1832	like($@, qr/one column/);
	1833
	1834	$where = $dbi->where
	1835	->clause(['or', ('key1 = :key1') x 3])
	1836	->param({key1 => [$dbi->not_exists, 1, 3]});
	1837	$result = $dbi->select(
	1838	table => 'table1',
	1839	where => $where,
	1840	);
	1841	$row = $result->all;
	1842	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
	1843
	1844	$where = $dbi->where
	1845	->clause(['or', ('key1 = :key1') x 3])
	1846	->param({key1 => [1, $dbi->not_exists, 3]});
	1847	$result = $dbi->select(
	1848	table => 'table1',
	1849	where => $where,
	1850	);
	1851	$row = $result->all;
	1852	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
	1853
	1854	$where = $dbi->where
	1855	->clause(['or', ('key1 = :key1') x 3])
	1856	->param({key1 => [1, 3, $dbi->not_exists]});
	1857	$result = $dbi->select(
	1858	table => 'table1',
	1859	where => $where,
	1860	);
	1861	$row = $result->all;
	1862	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
	1863
	1864	$where = $dbi->where
	1865	->clause(['or', ('key1 = :key1') x 3])
	1866	->param({key1 => [1, $dbi->not_exists, $dbi->not_exists]});
	1867	$result = $dbi->select(
	1868	table => 'table1',
	1869	where => $where,
	1870	);
	1871	$row = $result->all;
	1872	is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
	1873
	1874	$where = $dbi->where
	1875	->clause(['or', ('key1 = :key1') x 3])
	1876	->param({key1 => [$dbi->not_exists, 1, $dbi->not_exists]});
	1877	$result = $dbi->select(
	1878	table => 'table1',
	1879	where => $where,
	1880	);
	1881	$row = $result->all;
	1882	is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
	1883
	1884	$where = $dbi->where
	1885	->clause(['or', ('key1 = :key1') x 3])
	1886	->param({key1 => [$dbi->not_exists, $dbi->not_exists, 1]});
	1887	$result = $dbi->select(
	1888	table => 'table1',
	1889	where => $where,
	1890	);
	1891	$row = $result->all;
	1892	is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
	1893
	1894	$where = $dbi->where
	1895	->clause(['or', ('key1 = :key1') x 3])
	1896	->param({key1 => [$dbi->not_exists, $dbi->not_exists, $dbi->not_exists]});
	1897	$result = $dbi->select(
	1898	table => 'table1',
	1899	where => $where,
	1900	);
	1901	$row = $result->all;
	1902	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
	1903
	1904	$where = $dbi->where
	1905	->clause(['or', ('key1 = :key1') x 3])
	1906	->param({key1 => []});
	1907	$result = $dbi->select(
	1908	table => 'table1',
	1909	where => $where,
	1910	);
	1911	$row = $result->all;
	1912	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
	1913
	1914	$where = $dbi->where
	1915	->clause(['and', '{> key1}', '{< key1}' ])
	1916	->param({key1 => [2, $dbi->not_exists]});
	1917	$result = $dbi->select(
	1918	table => 'table1',
	1919	where => $where,
	1920	);
	1921	$row = $result->all;
	1922	is_deeply($row, [{key1 => 3, key2 => 4}], 'not_exists');
	1923
	1924	$where = $dbi->where
	1925	->clause(['and', '{> key1}', '{< key1}' ])
	1926	->param({key1 => [$dbi->not_exists, 2]});
	1927	$result = $dbi->select(
	1928	table => 'table1',
	1929	where => $where,
	1930	);
	1931	$row = $result->all;
	1932	is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
	1933
	1934	$where = $dbi->where
	1935	->clause(['and', '{> key1}', '{< key1}' ])
	1936	->param({key1 => [$dbi->not_exists, $dbi->not_exists]});
	1937	$result = $dbi->select(
	1938	table => 'table1',
	1939	where => $where,
	1940	);
	1941	$row = $result->all;
	1942	is_deeply($row, [{key1 => 1, key2 => 2},{key1 => 3, key2 => 4}], 'not_exists');
	1943
	1944	$where = $dbi->where
	1945	->clause(['and', '{> key1}', '{< key1}' ])
	1946	->param({key1 => [0, 2]});
	1947	$result = $dbi->select(
	1948	table => 'table1',
	1949	where => $where,
	1950	);
	1951	$row = $result->all;
	1952	is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
	1953
	1954	$where = $dbi->where
	1955	->clause(['and', 'key1 is not null', 'key2 is not null' ]);
	1956	$result = $dbi->select(
	1957	table => 'table1',
	1958	where => $where,
	1959	);
	1960	$row = $result->all;
	1961	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
	1962
	1963	eval {$dbi->where(ppp => 1) };
	1964	like($@, qr/invalid/);
	1965
	1966	$where = $dbi->where(
	1967	clause => ['and', ['or'], ['and', 'key1 = :key1', 'key2 = :key2']],
	1968	param => {key1 => 1, key2 => 2}
	1969	);
	1970	$result = $dbi->select(
	1971	table => 'table1',
	1972	where => $where,
	1973	);
	1974	$row = $result->all;
	1975	is_deeply($row, [{key1 => 1, key2 => 2}]);
	1976
	1977
	1978	$where = $dbi->where(
	1979	clause => ['and', ['or'], ['or', ':key1', ':key2']],
	1980	param => {}
	1981	);
	1982	$result = $dbi->select(
	1983	table => 'table1',
	1984	where => $where,
	1985	);
	1986	$row = $result->all;
	1987	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
	1988
	1989	$where = $dbi->where;
	1990	$where->clause(['and', ':key1{=}']);
	1991	$where->param({key1 => undef});
	1992	$result = $dbi->execute("select * from table1 $where", {key1 => 1});
	1993	$row = $result->all;
	1994	is_deeply($row, [{key1 => 1, key2 => 2}]);
	1995
	1996	$where = $dbi->where;
	1997	$where->clause(['and', ':key1{=}']);
	1998	$where->param({key1 => undef});
	1999	$where->if('defined');
	2000	$where->map;
	2001	$result = $dbi->execute("select * from table1 $where", {key1 => 1});
	2002	$row = $result->all;
	2003	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
	2004
	2005	$where = $dbi->where;
	2006	$where->clause(['or', ':key1{=}', ':key1{=}']);
	2007	$where->param({key1 => [undef, undef]});
	2008	$result = $dbi->execute("select * from table1 $where", {key1 => [1, 0]});
	2009	$row = $result->all;
	2010	is_deeply($row, [{key1 => 1, key2 => 2}]);
	2011	$result = $dbi->execute("select * from table1 $where", {key1 => [0, 1]});
	2012	$row = $result->all;
	2013	is_deeply($row, [{key1 => 1, key2 => 2}]);
	2014
	2015	$where = $dbi->where;
	2016	$where->clause(['and', ':key1{=}']);
	2017	$where->param({key1 => [undef, undef]});
	2018	$where->if('defined');
	2019	$where->map;
	2020	$result = $dbi->execute("select * from table1 $where", {key1 => [1, 0]});
	2021	$row = $result->all;
	2022	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
	2023	$result = $dbi->execute("select * from table1 $where", {key1 => [0, 1]});
	2024	$row = $result->all;
	2025	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
	2026
	2027	$where = $dbi->where;
	2028	$where->clause(['and', ':key1{=}']);
	2029	$where->param({key1 => 0});
	2030	$where->if('length');
	2031	$where->map;
	2032	$result = $dbi->execute("select * from table1 $where", {key1 => 1});
	2033	$row = $result->all;
	2034	is_deeply($row, [{key1 => 1, key2 => 2}]);
	2035
	2036	$where = $dbi->where;
	2037	$where->clause(['and', ':key1{=}']);
	2038	$where->param({key1 => ''});
	2039	$where->if('length');
	2040	$where->map;
	2041	$result = $dbi->execute("select * from table1 $where", {key1 => 1});
	2042	$row = $result->all;
	2043	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
	2044
	2045	$where = $dbi->where;
	2046	$where->clause(['and', ':key1{=}']);
	2047	$where->param({key1 => 5});
	2048	$where->if(sub { ($_[0] \|\| '') eq 5 });
	2049	$where->map;
	2050	$result = $dbi->execute("select * from table1 $where", {key1 => 1});
	2051	$row = $result->all;
	2052	is_deeply($row, [{key1 => 1, key2 => 2}]);
	2053
	2054	$where = $dbi->where;
	2055	$where->clause(['and', ':key1{=}']);
	2056	$where->param({key1 => 7});
	2057	$where->if(sub { ($_[0] \|\| '') eq 5 });
	2058	$where->map;
	2059	$result = $dbi->execute("select * from table1 $where", {key1 => 1});
	2060	$row = $result->all;
	2061	is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
	2062
	2063	$where = $dbi->where;
	2064	$where->param({id => 1, author => 'Ken', price => 1900});
	2065	$where->map(id => 'book.id',
	2066	author => ['book.author', sub { '%' . $_[0] . '%' }],
	2067	price => ['book.price', {if => sub { $_[0] eq 1900 }}]
	2068	);
	2069	is_deeply($where->param, {'book.id' => 1, 'book.author' => '%Ken%',
	2070	'book.price' => 1900});
	2071
	2072	$where = $dbi->where;
	2073	$where->param({id => 0, author => 0, price => 0});
	2074	$where->map(
	2075	id => 'book.id',
	2076	author => ['book.author', sub { '%' . $_[0] . '%' }],
	2077	price => ['book.price', sub { '%' . $_[0] . '%' },
	2078	{if => sub { $_[0] eq 0 }}]
	2079	);
	2080	is_deeply($where->param, {'book.id' => 0, 'book.author' => '%0%', 'book.price' => '%0%'});
	2081
	2082	$where = $dbi->where;
	2083	$where->param({id => '', author => '', price => ''});
	2084	$where->if('length');
	2085	$where->map(
	2086	id => 'book.id',
	2087	author => ['book.author', sub { '%' . $_[0] . '%' }],
	2088	price => ['book.price', sub { '%' . $_[0] . '%' },
	2089	{if => sub { $_[0] eq 1 }}]
	2090	);
	2091	is_deeply($where->param, {});
	2092
	2093	$where = $dbi->where;
	2094	$where->param({id => undef, author => undef, price => undef});
	2095	$where->if('length');
	2096	$where->map(
	2097	id => 'book.id',
	2098	price => ['book.price', {if => 'exists'}]
	2099	);
	2100	is_deeply($where->param, {'book.price' => undef});
	2101
	2102	$where = $dbi->where;
	2103	$where->param({price => 'a'});
	2104	$where->if('length');
	2105	$where->map(
	2106	id => ['book.id', {if => 'exists'}],
	2107	price => ['book.price', sub { '%' . $_[0] }, {if => 'exists'}]
	2108	);
	2109	is_deeply($where->param, {'book.price' => '%a'});
	2110
	2111	$where = $dbi->where;
	2112	$where->param({id => [1, 2], author => 'Ken', price => 1900});
	2113	$where->map(
	2114	id => 'book.id',
	2115	author => ['book.author', sub { '%' . $_[0] . '%' }],
	2116	price => ['book.price', {if => sub { $_[0] eq 1900 }}]
	2117	);
	2118	is_deeply($where->param, {'book.id' => [1, 2], 'book.author' => '%Ken%',
	2119	'book.price' => 1900});
	2120
	2121	$where = $dbi->where;
	2122	$where->if('length');
	2123	$where->param({id => ['', ''], author => 'Ken', price => 1900});
	2124	$where->map(
	2125	id => 'book.id',
	2126	author => ['book.author', sub { '%' . $_[0] . '%' }],
	2127	price => ['book.price', {if => sub { $_[0] eq 1900 }}]
	2128	);
	2129	is_deeply($where->param, {'book.id' => [$dbi->not_exists, $dbi->not_exists], 'book.author' => '%Ken%',
	2130	'book.price' => 1900});
	2131
	2132	$where = $dbi->where;
	2133	$where->param({id => ['', ''], author => 'Ken', price => 1900});
	2134	$where->map(
	2135	id => ['book.id', {if => 'length'}],
	2136	author => ['book.author', sub { '%' . $_[0] . '%' }, {if => 'defined'}],
	2137	price => ['book.price', {if => sub { $_[0] eq 1900 }}]
	2138	);
	2139	is_deeply($where->param, {'book.id' => [$dbi->not_exists, $dbi->not_exists], 'book.author' => '%Ken%',
	2140	'book.price' => 1900});
	2141
	2142	test 'dbi_option default';
	2143	$dbi = DBIx::Custom->new;
	2144	is_deeply($dbi->dbi_option, {});
	2145
	2146	test 'register_tag_processor';
	2147	$dbi = DBIx::Custom->connect;
	2148	$dbi->register_tag_processor(
	2149	a => sub { 1 }
	2150	);
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	2151	is($dbi->{_tags}->{a}->(), 1);
test cleanup b7c037f Yuki Kimoto authored on 2011-08-10	2152
	2153	test 'register_tag';
	2154	$dbi = DBIx::Custom->connect;
	2155	$dbi->register_tag(
	2156	b => sub { 2 }
	2157	);
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	2158	is($dbi->{_tags}->{b}->(), 2);
test cleanup b7c037f Yuki Kimoto authored on 2011-08-10	2159
	2160	test 'table not specify exception';
	2161	$dbi = DBIx::Custom->connect;
	2162	eval {$dbi->insert};
	2163	like($@, qr/table/);
	2164	eval {$dbi->update};
	2165	like($@, qr/table/);
	2166	eval {$dbi->delete};
	2167	like($@, qr/table/);
	2168	eval {$dbi->select};
	2169	like($@, qr/table/);
test cleanup 723ba3d Yuki Kimoto authored on 2011-08-10	2170
test cleanup 34c937b Yuki Kimoto authored on 2011-08-10	2171	test 'more tests';
	2172	$dbi = DBIx::Custom->connect;
	2173	eval{$dbi->apply_filter('table', 'column', [])};
	2174	like($@, qr/apply_filter/);
	2175
	2176	eval{$dbi->apply_filter('table', 'column', {outer => 2})};
	2177	like($@, qr/apply_filter/);
	2178
	2179	$dbi->apply_filter(
	2180
	2181	);
	2182	$dbi = DBIx::Custom->connect;
	2183	eval { $dbi->execute('drop table table1') };
	2184	$dbi->execute($create_table1);
	2185	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2186	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	2187	$dbi->apply_filter('table1', 'key2',
	2188	{in => sub { $_[0] * 3 }, out => sub { $_[0] * 2 }});
	2189	$rows = $dbi->select(table => 'table1', where => {key2 => 1})->all;
	2190	is_deeply($rows, [{key1 => 1, key2 => 6}]);
	2191
	2192	$dbi = DBIx::Custom->connect;
	2193	eval { $dbi->execute('drop table table1') };
	2194	$dbi->execute($create_table1);
	2195	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2196	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	2197	$dbi->apply_filter('table1', 'key2', {});
	2198	$rows = $dbi->select(table => 'table1', where => {key2 => 2})->all;
	2199	is_deeply($rows, [{key1 => 1, key2 => 2}]);
	2200
	2201	$dbi = DBIx::Custom->connect;
	2202	eval {$dbi->apply_filter('table1', 'key2', {out => 'no'})};
	2203	like($@, qr/not registered/);
	2204	eval {$dbi->apply_filter('table1', 'key2', {in => 'no'})};
	2205	like($@, qr/not registered/);
	2206	$dbi->method({one => sub { 1 }});
	2207	is($dbi->one, 1);
	2208
	2209	eval{DBIx::Custom->connect(dsn => undef)};
	2210	like($@, qr/_connect/);
	2211
	2212	$dbi = DBIx::Custom->connect;
	2213	eval { $dbi->execute('drop table table1') };
	2214	$dbi->execute($create_table1);
	2215	$dbi->register_filter(twice => sub { $_[0] * 2 });
	2216	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
	2217	filter => {key1 => 'twice'});
	2218	$row = $dbi->select(table => 'table1')->one;
	2219	is_deeply($row, {key1 => 2, key2 => 2});
	2220	eval {$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
	2221	filter => {key1 => 'no'}) };
	2222	like($@, qr//);
	2223
	2224	$dbi->register_filter(one => sub { });
	2225	$dbi->default_fetch_filter('one');
	2226	ok($dbi->default_fetch_filter);
	2227	$dbi->default_bind_filter('one');
	2228	ok($dbi->default_bind_filter);
	2229	eval{$dbi->default_fetch_filter('no')};
	2230	like($@, qr/not registered/);
	2231	eval{$dbi->default_bind_filter('no')};
	2232	like($@, qr/not registered/);
	2233	$dbi->default_bind_filter(undef);
	2234	ok(!defined $dbi->default_bind_filter);
	2235	$dbi->default_fetch_filter(undef);
	2236	ok(!defined $dbi->default_fetch_filter);
	2237	eval {$dbi->execute('select * from table1 {} {= author') };
	2238	like($@, qr/Tag not finished/);
	2239
	2240	$dbi = DBIx::Custom->connect;
	2241	eval { $dbi->execute('drop table table1') };
	2242	$dbi->execute($create_table1);
	2243	$dbi->register_filter(one => sub { 1 });
	2244	$result = $dbi->select(table => 'table1');
	2245	eval {$result->filter(key1 => 'no')};
	2246	like($@, qr/not registered/);
	2247	eval {$result->end_filter(key1 => 'no')};
	2248	like($@, qr/not registered/);
	2249	$result->default_filter(undef);
	2250	ok(!defined $result->default_filter);
	2251	$result->default_filter('one');
	2252	is($result->default_filter->(), 1);
	2253
	2254	test 'dbi_option';
	2255	$dbi = DBIx::Custom->connect(dbi_option => {PrintError => 1});
	2256	ok($dbi->dbh->{PrintError});
	2257	$dbi = DBIx::Custom->connect(dbi_options => {PrintError => 1});
	2258	ok($dbi->dbh->{PrintError});
	2259
	2260	test 'DBIx::Custom::Result stash()';
	2261	$result = DBIx::Custom::Result->new;
	2262	is_deeply($result->stash, {}, 'default');
	2263	$result->stash->{foo} = 1;
	2264	is($result->stash->{foo}, 1, 'get and set');
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	2265
cleanup test 7115444 Yuki Kimoto authored on 2011-08-10	2266	test 'delete_at';
	2267	$dbi = DBIx::Custom->connect;
	2268	eval { $dbi->execute('drop table table1') };
	2269	$dbi->execute($create_table1_2);
	2270	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2271	$dbi->delete_at(
	2272	table => 'table1',
	2273	primary_key => ['key1', 'key2'],
	2274	where => [1, 2],
	2275	);
	2276	is_deeply($dbi->select(table => 'table1')->all, []);
	2277
	2278	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2279	$dbi->delete_at(
	2280	table => 'table1',
	2281	primary_key => 'key1',
	2282	where => 1,
	2283	);
	2284	is_deeply($dbi->select(table => 'table1')->all, []);
	2285
	2286	test 'insert_at';
	2287	$dbi = DBIx::Custom->connect;
	2288	eval { $dbi->execute('drop table table1') };
	2289	$dbi->execute($create_table1_2);
	2290	$dbi->insert_at(
	2291	primary_key => ['key1', 'key2'],
	2292	table => 'table1',
	2293	where => [1, 2],
	2294	param => {key3 => 3}
	2295	);
	2296	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2297	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2298	is($dbi->select(table => 'table1')->one->{key3}, 3);
	2299
	2300	$dbi->delete_all(table => 'table1');
	2301	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2302	$dbi->insert_at(
	2303	primary_key => 'key1',
	2304	table => 'table1',
	2305	where => 1,
	2306	param => {key2 => 2, key3 => 3}
	2307	);
	2308
	2309	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2310	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2311	is($dbi->select(table => 'table1')->one->{key3}, 3);
	2312
	2313	eval {
	2314	$dbi->insert_at(
	2315	table => 'table1',
	2316	primary_key => ['key1', 'key2'],
	2317	where => {},
	2318	param => {key1 => 1, key2 => 2, key3 => 3},
	2319	);
	2320	};
	2321	like($@, qr/must be/);
	2322
	2323	$dbi = DBIx::Custom->connect;
	2324	eval { $dbi->execute('drop table table1') };
	2325	$dbi->execute($create_table1_2);
	2326	$dbi->insert_at(
	2327	{key3 => 3},
	2328	primary_key => ['key1', 'key2'],
	2329	table => 'table1',
	2330	where => [1, 2],
	2331	);
	2332	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2333	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2334	is($dbi->select(table => 'table1')->one->{key3}, 3);
	2335
	2336	test 'update_at';
	2337	$dbi = DBIx::Custom->connect;
	2338	eval { $dbi->execute('drop table table1') };
	2339	$dbi->execute($create_table1_2);
	2340	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2341	$dbi->update_at(
	2342	table => 'table1',
	2343	primary_key => ['key1', 'key2'],
	2344	where => [1, 2],
	2345	param => {key3 => 4}
	2346	);
	2347	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2348	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2349	is($dbi->select(table => 'table1')->one->{key3}, 4);
	2350
	2351	$dbi->delete_all(table => 'table1');
	2352	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2353	$dbi->update_at(
	2354	table => 'table1',
	2355	primary_key => 'key1',
	2356	where => 1,
	2357	param => {key3 => 4}
	2358	);
	2359	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2360	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2361	is($dbi->select(table => 'table1')->one->{key3}, 4);
	2362
	2363	$dbi = DBIx::Custom->connect;
	2364	eval { $dbi->execute('drop table table1') };
	2365	$dbi->execute($create_table1_2);
	2366	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2367	$dbi->update_at(
	2368	{key3 => 4},
	2369	table => 'table1',
	2370	primary_key => ['key1', 'key2'],
	2371	where => [1, 2]
	2372	);
	2373	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2374	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2375	is($dbi->select(table => 'table1')->one->{key3}, 4);
	2376
	2377	test 'select_at';
	2378	$dbi = DBIx::Custom->connect;
	2379	eval { $dbi->execute('drop table table1') };
	2380	$dbi->execute($create_table1_2);
	2381	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2382	$result = $dbi->select_at(
	2383	table => 'table1',
	2384	primary_key => ['key1', 'key2'],
	2385	where => [1, 2]
	2386	);
	2387	$row = $result->one;
	2388	is($row->{key1}, 1);
	2389	is($row->{key2}, 2);
	2390	is($row->{key3}, 3);
	2391
	2392	$dbi->delete_all(table => 'table1');
	2393	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2394	$result = $dbi->select_at(
	2395	table => 'table1',
	2396	primary_key => 'key1',
	2397	where => 1,
	2398	);
	2399	$row = $result->one;
	2400	is($row->{key1}, 1);
	2401	is($row->{key2}, 2);
	2402	is($row->{key3}, 3);
	2403
	2404	$dbi->delete_all(table => 'table1');
	2405	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2406	$result = $dbi->select_at(
	2407	table => 'table1',
	2408	primary_key => ['key1', 'key2'],
	2409	where => [1, 2]
	2410	);
	2411	$row = $result->one;
	2412	is($row->{key1}, 1);
	2413	is($row->{key2}, 2);
	2414	is($row->{key3}, 3);
	2415
	2416	eval {
	2417	$result = $dbi->select_at(
	2418	table => 'table1',
	2419	primary_key => ['key1', 'key2'],
	2420	where => {},
	2421	);
	2422	};
	2423	like($@, qr/must be/);
	2424
	2425	eval {
	2426	$result = $dbi->select_at(
	2427	table => 'table1',
	2428	primary_key => ['key1', 'key2'],
	2429	where => [1],
	2430	);
	2431	};
	2432	like($@, qr/same/);
	2433
	2434	eval {
	2435	$result = $dbi->update_at(
	2436	table => 'table1',
	2437	primary_key => ['key1', 'key2'],
	2438	where => {},
	2439	param => {key1 => 1, key2 => 2},
	2440	);
	2441	};
	2442	like($@, qr/must be/);
	2443
	2444	eval {
	2445	$result = $dbi->delete_at(
	2446	table => 'table1',
	2447	primary_key => ['key1', 'key2'],
	2448	where => {},
	2449	);
	2450	};
	2451	like($@, qr/must be/);
	2452
	2453	test 'columns';
	2454	use MyDBI1;
	2455	$dbi = MyDBI1->connect;
	2456	$model = $dbi->model('book');
	2457
	2458
	2459	test 'model delete_at';
	2460	$dbi = MyDBI6->connect;
	2461	eval { $dbi->execute('drop table table1') };
	2462	eval { $dbi->execute('drop table table2') };
	2463	eval { $dbi->execute('drop table table3') };
	2464	$dbi->execute($create_table1_2);
	2465	$dbi->execute($create_table2_2);
	2466	$dbi->execute($create_table3);
	2467	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2468	$dbi->model('table1')->delete_at(where => [1, 2]);
	2469	is_deeply($dbi->select(table => 'table1')->all, []);
	2470	$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
	2471	$dbi->model('table1_1')->delete_at(where => [1, 2]);
	2472	is_deeply($dbi->select(table => 'table1')->all, []);
	2473	$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
	2474	$dbi->model('table1_3')->delete_at(where => [1, 2]);
	2475	is_deeply($dbi->select(table => 'table1')->all, []);
	2476
	2477	test 'model insert_at';
	2478	$dbi = MyDBI6->connect;
	2479	eval { $dbi->execute('drop table table1') };
	2480	$dbi->execute($create_table1_2);
	2481	$dbi->model('table1')->insert_at(
	2482	where => [1, 2],
	2483	param => {key3 => 3}
	2484	);
	2485	$result = $dbi->model('table1')->select;
	2486	$row = $result->one;
	2487	is($row->{key1}, 1);
	2488	is($row->{key2}, 2);
	2489	is($row->{key3}, 3);
	2490
	2491	test 'model update_at';
	2492	$dbi = MyDBI6->connect;
	2493	eval { $dbi->execute('drop table table1') };
	2494	$dbi->execute($create_table1_2);
	2495	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2496	$dbi->model('table1')->update_at(
	2497	where => [1, 2],
	2498	param => {key3 => 4}
	2499	);
	2500	$result = $dbi->model('table1')->select;
	2501	$row = $result->one;
	2502	is($row->{key1}, 1);
	2503	is($row->{key2}, 2);
	2504	is($row->{key3}, 4);
	2505
	2506	test 'model select_at';
	2507	$dbi = MyDBI6->connect;
	2508	eval { $dbi->execute('drop table table1') };
	2509	$dbi->execute($create_table1_2);
	2510	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2511	$result = $dbi->model('table1')->select_at(where => [1, 2]);
	2512	$row = $result->one;
	2513	is($row->{key1}, 1);
	2514	is($row->{key2}, 2);
	2515	is($row->{key3}, 3);
	2516
	2517
	2518	test 'mycolumn and column';
	2519	$dbi = MyDBI7->connect;
	2520	eval { $dbi->execute('drop table table1') };
	2521	eval { $dbi->execute('drop table table2') };
	2522	$dbi->execute($create_table1);
	2523	$dbi->execute($create_table2);
	2524	$dbi->separator('__');
	2525	$dbi->setup_model;
	2526	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2527	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
	2528	$model = $dbi->model('table1');
	2529	$result = $model->select(
	2530	column => [$model->mycolumn, $model->column('table2')],
	2531	where => {'table1.key1' => 1}
	2532	);
	2533	is_deeply($result->one,
	2534	{key1 => 1, key2 => 2, 'table2__key1' => 1, 'table2__key3' => 3});
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	2535
test cleanup db64470 Yuki Kimoto authored on 2011-08-10	2536	test 'insert_param';
	2537	$dbi = DBIx::Custom->connect;
	2538	eval { $dbi->execute('drop table table1') };
	2539	$dbi->execute($create_table1_2);
	2540	$param = {key1 => 1, key2 => 2};
	2541	$insert_param = $dbi->insert_param($param);
	2542	$sql = <<"EOS";
	2543	insert into table1 $insert_param
	2544	EOS
	2545	$dbi->execute($sql, param => $param, table => 'table1');
	2546	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2547	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2548
	2549	$dbi = DBIx::Custom->connect;
	2550	eval { $dbi->execute('drop table table1') };
	2551	$dbi->execute($create_table1_2);
	2552	$param = {key1 => 1, key2 => 2};
	2553	$insert_param = $dbi->insert_param($param);
	2554	$sql = <<"EOS";
	2555	insert into table1 $insert_param
	2556	EOS
	2557	$dbi->execute($sql, param => $param, table => 'table1');
	2558	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2559	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2560
	2561	eval { $dbi->insert_param({";" => 1}) };
	2562	like($@, qr/not safety/);
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	2563
cleanup test ee32d7d Yuki Kimoto authored on 2011-08-10	2564	test 'mycolumn';
	2565	$dbi = MyDBI8->connect;
	2566	eval { $dbi->execute('drop table table1') };
	2567	eval { $dbi->execute('drop table table2') };
	2568	$dbi->execute($create_table1);
	2569	$dbi->execute($create_table2);
	2570	$dbi->setup_model;
	2571	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2572	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
	2573	$model = $dbi->model('table1');
	2574	$result = $model->select_at(
	2575	column => [
	2576	$model->mycolumn,
	2577	$model->column('table2')
	2578	]
	2579	);
	2580	is_deeply($result->one,
	2581	{key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
	2582
	2583	$result = $model->select_at(
	2584	column => [
	2585	$model->mycolumn(['key1']),
	2586	$model->column(table2 => ['key1'])
	2587	]
	2588	);
	2589	is_deeply($result->one,
	2590	{key1 => 1, 'table2.key1' => 1});
	2591	$result = $model->select_at(
	2592	column => [
	2593	$model->mycolumn(['key1']),
	2594	{table2 => ['key1']}
	2595	]
	2596	);
	2597	is_deeply($result->one,
	2598	{key1 => 1, 'table2.key1' => 1});
	2599
	2600	$result = $model->select_at(
	2601	column => [
	2602	$model->mycolumn(['key1']),
	2603	['table2.key1', as => 'table2.key1']
	2604	]
	2605	);
	2606	is_deeply($result->one,
	2607	{key1 => 1, 'table2.key1' => 1});
	2608
	2609	$result = $model->select_at(
	2610	column => [
	2611	$model->mycolumn(['key1']),
	2612	['table2.key1' => 'table2.key1']
	2613	]
	2614	);
	2615	is_deeply($result->one,
	2616	{key1 => 1, 'table2.key1' => 1});
	2617
cleanup test 3e9a4ba Yuki Kimoto authored on 2011-08-10	2618	test 'merge_param';
	2619	$dbi = DBIx::Custom->new;
	2620	$params = [
	2621	{key1 => 1, key2 => 2, key3 => 3},
	2622	{key1 => 1, key2 => 2},
	2623	{key1 => 1}
	2624	];
	2625	$param = $dbi->merge_param($params->[0], $params->[1], $params->[2]);
	2626	is_deeply($param, {key1 => [1, 1, 1], key2 => [2, 2], key3 => 3});
	2627
	2628	$params = [
	2629	{key1 => [1, 2], key2 => 1, key3 => [1, 2]},
	2630	{key1 => [3, 4], key2 => [2, 3], key3 => 3}
	2631	];
	2632	$param = $dbi->merge_param($params->[0], $params->[1]);
	2633	is_deeply($param, {key1 => [1, 2, 3, 4], key2 => [1, 2, 3], key3 => [1, 2, 3]});
	2634
	2635	test 'select() param option';
	2636	$dbi = DBIx::Custom->connect;
	2637	eval { $dbi->execute('drop table table1') };
	2638	$dbi->execute($create_table1);
	2639	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2640	$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
	2641	eval { $dbi->execute('drop table table2') };
	2642	$dbi->execute($create_table2);
	2643	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
	2644	$dbi->insert(table => 'table2', param => {key1 => 2, key3 => 5});
	2645	$rows = $dbi->select(
	2646	table => 'table1',
	2647	column => 'table1.key1 as table1_key1, key2, key3',
	2648	where => {'table1.key2' => 3},
	2649	join => ['inner join (select * from table2 where {= table2.key3})' .
	2650	' as table2 on table1.key1 = table2.key1'],
	2651	param => {'table2.key3' => 5}
	2652	)->all;
	2653	is_deeply($rows, [{table1_key1 => 2, key2 => 3, key3 => 5}]);
	2654
	2655	test 'select() string where';
	2656	$dbi = DBIx::Custom->connect;
	2657	eval { $dbi->execute('drop table table1') };
	2658	$dbi->execute($create_table1);
	2659	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2660	$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
	2661	$rows = $dbi->select(
	2662	table => 'table1',
	2663	where => 'key1 = :key1 and key2 = :key2',
	2664	where_param => {key1 => 1, key2 => 2}
	2665	)->all;
	2666	is_deeply($rows, [{key1 => 1, key2 => 2}]);
	2667
	2668	$dbi = DBIx::Custom->connect;
	2669	eval { $dbi->execute('drop table table1') };
	2670	$dbi->execute($create_table1);
	2671	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2672	$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
	2673	$rows = $dbi->select(
	2674	table => 'table1',
	2675	where => [
	2676	'key1 = :key1 and key2 = :key2',
	2677	{key1 => 1, key2 => 2}
	2678	]
	2679	)->all;
	2680	is_deeply($rows, [{key1 => 1, key2 => 2}]);
	2681
	2682	test 'delete() string where';
	2683	$dbi = DBIx::Custom->connect;
	2684	eval { $dbi->execute('drop table table1') };
	2685	$dbi->execute($create_table1);
	2686	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2687	$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
	2688	$dbi->delete(
	2689	table => 'table1',
	2690	where => 'key1 = :key1 and key2 = :key2',
	2691	where_param => {key1 => 1, key2 => 2}
	2692	);
	2693	$rows = $dbi->select(table => 'table1')->all;
	2694	is_deeply($rows, [{key1 => 2, key2 => 3}]);
	2695
	2696	$dbi = DBIx::Custom->connect;
	2697	eval { $dbi->execute('drop table table1') };
	2698	$dbi->execute($create_table1);
	2699	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2700	$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
	2701	$dbi->delete(
	2702	table => 'table1',
	2703	where => [
	2704	'key1 = :key1 and key2 = :key2',
	2705	{key1 => 1, key2 => 2}
	2706	]
	2707	);
	2708	$rows = $dbi->select(table => 'table1')->all;
	2709	is_deeply($rows, [{key1 => 2, key2 => 3}]);
	2710
	2711
	2712	test 'update() string where';
	2713	$dbi = DBIx::Custom->connect;
	2714	eval { $dbi->execute('drop table table1') };
	2715	$dbi->execute($create_table1);
	2716	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2717	$dbi->update(
	2718	table => 'table1',
	2719	param => {key1 => 5},
	2720	where => 'key1 = :key1 and key2 = :key2',
	2721	where_param => {key1 => 1, key2 => 2}
	2722	);
	2723	$rows = $dbi->select(table => 'table1')->all;
	2724	is_deeply($rows, [{key1 => 5, key2 => 2}]);
	2725
	2726	$dbi = DBIx::Custom->connect;
	2727	eval { $dbi->execute('drop table table1') };
	2728	$dbi->execute($create_table1);
	2729	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2730	$dbi->update(
	2731	table => 'table1',
	2732	param => {key1 => 5},
	2733	where => [
	2734	'key1 = :key1 and key2 = :key2',
	2735	{key1 => 1, key2 => 2}
	2736	]
	2737	);
	2738	$rows = $dbi->select(table => 'table1')->all;
	2739	is_deeply($rows, [{key1 => 5, key2 => 2}]);
	2740
	2741	test 'insert id and primary_key option';
	2742	$dbi = DBIx::Custom->connect;
	2743	eval { $dbi->execute('drop table table1') };
	2744	$dbi->execute($create_table1_2);
	2745	$dbi->insert(
	2746	primary_key => ['key1', 'key2'],
	2747	table => 'table1',
	2748	id => [1, 2],
	2749	param => {key3 => 3}
	2750	);
	2751	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2752	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2753	is($dbi->select(table => 'table1')->one->{key3}, 3);
	2754
	2755	$dbi->delete_all(table => 'table1');
	2756	$dbi->insert(
	2757	primary_key => 'key1',
	2758	table => 'table1',
	2759	id => 0,
	2760	param => {key2 => 2, key3 => 3}
	2761	);
	2762
	2763	is($dbi->select(table => 'table1')->one->{key1}, 0);
	2764	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2765	is($dbi->select(table => 'table1')->one->{key3}, 3);
	2766
	2767	$dbi = DBIx::Custom->connect;
	2768	eval { $dbi->execute('drop table table1') };
	2769	$dbi->execute($create_table1_2);
	2770	$dbi->insert(
	2771	{key3 => 3},
	2772	primary_key => ['key1', 'key2'],
	2773	table => 'table1',
	2774	id => [1, 2],
	2775	);
	2776	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2777	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2778	is($dbi->select(table => 'table1')->one->{key3}, 3);
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	2779
cleanup test 65d6fcd Yuki Kimoto authored on 2011-08-10	2780	test 'model insert id and primary_key option';
	2781	$dbi = MyDBI6->connect;
	2782	eval { $dbi->execute('drop table table1') };
	2783	$dbi->execute($create_table1_2);
	2784	$dbi->model('table1')->insert(
	2785	id => [1, 2],
	2786	param => {key3 => 3}
	2787	);
	2788	$result = $dbi->model('table1')->select;
	2789	$row = $result->one;
	2790	is($row->{key1}, 1);
	2791	is($row->{key2}, 2);
	2792	is($row->{key3}, 3);
	2793
	2794	$dbi = MyDBI6->connect;
	2795	eval { $dbi->execute('drop table table1') };
	2796	$dbi->execute($create_table1_2);
	2797	$dbi->model('table1')->insert(
	2798	{key3 => 3},
	2799	id => [1, 2]
	2800	);
	2801	$result = $dbi->model('table1')->select;
	2802	$row = $result->one;
	2803	is($row->{key1}, 1);
	2804	is($row->{key2}, 2);
	2805	is($row->{key3}, 3);
	2806
	2807	test 'update and id option';
	2808	$dbi = DBIx::Custom->connect;
	2809	eval { $dbi->execute('drop table table1') };
	2810	$dbi->execute($create_table1_2);
	2811	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2812	$dbi->update(
	2813	table => 'table1',
	2814	primary_key => ['key1', 'key2'],
	2815	id => [1, 2],
	2816	param => {key3 => 4}
	2817	);
	2818	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2819	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2820	is($dbi->select(table => 'table1')->one->{key3}, 4);
	2821
	2822	$dbi->delete_all(table => 'table1');
	2823	$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
	2824	$dbi->update(
	2825	table => 'table1',
	2826	primary_key => 'key1',
	2827	id => 0,
	2828	param => {key3 => 4}
	2829	);
	2830	is($dbi->select(table => 'table1')->one->{key1}, 0);
	2831	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2832	is($dbi->select(table => 'table1')->one->{key3}, 4);
	2833
	2834	$dbi = DBIx::Custom->connect;
	2835	eval { $dbi->execute('drop table table1') };
	2836	$dbi->execute($create_table1_2);
	2837	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2838	$dbi->update(
	2839	{key3 => 4},
	2840	table => 'table1',
	2841	primary_key => ['key1', 'key2'],
	2842	id => [1, 2]
	2843	);
	2844	is($dbi->select(table => 'table1')->one->{key1}, 1);
	2845	is($dbi->select(table => 'table1')->one->{key2}, 2);
	2846	is($dbi->select(table => 'table1')->one->{key3}, 4);
	2847
	2848
	2849	test 'model update and id option';
	2850	$dbi = MyDBI6->connect;
	2851	eval { $dbi->execute('drop table table1') };
	2852	$dbi->execute($create_table1_2);
	2853	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2854	$dbi->model('table1')->update(
	2855	id => [1, 2],
	2856	param => {key3 => 4}
	2857	);
	2858	$result = $dbi->model('table1')->select;
	2859	$row = $result->one;
	2860	is($row->{key1}, 1);
	2861	is($row->{key2}, 2);
	2862	is($row->{key3}, 4);
	2863
	2864
	2865	test 'delete and id option';
	2866	$dbi = DBIx::Custom->connect;
	2867	eval { $dbi->execute('drop table table1') };
	2868	$dbi->execute($create_table1_2);
	2869	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2870	$dbi->delete(
	2871	table => 'table1',
	2872	primary_key => ['key1', 'key2'],
	2873	id => [1, 2],
	2874	);
	2875	is_deeply($dbi->select(table => 'table1')->all, []);
	2876
	2877	$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
	2878	$dbi->delete(
	2879	table => 'table1',
	2880	primary_key => 'key1',
	2881	id => 0,
	2882	);
	2883	is_deeply($dbi->select(table => 'table1')->all, []);
	2884
	2885
	2886	test 'model delete and id option';
	2887	$dbi = MyDBI6->connect;
	2888	eval { $dbi->execute('drop table table1') };
	2889	eval { $dbi->execute('drop table table2') };
	2890	eval { $dbi->execute('drop table table3') };
	2891	$dbi->execute($create_table1_2);
	2892	$dbi->execute($create_table2_2);
	2893	$dbi->execute($create_table3);
	2894	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2895	$dbi->model('table1')->delete(id => [1, 2]);
	2896	is_deeply($dbi->select(table => 'table1')->all, []);
	2897	$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
	2898	$dbi->model('table1_1')->delete(id => [1, 2]);
	2899	is_deeply($dbi->select(table => 'table1')->all, []);
	2900	$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
	2901	$dbi->model('table1_3')->delete(id => [1, 2]);
	2902	is_deeply($dbi->select(table => 'table1')->all, []);
	2903
	2904
	2905	test 'select and id option';
	2906	$dbi = DBIx::Custom->connect;
	2907	eval { $dbi->execute('drop table table1') };
	2908	$dbi->execute($create_table1_2);
	2909	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2910	$result = $dbi->select(
	2911	table => 'table1',
	2912	primary_key => ['key1', 'key2'],
	2913	id => [1, 2]
	2914	);
	2915	$row = $result->one;
	2916	is($row->{key1}, 1);
	2917	is($row->{key2}, 2);
	2918	is($row->{key3}, 3);
	2919
	2920	$dbi->delete_all(table => 'table1');
	2921	$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
	2922	$result = $dbi->select(
	2923	table => 'table1',
	2924	primary_key => 'key1',
	2925	id => 0,
	2926	);
	2927	$row = $result->one;
	2928	is($row->{key1}, 0);
	2929	is($row->{key2}, 2);
	2930	is($row->{key3}, 3);
	2931
	2932	$dbi->delete_all(table => 'table1');
	2933	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2934	$result = $dbi->select(
	2935	table => 'table1',
	2936	primary_key => ['key1', 'key2'],
	2937	id => [1, 2]
	2938	);
	2939	$row = $result->one;
	2940	is($row->{key1}, 1);
	2941	is($row->{key2}, 2);
	2942	is($row->{key3}, 3);
	2943
	2944
	2945	test 'model select_at';
	2946	$dbi = MyDBI6->connect;
	2947	eval { $dbi->execute('drop table table1') };
	2948	$dbi->execute($create_table1_2);
	2949	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
	2950	$result = $dbi->model('table1')->select(id => [1, 2]);
	2951	$row = $result->one;
	2952	is($row->{key1}, 1);
	2953	is($row->{key2}, 2);
	2954	is($row->{key3}, 3);
	2955
	2956	test 'column separator is default .';
	2957	$dbi = MyDBI7->connect;
	2958	eval { $dbi->execute('drop table table1') };
	2959	eval { $dbi->execute('drop table table2') };
	2960	$dbi->execute($create_table1);
	2961	$dbi->execute($create_table2);
	2962	$dbi->setup_model;
	2963	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2964	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
	2965	$model = $dbi->model('table1');
	2966	$result = $model->select(
	2967	column => [$model->column('table2')],
	2968	where => {'table1.key1' => 1}
	2969	);
	2970	is_deeply($result->one,
	2971	{'table2.key1' => 1, 'table2.key3' => 3});
	2972
	2973	$result = $model->select(
	2974	column => [$model->column('table2' => [qw/key1 key3/])],
	2975	where => {'table1.key1' => 1}
	2976	);
	2977	is_deeply($result->one,
	2978	{'table2.key1' => 1, 'table2.key3' => 3});
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	2979
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	2980	test 'separator';
	2981	$dbi = DBIx::Custom->connect;
	2982	eval { $dbi->execute('drop table table1') };
	2983	eval { $dbi->execute('drop table table2') };
	2984	$dbi->execute($create_table1);
	2985	$dbi->execute($create_table2);
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	2986
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	2987	$dbi->create_model(
	2988	table => 'table1',
	2989	join => [
	2990	'left outer join table2 on table1.key1 = table2.key1'
	2991	],
	2992	primary_key => ['key1'],
	2993	);
	2994	$model2 = $dbi->create_model(
	2995	table => 'table2',
	2996	);
	2997	$dbi->setup_model;
	2998	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	2999	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
	3000	$model = $dbi->model('table1');
	3001	$result = $model->select(
	3002	column => [
	3003	$model->mycolumn,
	3004	{table2 => [qw/key1 key3/]}
	3005	],
	3006	where => {'table1.key1' => 1}
	3007	);
	3008	is_deeply($result->one,
	3009	{key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
	3010	is_deeply($model2->select->one, {key1 => 1, key3 => 3});
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3011
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	3012	$dbi->separator('__');
	3013	$model = $dbi->model('table1');
	3014	$result = $model->select(
	3015	column => [
	3016	$model->mycolumn,
	3017	{table2 => [qw/key1 key3/]}
	3018	],
	3019	where => {'table1.key1' => 1}
	3020	);
	3021	is_deeply($result->one,
	3022	{key1 => 1, key2 => 2, 'table2__key1' => 1, 'table2__key3' => 3});
	3023	is_deeply($model2->select->one, {key1 => 1, key3 => 3});
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3024
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	3025	$dbi->separator('-');
	3026	$model = $dbi->model('table1');
	3027	$result = $model->select(
	3028	column => [
	3029	$model->mycolumn,
	3030	{table2 => [qw/key1 key3/]}
	3031	],
	3032	where => {'table1.key1' => 1}
	3033	);
	3034	is_deeply($result->one,
	3035	{key1 => 1, key2 => 2, 'table2-key1' => 1, 'table2-key3' => 3});
	3036	is_deeply($model2->select->one, {key1 => 1, key3 => 3});
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3037
	3038
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	3039	test 'filter_off';
	3040	$dbi = DBIx::Custom->connect;
	3041	eval { $dbi->execute('drop table table1') };
	3042	eval { $dbi->execute('drop table table2') };
	3043	$dbi->execute($create_table1);
	3044	$dbi->execute($create_table2);
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3045
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	3046	$dbi->create_model(
	3047	table => 'table1',
	3048	join => [
	3049	'left outer join table2 on table1.key1 = table2.key1'
	3050	],
	3051	primary_key => ['key1'],
	3052	);
	3053	$dbi->setup_model;
	3054	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	3055	$model = $dbi->model('table1');
	3056	$result = $model->select(column => 'key1');
	3057	$result->filter(key1 => sub { $_[0] * 2 });
	3058	is_deeply($result->one, {key1 => 2});
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3059
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	3060	test 'available_datetype';
	3061	$dbi = DBIx::Custom->connect;
	3062	ok($dbi->can('available_datatype'));
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3063
	3064
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	3065	test 'select prefix option';
	3066	$dbi = DBIx::Custom->connect;
	3067	eval { $dbi->execute('drop table table1') };
	3068	$dbi->execute($create_table1);
	3069	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	3070	$rows = $dbi->select(prefix => 'key1,', column => 'key2', table => 'table1')->all;
	3071	is_deeply($rows, [{key1 => 1, key2 => 2}], "table");
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3072
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	3073	test 'map_param';
	3074	$dbi = DBIx::Custom->connect;
	3075	$param = $dbi->map_param(
	3076	{id => 1, author => 'Ken', price => 1900},
	3077	id => 'book.id',
	3078	author => ['book.author', sub { '%' . $_[0] . '%' }],
	3079	price => ['book.price', {if => sub { $_[0] eq 1900 }}]
	3080	);
	3081	is_deeply($param, {'book.id' => 1, 'book.author' => '%Ken%',
	3082	'book.price' => 1900});
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3083
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	3084	$param = $dbi->map_param(
	3085	{id => 0, author => 0, price => 0},
	3086	id => 'book.id',
	3087	author => ['book.author', sub { '%' . $_[0] . '%' }],
	3088	price => ['book.price', sub { '%' . $_[0] . '%' },
	3089	{if => sub { $_[0] eq 0 }}]
	3090	);
	3091	is_deeply($param, {'book.id' => 0, 'book.author' => '%0%', 'book.price' => '%0%'});
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3092
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	3093	$param = $dbi->map_param(
	3094	{id => '', author => '', price => ''},
	3095	id => 'book.id',
	3096	author => ['book.author', sub { '%' . $_[0] . '%' }],
	3097	price => ['book.price', sub { '%' . $_[0] . '%' },
	3098	{if => sub { $_[0] eq 1 }}]
	3099	);
	3100	is_deeply($param, {});
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3101
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	3102	$param = $dbi->map_param(
	3103	{id => undef, author => undef, price => undef},
	3104	id => 'book.id',
	3105	price => ['book.price', {if => 'exists'}]
	3106	);
	3107	is_deeply($param, {'book.price' => undef});
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3108
cleanup test c1c6c1f Yuki Kimoto authored on 2011-08-10	3109	$param = $dbi->map_param(
	3110	{price => 'a'},
	3111	id => ['book.id', {if => 'exists'}],
	3112	price => ['book.price', sub { '%' . $_[0] }, {if => 'exists'}]
	3113	);
	3114	is_deeply($param, {'book.price' => '%a'});
test cleanup 1d42ba8 Yuki Kimoto authored on 2011-08-10	3115
test cleanup 0419ba5 Yuki Kimoto authored on 2011-08-10	3116	test 'order';
	3117	$dbi = DBIx::Custom->connect;
	3118	eval { $dbi->execute('drop table table1') };
	3119	$dbi->execute($create_table1);
	3120	$dbi->insert({key1 => 1, key2 => 1}, table => 'table1');
	3121	$dbi->insert({key1 => 1, key2 => 3}, table => 'table1');
	3122	$dbi->insert({key1 => 2, key2 => 2}, table => 'table1');
	3123	$dbi->insert({key1 => 2, key2 => 4}, table => 'table1');
	3124	my $order = $dbi->order;
	3125	$order->prepend('key1', 'key2 desc');
	3126	$result = $dbi->select(table => 'table1', append => "$order");
	3127	is_deeply($result->all, [{key1 => 1, key2 => 3}, {key1 => 1, key2 => 1},
	3128	{key1 => 2, key2 => 4}, {key1 => 2, key2 => 2}]);
	3129	$order->prepend('key1 desc');
	3130	$result = $dbi->select(table => 'table1', append => "$order");
	3131	is_deeply($result->all, [{key1 => 2, key2 => 4}, {key1 => 2, key2 => 2},
	3132	{key1 => 1, key2 => 3}, {key1 => 1, key2 => 1}]);
	3133
	3134	$order = $dbi->order;
	3135	$order->prepend(['table1-key1'], [qw/table1-key2 desc/]);
	3136	$result = $dbi->select(table => 'table1',
	3137	column => [[key1 => 'table1-key1'], [key2 => 'table1-key2']],
	3138	append => "$order");
	3139	is_deeply($result->all, [{'table1-key1' => 1, 'table1-key2' => 3},
	3140	{'table1-key1' => 1, 'table1-key2' => 1},
	3141	{'table1-key1' => 2, 'table1-key2' => 4},
	3142	{'table1-key1' => 2, 'table1-key2' => 2}]);
	3143
	3144	test 'tag_parse';
	3145	$dbi = DBIx::Custom->connect;
	3146	$dbi->tag_parse(0);
	3147	eval { $dbi->execute('drop table table1') };
	3148	$dbi->execute($create_table1);
	3149	$dbi->insert({key1 => 1, key2 => 1}, table => 'table1');
	3150	eval {$dbi->execute("select * from table1 where {= key1}", {key1 => 1})};
	3151	ok($@);
	3152
	3153	test 'last_sql';
	3154	$dbi = DBIx::Custom->connect;
	3155	eval { $dbi->execute('drop table table1') };
	3156	$dbi->execute($create_table1);
	3157	$dbi->execute('select * from table1');
	3158	is($dbi->last_sql, 'select * from table1;');
	3159
	3160	eval{$dbi->execute("aaa")};
	3161	is($dbi->last_sql, 'aaa;');
	3162
	3163	test 'DBIx::Custom header';
	3164	$dbi = DBIx::Custom->connect;
	3165	eval { $dbi->execute('drop table table1') };
	3166	$dbi->execute($create_table1);
	3167	$result = $dbi->execute('select key1 as h1, key2 as h2 from table1');
	3168	is_deeply($result->header, [qw/h1 h2/]);
	3169
	3170	test 'Named placeholder :name(operater) syntax';
	3171	$dbi->execute('drop table table1');
	3172	$dbi->execute($create_table1_2);
	3173	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	3174	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	3175
	3176	$source = "select * from table1 where :key1{=} and :key2{=}";
	3177	$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
	3178	$rows = $result->all;
	3179	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
	3180
	3181	$source = "select * from table1 where :key1{ = } and :key2{=}";
	3182	$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
	3183	$rows = $result->all;
	3184	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
	3185
	3186	$source = "select * from table1 where :key1{<} and :key2{=}";
	3187	$result = $dbi->execute($source, param => {key1 => 5, key2 => 2});
	3188	$rows = $result->all;
	3189	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
	3190
	3191	$source = "select * from table1 where :table1.key1{=} and :table1.key2{=}";
	3192	$result = $dbi->execute(
	3193	$source,
	3194	param => {'table1.key1' => 1, 'table1.key2' => 1},
	3195	filter => {'table1.key2' => sub { $_[0] * 2 }}
	3196	);
	3197	$rows = $result->all;
	3198	is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
	3199
	3200	test 'high perfomance way';
	3201	$dbi->execute('drop table table1');
	3202	$dbi->execute($create_table1_highperformance);
	3203	$rows = [
	3204	{ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
	3205	{ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
	3206	];
	3207	{
	3208	my $query;
	3209	foreach my $row (@$rows) {
	3210	$query \|\|= $dbi->insert($row, table => 'table1', query => 1);
	3211	$dbi->execute($query, $row, filter => {ab => sub { $_[0] * 2 }});
	3212	}
	3213	is_deeply($dbi->select(table => 'table1')->all,
	3214	[
	3215	{ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
	3216	{ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
	3217	]
	3218	);
	3219	}
	3220
	3221	$dbi->execute('drop table table1');
	3222	$dbi->execute($create_table1_highperformance);
	3223	$rows = [
	3224	{ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
	3225	{ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
	3226	];
	3227	{
	3228	my $query;
	3229	my $sth;
	3230	foreach my $row (@$rows) {
	3231	$query \|\|= $dbi->insert($row, table => 'table1', query => 1);
	3232	$sth \|\|= $query->sth;
	3233	$sth->execute(map { $row->{$_} } sort keys %$row);
	3234	}
	3235	is_deeply($dbi->select(table => 'table1')->all,
	3236	[
	3237	{ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
	3238	{ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
	3239	]
	3240	);
	3241	}
	3242
	3243	test 'result';
	3244	$dbi = DBIx::Custom->connect;
	3245	eval { $dbi->execute('drop table table1') };
	3246	$dbi->execute($create_table1);
	3247	$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
	3248	$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
	3249
	3250	$result = $dbi->select(table => 'table1');
	3251	@rows = ();
	3252	while (my $row = $result->fetch) {
	3253	push @rows, [@$row];
	3254	}
	3255	is_deeply(\@rows, [[1, 2], [3, 4]]);
	3256
	3257	$result = $dbi->select(table => 'table1');
	3258	@rows = ();
	3259	while (my $row = $result->fetch_hash) {
	3260	push @rows, {%$row};
	3261	}
	3262	is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
	3263
	3264	$dbi = DBIx::Custom->connect;
	3265	eval { $dbi->execute('drop table table1') };
	3266	$dbi->execute($create_table1);
	3267	$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
	3268	$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
	3269
	3270	test 'fetch_all';
	3271	$result = $dbi->select(table => 'table1');
	3272	$rows = $result->fetch_all;
	3273	is_deeply($rows, [[1, 2], [3, 4]]);
	3274
	3275	$result = $dbi->select(table => 'table1');
	3276	$rows = $result->fetch_hash_all;
	3277	is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
	3278
	3279	$result = $dbi->select(table => 'table1');
	3280	$result->dbi->filters({three_times => sub { $_[0] * 3}});
	3281	$result->filter({key1 => 'three_times'});
	3282
	3283	$rows = $result->fetch_all;
	3284	is_deeply($rows, [[3, 2], [9, 4]], "array");
	3285
	3286	$result = $dbi->select(table => 'table1');
	3287	$result->dbi->filters({three_times => sub { $_[0] * 3}});
	3288	$result->filter({key1 => 'three_times'});
	3289	$rows = $result->fetch_hash_all;
	3290	is_deeply($rows, [{key1 => 3, key2 => 2}, {key1 => 9, key2 => 4}], "hash");
	3291
	3292	test "query_builder";
	3293	$datas = [
	3294	# Basic tests
	3295	{ name => 'placeholder basic',
	3296	source => "a {? k1} b {= k2} {<> k3} {> k4} {< k5} {>= k6} {<= k7} {like k8}", ,
	3297	sql_expected => "a ? b k2 = ? k3 <> ? k4 > ? k5 < ? k6 >= ? k7 <= ? k8 like ?;",
	3298	columns_expected => [qw/k1 k2 k3 k4 k5 k6 k7 k8/]
	3299	},
	3300	{
	3301	name => 'placeholder in',
	3302	source => "{in k1 3};",
	3303	sql_expected => "k1 in (?, ?, ?);",
	3304	columns_expected => [qw/k1 k1 k1/]
	3305	},
	3306
	3307	# Table name
	3308	{
	3309	name => 'placeholder with table name',
	3310	source => "{= a.k1} {= a.k2}",
	3311	sql_expected => "a.k1 = ? a.k2 = ?;",
	3312	columns_expected => [qw/a.k1 a.k2/]
	3313	},
	3314	{
	3315	name => 'placeholder in with table name',
	3316	source => "{in a.k1 2} {in b.k2 2}",
	3317	sql_expected => "a.k1 in (?, ?) b.k2 in (?, ?);",
	3318	columns_expected => [qw/a.k1 a.k1 b.k2 b.k2/]
	3319	},
	3320	{
	3321	name => 'not contain tag',
	3322	source => "aaa",
	3323	sql_expected => "aaa;",
	3324	columns_expected => [],
	3325	}
	3326	];
	3327
	3328	for (my $i = 0; $i < @$datas; $i++) {
	3329	my $data = $datas->[$i];
	3330	my $builder = DBIx::Custom->new->query_builder;
	3331	my $query = $builder->build_query($data->{source});
	3332	is($query->{sql}, $data->{sql_expected}, "$data->{name} : sql");
	3333	is_deeply($query->columns, $data->{columns_expected}, "$data->{name} : columns");
	3334	}
	3335
	3336	$builder = DBIx::Custom->new->query_builder;
	3337	$ret_val = $builder->register_tag(
	3338	p => sub {
	3339	my @args = @_;
	3340
	3341	my $expand = "? $args[0] $args[1]";
	3342	my $columns = [2];
	3343	return [$expand, $columns];
	3344	}
	3345	);
	3346
	3347	$query = $builder->build_query("{p a b}");
	3348	is($query->{sql}, "? a b;", "register_tag sql");
	3349	is_deeply($query->{columns}, [2], "register_tag columns");
	3350	isa_ok($ret_val, 'DBIx::Custom::QueryBuilder');
	3351
	3352	$builder = DBIx::Custom->new->query_builder;
	3353
	3354	eval{$builder->build_query('{? }')};
	3355	like($@, qr/\QColumn name must be specified in tag "{? }"/, "? not arguments");
	3356
	3357	eval{$builder->build_query("{a }")};
	3358	like($@, qr/\QTag "a" is not registered/, "tag not exist");
	3359
	3360	$builder->register_tag({
	3361	q => 'string'
	3362	});
	3363
	3364	eval{$builder->build_query("{q}", {})};
	3365	like($@, qr/Tag "q" must be sub reference/, "tag not code ref");
	3366
	3367	$builder->register_tag({
	3368	r => sub {}
	3369	});
	3370
	3371	eval{$builder->build_query("{r}")};
	3372	like($@, qr/\QTag "r" must return [STRING, ARRAY_REFERENCE]/, "tag return noting");
	3373
	3374	$builder->register_tag({
	3375	s => sub { return ["a", ""]}
	3376	});
	3377
	3378	eval{$builder->build_query("{s}")};
	3379	like($@, qr/\QTag "s" must return [STRING, ARRAY_REFERENCE]/, "tag return not array columns");
	3380
	3381	$builder->register_tag(
	3382	t => sub {return ["a", []]}
	3383	);
	3384
	3385
	3386	test 'General error case';
	3387	$builder = DBIx::Custom->new->query_builder;
	3388	$builder->register_tag(
	3389	a => sub {
	3390	return ["? ? ?", ['']];
	3391	}
	3392	);
	3393	eval{$builder->build_query("{a}")};
	3394	like($@, qr/\QPlaceholder count/, "placeholder count is invalid");
	3395
	3396
	3397	test 'Default tag Error case';
	3398	eval{$builder->build_query("{= }")};
	3399	like($@, qr/Column name must be specified in tag "{= }"/, "basic '=' : key not exist");
	3400
	3401	eval{$builder->build_query("{in }")};
	3402	like($@, qr/Column name and count of values must be specified in tag "{in }"/, "in : key not exist");
	3403
	3404	eval{$builder->build_query("{in a}")};
	3405	like($@, qr/\QColumn name and count of values must be specified in tag "{in }"/,
	3406	"in : key not exist");
	3407
	3408	eval{$builder->build_query("{in a r}")};
	3409	like($@, qr/\QColumn name and count of values must be specified in tag "{in }"/,
	3410	"in : key not exist");
	3411
	3412	test 'variouse source';
	3413	$source = "a {= b} c \\{ \\} {= \\{} {= \\}} d;";
	3414	$query = $builder->build_query($source);
	3415	is($query->sql, 'a b = ? c { } { = ? } = ? d;', "basic : 1");
	3416
	3417	$source = "abc;";
	3418	$query = $builder->build_query($source);
	3419	is($query->sql, 'abc;', "basic : 2");
	3420
	3421	$source = "{= a}";
	3422	$query = $builder->build_query($source);
	3423	is($query->sql, 'a = ?;', "only tag");
	3424
	3425	$source = "000;";
	3426	$query = $builder->build_query($source);
	3427	is($query->sql, '000;', "contain 0 value");
	3428
	3429	$source = "a {= b} }";
	3430	eval{$builder->build_query($source)};
	3431	like($@, qr/unexpected "}"/, "error : 1");
	3432
	3433	$source = "a {= {}";
	3434	eval{$builder->build_query($source)};
	3435	like($@, qr/unexpected "{"/, "error : 2");
	3436
cleanup test 0b08446 Yuki Kimoto authored on 2011-08-10	3437	test 'select() wrap option';
	3438	$dbi = DBIx::Custom->connect;
	3439	eval { $dbi->execute('drop table table1') };
	3440	$dbi->execute($create_table1);
	3441	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	3442	$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
	3443	$rows = $dbi->select(
	3444	table => 'table1',
	3445	column => 'key1',
	3446	wrap => ['select * from (', ') as t where key1 = 1']
	3447	)->all;
	3448	is_deeply($rows, [{key1 => 1}]);
	3449
cleanup test cec00a1 Yuki Kimoto authored on 2011-08-10	3450	eval {
	3451	$dbi->select(
	3452	table => 'table1',
	3453	column => 'key1',
	3454	wrap => 'select * from ('
	3455	)
	3456	};
	3457	like($@, qr/array/);
	3458
cleanup test 53a975a Yuki Kimoto authored on 2011-08-10	3459	test 'dbi method from model';
	3460	$dbi = MyDBI9->connect;
	3461	eval { $dbi->execute('drop table table1') };
	3462	$dbi->execute($create_table1);
	3463	$dbi->setup_model;
	3464	$model = $dbi->model('table1');
	3465	eval{$model->execute('select * from table1')};
	3466	ok(!$@);
	3467
	3468	test 'column table option';
	3469	$dbi = MyDBI9->connect;
	3470	eval { $dbi->execute('drop table table1') };
	3471	$dbi->execute($create_table1);
	3472	eval { $dbi->execute('drop table table2') };
	3473	$dbi->execute($create_table2);
	3474	$dbi->setup_model;
	3475	$dbi->execute('insert into table1 (key1, key2) values (1, 2);');
	3476	$dbi->execute('insert into table2 (key1, key3) values (1, 4);');
	3477	$model = $dbi->model('table1');
	3478	$result = $model->select(
	3479	column => [
	3480	$model->column('table2', {alias => 'table2_alias'})
	3481	],
	3482	where => {'table2_alias.key3' => 4}
	3483	);
	3484	is_deeply($result->one,
	3485	{'table2_alias.key1' => 1, 'table2_alias.key3' => 4});
	3486
	3487	$dbi->separator('__');
	3488	$result = $model->select(
	3489	column => [
	3490	$model->column('table2', {alias => 'table2_alias'})
	3491	],
	3492	where => {'table2_alias.key3' => 4}
	3493	);
	3494	is_deeply($result->one,
	3495	{'table2_alias__key1' => 1, 'table2_alias__key3' => 4});
	3496
	3497	$dbi->separator('-');
	3498	$result = $model->select(
	3499	column => [
	3500	$model->column('table2', {alias => 'table2_alias'})
	3501	],
	3502	where => {'table2_alias.key3' => 4}
	3503	);
	3504	is_deeply($result->one,
	3505	{'table2_alias-key1' => 1, 'table2_alias-key3' => 4});
	3506
	3507	test 'create_model';
	3508	$dbi = DBIx::Custom->connect;
	3509	eval { $dbi->execute('drop table table1') };
	3510	eval { $dbi->execute('drop table table2') };
	3511	$dbi->execute($create_table1);
	3512	$dbi->execute($create_table2);
	3513
	3514	$dbi->create_model(
	3515	table => 'table1',
	3516	join => [
	3517	'left outer join table2 on table1.key1 = table2.key1'
	3518	],
	3519	primary_key => ['key1']
	3520	);
	3521	$model2 = $dbi->create_model(
	3522	table => 'table2'
	3523	);
	3524	$dbi->create_model(
	3525	table => 'table3',
	3526	filter => [
	3527	key1 => {in => sub { uc $_[0] }}
	3528	]
	3529	);
	3530	$dbi->setup_model;
	3531	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	3532	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
	3533	$model = $dbi->model('table1');
	3534	$result = $model->select(
	3535	column => [$model->mycolumn, $model->column('table2')],
	3536	where => {'table1.key1' => 1}
	3537	);
	3538	is_deeply($result->one,
	3539	{key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
	3540	is_deeply($model2->select->one, {key1 => 1, key3 => 3});
	3541
	3542	test 'model method';
	3543	$dbi = DBIx::Custom->connect;
	3544	eval { $dbi->execute('drop table table2') };
	3545	$dbi->execute($create_table2);
	3546	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
	3547	$model = $dbi->create_model(
	3548	table => 'table2'
	3549	);
	3550	$model->method(foo => sub { shift->select(@_) });
	3551	is_deeply($model->foo->one, {key1 => 1, key3 => 3});
	3552
cleanup test 9074b3b Yuki Kimoto authored on 2011-08-10	3553	test 'update_param';
	3554	$dbi = DBIx::Custom->connect;
	3555	eval { $dbi->execute('drop table table1') };
	3556	$dbi->execute($create_table1_2);
	3557	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	3558	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	3559
	3560	$param = {key2 => 11};
	3561	$update_param = $dbi->update_param($param);
	3562	$sql = <<"EOS";
	3563	update table1 $update_param
	3564	where key1 = 1
	3565	EOS
	3566	$dbi->execute($sql, param => $param);
	3567	$result = $dbi->execute('select * from table1 order by key1;', table => 'table1');
	3568	$rows = $result->all;
	3569	is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
	3570	{key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}],
	3571	"basic");
	3572
	3573
	3574	$dbi = DBIx::Custom->connect;
	3575	eval { $dbi->execute('drop table table1') };
	3576	$dbi->execute($create_table1_2);
	3577	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	3578	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	3579
	3580	$param = {key2 => 11, key3 => 33};
	3581	$update_param = $dbi->update_param($param);
	3582	$sql = <<"EOS";
	3583	update table1 $update_param
	3584	where key1 = 1
	3585	EOS
	3586	$dbi->execute($sql, param => $param);
	3587	$result = $dbi->execute('select * from table1 order by key1;', table => 'table1');
	3588	$rows = $result->all;
	3589	is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 33, key4 => 4, key5 => 5},
	3590	{key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}],
	3591	"basic");
	3592
	3593	$dbi = DBIx::Custom->connect;
	3594	eval { $dbi->execute('drop table table1') };
	3595	$dbi->execute($create_table1_2);
	3596	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	3597	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	3598
	3599	$param = {key2 => 11, key3 => 33};
	3600	$update_param = $dbi->update_param($param, {no_set => 1});
	3601	$sql = <<"EOS";
	3602	update table1 set $update_param
	3603	where key1 = 1
	3604	EOS
	3605	$dbi->execute($sql, param => $param);
	3606	$result = $dbi->execute('select * from table1 order by key1;', table => 'table1');
	3607	$rows = $result->all;
	3608	is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 33, key4 => 4, key5 => 5},
	3609	{key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}],
	3610	"update param no_set");
	3611
	3612
	3613	eval { $dbi->update_param({";" => 1}) };
	3614	like($@, qr/not safety/);
	3615
	3616
	3617	test 'update_param';
	3618	$dbi = DBIx::Custom->connect;
	3619	eval { $dbi->execute('drop table table1') };
	3620	$dbi->execute($create_table1_2);
	3621	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
	3622	$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
	3623
	3624	$param = {key2 => 11};
	3625	$update_param = $dbi->assign_param($param);
	3626	$sql = <<"EOS";
	3627	update table1 set $update_param
	3628	where key1 = 1
	3629	EOS
	3630	$dbi->execute($sql, param => $param, table => 'table1');
	3631	$result = $dbi->execute('select * from table1 order by key1;');
	3632	$rows = $result->all;
	3633	is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
	3634	{key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}],
	3635	"basic");
	3636
cleanup test 2242f17 Yuki Kimoto authored on 2011-08-10	3637	test 'join';
	3638	$dbi = DBIx::Custom->connect;
	3639	eval { $dbi->execute('drop table table1') };
	3640	$dbi->execute($create_table1);
	3641	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	3642	$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
	3643	eval { $dbi->execute('drop table table2') };
	3644	$dbi->execute($create_table2);
	3645	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
	3646	eval { $dbi->execute('drop table table3') };
	3647	$dbi->execute('create table table3 (key3 int, key4 int);');
	3648	$dbi->insert(table => 'table3', param => {key3 => 5, key4 => 4});
	3649	$rows = $dbi->select(
	3650	table => 'table1',
	3651	column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
	3652	where => {'table1.key2' => 2},
	3653	join => ['left outer join table2 on table1.key1 = table2.key1']
	3654	)->all;
	3655	is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}]);
	3656
	3657	$rows = $dbi->select(
	3658	table => 'table1',
	3659	where => {'key1' => 1},
	3660	join => ['left outer join table2 on table1.key1 = table2.key1']
	3661	)->all;
	3662	is_deeply($rows, [{key1 => 1, key2 => 2}]);
	3663
	3664	eval {
	3665	$rows = $dbi->select(
	3666	table => 'table1',
	3667	column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
	3668	where => {'table1.key2' => 2},
	3669	join => {'table1.key1' => 'table2.key1'}
	3670	);
	3671	};
	3672	like ($@, qr/array/);
	3673
	3674	$rows = $dbi->select(
	3675	table => 'table1',
	3676	where => {'key1' => 1},
	3677	join => ['left outer join table2 on table1.key1 = table2.key1',
	3678	'left outer join table3 on table2.key3 = table3.key3']
	3679	)->all;
	3680	is_deeply($rows, [{key1 => 1, key2 => 2}]);
	3681
	3682	$rows = $dbi->select(
	3683	column => 'table3.key4 as table3__key4',
	3684	table => 'table1',
	3685	where => {'table1.key1' => 1},
	3686	join => ['left outer join table2 on table1.key1 = table2.key1',
	3687	'left outer join table3 on table2.key3 = table3.key3']
	3688	)->all;
	3689	is_deeply($rows, [{table3__key4 => 4}]);
	3690
	3691	$rows = $dbi->select(
	3692	column => 'table1.key1 as table1__key1',
	3693	table => 'table1',
	3694	where => {'table3.key4' => 4},
	3695	join => ['left outer join table2 on table1.key1 = table2.key1',
	3696	'left outer join table3 on table2.key3 = table3.key3']
	3697	)->all;
	3698	is_deeply($rows, [{table1__key1 => 1}]);
	3699
	3700	$dbi = DBIx::Custom->connect;
	3701	eval { $dbi->execute('drop table table1') };
	3702	$dbi->execute($create_table1);
	3703	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	3704	eval { $dbi->execute('drop table table2') };
	3705	$dbi->execute($create_table2);
	3706	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
	3707	$rows = $dbi->select(
	3708	table => 'table1',
	3709	column => "${q}table1$p.${q}key1$p as ${q}table1_key1$p, ${q}table2$p.${q}key1$p as ${q}table2_key1$p, ${q}key2$p, ${q}key3$p",
	3710	where => {'table1.key2' => 2},
	3711	join => ["left outer join ${q}table2$p on ${q}table1$p.${q}key1$p = ${q}table2$p.${q}key1$p"],
	3712	)->all;
	3713	is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}],
	3714	'quote');
	3715
	3716
	3717	$dbi = DBIx::Custom->connect;
	3718	eval { $dbi->execute('drop table table1') };
	3719	$dbi->execute($create_table1);
	3720	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	3721	$sql = <<"EOS";
	3722	left outer join (
	3723	select * from table1 as t1
	3724	where t1.key2 = (
	3725	select max(t2.key2) from table1 as t2
	3726	where t1.key1 = t2.key1
	3727	)
	3728	) as latest_table1 on table1.key1 = latest_table1.key1
	3729	EOS
	3730	$join = [$sql];
	3731	$rows = $dbi->select(
	3732	table => 'table1',
	3733	column => 'latest_table1.key1 as latest_table1__key1',
	3734	join => $join
	3735	)->all;
	3736	is_deeply($rows, [{latest_table1__key1 => 1}]);
	3737
	3738	$dbi = DBIx::Custom->connect;
	3739	eval { $dbi->execute('drop table table1') };
	3740	eval { $dbi->execute('drop table table2') };
	3741	$dbi->execute($create_table1);
	3742	$dbi->execute($create_table2);
	3743	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	3744	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
	3745	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
	3746	$result = $dbi->select(
	3747	table => 'table1',
	3748	join => [
	3749	"left outer join table2 on table2.key2 = '4' and table1.key1 = table2.key1"
	3750	]
	3751	);
	3752	is_deeply($result->all, [{key1 => 1, key2 => 2}]);
	3753	$result = $dbi->select(
	3754	table => 'table1',
	3755	column => [{table2 => ['key3']}],
	3756	join => [
	3757	"left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1"
	3758	]
	3759	);
	3760	is_deeply($result->all, [{'table2.key3' => 4}]);
	3761	$result = $dbi->select(
	3762	table => 'table1',
	3763	column => [{table2 => ['key3']}],
	3764	join => [
	3765	"left outer join table2 on table1.key1 = table2.key1 and table2.key3 = '4'"
	3766	]
	3767	);
	3768	is_deeply($result->all, [{'table2.key3' => 4}]);
	3769
	3770	$dbi = DBIx::Custom->connect;
	3771	eval { $dbi->execute('drop table table1') };
	3772	eval { $dbi->execute('drop table table2') };
	3773	$dbi->execute($create_table1);
	3774	$dbi->execute($create_table2);
	3775	$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
	3776	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
	3777	$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
	3778	$result = $dbi->select(
	3779	table => 'table1',
	3780	column => [{table2 => ['key3']}],
	3781	join => [
	3782	{
	3783	clause => "left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1",
	3784	table => ['table1', 'table2']
	3785	}
	3786	]
	3787	);
	3788	is_deeply($result->all, [{'table2.key3' => 4}]);
	3789
test cleanup cbe4e06 Yuki Kimoto authored on 2011-08-10	3790
cleanup test 9074b3b Yuki Kimoto authored on 2011-08-10	3791
cleanup test 08c3c54 Yuki Kimoto authored on 2011-08-08	3792	1;