ken/DBIx-Custom at bd35c821096e6d8a612cfef9e3e66be77d8e2334 ・ Gitprep

DBIx-Custom / DBIx-Custom-0.1711 / t / basic.t /

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

3750 lines | 123.351kb

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