DBIx-Custom / t / common.t /
Newer Older
3792 lines | 122.344kb
added common test executing ...
Yuki Kimoto authored on 2011-08-07
1
use Test::More;
2
use strict;
3
use warnings;
test cleanup
Yuki Kimoto authored on 2011-08-10
4
use Encode qw/encode_utf8/;
cleanup test
Yuki Kimoto authored on 2011-08-10
5
use FindBin;
6
use lib "$FindBin::Bin/common";
7

            
added common test executing ...
Yuki Kimoto authored on 2011-08-07
8
my $dbi;
9

            
10
plan skip_all => $ENV{DBIX_CUSTOM_SKIP_MESSAGE} || 'common.t is always skipped'
11
  unless $ENV{DBIX_CUSTOM_TEST_RUN}
12
    && eval { $dbi = DBIx::Custom->connect; 1 };
13

            
14
plan 'no_plan';
15

            
test cleanup
Yuki Kimoto authored on 2011-08-10
16
use MyDBI1;
17
{
18
    package MyDBI4;
19

            
20
    use strict;
21
    use warnings;
22

            
23
    use base 'DBIx::Custom';
24

            
25
    sub connect {
26
        my $self = shift->SUPER::connect(@_);
27
        
28
        $self->include_model(
29
            MyModel2 => [
test cleanup
Yuki Kimoto authored on 2011-08-10
30
                'table1',
31
                'table2'
test cleanup
Yuki Kimoto authored on 2011-08-10
32
            ]
33
        );
34
    }
35

            
36
    package MyModel2::Base1;
37

            
38
    use strict;
39
    use warnings;
40

            
41
    use base 'DBIx::Custom::Model';
42

            
test cleanup
Yuki Kimoto authored on 2011-08-10
43
    package MyModel2::table1;
test cleanup
Yuki Kimoto authored on 2011-08-10
44

            
45
    use strict;
46
    use warnings;
47

            
48
    use base 'MyModel2::Base1';
49

            
50
    sub insert {
51
        my ($self, $param) = @_;
52
        
53
        return $self->SUPER::insert(param => $param);
54
    }
55

            
56
    sub list { shift->select; }
57

            
test cleanup
Yuki Kimoto authored on 2011-08-10
58
    package MyModel2::table2;
test cleanup
Yuki Kimoto authored on 2011-08-10
59

            
60
    use strict;
61
    use warnings;
62

            
63
    use base 'MyModel2::Base1';
64

            
65
    sub insert {
66
        my ($self, $param) = @_;
67
        
68
        return $self->SUPER::insert(param => $param);
69
    }
70

            
71
    sub list { shift->select; }
72
}
73
{
74
     package MyDBI5;
75

            
76
    use strict;
77
    use warnings;
78

            
79
    use base 'DBIx::Custom';
80

            
81
    sub connect {
82
        my $self = shift->SUPER::connect(@_);
83
        
84
        $self->include_model('MyModel4');
85
    }
86
}
87
{
88
    package MyDBI6;
89
    
90
    use base 'DBIx::Custom';
91
    
92
    sub connect {
93
        my $self = shift->SUPER::connect(@_);
94
        
95
        $self->include_model('MyModel5');
96
        
97
        return $self;
98
    }
99
}
100
{
101
    package MyDBI7;
102
    
103
    use base 'DBIx::Custom';
104
    
105
    sub connect {
106
        my $self = shift->SUPER::connect(@_);
107
        
108
        $self->include_model('MyModel6');
109
        
110
        
111
        return $self;
112
    }
113
}
114
{
115
    package MyDBI8;
116
    
117
    use base 'DBIx::Custom';
118
    
119
    sub connect {
120
        my $self = shift->SUPER::connect(@_);
121
        
122
        $self->include_model('MyModel7');
123
        
124
        return $self;
125
    }
126
}
127

            
128
{
129
    package MyDBI9;
130
    
131
    use base 'DBIx::Custom';
132
    
133
    sub connect {
134
        my $self = shift->SUPER::connect(@_);
135
        
cleanup test
Yuki Kimoto authored on 2011-08-10
136
        $self->include_model('MyModel8');
test cleanup
Yuki Kimoto authored on 2011-08-10
137
        
138
        return $self;
139
    }
140
}
141

            
cleanup test
Yuki Kimoto authored on 2011-08-08
142
$SIG{__WARN__} = sub { warn $_[0] unless $_[0] =~ /DEPRECATED/};
143
sub test { print "# $_[0]\n" }
144

            
added common test executing ...
Yuki Kimoto authored on 2011-08-07
145
# Constant
146
my $create_table1 = $dbi->create_table1;
cleanup test
Yuki Kimoto authored on 2011-08-08
147
my $create_table1_2 = $dbi->create_table1_2;
test cleanup
Yuki Kimoto authored on 2011-08-10
148
my $create_table1_type = $dbi->create_table1_type;
test cleanup
Yuki Kimoto authored on 2011-08-10
149
my $create_table1_highperformance = $dbi->create_table1_highperformance;
test cleanup
Yuki Kimoto authored on 2011-08-10
150
my $create_table2 = $dbi->create_table2;
test cleanup
Yuki Kimoto authored on 2011-08-10
151
my $create_table2_2 = $dbi->create_table2_2;
152
my $create_table3 = $dbi->create_table3;
test cleanup
Yuki Kimoto authored on 2011-08-10
153
my $create_table_reserved = $dbi->create_table_reserved;
cleanup test
Yuki Kimoto authored on 2011-08-10
154
my $q = substr($dbi->quote, 0, 1);
155
my $p = substr($dbi->quote, 1, 1) || $q;
test cleanup
Yuki Kimoto authored on 2011-08-10
156
my $date_typename = $dbi->date_typename;
157
my $time_typename = $dbi->time_typename;
158
my $datetime_typename = $dbi->datetime_typename;
159
my $date_datatype = $dbi->date_datatype;
160
my $time_datatype = $dbi->time_datatype;
161
my $datetime_datatype = $dbi->datetime_datatype;
added common test executing ...
Yuki Kimoto authored on 2011-08-07
162

            
163
# Variable
cleanup test
Yuki Kimoto authored on 2011-08-08
164
# Variables
165
my $builder;
166
my $datas;
167
my $sth;
168
my $source;
169
my @sources;
170
my $select_source;
171
my $insert_source;
172
my $update_source;
173
my $param;
174
my $params;
175
my $sql;
176
my $result;
177
my $row;
178
my @rows;
179
my $rows;
180
my $query;
181
my @queries;
182
my $select_query;
183
my $insert_query;
184
my $update_query;
185
my $ret_val;
186
my $infos;
added common test executing ...
Yuki Kimoto authored on 2011-08-07
187
my $model;
cleanup test
Yuki Kimoto authored on 2011-08-08
188
my $model2;
189
my $where;
190
my $update_param;
191
my $insert_param;
192
my $join;
cleanup test
Yuki Kimoto authored on 2011-08-10
193
my $binary;
added common test executing ...
Yuki Kimoto authored on 2011-08-07
194

            
195
# Drop table
196
eval { $dbi->execute('drop table table1') };
test cleanup
Yuki Kimoto authored on 2011-08-10
197

            
198
test 'type_rule into';
199
$dbi = DBIx::Custom->connect;
200
eval { $dbi->execute('drop table table1') };
201
$dbi->execute($create_table1_type);
202
$dbi->type_rule(
203
    into1 => {
204
        $date_typename => sub { '2010-' . $_[0] }
205
    }
206
);
207
$dbi->insert({key1 => '01-01'}, table => 'table1');
208
$result = $dbi->select(table => 'table1');
209
is($result->one->{key1}, '2010-01-01');
210

            
211
$dbi = DBIx::Custom->connect;
212
eval { $dbi->execute('drop table table1') };
213
$dbi->execute($create_table1_type);
214
$dbi->type_rule(
215
    into1 => [
216
         [$date_typename, $datetime_typename] => sub {
217
            my $value = shift;
218
            $value =~ s/02/03/g;
219
            return $value;
220
         }
221
    ]
222
);
223
$dbi->insert({key1 => '2010-01-02', key2 => '2010-01-01 01:01:02'}, table => 'table1');
224
$result = $dbi->select(table => 'table1');
225
$row = $result->one;
226
is($row->{key1}, '2010-01-03');
227
is($row->{key2}, '2010-01-01 01:01:03');
228

            
229
$dbi = DBIx::Custom->connect;
230
eval { $dbi->execute('drop table table1') };
231
$dbi->execute($create_table1_type);
232
$dbi->insert({key1 => '2010-01-03', key2 => '2010-01-01 01:01:03'}, table => 'table1');
233
$dbi->type_rule(
234
    into1 => [
235
        [$date_typename, $datetime_typename] => sub {
236
            my $value = shift;
237
            $value =~ s/02/03/g;
238
            return $value;
239
        }
240
    ]
241
);
242
$result = $dbi->execute(
243
    "select * from table1 where key1 = :key1 and key2 = :table1.key2;",
244
    param => {key1 => '2010-01-03', 'table1.key2' => '2010-01-01 01:01:02'}
245
);
246
$row = $result->one;
247
is($row->{key1}, '2010-01-03');
248
is($row->{key2}, '2010-01-01 01:01:03');
249

            
250
$dbi = DBIx::Custom->connect;
251
eval { $dbi->execute('drop table table1') };
252
$dbi->execute($create_table1_type);
253
$dbi->insert({key1 => '2010-01-03', key2 => '2010-01-01 01:01:03'}, table => 'table1');
254
$dbi->type_rule(
255
    into1 => [
256
        [$date_typename, $datetime_typename] => sub {
257
            my $value = shift;
258
            $value =~ s/02/03/g;
259
            return $value;
260
        }
261
    ]
262
);
263
$result = $dbi->execute(
264
    "select * from table1 where key1 = :key1 and key2 = :table1.key2;",
265
    param => {key1 => '2010-01-02', 'table1.key2' => '2010-01-01 01:01:02'},
266
    table => 'table1'
267
);
268
$row = $result->one;
269
is($row->{key1}, '2010-01-03');
270
is($row->{key2}, '2010-01-01 01:01:03');
271

            
272
$dbi = DBIx::Custom->connect;
273
eval { $dbi->execute('drop table table1') };
274
$dbi->execute($create_table1_type);
275
$dbi->register_filter(convert => sub {
276
    my $value = shift;
277
    $value =~ s/02/03/;
278
    return $value;
279
});
280
$dbi->type_rule(
281
    from1 => {
282
        $date_datatype => 'convert',
283
    },
284
    into1 => {
285
        $date_typename => 'convert',
286
    }
287
);
288
$dbi->insert({key1 => '2010-02-02'}, table => 'table1');
289
$result = $dbi->select(table => 'table1');
290
is($result->fetch->[0], '2010-03-03');
291

            
292

            
293

            
294

            
295

            
296

            
297

            
298

            
299

            
300

            
test cleanup
Yuki Kimoto authored on 2011-08-10
301
test 'type_rule and filter order';
302
$dbi = DBIx::Custom->connect;
303
eval { $dbi->execute('drop table table1') };
304
$dbi->execute($create_table1_type);
305
$dbi->type_rule(
306
    into1 => {
307
        $date_typename => sub { my $v = shift; $v =~ s/4/5/; return $v }
308
    },
309
    into2 => {
310
        $date_typename => sub { my $v = shift; $v =~ s/5/6/; return $v }
311
    },
312
    from1 => {
313
        $date_datatype => sub { my $v = shift; $v =~ s/6/7/; return $v }
314
    },
315
    from2 => {
316
        $date_datatype => sub { my $v = shift; $v =~ s/7/8/; return $v }
317
    }
318
);
319
$dbi->insert({key1 => '2010-01-03'}, 
320
  table => 'table1', filter => {key1 => sub { my $v = shift; $v =~ s/3/4/; return $v }});
321
$result = $dbi->select(table => 'table1');
322
$result->filter(key1 => sub { my $v = shift; $v =~ s/8/9/; return $v });
323
is($result->fetch_first->[0], '2010-01-09');
test cleanup
Yuki Kimoto authored on 2011-08-10
324

            
325

            
test cleanup
Yuki Kimoto authored on 2011-08-10
326
$dbi = DBIx::Custom->connect;
327
eval { $dbi->execute('drop table table1') };
328
$dbi->execute($create_table1_type);
329
$dbi->type_rule(
330
    from1 => {
331
        $date_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v }
332
    },
333
    from2 => {
334
        $date_datatype => sub { my $v = shift; $v =~ s/4/5/; return $v }
335
    },
336
);
337
$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
338
$result = $dbi->select(table => 'table1');
339
$result->type_rule(
340
    from1 => {
341
        $date_datatype => sub { my $v = shift; $v =~ s/3/6/; return $v }
342
    },
343
    from2 => {
344
        $date_datatype => sub { my $v = shift; $v =~ s/6/8/; return $v }
345
    }
346
);
347
$result->filter(key1 => sub { my $v = shift; $v =~ s/8/9/; return $v });
348
is($result->fetch_first->[0], '2010-01-09');
test cleanup
Yuki Kimoto authored on 2011-08-10
349

            
test cleanup
Yuki Kimoto authored on 2011-08-10
350
test 'type_rule_off';
351
$dbi = DBIx::Custom->connect;
352
eval { $dbi->execute('drop table table1') };
353
$dbi->execute($create_table1_type);
354
$dbi->type_rule(
355
    from1 => {
356
        $date_datatype => sub { my $v = shift; $v =~ s/3/5/; return $v }
357
    },
358
    into1 => {
359
        $date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
360
    }
361
);
362
$dbi->insert({key1 => '2010-01-03'}, table => 'table1', type_rule_off => 1);
363
$result = $dbi->select(table => 'table1', type_rule_off => 1);
364
is($result->type_rule_off->fetch->[0], '2010-01-03');
test cleanup
Yuki Kimoto authored on 2011-08-10
365

            
test cleanup
Yuki Kimoto authored on 2011-08-10
366
$dbi = DBIx::Custom->connect;
367
eval { $dbi->execute('drop table table1') };
368
$dbi->execute($create_table1_type);
369
$dbi->type_rule(
370
    from1 => {
371
        $date_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v }
372
    },
373
    into1 => {
374
        $date_typename => sub { my $v = shift; $v =~ s/3/5/; return $v }
375
    }
376
);
377
$dbi->insert({key1 => '2010-01-03'}, table => 'table1', type_rule_off => 1);
378
$result = $dbi->select(table => 'table1', type_rule_off => 1);
379
is($result->one->{key1}, '2010-01-04');
test cleanup
Yuki Kimoto authored on 2011-08-10
380

            
test cleanup
Yuki Kimoto authored on 2011-08-10
381
$dbi = DBIx::Custom->connect;
382
eval { $dbi->execute('drop table table1') };
383
$dbi->execute($create_table1_type);
384
$dbi->type_rule(
385
    from1 => {
386
        $date_datatype => sub { my $v = shift; $v =~ s/4/5/; return $v }
387
    },
388
    into1 => {
389
        $date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
390
    }
391
);
392
$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
393
$result = $dbi->select(table => 'table1');
394
is($result->one->{key1}, '2010-01-05');
test cleanup
Yuki Kimoto authored on 2011-08-10
395

            
test cleanup
Yuki Kimoto authored on 2011-08-10
396
$dbi = DBIx::Custom->connect;
397
eval { $dbi->execute('drop table table1') };
398
$dbi->execute($create_table1_type);
399
$dbi->type_rule(
400
    from1 => {
401
        $date_datatype => sub { my $v = shift; $v =~ s/4/5/; return $v }
402
    },
403
    into1 => {
404
        $date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
405
    }
406
);
407
$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
408
$result = $dbi->select(table => 'table1');
409
is($result->fetch->[0], '2010-01-05');
test cleanup
Yuki Kimoto authored on 2011-08-10
410

            
test cleanup
Yuki Kimoto authored on 2011-08-10
411
$dbi = DBIx::Custom->connect;
412
eval { $dbi->execute('drop table table1') };
413
$dbi->execute($create_table1_type);
414
$dbi->register_filter(ppp => sub { my $v = shift; $v =~ s/3/4/; return $v });
415
$dbi->type_rule(
416
    into1 => {
417
        $date_typename => 'ppp'
418
    }
419
);
420
$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
421
$result = $dbi->select(table => 'table1');
422
is($result->one->{key1}, '2010-01-04');
test cleanup
Yuki Kimoto authored on 2011-08-10
423

            
test cleanup
Yuki Kimoto authored on 2011-08-10
424
eval{$dbi->type_rule(
425
    into1 => {
426
        $date_typename => 'pp'
427
    }
428
)};
429
like($@, qr/not registered/);
test cleanup
Yuki Kimoto authored on 2011-08-10
430

            
test cleanup
Yuki Kimoto authored on 2011-08-10
431
$dbi = DBIx::Custom->connect;
432
eval { $dbi->execute('drop table table1') };
433
$dbi->execute($create_table1_type);
434
eval {
435
    $dbi->type_rule(
436
        from1 => {
437
            Date => sub { $_[0] * 2 },
438
        }
439
    );
440
};
441
like($@, qr/lower/);
test cleanup
Yuki Kimoto authored on 2011-08-10
442

            
test cleanup
Yuki Kimoto authored on 2011-08-10
443
eval {
444
    $dbi->type_rule(
445
        into1 => {
446
            Date => sub { $_[0] * 2 },
447
        }
448
    );
449
};
450
like($@, qr/lower/);
test cleanup
Yuki Kimoto authored on 2011-08-10
451

            
test cleanup
Yuki Kimoto authored on 2011-08-10
452
$dbi = DBIx::Custom->connect;
453
eval { $dbi->execute('drop table table1') };
454
$dbi->execute($create_table1_type);
455
$dbi->type_rule(
456
    from1 => {
457
        $date_datatype => sub { my $v = shift; $v =~ s/4/5/; return $v }
458
    },
459
    into1 => {
460
        $date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
461
    }
462
);
463
$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
464
$result = $dbi->select(table => 'table1');
465
$result->type_rule_off;
466
is($result->one->{key1}, '2010-01-04');
test cleanup
Yuki Kimoto authored on 2011-08-10
467

            
test cleanup
Yuki Kimoto authored on 2011-08-10
468
$dbi = DBIx::Custom->connect;
469
eval { $dbi->execute('drop table table1') };
470
$dbi->execute($create_table1_type);
471
$dbi->type_rule(
472
    from1 => {
473
        $date_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v },
474
        $datetime_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v }
475
    },
