kimoto/DBIx-Custom at 73f5463d359d8edc46da29289e9d9469f6bc740d ・ Gitprep

DBIx-Custom / t / next / common.t /

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

3898 lines | 129.484kb

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