kimoto/DBIx-Custom at 621aa3924a8b80e21c4a23c277c561315ef9a5e7 ・ Gitprep

DBIx-Custom / t / common.t /

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

3502 lines | 112.641kb

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