476
);
477
$dbi->insert({key1 => '2010-01-03', key2 => '2010-01-01 01:01:03'}, table => 'table1');
478
$result = $dbi->select(table => 'table1');
479
$result->type_rule(
480
    from1 => {
481
        $date_datatype => sub { my $v = shift; $v =~ s/3/5/; return $v }
482
    }
483
);
484
$row = $result->one;
485
is($row->{key1}, '2010-01-05');
486
is($row->{key2}, '2010-01-01 01:01:03');
test cleanup
Yuki Kimoto authored on 2011-08-10
487

            
test cleanup
Yuki Kimoto authored on 2011-08-10
488
$result = $dbi->select(table => 'table1');
489
$result->type_rule(
490
    from1 => {
491
        $date_datatype => sub { my $v = shift; $v =~ s/3/5/; return $v }
492
    }
493
);
494
$row = $result->one;
495
is($row->{key1}, '2010-01-05');
496
is($row->{key2}, '2010-01-01 01:01:03');
test cleanup
Yuki Kimoto authored on 2011-08-10
497

            
test cleanup
Yuki Kimoto authored on 2011-08-10
498
$result = $dbi->select(table => 'table1');
499
$result->type_rule(
500
    from1 => {
501
        $date_datatype => sub { my $v = shift; $v =~ s/3/5/; return $v }
502
    }
503
);
504
$row = $result->one;
505
is($row->{key1}, '2010-01-05');
506
is($row->{key2}, '2010-01-01 01:01:03');
test cleanup
Yuki Kimoto authored on 2011-08-10
507

            
test cleanup
Yuki Kimoto authored on 2011-08-10
508
$result = $dbi->select(table => 'table1');
509
$result->type_rule(
510
    from1 => [$date_datatype => sub { my $v = shift; $v =~ s/3/5/; return $v }]
511
);
512
$row = $result->one;
513
is($row->{key1}, '2010-01-05');
514
is($row->{key2}, '2010-01-01 01:01:03');
test cleanup
Yuki Kimoto authored on 2011-08-10
515

            
test cleanup
Yuki Kimoto authored on 2011-08-10
516
$dbi->register_filter(five => sub { my $v = shift; $v =~ s/3/5/; return $v });
517
$result = $dbi->select(table => 'table1');
518
$result->type_rule(
519
    from1 => [$date_datatype => 'five']
520
);
521
$row = $result->one;
522
is($row->{key1}, '2010-01-05');
523
is($row->{key2}, '2010-01-01 01:01:03');
test cleanup
Yuki Kimoto authored on 2011-08-10
524

            
test cleanup
Yuki Kimoto authored on 2011-08-10
525
$result = $dbi->select(table => 'table1');
526
$result->type_rule(
527
    from1 => [$date_datatype => undef]
528
);
529
$row = $result->one;
530
is($row->{key1}, '2010-01-03');
531
is($row->{key2}, '2010-01-01 01:01:03');
test cleanup
Yuki Kimoto authored on 2011-08-10
532

            
test cleanup
Yuki Kimoto authored on 2011-08-10
533
$dbi = DBIx::Custom->connect;
534
eval { $dbi->execute('drop table table1') };
535
$dbi->execute($create_table1_type);
536
$dbi->type_rule(
537
    from1 => {
538
        $date_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v },
539
    },
540
);
541
$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
542
$result = $dbi->select(table => 'table1');
543
$result->filter(key1 => sub { my $v = shift; $v =~ s/4/5/; return $v });
544
is($result->one->{key1}, '2010-01-05');
test cleanup
Yuki Kimoto authored on 2011-08-10
545

            
test cleanup
Yuki Kimoto authored on 2011-08-10
546
$dbi = DBIx::Custom->connect;
547
eval { $dbi->execute('drop table table1') };
548
$dbi->execute($create_table1_type);
549
$dbi->type_rule(
550
    from1 => {
551
        $date_datatype => sub { my $v = shift; $v =~ s/3/4/; return $v }
552
    },
553
);
554
$dbi->insert({key1 => '2010-01-03'}, table => 'table1');
555
$result = $dbi->select(table => 'table1');
556
$result->filter(key1 => sub { my $v = shift; $v =~ s/4/5/; return $v });
557
is($result->fetch->[0], '2010-01-05');
test cleanup
Yuki Kimoto authored on 2011-08-10
558

            
test cleanup
Yuki Kimoto authored on 2011-08-10
559
$dbi = DBIx::Custom->connect;
560
eval { $dbi->execute('drop table table1') };
561
$dbi->execute($create_table1_type);
562
$dbi->type_rule(
563
    into1 => {
564
        $date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
565
    },
566
    into2 => {
567
        $date_typename => sub { my $v = shift; $v =~ s/3/5/; return $v }
568
    },
569
    from1 => {
570
        $date_datatype => sub { my $v = shift; $v =~ s/3/6/; return $v }
571
    },
572
    from2 => {
573
        $date_datatype => sub { my $v = shift; $v =~ s/(3|6)/7/; return $v }
574
    }
575
);
576
$dbi->insert({key1 => '2010-01-03'}, table => 'table1', type_rule_off => 1);
577
$result = $dbi->select(table => 'table1');
578
is($result->type_rule_off->fetch_first->[0], '2010-01-03');
579
$result = $dbi->select(table => 'table1');
580
is($result->type_rule_on->fetch_first->[0], '2010-01-07');
test cleanup
Yuki Kimoto authored on 2011-08-10
581

            
test cleanup
Yuki Kimoto authored on 2011-08-10
582
$dbi = DBIx::Custom->connect;
583
eval { $dbi->execute('drop table table1') };
584
$dbi->execute($create_table1_type);
585
$dbi->type_rule(
586
    into1 => {
587
        $date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
588
    },
589
    into2 => {
590
        $date_typename => sub { my $v = shift; $v =~ s/3/5/; return $v }
591
    },
592
    from1 => {
593
        $date_datatype => sub { my $v = shift; $v =~ s/(3|5)/6/; return $v }
594
    },
595
    from2 => {
596
        $date_datatype => sub { my $v = shift; $v =~ s/6/7/; return $v }
597
    }
598
);
599
$dbi->insert({key1 => '2010-01-03'}, table => 'table1', type_rule1_off => 1);
600
$result = $dbi->select(table => 'table1');
601
is($result->type_rule1_off->fetch_first->[0], '2010-01-05');
602
$result = $dbi->select(table => 'table1');
603
is($result->type_rule1_on->fetch_first->[0], '2010-01-07');
test cleanup
Yuki Kimoto authored on 2011-08-10
604

            
test cleanup
Yuki Kimoto authored on 2011-08-10
605
$dbi = DBIx::Custom->connect;
606
eval { $dbi->execute('drop table table1') };
607
$dbi->execute($create_table1_type);
608
$dbi->type_rule(
609
    into1 => {
610
        $date_typename => sub { my $v = shift; $v =~ s/3/5/; return $v }
611
    },
612
    into2 => {
613
        $date_typename => sub { my $v = shift; $v =~ s/3/4/; return $v }
614
    },
615
    from1 => {
616
        $date_datatype => sub { my $v = shift; $v =~ s/5/6/; return $v }
617
    },
618
    from2 => {
619
        $date_datatype => sub { my $v = shift; $v =~ s/(3|6)/7/; return $v }
620
    }
621
);
622
$dbi->insert({key1 => '2010-01-03'}, table => 'table1', type_rule2_off => 1);
623
$result = $dbi->select(table => 'table1');
624
is($result->type_rule2_off->fetch_first->[0], '2010-01-06');
625
$result = $dbi->select(table => 'table1');
626
is($result->type_rule2_on->fetch_first->[0], '2010-01-07');
test cleanup
Yuki Kimoto authored on 2011-08-10
627

            
628

            
added common test executing ...
Yuki Kimoto authored on 2011-08-07
629

            
630
# Create table
test cleanup
Yuki Kimoto authored on 2011-08-10
631
$dbi = DBIx::Custom->connect;
test cleanup
Yuki Kimoto authored on 2011-08-10
632
eval { $dbi->execute('drop table table1') };
added common test executing ...
Yuki Kimoto authored on 2011-08-07
633
$dbi->execute($create_table1);
634
$model = $dbi->create_model(table => 'table1');
635
$model->insert({key1 => 1, key2 => 2});
636
is_deeply($model->select->all, [{key1 => 1, key2 => 2}]);
637

            
cleanup test
Yuki Kimoto authored on 2011-08-08
638
test 'DBIx::Custom::Result test';
639
$dbi->delete_all(table => 'table1');
640
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
641
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
642
$source = "select key1, key2 from table1";
643
$query = $dbi->create_query($source);
644
$result = $dbi->execute($query);
645

            
646
@rows = ();
647
while (my $row = $result->fetch) {
648
    push @rows, [@$row];
649
}
650
is_deeply(\@rows, [[1, 2], [3, 4]], "fetch");
651

            
652
$result = $dbi->execute($query);
653
@rows = ();
654
while (my $row = $result->fetch_hash) {
655
    push @rows, {%$row};
656
}
657
is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "fetch_hash");
658

            
659
$result = $dbi->execute($query);
660
$rows = $result->fetch_all;
661
is_deeply($rows, [[1, 2], [3, 4]], "fetch_all");
662

            
663
$result = $dbi->execute($query);
664
$rows = $result->fetch_hash_all;
665
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "all");
666

            
667
test 'Insert query return value';
668
$source = "insert into table1 {insert_param key1 key2}";
669
$query = $dbi->execute($source, {}, query => 1);
670
$ret_val = $dbi->execute($query, param => {key1 => 1, key2 => 2});
671
ok($ret_val);
672

            
673
test 'Direct query';
674
$dbi->delete_all(table => 'table1');
675
$insert_source = "insert into table1 {insert_param key1 key2}";
676
$dbi->execute($insert_source, param => {key1 => 1, key2 => 2});
677
$result = $dbi->execute('select * from table1;');
678
$rows = $result->all;
679
is_deeply($rows, [{key1 => 1, key2 => 2}]);
680

            
681
test 'Filter basic';
682
$dbi->delete_all(table => 'table1');
683
$dbi->register_filter(twice       => sub { $_[0] * 2}, 
684
                    three_times => sub { $_[0] * 3});
685

            
686
$insert_source  = "insert into table1 {insert_param key1 key2};";
687
$insert_query = $dbi->execute($insert_source, {}, query => 1);
688
$insert_query->filter({key1 => 'twice'});
689
$dbi->execute($insert_query, param => {key1 => 1, key2 => 2});
690
$result = $dbi->execute('select * from table1;');
691
$rows = $result->filter({key2 => 'three_times'})->all;
692
is_deeply($rows, [{key1 => 2, key2 => 6}], "filter fetch_filter");
693

            
694
test 'Filter in';
695
$dbi->delete_all(table => 'table1');
696
$insert_source  = "insert into table1 {insert_param key1 key2};";
697
$insert_query = $dbi->execute($insert_source, {}, query => 1);
698
$dbi->execute($insert_query, param => {key1 => 2, key2 => 4});
699
$select_source = "select * from table1 where {in table1.key1 2} and {in table1.key2 2}";
700
$select_query = $dbi->execute($select_source,{}, query => 1);
701
$select_query->filter({'table1.key1' => 'twice'});
702
$result = $dbi->execute($select_query, param => {'table1.key1' => [1,5], 'table1.key2' => [2,4]});
703
$rows = $result->all;
704
is_deeply($rows, [{key1 => 2, key2 => 4}], "filter");
705

            
cleanup test
Yuki Kimoto authored on 2011-08-08
706
test 'DBIx::Custom::SQLTemplate basic tag';
707
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-08
708
$dbi->execute($create_table1_2);
cleanup test
Yuki Kimoto authored on 2011-08-08
709
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
710
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
711

            
712
$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";
713
$query = $dbi->execute($source, {}, query => 1);
714
$result = $dbi->execute($query, param => {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
715
$rows = $result->all;
716
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");
717

            
718
$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";
719
$query = $dbi->execute($source, {}, query => 1);
720
$result = $dbi->execute($query, {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
721
$rows = $result->all;
722
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");
723

            
724
$source = "select * from table1 where {<= key1} and {like key2};";
725
$query = $dbi->execute($source, {}, query => 1);
726
$result = $dbi->execute($query, param => {key1 => 1, key2 => '%2%'});
727
$rows = $result->all;
728
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag2");
729

            
730
test 'DIB::Custom::SQLTemplate in tag';
731
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-08
732
$dbi->execute($create_table1_2);
cleanup test
Yuki Kimoto authored on 2011-08-08
733
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
734
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
735

            
736
$source = "select * from table1 where {in key1 2};";
737
$query = $dbi->execute($source, {}, query => 1);
738
$result = $dbi->execute($query, param => {key1 => [9, 1]});
739
$rows = $result->all;
740
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic");
741

            
742
test 'DBIx::Custom::SQLTemplate insert tag';
743
$dbi->delete_all(table => 'table1');
744
$insert_source = 'insert into table1 {insert_param key1 key2 key3 key4 key5}';
745
$dbi->execute($insert_source, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
746

            
747
$result = $dbi->execute('select * from table1;');
748
$rows = $result->all;
749
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic");
750

            
751
test 'DBIx::Custom::SQLTemplate update tag';
752
$dbi->delete_all(table => 'table1');
753
$insert_source = "insert into table1 {insert_param key1 key2 key3 key4 key5}";
754
$dbi->execute($insert_source, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
755
$dbi->execute($insert_source, param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
756

            
757
$update_source = 'update table1 {update_param key1 key2 key3 key4} where {= key5}';
758
$dbi->execute($update_source, param => {key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5});
759

            
760
$result = $dbi->execute('select * from table1 order by key1;');
761
$rows = $result->all;
762
is_deeply($rows, [{key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5},
763
                  {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}], "basic");
764

            
cleanup test
Yuki Kimoto authored on 2011-08-08
765
test 'Named placeholder';
766
$dbi->execute('drop table table1');
767
$dbi->execute($create_table1_2);
768
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
769
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
770

            
771
$source = "select * from table1 where key1 = :key1 and key2 = :key2";
772
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
773
$rows = $result->all;
774
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
775

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

            
781
$source = "select * from table1 where key1 = :key1 or key1 = :key1";
782
$result = $dbi->execute($source, param => {key1 => [1, 2]});
783
$rows = $result->all;
784
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
785

            
786
$source = "select * from table1 where key1 = :table1.key1 and key2 = :table1.key2";
787
$result = $dbi->execute(
788
    $source,
789
    param => {'table1.key1' => 1, 'table1.key2' => 1},
790
    filter => {'table1.key2' => sub { $_[0] * 2 }}
791
);
792
$rows = $result->all;
793
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
794

            
795
$dbi->execute('drop table table1');
796
$dbi->execute($create_table1);
797
$dbi->insert(table => 'table1', param => {key1 => '2011-10-14 12:19:18', key2 => 2});
798
$source = "select * from table1 where key1 = '2011-10-14 12:19:18' and key2 = :key2";
799
$result = $dbi->execute(
800
    $source,
801
    param => {'key2' => 2},
802
);
803

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

            
807
$dbi->delete_all(table => 'table1');
808
$dbi->insert(table => 'table1', param => {key1 => 'a:b c:d', key2 => 2});
809
$source = "select * from table1 where key1 = 'a\\:b c\\:d' and key2 = :key2";
810
$result = $dbi->execute(
811
    $source,
812
    param => {'key2' => 2},
813
);
814
$rows = $result->all;
815
is_deeply($rows, [{key1 => 'a:b c:d', key2 => 2}]);
816

            
test cleanup
Yuki Kimoto authored on 2011-08-10
817
test 'Error case';
818
eval {DBIx::Custom->connect(dsn => 'dbi:SQLit')};
819
ok($@, "connect error");
820

            
821
eval{$dbi->execute("{p }", {}, query => 1)};
822
ok($@, "create_query invalid SQL template");
added EXPERIMENTAL DBIx::Cus...
Yuki Kimoto authored on 2011-08-09
823

            
cleanup test
Yuki Kimoto authored on 2011-08-10
824
test 'insert';
cleanup test
Yuki Kimoto authored on 2011-08-10
825
eval { $dbi->execute('drop table table1') };
826
$dbi->execute($create_table1);
cleanup test
Yuki Kimoto authored on 2011-08-10
827
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
828
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
829
$result = $dbi->execute('select * from table1;');
830
$rows   = $result->all;
831
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
832

            
833
$dbi->execute('delete from table1');
834
$dbi->register_filter(
835
    twice       => sub { $_[0] * 2 },
836
    three_times => sub { $_[0] * 3 }
837
);
838
$dbi->default_bind_filter('twice');
839
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => 'three_times'});
840
$result = $dbi->execute('select * from table1;');
841
$rows   = $result->all;
842
is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
843
$dbi->default_bind_filter(undef);
844

            
cleanup test
Yuki Kimoto authored on 2011-08-10
845
$dbi->execute('drop table table1');
846
$dbi->execute($create_table1);
cleanup test
Yuki Kimoto authored on 2011-08-10
847
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, append => '   ');
848
$rows = $dbi->select(table => 'table1')->all;
849
is_deeply($rows, [{key1 => 1, key2 => 2}], 'insert append');
850

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

            
854
eval{$dbi->insert(table => 'table', param => {';' => 1})};
855
like($@, qr/safety/);
856

            
cleanup test
Yuki Kimoto authored on 2011-08-10
857
eval { $dbi->execute("drop table ${q}table$p") };
test cleanup
Yuki Kimoto authored on 2011-08-10
858
$dbi->execute($create_table_reserved);
cleanup test
Yuki Kimoto authored on 2011-08-10
859
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
860
$dbi->insert(table => 'table', param => {select => 1});
cleanup test
Yuki Kimoto authored on 2011-08-10
861
$result = $dbi->execute("select * from ${q}table$p");
cleanup test
Yuki Kimoto authored on 2011-08-10
862
$rows   = $result->all;
test cleanup
Yuki Kimoto authored on 2011-08-10
863
is_deeply($rows, [{select => 2, update => undef}], "reserved word");
cleanup test
Yuki Kimoto authored on 2011-08-10
864

            
cleanup test
Yuki Kimoto authored on 2011-08-10
865
eval { $dbi->execute('drop table table1') };
cleanup test
Yuki Kimoto authored on 2011-08-10
866
$dbi->execute($create_table1);
867
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
868
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
869
$result = $dbi->execute('select * from table1;');
870
$rows   = $result->all;
871
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
872

            
cleanup test
Yuki Kimoto authored on 2011-08-10
873
eval { $dbi->execute('drop table table1') };
cleanup test
Yuki Kimoto authored on 2011-08-10
874
$dbi->execute($create_table1);
875
$dbi->insert(table => 'table1', param => {key1 => \"'1'", key2 => 2});
876
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
877
$result = $dbi->execute('select * from table1;');
878
$rows   = $result->all;
879
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
880

            
test cleanup
Yuki Kimoto authored on 2011-08-10
881
test 'update';
882
eval { $dbi->execute('drop table table1') };
883
$dbi->execute($create_table1_2);
884
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
885
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
886
$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1});
test cleanup
Yuki Kimoto authored on 2011-08-10
887
$result = $dbi->execute('select * from table1 order by key1;');
test cleanup
Yuki Kimoto authored on 2011-08-10
888
$rows   = $result->all;
889
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
890
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
891
                  "basic");
892
                  
893
$dbi->execute("delete from table1");
894
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
895
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
896
$dbi->update(table => 'table1', param => {key2 => 12}, where => {key2 => 2, key3 => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
897
$result = $dbi->execute('select * from table1 order by key1;');
test cleanup
Yuki Kimoto authored on 2011-08-10
898
$rows   = $result->all;
899
is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
900
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
901
                  "update key same as search key");
902

            
903
$dbi->update(table => 'table1', param => {key2 => [12]}, where => {key2 => 2, key3 => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
904
$result = $dbi->execute('select * from table1 order by key1;');
test cleanup
Yuki Kimoto authored on 2011-08-10
905
$rows   = $result->all;
906
is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
907
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
908
                  "update key same as search key : param is array ref");
909

            
910
$dbi->execute("delete from table1");
911
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
912
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
913
$dbi->register_filter(twice => sub { $_[0] * 2 });
914
$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1},
915
              filter => {key2 => sub { $_[0] * 2 }});
test cleanup
Yuki Kimoto authored on 2011-08-10
916
$result = $dbi->execute('select * from table1 order by key1;');
test cleanup
Yuki Kimoto authored on 2011-08-10
917
$rows   = $result->all;
918
is_deeply($rows, [{key1 => 1, key2 => 22, key3 => 3, key4 => 4, key5 => 5},
919
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
920
                  "filter");
921

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

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

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

            
930
eval { $dbi->execute('drop table table1') };
931
$dbi->execute($create_table1);
932
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
933
$where = $dbi->where;
934
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
935
$where->param({key1 => 1, key2 => 2});
936
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
937
$result = $dbi->select(table => 'table1');
938
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
939

            
940
eval { $dbi->execute('drop table table1') };
941
$dbi->execute($create_table1);
942
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
943
$dbi->update(
944
    table => 'table1',
945
    param => {key1 => 3},
946
    where => [
947
        ['and', 'key1 = :key1', 'key2 = :key2'],
948
        {key1 => 1, key2 => 2}
949
    ]
950
);
951
$result = $dbi->select(table => 'table1');
952
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
953

            
954
eval { $dbi->execute('drop table table1') };
955
$dbi->execute($create_table1);
956
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
957
$where = $dbi->where;
958
$where->clause(['and', 'key2 = :key2']);
959
$where->param({key2 => 2});
960
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
961
$result = $dbi->select(table => 'table1');
962
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
963

            
964
eval{$dbi->update(table => 'table1', param => {';' => 1})};
965
like($@, qr/safety/);
966

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

            
970
eval { $dbi->execute('drop table table1') };
971
eval { $dbi->execute("drop table ${q}table$p") };
test cleanup
Yuki Kimoto authored on 2011-08-10
972
$dbi->execute($create_table_reserved);
test cleanup
Yuki Kimoto authored on 2011-08-10
973
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
974
$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
975
$dbi->insert(table => 'table', param => {select => 1});
976
$dbi->update(table => 'table', where => {select => 1}, param => {update => 2});
977
$result = $dbi->execute("select * from ${q}table$p");
978
$rows   = $result->all;
979
is_deeply($rows, [{select => 2, update => 6}], "reserved word");
980

            
981
eval {$dbi->update_all(table => 'table', param => {';' => 2}) };
982
like($@, qr/safety/);
983

            
984
eval { $dbi->execute("drop table ${q}table$p") };
test cleanup
Yuki Kimoto authored on 2011-08-10
985
$dbi->execute($create_table_reserved);
test cleanup
Yuki Kimoto authored on 2011-08-10
986
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
987
$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
988
$dbi->insert(table => 'table', param => {select => 1});
989
$dbi->update(table => 'table', where => {'table.select' => 1}, param => {update => 2});
990
$result = $dbi->execute("select * from ${q}table$p");
991
$rows   = $result->all;
992
is_deeply($rows, [{select => 2, update => 6}], "reserved word");
993

            
994
eval { $dbi->execute('drop table table1') };
995
$dbi->execute($create_table1_2);
996
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
997
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
998
$dbi->update({key2 => 11}, table => 'table1', where => {key1 => 1});
test cleanup
Yuki Kimoto authored on 2011-08-10
999
$result = $dbi->execute('select * from table1 order by key1;');
test cleanup
Yuki Kimoto authored on 2011-08-10
1000
$rows   = $result->all;
1001
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
1002
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
1003
                  "basic");
1004

            
1005
eval { $dbi->execute('drop table table1') };
1006
$dbi->execute($create_table1_2);
1007
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
1008
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
1009
$dbi->update(table => 'table1', param => {key2 => \"'11'"}, where => {key1 => 1});
test cleanup
Yuki Kimoto authored on 2011-08-10
1010
$result = $dbi->execute('select * from table1 order by key1;');
test cleanup
Yuki Kimoto authored on 2011-08-10
1011
$rows   = $result->all;
1012
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
1013
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
1014
                  "basic");
