ken/DBIx-Custom at 4067c452e8d12f8f496a9a2ec72260485928c148 ・ Gitprep

DBIx-Custom / t / basic.t /

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

3535 lines | 113.858kb

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