DBIx-Custom / t / basic.t /
Newer Older
3535 lines | 113.858kb
cleanup test
Yuki Kimoto authored on 2011-08-06
1
use Test::More;
2
use strict;
3
use warnings;
4

            
5
use utf8;
6
use Encode qw/encode_utf8 decode_utf8/;
7
use Data::Dumper;
8

            
9
$SIG{__WARN__} = sub { warn $_[0] unless $_[0] =~ /DEPRECATED/};
10

            
11
BEGIN {
12
    eval { require DBD::SQLite; 1 }
13
        or plan skip_all => 'DBD::SQLite required';
14
    eval { DBD::SQLite->VERSION >= 1.25 }
15
        or plan skip_all => 'DBD::SQLite >= 1.25 required';
16

            
17
    plan 'no_plan';
18
    use_ok('DBIx::Custom');
19
}
20

            
21
use FindBin;
22
use lib "$FindBin::Bin/basic";
23

            
24
# Function for test name
25
sub test { print "# $_[0]\n" }
26

            
27
# Constant varialbes for test
28
my $CREATE_TABLE = {
29
    0 => 'create table table1 (key1 char(255), key2 char(255));',
30
    1 => 'create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));',
31
    2 => 'create table table2 (key1 char(255), key3 char(255));',
32
    3 => 'create table table1 (key1 Date, key2 datetime);',
33
    4 => 'create table table3 (key3 int, key4 int);'
34
};
35

            
36
my $SELECT_SOURCES = {
37
    0 => 'select * from table1;'
38
};
39

            
40
my $DROP_TABLE = {
41
    0 => 'drop table table1'
42
};
43

            
44
my $NEW_ARGS = {
45
    0 => {dsn => 'dbi:SQLite:dbname=:memory:'}
46
};
47

            
48
# Variables
49
my $dbi;
50
my $sth;
51
my $source;
52
my @sources;
53
my $select_SOURCE;
54
my $insert_SOURCE;
55
my $update_SOURCE;
56
my $param;
57
my $params;
58
my $sql;
59
my $result;
60
my $row;
61
my @rows;
62
my $rows;
63
my $query;
64
my @queries;
65
my $select_query;
66
my $insert_query;
67
my $update_query;
68
my $ret_val;
69
my $infos;
70
my $model;
71
my $model2;
72
my $where;
73
my $update_param;
74
my $insert_param;
75
my $join;
76

            
77
# Prepare table
78
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
79
$dbi->execute($CREATE_TABLE->{0});
80
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
81
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
82

            
83
test 'DBIx::Custom::Result test';
84
$source = "select key1, key2 from table1";
85
$query = $dbi->create_query($source);
86
$result = $dbi->execute($query);
87

            
88
@rows = ();
89
while (my $row = $result->fetch) {
90
    push @rows, [@$row];
91
}
92
is_deeply(\@rows, [[1, 2], [3, 4]], "fetch");
93

            
94
$result = $dbi->execute($query);
95
@rows = ();
96
while (my $row = $result->fetch_hash) {
97
    push @rows, {%$row};
98
}
99
is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "fetch_hash");
100

            
101
$result = $dbi->execute($query);
102
$rows = $result->fetch_all;
103
is_deeply($rows, [[1, 2], [3, 4]], "fetch_all");
104

            
105
$result = $dbi->execute($query);
106
$rows = $result->fetch_hash_all;
107
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "all");
108

            
109
test 'Insert query return value';
110
$dbi->execute($DROP_TABLE->{0});
111
$dbi->execute($CREATE_TABLE->{0});
112
$source = "insert into table1 {insert_param key1 key2}";
113
$query = $dbi->execute($source, {}, query => 1);
114
$ret_val = $dbi->execute($query, param => {key1 => 1, key2 => 2});
115
ok($ret_val);
116

            
117

            
118
test 'Direct query';
119
$dbi->execute($DROP_TABLE->{0});
120
$dbi->execute($CREATE_TABLE->{0});
121
$insert_SOURCE = "insert into table1 {insert_param key1 key2}";
122
$dbi->execute($insert_SOURCE, param => {key1 => 1, key2 => 2});
123
$result = $dbi->execute($SELECT_SOURCES->{0});
124
$rows = $result->all;
125
is_deeply($rows, [{key1 => 1, key2 => 2}]);
126

            
127
test 'Filter basic';
128
$dbi->execute($DROP_TABLE->{0});
129
$dbi->execute($CREATE_TABLE->{0});
130
$dbi->register_filter(twice       => sub { $_[0] * 2}, 
131
                    three_times => sub { $_[0] * 3});
132

            
133
$insert_SOURCE  = "insert into table1 {insert_param key1 key2};";
134
$insert_query = $dbi->execute($insert_SOURCE, {}, query => 1);
135
$insert_query->filter({key1 => 'twice'});
136
$dbi->execute($insert_query, param => {key1 => 1, key2 => 2});
137
$result = $dbi->execute($SELECT_SOURCES->{0});
138
$rows = $result->filter({key2 => 'three_times'})->all;
139
is_deeply($rows, [{key1 => 2, key2 => 6}], "filter fetch_filter");
140
$dbi->execute($DROP_TABLE->{0});
141

            
142
test 'Filter in';
143
$dbi->execute($CREATE_TABLE->{0});
144
$insert_SOURCE  = "insert into table1 {insert_param key1 key2};";
145
$insert_query = $dbi->execute($insert_SOURCE, {}, query => 1);
146
$dbi->execute($insert_query, param => {key1 => 2, key2 => 4});
147
$select_SOURCE = "select * from table1 where {in table1.key1 2} and {in table1.key2 2}";
148
$select_query = $dbi->execute($select_SOURCE,{}, query => 1);
149
$select_query->filter({'table1.key1' => 'twice'});
150
$result = $dbi->execute($select_query, param => {'table1.key1' => [1,5], 'table1.key2' => [2,4]});
151
$rows = $result->all;
152
is_deeply($rows, [{key1 => 2, key2 => 4}], "filter");
153

            
154
test 'DBIx::Custom::SQLTemplate basic tag';
155
$dbi->execute($DROP_TABLE->{0});
156
$dbi->execute($CREATE_TABLE->{1});
157
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
158
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
159

            
160
$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";
161
$query = $dbi->execute($source, {}, query => 1);
162
$result = $dbi->execute($query, param => {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
163
$rows = $result->all;
164
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");
165

            
166
$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";
167
$query = $dbi->execute($source, {}, query => 1);
168
$result = $dbi->execute($query, {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
169
$rows = $result->all;
170
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");
171

            
172
$source = "select * from table1 where {<= key1} and {like key2};";
173
$query = $dbi->execute($source, {}, query => 1);
174
$result = $dbi->execute($query, param => {key1 => 1, key2 => '%2%'});
175
$rows = $result->all;
176
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag2");
177

            
178
test 'DIB::Custom::SQLTemplate in tag';
179
$dbi->execute($DROP_TABLE->{0});
180
$dbi->execute($CREATE_TABLE->{1});
181
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
182
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
183

            
184
$source = "select * from table1 where {in key1 2};";
185
$query = $dbi->execute($source, {}, query => 1);
186
$result = $dbi->execute($query, param => {key1 => [9, 1]});
187
$rows = $result->all;
188
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic");
189

            
190
test 'DBIx::Custom::SQLTemplate insert tag';
191
$dbi->execute("delete from table1");
192
$insert_SOURCE = 'insert into table1 {insert_param key1 key2 key3 key4 key5}';
193
$dbi->execute($insert_SOURCE, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
194

            
195
$result = $dbi->execute($SELECT_SOURCES->{0});
196
$rows = $result->all;
197
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic");
198

            
199
test 'DBIx::Custom::SQLTemplate update tag';
200
$dbi->execute("delete from table1");
201
$insert_SOURCE = "insert into table1 {insert_param key1 key2 key3 key4 key5}";
202
$dbi->execute($insert_SOURCE, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
203
$dbi->execute($insert_SOURCE, param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
204

            
205
$update_SOURCE = 'update table1 {update_param key1 key2 key3 key4} where {= key5}';
206
$dbi->execute($update_SOURCE, param => {key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5});
207

            
208
$result = $dbi->execute($SELECT_SOURCES->{0});
209
$rows = $result->all;
210
is_deeply($rows, [{key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5},
211
                  {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}], "basic");
212

            
213

            
214
test 'Named placeholder';
215
$dbi->execute($DROP_TABLE->{0});
216
$dbi->execute($CREATE_TABLE->{1});
217
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
218
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
219

            
220
$source = "select * from table1 where key1 = :key1 and key2 = :key2";
221
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
222
$rows = $result->all;
223
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
224

            
225
$source = "select * from table1 where key1 = \n:key1\n and key2 = :key2";
226
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
227
$rows = $result->all;
228
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
229

            
230
$source = "select * from table1 where key1 = :key1 or key1 = :key1";
231
$result = $dbi->execute($source, param => {key1 => [1, 2]});
232
$rows = $result->all;
233
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
234

            
235
$source = "select * from table1 where key1 = :table1.key1 and key2 = :table1.key2";
236
$result = $dbi->execute(
237
    $source,
238
    param => {'table1.key1' => 1, 'table1.key2' => 1},
239
    filter => {'table1.key2' => sub { $_[0] * 2 }}
240
);
241
$rows = $result->all;
242
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
243

            
244
$dbi->execute($DROP_TABLE->{0});
245
$dbi->execute($CREATE_TABLE->{0});
246
$dbi->insert(table => 'table1', param => {key1 => '2011-10-14 12:19:18', key2 => 2});
247
$source = "select * from table1 where key1 = '2011-10-14 12:19:18' and key2 = :key2";
248
$result = $dbi->execute(
249
    $source,
250
    param => {'key2' => 2},
251
);
252

            
253
$rows = $result->all;
254
is_deeply($rows, [{key1 => '2011-10-14 12:19:18', key2 => 2}]);
255

            
256
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
257
$dbi->execute($CREATE_TABLE->{0});
258
$dbi->insert(table => 'table1', param => {key1 => 'a:b c:d', key2 => 2});
259
$source = "select * from table1 where key1 = 'a\\:b c\\:d' and key2 = :key2";
260
$result = $dbi->execute(
261
    $source,
262
    param => {'key2' => 2},
263
);
264
$rows = $result->all;
265
is_deeply($rows, [{key1 => 'a:b c:d', key2 => 2}]);
266

            
267

            
268
test 'Error case';
269
eval {DBIx::Custom->connect(dsn => 'dbi:SQLit')};
270
ok($@, "connect error");
271

            
272
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
273
eval{$dbi->execute("{p }", {}, query => 1)};
274
ok($@, "create_query invalid SQL template");
275

            
276
test 'insert';
277
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
278
$dbi->execute($CREATE_TABLE->{0});
279
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
280
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
281
$result = $dbi->execute($SELECT_SOURCES->{0});
282
$rows   = $result->all;
283
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
284

            
285
$dbi->execute('delete from table1');
286
$dbi->register_filter(
287
    twice       => sub { $_[0] * 2 },
288
    three_times => sub { $_[0] * 3 }
289
);
290
$dbi->default_bind_filter('twice');
291
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => 'three_times'});
292
$result = $dbi->execute($SELECT_SOURCES->{0});
293
$rows   = $result->all;
294
is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
295
$dbi->default_bind_filter(undef);
296

            
297
$dbi->execute($DROP_TABLE->{0});
298
$dbi->execute($CREATE_TABLE->{0});
299
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, append => '   ');
300
$rows = $dbi->select(table => 'table1')->all;
301
is_deeply($rows, [{key1 => 1, key2 => 2}], 'insert append');
302

            
303
eval{$dbi->insert(table => 'table1', noexist => 1)};
304
like($@, qr/noexist/, "invalid");
305

            
306
eval{$dbi->insert(table => 'table', param => {';' => 1})};
307
like($@, qr/safety/);
308

            
309
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
310
$dbi->quote('"');
311
$dbi->execute('create table "table" ("select")');
312
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
313
$dbi->insert(table => 'table', param => {select => 1});
314
$result = $dbi->execute('select * from "table"');
315
$rows   = $result->all;
316
is_deeply($rows, [{select => 2}], "reserved word");
317

            
318
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
319
$dbi->execute($CREATE_TABLE->{0});
320
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
321
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
322
$result = $dbi->execute($SELECT_SOURCES->{0});
323
$rows   = $result->all;
324
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
325

            
326
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
327
$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");
328
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
329
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 4}, prefix => 'or replace');
330
$result = $dbi->execute($SELECT_SOURCES->{0});
331
$rows   = $result->all;
332
is_deeply($rows, [{key1 => 1, key2 => 4}], "basic");
333

            
334
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
335
$dbi->execute($CREATE_TABLE->{0});
336
$dbi->insert(table => 'table1', param => {key1 => \"'1'", key2 => 2});
337
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
338
$result = $dbi->execute($SELECT_SOURCES->{0});
339
$rows   = $result->all;
340
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
341

            
342
test 'update';
343
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
344
$dbi->execute($CREATE_TABLE->{1});
345
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
346
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
347
$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1});
348
$result = $dbi->execute($SELECT_SOURCES->{0});
349
$rows   = $result->all;
350
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
351
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
352
                  "basic");
353
                  
354
$dbi->execute("delete from table1");
355
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
356
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
357
$dbi->update(table => 'table1', param => {key2 => 12}, where => {key2 => 2, key3 => 3});
358
$result = $dbi->execute($SELECT_SOURCES->{0});
359
$rows   = $result->all;
360
is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
361
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
362
                  "update key same as search key");
363

            
364
$dbi->update(table => 'table1', param => {key2 => [12]}, where => {key2 => 2, key3 => 3});
365
$result = $dbi->execute($SELECT_SOURCES->{0});
366
$rows   = $result->all;
367
is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
368
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
369
                  "update key same as search key : param is array ref");
370

            
371
$dbi->execute("delete from table1");
372
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
373
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
374
$dbi->register_filter(twice => sub { $_[0] * 2 });
375
$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1},
376
              filter => {key2 => sub { $_[0] * 2 }});
377
$result = $dbi->execute($SELECT_SOURCES->{0});
378
$rows   = $result->all;
379
is_deeply($rows, [{key1 => 1, key2 => 22, key3 => 3, key4 => 4, key5 => 5},
380
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
381
                  "filter");
382

            
383
$result = $dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1}, append => '   ');
384

            
385
eval{$dbi->update(table => 'table1', where => {key1 => 1}, noexist => 1)};
386
like($@, qr/noexist/, "invalid");
387

            
388
eval{$dbi->update(table => 'table1')};
389
like($@, qr/where/, "not contain where");
390

            
391
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
392
$dbi->execute($CREATE_TABLE->{0});
393
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
394
$where = $dbi->where;
395
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
396
$where->param({key1 => 1, key2 => 2});
397
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
398
$result = $dbi->select(table => 'table1');
399
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
400

            
401
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
402
$dbi->execute($CREATE_TABLE->{0});
403
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
404
$dbi->update(
405
    table => 'table1',
406
    param => {key1 => 3},
407
    where => [
408
        ['and', 'key1 = :key1', 'key2 = :key2'],
409
        {key1 => 1, key2 => 2}
410
    ]
411
);
412
$result = $dbi->select(table => 'table1');
413
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
414

            
415
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
416
$dbi->execute($CREATE_TABLE->{0});
417
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
418
$where = $dbi->where;
419
$where->clause(['and', 'key2 = :key2']);
420
$where->param({key2 => 2});
421
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
422
$result = $dbi->select(table => 'table1');
423
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
424

            
425
eval{$dbi->update(table => 'table1', param => {';' => 1})};
426
like($@, qr/safety/);
427

            
428
eval{$dbi->update(table => 'table1', param => {'key1' => 1}, where => {';' => 1})};
429
like($@, qr/safety/);
430

            
431
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
432
$dbi->quote('"');
433
$dbi->execute('create table "table" ("select", "update")');
434
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
435
$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
436
$dbi->insert(table => 'table', param => {select => 1});
437
$dbi->update(table => 'table', where => {select => 1}, param => {update => 2});
438
$result = $dbi->execute('select * from "table"');
439
$rows   = $result->all;
440
is_deeply($rows, [{select => 2, update => 6}], "reserved word");
441

            
442
eval {$dbi->update_all(table => 'table', param => {';' => 2}) };
443
like($@, qr/safety/);
444

            
445
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
446
$dbi->reserved_word_quote('"');
447
$dbi->execute('create table "table" ("select", "update")');
448
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
449
$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
450
$dbi->insert(table => 'table', param => {select => 1});
451
$dbi->update(table => 'table', where => {'table.select' => 1}, param => {update => 2});
452
$result = $dbi->execute('select * from "table"');
453
$rows   = $result->all;
454
is_deeply($rows, [{select => 2, update => 6}], "reserved word");
455

            
456
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
457
$dbi->execute($CREATE_TABLE->{1});
458
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
459
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
460
$dbi->update({key2 => 11}, table => 'table1', where => {key1 => 1});
461
$result = $dbi->execute($SELECT_SOURCES->{0});
462
$rows   = $result->all;
463
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
464
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
465
                  "basic");