1015

            
1016
test 'update_all';
1017
eval { $dbi->execute('drop table table1') };
1018
$dbi->execute($create_table1_2);
1019
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
1020
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
1021
$dbi->register_filter(twice => sub { $_[0] * 2 });
1022
$dbi->update_all(table => 'table1', param => {key2 => 10}, filter => {key2 => 'twice'});
1023
$result = $dbi->execute('select * from table1;');
1024
$rows   = $result->all;
1025
is_deeply($rows, [{key1 => 1, key2 => 20, key3 => 3, key4 => 4, key5 => 5},
1026
                  {key1 => 6, key2 => 20, key3 => 8, key4 => 9, key5 => 10}],
1027
                  "filter");
1028

            
1029

            
1030
test 'delete';
1031
eval { $dbi->execute('drop table table1') };
1032
$dbi->execute($create_table1);
1033
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1034
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1035
$dbi->delete(table => 'table1', where => {key1 => 1});
1036
$result = $dbi->execute('select * from table1;');
1037
$rows   = $result->all;
1038
is_deeply($rows, [{key1 => 3, key2 => 4}], "basic");
1039

            
1040
$dbi->execute("delete from table1;");
1041
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1042
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1043
$dbi->register_filter(twice => sub { $_[0] * 2 });
1044
$dbi->delete(table => 'table1', where => {key2 => 1}, filter => {key2 => 'twice'});
1045
$result = $dbi->execute('select * from table1;');
1046
$rows   = $result->all;
1047
is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
1048

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

            
1051
$dbi->delete_all(table => 'table1');
1052
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1053
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1054
$dbi->delete(table => 'table1', where => {key1 => 1, key2 => 2});
1055
$rows = $dbi->select(table => 'table1')->all;
1056
is_deeply($rows, [{key1 => 3, key2 => 4}], "delete multi key");
1057

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

            
1061
eval { $dbi->execute('drop table table1') };
1062
$dbi->execute($create_table1);
1063
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1064
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1065
$where = $dbi->where;
1066
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
1067
$where->param({ke1 => 1, key2 => 2});
1068
$dbi->delete(table => 'table1', where => $where);
1069
$result = $dbi->select(table => 'table1');
1070
is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
1071

            
1072
eval { $dbi->execute('drop table table1') };
1073
$dbi->execute($create_table1);
1074
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1075
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1076
$dbi->delete(
1077
    table => 'table1',
1078
    where => [
1079
        ['and', 'key1 = :key1', 'key2 = :key2'],
1080
        {ke1 => 1, key2 => 2}
1081
    ]
1082
);
1083
$result = $dbi->select(table => 'table1');
1084
is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
1085

            
1086
eval { $dbi->execute('drop table table1') };
test cleanup
Yuki Kimoto authored on 2011-08-10
1087
$dbi->execute($create_table1);
test cleanup
Yuki Kimoto authored on 2011-08-10
1088
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1089
$dbi->delete(table => 'table1', where => {key1 => 1}, prefix => '    ');
1090
$result = $dbi->execute('select * from table1;');
1091
$rows   = $result->all;
1092
is_deeply($rows, [], "basic");
1093

            
1094
test 'delete error';
1095
eval { $dbi->execute('drop table table1') };
1096
$dbi->execute($create_table1);
1097
eval{$dbi->delete(table => 'table1')};
1098
like($@, qr/"where" must be specified/,
1099
         "where key-value pairs not specified");
1100

            
1101
eval{$dbi->delete(table => 'table1', where => {';' => 1})};
1102
like($@, qr/safety/);
1103

            
test cleanup
Yuki Kimoto authored on 2011-08-10
1104
$dbi = undef;
test cleanup
Yuki Kimoto authored on 2011-08-10
1105
$dbi = DBIx::Custom->connect;
1106
eval { $dbi->execute("drop table ${q}table$p") };
test cleanup
Yuki Kimoto authored on 2011-08-10
1107
$dbi->execute($create_table_reserved);
test cleanup
Yuki Kimoto authored on 2011-08-10
1108
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
1109
$dbi->insert(table => 'table', param => {select => 1});
1110
$dbi->delete(table => 'table', where => {select => 1});
1111
$result = $dbi->execute("select * from ${q}table$p");
1112
$rows   = $result->all;
1113
is_deeply($rows, [], "reserved word");
1114

            
1115
test 'delete_all';
1116
eval { $dbi->execute('drop table table1') };
1117
$dbi->execute($create_table1);
1118
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1119
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1120
$dbi->delete_all(table => 'table1');
1121
$result = $dbi->execute('select * from table1;');
1122
$rows   = $result->all;
1123
is_deeply($rows, [], "basic");
1124

            
1125

            
1126
test 'select';
1127
eval { $dbi->execute('drop table table1') };
1128
$dbi->execute($create_table1);
1129
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1130
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1131
$rows = $dbi->select(table => 'table1')->all;
1132
is_deeply($rows, [{key1 => 1, key2 => 2},
1133
                  {key1 => 3, key2 => 4}], "table");
1134

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

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

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

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

            
1147
$dbi->register_filter(decrement => sub { $_[0] - 1 });
1148
$rows = $dbi->select(table => 'table1', where => {key1 => 2}, filter => {key1 => 'decrement'})
1149
            ->all;
1150
is_deeply($rows, [{key1 => 1, key2 => 2}], "filter");
1151

            
test cleanup
Yuki Kimoto authored on 2011-08-10
1152
eval { $dbi->execute("drop table table2") };
1153
$dbi->execute($create_table2);
test cleanup
Yuki Kimoto authored on 2011-08-10
1154
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
1155
$rows = $dbi->select(
1156
    table => [qw/table1 table2/],
1157
    column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
1158
    where   => {'table1.key2' => 2},
1159
    relation  => {'table1.key1' => 'table2.key1'}
1160
)->all;
1161
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : exists where");
1162

            
1163
$rows = $dbi->select(
1164
    table => [qw/table1 table2/],
1165
    column => ['table1.key1 as table1_key1', 'table2.key1 as table2_key1', 'key2', 'key3'],
1166
    relation  => {'table1.key1' => 'table2.key1'}
1167
)->all;
1168
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : no exists where");
1169

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

            
1173
$dbi = DBIx::Custom->connect;
test cleanup
Yuki Kimoto authored on 2011-08-10
1174
eval { $dbi->execute("drop table ${q}table$p") };
1175
$dbi->execute($create_table_reserved);
test cleanup
Yuki Kimoto authored on 2011-08-10
1176
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
1177
$dbi->insert(table => 'table', param => {select => 1, update => 2});
1178
$result = $dbi->select(table => 'table', where => {select => 1});
1179
$rows   = $result->all;
1180
is_deeply($rows, [{select => 2, update => 2}], "reserved word");
1181

            
1182
test 'fetch filter';
1183
eval { $dbi->execute('drop table table1') };
1184
$dbi->register_filter(
1185
    twice       => sub { $_[0] * 2 },
1186
    three_times => sub { $_[0] * 3 }
1187
);
1188
$dbi->default_fetch_filter('twice');
1189
$dbi->execute($create_table1);
1190
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1191
$result = $dbi->select(table => 'table1');
1192
$result->filter({key1 => 'three_times'});
1193
$row = $result->one;
1194
is_deeply($row, {key1 => 3, key2 => 4}, "default_fetch_filter and filter");
1195

            
1196
test 'filters';
1197
$dbi = DBIx::Custom->new;
1198

            
1199
is($dbi->filters->{decode_utf8}->(encode_utf8('あ')),
1200
   'あ', "decode_utf8");
1201

            
1202
is($dbi->filters->{encode_utf8}->('あ'),
1203
   encode_utf8('あ'), "encode_utf8");
1204

            
cleanup test
Yuki Kimoto authored on 2011-08-10
1205
test 'transaction1';
test cleanup
Yuki Kimoto authored on 2011-08-10
1206
$dbi = DBIx::Custom->connect;
1207
eval { $dbi->execute('drop table table1') };
1208
$dbi->execute($create_table1);
1209
$dbi->dbh->begin_work;
1210
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1211
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
1212
$dbi->dbh->commit;
1213
$result = $dbi->select(table => 'table1');
1214
is_deeply(scalar $result->all, [{key1 => 1, key2 => 2}, {key1 => 2, key2 => 3}],
1215
          "commit");
1216

            
1217
$dbi = DBIx::Custom->connect;
1218
eval { $dbi->execute('drop table table1') };
1219
$dbi->execute($create_table1);
1220
$dbi->dbh->begin_work(0);
1221
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1222
$dbi->dbh->rollback;
1223

            
1224
$result = $dbi->select(table => 'table1');
1225
ok(! $result->fetch_first, "rollback");
1226

            
1227
test 'execute';
1228
eval { $dbi->execute('drop table table1') };
1229
$dbi->execute($create_table1);
1230
{
1231
    local $Carp::Verbose = 0;
1232
    eval{$dbi->execute('select * frm table1')};
1233
    like($@, qr/\Qselect * frm table1;/, "fail prepare");
1234
    like($@, qr/\.t /, "fail : not verbose");
1235
}
1236
{
1237
    local $Carp::Verbose = 1;
1238
    eval{$dbi->execute('select * frm table1')};
1239
    like($@, qr/Custom.*\.t /s, "fail : verbose");
1240
}
1241

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

            
1245
$query = $dbi->execute('select * from table1 where key1 = :key1', {}, query => 1);
1246
$dbi->dbh->disconnect;
1247
eval{$dbi->execute($query, param => {key1 => {a => 1}})};
1248
ok($@, "execute fail");
1249

            
1250
{
1251
    local $Carp::Verbose = 0;
1252
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
1253
    like($@, qr/\Q.t /, "caller spec : not vebose");
1254
}
1255
{
1256
    local $Carp::Verbose = 1;
1257
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
1258
    like($@, qr/QueryBuilder.*\.t /s, "caller spec : not vebose");
1259
}
1260

            
1261

            
cleanup test
Yuki Kimoto authored on 2011-08-10
1262
test 'transaction2';
test cleanup
Yuki Kimoto authored on 2011-08-10
1263
$dbi = DBIx::Custom->connect;
1264
eval { $dbi->execute('drop table table1') };
1265
$dbi->execute($create_table1);
1266

            
1267
$dbi->begin_work;
1268

            
1269
eval {
1270
    $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1271
    die "Error";
1272
    $dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1273
};
1274

            
1275
$dbi->rollback if $@;
1276

            
1277
$result = $dbi->select(table => 'table1');
1278
$rows = $result->all;
1279
is_deeply($rows, [], "rollback");
1280

            
1281
$dbi->begin_work;
1282

            
1283
eval {
1284
    $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1285
    $dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1286
};
1287

            
1288
$dbi->commit unless $@;
1289

            
1290
$result = $dbi->select(table => 'table1');
1291
$rows = $result->all;
1292
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "commit");
1293

            
1294
$dbi->dbh->{AutoCommit} = 0;
1295
eval{ $dbi->begin_work };
1296
ok($@, "exception");
1297
$dbi->dbh->{AutoCommit} = 1;
added EXPERIMENTAL DBIx::Cus...
Yuki Kimoto authored on 2011-08-09
1298

            
test cleanup
Yuki Kimoto authored on 2011-08-10
1299
test 'cache';
1300
eval { $dbi->execute('drop table table1') };
1301
$dbi->cache(1);
1302
$dbi->execute($create_table1);
1303
$source = 'select * from table1 where key1 = :key1 and key2 = :key2;';
1304
$dbi->execute($source, {}, query => 1);
1305
is_deeply($dbi->{_cached}->{$source}, 
1306
          {sql => "select * from table1 where key1 = ? and key2 = ?;", columns => ['key1', 'key2'], tables => []}, "cache");
1307

            
1308
eval { $dbi->execute('drop table table1') };
1309
$dbi->execute($create_table1);
1310
$dbi->{_cached} = {};
1311
$dbi->cache(0);
1312
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1313
is(scalar keys %{$dbi->{_cached}}, 0, 'not cache');
1314

            
1315
test 'execute';
1316
eval { $dbi->execute('drop table table1') };
1317
$dbi->execute($create_table1);
1318
{
1319
    local $Carp::Verbose = 0;
1320
    eval{$dbi->execute('select * frm table1')};
1321
    like($@, qr/\Qselect * frm table1;/, "fail prepare");
1322
    like($@, qr/\.t /, "fail : not verbose");
1323
}
1324
{
1325
    local $Carp::Verbose = 1;
1326
    eval{$dbi->execute('select * frm table1')};
1327
    like($@, qr/Custom.*\.t /s, "fail : verbose");
1328
}
1329

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

            
1333
$query = $dbi->execute('select * from table1 where key1 = :key1', {}, query => 1);
1334
$dbi->dbh->disconnect;
1335
eval{$dbi->execute($query, param => {key1 => {a => 1}})};
1336
ok($@, "execute fail");
1337

            
1338
{
1339
    local $Carp::Verbose = 0;
1340
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
1341
    like($@, qr/\Q.t /, "caller spec : not vebose");
1342
}
1343
{
1344
    local $Carp::Verbose = 1;
1345
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
1346
    like($@, qr/QueryBuilder.*\.t /s, "caller spec : not vebose");
1347
}
1348

            
1349
test 'method';
1350
$dbi->method(
1351
    one => sub { 1 }
1352
);
1353
$dbi->method(
1354
    two => sub { 2 }
1355
);
1356
$dbi->method({
1357
    twice => sub {
1358
        my $self = shift;
1359
        return $_[0] * 2;
1360
    }
1361
});
1362

            
1363
is($dbi->one, 1, "first");
1364
is($dbi->two, 2, "second");
1365
is($dbi->twice(5), 10 , "second");
1366

            
1367
eval {$dbi->XXXXXX};
1368
ok($@, "not exists");
1369

            
1370
test 'out filter';
1371
$dbi = DBIx::Custom->connect;
1372
eval { $dbi->execute('drop table table1') };
1373
$dbi->execute($create_table1);
1374
$dbi->register_filter(twice => sub { $_[0] * 2 });
1375
$dbi->register_filter(three_times => sub { $_[0] * 3});
1376
$dbi->apply_filter(
1377
    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 
1378
              'key2' => {out => 'three_times', in => 'twice'});
1379
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1380
$result = $dbi->execute('select * from table1;');
1381
$row   = $result->fetch_hash_first;
1382
is_deeply($row, {key1 => 2, key2 => 6}, "insert");
1383
$result = $dbi->select(table => 'table1');
1384
$row   = $result->one;
1385
is_deeply($row, {key1 => 6, key2 => 12}, "insert");
1386

            
1387
$dbi = DBIx::Custom->connect;
1388
eval { $dbi->execute('drop table table1') };
1389
$dbi->execute($create_table1);
1390
$dbi->register_filter(twice => sub { $_[0] * 2 });
1391
$dbi->register_filter(three_times => sub { $_[0] * 3});
1392
$dbi->apply_filter(
1393
    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 
1394
              'key2' => {out => 'three_times', in => 'twice'});
