ken/DBIx-Custom at d0094d45eb8de257342cf7da2c9f1a27268b3f4a ・ Gitprep

DBIx-Custom / t / basic.t /

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

3518 lines | 116.401kb

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