466

            
467
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
468
$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");
469
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
470
$dbi->update(table => 'table1', param => {key2 => 4},
471
  where => {key1 => 1}, prefix => 'or replace');
472
$result = $dbi->execute($SELECT_SOURCES->{0});
473
$rows   = $result->all;
474
is_deeply($rows, [{key1 => 1, key2 => 4}], "basic");
475

            
476
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
477
$dbi->execute($CREATE_TABLE->{1});
478
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
479
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
480
$dbi->update(table => 'table1', param => {key2 => \"'11'"}, where => {key1 => 1});
481
$result = $dbi->execute($SELECT_SOURCES->{0});
482
$rows   = $result->all;
483
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
484
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
485
                  "basic");
486

            
487
test 'update_all';
488
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
489
$dbi->execute($CREATE_TABLE->{1});
490
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
491
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
492
$dbi->register_filter(twice => sub { $_[0] * 2 });
493
$dbi->update_all(table => 'table1', param => {key2 => 10}, filter => {key2 => 'twice'});
494
$result = $dbi->execute($SELECT_SOURCES->{0});
495
$rows   = $result->all;
496
is_deeply($rows, [{key1 => 1, key2 => 20, key3 => 3, key4 => 4, key5 => 5},
497
                  {key1 => 6, key2 => 20, key3 => 8, key4 => 9, key5 => 10}],
498
                  "filter");
499

            
500

            
501
test 'delete';
502
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
503
$dbi->execute($CREATE_TABLE->{0});
504
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
505
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
506
$dbi->delete(table => 'table1', where => {key1 => 1});
507
$result = $dbi->execute($SELECT_SOURCES->{0});
508
$rows   = $result->all;
509
is_deeply($rows, [{key1 => 3, key2 => 4}], "basic");
510

            
511
$dbi->execute("delete from table1;");
512
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
513
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
514
$dbi->register_filter(twice => sub { $_[0] * 2 });
515
$dbi->delete(table => 'table1', where => {key2 => 1}, filter => {key2 => 'twice'});
516
$result = $dbi->execute($SELECT_SOURCES->{0});
517
$rows   = $result->all;
518
is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
519

            
520
$dbi->delete(table => 'table1', where => {key1 => 1}, append => '   ');
521

            
522
$dbi->delete_all(table => 'table1');
523
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
524
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
525
$dbi->delete(table => 'table1', where => {key1 => 1, key2 => 2});
526
$rows = $dbi->select(table => 'table1')->all;
527
is_deeply($rows, [{key1 => 3, key2 => 4}], "delete multi key");
528

            
529
eval{$dbi->delete(table => 'table1', where => {key1 => 1}, noexist => 1)};
530
like($@, qr/noexist/, "invalid");
531

            
532
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
533
$dbi->execute($CREATE_TABLE->{0});
534
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
535
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
536
$where = $dbi->where;
537
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
538
$where->param({ke1 => 1, key2 => 2});
539
$dbi->delete(table => 'table1', where => $where);
540
$result = $dbi->select(table => 'table1');
541
is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
542

            
543
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
544
$dbi->execute($CREATE_TABLE->{0});
545
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
546
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
547
$dbi->delete(
548
    table => 'table1',
549
    where => [
550
        ['and', 'key1 = :key1', 'key2 = :key2'],
551
        {ke1 => 1, key2 => 2}
552
    ]
553
);
554
$result = $dbi->select(table => 'table1');
555
is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
556

            
557
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
558
$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");
559
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
560
$dbi->delete(table => 'table1', where => {key1 => 1}, prefix => '    ');
561
$result = $dbi->execute($SELECT_SOURCES->{0});
562
$rows   = $result->all;
563
is_deeply($rows, [], "basic");
564

            
565
test 'delete error';
566
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
567
$dbi->execute($CREATE_TABLE->{0});
568
eval{$dbi->delete(table => 'table1')};
569
like($@, qr/"where" must be specified/,
570
         "where key-value pairs not specified");
571

            
572
eval{$dbi->delete(table => 'table1', where => {';' => 1})};
573
like($@, qr/safety/);
574

            
575
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
576
$dbi->quote('"');
577
$dbi->execute('create table "table" ("select", "update")');
578
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
579
$dbi->insert(table => 'table', param => {select => 1});
580
$dbi->delete(table => 'table', where => {select => 1});
581
$result = $dbi->execute('select * from "table"');
582
$rows   = $result->all;
583
is_deeply($rows, [], "reserved word");
584

            
585
test 'delete_all';
586
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
587
$dbi->execute($CREATE_TABLE->{0});
588
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
589
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
590
$dbi->delete_all(table => 'table1');
591
$result = $dbi->execute($SELECT_SOURCES->{0});
592
$rows   = $result->all;
593
is_deeply($rows, [], "basic");
594

            
595

            
596
test 'select';
597
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
598
$dbi->execute($CREATE_TABLE->{0});
599
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
600
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
601
$rows = $dbi->select(table => 'table1')->all;
602
is_deeply($rows, [{key1 => 1, key2 => 2},
603
                  {key1 => 3, key2 => 4}], "table");
604

            
605
$rows = $dbi->select(table => 'table1', column => ['key1'])->all;
606
is_deeply($rows, [{key1 => 1}, {key1 => 3}], "table and columns and where key");
607

            
608
$rows = $dbi->select(table => 'table1', where => {key1 => 1})->all;
609
is_deeply($rows, [{key1 => 1, key2 => 2}], "table and columns and where key");
610

            
611
$rows = $dbi->select(table => 'table1', column => ['key1'], where => {key1 => 3})->all;
612
is_deeply($rows, [{key1 => 3}], "table and columns and where key");
613

            
614
$rows = $dbi->select(table => 'table1', append => "order by key1 desc limit 1")->all;
615
is_deeply($rows, [{key1 => 3, key2 => 4}], "append statement");
616

            
617
$dbi->register_filter(decrement => sub { $_[0] - 1 });
618
$rows = $dbi->select(table => 'table1', where => {key1 => 2}, filter => {key1 => 'decrement'})
619
            ->all;
620
is_deeply($rows, [{key1 => 1, key2 => 2}], "filter");
621

            
622
$dbi->execute($CREATE_TABLE->{2});
623
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
624
$rows = $dbi->select(
625
    table => [qw/table1 table2/],
626
    column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
627
    where   => {'table1.key2' => 2},
628
    relation  => {'table1.key1' => 'table2.key1'}
629
)->all;
630
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : exists where");
631

            
632
$rows = $dbi->select(
633
    table => [qw/table1 table2/],
634
    column => ['table1.key1 as table1_key1', 'table2.key1 as table2_key1', 'key2', 'key3'],
635
    relation  => {'table1.key1' => 'table2.key1'}
636
)->all;
637
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : no exists where");
638

            
639
eval{$dbi->select(table => 'table1', noexist => 1)};
640
like($@, qr/noexist/, "invalid");
641

            
642
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
643
$dbi->quote('"');
644
$dbi->execute('create table "table" ("select", "update")');
645
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
646
$dbi->insert(table => 'table', param => {select => 1, update => 2});
647
$result = $dbi->select(table => 'table', where => {select => 1});
648
$rows   = $result->all;
649
is_deeply($rows, [{select => 2, update => 2}], "reserved word");
650

            
651
test 'fetch filter';
652
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
653
$dbi->register_filter(
654
    twice       => sub { $_[0] * 2 },
655
    three_times => sub { $_[0] * 3 }
656
);
657
$dbi->default_fetch_filter('twice');
658
$dbi->execute($CREATE_TABLE->{0});
659
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
660
$result = $dbi->select(table => 'table1');
661
$result->filter({key1 => 'three_times'});
662
$row = $result->one;
663
is_deeply($row, {key1 => 3, key2 => 4}, "default_fetch_filter and filter");
664

            
665
test 'filters';
666
$dbi = DBIx::Custom->new;
667

            
668
is($dbi->filters->{decode_utf8}->(encode_utf8('あ')),
669
   'あ', "decode_utf8");
670

            
671
is($dbi->filters->{encode_utf8}->('あ'),
672
   encode_utf8('あ'), "encode_utf8");
673

            
674
test 'transaction';
675
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
676
$dbi->execute($CREATE_TABLE->{0});
677
$dbi->dbh->begin_work;
678
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
679
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
680
$dbi->dbh->commit;
681
$result = $dbi->select(table => 'table1');
682
is_deeply(scalar $result->all, [{key1 => 1, key2 => 2}, {key1 => 2, key2 => 3}],
683
          "commit");
684

            
685
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
686
$dbi->execute($CREATE_TABLE->{0});
687
$dbi->dbh->begin_work(0);
688
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
689
$dbi->dbh->rollback;
690

            
691
$result = $dbi->select(table => 'table1');
692
ok(! $result->fetch_first, "rollback");
693

            
694
test 'cache';
695
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
696
$dbi->cache(1);
697
$dbi->execute($CREATE_TABLE->{0});
698
$source = 'select * from table1 where key1 = :key1 and key2 = :key2;';
699
$dbi->execute($source, {}, query => 1);
700
is_deeply($dbi->{_cached}->{$source}, 
701
          {sql => "select * from table1 where key1 = ? and key2 = ?;", columns => ['key1', 'key2'], tables => []}, "cache");
702

            
703
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
704
$dbi->execute($CREATE_TABLE->{0});
705
$dbi->{_cached} = {};
706
$dbi->cache(0);
707
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
708
is(scalar keys %{$dbi->{_cached}}, 0, 'not cache');
709

            
710
test 'execute';
711
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
712
$dbi->execute($CREATE_TABLE->{0});
713
{
714
    local $Carp::Verbose = 0;
715
    eval{$dbi->execute('select * frm table1')};
716
    like($@, qr/\Qselect * frm table1;/, "fail prepare");
717
    like($@, qr/\.t /, "fail : not verbose");
718
}
719
{
720
    local $Carp::Verbose = 1;
721
    eval{$dbi->execute('select * frm table1')};
722
    like($@, qr/Custom.*\.t /s, "fail : verbose");
723
}
724

            
725
eval{$dbi->execute('select * from table1', no_exists => 1)};
726
like($@, qr/wrong/, "invald SQL");
727

            
728
$query = $dbi->execute('select * from table1 where key1 = :key1', {}, query => 1);
729
$dbi->dbh->disconnect;
730
eval{$dbi->execute($query, param => {key1 => {a => 1}})};
731
ok($@, "execute fail");
732

            
733
{
734
    local $Carp::Verbose = 0;
735
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
736
    like($@, qr/\Q.t /, "caller spec : not vebose");
737
}
738
{
739
    local $Carp::Verbose = 1;
740
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
741
    like($@, qr/QueryBuilder.*\.t /s, "caller spec : not vebose");
742
}
743

            
744

            
745
test 'transaction';
746
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
747
$dbi->execute($CREATE_TABLE->{0});
748

            
749
$dbi->begin_work;
750

            
751
eval {
752
    $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
753
    die "Error";
754
    $dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
755
};
756

            
757
$dbi->rollback if $@;
758

            
759
$result = $dbi->select(table => 'table1');
760
$rows = $result->all;
761
is_deeply($rows, [], "rollback");
762

            
763
$dbi->begin_work;
764

            
765
eval {
766
    $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
767
    $dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
768
};
769

            
770
$dbi->commit unless $@;
771

            
772
$result = $dbi->select(table => 'table1');
773
$rows = $result->all;
774
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "commit");
775

            
776
$dbi->dbh->{AutoCommit} = 0;
777
eval{ $dbi->begin_work };
778
ok($@, "exception");
779
$dbi->dbh->{AutoCommit} = 1;
780

            
781

            
782
test 'method';
783
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
784
$dbi->method(
785
    one => sub { 1 }
786
);
787
$dbi->method(
788
    two => sub { 2 }
789
);
790
$dbi->method({
791
    twice => sub {
792
        my $self = shift;
793
        return $_[0] * 2;
794
    }
795
});
796

            
797
is($dbi->one, 1, "first");
798
is($dbi->two, 2, "second");
799
is($dbi->twice(5), 10 , "second");
800

            
801
eval {$dbi->XXXXXX};
802
ok($@, "not exists");
803

            
804
test 'out filter';
805
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
806
$dbi->execute($CREATE_TABLE->{0});
807
$dbi->register_filter(twice => sub { $_[0] * 2 });
808
$dbi->register_filter(three_times => sub { $_[0] * 3});
809
$dbi->apply_filter(
810
    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 
811
              'key2' => {out => 'three_times', in => 'twice'});
812
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
813
$result = $dbi->execute($SELECT_SOURCES->{0});
814
$row   = $result->fetch_hash_first;
815
is_deeply($row, {key1 => 2, key2 => 6}, "insert");
816
$result = $dbi->select(table => 'table1');
817
$row   = $result->one;
818
is_deeply($row, {key1 => 6, key2 => 12}, "insert");
819

            
820
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
821
$dbi->execute($CREATE_TABLE->{0});
822
$dbi->register_filter(twice => sub { $_[0] * 2 });
823
$dbi->register_filter(three_times => sub { $_[0] * 3});
824
$dbi->apply_filter(
825
    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 
826
              'key2' => {out => 'three_times', in => 'twice'});
827
$dbi->apply_filter(
828
    'table1', 'key1' => {out => undef}
829
); 
830
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
831
$result = $dbi->execute($SELECT_SOURCES->{0});
832
$row   = $result->one;
833
is_deeply($row, {key1 => 1, key2 => 6}, "insert");
834

            
835
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
836
$dbi->execute($CREATE_TABLE->{0});
837
$dbi->register_filter(twice => sub { $_[0] * 2 });
838
$dbi->apply_filter(
839
    'table1', 'key1' => {out => 'twice', in => 'twice'}
840
);
841
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => undef});
842
$dbi->update(table => 'table1', param => {key1 => 2}, where => {key2 => 2});
843
$result = $dbi->execute($SELECT_SOURCES->{0});
844
$row   = $result->one;
845
is_deeply($row, {key1 => 4, key2 => 2}, "update");
846

            
847
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
848
$dbi->execute($CREATE_TABLE->{0});
849
$dbi->register_filter(twice => sub { $_[0] * 2 });
850
$dbi->apply_filter(
851
    'table1', 'key1' => {out => 'twice', in => 'twice'}
852
);
853
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1=> undef});
854
$dbi->delete(table => 'table1', where => {key1 => 1});
855
$result = $dbi->execute($SELECT_SOURCES->{0});
856
$rows   = $result->all;
857
is_deeply($rows, [], "delete");
858

            
859
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
860
$dbi->execute($CREATE_TABLE->{0});
861
$dbi->register_filter(twice => sub { $_[0] * 2 });
862
$dbi->apply_filter(
863
    'table1', 'key1' => {out => 'twice', in => 'twice'}
864
);
865
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
866
$result = $dbi->select(table => 'table1', where => {key1 => 1});
867
$result->filter({'key2' => 'twice'});
868
$rows   = $result->all;
869
is_deeply($rows, [{key1 => 4, key2 => 4}], "select");
870

            
871
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
872
$dbi->execute($CREATE_TABLE->{0});
873
$dbi->register_filter(twice => sub { $_[0] * 2 });
874
$dbi->apply_filter(
875
    'table1', 'key1' => {out => 'twice', in => 'twice'}
876
);
877
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
878
$result = $dbi->execute("select * from table1 where key1 = :key1 and key2 = :key2;",
879
                        param => {key1 => 1, key2 => 2},
880
                        table => ['table1']);