1395
$dbi->apply_filter(
1396
    'table1', 'key1' => {out => undef}
1397
); 
1398
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1399
$result = $dbi->execute('select * from table1;');
1400
$row   = $result->one;
1401
is_deeply($row, {key1 => 1, key2 => 6}, "insert");
1402

            
1403
$dbi = DBIx::Custom->connect;
1404
eval { $dbi->execute('drop table table1') };
1405
$dbi->execute($create_table1);
1406
$dbi->register_filter(twice => sub { $_[0] * 2 });
1407
$dbi->apply_filter(
1408
    'table1', 'key1' => {out => 'twice', in => 'twice'}
1409
);
1410
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => undef});
1411
$dbi->update(table => 'table1', param => {key1 => 2}, where => {key2 => 2});
1412
$result = $dbi->execute('select * from table1;');
1413
$row   = $result->one;
1414
is_deeply($row, {key1 => 4, key2 => 2}, "update");
1415

            
1416
$dbi = DBIx::Custom->connect;
1417
eval { $dbi->execute('drop table table1') };
1418
$dbi->execute($create_table1);
1419
$dbi->register_filter(twice => sub { $_[0] * 2 });
1420
$dbi->apply_filter(
1421
    'table1', 'key1' => {out => 'twice', in => 'twice'}
1422
);
1423
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1=> undef});
1424
$dbi->delete(table => 'table1', where => {key1 => 1});
1425
$result = $dbi->execute('select * from table1;');
1426
$rows   = $result->all;
1427
is_deeply($rows, [], "delete");
1428

            
1429
$dbi = DBIx::Custom->connect;
1430
eval { $dbi->execute('drop table table1') };
1431
$dbi->execute($create_table1);
1432
$dbi->register_filter(twice => sub { $_[0] * 2 });
1433
$dbi->apply_filter(
1434
    'table1', 'key1' => {out => 'twice', in => 'twice'}
1435
);
1436
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
1437
$result = $dbi->select(table => 'table1', where => {key1 => 1});
1438
$result->filter({'key2' => 'twice'});
1439
$rows   = $result->all;
1440
is_deeply($rows, [{key1 => 4, key2 => 4}], "select");
1441

            
1442
$dbi = DBIx::Custom->connect;
1443
eval { $dbi->execute('drop table table1') };
1444
$dbi->execute($create_table1);
1445
$dbi->register_filter(twice => sub { $_[0] * 2 });
1446
$dbi->apply_filter(
1447
    'table1', 'key1' => {out => 'twice', in => 'twice'}
1448
);
1449
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
1450
$result = $dbi->execute("select * from table1 where key1 = :key1 and key2 = :key2;",
1451
                        param => {key1 => 1, key2 => 2},
1452
                        table => ['table1']);
1453
$rows   = $result->all;
1454
is_deeply($rows, [{key1 => 4, key2 => 2}], "execute");
1455

            
1456
$dbi = DBIx::Custom->connect;
1457
eval { $dbi->execute('drop table table1') };
1458
$dbi->execute($create_table1);
1459
$dbi->register_filter(twice => sub { $_[0] * 2 });
1460
$dbi->apply_filter(
1461
    'table1', 'key1' => {out => 'twice', in => 'twice'}
1462
);
1463
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
1464
$result = $dbi->execute("select * from {table table1} where key1 = :key1 and key2 = :key2;",
1465
                        param => {key1 => 1, key2 => 2});
1466
$rows   = $result->all;
1467
is_deeply($rows, [{key1 => 4, key2 => 2}], "execute table tag");
1468

            
1469
$dbi = DBIx::Custom->connect;
1470
eval { $dbi->execute('drop table table1') };
1471
eval { $dbi->execute('drop table table2') };
1472
$dbi->execute($create_table1);
1473
$dbi->execute($create_table2);
1474
$dbi->register_filter(twice => sub { $_[0] * 2 });
1475
$dbi->register_filter(three_times => sub { $_[0] * 3 });
1476
$dbi->apply_filter(
1477
    'table1', 'key2' => {out => 'twice', in => 'twice'}
1478
);
1479
$dbi->apply_filter(
1480
    'table2', 'key3' => {out => 'three_times', in => 'three_times'}
1481
);
1482
$dbi->insert(table => 'table1', param => {key1 => 5, key2 => 2}, filter => {key2 => undef});
1483
$dbi->insert(table => 'table2', param => {key1 => 5, key3 => 6}, filter => {key3 => undef});
1484
$result = $dbi->select(
1485
     table => ['table1', 'table2'],
1486
     column => ['key2', 'key3'],
1487
     where => {'table1.key2' => 1, 'table2.key3' => 2}, relation => {'table1.key1' => 'table2.key1'});
1488

            
1489
$result->filter({'key2' => 'twice'});
1490
$rows   = $result->all;
1491
is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join");
1492

            
1493
$result = $dbi->select(
1494
     table => ['table1', 'table2'],
1495
     column => ['key2', 'key3'],
1496
     where => {'key2' => 1, 'key3' => 2}, relation => {'table1.key1' => 'table2.key1'});
1497

            
1498
$result->filter({'key2' => 'twice'});
1499
$rows   = $result->all;
1500
is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join : omit");
1501

            
1502
test 'each_column';
1503
$dbi = DBIx::Custom->connect;
1504
eval { $dbi->execute("drop table ${q}table$p") };
1505
eval { $dbi->execute('drop table table1') };
1506
eval { $dbi->execute('drop table table2') };
test cleranup
Yuki Kimoto authored on 2011-08-10
1507
eval { $dbi->execute('drop table table3') };
test cleanup
Yuki Kimoto authored on 2011-08-10
1508
$dbi->execute($create_table1_type);
1509
$dbi->execute($create_table2);
1510

            
1511
$infos = [];
1512
$dbi->each_column(sub {
1513
    my ($self, $table, $column, $cinfo) = @_;
1514
    
1515
    if ($table =~ /^table\d/) {
1516
         my $info = [$table, $column, $cinfo->{COLUMN_NAME}];
1517
         push @$infos, $info;
1518
    }
1519
});
1520
$infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
1521
is_deeply($infos, 
1522
    [
1523
        ['table1', 'key1', 'key1'],
1524
        ['table1', 'key2', 'key2'],
1525
        ['table2', 'key1', 'key1'],
1526
        ['table2', 'key3', 'key3']
1527
    ]
1528
    
1529
);
1530
test 'each_table';
1531
$dbi = DBIx::Custom->connect;
1532
eval { $dbi->execute('drop table table1') };
1533
eval { $dbi->execute('drop table table2') };
1534
$dbi->execute($create_table2);
1535
$dbi->execute($create_table1_type);
1536

            
1537
$infos = [];
1538
$dbi->each_table(sub {
1539
    my ($self, $table, $table_info) = @_;
1540
    
1541
    if ($table =~ /^table\d/) {
1542
         my $info = [$table, $table_info->{TABLE_NAME}];
1543
         push @$infos, $info;
1544
    }
1545
});
1546
$infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
1547
is_deeply($infos, 
1548
    [
1549
        ['table1', 'table1'],
1550
        ['table2', 'table2'],
1551
    ]
1552
);
1553

            
1554
test 'limit';
1555
$dbi = DBIx::Custom->connect;
1556
eval { $dbi->execute('drop table table1') };
1557
$dbi->execute($create_table1);
1558
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1559
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 4});
1560
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 6});
1561
$dbi->register_tag(
1562
    limit => sub {
1563
        my ($count, $offset) = @_;
1564
        
1565
        my $s = '';
1566
        $s .= "limit $count";
1567
        $s .= " offset $offset" if defined $offset;
1568
        
1569
        return [$s, []];
1570
    }
1571
);
1572
$rows = $dbi->select(
1573
  table => 'table1',
1574
  where => {key1 => 1},
1575
  append => "order by key2 {limit 1 0}"
1576
)->all;
1577
is_deeply($rows, [{key1 => 1, key2 => 2}]);
1578
$rows = $dbi->select(
1579
  table => 'table1',
1580
  where => {key1 => 1},
1581
  append => "order by key2 {limit 2 1}"
1582
)->all;
1583
is_deeply($rows, [{key1 => 1, key2 => 4},{key1 => 1, key2 => 6}]);
1584
$rows = $dbi->select(
1585
  table => 'table1',
1586
  where => {key1 => 1},
1587
  append => "order by key2 {limit 1}"
1588
)->all;
1589
is_deeply($rows, [{key1 => 1, key2 => 2}]);
1590

            
1591
test 'connect super';
test cleanup
Yuki Kimoto authored on 2011-08-10
1592
$dbi = DBIx::Custom->connect;
test cleanup
Yuki Kimoto authored on 2011-08-10
1593
eval { $dbi->execute('drop table table1') };
1594
$dbi->execute($create_table1);
1595
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1596
is($dbi->select(table => 'table1')->one->{key1}, 1);
1597

            
test cleanup
Yuki Kimoto authored on 2011-08-10
1598
$dbi = DBIx::Custom->new;
test cleanup
Yuki Kimoto authored on 2011-08-10
1599
$dbi->connect;
1600
eval { $dbi->execute('drop table table1') };
1601
$dbi->execute($create_table1);
1602
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1603
is($dbi->select(table => 'table1')->one->{key1}, 1);
1604

            
test cleanup
Yuki Kimoto authored on 2011-08-10
1605
$dbi = DBIx::Custom->connect;
test cleanup
Yuki Kimoto authored on 2011-08-10
1606
eval { $dbi->execute('drop table table1') };
1607
$dbi->execute($create_table1);
1608
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1609
is($dbi->select(table => 'table1')->one->{key1}, 1);
1610

            
1611
test 'end_filter';
1612
$dbi = DBIx::Custom->connect;
1613
eval { $dbi->execute('drop table table1') };
1614
$dbi->execute($create_table1);
1615
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1616
$result = $dbi->select(table => 'table1');
1617
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1618
$result->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 });
1619
$row = $result->fetch_first;
1620
is_deeply($row, [6, 40]);
1621

            
1622
$dbi = DBIx::Custom->connect;
1623
eval { $dbi->execute('drop table table1') };
1624
$dbi->execute($create_table1);
1625
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1626
$result = $dbi->select(table => 'table1');
1627
$result->filter([qw/key1 key2/] => sub { $_[0] * 2 });
1628
$result->end_filter([[qw/key1 key2/] => sub { $_[0] * 3 }]);
1629
$row = $result->fetch_first;
1630
is_deeply($row, [6, 12]);
1631

            
1632
$dbi = DBIx::Custom->connect;
1633
eval { $dbi->execute('drop table table1') };
1634
$dbi->execute($create_table1);
1635
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1636
$result = $dbi->select(table => 'table1');
1637
$result->filter([[qw/key1 key2/] => sub { $_[0] * 2 }]);
1638
$result->end_filter([qw/key1 key2/] => sub { $_[0] * 3 });
1639
$row = $result->fetch_first;
1640
is_deeply($row, [6, 12]);
1641

            
1642
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1643
$result = $dbi->select(table => 'table1');
1644
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1645
$result->end_filter({key1 => sub { $_[0] * 3 }, key2 => 'five_times' });
1646
$row = $result->one;
1647
is_deeply($row, {key1 => 6, key2 => 40});
1648

            
1649
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1650
$dbi->apply_filter('table1',
1651
    key1 => {end => sub { $_[0] * 3 } },
1652
    key2 => {end => 'five_times'}
1653
);
1654
$result = $dbi->select(table => 'table1');
1655
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1656
$row = $result->one;
1657
is_deeply($row, {key1 => 6, key2 => 40}, 'apply_filter');
1658

            
1659
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1660
$dbi->apply_filter('table1',
1661
    key1 => {end => sub { $_[0] * 3 } },
1662
    key2 => {end => 'five_times'}
1663
);
1664
$result = $dbi->select(table => 'table1');
1665
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1666
$result->filter(key1 => undef);
1667
$result->end_filter(key1 => undef);
1668
$row = $result->one;
1669
is_deeply($row, {key1 => 1, key2 => 40}, 'apply_filter overwrite');
1670

            
test cleanup
Yuki Kimoto authored on 2011-08-10
1671
test 'remove_end_filter and remove_filter';
1672
$dbi = DBIx::Custom->connect;
1673
eval { $dbi->execute('drop table table1') };
1674
$dbi->execute($create_table1);
1675
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1676
$result = $dbi->select(table => 'table1');
1677
$row = $result
1678
       ->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 })
1679
       ->remove_filter
1680
       ->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 })
1681
       ->remove_end_filter
1682
       ->fetch_first;
1683
is_deeply($row, [1, 2]);
1684

            
1685
test 'empty where select';
1686
$dbi = DBIx::Custom->connect;
1687
eval { $dbi->execute('drop table table1') };
1688
$dbi->execute($create_table1);
1689
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1690
$result = $dbi->select(table => 'table1', where => {});
1691
$row = $result->one;
1692
is_deeply($row, {key1 => 1, key2 => 2});
1693

            
1694
test 'select query option';
1695
$dbi = DBIx::Custom->connect;
1696
eval { $dbi->execute('drop table table1') };
1697
$dbi->execute($create_table1);
1698
$query = $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, query => 1);
1699
is(ref $query, 'DBIx::Custom::Query');
1700
$query = $dbi->update(table => 'table1', where => {key1 => 1}, param => {key2 => 2}, query => 1);
1701
is(ref $query, 'DBIx::Custom::Query');
1702
$query = $dbi->delete(table => 'table1', where => {key1 => 1}, query => 1);
1703
is(ref $query, 'DBIx::Custom::Query');
1704
$query = $dbi->select(table => 'table1', where => {key1 => 1, key2 => 2}, query => 1);
1705
is(ref $query, 'DBIx::Custom::Query');
1706

            
1707
test 'where';
1708
$dbi = DBIx::Custom->connect;
1709
eval { $dbi->execute('drop table table1') };
1710
$dbi->execute($create_table1);
1711
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1712
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1713
$where = $dbi->where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
1714
is("$where", "where ( key1 = :key1 and key2 = :key2 )", 'no param');
1715

            
1716
$where = $dbi->where
1717
             ->clause(['and', 'key1 = :key1', 'key2 = :key2'])
1718
             ->param({key1 => 1});
1719

            
1720
$result = $dbi->select(
1721
    table => 'table1',
1722
    where => $where
1723
);
1724
$row = $result->all;
1725
is_deeply($row, [{key1 => 1, key2 => 2}]);
1726

            
1727
$result = $dbi->select(
1728
    table => 'table1',
1729
    where => [
1730
        ['and', 'key1 = :key1', 'key2 = :key2'],
1731
        {key1 => 1}
1732
    ]
1733
);
1734
$row = $result->all;
1735
is_deeply($row, [{key1 => 1, key2 => 2}]);
1736

            
1737
$where = $dbi->where
1738
             ->clause(['and', 'key1 = :key1', 'key2 = :key2'])
1739
             ->param({key1 => 1, key2 => 2});
1740
$result = $dbi->select(
1741
    table => 'table1',
1742
    where => $where
1743
);
1744
$row = $result->all;
1745
is_deeply($row, [{key1 => 1, key2 => 2}]);
1746

            
1747
$where = $dbi->where
1748
             ->clause(['and', 'key1 = :key1', 'key2 = :key2'])
1749
             ->param({});
1750
$result = $dbi->select(
1751
    table => 'table1',
1752
    where => $where,
1753
);
1754
$row = $result->all;
1755
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1756

            
1757
$where = $dbi->where
1758
             ->clause(['and', ['or', 'key1 > :key1', 'key1 < :key1'], 'key2 = :key2'])
1759
             ->param({key1 => [0, 3], key2 => 2});
1760
$result = $dbi->select(
1761
    table => 'table1',
1762
    where => $where,
1763
); 
1764
$row = $result->all;
1765
is_deeply($row, [{key1 => 1, key2 => 2}]);
1766

            
1767
$where = $dbi->where;
1768
$result = $dbi->select(
1769
    table => 'table1',
1770
    where => $where
1771
);
1772
$row = $result->all;
1773
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1774

            
1775
eval {
1776
$where = $dbi->where
1777
             ->clause(['uuu']);
1778
$result = $dbi->select(
1779
    table => 'table1',
1780
    where => $where
1781
);
1782
};
1783
ok($@);
1784

            
1785
$where = $dbi->where;
1786
is("$where", '');
1787

            
1788
$where = $dbi->where
1789
             ->clause(['or', ('key1 = :key1') x 2])
1790
             ->param({key1 => [1, 3]});
1791
$result = $dbi->select(
1792
    table => 'table1',
1793
    where => $where,
1794
);
1795
$row = $result->all;
1796
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1797

            
1798
$where = $dbi->where
1799
             ->clause(['or', ('key1 = :key1') x 2])
1800
             ->param({key1 => [1]});
1801
$result = $dbi->select(
1802
    table => 'table1',
1803
    where => $where,
1804
);
1805
$row = $result->all;
1806
is_deeply($row, [{key1 => 1, key2 => 2}]);
1807

            
1808
$where = $dbi->where
1809
             ->clause(['or', ('key1 = :key1') x 2])
1810
             ->param({key1 => 1});
1811
$result = $dbi->select(
1812
    table => 'table1',
1813
    where => $where,
1814
);
1815
$row = $result->all;
1816
is_deeply($row, [{key1 => 1, key2 => 2}]);
1817

            
1818
$where = $dbi->where
1819
             ->clause('key1 = :key1')
1820
             ->param({key1 => 1});
1821
$result = $dbi->select(
1822
    table => 'table1',
1823
    where => $where,
1824
);
1825
$row = $result->all;
1826
is_deeply($row, [{key1 => 1, key2 => 2}]);
1827

            
1828
$where = $dbi->where
1829
             ->clause('key1 = :key1 key2 = :key2')
1830
             ->param({key1 => 1});
1831
eval{$where->to_string};
1832
like($@, qr/one column/);
1833

            
1834
$where = $dbi->where
1835
             ->clause(['or', ('key1 = :key1') x 3])
1836
             ->param({key1 => [$dbi->not_exists, 1, 3]});
1837
$result = $dbi->select(
1838
    table => 'table1',
1839
    where => $where,
1840
);
1841
$row = $result->all;
1842
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1843

            
1844
$where = $dbi->where
1845
             ->clause(['or', ('key1 = :key1') x 3])
1846
             ->param({key1 => [1, $dbi->not_exists, 3]});
1847
$result = $dbi->select(
1848
    table => 'table1',
1849
    where => $where,
1850
);
1851
$row = $result->all;
1852
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1853

            
1854
$where = $dbi->where
1855
             ->clause(['or', ('key1 = :key1') x 3])
1856
             ->param({key1 => [1, 3, $dbi->not_exists]});
1857
$result = $dbi->select(
1858
    table => 'table1',
1859
    where => $where,
1860
);
1861
$row = $result->all;
1862
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1863

            
1864
$where = $dbi->where
1865
             ->clause(['or', ('key1 = :key1') x 3])
1866
             ->param({key1 => [1, $dbi->not_exists, $dbi->not_exists]});
1867
$result = $dbi->select(
1868
    table => 'table1',
1869
    where => $where,
1870
);
1871
$row = $result->all;
1872
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1873

            
1874
$where = $dbi->where
1875
             ->clause(['or', ('key1 = :key1') x 3])
1876
             ->param({key1 => [$dbi->not_exists, 1, $dbi->not_exists]});
1877
$result = $dbi->select(
1878
    table => 'table1',
1879
    where => $where,
1880
);
1881
$row = $result->all;
1882
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1883

            
1884
$where = $dbi->where
1885
             ->clause(['or', ('key1 = :key1') x 3])
1886
             ->param({key1 => [$dbi->not_exists, $dbi->not_exists, 1]});
