kimoto/DBIx-Custom at 7e185cecd49e3f0ac207c4ba3bc75b2fd43861bb ・ Gitprep

DBIx-Custom / t / dbix-custom-core-sqlite-quote.t /

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

3459 lines | 110.826kb

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