881
$rows   = $result->all;
882
is_deeply($rows, [{key1 => 4, key2 => 2}], "execute");
883

            
884
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
885
$dbi->execute($CREATE_TABLE->{0});
886
$dbi->register_filter(twice => sub { $_[0] * 2 });
887
$dbi->apply_filter(
888
    'table1', 'key1' => {out => 'twice', in => 'twice'}
889
);
890
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
891
$result = $dbi->execute("select * from {table table1} where key1 = :key1 and key2 = :key2;",
892
                        param => {key1 => 1, key2 => 2});
893
$rows   = $result->all;
894
is_deeply($rows, [{key1 => 4, key2 => 2}], "execute table tag");
895

            
896
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
897
$dbi->execute($CREATE_TABLE->{0});
898
$dbi->execute($CREATE_TABLE->{2});
899
$dbi->register_filter(twice => sub { $_[0] * 2 });
900
$dbi->register_filter(three_times => sub { $_[0] * 3 });
901
$dbi->apply_filter(
902
    'table1', 'key2' => {out => 'twice', in => 'twice'}
903
);
904
$dbi->apply_filter(
905
    'table2', 'key3' => {out => 'three_times', in => 'three_times'}
906
);
907
$dbi->insert(table => 'table1', param => {key1 => 5, key2 => 2}, filter => {key2 => undef});
908
$dbi->insert(table => 'table2', param => {key1 => 5, key3 => 6}, filter => {key3 => undef});
909
$result = $dbi->select(
910
     table => ['table1', 'table2'],
911
     column => ['key2', 'key3'],
912
     where => {'table1.key2' => 1, 'table2.key3' => 2}, relation => {'table1.key1' => 'table2.key1'});
913

            
914
$result->filter({'key2' => 'twice'});
915
$rows   = $result->all;
916
is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join");
917

            
918
$result = $dbi->select(
919
     table => ['table1', 'table2'],
920
     column => ['key2', 'key3'],
921
     where => {'key2' => 1, 'key3' => 2}, relation => {'table1.key1' => 'table2.key1'});
922

            
923
$result->filter({'key2' => 'twice'});
924
$rows   = $result->all;
925
is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join : omit");
926

            
927
test 'each_column';
928
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
929
$dbi->execute($CREATE_TABLE->{2});
930
$dbi->execute($CREATE_TABLE->{3});
931

            
932
$infos = [];
933
$dbi->each_column(sub {
934
    my ($self, $table, $column, $cinfo) = @_;
935
    
936
    if ($table =~ /^table/) {
937
         my $info = [$table, $column, $cinfo->{COLUMN_NAME}];
938
         push @$infos, $info;
939
    }
940
});
941
$infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
942
is_deeply($infos, 
943
    [
944
        ['table1', 'key1', 'key1'],
945
        ['table1', 'key2', 'key2'],
946
        ['table2', 'key1', 'key1'],
947
        ['table2', 'key3', 'key3']
948
    ]
949
    
950
);
951
test 'each_table';
952
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
953
$dbi->execute($CREATE_TABLE->{2});
954
$dbi->execute($CREATE_TABLE->{3});
955

            
956
$infos = [];
957
$dbi->each_table(sub {
958
    my ($self, $table, $table_info) = @_;
959
    
960
    if ($table =~ /^table/) {
961
         my $info = [$table, $table_info->{TABLE_NAME}];
962
         push @$infos, $info;
963
    }
964
});
965
$infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
966
is_deeply($infos, 
967
    [
968
        ['table1', 'table1'],
969
        ['table2', 'table2'],
970
    ]
971
);
972

            
973
test 'limit';
974
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
975
$dbi->execute($CREATE_TABLE->{0});
976
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
977
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 4});
978
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 6});
979
$dbi->register_tag(
980
    limit => sub {
981
        my ($count, $offset) = @_;
982
        
983
        my $s = '';
984
        $s .= "limit $count";
985
        $s .= " offset $offset" if defined $offset;
986
        
987
        return [$s, []];
988
    }
989
);
990
$rows = $dbi->select(
991
  table => 'table1',
992
  where => {key1 => 1},
993
  append => "order by key2 {limit 1 0}"
994
)->all;
995
is_deeply($rows, [{key1 => 1, key2 => 2}]);
996
$rows = $dbi->select(
997
  table => 'table1',
998
  where => {key1 => 1},
999
  append => "order by key2 {limit 2 1}"
1000
)->all;
1001
is_deeply($rows, [{key1 => 1, key2 => 4},{key1 => 1, key2 => 6}]);
1002
$rows = $dbi->select(
1003
  table => 'table1',
1004
  where => {key1 => 1},
1005
  append => "order by key2 {limit 1}"
1006
)->all;
1007
is_deeply($rows, [{key1 => 1, key2 => 2}]);
1008

            
1009
test 'connect super';
1010
{
1011
    package MyDBI;
1012
    
1013
    use base 'DBIx::Custom';
1014
    sub connect {
1015
        my $self = shift->SUPER::connect(@_);
1016
        
1017
        return $self;
1018
    }
1019
    
1020
    sub new {
1021
        my $self = shift->SUPER::new(@_);
1022
        
1023
        return $self;
1024
    }
1025
}
1026

            
1027
$dbi = MyDBI->connect($NEW_ARGS->{0});
1028
$dbi->execute($CREATE_TABLE->{0});
1029
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1030
is($dbi->select(table => 'table1')->one->{key1}, 1);
1031

            
1032
$dbi = MyDBI->new($NEW_ARGS->{0});
1033
$dbi->connect;
1034
$dbi->execute($CREATE_TABLE->{0});
1035
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1036
is($dbi->select(table => 'table1')->one->{key1}, 1);
1037

            
1038
{
1039
    package MyDBI2;
1040
    
1041
    use base 'DBIx::Custom';
1042
    sub connect {
1043
        my $self = shift->SUPER::new(@_);
1044
        $self->connect;
1045
        
1046
        return $self;
1047
    }
1048
}
1049

            
1050
$dbi = MyDBI->connect($NEW_ARGS->{0});
1051
$dbi->execute($CREATE_TABLE->{0});
1052
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1053
is($dbi->select(table => 'table1')->one->{key1}, 1);
1054

            
1055
test 'end_filter';
1056
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1057
$dbi->execute($CREATE_TABLE->{0});
1058
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1059
$result = $dbi->select(table => 'table1');
1060
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1061
$result->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 });
1062
$row = $result->fetch_first;
1063
is_deeply($row, [6, 40]);
1064

            
1065
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1066
$dbi->execute($CREATE_TABLE->{0});
1067
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1068
$result = $dbi->select(table => 'table1');
1069
$result->filter([qw/key1 key2/] => sub { $_[0] * 2 });
1070
$result->end_filter([[qw/key1 key2/] => sub { $_[0] * 3 }]);
1071
$row = $result->fetch_first;
1072
is_deeply($row, [6, 12]);
1073

            
1074
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1075
$dbi->execute($CREATE_TABLE->{0});
1076
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1077
$result = $dbi->select(table => 'table1');
1078
$result->filter([[qw/key1 key2/] => sub { $_[0] * 2 }]);
1079
$result->end_filter([qw/key1 key2/] => sub { $_[0] * 3 });
1080
$row = $result->fetch_first;
1081
is_deeply($row, [6, 12]);
1082

            
1083
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1084
$result = $dbi->select(table => 'table1');
1085
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1086
$result->end_filter({key1 => sub { $_[0] * 3 }, key2 => 'five_times' });
1087
$row = $result->one;
1088
is_deeply($row, {key1 => 6, key2 => 40});
1089

            
1090
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1091
$dbi->apply_filter('table1',
1092
    key1 => {end => sub { $_[0] * 3 } },
1093
    key2 => {end => 'five_times'}
1094
);
1095
$result = $dbi->select(table => 'table1');
1096
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1097
$row = $result->one;
1098
is_deeply($row, {key1 => 6, key2 => 40}, 'apply_filter');
1099

            
1100
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1101
$dbi->apply_filter('table1',
1102
    key1 => {end => sub { $_[0] * 3 } },
1103
    key2 => {end => 'five_times'}
1104
);
1105
$result = $dbi->select(table => 'table1');
1106
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1107
$result->filter(key1 => undef);
1108
$result->end_filter(key1 => undef);
1109
$row = $result->one;
1110
is_deeply($row, {key1 => 1, key2 => 40}, 'apply_filter overwrite');
1111

            
1112
test 'remove_end_filter and remove_filter';
1113
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1114
$dbi->execute($CREATE_TABLE->{0});
1115
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1116
$result = $dbi->select(table => 'table1');
1117
$row = $result
1118
       ->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 })
1119
       ->remove_filter
1120
       ->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 })
1121
       ->remove_end_filter
1122
       ->fetch_first;
1123
is_deeply($row, [1, 2]);
1124

            
1125
test 'empty where select';
1126
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1127
$dbi->execute($CREATE_TABLE->{0});
1128
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1129
$result = $dbi->select(table => 'table1', where => {});
1130
$row = $result->one;
1131
is_deeply($row, {key1 => 1, key2 => 2});
1132

            
1133
test 'select query option';
1134
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1135
$dbi->execute($CREATE_TABLE->{0});
1136
$query = $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, query => 1);
1137
is(ref $query, 'DBIx::Custom::Query');
1138
$query = $dbi->update(table => 'table1', where => {key1 => 1}, param => {key2 => 2}, query => 1);
1139
is(ref $query, 'DBIx::Custom::Query');
1140
$query = $dbi->delete(table => 'table1', where => {key1 => 1}, query => 1);
1141
is(ref $query, 'DBIx::Custom::Query');
1142
$query = $dbi->select(table => 'table1', where => {key1 => 1, key2 => 2}, query => 1);
1143
is(ref $query, 'DBIx::Custom::Query');
1144

            
1145
test 'DBIx::Custom::Where';
1146
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1147
$dbi->execute($CREATE_TABLE->{0});
1148
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1149
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1150
$where = $dbi->where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
1151
is("$where", "where ( key1 = :key1 and key2 = :key2 )", 'no param');
1152

            
1153
$where = $dbi->where
1154
             ->clause(['and', 'key1 = :key1', 'key2 = :key2'])
1155
             ->param({key1 => 1});
1156

            
1157
$result = $dbi->select(
1158
    table => 'table1',
1159
    where => $where
1160
);
1161
$row = $result->all;
1162
is_deeply($row, [{key1 => 1, key2 => 2}]);
1163

            
1164
$result = $dbi->select(
1165
    table => 'table1',
1166
    where => [
1167
        ['and', 'key1 = :key1', 'key2 = :key2'],
1168
        {key1 => 1}
1169
    ]
1170
);
1171
$row = $result->all;
1172
is_deeply($row, [{key1 => 1, key2 => 2}]);
1173

            
1174
$where = $dbi->where
1175
             ->clause(['and', 'key1 = :key1', 'key2 = :key2'])
1176
             ->param({key1 => 1, key2 => 2});
1177
$result = $dbi->select(
1178
    table => 'table1',
1179
    where => $where
1180
);
1181
$row = $result->all;
1182
is_deeply($row, [{key1 => 1, key2 => 2}]);
1183

            
1184
$where = $dbi->where
1185
             ->clause(['and', 'key1 = :key1', 'key2 = :key2'])
1186
             ->param({});
1187
$result = $dbi->select(
1188
    table => 'table1',
1189
    where => $where,
1190
);
1191
$row = $result->all;
1192
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1193

            
1194
$where = $dbi->where
1195
             ->clause(['and', ['or', 'key1 > :key1', 'key1 < :key1'], 'key2 = :key2'])
1196
             ->param({key1 => [0, 3], key2 => 2});
1197
$result = $dbi->select(
1198
    table => 'table1',
1199
    where => $where,
1200
); 
1201
$row = $result->all;
1202
is_deeply($row, [{key1 => 1, key2 => 2}]);
1203

            
1204
$where = $dbi->where;
1205
$result = $dbi->select(
1206
    table => 'table1',
1207
    where => $where
1208
);
1209
$row = $result->all;
1210
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1211

            
1212
eval {
1213
$where = $dbi->where
1214
             ->clause(['uuu']);
1215
$result = $dbi->select(
1216
    table => 'table1',
1217
    where => $where
1218
);
1219
};
1220
ok($@);
1221

            
1222
$where = $dbi->where;
1223
is("$where", '');
1224

            
1225
$where = $dbi->where
1226
             ->clause(['or', ('key1 = :key1') x 2])
1227
             ->param({key1 => [1, 3]});
1228
$result = $dbi->select(
1229
    table => 'table1',
1230
    where => $where,
1231
);
1232
$row = $result->all;
1233
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1234

            
1235
$where = $dbi->where
1236
             ->clause(['or', ('key1 = :key1') x 2])
1237
             ->param({key1 => [1]});
1238
$result = $dbi->select(
1239
    table => 'table1',
1240
    where => $where,
1241
);
1242
$row = $result->all;
1243
is_deeply($row, [{key1 => 1, key2 => 2}]);
1244

            
1245
$where = $dbi->where
1246
             ->clause(['or', ('key1 = :key1') x 2])
1247
             ->param({key1 => 1});
1248
$result = $dbi->select(
1249
    table => 'table1',
1250
    where => $where,
1251
);
1252
$row = $result->all;
1253
is_deeply($row, [{key1 => 1, key2 => 2}]);
1254

            
1255
$where = $dbi->where
1256
             ->clause('key1 = :key1')
1257
             ->param({key1 => 1});
1258
$result = $dbi->select(
1259
    table => 'table1',
1260
    where => $where,
1261
);
1262
$row = $result->all;
1263
is_deeply($row, [{key1 => 1, key2 => 2}]);
1264

            
1265
$where = $dbi->where
1266
             ->clause('key1 = :key1 key2 = :key2')
1267
             ->param({key1 => 1});
1268
eval{$where->to_string};
1269
like($@, qr/one column/);
1270

            
1271
$where = $dbi->where
1272
             ->clause('key1 = :key1')
1273
             ->param([]);
1274
eval{$where->to_string};
1275
like($@, qr/Parameter/);
1276

            
1277
$where = $dbi->where
1278
             ->clause(['or', ('key1 = :key1') x 3])
1279
             ->param({key1 => [$dbi->not_exists, 1, 3]});
1280
$result = $dbi->select(
1281
    table => 'table1',
1282
    where => $where,
1283
);
1284
$row = $result->all;
1285
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1286

            
1287
$where = $dbi->where
1288
             ->clause(['or', ('key1 = :key1') x 3])
1289
             ->param({key1 => [1, $dbi->not_exists, 3]});
1290
$result = $dbi->select(
1291
    table => 'table1',
1292
    where => $where,
1293
);
1294
$row = $result->all;
1295
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1296

            
1297
$where = $dbi->where
1298
             ->clause(['or', ('key1 = :key1') x 3])
1299
             ->param({key1 => [1, 3, $dbi->not_exists]});
1300
$result = $dbi->select(
1301
    table => 'table1',
1302
    where => $where,
1303
);
1304
$row = $result->all;
1305
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1306

            
1307
$where = $dbi->where
1308
             ->clause(['or', ('key1 = :key1') x 3])
1309
             ->param({key1 => [1, $dbi->not_exists, $dbi->not_exists]});