1887
$result = $dbi->select(
1888
    table => 'table1',
1889
    where => $where,
1890
);
1891
$row = $result->all;
1892
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1893

            
1894
$where = $dbi->where
1895
             ->clause(['or', ('key1 = :key1') x 3])
1896
             ->param({key1 => [$dbi->not_exists, $dbi->not_exists, $dbi->not_exists]});
1897
$result = $dbi->select(
1898
    table => 'table1',
1899
    where => $where,
1900
);
1901
$row = $result->all;
1902
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1903

            
1904
$where = $dbi->where
1905
             ->clause(['or', ('key1 = :key1') x 3])
1906
             ->param({key1 => []});
1907
$result = $dbi->select(
1908
    table => 'table1',
1909
    where => $where,
1910
);
1911
$row = $result->all;
1912
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1913

            
1914
$where = $dbi->where
1915
             ->clause(['and', '{> key1}', '{< key1}' ])
1916
             ->param({key1 => [2, $dbi->not_exists]});
1917
$result = $dbi->select(
1918
    table => 'table1',
1919
    where => $where,
1920
);
1921
$row = $result->all;
1922
is_deeply($row, [{key1 => 3, key2 => 4}], 'not_exists');
1923

            
1924
$where = $dbi->where
1925
             ->clause(['and', '{> key1}', '{< key1}' ])
1926
             ->param({key1 => [$dbi->not_exists, 2]});
1927
$result = $dbi->select(
1928
    table => 'table1',
1929
    where => $where,
1930
);
1931
$row = $result->all;
1932
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1933

            
1934
$where = $dbi->where
1935
             ->clause(['and', '{> key1}', '{< key1}' ])
1936
             ->param({key1 => [$dbi->not_exists, $dbi->not_exists]});
1937
$result = $dbi->select(
1938
    table => 'table1',
1939
    where => $where,
1940
);
1941
$row = $result->all;
1942
is_deeply($row, [{key1 => 1, key2 => 2},{key1 => 3, key2 => 4}], 'not_exists');
1943

            
1944
$where = $dbi->where
1945
             ->clause(['and', '{> key1}', '{< key1}' ])
1946
             ->param({key1 => [0, 2]});
1947
$result = $dbi->select(
1948
    table => 'table1',
1949
    where => $where,
1950
);
1951
$row = $result->all;
1952
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1953

            
1954
$where = $dbi->where
1955
             ->clause(['and', 'key1 is not null', 'key2 is not null' ]);
1956
$result = $dbi->select(
1957
    table => 'table1',
1958
    where => $where,
1959
);
1960
$row = $result->all;
1961
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1962

            
1963
eval {$dbi->where(ppp => 1) };
1964
like($@, qr/invalid/);
1965

            
1966
$where = $dbi->where(
1967
    clause => ['and', ['or'], ['and', 'key1 = :key1', 'key2 = :key2']],
1968
    param => {key1 => 1, key2 => 2}
1969
);
1970
$result = $dbi->select(
1971
    table => 'table1',
1972
    where => $where,
1973
);
1974
$row = $result->all;
1975
is_deeply($row, [{key1 => 1, key2 => 2}]);
1976

            
1977

            
1978
$where = $dbi->where(
1979
    clause => ['and', ['or'], ['or', ':key1', ':key2']],
1980
    param => {}
1981
);
1982
$result = $dbi->select(
1983
    table => 'table1',
1984
    where => $where,
1985
);
1986
$row = $result->all;
1987
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1988

            
1989
$where = $dbi->where;
1990
$where->clause(['and', ':key1{=}']);
1991
$where->param({key1 => undef});
1992
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
1993
$row = $result->all;
1994
is_deeply($row, [{key1 => 1, key2 => 2}]);
1995

            
1996
$where = $dbi->where;
1997
$where->clause(['and', ':key1{=}']);
1998
$where->param({key1 => undef});
1999
$where->if('defined');
2000
$where->map;
2001
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
2002
$row = $result->all;
2003
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
2004

            
2005
$where = $dbi->where;
2006
$where->clause(['or', ':key1{=}', ':key1{=}']);
2007
$where->param({key1 => [undef, undef]});
2008
$result = $dbi->execute("select * from table1 $where", {key1 => [1, 0]});
2009
$row = $result->all;
2010
is_deeply($row, [{key1 => 1, key2 => 2}]);
2011
$result = $dbi->execute("select * from table1 $where", {key1 => [0, 1]});
2012
$row = $result->all;
2013
is_deeply($row, [{key1 => 1, key2 => 2}]);
2014

            
2015
$where = $dbi->where;
2016
$where->clause(['and', ':key1{=}']);
2017
$where->param({key1 => [undef, undef]});
2018
$where->if('defined');
2019
$where->map;
2020
$result = $dbi->execute("select * from table1 $where", {key1 => [1, 0]});
2021
$row = $result->all;
2022
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
2023
$result = $dbi->execute("select * from table1 $where", {key1 => [0, 1]});
2024
$row = $result->all;
2025
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
2026

            
2027
$where = $dbi->where;
2028
$where->clause(['and', ':key1{=}']);
2029
$where->param({key1 => 0});
2030
$where->if('length');
2031
$where->map;
2032
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
2033
$row = $result->all;
2034
is_deeply($row, [{key1 => 1, key2 => 2}]);
2035

            
2036
$where = $dbi->where;
2037
$where->clause(['and', ':key1{=}']);
2038
$where->param({key1 => ''});
2039
$where->if('length');
2040
$where->map;
2041
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
2042
$row = $result->all;
2043
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
2044

            
2045
$where = $dbi->where;
2046
$where->clause(['and', ':key1{=}']);
2047
$where->param({key1 => 5});
2048
$where->if(sub { ($_[0] || '') eq 5 });
2049
$where->map;
2050
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
2051
$row = $result->all;
2052
is_deeply($row, [{key1 => 1, key2 => 2}]);
2053

            
2054
$where = $dbi->where;
2055
$where->clause(['and', ':key1{=}']);
2056
$where->param({key1 => 7});
2057
$where->if(sub { ($_[0] || '') eq 5 });
2058
$where->map;
2059
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
2060
$row = $result->all;
2061
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
2062

            
2063
$where = $dbi->where;
2064
$where->param({id => 1, author => 'Ken', price => 1900});
test cleanup
Yuki Kimoto authored on 2011-08-10
2065
$where->map(id => 'table1.id',
2066
    author => ['table1.author', sub { '%' . $_[0] . '%' }],
2067
    price => ['table1.price', {if => sub { $_[0] eq 1900 }}]
test cleanup
Yuki Kimoto authored on 2011-08-10
2068
);
test cleanup
Yuki Kimoto authored on 2011-08-10
2069
is_deeply($where->param, {'table1.id' => 1, 'table1.author' => '%Ken%',
2070
  'table1.price' => 1900});
test cleanup
Yuki Kimoto authored on 2011-08-10
2071

            
2072
$where = $dbi->where;
2073
$where->param({id => 0, author => 0, price => 0});
2074
$where->map(
test cleanup
Yuki Kimoto authored on 2011-08-10
2075
    id => 'table1.id',
2076
    author => ['table1.author', sub { '%' . $_[0] . '%' }],
2077
    price => ['table1.price', sub { '%' . $_[0] . '%' },
test cleanup
Yuki Kimoto authored on 2011-08-10
2078
      {if => sub { $_[0] eq 0 }}]
2079
);
test cleanup
Yuki Kimoto authored on 2011-08-10
2080
is_deeply($where->param, {'table1.id' => 0, 'table1.author' => '%0%', 'table1.price' => '%0%'});
test cleanup
Yuki Kimoto authored on 2011-08-10
2081

            
2082
$where = $dbi->where;
2083
$where->param({id => '', author => '', price => ''});
2084
$where->if('length');
2085
$where->map(
test cleanup
Yuki Kimoto authored on 2011-08-10
2086
    id => 'table1.id',
2087
    author => ['table1.author', sub { '%' . $_[0] . '%' }],
2088
    price => ['table1.price', sub { '%' . $_[0] . '%' },
test cleanup
Yuki Kimoto authored on 2011-08-10
2089
      {if => sub { $_[0] eq 1 }}]
2090
);
2091
is_deeply($where->param, {});
2092

            
2093
$where = $dbi->where;
2094
$where->param({id => undef, author => undef, price => undef});
2095
$where->if('length');
2096
$where->map(
test cleanup
Yuki Kimoto authored on 2011-08-10
2097
    id => 'table1.id',
2098
    price => ['table1.price', {if => 'exists'}]
test cleanup
Yuki Kimoto authored on 2011-08-10
2099
);
test cleanup
Yuki Kimoto authored on 2011-08-10
2100
is_deeply($where->param, {'table1.price' => undef});
test cleanup
Yuki Kimoto authored on 2011-08-10
2101

            
2102
$where = $dbi->where;
2103
$where->param({price => 'a'});
2104
$where->if('length');
2105
$where->map(
test cleanup
Yuki Kimoto authored on 2011-08-10
2106
    id => ['table1.id', {if => 'exists'}],
2107
    price => ['table1.price', sub { '%' . $_[0] }, {if => 'exists'}]
test cleanup
Yuki Kimoto authored on 2011-08-10
2108
);
test cleanup
Yuki Kimoto authored on 2011-08-10
2109
is_deeply($where->param, {'table1.price' => '%a'});
test cleanup
Yuki Kimoto authored on 2011-08-10
2110

            
2111
$where = $dbi->where;
2112
$where->param({id => [1, 2], author => 'Ken', price => 1900});
2113
$where->map(
test cleanup
Yuki Kimoto authored on 2011-08-10
2114
    id => 'table1.id',
2115
    author => ['table1.author', sub { '%' . $_[0] . '%' }],
2116
    price => ['table1.price', {if => sub { $_[0] eq 1900 }}]
test cleanup
Yuki Kimoto authored on 2011-08-10
2117
);
test cleanup
Yuki Kimoto authored on 2011-08-10
2118
is_deeply($where->param, {'table1.id' => [1, 2], 'table1.author' => '%Ken%',
2119
  'table1.price' => 1900});
test cleanup
Yuki Kimoto authored on 2011-08-10
2120

            
2121
$where = $dbi->where;
2122
$where->if('length');
2123
$where->param({id => ['', ''], author => 'Ken', price => 1900});
2124
$where->map(
test cleanup
Yuki Kimoto authored on 2011-08-10
2125
    id => 'table1.id',
2126
    author => ['table1.author', sub { '%' . $_[0] . '%' }],
2127
    price => ['table1.price', {if => sub { $_[0] eq 1900 }}]
test cleanup
Yuki Kimoto authored on 2011-08-10
2128
);
test cleanup
Yuki Kimoto authored on 2011-08-10
2129
is_deeply($where->param, {'table1.id' => [$dbi->not_exists, $dbi->not_exists], 'table1.author' => '%Ken%',
2130
  'table1.price' => 1900});
test cleanup
Yuki Kimoto authored on 2011-08-10
2131

            
2132
$where = $dbi->where;
2133
$where->param({id => ['', ''], author => 'Ken', price => 1900});
2134
$where->map(
test cleanup
Yuki Kimoto authored on 2011-08-10
2135
    id => ['table1.id', {if => 'length'}],
2136
    author => ['table1.author', sub { '%' . $_[0] . '%' }, {if => 'defined'}],
2137
    price => ['table1.price', {if => sub { $_[0] eq 1900 }}]
test cleanup
Yuki Kimoto authored on 2011-08-10
2138
);
test cleanup
Yuki Kimoto authored on 2011-08-10
2139
is_deeply($where->param, {'table1.id' => [$dbi->not_exists, $dbi->not_exists], 'table1.author' => '%Ken%',
2140
  'table1.price' => 1900});
test cleanup
Yuki Kimoto authored on 2011-08-10
2141

            
2142
test 'dbi_option default';
2143
$dbi = DBIx::Custom->new;
2144
is_deeply($dbi->dbi_option, {});
2145

            
2146
test 'register_tag_processor';
2147
$dbi = DBIx::Custom->connect;
2148
$dbi->register_tag_processor(
2149
    a => sub { 1 }
2150
);
test cleanup
Yuki Kimoto authored on 2011-08-10
2151
is($dbi->{_tags}->{a}->(), 1);
test cleanup
Yuki Kimoto authored on 2011-08-10
2152

            
2153
test 'register_tag';
2154
$dbi = DBIx::Custom->connect;
2155
$dbi->register_tag(
2156
    b => sub { 2 }
2157
);
test cleanup
Yuki Kimoto authored on 2011-08-10
2158
is($dbi->{_tags}->{b}->(), 2);
test cleanup
Yuki Kimoto authored on 2011-08-10
2159

            
2160
test 'table not specify exception';
2161
$dbi = DBIx::Custom->connect;
2162
eval {$dbi->insert};
2163
like($@, qr/table/);
2164
eval {$dbi->update};
2165
like($@, qr/table/);
2166
eval {$dbi->delete};
2167
like($@, qr/table/);
2168
eval {$dbi->select};
2169
like($@, qr/table/);
test cleanup
Yuki Kimoto authored on 2011-08-10
2170

            
test cleanup
Yuki Kimoto authored on 2011-08-10
2171
test 'more tests';
2172
$dbi = DBIx::Custom->connect;
2173
eval{$dbi->apply_filter('table', 'column', [])};
2174
like($@, qr/apply_filter/);
2175

            
2176
eval{$dbi->apply_filter('table', 'column', {outer => 2})};
2177
like($@, qr/apply_filter/);
2178

            
2179
$dbi->apply_filter(
2180

            
2181
);
2182
$dbi = DBIx::Custom->connect;
2183
eval { $dbi->execute('drop table table1') };
2184
$dbi->execute($create_table1);
2185
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2186
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
2187
$dbi->apply_filter('table1', 'key2', 
2188
                   {in => sub { $_[0] * 3 }, out => sub { $_[0] * 2 }});
2189
$rows = $dbi->select(table => 'table1', where => {key2 => 1})->all;
2190
is_deeply($rows, [{key1 => 1, key2 => 6}]);
2191

            
2192
$dbi = DBIx::Custom->connect;
2193
eval { $dbi->execute('drop table table1') };
2194
$dbi->execute($create_table1);
2195
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2196
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
2197
$dbi->apply_filter('table1', 'key2', {});
2198
$rows = $dbi->select(table => 'table1', where => {key2 => 2})->all;
2199
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2200

            
2201
$dbi = DBIx::Custom->connect;
2202
eval {$dbi->apply_filter('table1', 'key2', {out => 'no'})};
2203
like($@, qr/not registered/);
2204
eval {$dbi->apply_filter('table1', 'key2', {in => 'no'})};
2205
like($@, qr/not registered/);
2206
$dbi->method({one => sub { 1 }});
2207
is($dbi->one, 1);
2208

            
2209
eval{DBIx::Custom->connect(dsn => undef)};
2210
like($@, qr/_connect/);
2211

            
2212
$dbi = DBIx::Custom->connect;
2213
eval { $dbi->execute('drop table table1') };
2214
$dbi->execute($create_table1);
2215
$dbi->register_filter(twice => sub { $_[0] * 2 });
2216
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
2217
             filter => {key1 => 'twice'});
2218
$row = $dbi->select(table => 'table1')->one;
2219
is_deeply($row, {key1 => 2, key2 => 2});
2220
eval {$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
2221
             filter => {key1 => 'no'}) };
