kimoto/DBIx-Custom at 6a1f605579701f6a14c429b4b98f20a4e5cc26e6 ・ Gitprep

DBIx-Custom / t / basic.t /

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

3526 lines | 116.402kb

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