1310
$result = $dbi->select(
1311
    table => 'table1',
1312
    where => $where,
1313
);
1314
$row = $result->all;
1315
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1316

            
1317
$where = $dbi->where
1318
             ->clause(['or', ('key1 = :key1') x 3])
1319
             ->param({key1 => [$dbi->not_exists, 1, $dbi->not_exists]});
1320
$result = $dbi->select(
1321
    table => 'table1',
1322
    where => $where,
1323
);
1324
$row = $result->all;
1325
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1326

            
1327
$where = $dbi->where
1328
             ->clause(['or', ('key1 = :key1') x 3])
1329
             ->param({key1 => [$dbi->not_exists, $dbi->not_exists, 1]});
1330
$result = $dbi->select(
1331
    table => 'table1',
1332
    where => $where,
1333
);
1334
$row = $result->all;
1335
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1336

            
1337
$where = $dbi->where
1338
             ->clause(['or', ('key1 = :key1') x 3])
1339
             ->param({key1 => [$dbi->not_exists, $dbi->not_exists, $dbi->not_exists]});
1340
$result = $dbi->select(
1341
    table => 'table1',
1342
    where => $where,
1343
);
1344
$row = $result->all;
1345
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1346

            
1347
$where = $dbi->where
1348
             ->clause(['or', ('key1 = :key1') x 3])
1349
             ->param({key1 => []});
1350
$result = $dbi->select(
1351
    table => 'table1',
1352
    where => $where,
1353
);
1354
$row = $result->all;
1355
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1356

            
1357
$where = $dbi->where
1358
             ->clause(['and', '{> key1}', '{< key1}' ])
1359
             ->param({key1 => [2, $dbi->not_exists]});
1360
$result = $dbi->select(
1361
    table => 'table1',
1362
    where => $where,
1363
);
1364
$row = $result->all;
1365
is_deeply($row, [{key1 => 3, key2 => 4}], 'not_exists');
1366

            
1367
$where = $dbi->where
1368
             ->clause(['and', '{> key1}', '{< key1}' ])
1369
             ->param({key1 => [$dbi->not_exists, 2]});
1370
$result = $dbi->select(
1371
    table => 'table1',
1372
    where => $where,
1373
);
1374
$row = $result->all;
1375
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1376

            
1377
$where = $dbi->where
1378
             ->clause(['and', '{> key1}', '{< key1}' ])
1379
             ->param({key1 => [$dbi->not_exists, $dbi->not_exists]});
1380
$result = $dbi->select(
1381
    table => 'table1',
1382
    where => $where,
1383
);
1384
$row = $result->all;
1385
is_deeply($row, [{key1 => 1, key2 => 2},{key1 => 3, key2 => 4}], 'not_exists');
1386

            
1387
$where = $dbi->where
1388
             ->clause(['and', '{> key1}', '{< key1}' ])
1389
             ->param({key1 => [0, 2]});
1390
$result = $dbi->select(
1391
    table => 'table1',
1392
    where => $where,
1393
);
1394
$row = $result->all;
1395
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1396

            
1397
$where = $dbi->where
1398
             ->clause(['and', 'key1 is not null', 'key2 is not null' ]);
1399
$result = $dbi->select(
1400
    table => 'table1',
1401
    where => $where,
1402
);
1403
$row = $result->all;
1404
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1405

            
1406
eval {$dbi->where(ppp => 1) };
1407
like($@, qr/invalid/);
1408

            
1409
$where = $dbi->where(
1410
    clause => ['and', ['or'], ['and', 'key1 = :key1', 'key2 = :key2']],
1411
    param => {key1 => 1, key2 => 2}
1412
);
1413
$result = $dbi->select(
1414
    table => 'table1',
1415
    where => $where,
1416
);
1417
$row = $result->all;
1418
is_deeply($row, [{key1 => 1, key2 => 2}]);
1419

            
1420

            
1421
$where = $dbi->where(
1422
    clause => ['and', ['or'], ['or', ':key1', ':key2']],
1423
    param => {}
1424
);
1425
$result = $dbi->select(
1426
    table => 'table1',
1427
    where => $where,
1428
);
1429
$row = $result->all;
1430
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1431

            
1432

            
1433
test 'dbi_option default';
1434
$dbi = DBIx::Custom->new;
1435
is_deeply($dbi->dbi_option, {});
1436

            
1437
test 'register_tag_processor';
1438
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1439
$dbi->register_tag_processor(
1440
    a => sub { 1 }
1441
);
1442
is($dbi->query_builder->tag_processors->{a}->(), 1);
1443

            
1444
test 'register_tag';
1445
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1446
$dbi->register_tag(
1447
    b => sub { 2 }
1448
);
1449
is($dbi->query_builder->tags->{b}->(), 2);
1450

            
1451
test 'table not specify exception';
1452
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1453
eval {$dbi->insert};
1454
like($@, qr/table/);
1455
eval {$dbi->update};
1456
like($@, qr/table/);
1457
eval {$dbi->delete};
1458
like($@, qr/table/);
1459
eval {$dbi->select};
1460
like($@, qr/table/);
1461

            
1462

            
1463
test 'more tests';
1464
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1465
eval{$dbi->apply_filter('table', 'column', [])};
1466
like($@, qr/apply_filter/);
1467

            
1468
eval{$dbi->apply_filter('table', 'column', {outer => 2})};
1469
like($@, qr/apply_filter/);
1470

            
1471
$dbi->apply_filter(
1472

            
1473
);
1474
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1475
$dbi->execute($CREATE_TABLE->{0});
1476
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1477
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1478
$dbi->apply_filter('table1', 'key2', 
1479
                   {in => sub { $_[0] * 3 }, out => sub { $_[0] * 2 }});
1480
$rows = $dbi->select(table => 'table1', where => {key2 => 1})->all;
1481
is_deeply($rows, [{key1 => 1, key2 => 6}]);
1482

            
1483
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1484
$dbi->execute($CREATE_TABLE->{0});
1485
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1486
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1487
$dbi->apply_filter('table1', 'key2', {});
1488
$rows = $dbi->select(table => 'table1', where => {key2 => 2})->all;
1489
is_deeply($rows, [{key1 => 1, key2 => 2}]);
1490

            
1491
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1492
eval {$dbi->apply_filter('table1', 'key2', {out => 'no'})};
1493
like($@, qr/not registered/);
1494
eval {$dbi->apply_filter('table1', 'key2', {in => 'no'})};
1495
like($@, qr/not registered/);
1496
$dbi->method({one => sub { 1 }});
1497
is($dbi->one, 1);
1498

            
1499
eval{DBIx::Custom->connect()};
1500
like($@, qr/_connect/);
1501

            
1502
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1503
$dbi->execute($CREATE_TABLE->{0});
1504
$dbi->register_filter(twice => sub { $_[0] * 2 });
1505
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
1506
             filter => {key1 => 'twice'});
1507
$row = $dbi->select(table => 'table1')->one;
1508
is_deeply($row, {key1 => 2, key2 => 2});
1509
eval {$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
1510
             filter => {key1 => 'no'}) };
1511
like($@, qr//);
1512

            
1513
$dbi->register_filter(one => sub { });
1514
$dbi->default_fetch_filter('one');
1515
ok($dbi->default_fetch_filter);
1516
$dbi->default_bind_filter('one');
1517
ok($dbi->default_bind_filter);
1518
eval{$dbi->default_fetch_filter('no')};
1519
like($@, qr/not registered/);
1520
eval{$dbi->default_bind_filter('no')};
1521
like($@, qr/not registered/);
1522
$dbi->default_bind_filter(undef);
1523
ok(!defined $dbi->default_bind_filter);
1524
$dbi->default_fetch_filter(undef);
1525
ok(!defined $dbi->default_fetch_filter);
1526
eval {$dbi->execute('select * from table1 {} {= author') };
1527
like($@, qr/Tag not finished/);
1528

            
1529
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1530
$dbi->execute($CREATE_TABLE->{0});
1531
$dbi->register_filter(one => sub { 1 });
1532
$result = $dbi->select(table => 'table1');
1533
eval {$result->filter(key1 => 'no')};
1534
like($@, qr/not registered/);
1535
eval {$result->end_filter(key1 => 'no')};
1536
like($@, qr/not registered/);
1537
$result->default_filter(undef);
1538
ok(!defined $result->default_filter);
1539
$result->default_filter('one');
1540
is($result->default_filter->(), 1);
1541

            
1542
test 'dbi_option';
1543
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:',
1544
                             dbi_option => {PrintError => 1});
1545
ok($dbi->dbh->{PrintError});
1546
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:',
1547
                             dbi_options => {PrintError => 1});