2222
like($@, qr//);
2223

            
2224
$dbi->register_filter(one => sub { });
2225
$dbi->default_fetch_filter('one');
2226
ok($dbi->default_fetch_filter);
2227
$dbi->default_bind_filter('one');
2228
ok($dbi->default_bind_filter);
2229
eval{$dbi->default_fetch_filter('no')};
2230
like($@, qr/not registered/);
2231
eval{$dbi->default_bind_filter('no')};
2232
like($@, qr/not registered/);
2233
$dbi->default_bind_filter(undef);
2234
ok(!defined $dbi->default_bind_filter);
2235
$dbi->default_fetch_filter(undef);
2236
ok(!defined $dbi->default_fetch_filter);
2237
eval {$dbi->execute('select * from table1 {} {= author') };
2238
like($@, qr/Tag not finished/);
2239

            
2240
$dbi = DBIx::Custom->connect;
2241
eval { $dbi->execute('drop table table1') };
2242
$dbi->execute($create_table1);
2243
$dbi->register_filter(one => sub { 1 });
2244
$result = $dbi->select(table => 'table1');
2245
eval {$result->filter(key1 => 'no')};
2246
like($@, qr/not registered/);
2247
eval {$result->end_filter(key1 => 'no')};
2248
like($@, qr/not registered/);
2249
$result->default_filter(undef);
2250
ok(!defined $result->default_filter);
2251
$result->default_filter('one');
2252
is($result->default_filter->(), 1);
2253

            
2254
test 'dbi_option';
2255
$dbi = DBIx::Custom->connect(dbi_option => {PrintError => 1});
2256
ok($dbi->dbh->{PrintError});
2257
$dbi = DBIx::Custom->connect(dbi_options => {PrintError => 1});
2258
ok($dbi->dbh->{PrintError});
2259

            
2260
test 'DBIx::Custom::Result stash()';
2261
$result = DBIx::Custom::Result->new;
2262
is_deeply($result->stash, {}, 'default');
2263
$result->stash->{foo} = 1;
2264
is($result->stash->{foo}, 1, 'get and set');
test cleanup
Yuki Kimoto authored on 2011-08-10
2265

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2266
test 'delete_at';
2267
$dbi = DBIx::Custom->connect;
2268
eval { $dbi->execute('drop table table1') };
2269
$dbi->execute($create_table1_2);
2270
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2271
$dbi->delete_at(
2272
    table => 'table1',
2273
    primary_key => ['key1', 'key2'],
2274
    where => [1, 2],
2275
);
2276
is_deeply($dbi->select(table => 'table1')->all, []);
2277

            
2278
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2279
$dbi->delete_at(
2280
    table => 'table1',
2281
    primary_key => 'key1',
2282
    where => 1,
2283
);
2284
is_deeply($dbi->select(table => 'table1')->all, []);
2285

            
2286
test 'insert_at';
2287
$dbi = DBIx::Custom->connect;
2288
eval { $dbi->execute('drop table table1') };
2289
$dbi->execute($create_table1_2);
2290
$dbi->insert_at(
2291
    primary_key => ['key1', 'key2'], 
2292
    table => 'table1',
2293
    where => [1, 2],
2294
    param => {key3 => 3}
2295
);
2296
is($dbi->select(table => 'table1')->one->{key1}, 1);
2297
is($dbi->select(table => 'table1')->one->{key2}, 2);
2298
is($dbi->select(table => 'table1')->one->{key3}, 3);
2299

            
2300
$dbi->delete_all(table => 'table1');
2301
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2302
$dbi->insert_at(
2303
    primary_key => 'key1', 
2304
    table => 'table1',
2305
    where => 1,
2306
    param => {key2 => 2, key3 => 3}
2307
);
2308

            
2309
is($dbi->select(table => 'table1')->one->{key1}, 1);
2310
is($dbi->select(table => 'table1')->one->{key2}, 2);
2311
is($dbi->select(table => 'table1')->one->{key3}, 3);
2312

            
2313
eval {
2314
    $dbi->insert_at(
2315
        table => 'table1',
2316
        primary_key => ['key1', 'key2'],
2317
        where => {},
2318
        param => {key1 => 1, key2 => 2, key3 => 3},
2319
    );
2320
};
2321
like($@, qr/must be/);
2322

            
2323
$dbi = DBIx::Custom->connect;
2324
eval { $dbi->execute('drop table table1') };
2325
$dbi->execute($create_table1_2);
2326
$dbi->insert_at(
2327
    {key3 => 3},
2328
    primary_key => ['key1', 'key2'], 
2329
    table => 'table1',
2330
    where => [1, 2],
2331
);
2332
is($dbi->select(table => 'table1')->one->{key1}, 1);
2333
is($dbi->select(table => 'table1')->one->{key2}, 2);
2334
is($dbi->select(table => 'table1')->one->{key3}, 3);
2335

            
2336
test 'update_at';
2337
$dbi = DBIx::Custom->connect;
2338
eval { $dbi->execute('drop table table1') };
2339
$dbi->execute($create_table1_2);
2340
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2341
$dbi->update_at(
2342
    table => 'table1',
2343
    primary_key => ['key1', 'key2'],
2344
    where => [1, 2],
2345
    param => {key3 => 4}
2346
);
2347
is($dbi->select(table => 'table1')->one->{key1}, 1);
2348
is($dbi->select(table => 'table1')->one->{key2}, 2);
2349
is($dbi->select(table => 'table1')->one->{key3}, 4);
2350

            
2351
$dbi->delete_all(table => 'table1');
2352
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2353
$dbi->update_at(
2354
    table => 'table1',
2355
    primary_key => 'key1',
2356
    where => 1,
2357
    param => {key3 => 4}
2358
);
2359
is($dbi->select(table => 'table1')->one->{key1}, 1);
2360
is($dbi->select(table => 'table1')->one->{key2}, 2);
2361
is($dbi->select(table => 'table1')->one->{key3}, 4);
2362

            
2363
$dbi = DBIx::Custom->connect;
2364
eval { $dbi->execute('drop table table1') };
2365
$dbi->execute($create_table1_2);
2366
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2367
$dbi->update_at(
2368
    {key3 => 4},
2369
    table => 'table1',
2370
    primary_key => ['key1', 'key2'],
2371
    where => [1, 2]
2372
);
2373
is($dbi->select(table => 'table1')->one->{key1}, 1);
2374
is($dbi->select(table => 'table1')->one->{key2}, 2);
2375
is($dbi->select(table => 'table1')->one->{key3}, 4);
2376

            
2377
test 'select_at';
2378
$dbi = DBIx::Custom->connect;
2379
eval { $dbi->execute('drop table table1') };
2380
$dbi->execute($create_table1_2);
2381
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2382
$result = $dbi->select_at(
2383
    table => 'table1',
2384
    primary_key => ['key1', 'key2'],
2385
    where => [1, 2]
2386
);
2387
$row = $result->one;
2388
is($row->{key1}, 1);
2389
is($row->{key2}, 2);
2390
is($row->{key3}, 3);
2391

            
2392
$dbi->delete_all(table => 'table1');
2393
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2394
$result = $dbi->select_at(
2395
    table => 'table1',
2396
    primary_key => 'key1',
2397
    where => 1,
2398
);
2399
$row = $result->one;
2400
is($row->{key1}, 1);
2401
is($row->{key2}, 2);
2402
is($row->{key3}, 3);
2403

            
2404
$dbi->delete_all(table => 'table1');
2405
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2406
$result = $dbi->select_at(
2407
    table => 'table1',
2408
    primary_key => ['key1', 'key2'],
2409
    where => [1, 2]
2410
);
2411
$row = $result->one;
2412
is($row->{key1}, 1);
2413
is($row->{key2}, 2);
2414
is($row->{key3}, 3);
2415

            
2416
eval {
2417
    $result = $dbi->select_at(
2418
        table => 'table1',
2419
        primary_key => ['key1', 'key2'],
2420
        where => {},
2421
    );
2422
};
2423
like($@, qr/must be/);
2424

            
2425
eval {
2426
    $result = $dbi->select_at(
2427
        table => 'table1',
2428
        primary_key => ['key1', 'key2'],
2429
        where => [1],
2430
    );
2431
};
2432
like($@, qr/same/);
2433

            
2434
eval {
2435
    $result = $dbi->update_at(
2436
        table => 'table1',
2437
        primary_key => ['key1', 'key2'],
2438
        where => {},
2439
        param => {key1 => 1, key2 => 2},
2440
    );
2441
};
2442
like($@, qr/must be/);
2443

            
2444
eval {
2445
    $result = $dbi->delete_at(
2446
        table => 'table1',
2447
        primary_key => ['key1', 'key2'],
2448
        where => {},
2449
    );
2450
};
2451
like($@, qr/must be/);
2452

            
2453
test 'columns';
2454
use MyDBI1;
2455
$dbi = MyDBI1->connect;
cleanup test
Yuki Kimoto authored on 2011-08-10
2456
$model = $dbi->model('table1');
cleanup test
Yuki Kimoto authored on 2011-08-10
2457

            
2458

            
2459
test 'model delete_at';
2460
$dbi = MyDBI6->connect;
2461
eval { $dbi->execute('drop table table1') };
2462
eval { $dbi->execute('drop table table2') };
2463
eval { $dbi->execute('drop table table3') };
2464
$dbi->execute($create_table1_2);
2465
$dbi->execute($create_table2_2);
2466
$dbi->execute($create_table3);
2467
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2468
$dbi->model('table1')->delete_at(where => [1, 2]);
2469
is_deeply($dbi->select(table => 'table1')->all, []);
2470
$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
cleanup test
Yuki Kimoto authored on 2011-08-10
2471
$dbi->model('table1')->delete_at(where => [1, 2]);
cleanup test
Yuki Kimoto authored on 2011-08-10
2472
is_deeply($dbi->select(table => 'table1')->all, []);
2473
$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
cleanup test
Yuki Kimoto authored on 2011-08-10
2474
$dbi->model('table3')->delete_at(where => [1, 2]);
2475
is_deeply($dbi->select(table => 'table3')->all, []);
cleanup test
Yuki Kimoto authored on 2011-08-10
2476

            
2477
test 'model insert_at';
2478
$dbi = MyDBI6->connect;
2479
eval { $dbi->execute('drop table table1') };
2480
$dbi->execute($create_table1_2);
2481
$dbi->model('table1')->insert_at(
2482
    where => [1, 2],
2483
    param => {key3 => 3}
2484
);
2485
$result = $dbi->model('table1')->select;
2486
$row = $result->one;
2487
is($row->{key1}, 1);
2488
is($row->{key2}, 2);
2489
is($row->{key3}, 3);
2490

            
2491
test 'model update_at';
2492
$dbi = MyDBI6->connect;
2493
eval { $dbi->execute('drop table table1') };
2494
$dbi->execute($create_table1_2);
2495
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2496
$dbi->model('table1')->update_at(
2497
    where => [1, 2],
2498
    param => {key3 => 4}
2499
);
2500
$result = $dbi->model('table1')->select;
2501
$row = $result->one;
2502
is($row->{key1}, 1);
2503
is($row->{key2}, 2);
2504
is($row->{key3}, 4);
2505

            
2506
test 'model select_at';
2507
$dbi = MyDBI6->connect;
2508
eval { $dbi->execute('drop table table1') };
2509
$dbi->execute($create_table1_2);
2510
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2511
$result = $dbi->model('table1')->select_at(where => [1, 2]);
2512
$row = $result->one;
2513
is($row->{key1}, 1);
2514
is($row->{key2}, 2);
2515
is($row->{key3}, 3);
2516

            
2517

            
2518
test 'mycolumn and column';
2519
$dbi = MyDBI7->connect;
2520
eval { $dbi->execute('drop table table1') };
2521
eval { $dbi->execute('drop table table2') };
2522
$dbi->execute($create_table1);
2523
$dbi->execute($create_table2);
2524
$dbi->separator('__');
2525
$dbi->setup_model;
2526
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2527
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2528
$model = $dbi->model('table1');
2529
$result = $model->select(
2530
    column => [$model->mycolumn, $model->column('table2')],
2531
    where => {'table1.key1' => 1}
2532
);
2533
is_deeply($result->one,
2534
          {key1 => 1, key2 => 2, 'table2__key1' => 1, 'table2__key3' => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
2535

            
test cleanup
Yuki Kimoto authored on 2011-08-10
2536
test 'insert_param';
2537
$dbi = DBIx::Custom->connect;
2538
eval { $dbi->execute('drop table table1') };
2539
$dbi->execute($create_table1_2);
2540
$param = {key1 => 1, key2 => 2};
2541
$insert_param = $dbi->insert_param($param);
2542
$sql = <<"EOS";
2543
insert into table1 $insert_param
2544
EOS
2545
$dbi->execute($sql, param => $param, table => 'table1');
2546
is($dbi->select(table => 'table1')->one->{key1}, 1);
2547
is($dbi->select(table => 'table1')->one->{key2}, 2);
2548

            
2549
$dbi = DBIx::Custom->connect;
2550
eval { $dbi->execute('drop table table1') };
2551
$dbi->execute($create_table1_2);
2552
$param = {key1 => 1, key2 => 2};
2553
$insert_param = $dbi->insert_param($param);
2554
$sql = <<"EOS";
2555
insert into table1 $insert_param
2556
EOS
2557
$dbi->execute($sql, param => $param, table => 'table1');
2558
is($dbi->select(table => 'table1')->one->{key1}, 1);
2559
is($dbi->select(table => 'table1')->one->{key2}, 2);
2560

            
2561
eval { $dbi->insert_param({";" => 1}) };
2562
like($@, qr/not safety/);
test cleanup
Yuki Kimoto authored on 2011-08-10
2563

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2564
test 'mycolumn';
2565
$dbi = MyDBI8->connect;
2566
eval { $dbi->execute('drop table table1') };
2567
eval { $dbi->execute('drop table table2') };
2568
$dbi->execute($create_table1);
2569
$dbi->execute($create_table2);
2570
$dbi->setup_model;
2571
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2572
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2573
$model = $dbi->model('table1');
2574
$result = $model->select_at(
2575
    column => [
2576
        $model->mycolumn,
2577
        $model->column('table2')
2578
    ]
2579
);
2580
is_deeply($result->one,
2581
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
2582

            
2583
$result = $model->select_at(
2584
    column => [
2585
        $model->mycolumn(['key1']),
2586
        $model->column(table2 => ['key1'])
2587
    ]
2588
);
2589
is_deeply($result->one,
2590
          {key1 => 1, 'table2.key1' => 1});
2591
$result = $model->select_at(
2592
    column => [
2593
        $model->mycolumn(['key1']),
2594
        {table2 => ['key1']}
2595
    ]
2596
);
2597
is_deeply($result->one,
2598
          {key1 => 1, 'table2.key1' => 1});
2599

            
2600
$result = $model->select_at(
2601
    column => [
2602
        $model->mycolumn(['key1']),
2603
        ['table2.key1', as => 'table2.key1']
2604
    ]
2605
);
2606
is_deeply($result->one,
2607
          {key1 => 1, 'table2.key1' => 1});
2608

            
2609
$result = $model->select_at(
2610
    column => [
2611
        $model->mycolumn(['key1']),
2612
        ['table2.key1' => 'table2.key1']
2613
    ]
2614
);
2615
is_deeply($result->one,
2616
          {key1 => 1, 'table2.key1' => 1});
2617

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2618
test 'merge_param';
2619
$dbi = DBIx::Custom->new;
2620
$params = [
2621
    {key1 => 1, key2 => 2, key3 => 3},
2622
    {key1 => 1, key2 => 2},
2623
    {key1 => 1}
2624
];
2625
$param = $dbi->merge_param($params->[0], $params->[1], $params->[2]);
2626
is_deeply($param, {key1 => [1, 1, 1], key2 => [2, 2], key3 => 3});
2627

            
2628
$params = [
2629
    {key1 => [1, 2], key2 => 1, key3 => [1, 2]},
2630
    {key1 => [3, 4], key2 => [2, 3], key3 => 3}
2631
];
2632
$param = $dbi->merge_param($params->[0], $params->[1]);
2633
is_deeply($param, {key1 => [1, 2, 3, 4], key2 => [1, 2, 3], key3 => [1, 2, 3]});
2634

            
2635
test 'select() param option';
2636
$dbi = DBIx::Custom->connect;
2637
eval { $dbi->execute('drop table table1') };
2638
$dbi->execute($create_table1);
2639
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2640
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2641
eval { $dbi->execute('drop table table2') };
2642
$dbi->execute($create_table2);
2643
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
2644
$dbi->insert(table => 'table2', param => {key1 => 2, key3 => 5});
2645
$rows = $dbi->select(
2646
    table => 'table1',
2647
    column => 'table1.key1 as table1_key1, key2, key3',
2648
    where   => {'table1.key2' => 3},
2649
    join  => ['inner join (select * from table2 where {= table2.key3})' . 
2650
              ' as table2 on table1.key1 = table2.key1'],
2651
    param => {'table2.key3' => 5}
2652
)->all;
2653
is_deeply($rows, [{table1_key1 => 2, key2 => 3, key3 => 5}]);
2654

            
2655
test 'select() string where';
2656
$dbi = DBIx::Custom->connect;
2657
eval { $dbi->execute('drop table table1') };
2658
$dbi->execute($create_table1);
2659
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2660
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2661
$rows = $dbi->select(
2662
    table => 'table1',
2663
    where => 'key1 = :key1 and key2 = :key2',
2664
    where_param => {key1 => 1, key2 => 2}
2665
)->all;
2666
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2667

            
2668
$dbi = DBIx::Custom->connect;
2669
eval { $dbi->execute('drop table table1') };
2670
$dbi->execute($create_table1);
2671
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2672
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2673
$rows = $dbi->select(
2674
    table => 'table1',
2675
    where => [
2676
        'key1 = :key1 and key2 = :key2',
2677
        {key1 => 1, key2 => 2}
2678
    ]
2679
)->all;
2680
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2681

            
2682
test 'delete() string where';
2683
$dbi = DBIx::Custom->connect;
2684
eval { $dbi->execute('drop table table1') };
2685
$dbi->execute($create_table1);
2686
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2687
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2688
$dbi->delete(
2689
    table => 'table1',
2690
    where => 'key1 = :key1 and key2 = :key2',
2691
    where_param => {key1 => 1, key2 => 2}
2692
);
2693
$rows = $dbi->select(table => 'table1')->all;
2694
is_deeply($rows, [{key1 => 2, key2 => 3}]);
2695

            
2696
$dbi = DBIx::Custom->connect;
2697
eval { $dbi->execute('drop table table1') };
2698
$dbi->execute($create_table1);
2699
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2700
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2701
$dbi->delete(
2702
    table => 'table1',
2703
    where => [
2704
        'key1 = :key1 and key2 = :key2',
2705
         {key1 => 1, key2 => 2}
2706
    ]
2707
);
2708
$rows = $dbi->select(table => 'table1')->all;
2709
is_deeply($rows, [{key1 => 2, key2 => 3}]);
2710

            
2711

            
2712
test 'update() string where';
2713
$dbi = DBIx::Custom->connect;
2714
eval { $dbi->execute('drop table table1') };
2715
$dbi->execute($create_table1);
2716
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2717
$dbi->update(
2718
    table => 'table1',
2719
    param => {key1 => 5},
2720
    where => 'key1 = :key1 and key2 = :key2',
2721
    where_param => {key1 => 1, key2 => 2}
2722
);
2723
$rows = $dbi->select(table => 'table1')->all;
2724
is_deeply($rows, [{key1 => 5, key2 => 2}]);
2725

            
2726
$dbi = DBIx::Custom->connect;
2727
eval { $dbi->execute('drop table table1') };
2728
$dbi->execute($create_table1);
2729
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2730
$dbi->update(
2731
    table => 'table1',
2732
    param => {key1 => 5},
2733
    where => [
2734
        'key1 = :key1 and key2 = :key2',
2735
        {key1 => 1, key2 => 2}
2736
    ]
2737
);
2738
$rows = $dbi->select(table => 'table1')->all;
2739
is_deeply($rows, [{key1 => 5, key2 => 2}]);
2740

            
2741
test 'insert id and primary_key option';
2742
$dbi = DBIx::Custom->connect;
2743
eval { $dbi->execute('drop table table1') };
2744
$dbi->execute($create_table1_2);
2745
$dbi->insert(
2746
    primary_key => ['key1', 'key2'], 
2747
    table => 'table1',
2748
    id => [1, 2],
2749
    param => {key3 => 3}
2750
);
2751
is($dbi->select(table => 'table1')->one->{key1}, 1);
2752
is($dbi->select(table => 'table1')->one->{key2}, 2);
2753
is($dbi->select(table => 'table1')->one->{key3}, 3);
2754

            
2755
$dbi->delete_all(table => 'table1');
2756
$dbi->insert(
2757
    primary_key => 'key1', 
2758
    table => 'table1',
2759
    id => 0,
2760
    param => {key2 => 2, key3 => 3}
2761
);
2762

            
2763
is($dbi->select(table => 'table1')->one->{key1}, 0);
2764
is($dbi->select(table => 'table1')->one->{key2}, 2);
2765
is($dbi->select(table => 'table1')->one->{key3}, 3);
2766

            
2767
$dbi = DBIx::Custom->connect;
2768
eval { $dbi->execute('drop table table1') };
2769
$dbi->execute($create_table1_2);
2770
$dbi->insert(
2771
    {key3 => 3},
2772
    primary_key => ['key1', 'key2'], 
2773
    table => 'table1',
2774
    id => [1, 2],
2775
);
2776
is($dbi->select(table => 'table1')->one->{key1}, 1);
2777
is($dbi->select(table => 'table1')->one->{key2}, 2);
2778
is($dbi->select(table => 'table1')->one->{key3}, 3);
test cleanup
Yuki Kimoto authored on 2011-08-10
2779

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2780
test 'model insert id and primary_key option';
2781
$dbi = MyDBI6->connect;
2782
eval { $dbi->execute('drop table table1') };
2783
$dbi->execute($create_table1_2);
2784
$dbi->model('table1')->insert(
2785
    id => [1, 2],
2786
    param => {key3 => 3}
2787
);
2788
$result = $dbi->model('table1')->select;
2789
$row = $result->one;
2790
is($row->{key1}, 1);
2791
is($row->{key2}, 2);
2792
is($row->{key3}, 3);
2793

            
2794
$dbi = MyDBI6->connect;
2795
eval { $dbi->execute('drop table table1') };
2796
$dbi->execute($create_table1_2);
2797
$dbi->model('table1')->insert(
2798
    {key3 => 3},
2799
    id => [1, 2]
2800
);
2801
$result = $dbi->model('table1')->select;
2802
$row = $result->one;
2803
is($row->{key1}, 1);
2804
is($row->{key2}, 2);
2805
is($row->{key3}, 3);
2806

            
2807
test 'update and id option';
2808
$dbi = DBIx::Custom->connect;
2809
eval { $dbi->execute('drop table table1') };
2810
$dbi->execute($create_table1_2);
2811
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2812
$dbi->update(
2813
    table => 'table1',
2814
    primary_key => ['key1', 'key2'],
2815
    id => [1, 2],
2816
    param => {key3 => 4}
2817
);
2818
is($dbi->select(table => 'table1')->one->{key1}, 1);
2819
is($dbi->select(table => 'table1')->one->{key2}, 2);
2820
is($dbi->select(table => 'table1')->one->{key3}, 4);
2821

            
2822
$dbi->delete_all(table => 'table1');
2823
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2824
$dbi->update(
2825
    table => 'table1',
2826
    primary_key => 'key1',
2827
    id => 0,
2828
    param => {key3 => 4}
2829
);
2830
is($dbi->select(table => 'table1')->one->{key1}, 0);
2831
is($dbi->select(table => 'table1')->one->{key2}, 2);
2832
is($dbi->select(table => 'table1')->one->{key3}, 4);
2833

            
2834
$dbi = DBIx::Custom->connect;
2835
eval { $dbi->execute('drop table table1') };
2836
$dbi->execute($create_table1_2);
2837
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2838
$dbi->update(
2839
    {key3 => 4},
2840
    table => 'table1',
2841
    primary_key => ['key1', 'key2'],
2842
    id => [1, 2]
2843
);
2844
is($dbi->select(table => 'table1')->one->{key1}, 1);
2845
is($dbi->select(table => 'table1')->one->{key2}, 2);
2846
is($dbi->select(table => 'table1')->one->{key3}, 4);
2847

            
2848

            
2849
test 'model update and id option';
2850
$dbi = MyDBI6->connect;
2851
eval { $dbi->execute('drop table table1') };
2852
$dbi->execute($create_table1_2);
2853
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2854
$dbi->model('table1')->update(
2855
    id => [1, 2],
2856
    param => {key3 => 4}
2857
);
2858
$result = $dbi->model('table1')->select;
2859
$row = $result->one;
2860
is($row->{key1}, 1);
2861
is($row->{key2}, 2);
2862
is($row->{key3}, 4);
2863

            
2864

            
2865
test 'delete and id option';
2866
$dbi = DBIx::Custom->connect;
2867
eval { $dbi->execute('drop table table1') };
2868
$dbi->execute($create_table1_2);
2869
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2870
$dbi->delete(
2871
    table => 'table1',
2872
    primary_key => ['key1', 'key2'],
2873
    id => [1, 2],
2874
);
2875
is_deeply($dbi->select(table => 'table1')->all, []);
2876

            
2877
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2878
$dbi->delete(
2879
    table => 'table1',
2880
    primary_key => 'key1',
2881
    id => 0,
2882
);
2883
is_deeply($dbi->select(table => 'table1')->all, []);
2884

            
2885

            
2886
test 'model delete and id option';
2887
$dbi = MyDBI6->connect;
2888
eval { $dbi->execute('drop table table1') };
2889
eval { $dbi->execute('drop table table2') };
2890
eval { $dbi->execute('drop table table3') };
2891
$dbi->execute($create_table1_2);
2892
$dbi->execute($create_table2_2);
2893
$dbi->execute($create_table3);
2894
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2895
$dbi->model('table1')->delete(id => [1, 2]);
2896
is_deeply($dbi->select(table => 'table1')->all, []);
2897
$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
cleanup test
Yuki Kimoto authored on 2011-08-10
2898
$dbi->model('table1')->delete(id => [1, 2]);
cleanup test
Yuki Kimoto authored on 2011-08-10
2899
is_deeply($dbi->select(table => 'table1')->all, []);
2900
$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
cleanup test
Yuki Kimoto authored on 2011-08-10
2901
$dbi->model('table3')->delete(id => [1, 2]);
2902
is_deeply($dbi->select(table => 'table3')->all, []);
cleanup test
Yuki Kimoto authored on 2011-08-10
2903

            
2904

            
2905
test 'select and id option';
2906
$dbi = DBIx::Custom->connect;
2907
eval { $dbi->execute('drop table table1') };
2908
$dbi->execute($create_table1_2);
2909
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2910
$result = $dbi->select(
2911
    table => 'table1',
2912
    primary_key => ['key1', 'key2'],
2913
    id => [1, 2]
2914
);
2915
$row = $result->one;
2916
is($row->{key1}, 1);
2917
is($row->{key2}, 2);
2918
is($row->{key3}, 3);
2919

            
2920
$dbi->delete_all(table => 'table1');
2921
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2922
$result = $dbi->select(
2923
    table => 'table1',
2924
    primary_key => 'key1',
2925
    id => 0,
2926
);
2927
$row = $result->one;
2928
is($row->{key1}, 0);
2929
is($row->{key2}, 2);
2930
is($row->{key3}, 3);
2931

            
2932
$dbi->delete_all(table => 'table1');
2933
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2934
$result = $dbi->select(
2935
    table => 'table1',
2936
    primary_key => ['key1', 'key2'],
2937
    id => [1, 2]
2938
);
2939
$row = $result->one;
2940
is($row->{key1}, 1);
2941
is($row->{key2}, 2);
2942
is($row->{key3}, 3);
2943

            
2944

            
2945
test 'model select_at';
2946
$dbi = MyDBI6->connect;
2947
eval { $dbi->execute('drop table table1') };
2948
$dbi->execute($create_table1_2);
2949
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2950
$result = $dbi->model('table1')->select(id => [1, 2]);
2951
$row = $result->one;
2952
is($row->{key1}, 1);
2953
is($row->{key2}, 2);
2954
is($row->{key3}, 3);
2955

            
2956
test 'column separator is default .';
2957
$dbi = MyDBI7->connect;
2958
eval { $dbi->execute('drop table table1') };
2959
eval { $dbi->execute('drop table table2') };
2960
$dbi->execute($create_table1);
2961
$dbi->execute($create_table2);
2962
$dbi->setup_model;
2963
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2964
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2965
$model = $dbi->model('table1');
2966
$result = $model->select(
2967
    column => [$model->column('table2')],
2968
    where => {'table1.key1' => 1}
2969
);
2970
is_deeply($result->one,
2971
          {'table2.key1' => 1, 'table2.key3' => 3});
2972

            
2973
$result = $model->select(
2974
    column => [$model->column('table2' => [qw/key1 key3/])],
2975
    where => {'table1.key1' => 1}
2976
);
2977
is_deeply($result->one,
2978
          {'table2.key1' => 1, 'table2.key3' => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
2979

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2980
test 'separator';
2981
$dbi = DBIx::Custom->connect;
2982
eval { $dbi->execute('drop table table1') };
2983
eval { $dbi->execute('drop table table2') };
2984
$dbi->execute($create_table1);
2985
$dbi->execute($create_table2);
test cleanup
Yuki Kimoto authored on 2011-08-10
2986

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2987
$dbi->create_model(
2988
    table => 'table1',
2989
    join => [
2990
       'left outer join table2 on table1.key1 = table2.key1'
2991
    ],
2992
    primary_key => ['key1'],
2993
);
2994
$model2 = $dbi->create_model(
2995
    table => 'table2',
2996
);
2997
$dbi->setup_model;
2998
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2999
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
3000
$model = $dbi->model('table1');
3001
$result = $model->select(
3002
    column => [
3003
        $model->mycolumn,
3004
        {table2 => [qw/key1 key3/]}
3005
    ],
3006
    where => {'table1.key1' => 1}
3007
);
3008
is_deeply($result->one,
3009
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
3010
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
3011

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3012
$dbi->separator('__');
3013
$model = $dbi->model('table1');
3014
$result = $model->select(
3015
    column => [
3016
        $model->mycolumn,
3017
        {table2 => [qw/key1 key3/]}
3018
    ],
3019
    where => {'table1.key1' => 1}
3020
);
3021
is_deeply($result->one,
3022
          {key1 => 1, key2 => 2, 'table2__key1' => 1, 'table2__key3' => 3});
3023
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
3024

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3025
$dbi->separator('-');
3026
$model = $dbi->model('table1');
3027
$result = $model->select(
3028
    column => [
3029
        $model->mycolumn,
3030
        {table2 => [qw/key1 key3/]}
3031
    ],
3032
    where => {'table1.key1' => 1}
3033
);
3034
is_deeply($result->one,
3035
          {key1 => 1, key2 => 2, 'table2-key1' => 1, 'table2-key3' => 3});
3036
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
3037

            
3038

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3039
test 'filter_off';
3040
$dbi = DBIx::Custom->connect;
3041
eval { $dbi->execute('drop table table1') };
3042
eval { $dbi->execute('drop table table2') };
3043
$dbi->execute($create_table1);
3044
$dbi->execute($create_table2);
test cleanup
Yuki Kimoto authored on 2011-08-10
3045

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3046
$dbi->create_model(
3047
    table => 'table1',
3048
    join => [
3049
       'left outer join table2 on table1.key1 = table2.key1'
3050
    ],
3051
    primary_key => ['key1'],
3052
);
3053
$dbi->setup_model;
3054
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3055
$model = $dbi->model('table1');
3056
$result = $model->select(column => 'key1');
3057
$result->filter(key1 => sub { $_[0] * 2 });
3058
is_deeply($result->one, {key1 => 2});
test cleanup
Yuki Kimoto authored on 2011-08-10
3059

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3060
test 'available_datetype';
3061
$dbi = DBIx::Custom->connect;
3062
ok($dbi->can('available_datatype'));
test cleanup
Yuki Kimoto authored on 2011-08-10
3063

            
3064

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3065
test 'select prefix option';
3066
$dbi = DBIx::Custom->connect;
3067
eval { $dbi->execute('drop table table1') };
3068
$dbi->execute($create_table1);
3069
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3070
$rows = $dbi->select(prefix => 'key1,', column => 'key2', table => 'table1')->all;
3071
is_deeply($rows, [{key1 => 1, key2 => 2}], "table");
test cleanup
Yuki Kimoto authored on 2011-08-10
3072

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3073
test 'map_param';
3074
$dbi = DBIx::Custom->connect;
3075
$param = $dbi->map_param(
3076
    {id => 1, author => 'Ken', price => 1900},
test cleanup
Yuki Kimoto authored on 2011-08-10
3077
    id => 'table1.id',
3078
    author => ['table1.author', sub { '%' . $_[0] . '%' }],
3079
    price => ['table1.price', {if => sub { $_[0] eq 1900 }}]
cleanup test
Yuki Kimoto authored on 2011-08-10
3080
);
test cleanup
Yuki Kimoto authored on 2011-08-10
3081
is_deeply($param, {'table1.id' => 1, 'table1.author' => '%Ken%',
3082
  'table1.price' => 1900});
test cleanup
Yuki Kimoto authored on 2011-08-10
3083

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3084
$param = $dbi->map_param(
3085
    {id => 0, author => 0, price => 0},
test cleanup
Yuki Kimoto authored on 2011-08-10
3086
    id => 'table1.id',
3087
    author => ['table1.author', sub { '%' . $_[0] . '%' }],
3088
    price => ['table1.price', sub { '%' . $_[0] . '%' },
cleanup test
Yuki Kimoto authored on 2011-08-10
3089
      {if => sub { $_[0] eq 0 }}]
3090
);
test cleanup
Yuki Kimoto authored on 2011-08-10
3091
is_deeply($param, {'table1.id' => 0, 'table1.author' => '%0%', 'table1.price' => '%0%'});
test cleanup
Yuki Kimoto authored on 2011-08-10
3092

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3093
$param = $dbi->map_param(
3094
    {id => '', author => '', price => ''},
test cleanup
Yuki Kimoto authored on 2011-08-10
3095
    id => 'table1.id',
3096
    author => ['table1.author', sub { '%' . $_[0] . '%' }],
3097
    price => ['table1.price', sub { '%' . $_[0] . '%' },
cleanup test
Yuki Kimoto authored on 2011-08-10
3098
      {if => sub { $_[0] eq 1 }}]
3099
);
3100
is_deeply($param, {});
test cleanup
Yuki Kimoto authored on 2011-08-10
3101

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3102
$param = $dbi->map_param(
3103
    {id => undef, author => undef, price => undef},
test cleanup
Yuki Kimoto authored on 2011-08-10
3104
    id => 'table1.id',
3105
    price => ['table1.price', {if => 'exists'}]
cleanup test
Yuki Kimoto authored on 2011-08-10
3106
);
test cleanup
Yuki Kimoto authored on 2011-08-10
3107
is_deeply($param, {'table1.price' => undef});
test cleanup
Yuki Kimoto authored on 2011-08-10
3108

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3109
$param = $dbi->map_param(
3110
    {price => 'a'},
test cleanup
Yuki Kimoto authored on 2011-08-10
3111
    id => ['table1.id', {if => 'exists'}],
3112
    price => ['table1.price', sub { '%' . $_[0] }, {if => 'exists'}]
cleanup test
Yuki Kimoto authored on 2011-08-10
3113
);
test cleanup
Yuki Kimoto authored on 2011-08-10
3114
is_deeply($param, {'table1.price' => '%a'});
test cleanup
Yuki Kimoto authored on 2011-08-10
3115

            
test cleanup
Yuki Kimoto authored on 2011-08-10
3116
test 'order';
3117
$dbi = DBIx::Custom->connect;
3118
eval { $dbi->execute('drop table table1') };
3119
$dbi->execute($create_table1);
3120
$dbi->insert({key1 => 1, key2 => 1}, table => 'table1');
3121
$dbi->insert({key1 => 1, key2 => 3}, table => 'table1');
3122
$dbi->insert({key1 => 2, key2 => 2}, table => 'table1');
3123
$dbi->insert({key1 => 2, key2 => 4}, table => 'table1');
3124
my $order = $dbi->order;
3125
$order->prepend('key1', 'key2 desc');
3126
$result = $dbi->select(table => 'table1', append => "$order");
3127
is_deeply($result->all, [{key1 => 1, key2 => 3}, {key1 => 1, key2 => 1},
3128
  {key1 => 2, key2 => 4}, {key1 => 2, key2 => 2}]);
3129
$order->prepend('key1 desc');
3130
$result = $dbi->select(table => 'table1', append => "$order");
3131
is_deeply($result->all, [{key1 => 2, key2 => 4}, {key1 => 2, key2 => 2},
3132
  {key1 => 1, key2 => 3}, {key1 => 1, key2 => 1}]);
3133

            
3134
$order = $dbi->order;
3135
$order->prepend(['table1-key1'], [qw/table1-key2 desc/]);
3136
$result = $dbi->select(table => 'table1',
3137
  column => [[key1 => 'table1-key1'], [key2 => 'table1-key2']],
3138
  append => "$order");
3139
is_deeply($result->all, [{'table1-key1' => 1, 'table1-key2' => 3},
3140
  {'table1-key1' => 1, 'table1-key2' => 1},
3141
  {'table1-key1' => 2, 'table1-key2' => 4},
3142
  {'table1-key1' => 2, 'table1-key2' => 2}]);
3143

            
3144
test 'tag_parse';
3145
$dbi = DBIx::Custom->connect;
3146
$dbi->tag_parse(0);
3147
eval { $dbi->execute('drop table table1') };
3148
$dbi->execute($create_table1);
3149
$dbi->insert({key1 => 1, key2 => 1}, table => 'table1');
3150
eval {$dbi->execute("select * from table1 where {= key1}", {key1 => 1})};
3151
ok($@);
3152

            
3153
test 'last_sql';
3154
$dbi = DBIx::Custom->connect;
3155
eval { $dbi->execute('drop table table1') };
3156
$dbi->execute($create_table1);
3157
$dbi->execute('select * from table1');
3158
is($dbi->last_sql, 'select * from table1;');
3159

            
3160
eval{$dbi->execute("aaa")};
3161
is($dbi->last_sql, 'aaa;');
3162

            
3163
test 'DBIx::Custom header';
3164
$dbi = DBIx::Custom->connect;
3165
eval { $dbi->execute('drop table table1') };
3166
$dbi->execute($create_table1);
3167
$result = $dbi->execute('select key1 as h1, key2 as h2 from table1');
3168
is_deeply($result->header, [qw/h1 h2/]);
3169

            
3170
test 'Named placeholder :name(operater) syntax';
3171
$dbi->execute('drop table table1');
3172
$dbi->execute($create_table1_2);
3173
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3174
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3175

            
3176
$source = "select * from table1 where :key1{=} and :key2{=}";
3177
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
3178
$rows = $result->all;
3179
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3180

            
3181
$source = "select * from table1 where :key1{ = } and :key2{=}";
3182
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
3183
$rows = $result->all;
3184
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3185

            
3186
$source = "select * from table1 where :key1{<} and :key2{=}";
3187
$result = $dbi->execute($source, param => {key1 => 5, key2 => 2});
3188
$rows = $result->all;
3189
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3190

            
3191
$source = "select * from table1 where :table1.key1{=} and :table1.key2{=}";
3192
$result = $dbi->execute(
3193
    $source,
3194
    param => {'table1.key1' => 1, 'table1.key2' => 1},
3195
    filter => {'table1.key2' => sub { $_[0] * 2 }}
3196
);
3197
$rows = $result->all;
3198
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3199

            
3200
test 'high perfomance way';
3201
$dbi->execute('drop table table1');
3202
$dbi->execute($create_table1_highperformance);
3203
$rows = [
3204
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3205
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3206
];
3207
{
3208
    my $query;
3209
    foreach my $row (@$rows) {
3210
      $query ||= $dbi->insert($row, table => 'table1', query => 1);
3211
      $dbi->execute($query, $row, filter => {ab => sub { $_[0] * 2 }});
3212
    }
3213
    is_deeply($dbi->select(table => 'table1')->all,
3214
      [
3215
          {ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3216
          {ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3217
      ]
3218
    );
3219
}
3220

            
3221
$dbi->execute('drop table table1');
3222
$dbi->execute($create_table1_highperformance);
3223
$rows = [
3224
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3225
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3226
];
3227
{
3228
    my $query;
3229
    my $sth;
3230
    foreach my $row (@$rows) {
3231
      $query ||= $dbi->insert($row, table => 'table1', query => 1);
3232
      $sth ||= $query->sth;
3233
      $sth->execute(map { $row->{$_} } sort keys %$row);
3234
    }
3235
    is_deeply($dbi->select(table => 'table1')->all,
3236
      [
3237
          {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3238
          {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3239
      ]
3240
    );
3241
}
3242

            
3243
test 'result';
3244
$dbi = DBIx::Custom->connect;
3245
eval { $dbi->execute('drop table table1') };
3246
$dbi->execute($create_table1);
3247
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
3248
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
3249

            
3250
$result = $dbi->select(table => 'table1');
3251
@rows = ();
3252
while (my $row = $result->fetch) {
3253
    push @rows, [@$row];
3254
}
3255
is_deeply(\@rows, [[1, 2], [3, 4]]);
3256

            
3257
$result = $dbi->select(table => 'table1');
3258
@rows = ();
3259
while (my $row = $result->fetch_hash) {
3260
    push @rows, {%$row};
3261
}
3262
is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
3263

            
3264
$dbi = DBIx::Custom->connect;
3265
eval { $dbi->execute('drop table table1') };
3266
$dbi->execute($create_table1);
3267
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
3268
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
3269

            
3270
test 'fetch_all';
3271
$result = $dbi->select(table => 'table1');
3272
$rows = $result->fetch_all;
3273
is_deeply($rows, [[1, 2], [3, 4]]);
3274

            
3275
$result = $dbi->select(table => 'table1');
3276
$rows = $result->fetch_hash_all;
3277
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
3278

            
3279
$result = $dbi->select(table => 'table1');
3280
$result->dbi->filters({three_times => sub { $_[0] * 3}});
3281
$result->filter({key1 => 'three_times'});
3282

            
3283
$rows = $result->fetch_all;
3284
is_deeply($rows, [[3, 2], [9, 4]], "array");
3285

            
3286
$result = $dbi->select(table => 'table1');
3287
$result->dbi->filters({three_times => sub { $_[0] * 3}});
3288
$result->filter({key1 => 'three_times'});
3289
$rows = $result->fetch_hash_all;
3290
is_deeply($rows, [{key1 => 3, key2 => 2}, {key1 => 9, key2 => 4}], "hash");
3291

            
3292
test "query_builder";
3293
$datas = [
3294
    # Basic tests
3295
    {   name            => 'placeholder basic',
3296
        source            => "a {?  k1} b {=  k2} {<> k3} {>  k4} {<  k5} {>= k6} {<= k7} {like k8}", ,
3297
        sql_expected    => "a ? b k2 = ? k3 <> ? k4 > ? k5 < ? k6 >= ? k7 <= ? k8 like ?;",
3298
        columns_expected   => [qw/k1 k2 k3 k4 k5 k6 k7 k8/]
3299
    },
3300
    {
3301
        name            => 'placeholder in',
3302
        source            => "{in k1 3};",
3303
        sql_expected    => "k1 in (?, ?, ?);",
3304
        columns_expected   => [qw/k1 k1 k1/]
3305
    },
3306
    
3307
    # Table name
3308
    {
3309
        name            => 'placeholder with table name',
3310
        source            => "{= a.k1} {= a.k2}",
3311
        sql_expected    => "a.k1 = ? a.k2 = ?;",
3312
        columns_expected  => [qw/a.k1 a.k2/]
3313
    },
3314
    {   
3315
        name            => 'placeholder in with table name',
3316
        source            => "{in a.k1 2} {in b.k2 2}",
3317
        sql_expected    => "a.k1 in (?, ?) b.k2 in (?, ?);",
3318
        columns_expected  => [qw/a.k1 a.k1 b.k2 b.k2/]
3319
    },
3320
    {
3321
        name            => 'not contain tag',
3322
        source            => "aaa",
3323
        sql_expected    => "aaa;",
3324
        columns_expected  => [],
3325
    }
3326
];
3327

            
3328
for (my $i = 0; $i < @$datas; $i++) {
3329
    my $data = $datas->[$i];
3330
    my $builder = DBIx::Custom->new->query_builder;
3331
    my $query = $builder->build_query($data->{source});
3332
    is($query->{sql}, $data->{sql_expected}, "$data->{name} : sql");
3333
    is_deeply($query->columns, $data->{columns_expected}, "$data->{name} : columns");
3334
}
3335

            
3336
$builder = DBIx::Custom->new->query_builder;
3337
$ret_val = $builder->register_tag(
3338
    p => sub {
3339
        my @args = @_;
3340
        
3341
        my $expand    = "? $args[0] $args[1]";
3342
        my $columns = [2];
3343
        return [$expand, $columns];
3344
    }
3345
);
3346

            
3347
$query = $builder->build_query("{p a b}");
3348
is($query->{sql}, "? a b;", "register_tag sql");
3349
is_deeply($query->{columns}, [2], "register_tag columns");
3350
isa_ok($ret_val, 'DBIx::Custom::QueryBuilder');
3351

            
3352
$builder = DBIx::Custom->new->query_builder;
3353

            
3354
eval{$builder->build_query('{? }')};
3355
like($@, qr/\QColumn name must be specified in tag "{? }"/, "? not arguments");
3356

            
3357
eval{$builder->build_query("{a }")};
3358
like($@, qr/\QTag "a" is not registered/, "tag not exist");
3359

            
3360
$builder->register_tag({
3361
    q => 'string'
3362
});
3363

            
3364
eval{$builder->build_query("{q}", {})};
3365
like($@, qr/Tag "q" must be sub reference/, "tag not code ref");
3366

            
3367
$builder->register_tag({
3368
   r => sub {} 
3369
});
3370

            
3371
eval{$builder->build_query("{r}")};
3372
like($@, qr/\QTag "r" must return [STRING, ARRAY_REFERENCE]/, "tag return noting");
3373

            
3374
$builder->register_tag({
3375
   s => sub { return ["a", ""]} 
3376
});
3377

            
3378
eval{$builder->build_query("{s}")};
3379
like($@, qr/\QTag "s" must return [STRING, ARRAY_REFERENCE]/, "tag return not array columns");
3380

            
3381
$builder->register_tag(
3382
    t => sub {return ["a", []]}
3383
);
3384

            
3385

            
3386
test 'General error case';
3387
$builder = DBIx::Custom->new->query_builder;
3388
$builder->register_tag(
3389
    a => sub {
3390
        return ["? ? ?", ['']];
3391
    }
3392
);
3393
eval{$builder->build_query("{a}")};
3394
like($@, qr/\QPlaceholder count/, "placeholder count is invalid");
3395

            
3396

            
3397
test 'Default tag Error case';
3398
eval{$builder->build_query("{= }")};
3399
like($@, qr/Column name must be specified in tag "{= }"/, "basic '=' : key not exist");
3400

            
3401
eval{$builder->build_query("{in }")};
3402
like($@, qr/Column name and count of values must be specified in tag "{in }"/, "in : key not exist");
3403

            
3404
eval{$builder->build_query("{in a}")};
3405
like($@, qr/\QColumn name and count of values must be specified in tag "{in }"/,
3406
     "in : key not exist");
3407

            
3408
eval{$builder->build_query("{in a r}")};
3409
like($@, qr/\QColumn name and count of values must be specified in tag "{in }"/,
3410
     "in : key not exist");
3411

            
3412
test 'variouse source';
3413
$source = "a {= b} c \\{ \\} {= \\{} {= \\}} d;";
3414
$query = $builder->build_query($source);
3415
is($query->sql, 'a b = ? c { } { = ? } = ? d;', "basic : 1");
3416

            
3417
$source = "abc;";
3418
$query = $builder->build_query($source);
3419
is($query->sql, 'abc;', "basic : 2");
3420

            
3421
$source = "{= a}";
3422
$query = $builder->build_query($source);
3423
is($query->sql, 'a = ?;', "only tag");
3424

            
3425
$source = "000;";
3426
$query = $builder->build_query($source);
3427
is($query->sql, '000;', "contain 0 value");
3428

            
3429
$source = "a {= b} }";
3430
eval{$builder->build_query($source)};
3431
like($@, qr/unexpected "}"/, "error : 1");
3432

            
3433
$source = "a {= {}";
3434
eval{$builder->build_query($source)};
3435
like($@, qr/unexpected "{"/, "error : 2");
3436

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3437
test 'select() wrap option';
3438
$dbi = DBIx::Custom->connect;
3439
eval { $dbi->execute('drop table table1') };
3440
$dbi->execute($create_table1);
3441
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3442
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
3443
$rows = $dbi->select(
3444
    table => 'table1',
3445
    column => 'key1',
3446
    wrap => ['select * from (', ') as t where key1 = 1']
3447
)->all;
3448
is_deeply($rows, [{key1 => 1}]);
3449

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3450
eval {
3451
$dbi->select(
3452
    table => 'table1',
3453
    column => 'key1',
3454
    wrap => 'select * from ('
3455
)
3456
};
3457
like($@, qr/array/);
3458

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3459
test 'dbi method from model';
3460
$dbi = MyDBI9->connect;
3461
eval { $dbi->execute('drop table table1') };
3462
$dbi->execute($create_table1);
3463
$dbi->setup_model;
3464
$model = $dbi->model('table1');
3465
eval{$model->execute('select * from table1')};
3466
ok(!$@);
3467

            
3468
test 'column table option';
3469
$dbi = MyDBI9->connect;
3470
eval { $dbi->execute('drop table table1') };
3471
$dbi->execute($create_table1);
3472
eval { $dbi->execute('drop table table2') };
3473
$dbi->execute($create_table2);
3474
$dbi->setup_model;
3475
$dbi->execute('insert into table1 (key1, key2) values (1, 2);');
3476
$dbi->execute('insert into table2 (key1, key3) values (1, 4);');
3477
$model = $dbi->model('table1');
3478
$result = $model->select(
3479
    column => [
3480
        $model->column('table2', {alias => 'table2_alias'})
3481
    ],
3482
    where => {'table2_alias.key3' => 4}
3483
);
3484
is_deeply($result->one, 
3485
          {'table2_alias.key1' => 1, 'table2_alias.key3' => 4});
3486

            
3487
$dbi->separator('__');
3488
$result = $model->select(
3489
    column => [
3490
        $model->column('table2', {alias => 'table2_alias'})
3491
    ],
3492
    where => {'table2_alias.key3' => 4}
3493
);
3494
is_deeply($result->one, 
3495
          {'table2_alias__key1' => 1, 'table2_alias__key3' => 4});
3496

            
3497
$dbi->separator('-');
3498
$result = $model->select(
3499
    column => [
3500
        $model->column('table2', {alias => 'table2_alias'})
3501
    ],
3502
    where => {'table2_alias.key3' => 4}
3503
);
3504
is_deeply($result->one, 
3505
          {'table2_alias-key1' => 1, 'table2_alias-key3' => 4});
3506

            
3507
test 'create_model';
3508
$dbi = DBIx::Custom->connect;
3509
eval { $dbi->execute('drop table table1') };
3510
eval { $dbi->execute('drop table table2') };
3511
$dbi->execute($create_table1);
3512
$dbi->execute($create_table2);
3513

            
3514
$dbi->create_model(
3515
    table => 'table1',
3516
    join => [
3517
       'left outer join table2 on table1.key1 = table2.key1'
3518
    ],
3519
    primary_key => ['key1']
3520
);
3521
$model2 = $dbi->create_model(
3522
    table => 'table2'
3523
);
3524
$dbi->create_model(
3525
    table => 'table3',
3526
    filter => [
3527
        key1 => {in => sub { uc $_[0] }}
3528
    ]
3529
);
3530
$dbi->setup_model;
3531
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3532
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
3533
$model = $dbi->model('table1');
3534
$result = $model->select(
3535
    column => [$model->mycolumn, $model->column('table2')],
3536
    where => {'table1.key1' => 1}
3537
);
3538
is_deeply($result->one,
3539
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
3540
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
3541

            
3542
test 'model method';
3543
$dbi = DBIx::Custom->connect;
3544
eval { $dbi->execute('drop table table2') };
3545
$dbi->execute($create_table2);
3546
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
3547
$model = $dbi->create_model(
3548
    table => 'table2'
3549
);
3550
$model->method(foo => sub { shift->select(@_) });
3551
is_deeply($model->foo->one, {key1 => 1, key3 => 3});
3552

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3553
test 'update_param';
3554
$dbi = DBIx::Custom->connect;
3555
eval { $dbi->execute('drop table table1') };
3556
$dbi->execute($create_table1_2);
3557
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3558
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3559

            
3560
$param = {key2 => 11};
3561
$update_param = $dbi->update_param($param);
3562
$sql = <<"EOS";
3563
update table1 $update_param
3564
where key1 = 1
3565
EOS
3566
$dbi->execute($sql, param => $param);
3567
$result = $dbi->execute('select * from table1 order by key1;', table => 'table1');
3568
$rows   = $result->all;
3569
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
3570
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
3571
                  "basic");
3572

            
3573

            
3574
$dbi = DBIx::Custom->connect;
3575
eval { $dbi->execute('drop table table1') };
3576
$dbi->execute($create_table1_2);
3577
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3578
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3579

            
3580
$param = {key2 => 11, key3 => 33};
3581
$update_param = $dbi->update_param($param);
3582
$sql = <<"EOS";
3583
update table1 $update_param
3584
where key1 = 1
3585
EOS
3586
$dbi->execute($sql, param => $param);
3587
$result = $dbi->execute('select * from table1 order by key1;', table => 'table1');
3588
$rows   = $result->all;
3589
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 33, key4 => 4, key5 => 5},
3590
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
3591
                  "basic");
3592

            
3593
$dbi = DBIx::Custom->connect;
3594
eval { $dbi->execute('drop table table1') };
3595
$dbi->execute($create_table1_2);
3596
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3597
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3598

            
3599
$param = {key2 => 11, key3 => 33};
3600
$update_param = $dbi->update_param($param, {no_set => 1});
3601
$sql = <<"EOS";
3602
update table1 set $update_param
3603
where key1 = 1
3604
EOS
3605
$dbi->execute($sql, param => $param);
3606
$result = $dbi->execute('select * from table1 order by key1;', table => 'table1');
3607
$rows   = $result->all;
3608
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 33, key4 => 4, key5 => 5},
3609
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
3610
                  "update param no_set");
3611

            
3612
            
3613
eval { $dbi->update_param({";" => 1}) };
3614
like($@, qr/not safety/);
3615

            
3616

            
3617
test 'update_param';
3618
$dbi = DBIx::Custom->connect;
3619
eval { $dbi->execute('drop table table1') };
3620
$dbi->execute($create_table1_2);
3621
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3622
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3623

            
3624
$param = {key2 => 11};
3625
$update_param = $dbi->assign_param($param);
3626
$sql = <<"EOS";
3627
update table1 set $update_param
3628
where key1 = 1
3629
EOS
3630
$dbi->execute($sql, param => $param, table => 'table1');
3631
$result = $dbi->execute('select * from table1 order by key1;');
3632
$rows   = $result->all;
3633
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
3634
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
3635
                  "basic");
3636

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3637
test 'join';
3638
$dbi = DBIx::Custom->connect;
3639
eval { $dbi->execute('drop table table1') };
3640
$dbi->execute($create_table1);
3641
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3642
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
3643
eval { $dbi->execute('drop table table2') };
3644
$dbi->execute($create_table2);
3645
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
3646
eval { $dbi->execute('drop table table3') };
3647
$dbi->execute('create table table3 (key3 int, key4 int);');
3648
$dbi->insert(table => 'table3', param => {key3 => 5, key4 => 4});
3649
$rows = $dbi->select(
3650
    table => 'table1',
3651
    column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
3652
    where   => {'table1.key2' => 2},
3653
    join  => ['left outer join table2 on table1.key1 = table2.key1']
3654
)->all;
3655
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}]);
3656

            
3657
$rows = $dbi->select(
3658
    table => 'table1',
3659
    where   => {'key1' => 1},
3660
    join  => ['left outer join table2 on table1.key1 = table2.key1']
3661
)->all;
3662
is_deeply($rows, [{key1 => 1, key2 => 2}]);
3663

            
3664
eval {
3665
    $rows = $dbi->select(
3666
        table => 'table1',
3667
        column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
3668
        where   => {'table1.key2' => 2},
3669
        join  => {'table1.key1' => 'table2.key1'}
3670
    );
3671
};
3672
like ($@, qr/array/);
3673

            
3674
$rows = $dbi->select(
3675
    table => 'table1',
3676
    where   => {'key1' => 1},
3677
    join  => ['left outer join table2 on table1.key1 = table2.key1',
3678
              'left outer join table3 on table2.key3 = table3.key3']
3679
)->all;
3680
is_deeply($rows, [{key1 => 1, key2 => 2}]);
3681

            
3682
$rows = $dbi->select(
3683
    column => 'table3.key4 as table3__key4',
3684
    table => 'table1',
3685
    where   => {'table1.key1' => 1},
3686
    join  => ['left outer join table2 on table1.key1 = table2.key1',
3687
              'left outer join table3 on table2.key3 = table3.key3']
3688
)->all;
3689
is_deeply($rows, [{table3__key4 => 4}]);
3690

            
3691
$rows = $dbi->select(
3692
    column => 'table1.key1 as table1__key1',
3693
    table => 'table1',
3694
    where   => {'table3.key4' => 4},
3695
    join  => ['left outer join table2 on table1.key1 = table2.key1',
3696
              'left outer join table3 on table2.key3 = table3.key3']
3697
)->all;
3698
is_deeply($rows, [{table1__key1 => 1}]);
3699

            
3700
$dbi = DBIx::Custom->connect;
3701
eval { $dbi->execute('drop table table1') };
3702
$dbi->execute($create_table1);
3703
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3704
eval { $dbi->execute('drop table table2') };
3705
$dbi->execute($create_table2);
3706
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
3707
$rows = $dbi->select(
3708
    table => 'table1',
3709
    column => "${q}table1$p.${q}key1$p as ${q}table1_key1$p, ${q}table2$p.${q}key1$p as ${q}table2_key1$p, ${q}key2$p, ${q}key3$p",
3710
    where   => {'table1.key2' => 2},
3711
    join  => ["left outer join ${q}table2$p on ${q}table1$p.${q}key1$p = ${q}table2$p.${q}key1$p"],
3712
)->all;
3713
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}],
3714
          'quote');
