ken/DBIx-Custom at 058b0fdc72b9ec8a845f84e06a4b744748dc32e1 ・ Gitprep

DBIx-Custom / t / sqlite.t /

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

3562 lines | 115.061kb

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