1548
ok($dbi->dbh->{PrintError});
1549

            
1550
test 'DBIx::Custom::Result stash()';
1551
$result = DBIx::Custom::Result->new;
1552
is_deeply($result->stash, {}, 'default');
1553
$result->stash->{foo} = 1;
1554
is($result->stash->{foo}, 1, 'get and set');
1555

            
1556
test 'filter __ expression';
1557
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1558
$dbi->execute('create table company (id, name, location_id)');
1559
$dbi->execute('create table location (id, name)');
1560
$dbi->apply_filter('location',
1561
  name => {in => sub { uc $_[0] } }
1562
);
1563

            
1564
$dbi->insert(table => 'company', param => {id => 1, name => 'a', location_id => 2});
1565
$dbi->insert(table => 'location', param => {id => 2, name => 'b'});
1566

            
1567
$result = $dbi->select(
1568
    table => ['company', 'location'], relation => {'company.location_id' => 'location.id'},
1569
    column => ['location.name as location__name']
1570
);
1571
is($result->fetch_first->[0], 'B');
1572

            
1573
$result = $dbi->select(
1574
    table => 'company', relation => {'company.location_id' => 'location.id'},
1575
    column => ['location.name as location__name']
1576
);
1577
is($result->fetch_first->[0], 'B');
1578

            
1579
$result = $dbi->select(
1580
    table => 'company', relation => {'company.location_id' => 'location.id'},
1581
    column => ['location.name as "location.name"']
1582
);
1583
is($result->fetch_first->[0], 'B');
1584

            
1585
test 'Model class';
1586
use MyDBI1;
1587
$dbi = MyDBI1->connect($NEW_ARGS->{0});
1588
$dbi->execute("create table book (title, author)");
1589
$model = $dbi->model('book');
1590
$model->insert({title => 'a', author => 'b'});
1591
is_deeply($model->list->all, [{title => 'a', author => 'b'}], 'basic');
1592
$dbi->execute("create table company (name)");
1593
$model = $dbi->model('company');
1594
$model->insert({name => 'a'});
1595
is_deeply($model->list->all, [{name => 'a'}], 'basic');
1596
is($dbi->models->{'book'}, $dbi->model('book'));
1597
is($dbi->models->{'company'}, $dbi->model('company'));
1598

            
1599
{
1600
    package MyDBI4;
1601

            
1602
    use strict;
1603
    use warnings;
1604

            
1605
    use base 'DBIx::Custom';
1606

            
1607
    sub connect {
1608
        my $self = shift->SUPER::connect(@_);
1609
        
1610
        $self->include_model(
1611
            MyModel2 => [
1612
                'book',
1613
                {class => 'Company', name => 'company'}
1614
            ]
1615
        );
1616
    }
1617

            
1618
    package MyModel2::Base1;
1619

            
1620
    use strict;
1621
    use warnings;
1622

            
1623
    use base 'DBIx::Custom::Model';
1624

            
1625
    package MyModel2::book;
1626

            
1627
    use strict;
1628
    use warnings;
1629

            
1630
    use base 'MyModel2::Base1';
1631

            
1632
    sub insert {
1633
        my ($self, $param) = @_;
1634
        
1635
        return $self->SUPER::insert(param => $param);
1636
    }
1637

            
1638
    sub list { shift->select; }
1639

            
1640
    package MyModel2::Company;
1641

            
1642
    use strict;
1643
    use warnings;
1644

            
1645
    use base 'MyModel2::Base1';
1646

            
1647
    sub insert {
1648
        my ($self, $param) = @_;
1649
        
1650
        return $self->SUPER::insert(param => $param);
1651
    }
1652

            
1653
    sub list { shift->select; }
1654
}
1655
$dbi = MyDBI4->connect($NEW_ARGS->{0});
1656
$dbi->execute("create table book (title, author)");
1657
$model = $dbi->model('book');
1658
$model->insert({title => 'a', author => 'b'});
1659
is_deeply($model->list->all, [{title => 'a', author => 'b'}], 'basic');
1660
$dbi->execute("create table company (name)");
1661
$model = $dbi->model('company');
1662
$model->insert({name => 'a'});
1663
is_deeply($model->list->all, [{name => 'a'}], 'basic');
1664

            
1665
{
1666
     package MyDBI5;
1667

            
1668
    use strict;
1669
    use warnings;
1670

            
1671
    use base 'DBIx::Custom';
1672

            
1673
    sub connect {
1674
        my $self = shift->SUPER::connect(@_);
1675
        
1676
        $self->include_model('MyModel4');
1677
    }
1678
}
1679
$dbi = MyDBI5->connect($NEW_ARGS->{0});
1680
$dbi->execute("create table company (name)");
1681
$dbi->execute("create table table1 (key1)");
1682
$model = $dbi->model('company');
1683
$model->insert({name => 'a'});
1684
is_deeply($model->list->all, [{name => 'a'}], 'include all model');
1685
$dbi->insert(table => 'table1', param => {key1 => 1});
1686
$model = $dbi->model('book');
1687
is_deeply($model->list->all, [{key1 => 1}], 'include all model');
1688

            
1689
test 'primary_key';
1690
use MyDBI1;
1691
$dbi = MyDBI1->connect($NEW_ARGS->{0});
1692
$model = $dbi->model('book');
1693
$model->primary_key(['id', 'number']);
1694
is_deeply($model->primary_key, ['id', 'number']);
1695

            
1696
test 'columns';
1697
use MyDBI1;
1698
$dbi = MyDBI1->connect($NEW_ARGS->{0});
1699
$model = $dbi->model('book');
1700
$model->columns(['id', 'number']);
1701
is_deeply($model->columns, ['id', 'number']);
1702

            
1703
test 'setup_model';
1704
use MyDBI1;
1705
$dbi = MyDBI1->connect($NEW_ARGS->{0});
1706
$dbi->execute('create table book (id)');
1707
$dbi->execute('create table company (id, name);');
1708
$dbi->execute('create table test (id, name, primary key (id, name));');
1709
$dbi->setup_model;
1710
is_deeply($dbi->model('book')->columns, ['id']);
1711
is_deeply($dbi->model('company')->columns, ['id', 'name']);
1712

            
1713
test 'delete_at';
1714
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1715
$dbi->execute($CREATE_TABLE->{1});
1716
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1717
$dbi->delete_at(
1718
    table => 'table1',
1719
    primary_key => ['key1', 'key2'],
1720
    where => [1, 2],
1721
);
1722
is_deeply($dbi->select(table => 'table1')->all, []);
1723

            
1724
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1725
$dbi->delete_at(
1726
    table => 'table1',
1727
    primary_key => 'key1',
1728
    where => 1,
1729
);
1730
is_deeply($dbi->select(table => 'table1')->all, []);
1731

            
1732
test 'insert_at';
1733
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1734
$dbi->execute($CREATE_TABLE->{1});
1735
$dbi->insert_at(
1736
    primary_key => ['key1', 'key2'], 
1737
    table => 'table1',
1738
    where => [1, 2],
1739
    param => {key3 => 3}
1740
);
1741
is($dbi->select(table => 'table1')->one->{key1}, 1);
1742
is($dbi->select(table => 'table1')->one->{key2}, 2);
1743
is($dbi->select(table => 'table1')->one->{key3}, 3);
1744

            
1745
$dbi->delete_all(table => 'table1');
1746
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1747
$dbi->insert_at(
1748
    primary_key => 'key1', 
1749
    table => 'table1',
1750
    where => 1,
1751
    param => {key2 => 2, key3 => 3}
1752
);
1753

            
1754
is($dbi->select(table => 'table1')->one->{key1}, 1);
1755
is($dbi->select(table => 'table1')->one->{key2}, 2);
1756
is($dbi->select(table => 'table1')->one->{key3}, 3);
1757

            
1758
eval {
1759
    $dbi->insert_at(
1760
        table => 'table1',
1761
        primary_key => ['key1', 'key2'],
1762
        where => {},
1763
        param => {key1 => 1, key2 => 2, key3 => 3},
1764
    );
1765
};
1766
like($@, qr/must be/);
1767

            
1768
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1769
$dbi->execute($CREATE_TABLE->{1});
1770
$dbi->insert_at(
1771
    {key3 => 3},
1772
    primary_key => ['key1', 'key2'], 
1773
    table => 'table1',
1774
    where => [1, 2],
1775
);
1776
is($dbi->select(table => 'table1')->one->{key1}, 1);
1777
is($dbi->select(table => 'table1')->one->{key2}, 2);
1778
is($dbi->select(table => 'table1')->one->{key3}, 3);
1779

            
1780
test 'update_at';
1781
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1782
$dbi->execute($CREATE_TABLE->{1});
1783
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1784
$dbi->update_at(
1785
    table => 'table1',
1786
    primary_key => ['key1', 'key2'],
1787
    where => [1, 2],
1788
    param => {key3 => 4}
1789
);
1790
is($dbi->select(table => 'table1')->one->{key1}, 1);
1791
is($dbi->select(table => 'table1')->one->{key2}, 2);
1792
is($dbi->select(table => 'table1')->one->{key3}, 4);
1793

            
1794
$dbi->delete_all(table => 'table1');
1795
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1796
$dbi->update_at(
1797
    table => 'table1',
1798
    primary_key => 'key1',
1799
    where => 1,
1800
    param => {key3 => 4}
1801
);
1802
is($dbi->select(table => 'table1')->one->{key1}, 1);
1803
is($dbi->select(table => 'table1')->one->{key2}, 2);
1804
is($dbi->select(table => 'table1')->one->{key3}, 4);
1805

            
1806
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1807
$dbi->execute($CREATE_TABLE->{1});
1808
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1809
$dbi->update_at(
1810
    {key3 => 4},
1811
    table => 'table1',
1812
    primary_key => ['key1', 'key2'],
1813
    where => [1, 2]
1814
);
1815
is($dbi->select(table => 'table1')->one->{key1}, 1);
1816
is($dbi->select(table => 'table1')->one->{key2}, 2);
1817
is($dbi->select(table => 'table1')->one->{key3}, 4);
1818

            
1819
test 'select_at';
1820
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1821
$dbi->execute($CREATE_TABLE->{1});
1822
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1823
$result = $dbi->select_at(
1824
    table => 'table1',
1825
    primary_key => ['key1', 'key2'],
1826
    where => [1, 2]
1827
);
1828
$row = $result->one;
1829
is($row->{key1}, 1);
1830
is($row->{key2}, 2);
1831
is($row->{key3}, 3);
1832

            
1833
$dbi->delete_all(table => 'table1');
1834
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1835
$result = $dbi->select_at(
1836
    table => 'table1',
1837
    primary_key => 'key1',
1838
    where => 1,
1839
);
1840
$row = $result->one;
1841
is($row->{key1}, 1);
1842
is($row->{key2}, 2);
1843
is($row->{key3}, 3);
1844

            
1845
$dbi->delete_all(table => 'table1');
1846
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1847
$result = $dbi->select_at(
1848
    table => 'table1',
1849
    primary_key => ['key1', 'key2'],
1850
    where => [1, 2]
1851
);
1852
$row = $result->one;
1853
is($row->{key1}, 1);
1854
is($row->{key2}, 2);
1855
is($row->{key3}, 3);
1856

            
1857
eval {
1858
    $result = $dbi->select_at(
1859
        table => 'table1',
1860
        primary_key => ['key1', 'key2'],
1861
        where => {},
1862
    );
1863
};
1864
like($@, qr/must be/);
1865

            
1866
eval {
1867
    $result = $dbi->select_at(
1868
        table => 'table1',
1869
        primary_key => ['key1', 'key2'],
1870
        where => [1],
1871
    );
1872
};
1873
like($@, qr/same/);
1874

            
1875
eval {
1876
    $result = $dbi->update_at(
1877
        table => 'table1',
1878
        primary_key => ['key1', 'key2'],
1879
        where => {},
1880
        param => {key1 => 1, key2 => 2},
1881
    );
1882
};
1883
like($@, qr/must be/);
1884

            
1885
eval {
1886
    $result = $dbi->delete_at(
1887
        table => 'table1',
1888
        primary_key => ['key1', 'key2'],
1889
        where => {},
1890
    );
1891
};
1892
like($@, qr/must be/);
1893

            
1894
test 'columns';
1895
use MyDBI1;
1896
$dbi = MyDBI1->connect($NEW_ARGS->{0});
1897
$model = $dbi->model('book');
1898

            
1899

            
1900
test 'model delete_at';
1901
{
1902
    package MyDBI6;
1903
    
1904
    use base 'DBIx::Custom';
1905
    
1906
    sub connect {
1907
        my $self = shift->SUPER::connect(@_);
1908
        
1909
        $self->include_model('MyModel5');
1910
        
1911
        return $self;
1912
    }
1913
}
1914
$dbi = MyDBI6->connect($NEW_ARGS->{0});
1915
$dbi->execute($CREATE_TABLE->{1});
1916
$dbi->execute("create table table2 (key1, key2, key3)");
1917
$dbi->execute("create table table3 (key1, key2, key3)");
1918
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1919
$dbi->model('table1')->delete_at(where => [1, 2]);
1920
is_deeply($dbi->select(table => 'table1')->all, []);
1921
$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
1922
$dbi->model('table1_1')->delete_at(where => [1, 2]);
1923
is_deeply($dbi->select(table => 'table1')->all, []);
1924
$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
1925
$dbi->model('table1_3')->delete_at(where => [1, 2]);
1926
is_deeply($dbi->select(table => 'table1')->all, []);
1927

            
1928
test 'model insert_at';
1929
$dbi = MyDBI6->connect($NEW_ARGS->{0});
1930
$dbi->execute($CREATE_TABLE->{1});
1931
$dbi->model('table1')->insert_at(
1932
    where => [1, 2],
1933
    param => {key3 => 3}
1934
);
1935
$result = $dbi->model('table1')->select;
1936
$row = $result->one;
1937
is($row->{key1}, 1);
1938
is($row->{key2}, 2);
1939
is($row->{key3}, 3);
1940

            
1941
test 'model update_at';
1942
$dbi = MyDBI6->connect($NEW_ARGS->{0});
1943
$dbi->execute($CREATE_TABLE->{1});
1944
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1945
$dbi->model('table1')->update_at(
1946
    where => [1, 2],
1947
    param => {key3 => 4}
1948
);
1949
$result = $dbi->model('table1')->select;
1950
$row = $result->one;
1951
is($row->{key1}, 1);
1952
is($row->{key2}, 2);
1953
is($row->{key3}, 4);
1954

            
1955
test 'model select_at';
1956
$dbi = MyDBI6->connect($NEW_ARGS->{0});
1957
$dbi->execute($CREATE_TABLE->{1});
1958
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1959
$result = $dbi->model('table1')->select_at(where => [1, 2]);
1960
$row = $result->one;
1961
is($row->{key1}, 1);
1962
is($row->{key2}, 2);
1963
is($row->{key3}, 3);
1964

            
1965

            
1966
test 'mycolumn and column';
1967
{
1968
    package MyDBI7;
1969
    
1970
    use base 'DBIx::Custom';
1971
    
1972
    sub connect {
1973
        my $self = shift->SUPER::connect(@_);
1974
        
1975
        $self->include_model('MyModel6');
1976
        
1977
        
1978
        return $self;
1979
    }
1980
}
1981
$dbi = MyDBI7->connect($NEW_ARGS->{0});
1982
$dbi->execute($CREATE_TABLE->{0});
1983
$dbi->execute($CREATE_TABLE->{2});
1984
$dbi->separator('__');
1985
$dbi->setup_model;
1986
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1987
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
1988
$model = $dbi->model('table1');
1989
$result = $model->select(
1990
    column => [$model->mycolumn, $model->column('table2')],
1991
    where => {'table1.key1' => 1}
1992
);
1993
is_deeply($result->one,
1994
          {key1 => 1, key2 => 2, 'table2__key1' => 1, 'table2__key3' => 3});
1995

            
1996
test 'update_param';
1997
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
1998
$dbi->execute($CREATE_TABLE->{1});
1999
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2000
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2001

            
2002
$param = {key2 => 11};
2003
$update_param = $dbi->update_param($param);
2004
$sql = <<"EOS";
2005
update table1 $update_param
2006
where key1 = 1
2007
EOS
2008
$dbi->execute($sql, param => $param);
2009
$result = $dbi->execute($SELECT_SOURCES->{0}, table => 'table1');
2010
$rows   = $result->all;
2011
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
2012
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
2013
                  "basic");
2014

            
2015

            
2016
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2017
$dbi->execute($CREATE_TABLE->{1});
2018
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2019
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2020

            
2021
$param = {key2 => 11, key3 => 33};
2022
$update_param = $dbi->update_param($param);
2023
$sql = <<"EOS";
2024
update table1 $update_param
2025
where key1 = 1
2026
EOS
2027
$dbi->execute($sql, param => $param);
2028
$result = $dbi->execute($SELECT_SOURCES->{0}, table => 'table1');
2029
$rows   = $result->all;
2030
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 33, key4 => 4, key5 => 5},
2031
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
2032
                  "basic");
2033

            
2034
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2035
$dbi->execute($CREATE_TABLE->{1});
2036
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2037
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2038

            
2039
$param = {key2 => 11, key3 => 33};
2040
$update_param = $dbi->update_param($param, {no_set => 1});
2041
$sql = <<"EOS";
2042
update table1 set $update_param
2043
where key1 = 1
2044
EOS
2045
$dbi->execute($sql, param => $param);
2046
$result = $dbi->execute($SELECT_SOURCES->{0}, table => 'table1');
2047
$rows   = $result->all;
2048
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 33, key4 => 4, key5 => 5},
2049
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
2050
                  "update param no_set");
2051

            
2052
            
2053
eval { $dbi->update_param({";" => 1}) };
2054
like($@, qr/not safety/);
2055

            
2056

            
2057
test 'update_param';
2058
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2059
$dbi->execute($CREATE_TABLE->{1});
2060
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2061
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2062

            
2063
$param = {key2 => 11};
2064
$update_param = $dbi->assign_param($param);
2065
$sql = <<"EOS";
2066
update table1 set $update_param
2067
where key1 = 1
2068
EOS
2069
$dbi->execute($sql, param => $param, table => 'table1');
2070
$result = $dbi->execute($SELECT_SOURCES->{0});
2071
$rows   = $result->all;
2072
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
2073
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
2074
                  "basic");
2075

            
2076

            
2077
test 'insert_param';
2078
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2079
$dbi->execute($CREATE_TABLE->{1});
2080
$param = {key1 => 1, key2 => 2};
2081
$insert_param = $dbi->insert_param($param);
2082
$sql = <<"EOS";
2083
insert into table1 $insert_param
2084
EOS
2085
$dbi->execute($sql, param => $param, table => 'table1');
2086
is($dbi->select(table => 'table1')->one->{key1}, 1);
2087
is($dbi->select(table => 'table1')->one->{key2}, 2);
2088

            
2089
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2090
$dbi->quote('"');
2091
$dbi->execute($CREATE_TABLE->{1});
2092
$param = {key1 => 1, key2 => 2};
2093
$insert_param = $dbi->insert_param($param);
2094
$sql = <<"EOS";
2095
insert into table1 $insert_param
2096
EOS
2097
$dbi->execute($sql, param => $param, table => 'table1');
2098
is($dbi->select(table => 'table1')->one->{key1}, 1);
2099
is($dbi->select(table => 'table1')->one->{key2}, 2);
2100

            
2101
eval { $dbi->insert_param({";" => 1}) };
2102
like($@, qr/not safety/);
2103

            
2104

            
2105
test 'join';
2106
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2107
$dbi->execute($CREATE_TABLE->{0});
2108
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2109
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
2110
$dbi->execute($CREATE_TABLE->{2});
2111
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2112
$dbi->execute($CREATE_TABLE->{4});
2113
$dbi->insert(table => 'table3', param => {key3 => 5, key4 => 4});
2114
$rows = $dbi->select(
2115
    table => 'table1',
2116
    column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
2117
    where   => {'table1.key2' => 2},
2118
    join  => ['left outer join table2 on table1.key1 = table2.key1']
2119
)->all;
2120
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}]);
2121

            
2122
$rows = $dbi->select(
2123
    table => 'table1',
2124
    where   => {'key1' => 1},
2125
    join  => ['left outer join table2 on table1.key1 = table2.key1']
2126
)->all;
2127
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2128

            
2129
eval {
2130
    $rows = $dbi->select(
2131
        table => 'table1',
2132
        column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
2133
        where   => {'table1.key2' => 2},
2134
        join  => {'table1.key1' => 'table2.key1'}
2135
    );
2136
};
2137
like ($@, qr/array/);
2138

            
2139
$rows = $dbi->select(
2140
    table => 'table1',
2141
    where   => {'key1' => 1},
2142
    join  => ['left outer join table2 on table1.key1 = table2.key1',
2143
              'left outer join table3 on table2.key3 = table3.key3']
2144
)->all;
2145
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2146

            
2147
$rows = $dbi->select(
2148
    column => 'table3.key4 as table3__key4',
2149
    table => 'table1',
2150
    where   => {'table1.key1' => 1},
2151
    join  => ['left outer join table2 on table1.key1 = table2.key1',
2152
              'left outer join table3 on table2.key3 = table3.key3']
2153
)->all;
2154
is_deeply($rows, [{table3__key4 => 4}]);
2155

            
2156
$rows = $dbi->select(
2157
    column => 'table1.key1 as table1__key1',
2158
    table => 'table1',
2159
    where   => {'table3.key4' => 4},
2160
    join  => ['left outer join table2 on table1.key1 = table2.key1',
2161
              'left outer join table3 on table2.key3 = table3.key3']
2162
)->all;
2163
is_deeply($rows, [{table1__key1 => 1}]);
2164

            
2165
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2166
$dbi->quote('"');
2167
$dbi->execute($CREATE_TABLE->{0});
2168
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2169
$dbi->execute($CREATE_TABLE->{2});
2170
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2171
$rows = $dbi->select(
2172
    table => 'table1',
2173
    column => '"table1"."key1" as "table1_key1", "table2"."key1" as "table2_key1", "key2", "key3"',
2174
    where   => {'table1.key2' => 2},
2175
    join  => ['left outer join "table2" on "table1"."key1" = "table2"."key1"'],
2176
)->all;
2177
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}],
2178
          'quote');