3715

            
3716

            
3717
$dbi = DBIx::Custom->connect;
3718
eval { $dbi->execute('drop table table1') };
3719
$dbi->execute($create_table1);
3720
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3721
$sql = <<"EOS";
3722
left outer join (
3723
  select * from table1 as t1
3724
  where t1.key2 = (
3725
    select max(t2.key2) from table1 as t2
3726
    where t1.key1 = t2.key1
3727
  )
3728
) as latest_table1 on table1.key1 = latest_table1.key1
3729
EOS
3730
$join = [$sql];
3731
$rows = $dbi->select(
3732
    table => 'table1',
3733
    column => 'latest_table1.key1 as latest_table1__key1',
3734
    join  => $join
3735
)->all;
3736
is_deeply($rows, [{latest_table1__key1 => 1}]);
3737

            
3738
$dbi = DBIx::Custom->connect;
3739
eval { $dbi->execute('drop table table1') };
3740
eval { $dbi->execute('drop table table2') };
3741
$dbi->execute($create_table1);
3742
$dbi->execute($create_table2);
3743
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3744
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
3745
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
3746
$result = $dbi->select(
3747
    table => 'table1',
3748
    join => [
3749
        "left outer join table2 on table2.key2 = '4' and table1.key1 = table2.key1"
3750
    ]
3751
);
3752
is_deeply($result->all, [{key1 => 1, key2 => 2}]);
3753
$result = $dbi->select(
3754
    table => 'table1',
3755
    column => [{table2 => ['key3']}],
3756
    join => [
3757
        "left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1"
3758
    ]
3759
);
3760
is_deeply($result->all, [{'table2.key3' => 4}]);
3761
$result = $dbi->select(
3762
    table => 'table1',
3763
    column => [{table2 => ['key3']}],
3764
    join => [
3765
        "left outer join table2 on table1.key1 = table2.key1 and table2.key3 = '4'"
3766
    ]
3767
);
3768
is_deeply($result->all, [{'table2.key3' => 4}]);
3769

            
3770
$dbi = DBIx::Custom->connect;
3771
eval { $dbi->execute('drop table table1') };
3772
eval { $dbi->execute('drop table table2') };
3773
$dbi->execute($create_table1);
3774
$dbi->execute($create_table2);
3775
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3776
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
3777
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
3778
$result = $dbi->select(
3779
    table => 'table1',
3780
    column => [{table2 => ['key3']}],
3781
    join => [
3782
        {
3783
            clause => "left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1",
3784
            table => ['table1', 'table2']
3785
        }
3786
    ]
3787
);
3788
is_deeply($result->all, [{'table2.key3' => 4}]);
3789

            
test cleanup
Yuki Kimoto authored on 2011-08-10
3790

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3791

            
cleanup test
Yuki Kimoto authored on 2011-08-08
3792
1;