2179

            
2180
{
2181
    package MyDBI8;
2182
    
2183
    use base 'DBIx::Custom';
2184
    
2185
    sub connect {
2186
        my $self = shift->SUPER::connect(@_);
2187
        
2188
        $self->include_model('MyModel7');
2189
        
2190
        return $self;
2191
    }
2192
}
2193

            
2194
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2195
$dbi->execute($CREATE_TABLE->{0});
2196
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2197
$sql = <<"EOS";
2198
left outer join (
2199
  select * from table1 as t1
2200
  where t1.key2 = (
2201
    select max(t2.key2) from table1 as t2
2202
    where t1.key1 = t2.key1
2203
  )
2204
) as latest_table1 on table1.key1 = latest_table1.key1
2205
EOS
2206
$join = [$sql];
2207
$rows = $dbi->select(
2208
    table => 'table1',
2209
    column => 'latest_table1.key1 as latest_table1__key1',
2210
    join  => $join
2211
)->all;
2212
is_deeply($rows, [{latest_table1__key1 => 1}]);
2213

            
2214
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2215
$dbi->execute($CREATE_TABLE->{0});
2216
$dbi->execute($CREATE_TABLE->{2});
2217
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2218
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
2219
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2220
$result = $dbi->select(
2221
    table => 'table1',
2222
    join => [
2223
        "left outer join table2 on table2.key2 = '4' and table1.key1 = table2.key1"
2224
    ]
2225
);
2226
is_deeply($result->all, [{key1 => 1, key2 => 2}]);
2227
$result = $dbi->select(
2228
    table => 'table1',
2229
    column => [{table2 => ['key3']}],
2230
    join => [
2231
        "left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1"
2232
    ]
2233
);
2234
is_deeply($result->all, [{'table2.key3' => 4}]);
2235
$result = $dbi->select(
2236
    table => 'table1',
2237
    column => [{table2 => ['key3']}],
2238
    join => [
2239
        "left outer join table2 on table1.key1 = table2.key1 and table2.key3 = '4'"
2240
    ]
2241
);
2242
is_deeply($result->all, [{'table2.key3' => 4}]);
2243

            
2244
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2245
$dbi->execute($CREATE_TABLE->{0});
2246
$dbi->execute($CREATE_TABLE->{2});
2247
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2248
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
2249
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2250
$result = $dbi->select(
2251
    table => 'table1',
2252
    column => [{table2 => ['key3']}],
2253
    join => [
2254
        {
2255
            clause => "left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1",
2256
            table => ['table1', 'table2']
2257
        }
2258
    ]
2259
);
2260
is_deeply($result->all, [{'table2.key3' => 4}]);
2261

            
2262
test 'mycolumn';
2263
$dbi = MyDBI8->connect($NEW_ARGS->{0});
2264
$dbi->execute($CREATE_TABLE->{0});
2265
$dbi->execute($CREATE_TABLE->{2});
2266
$dbi->setup_model;
2267
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2268
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2269
$model = $dbi->model('table1');
2270
$result = $model->select_at(
2271
    column => [
2272
        $model->mycolumn,
2273
        $model->column('table2')
2274
    ]
2275
);
2276
is_deeply($result->one,
2277
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
2278

            
2279
$result = $model->select_at(
2280
    column => [
2281
        $model->mycolumn(['key1']),
2282
        $model->column(table2 => ['key1'])
2283
    ]
2284
);
2285
is_deeply($result->one,
2286
          {key1 => 1, 'table2.key1' => 1});
2287
$result = $model->select_at(
2288
    column => [
2289
        $model->mycolumn(['key1']),
2290
        {table2 => ['key1']}
2291
    ]
2292
);
2293
is_deeply($result->one,
2294
          {key1 => 1, 'table2.key1' => 1});
2295

            
2296
$result = $model->select_at(
2297
    column => [
2298
        $model->mycolumn(['key1']),
2299
        ['table2.key1', as => 'table2.key1']
2300
    ]
2301
);
2302
is_deeply($result->one,
2303
          {key1 => 1, 'table2.key1' => 1});
2304

            
2305
$result = $model->select_at(
2306
    column => [
2307
        $model->mycolumn(['key1']),
2308
        ['table2.key1' => 'table2.key1']
2309
    ]
2310
);
2311
is_deeply($result->one,
2312
          {key1 => 1, 'table2.key1' => 1});
2313

            
2314
test 'dbi method from model';
2315
{
2316
    package MyDBI9;
2317
    
2318
    use base 'DBIx::Custom';
2319
    
2320
    sub connect {
2321
        my $self = shift->SUPER::connect(@_);
2322
        
2323
        $self->include_model('MyModel8')->setup_model;
2324
        
2325
        return $self;
2326
    }
2327
}
2328
$dbi = MyDBI9->connect($NEW_ARGS->{0});
2329
$dbi->execute($CREATE_TABLE->{0});
2330
$model = $dbi->model('table1');
2331
eval{$model->execute('select * from table1')};
2332
ok(!$@);
2333

            
2334
test 'column table option';
2335
$dbi = MyDBI9->connect($NEW_ARGS->{0});
2336
$dbi->execute($CREATE_TABLE->{0});
2337
$dbi->execute($CREATE_TABLE->{2});
2338
$dbi->setup_model;
2339
$dbi->execute('insert into table1 (key1, key2) values (1, 2);');
2340
$dbi->execute('insert into table2 (key1, key3) values (1, 4);');
2341
$model = $dbi->model('table1');
2342
$result = $model->select(
2343
    column => [
2344
        $model->column('table2', {alias => 'table2_alias'})
2345
    ],
2346
    where => {'table2_alias.key3' => 4}
2347
);
2348
is_deeply($result->one, 
2349
          {'table2_alias.key1' => 1, 'table2_alias.key3' => 4});
2350

            
2351
$dbi->separator('__');
2352
$result = $model->select(
2353
    column => [
2354
        $model->column('table2', {alias => 'table2_alias'})
2355
    ],
2356
    where => {'table2_alias.key3' => 4}
2357
);
2358
is_deeply($result->one, 
2359
          {'table2_alias__key1' => 1, 'table2_alias__key3' => 4});
2360

            
2361
$dbi->separator('-');
2362
$result = $model->select(
2363
    column => [
2364
        $model->column('table2', {alias => 'table2_alias'})
2365
    ],
2366
    where => {'table2_alias.key3' => 4}
2367
);
2368
is_deeply($result->one, 
2369
          {'table2_alias-key1' => 1, 'table2_alias-key3' => 4});
2370

            
2371
test 'type option'; # DEPRECATED!
2372
$dbi = DBIx::Custom->connect(
2373
    data_source => 'dbi:SQLite:dbname=:memory:',
2374
    dbi_option => {
2375
        $DBD::SQLite::VERSION > 1.26 ? (sqlite_unicode => 1) : (unicode => 1)
2376
    }
2377
);
2378
my $binary = pack("I3", 1, 2, 3);
2379
$dbi->execute('create table table1(key1, key2)');
2380
$dbi->insert(table => 'table1', param => {key1 => $binary, key2 => 'あ'}, type => [key1 => DBI::SQL_BLOB]);
2381
$result = $dbi->select(table => 'table1');
2382
$row   = $result->one;
2383
is_deeply($row, {key1 => $binary, key2 => 'あ'}, "basic");
2384
$result = $dbi->execute('select length(key1) as key1_length from table1');
2385
$row = $result->one;
2386
is($row->{key1_length}, length $binary);
2387

            
2388
$dbi->insert(table => 'table1', param => {key1 => $binary, key2 => 'あ'}, type => [['key1'] => DBI::SQL_BLOB]);
2389
$result = $dbi->select(table => 'table1');
2390
$row   = $result->one;
2391
is_deeply($row, {key1 => $binary, key2 => 'あ'}, "basic");
2392
$result = $dbi->execute('select length(key1) as key1_length from table1');
2393
$row = $result->one;
2394
is($row->{key1_length}, length $binary);
2395

            
2396

            
2397
test 'bind_type option';
2398
$dbi = DBIx::Custom->connect(
2399
    data_source => 'dbi:SQLite:dbname=:memory:',
2400
    dbi_option => {
2401
        $DBD::SQLite::VERSION > 1.26 ? (sqlite_unicode => 1) : (unicode => 1)
2402
    }
2403
);
2404
$binary = pack("I3", 1, 2, 3);
2405
$dbi->execute('create table table1(key1, key2)');
2406
$dbi->insert(table => 'table1', param => {key1 => $binary, key2 => 'あ'}, bind_type => [key1 => DBI::SQL_BLOB]);
2407
$result = $dbi->select(table => 'table1');
2408
$row   = $result->one;
2409
is_deeply($row, {key1 => $binary, key2 => 'あ'}, "basic");
2410
$result = $dbi->execute('select length(key1) as key1_length from table1');
2411
$row = $result->one;
2412
is($row->{key1_length}, length $binary);
2413

            
2414
$dbi->insert(table => 'table1', param => {key1 => $binary, key2 => 'あ'}, bind_type => [['key1'] => DBI::SQL_BLOB]);
2415
$result = $dbi->select(table => 'table1');
2416
$row   = $result->one;
2417
is_deeply($row, {key1 => $binary, key2 => 'あ'}, "basic");
2418
$result = $dbi->execute('select length(key1) as key1_length from table1');
2419
$row = $result->one;
2420
is($row->{key1_length}, length $binary);
2421

            
2422
test 'model type attribute';
2423
$dbi = DBIx::Custom->connect(
2424
    data_source => 'dbi:SQLite:dbname=:memory:',
2425
    dbi_option => {
2426
        $DBD::SQLite::VERSION > 1.26 ? (sqlite_unicode => 1) : (unicode => 1)
2427
    }
2428
);
2429
$binary = pack("I3", 1, 2, 3);
2430
$dbi->execute('create table table1(key1, key2)');
2431
$model = $dbi->create_model(table => 'table1', bind_type => [key1 => DBI::SQL_BLOB]);
2432
$model->insert(param => {key1 => $binary, key2 => 'あ'});
2433
$result = $dbi->select(table => 'table1');
2434
$row   = $result->one;
2435
is_deeply($row, {key1 => $binary, key2 => 'あ'}, "basic");
2436
$result = $dbi->execute('select length(key1) as key1_length from table1');
2437
$row = $result->one;
2438
is($row->{key1_length}, length $binary);
2439

            
2440
test 'create_model';
2441
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2442
$dbi->execute($CREATE_TABLE->{0});
2443
$dbi->execute($CREATE_TABLE->{2});
2444

            
2445
$dbi->create_model(
2446
    table => 'table1',
2447
    join => [
2448
       'left outer join table2 on table1.key1 = table2.key1'
2449
    ],
2450
    primary_key => ['key1']
2451
);
2452
$model2 = $dbi->create_model(
2453
    table => 'table2'
2454
);
2455
$dbi->create_model(
2456
    table => 'table3',
2457
    filter => [
2458
        key1 => {in => sub { uc $_[0] }}
2459
    ]
2460
);
2461
$dbi->setup_model;
2462
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2463
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2464
$model = $dbi->model('table1');
2465
$result = $model->select(
2466
    column => [$model->mycolumn, $model->column('table2')],
2467
    where => {'table1.key1' => 1}
2468
);
2469
is_deeply($result->one,
2470
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
2471
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
2472

            
2473
test 'model method';
2474
test 'create_model';
2475
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2476
$dbi->execute($CREATE_TABLE->{2});
2477
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2478
$model = $dbi->create_model(
2479
    table => 'table2'
2480
);
2481
$model->method(foo => sub { shift->select(@_) });
2482
is_deeply($model->foo->one, {key1 => 1, key3 => 3});
2483

            
2484
test 'merge_param';
2485
{
2486
    my $dbi = DBIx::Custom->new;
2487
    my $param1 = {key1 => 1, key2 => 2, key3 => 3};
2488
    my $param2 = {key1 => 1, key2 => 2};
2489
    my $param3 = {key1 => 1};
2490
    my $param = $dbi->merge_param($param1, $param2, $param3);
2491
    is_deeply($param, {key1 => [1, 1, 1], key2 => [2, 2], key3 => 3});
2492
}
2493

            
2494
{
2495
    my $dbi = DBIx::Custom->new;
2496
    my $param1 = {key1 => [1, 2], key2 => 1, key3 => [1, 2]};
2497
    my $param2 = {key1 => [3, 4], key2 => [2, 3], key3 => 3};
2498
    my $param = $dbi->merge_param($param1, $param2);
2499
    is_deeply($param, {key1 => [1, 2, 3, 4], key2 => [1, 2, 3], key3 => [1, 2, 3]});
2500
}
2501

            
2502
test 'select() param option';
2503
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2504
$dbi->execute($CREATE_TABLE->{0});
2505
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2506
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2507
$dbi->execute($CREATE_TABLE->{2});
2508
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
2509
$dbi->insert(table => 'table2', param => {key1 => 2, key3 => 5});
2510
$rows = $dbi->select(
2511
    table => 'table1',
2512
    column => 'table1.key1 as table1_key1, key2, key3',
2513
    where   => {'table1.key2' => 3},
2514
    join  => ['inner join (select * from table2 where {= table2.key3})' . 
2515
              ' as table2 on table1.key1 = table2.key1'],
2516
    param => {'table2.key3' => 5}
2517
)->all;
2518
is_deeply($rows, [{table1_key1 => 2, key2 => 3, key3 => 5}]);
2519

            
2520

            
2521
test 'select() wrap option';
2522
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2523
$dbi->execute($CREATE_TABLE->{0});
2524
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2525
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2526
$rows = $dbi->select(
2527
    table => 'table1',
2528
    column => 'key1',
2529
    wrap => ['select * from (', ') as t where key1 = 1']
2530
)->all;
2531
is_deeply($rows, [{key1 => 1}]);
2532

            
2533
eval {
2534
$dbi->select(
2535
    table => 'table1',
2536
    column => 'key1',
2537
    wrap => 'select * from ('
2538
)
2539
};
2540
like($@, qr/array/);
2541

            
2542
test 'select() string where';
2543
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2544
$dbi->execute($CREATE_TABLE->{0});
2545
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2546
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2547
$rows = $dbi->select(
2548
    table => 'table1',
2549
    where => 'key1 = :key1 and key2 = :key2',
2550
    where_param => {key1 => 1, key2 => 2}
2551
)->all;
2552
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2553

            
2554
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2555
$dbi->execute($CREATE_TABLE->{0});
2556
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2557
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2558
$rows = $dbi->select(
2559
    table => 'table1',
2560
    where => [
2561
        'key1 = :key1 and key2 = :key2',
2562
        {key1 => 1, key2 => 2}
2563
    ]
2564
)->all;
2565
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2566

            
2567
test 'delete() string where';
2568
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2569
$dbi->execute($CREATE_TABLE->{0});
2570
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2571
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2572
$dbi->delete(
2573
    table => 'table1',
2574
    where => 'key1 = :key1 and key2 = :key2',
2575
    where_param => {key1 => 1, key2 => 2}
2576
);
2577
$rows = $dbi->select(table => 'table1')->all;
2578
is_deeply($rows, [{key1 => 2, key2 => 3}]);
2579

            
2580
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2581
$dbi->execute($CREATE_TABLE->{0});
2582
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2583
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2584
$dbi->delete(
2585
    table => 'table1',
2586
    where => [
2587
        'key1 = :key1 and key2 = :key2',
2588
         {key1 => 1, key2 => 2}
2589
    ]
2590
);
2591
$rows = $dbi->select(table => 'table1')->all;
2592
is_deeply($rows, [{key1 => 2, key2 => 3}]);
2593

            
2594

            
2595
test 'update() string where';
2596
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2597
$dbi->execute($CREATE_TABLE->{0});
2598
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2599
$dbi->update(
2600
    table => 'table1',
2601
    param => {key1 => 5},
2602
    where => 'key1 = :key1 and key2 = :key2',
2603
    where_param => {key1 => 1, key2 => 2}
2604
);
2605
$rows = $dbi->select(table => 'table1')->all;
2606
is_deeply($rows, [{key1 => 5, key2 => 2}]);
2607

            
2608
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2609
$dbi->execute($CREATE_TABLE->{0});
2610
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2611
$dbi->update(
2612
    table => 'table1',
2613
    param => {key1 => 5},
2614
    where => [
2615
        'key1 = :key1 and key2 = :key2',
2616
        {key1 => 1, key2 => 2}
2617
    ]
2618
);
2619
$rows = $dbi->select(table => 'table1')->all;
2620
is_deeply($rows, [{key1 => 5, key2 => 2}]);
2621

            
2622
test 'insert id and primary_key option';
2623
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2624
$dbi->execute($CREATE_TABLE->{1});
2625
$dbi->insert(
2626
    primary_key => ['key1', 'key2'], 
2627
    table => 'table1',
2628
    id => [1, 2],
2629
    param => {key3 => 3}
2630
);
2631
is($dbi->select(table => 'table1')->one->{key1}, 1);
2632
is($dbi->select(table => 'table1')->one->{key2}, 2);
2633
is($dbi->select(table => 'table1')->one->{key3}, 3);
2634

            
2635
$dbi->delete_all(table => 'table1');
2636
$dbi->insert(
2637
    primary_key => 'key1', 
2638
    table => 'table1',
2639
    id => 0,
2640
    param => {key2 => 2, key3 => 3}
2641
);
2642

            
2643
is($dbi->select(table => 'table1')->one->{key1}, 0);
2644
is($dbi->select(table => 'table1')->one->{key2}, 2);
2645
is($dbi->select(table => 'table1')->one->{key3}, 3);
2646

            
2647
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2648
$dbi->execute($CREATE_TABLE->{1});
2649
$dbi->insert(
2650
    {key3 => 3},
2651
    primary_key => ['key1', 'key2'], 
2652
    table => 'table1',
2653
    id => [1, 2],
2654
);
2655
is($dbi->select(table => 'table1')->one->{key1}, 1);
2656
is($dbi->select(table => 'table1')->one->{key2}, 2);
2657
is($dbi->select(table => 'table1')->one->{key3}, 3);
2658

            
2659

            
2660
test 'model insert id and primary_key option';
2661
$dbi = MyDBI6->connect($NEW_ARGS->{0});
2662
$dbi->execute($CREATE_TABLE->{1});
2663
$dbi->model('table1')->insert(
2664
    id => [1, 2],
2665
    param => {key3 => 3}
2666
);
2667
$result = $dbi->model('table1')->select;
2668
$row = $result->one;
2669
is($row->{key1}, 1);
2670
is($row->{key2}, 2);
2671
is($row->{key3}, 3);
2672

            
2673
$dbi = MyDBI6->connect($NEW_ARGS->{0});
2674
$dbi->execute($CREATE_TABLE->{1});
2675
$dbi->model('table1')->insert(
2676
    {key3 => 3},
2677
    id => [1, 2]
2678
);
2679
$result = $dbi->model('table1')->select;
2680
$row = $result->one;
2681
is($row->{key1}, 1);
2682
is($row->{key2}, 2);
2683
is($row->{key3}, 3);
2684

            
2685
test 'update and id option';
2686
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2687
$dbi->execute($CREATE_TABLE->{1});
2688
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2689
$dbi->update(
2690
    table => 'table1',
2691
    primary_key => ['key1', 'key2'],
2692
    id => [1, 2],
2693
    param => {key3 => 4}
2694
);
2695
is($dbi->select(table => 'table1')->one->{key1}, 1);
2696
is($dbi->select(table => 'table1')->one->{key2}, 2);
2697
is($dbi->select(table => 'table1')->one->{key3}, 4);
2698

            
2699
$dbi->delete_all(table => 'table1');
2700
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2701
$dbi->update(
2702
    table => 'table1',
2703
    primary_key => 'key1',
2704
    id => 0,
2705
    param => {key3 => 4}
2706
);
2707
is($dbi->select(table => 'table1')->one->{key1}, 0);
2708
is($dbi->select(table => 'table1')->one->{key2}, 2);
2709
is($dbi->select(table => 'table1')->one->{key3}, 4);
2710

            
2711
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2712
$dbi->execute($CREATE_TABLE->{1});
2713
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2714
$dbi->update(
2715
    {key3 => 4},
2716
    table => 'table1',
2717
    primary_key => ['key1', 'key2'],
2718
    id => [1, 2]
2719
);
2720
is($dbi->select(table => 'table1')->one->{key1}, 1);
2721
is($dbi->select(table => 'table1')->one->{key2}, 2);
2722
is($dbi->select(table => 'table1')->one->{key3}, 4);
2723

            
2724

            
2725
test 'model update and id option';
2726
$dbi = MyDBI6->connect($NEW_ARGS->{0});
2727
$dbi->execute($CREATE_TABLE->{1});
2728
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2729
$dbi->model('table1')->update(
2730
    id => [1, 2],
2731
    param => {key3 => 4}
2732
);
2733
$result = $dbi->model('table1')->select;
2734
$row = $result->one;
2735
is($row->{key1}, 1);
2736
is($row->{key2}, 2);
2737
is($row->{key3}, 4);
2738

            
2739

            
2740
test 'delete and id option';
2741
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2742
$dbi->execute($CREATE_TABLE->{1});
2743
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2744
$dbi->delete(
2745
    table => 'table1',
2746
    primary_key => ['key1', 'key2'],
2747
    id => [1, 2],
2748
);
2749
is_deeply($dbi->select(table => 'table1')->all, []);
2750

            
2751
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2752
$dbi->delete(
2753
    table => 'table1',
2754
    primary_key => 'key1',
2755
    id => 0,
2756
);
2757
is_deeply($dbi->select(table => 'table1')->all, []);
2758

            
2759

            
2760
test 'model delete and id option';
2761
$dbi = MyDBI6->connect($NEW_ARGS->{0});
2762
$dbi->execute($CREATE_TABLE->{1});
2763
$dbi->execute("create table table2 (key1, key2, key3)");
2764
$dbi->execute("create table table3 (key1, key2, key3)");
2765
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2766
$dbi->model('table1')->delete(id => [1, 2]);
2767
is_deeply($dbi->select(table => 'table1')->all, []);
2768
$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
2769
$dbi->model('table1_1')->delete(id => [1, 2]);
2770
is_deeply($dbi->select(table => 'table1')->all, []);
2771
$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
2772
$dbi->model('table1_3')->delete(id => [1, 2]);
2773
is_deeply($dbi->select(table => 'table1')->all, []);
2774

            
2775

            
2776
test 'select and id option';
2777
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
2778
$dbi->execute($CREATE_TABLE->{1});
2779
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2780
$result = $dbi->select(
2781
    table => 'table1',
2782
    primary_key => ['key1', 'key2'],
2783
    id => [1, 2]
2784
);
2785
$row = $result->one;
2786
is($row->{key1}, 1);
2787
is($row->{key2}, 2);
2788
is($row->{key3}, 3);
2789

            
2790
$dbi->delete_all(table => 'table1');
2791
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2792
$result = $dbi->select(
2793
    table => 'table1',
2794
    primary_key => 'key1',
2795
    id => 0,
2796
);
2797
$row = $result->one;
2798
is($row->{key1}, 0);
2799
is($row->{key2}, 2);
2800
is($row->{key3}, 3);
2801

            
2802
$dbi->delete_all(table => 'table1');
2803
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2804
$result = $dbi->select(
2805
    table => 'table1',
2806
    primary_key => ['key1', 'key2'],
2807
    id => [1, 2]
2808
);
2809
$row = $result->one;
2810
is($row->{key1}, 1);
2811
is($row->{key2}, 2);
2812
is($row->{key3}, 3);
2813

            
2814

            
2815
test 'model select_at';
2816
$dbi = MyDBI6->connect($NEW_ARGS->{0});
2817
$dbi->execute($CREATE_TABLE->{1});
2818
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2819
$result = $dbi->model('table1')->select(id => [1, 2]);
2820
$row = $result->one;
2821
is($row->{key1}, 1);
2822
is($row->{key2}, 2);
2823
is($row->{key3}, 3);
2824

            
2825
test 'column separator is default .';
2826
$dbi = MyDBI7->connect($NEW_ARGS->{0});
2827
$dbi->execute($CREATE_TABLE->{0});
2828
$dbi->execute($CREATE_TABLE->{2});
2829
$dbi->setup_model;
2830
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2831
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2832
$model = $dbi->model('table1');
2833
$result = $model->select(
2834
    column => [$model->column('table2')],
2835
    where => {'table1.key1' => 1}
2836
);
2837
is_deeply($result->one,
2838
          {'table2.key1' => 1, 'table2.key3' => 3});
2839

            
2840
$result = $model->select(
2841
    column => [$model->column('table2' => [qw/key1 key3/])],
2842
    where => {'table1.key1' => 1}
2843
);
2844
is_deeply($result->one,
2845
          {'table2.key1' => 1, 'table2.key3' => 3});
2846

            
2847

            
2848
test 'type_rule from';
2849
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
2850
$dbi->type_rule(
2851
    from1 => {
2852
        date => sub { uc $_[0] }
2853
    }
2854
);
2855
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2856
$dbi->insert({key1 => 'a'}, table => 'table1');
2857
$result = $dbi->select(table => 'table1');
2858
is($result->fetch_first->[0], 'A');
2859

            
2860
$result = $dbi->select(table => 'table1');
2861
is($result->one->{key1}, 'A');
2862

            
2863

            
2864
test 'type_rule into';
2865
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
2866
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2867
$dbi->type_rule(
2868
    into1 => {
2869
        date => sub { uc $_[0] }
2870
    }
2871
);
2872
$dbi->insert({key1 => 'a'}, table => 'table1');
2873
$result = $dbi->select(table => 'table1');
2874
is($result->one->{key1}, 'A');
2875

            
2876
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
2877
$dbi->execute("create table table1 (key1 date, key2 datetime)");
2878
$dbi->type_rule(
2879
    into1 => [
2880
         [qw/date datetime/] => sub { uc $_[0] }
2881
    ]
2882
);
2883
$dbi->insert({key1 => 'a', key2 => 'b'}, table => 'table1');
2884
$result = $dbi->select(table => 'table1');
2885
$row = $result->one;
2886
is($row->{key1}, 'A');
2887
is($row->{key2}, 'B');
2888

            
2889
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
2890
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2891
$dbi->insert({key1 => 'a', key2 => 'B'}, table => 'table1');
2892
$dbi->type_rule(
2893
    into1 => [
2894
        [qw/date datetime/] => sub { uc $_[0] }
2895
    ]
2896
);
2897
$result = $dbi->execute(
2898
    "select * from table1 where key1 = :key1 and key2 = :table1.key2;",
2899
    param => {key1 => 'a', 'table1.key2' => 'b'}
2900
);
2901
$row = $result->one;
2902
is($row->{key1}, 'a');
2903
is($row->{key2}, 'B');
2904

            
2905
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
2906
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2907
$dbi->insert({key1 => 'A', key2 => 'B'}, table => 'table1');
2908
$dbi->type_rule(
2909
    into1 => [
2910
        [qw/date datetime/] => sub { uc $_[0] }
2911
    ]
2912
);
2913
$result = $dbi->execute(
2914
    "select * from table1 where key1 = :key1 and key2 = :table1.key2;",
2915
    param => {key1 => 'a', 'table1.key2' => 'b'},
2916
    table => 'table1'
2917
);
2918
$row = $result->one;
2919
is($row->{key1}, 'A');
2920
is($row->{key2}, 'B');
2921

            
2922
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
2923
$dbi->execute("create table table1 (key1 date, key2 datetime)");
2924
$dbi->register_filter(twice => sub { $_[0] * 2 });
2925
$dbi->type_rule(
2926
    from1 => {
2927
        date => 'twice',
2928
    },
2929
    into1 => {
2930
        date => 'twice',
2931
    }
2932
);
2933
$dbi->insert({key1 => 2}, table => 'table1');
2934
$result = $dbi->select(table => 'table1');
2935
is($result->fetch->[0], 8);
2936

            
2937
test 'type_rule and filter order';
2938
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
2939
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2940
$dbi->type_rule(
2941
    into1 => {
2942
        date => sub { $_[0] . 'b' }
2943
    },
2944
    into2 => {
2945
        date => sub { $_[0] . 'c' }
2946
    },
2947
    from1 => {
2948
        date => sub { $_[0] . 'd' }
2949
    },
2950
    from2 => {
2951
        date => sub { $_[0] . 'e' }
2952
    }
2953
);
2954
$dbi->insert({key1 => '1'}, table => 'table1', filter => {key1 => sub { $_[0] . 'a' }});
2955
$result = $dbi->select(table => 'table1');
2956
$result->filter(key1 => sub { $_[0] . 'f' });
2957
is($result->fetch_first->[0], '1abcdef');
2958

            
2959
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
2960
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2961
$dbi->type_rule(
2962
    from1 => {
2963
        date => sub { $_[0] . 'p' }
2964
    },
2965
    from2 => {
2966
        date => sub { $_[0] . 'q' }
2967
    },
2968
);
2969
$dbi->insert({key1 => '1'}, table => 'table1');
2970
$result = $dbi->select(table => 'table1');
2971
$result->type_rule(
2972
    from1 => {
2973
        date => sub { $_[0] . 'd' }
2974
    },
2975
    from2 => {
2976
        date => sub { $_[0] . 'e' }
2977
    }
2978
);
2979
$result->filter(key1 => sub { $_[0] . 'f' });
2980
is($result->fetch_first->[0], '1def');
2981

            
2982
test 'type_rule_off';
2983
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
2984
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2985
$dbi->type_rule(
2986
    from1 => {
2987
        date => sub { $_[0] * 2 },
2988
    },
2989
    into1 => {
2990
        date => sub { $_[0] * 2 },
2991
    }
2992
);
2993
$dbi->insert({key1 => 2}, table => 'table1', type_rule_off => 1);
2994
$result = $dbi->select(table => 'table1', type_rule_off => 1);
2995
is($result->type_rule_off->fetch->[0], 2);
2996

            
2997
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
2998
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2999
$dbi->type_rule(
3000
    from1 => {
3001
        date => sub { $_[0] * 2 },
3002
    },
3003
    into1 => {
3004
        date => sub { $_[0] * 3 },
3005
    }
3006
);
3007
$dbi->insert({key1 => 2}, table => 'table1', type_rule_off => 1);
3008
$result = $dbi->select(table => 'table1', type_rule_off => 1);
3009
is($result->one->{key1}, 4);
3010

            
3011
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3012
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3013
$dbi->type_rule(
3014
    from1 => {
3015
        date => sub { $_[0] * 2 },
3016
    },
3017
    into1 => {
3018
        date => sub { $_[0] * 3 },
3019
    }
3020
);
3021
$dbi->insert({key1 => 2}, table => 'table1');
3022
$result = $dbi->select(table => 'table1');
3023
is($result->one->{key1}, 12);
3024

            
3025
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3026
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3027
$dbi->type_rule(
3028
    from1 => {
3029
        date => sub { $_[0] * 2 },
3030
    },
3031
    into1 => {
3032
        date => sub { $_[0] * 3 },
3033
    }
3034
);
3035
$dbi->insert({key1 => 2}, table => 'table1');
3036
$result = $dbi->select(table => 'table1');
3037
is($result->fetch->[0], 12);
3038

            
3039
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3040
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3041
$dbi->register_filter(ppp => sub { uc $_[0] });
3042
$dbi->type_rule(
3043
    into1 => {
3044
        date => 'ppp'
3045
    }
3046
);
3047
$dbi->insert({key1 => 'a'}, table => 'table1');
3048
$result = $dbi->select(table => 'table1');
3049
is($result->one->{key1}, 'A');
3050

            
3051
eval{$dbi->type_rule(
3052
    into1 => {
3053
        date => 'pp'
3054
    }
3055
)};
3056
like($@, qr/not registered/);
3057

            
3058
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3059
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3060
eval {
3061
    $dbi->type_rule(
3062
        from1 => {
3063
            Date => sub { $_[0] * 2 },
3064
        }
3065
    );
3066
};
3067
like($@, qr/lower/);
3068

            
3069
eval {
3070
    $dbi->type_rule(
3071
        into1 => {
3072
            Date => sub { $_[0] * 2 },
3073
        }
3074
    );
3075
};
3076
like($@, qr/lower/);
3077

            
3078
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3079
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3080
$dbi->type_rule(
3081
    from1 => {
3082
        date => sub { $_[0] * 2 },
3083
    },
3084
    into1 => {
3085
        date => sub { $_[0] * 3 },
3086
    }
3087
);
3088
$dbi->insert({key1 => 2}, table => 'table1');
3089
$result = $dbi->select(table => 'table1');
3090
$result->type_rule_off;
3091
is($result->one->{key1}, 6);
3092

            
3093
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3094
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3095
$dbi->type_rule(
3096
    from1 => {
3097
        date => sub { $_[0] * 2 },
3098
        datetime => sub { $_[0] * 4 },
3099
    },
3100
);
3101
$dbi->insert({key1 => 2, key2 => 2}, table => 'table1');
3102
$result = $dbi->select(table => 'table1');
3103
$result->type_rule(
3104
    from1 => {
3105
        date => sub { $_[0] * 3 }
3106
    }
3107
);
3108
$row = $result->one;
3109
is($row->{key1}, 6);
3110
is($row->{key2}, 2);
3111

            
3112
$result = $dbi->select(table => 'table1');
3113
$result->type_rule(
3114
    from1 => {
3115
        date => sub { $_[0] * 3 }
3116
    }
3117
);
3118
$row = $result->one;
3119
is($row->{key1}, 6);
3120
is($row->{key2}, 2);
3121

            
3122
$result = $dbi->select(table => 'table1');
3123
$result->type_rule(
3124
    from1 => {
3125
        date => sub { $_[0] * 3 }
3126
    }
3127
);
3128
$row = $result->one;
3129
is($row->{key1}, 6);
3130
is($row->{key2}, 2);
3131
$result = $dbi->select(table => 'table1');
3132
$result->type_rule(
3133
    from1 => [date => sub { $_[0] * 3 }]
3134
);
3135
$row = $result->one;
3136
is($row->{key1}, 6);
3137
is($row->{key2}, 2);
3138
$dbi->register_filter(fivetimes => sub { $_[0] * 5});
3139
$result = $dbi->select(table => 'table1');
3140
$result->type_rule(
3141
    from1 => [date => 'fivetimes']
3142
);
3143
$row = $result->one;
3144
is($row->{key1}, 10);
3145
is($row->{key2}, 2);
3146
$result = $dbi->select(table => 'table1');
3147
$result->type_rule(
3148
    from1 => [date => undef]
3149
);
3150
$row = $result->one;
3151
is($row->{key1}, 2);
3152
is($row->{key2}, 2);
3153

            
3154
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3155
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3156
$dbi->type_rule(
3157
    from1 => {
3158
        date => sub { $_[0] * 2 },
3159
    },
3160
);
3161
$dbi->insert({key1 => 2}, table => 'table1');
3162
$result = $dbi->select(table => 'table1');
3163
$result->filter(key1 => sub { $_[0] * 3 });
3164
is($result->one->{key1}, 12);
3165

            
3166
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3167
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3168
$dbi->type_rule(
3169
    from1 => {
3170
        date => sub { $_[0] * 2 },
3171
    },
3172
);
3173
$dbi->insert({key1 => 2}, table => 'table1');
3174
$result = $dbi->select(table => 'table1');
3175
$result->filter(key1 => sub { $_[0] * 3 });
3176
is($result->fetch->[0], 12);
3177

            
3178
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3179
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3180
$dbi->type_rule(
3181
    into1 => {
3182
        date => sub { $_[0] . 'b' }
3183
    },
3184
    into2 => {
3185
        date => sub { $_[0] . 'c' }
3186
    },
3187
    from1 => {
3188
        date => sub { $_[0] . 'd' }
3189
    },
3190
    from2 => {
3191
        date => sub { $_[0] . 'e' }
3192
    }
3193
);
3194
$dbi->insert({key1 => '1'}, table => 'table1', type_rule_off => 1);
3195
$result = $dbi->select(table => 'table1');
3196
is($result->type_rule_off->fetch_first->[0], '1');
3197
$result = $dbi->select(table => 'table1');
3198
is($result->type_rule_on->fetch_first->[0], '1de');
3199

            
3200
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3201
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3202
$dbi->type_rule(
3203
    into1 => {
3204
        date => sub { $_[0] . 'b' }
3205
    },
3206
    into2 => {
3207
        date => sub { $_[0] . 'c' }
3208
    },
3209
    from1 => {
3210
        date => sub { $_[0] . 'd' }
3211
    },
3212
    from2 => {
3213
        date => sub { $_[0] . 'e' }
3214
    }
3215
);
3216
$dbi->insert({key1 => '1'}, table => 'table1', type_rule1_off => 1);
3217
$result = $dbi->select(table => 'table1');
3218
is($result->type_rule1_off->fetch_first->[0], '1ce');
3219
$result = $dbi->select(table => 'table1');
3220
is($result->type_rule1_on->fetch_first->[0], '1cde');
3221

            
3222
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3223
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3224
$dbi->type_rule(
3225
    into1 => {
3226
        date => sub { $_[0] . 'b' }
3227
    },
3228
    into2 => {
3229
        date => sub { $_[0] . 'c' }
3230
    },
3231
    from1 => {
3232
        date => sub { $_[0] . 'd' }
3233
    },
3234
    from2 => {
3235
        date => sub { $_[0] . 'e' }
3236
    }
3237
);
3238
$dbi->insert({key1 => '1'}, table => 'table1', type_rule2_off => 1);
3239
$result = $dbi->select(table => 'table1');
3240
is($result->type_rule2_off->fetch_first->[0], '1bd');
3241
$result = $dbi->select(table => 'table1');
3242
is($result->type_rule2_on->fetch_first->[0], '1bde');
3243

            
3244
test 'separator';
3245
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
3246
$dbi->execute($CREATE_TABLE->{0});
3247
$dbi->execute($CREATE_TABLE->{2});
3248

            
3249
$dbi->create_model(
3250
    table => 'table1',
3251
    join => [
3252
       'left outer join table2 on table1.key1 = table2.key1'
3253
    ],
3254
    primary_key => ['key1'],
3255
);
3256
$model2 = $dbi->create_model(
3257
    table => 'table2',
3258
);
3259
$dbi->setup_model;
3260
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3261
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
3262
$model = $dbi->model('table1');
3263
$result = $model->select(
3264
    column => [
3265
        $model->mycolumn,
3266
        {table2 => [qw/key1 key3/]}
3267
    ],
3268
    where => {'table1.key1' => 1}
3269
);
3270
is_deeply($result->one,
3271
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
3272
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
3273

            
3274
$dbi->separator('__');
3275
$model = $dbi->model('table1');
3276
$result = $model->select(
3277
    column => [
3278
        $model->mycolumn,
3279
        {table2 => [qw/key1 key3/]}
3280
    ],
3281
    where => {'table1.key1' => 1}
3282
);
3283
is_deeply($result->one,
3284
          {key1 => 1, key2 => 2, 'table2__key1' => 1, 'table2__key3' => 3});
3285
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
3286

            
3287
$dbi->separator('-');
3288
$model = $dbi->model('table1');
3289
$result = $model->select(
3290
    column => [
3291
        $model->mycolumn,
3292
        {table2 => [qw/key1 key3/]}
3293
    ],
3294
    where => {'table1.key1' => 1}
3295
);
3296
is_deeply($result->one,
3297
          {key1 => 1, key2 => 2, 'table2-key1' => 1, 'table2-key3' => 3});
3298
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
3299

            
3300

            
3301
test 'filter_off';
3302
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
3303
$dbi->execute($CREATE_TABLE->{0});
3304
$dbi->execute($CREATE_TABLE->{2});
3305

            
3306
$dbi->create_model(
3307
    table => 'table1',
3308
    join => [
3309
       'left outer join table2 on table1.key1 = table2.key1'
3310
    ],
3311
    primary_key => ['key1'],
3312
);
3313
$dbi->setup_model;
3314
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3315
$model = $dbi->model('table1');
3316
$result = $model->select(column => 'key1');
3317
$result->filter(key1 => sub { $_[0] * 2 });
3318
is_deeply($result->one, {key1 => 2});
3319

            
3320
test 'available_date_type';
3321
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
3322
ok($dbi->can('available_data_type'));
3323

            
3324

            
3325
test 'select prefix option';
3326
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
3327
$dbi->execute($CREATE_TABLE->{0});
3328
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3329
$rows = $dbi->select(prefix => 'key1,', column => 'key2', table => 'table1')->all;
3330
is_deeply($rows, [{key1 => 1, key2 => 2}], "table");
3331

            
3332

            
3333
test 'separator';
3334
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
3335
is($dbi->separator, '.');
3336
$dbi->separator('-');
3337
is($dbi->separator, '-');
3338
$dbi->separator('__');
3339
is($dbi->separator, '__');
3340
eval { $dbi->separator('?') };
3341
like($@, qr/Separator/);
3342

            
3343

            
3344
test 'map_param';
3345
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
3346
$param = $dbi->map_param(
3347
    {id => 1, author => 'Ken', price => 1900},
3348
    id => 'book.id',
3349
    author => ['book.author', sub { '%' . $_[0] . '%' }],
3350
    price => ['book.price', {if => sub { $_[0] eq 1900 }}]
3351
);
3352
is_deeply($param, {'book.id' => 1, 'book.author' => '%Ken%',
3353
  'book.price' => 1900});
3354

            
3355
$param = $dbi->map_param(
3356
    {id => 0, author => 0, price => 0},
3357
    id => 'book.id',
3358
    author => ['book.author', sub { '%' . $_[0] . '%' }],
3359
    price => ['book.price', sub { '%' . $_[0] . '%' },
3360
      {if => sub { $_[0] eq 0 }}]
3361
);
3362
is_deeply($param, {'book.id' => 0, 'book.author' => '%0%', 'book.price' => '%0%'});
3363

            
3364
$param = $dbi->map_param(
3365
    {id => '', author => '', price => ''},
3366
    id => 'book.id',
3367
    author => ['book.author', sub { '%' . $_[0] . '%' }],
3368
    price => ['book.price', sub { '%' . $_[0] . '%' },
3369
      {if => sub { $_[0] eq 1 }}]
3370
);
3371
is_deeply($param, {});
3372

            
3373
$param = $dbi->map_param(
3374
    {id => undef, author => undef, price => undef},
3375
    id => 'book.id',
3376
    price => ['book.price', {if => 'exists'}]
3377
);
3378
is_deeply($param, {'book.price' => undef});
3379

            
3380
$param = $dbi->map_param(
3381
    {price => 'a'},
3382
    id => ['book.id', {if => 'exists'}],
3383
    price => ['book.price', sub { '%' . $_[0] }, {if => 'exists'}]
3384
);
3385
is_deeply($param, {'book.price' => '%a'});
3386

            
3387

            
3388
test 'table_alias';
3389
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3390
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3391
$dbi->type_rule(
3392
    into1 => {
3393
        date => sub { uc $_[0] }
3394
    }
3395
);
3396
$dbi->execute("insert into table1 (key1) values (:table2.key1)", {'table2.key1' => 'a'},
3397
  table_alias => {table2 => 'table1'});
3398
$result = $dbi->select(table => 'table1');
3399
is($result->one->{key1}, 'A');
3400

            
3401

            
3402
test 'order';
3403
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3404
{
3405
    $dbi->execute("create table table1 (key1, key2)");
3406
    $dbi->insert({key1 => 1, key2 => 1}, table => 'table1');
3407
    $dbi->insert({key1 => 1, key2 => 3}, table => 'table1');
3408
    $dbi->insert({key1 => 2, key2 => 2}, table => 'table1');
3409
    $dbi->insert({key1 => 2, key2 => 4}, table => 'table1');
3410
    my $order = $dbi->order;
3411
    $order->prepend('key1', 'key2 desc');
3412
    $result = $dbi->select(table => 'table1', append => "$order");
3413
    is_deeply($result->all, [{key1 => 1, key2 => 3}, {key1 => 1, key2 => 1},
3414
      {key1 => 2, key2 => 4}, {key1 => 2, key2 => 2}]);
3415
    $order->prepend('key1 desc');
3416
    $result = $dbi->select(table => 'table1', append => "$order");
3417
    is_deeply($result->all, [{key1 => 2, key2 => 4}, {key1 => 2, key2 => 2},
3418
      {key1 => 1, key2 => 3}, {key1 => 1, key2 => 1}]);
3419

            
3420
    $order = $dbi->order;
3421
    $order->prepend(['table1-key1'], [qw/table1-key2 desc/]);
3422
    $result = $dbi->select(table => 'table1',
3423
      column => [[key1 => 'table1-key1'], [key2 => 'table1-key2']],
3424
      append => "$order");
3425
    is_deeply($result->all, [{'table1-key1' => 1, 'table1-key2' => 3},
3426
      {'table1-key1' => 1, 'table1-key2' => 1},
3427
      {'table1-key1' => 2, 'table1-key2' => 4},
3428
      {'table1-key1' => 2, 'table1-key2' => 2}]);
3429
}
3430

            
3431
test 'tag_parse';
3432
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3433
$dbi->tag_parse(0);
3434
{
3435
    $dbi->execute("create table table1 (key1, key2)");
3436
    $dbi->insert({key1 => 1, key2 => 1}, table => 'table1');
3437
    eval {$dbi->execute("select * from table1 where {= key1}", {key1 => 1})};
3438
    ok($@);
3439
}
3440

            
3441
test 'last_sql';
3442
{
3443
    my $dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3444
    $dbi->execute("create table table1 (key1, key2)");
3445
    $dbi->execute('select * from table1');
3446
    is($dbi->last_sql, 'select * from table1;');
3447
    
3448
    eval{$dbi->execute("aaa")};
3449
    is($dbi->last_sql, 'aaa;');
3450
    
3451
}
3452

            
3453
test 'DBIx::Custom header';
3454
{
3455
    my $dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3456
    $dbi->execute("create table table1 (key1, key2)");
3457
    my $result = $dbi->execute('select key1 as h1, key2 as h2 from table1');
3458
    
3459
    is_deeply($result->header, [qw/h1 h2/]);
3460
    
3461
}
3462

            
3463
test 'Named placeholder :name(operater) syntax';
3464
$dbi->execute($DROP_TABLE->{0});
3465
$dbi->execute($CREATE_TABLE->{1});
3466
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3467
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3468

            
3469
$source = "select * from table1 where :key1{=} and :key2{=}";
3470
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
3471
$rows = $result->all;
3472
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3473

            
3474
$source = "select * from table1 where :key1{ = } and :key2{=}";
3475
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
3476
$rows = $result->all;
3477
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3478

            
3479
$source = "select * from table1 where :key1{<} and :key2{=}";
3480
$result = $dbi->execute($source, param => {key1 => 5, key2 => 2});
3481
$rows = $result->all;
3482
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3483

            
3484
$source = "select * from table1 where :table1.key1{=} and :table1.key2{=}";
3485
$result = $dbi->execute(
3486
    $source,
3487
    param => {'table1.key1' => 1, 'table1.key2' => 1},
3488
    filter => {'table1.key2' => sub { $_[0] * 2 }}
3489
);
3490
$rows = $result->all;
3491
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3492

            
3493
test 'high perfomance way';
3494
$dbi->execute($DROP_TABLE->{0});
3495
$dbi->execute("create table table1 (ab, bc, ik, hi, ui, pq, dc);");
3496
$rows = [
3497
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3498
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3499
];
3500
{
3501
    my $query;
3502
    foreach my $row (@$rows) {
3503
      $query ||= $dbi->insert($row, table => 'table1', query => 1);
3504
      $dbi->execute($query, $row, filter => {ab => sub { $_[0] * 2 }});
3505
    }
3506
    is_deeply($dbi->select(table => 'table1')->all,
3507
      [
3508
          {ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3509
          {ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3510
      ]
3511
    );
3512
}
3513

            
3514
$dbi->execute($DROP_TABLE->{0});
3515
$dbi->execute("create table table1 (ab, bc, ik, hi, ui, pq, dc);");
3516
$rows = [
3517
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3518
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3519
];
3520
{
3521
    my $query;
3522
    my $sth;
3523
    foreach my $row (@$rows) {
3524
      $query ||= $dbi->insert($row, table => 'table1', query => 1);
3525
      $sth ||= $query->sth;
3526
      $sth->execute(map { $row->{$_} } sort keys %$row);
3527
    }
3528
    is_deeply($dbi->select(table => 'table1')->all,
3529
      [
3530
          {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3531
          {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3532
      ]
3533
    );
3534
}
3535
=cut