DBIx-Custom / t / common.t /
Newer Older
3352 lines | 109.117kb
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 => [
30
                'book',
31
                {class => 'Company', name => 'company'}
32
            ]
33
        );
34
    }
35

            
36
    package MyModel2::Base1;
37

            
38
    use strict;
39
    use warnings;
40

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

            
43
    package MyModel2::book;
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

            
58
    package MyModel2::Company;
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;
added common test executing ...
Yuki Kimoto authored on 2011-08-07
156

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

            
189
# Drop table
190
eval { $dbi->execute('drop table table1') };
191

            
192
# Create table
193
$dbi->execute($create_table1);
194
$model = $dbi->create_model(table => 'table1');
195
$model->insert({key1 => 1, key2 => 2});
196
is_deeply($model->select->all, [{key1 => 1, key2 => 2}]);
197

            
cleanup test
Yuki Kimoto authored on 2011-08-08
198
test 'DBIx::Custom::Result test';
199
$dbi->delete_all(table => 'table1');
200
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
201
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
202
$source = "select key1, key2 from table1";
203
$query = $dbi->create_query($source);
204
$result = $dbi->execute($query);
205

            
206
@rows = ();
207
while (my $row = $result->fetch) {
208
    push @rows, [@$row];
209
}
210
is_deeply(\@rows, [[1, 2], [3, 4]], "fetch");
211

            
212
$result = $dbi->execute($query);
213
@rows = ();
214
while (my $row = $result->fetch_hash) {
215
    push @rows, {%$row};
216
}
217
is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "fetch_hash");
218

            
219
$result = $dbi->execute($query);
220
$rows = $result->fetch_all;
221
is_deeply($rows, [[1, 2], [3, 4]], "fetch_all");
222

            
223
$result = $dbi->execute($query);
224
$rows = $result->fetch_hash_all;
225
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "all");
226

            
227
test 'Insert query return value';
228
$source = "insert into table1 {insert_param key1 key2}";
229
$query = $dbi->execute($source, {}, query => 1);
230
$ret_val = $dbi->execute($query, param => {key1 => 1, key2 => 2});
231
ok($ret_val);
232

            
233
test 'Direct query';
234
$dbi->delete_all(table => 'table1');
235
$insert_source = "insert into table1 {insert_param key1 key2}";
236
$dbi->execute($insert_source, param => {key1 => 1, key2 => 2});
237
$result = $dbi->execute('select * from table1;');
238
$rows = $result->all;
239
is_deeply($rows, [{key1 => 1, key2 => 2}]);
240

            
241
test 'Filter basic';
242
$dbi->delete_all(table => 'table1');
243
$dbi->register_filter(twice       => sub { $_[0] * 2}, 
244
                    three_times => sub { $_[0] * 3});
245

            
246
$insert_source  = "insert into table1 {insert_param key1 key2};";
247
$insert_query = $dbi->execute($insert_source, {}, query => 1);
248
$insert_query->filter({key1 => 'twice'});
249
$dbi->execute($insert_query, param => {key1 => 1, key2 => 2});
250
$result = $dbi->execute('select * from table1;');
251
$rows = $result->filter({key2 => 'three_times'})->all;
252
is_deeply($rows, [{key1 => 2, key2 => 6}], "filter fetch_filter");
253

            
254
test 'Filter in';
255
$dbi->delete_all(table => 'table1');
256
$insert_source  = "insert into table1 {insert_param key1 key2};";
257
$insert_query = $dbi->execute($insert_source, {}, query => 1);
258
$dbi->execute($insert_query, param => {key1 => 2, key2 => 4});
259
$select_source = "select * from table1 where {in table1.key1 2} and {in table1.key2 2}";
260
$select_query = $dbi->execute($select_source,{}, query => 1);
261
$select_query->filter({'table1.key1' => 'twice'});
262
$result = $dbi->execute($select_query, param => {'table1.key1' => [1,5], 'table1.key2' => [2,4]});
263
$rows = $result->all;
264
is_deeply($rows, [{key1 => 2, key2 => 4}], "filter");
265

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

            
272
$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";
273
$query = $dbi->execute($source, {}, query => 1);
274
$result = $dbi->execute($query, param => {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
275
$rows = $result->all;
276
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");
277

            
278
$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";
279
$query = $dbi->execute($source, {}, query => 1);
280
$result = $dbi->execute($query, {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
281
$rows = $result->all;
282
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");
283

            
284
$source = "select * from table1 where {<= key1} and {like key2};";
285
$query = $dbi->execute($source, {}, query => 1);
286
$result = $dbi->execute($query, param => {key1 => 1, key2 => '%2%'});
287
$rows = $result->all;
288
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag2");
289

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

            
296
$source = "select * from table1 where {in key1 2};";
297
$query = $dbi->execute($source, {}, query => 1);
298
$result = $dbi->execute($query, param => {key1 => [9, 1]});
299
$rows = $result->all;
300
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic");
301

            
302
test 'DBIx::Custom::SQLTemplate insert tag';
303
$dbi->delete_all(table => 'table1');
304
$insert_source = 'insert into table1 {insert_param key1 key2 key3 key4 key5}';
305
$dbi->execute($insert_source, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
306

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

            
311
test 'DBIx::Custom::SQLTemplate update tag';
312
$dbi->delete_all(table => 'table1');
313
$insert_source = "insert into table1 {insert_param key1 key2 key3 key4 key5}";
314
$dbi->execute($insert_source, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
315
$dbi->execute($insert_source, param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
316

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

            
320
$result = $dbi->execute('select * from table1 order by key1;');
321
$rows = $result->all;
322
is_deeply($rows, [{key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5},
323
                  {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}], "basic");
324

            
cleanup test
Yuki Kimoto authored on 2011-08-08
325
test 'Named placeholder';
326
$dbi->execute('drop table table1');
327
$dbi->execute($create_table1_2);
328
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
329
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
330

            
331
$source = "select * from table1 where key1 = :key1 and key2 = :key2";
332
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
333
$rows = $result->all;
334
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
335

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

            
341
$source = "select * from table1 where key1 = :key1 or key1 = :key1";
342
$result = $dbi->execute($source, param => {key1 => [1, 2]});
343
$rows = $result->all;
344
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
345

            
346
$source = "select * from table1 where key1 = :table1.key1 and key2 = :table1.key2";
347
$result = $dbi->execute(
348
    $source,
349
    param => {'table1.key1' => 1, 'table1.key2' => 1},
350
    filter => {'table1.key2' => sub { $_[0] * 2 }}
351
);
352
$rows = $result->all;
353
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
354

            
355
$dbi->execute('drop table table1');
356
$dbi->execute($create_table1);
357
$dbi->insert(table => 'table1', param => {key1 => '2011-10-14 12:19:18', key2 => 2});
358
$source = "select * from table1 where key1 = '2011-10-14 12:19:18' and key2 = :key2";
359
$result = $dbi->execute(
360
    $source,
361
    param => {'key2' => 2},
362
);
363

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

            
367
$dbi->delete_all(table => 'table1');
368
$dbi->insert(table => 'table1', param => {key1 => 'a:b c:d', key2 => 2});
369
$source = "select * from table1 where key1 = 'a\\:b c\\:d' and key2 = :key2";
370
$result = $dbi->execute(
371
    $source,
372
    param => {'key2' => 2},
373
);
374
$rows = $result->all;
375
is_deeply($rows, [{key1 => 'a:b c:d', key2 => 2}]);
376

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-10
384
test 'insert';
cleanup test
Yuki Kimoto authored on 2011-08-10
385
eval { $dbi->execute('drop table table1') };
386
$dbi->execute($create_table1);
cleanup test
Yuki Kimoto authored on 2011-08-10
387
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
388
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
389
$result = $dbi->execute('select * from table1;');
390
$rows   = $result->all;
391
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
392

            
393
$dbi->execute('delete from table1');
394
$dbi->register_filter(
395
    twice       => sub { $_[0] * 2 },
396
    three_times => sub { $_[0] * 3 }
397
);
398
$dbi->default_bind_filter('twice');
399
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => 'three_times'});
400
$result = $dbi->execute('select * from table1;');
401
$rows   = $result->all;
402
is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
403
$dbi->default_bind_filter(undef);
404

            
cleanup test
Yuki Kimoto authored on 2011-08-10
405
$dbi->execute('drop table table1');
406
$dbi->execute($create_table1);
cleanup test
Yuki Kimoto authored on 2011-08-10
407
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, append => '   ');
408
$rows = $dbi->select(table => 'table1')->all;
409
is_deeply($rows, [{key1 => 1, key2 => 2}], 'insert append');
410

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

            
414
eval{$dbi->insert(table => 'table', param => {';' => 1})};
415
like($@, qr/safety/);
416

            
cleanup test
Yuki Kimoto authored on 2011-08-10
417
eval { $dbi->execute("drop table ${q}table$p") };
test cleanup
Yuki Kimoto authored on 2011-08-10
418
$dbi->execute($create_table_reserved);
cleanup test
Yuki Kimoto authored on 2011-08-10
419
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
420
$dbi->insert(table => 'table', param => {select => 1});
cleanup test
Yuki Kimoto authored on 2011-08-10
421
$result = $dbi->execute("select * from ${q}table$p");
cleanup test
Yuki Kimoto authored on 2011-08-10
422
$rows   = $result->all;
test cleanup
Yuki Kimoto authored on 2011-08-10
423
is_deeply($rows, [{select => 2, update => undef}], "reserved word");
cleanup test
Yuki Kimoto authored on 2011-08-10
424

            
cleanup test
Yuki Kimoto authored on 2011-08-10
425
eval { $dbi->execute('drop table table1') };
cleanup test
Yuki Kimoto authored on 2011-08-10
426
$dbi->execute($create_table1);
427
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
428
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
429
$result = $dbi->execute('select * from table1;');
430
$rows   = $result->all;
431
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
432

            
cleanup test
Yuki Kimoto authored on 2011-08-10
433
eval { $dbi->execute('drop table table1') };
cleanup test
Yuki Kimoto authored on 2011-08-10
434
$dbi->execute($create_table1);
435
$dbi->insert(table => 'table1', param => {key1 => \"'1'", key2 => 2});
436
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
437
$result = $dbi->execute('select * from table1;');
438
$rows   = $result->all;
439
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
440

            
test cleanup
Yuki Kimoto authored on 2011-08-10
441
test 'update';
442
eval { $dbi->execute('drop table table1') };
443
$dbi->execute($create_table1_2);
444
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
445
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
446
$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1});
test cleanup
Yuki Kimoto authored on 2011-08-10
447
$result = $dbi->execute('select * from table1 order by key1;');
test cleanup
Yuki Kimoto authored on 2011-08-10
448
$rows   = $result->all;
449
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
450
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
451
                  "basic");
452
                  
453
$dbi->execute("delete from table1");
454
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
455
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
456
$dbi->update(table => 'table1', param => {key2 => 12}, where => {key2 => 2, key3 => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
457
$result = $dbi->execute('select * from table1 order by key1;');
test cleanup
Yuki Kimoto authored on 2011-08-10
458
$rows   = $result->all;
459
is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
460
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
461
                  "update key same as search key");
462

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

            
470
$dbi->execute("delete from table1");
471
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
472
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
473
$dbi->register_filter(twice => sub { $_[0] * 2 });
474
$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1},
475
              filter => {key2 => sub { $_[0] * 2 }});
test cleanup
Yuki Kimoto authored on 2011-08-10
476
$result = $dbi->execute('select * from table1 order by key1;');
test cleanup
Yuki Kimoto authored on 2011-08-10
477
$rows   = $result->all;
478
is_deeply($rows, [{key1 => 1, key2 => 22, key3 => 3, key4 => 4, key5 => 5},
479
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
480
                  "filter");
481

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

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

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

            
490
eval { $dbi->execute('drop table table1') };
491
$dbi->execute($create_table1);
492
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
493
$where = $dbi->where;
494
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
495
$where->param({key1 => 1, key2 => 2});
496
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
497
$result = $dbi->select(table => 'table1');
498
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
499

            
500
eval { $dbi->execute('drop table table1') };
501
$dbi->execute($create_table1);
502
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
503
$dbi->update(
504
    table => 'table1',
505
    param => {key1 => 3},
506
    where => [
507
        ['and', 'key1 = :key1', 'key2 = :key2'],
508
        {key1 => 1, key2 => 2}
509
    ]
510
);
511
$result = $dbi->select(table => 'table1');
512
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
513

            
514
eval { $dbi->execute('drop table table1') };
515
$dbi->execute($create_table1);
516
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
517
$where = $dbi->where;
518
$where->clause(['and', 'key2 = :key2']);
519
$where->param({key2 => 2});
520
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
521
$result = $dbi->select(table => 'table1');
522
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
523

            
524
eval{$dbi->update(table => 'table1', param => {';' => 1})};
525
like($@, qr/safety/);
526

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

            
530
eval { $dbi->execute('drop table table1') };
531
eval { $dbi->execute("drop table ${q}table$p") };
test cleanup
Yuki Kimoto authored on 2011-08-10
532
$dbi->execute($create_table_reserved);
test cleanup
Yuki Kimoto authored on 2011-08-10
533
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
534
$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
535
$dbi->insert(table => 'table', param => {select => 1});
536
$dbi->update(table => 'table', where => {select => 1}, param => {update => 2});
537
$result = $dbi->execute("select * from ${q}table$p");
538
$rows   = $result->all;
539
is_deeply($rows, [{select => 2, update => 6}], "reserved word");
540

            
541
eval {$dbi->update_all(table => 'table', param => {';' => 2}) };
542
like($@, qr/safety/);
543

            
544
eval { $dbi->execute("drop table ${q}table$p") };
test cleanup
Yuki Kimoto authored on 2011-08-10
545
$dbi->execute($create_table_reserved);
test cleanup
Yuki Kimoto authored on 2011-08-10
546
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
547
$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
548
$dbi->insert(table => 'table', param => {select => 1});
549
$dbi->update(table => 'table', where => {'table.select' => 1}, param => {update => 2});
550
$result = $dbi->execute("select * from ${q}table$p");
551
$rows   = $result->all;
552
is_deeply($rows, [{select => 2, update => 6}], "reserved word");
553

            
554
eval { $dbi->execute('drop table table1') };
555
$dbi->execute($create_table1_2);
556
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
557
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
558
$dbi->update({key2 => 11}, table => 'table1', where => {key1 => 1});
test cleanup
Yuki Kimoto authored on 2011-08-10
559
$result = $dbi->execute('select * from table1 order by key1;');
test cleanup
Yuki Kimoto authored on 2011-08-10
560
$rows   = $result->all;
561
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
562
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
563
                  "basic");
564

            
565
eval { $dbi->execute('drop table table1') };
566
$dbi->execute($create_table1_2);
567
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
568
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
569
$dbi->update(table => 'table1', param => {key2 => \"'11'"}, where => {key1 => 1});
test cleanup
Yuki Kimoto authored on 2011-08-10
570
$result = $dbi->execute('select * from table1 order by key1;');
test cleanup
Yuki Kimoto authored on 2011-08-10
571
$rows   = $result->all;
572
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
573
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
574
                  "basic");
575

            
576
test 'update_all';
577
eval { $dbi->execute('drop table table1') };
578
$dbi->execute($create_table1_2);
579
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
580
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
581
$dbi->register_filter(twice => sub { $_[0] * 2 });
582
$dbi->update_all(table => 'table1', param => {key2 => 10}, filter => {key2 => 'twice'});
583
$result = $dbi->execute('select * from table1;');
584
$rows   = $result->all;
585
is_deeply($rows, [{key1 => 1, key2 => 20, key3 => 3, key4 => 4, key5 => 5},
586
                  {key1 => 6, key2 => 20, key3 => 8, key4 => 9, key5 => 10}],
587
                  "filter");
588

            
589

            
590
test 'delete';
591
eval { $dbi->execute('drop table table1') };
592
$dbi->execute($create_table1);
593
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
594
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
595
$dbi->delete(table => 'table1', where => {key1 => 1});
596
$result = $dbi->execute('select * from table1;');
597
$rows   = $result->all;
598
is_deeply($rows, [{key1 => 3, key2 => 4}], "basic");
599

            
600
$dbi->execute("delete from table1;");
601
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
602
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
603
$dbi->register_filter(twice => sub { $_[0] * 2 });
604
$dbi->delete(table => 'table1', where => {key2 => 1}, filter => {key2 => 'twice'});
605
$result = $dbi->execute('select * from table1;');
606
$rows   = $result->all;
607
is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
608

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

            
611
$dbi->delete_all(table => 'table1');
612
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
613
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
614
$dbi->delete(table => 'table1', where => {key1 => 1, key2 => 2});
615
$rows = $dbi->select(table => 'table1')->all;
616
is_deeply($rows, [{key1 => 3, key2 => 4}], "delete multi key");
617

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

            
621
eval { $dbi->execute('drop table table1') };
622
$dbi->execute($create_table1);
623
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
624
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
625
$where = $dbi->where;
626
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
627
$where->param({ke1 => 1, key2 => 2});
628
$dbi->delete(table => 'table1', where => $where);
629
$result = $dbi->select(table => 'table1');
630
is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
631

            
632
eval { $dbi->execute('drop table table1') };
633
$dbi->execute($create_table1);
634
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
635
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
636
$dbi->delete(
637
    table => 'table1',
638
    where => [
639
        ['and', 'key1 = :key1', 'key2 = :key2'],
640
        {ke1 => 1, key2 => 2}
641
    ]
642
);
643
$result = $dbi->select(table => 'table1');
644
is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
645

            
646
eval { $dbi->execute('drop table table1') };
test cleanup
Yuki Kimoto authored on 2011-08-10
647
$dbi->execute($create_table1);
test cleanup
Yuki Kimoto authored on 2011-08-10
648
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
649
$dbi->delete(table => 'table1', where => {key1 => 1}, prefix => '    ');
650
$result = $dbi->execute('select * from table1;');
651
$rows   = $result->all;
652
is_deeply($rows, [], "basic");
653

            
654
test 'delete error';
655
eval { $dbi->execute('drop table table1') };
656
$dbi->execute($create_table1);
657
eval{$dbi->delete(table => 'table1')};
658
like($@, qr/"where" must be specified/,
659
         "where key-value pairs not specified");
660

            
661
eval{$dbi->delete(table => 'table1', where => {';' => 1})};
662
like($@, qr/safety/);
663

            
664
$dbi = DBIx::Custom->connect;
665
eval { $dbi->execute("drop table ${q}table$p") };
test cleanup
Yuki Kimoto authored on 2011-08-10
666
$dbi->execute($create_table_reserved);
test cleanup
Yuki Kimoto authored on 2011-08-10
667
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
668
$dbi->insert(table => 'table', param => {select => 1});
669
$dbi->delete(table => 'table', where => {select => 1});
670
$result = $dbi->execute("select * from ${q}table$p");
671
$rows   = $result->all;
672
is_deeply($rows, [], "reserved word");
673

            
674
test 'delete_all';
675
eval { $dbi->execute('drop table table1') };
676
$dbi->execute($create_table1);
677
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
678
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
679
$dbi->delete_all(table => 'table1');
680
$result = $dbi->execute('select * from table1;');
681
$rows   = $result->all;
682
is_deeply($rows, [], "basic");
683

            
684

            
685
test 'select';
686
eval { $dbi->execute('drop table table1') };
687
$dbi->execute($create_table1);
688
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
689
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
690
$rows = $dbi->select(table => 'table1')->all;
691
is_deeply($rows, [{key1 => 1, key2 => 2},
692
                  {key1 => 3, key2 => 4}], "table");
693

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

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

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

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

            
706
$dbi->register_filter(decrement => sub { $_[0] - 1 });
707
$rows = $dbi->select(table => 'table1', where => {key1 => 2}, filter => {key1 => 'decrement'})
708
            ->all;
709
is_deeply($rows, [{key1 => 1, key2 => 2}], "filter");
710

            
test cleanup
Yuki Kimoto authored on 2011-08-10
711
eval { $dbi->execute("drop table table2") };
712
$dbi->execute($create_table2);
test cleanup
Yuki Kimoto authored on 2011-08-10
713
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
714
$rows = $dbi->select(
715
    table => [qw/table1 table2/],
716
    column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
717
    where   => {'table1.key2' => 2},
718
    relation  => {'table1.key1' => 'table2.key1'}
719
)->all;
720
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : exists where");
721

            
722
$rows = $dbi->select(
723
    table => [qw/table1 table2/],
724
    column => ['table1.key1 as table1_key1', 'table2.key1 as table2_key1', 'key2', 'key3'],
725
    relation  => {'table1.key1' => 'table2.key1'}
726
)->all;
727
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : no exists where");
728

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

            
732
$dbi = DBIx::Custom->connect;
test cleanup
Yuki Kimoto authored on 2011-08-10
733
eval { $dbi->execute("drop table ${q}table$p") };
734
$dbi->execute($create_table_reserved);
test cleanup
Yuki Kimoto authored on 2011-08-10
735
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
736
$dbi->insert(table => 'table', param => {select => 1, update => 2});
737
$result = $dbi->select(table => 'table', where => {select => 1});
738
$rows   = $result->all;
739
is_deeply($rows, [{select => 2, update => 2}], "reserved word");
740

            
741
test 'fetch filter';
742
eval { $dbi->execute('drop table table1') };
743
$dbi->register_filter(
744
    twice       => sub { $_[0] * 2 },
745
    three_times => sub { $_[0] * 3 }
746
);
747
$dbi->default_fetch_filter('twice');
748
$dbi->execute($create_table1);
749
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
750
$result = $dbi->select(table => 'table1');
751
$result->filter({key1 => 'three_times'});
752
$row = $result->one;
753
is_deeply($row, {key1 => 3, key2 => 4}, "default_fetch_filter and filter");
754

            
755
test 'filters';
756
$dbi = DBIx::Custom->new;
757

            
758
is($dbi->filters->{decode_utf8}->(encode_utf8('あ')),
759
   'あ', "decode_utf8");
760

            
761
is($dbi->filters->{encode_utf8}->('あ'),
762
   encode_utf8('あ'), "encode_utf8");
763

            
cleanup test
Yuki Kimoto authored on 2011-08-10
764
test 'transaction1';
test cleanup
Yuki Kimoto authored on 2011-08-10
765
$dbi = DBIx::Custom->connect;
766
eval { $dbi->execute('drop table table1') };
767
$dbi->execute($create_table1);
768
$dbi->dbh->begin_work;
769
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
770
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
771
$dbi->dbh->commit;
772
$result = $dbi->select(table => 'table1');
773
is_deeply(scalar $result->all, [{key1 => 1, key2 => 2}, {key1 => 2, key2 => 3}],
774
          "commit");
775

            
776
$dbi = DBIx::Custom->connect;
777
eval { $dbi->execute('drop table table1') };
778
$dbi->execute($create_table1);
779
$dbi->dbh->begin_work(0);
780
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
781
$dbi->dbh->rollback;
782

            
783
$result = $dbi->select(table => 'table1');
784
ok(! $result->fetch_first, "rollback");
785

            
786
test 'execute';
787
eval { $dbi->execute('drop table table1') };
788
$dbi->execute($create_table1);
789
{
790
    local $Carp::Verbose = 0;
791
    eval{$dbi->execute('select * frm table1')};
792
    like($@, qr/\Qselect * frm table1;/, "fail prepare");
793
    like($@, qr/\.t /, "fail : not verbose");
794
}
795
{
796
    local $Carp::Verbose = 1;
797
    eval{$dbi->execute('select * frm table1')};
798
    like($@, qr/Custom.*\.t /s, "fail : verbose");
799
}
800

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

            
804
$query = $dbi->execute('select * from table1 where key1 = :key1', {}, query => 1);
805
$dbi->dbh->disconnect;
806
eval{$dbi->execute($query, param => {key1 => {a => 1}})};
807
ok($@, "execute fail");
808

            
809
{
810
    local $Carp::Verbose = 0;
811
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
812
    like($@, qr/\Q.t /, "caller spec : not vebose");
813
}
814
{
815
    local $Carp::Verbose = 1;
816
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
817
    like($@, qr/QueryBuilder.*\.t /s, "caller spec : not vebose");
818
}
819

            
820

            
cleanup test
Yuki Kimoto authored on 2011-08-10
821
test 'transaction2';
test cleanup
Yuki Kimoto authored on 2011-08-10
822
$dbi = DBIx::Custom->connect;
823
eval { $dbi->execute('drop table table1') };
824
$dbi->execute($create_table1);
825

            
826
$dbi->begin_work;
827

            
828
eval {
829
    $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
830
    die "Error";
831
    $dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
832
};
833

            
834
$dbi->rollback if $@;
835

            
836
$result = $dbi->select(table => 'table1');
837
$rows = $result->all;
838
is_deeply($rows, [], "rollback");
839

            
840
$dbi->begin_work;
841

            
842
eval {
843
    $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
844
    $dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
845
};
846

            
847
$dbi->commit unless $@;
848

            
849
$result = $dbi->select(table => 'table1');
850
$rows = $result->all;
851
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "commit");
852

            
853
$dbi->dbh->{AutoCommit} = 0;
854
eval{ $dbi->begin_work };
855
ok($@, "exception");
856
$dbi->dbh->{AutoCommit} = 1;
added EXPERIMENTAL DBIx::Cus...
Yuki Kimoto authored on 2011-08-09
857

            
test cleanup
Yuki Kimoto authored on 2011-08-10
858
test 'cache';
859
eval { $dbi->execute('drop table table1') };
860
$dbi->cache(1);
861
$dbi->execute($create_table1);
862
$source = 'select * from table1 where key1 = :key1 and key2 = :key2;';
863
$dbi->execute($source, {}, query => 1);
864
is_deeply($dbi->{_cached}->{$source}, 
865
          {sql => "select * from table1 where key1 = ? and key2 = ?;", columns => ['key1', 'key2'], tables => []}, "cache");
866

            
867
eval { $dbi->execute('drop table table1') };
868
$dbi->execute($create_table1);
869
$dbi->{_cached} = {};
870
$dbi->cache(0);
871
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
872
is(scalar keys %{$dbi->{_cached}}, 0, 'not cache');
873

            
874
test 'execute';
875
eval { $dbi->execute('drop table table1') };
876
$dbi->execute($create_table1);
877
{
878
    local $Carp::Verbose = 0;
879
    eval{$dbi->execute('select * frm table1')};
880
    like($@, qr/\Qselect * frm table1;/, "fail prepare");
881
    like($@, qr/\.t /, "fail : not verbose");
882
}
883
{
884
    local $Carp::Verbose = 1;
885
    eval{$dbi->execute('select * frm table1')};
886
    like($@, qr/Custom.*\.t /s, "fail : verbose");
887
}
888

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

            
892
$query = $dbi->execute('select * from table1 where key1 = :key1', {}, query => 1);
893
$dbi->dbh->disconnect;
894
eval{$dbi->execute($query, param => {key1 => {a => 1}})};
895
ok($@, "execute fail");
896

            
897
{
898
    local $Carp::Verbose = 0;
899
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
900
    like($@, qr/\Q.t /, "caller spec : not vebose");
901
}
902
{
903
    local $Carp::Verbose = 1;
904
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
905
    like($@, qr/QueryBuilder.*\.t /s, "caller spec : not vebose");
906
}
907

            
908
test 'method';
909
$dbi->method(
910
    one => sub { 1 }
911
);
912
$dbi->method(
913
    two => sub { 2 }
914
);
915
$dbi->method({
916
    twice => sub {
917
        my $self = shift;
918
        return $_[0] * 2;
919
    }
920
});
921

            
922
is($dbi->one, 1, "first");
923
is($dbi->two, 2, "second");
924
is($dbi->twice(5), 10 , "second");
925

            
926
eval {$dbi->XXXXXX};
927
ok($@, "not exists");
928

            
929
test 'out filter';
930
$dbi = DBIx::Custom->connect;
931
eval { $dbi->execute('drop table table1') };
932
$dbi->execute($create_table1);
933
$dbi->register_filter(twice => sub { $_[0] * 2 });
934
$dbi->register_filter(three_times => sub { $_[0] * 3});
935
$dbi->apply_filter(
936
    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 
937
              'key2' => {out => 'three_times', in => 'twice'});
938
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
939
$result = $dbi->execute('select * from table1;');
940
$row   = $result->fetch_hash_first;
941
is_deeply($row, {key1 => 2, key2 => 6}, "insert");
942
$result = $dbi->select(table => 'table1');
943
$row   = $result->one;
944
is_deeply($row, {key1 => 6, key2 => 12}, "insert");
945

            
946
$dbi = DBIx::Custom->connect;
947
eval { $dbi->execute('drop table table1') };
948
$dbi->execute($create_table1);
949
$dbi->register_filter(twice => sub { $_[0] * 2 });
950
$dbi->register_filter(three_times => sub { $_[0] * 3});
951
$dbi->apply_filter(
952
    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 
953
              'key2' => {out => 'three_times', in => 'twice'});
954
$dbi->apply_filter(
955
    'table1', 'key1' => {out => undef}
956
); 
957
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
958
$result = $dbi->execute('select * from table1;');
959
$row   = $result->one;
960
is_deeply($row, {key1 => 1, key2 => 6}, "insert");
961

            
962
$dbi = DBIx::Custom->connect;
963
eval { $dbi->execute('drop table table1') };
964
$dbi->execute($create_table1);
965
$dbi->register_filter(twice => sub { $_[0] * 2 });
966
$dbi->apply_filter(
967
    'table1', 'key1' => {out => 'twice', in => 'twice'}
968
);
969
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => undef});
970
$dbi->update(table => 'table1', param => {key1 => 2}, where => {key2 => 2});
971
$result = $dbi->execute('select * from table1;');
972
$row   = $result->one;
973
is_deeply($row, {key1 => 4, key2 => 2}, "update");
974

            
975
$dbi = DBIx::Custom->connect;
976
eval { $dbi->execute('drop table table1') };
977
$dbi->execute($create_table1);
978
$dbi->register_filter(twice => sub { $_[0] * 2 });
979
$dbi->apply_filter(
980
    'table1', 'key1' => {out => 'twice', in => 'twice'}
981
);
982
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1=> undef});
983
$dbi->delete(table => 'table1', where => {key1 => 1});
984
$result = $dbi->execute('select * from table1;');
985
$rows   = $result->all;
986
is_deeply($rows, [], "delete");
987

            
988
$dbi = DBIx::Custom->connect;
989
eval { $dbi->execute('drop table table1') };
990
$dbi->execute($create_table1);
991
$dbi->register_filter(twice => sub { $_[0] * 2 });
992
$dbi->apply_filter(
993
    'table1', 'key1' => {out => 'twice', in => 'twice'}
994
);
995
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
996
$result = $dbi->select(table => 'table1', where => {key1 => 1});
997
$result->filter({'key2' => 'twice'});
998
$rows   = $result->all;
999
is_deeply($rows, [{key1 => 4, key2 => 4}], "select");
1000

            
1001
$dbi = DBIx::Custom->connect;
1002
eval { $dbi->execute('drop table table1') };
1003
$dbi->execute($create_table1);
1004
$dbi->register_filter(twice => sub { $_[0] * 2 });
1005
$dbi->apply_filter(
1006
    'table1', 'key1' => {out => 'twice', in => 'twice'}
1007
);
1008
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
1009
$result = $dbi->execute("select * from table1 where key1 = :key1 and key2 = :key2;",
1010
                        param => {key1 => 1, key2 => 2},
1011
                        table => ['table1']);
1012
$rows   = $result->all;
1013
is_deeply($rows, [{key1 => 4, key2 => 2}], "execute");
1014

            
1015
$dbi = DBIx::Custom->connect;
1016
eval { $dbi->execute('drop table table1') };
1017
$dbi->execute($create_table1);
1018
$dbi->register_filter(twice => sub { $_[0] * 2 });
1019
$dbi->apply_filter(
1020
    'table1', 'key1' => {out => 'twice', in => 'twice'}
1021
);
1022
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
1023
$result = $dbi->execute("select * from {table table1} where key1 = :key1 and key2 = :key2;",
1024
                        param => {key1 => 1, key2 => 2});
1025
$rows   = $result->all;
1026
is_deeply($rows, [{key1 => 4, key2 => 2}], "execute table tag");
1027

            
1028
$dbi = DBIx::Custom->connect;
1029
eval { $dbi->execute('drop table table1') };
1030
eval { $dbi->execute('drop table table2') };
1031
$dbi->execute($create_table1);
1032
$dbi->execute($create_table2);
1033
$dbi->register_filter(twice => sub { $_[0] * 2 });
1034
$dbi->register_filter(three_times => sub { $_[0] * 3 });
1035
$dbi->apply_filter(
1036
    'table1', 'key2' => {out => 'twice', in => 'twice'}
1037
);
1038
$dbi->apply_filter(
1039
    'table2', 'key3' => {out => 'three_times', in => 'three_times'}
1040
);
1041
$dbi->insert(table => 'table1', param => {key1 => 5, key2 => 2}, filter => {key2 => undef});
1042
$dbi->insert(table => 'table2', param => {key1 => 5, key3 => 6}, filter => {key3 => undef});
1043
$result = $dbi->select(
1044
     table => ['table1', 'table2'],
1045
     column => ['key2', 'key3'],
1046
     where => {'table1.key2' => 1, 'table2.key3' => 2}, relation => {'table1.key1' => 'table2.key1'});
1047

            
1048
$result->filter({'key2' => 'twice'});
1049
$rows   = $result->all;
1050
is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join");
1051

            
1052
$result = $dbi->select(
1053
     table => ['table1', 'table2'],
1054
     column => ['key2', 'key3'],
1055
     where => {'key2' => 1, 'key3' => 2}, relation => {'table1.key1' => 'table2.key1'});
1056

            
1057
$result->filter({'key2' => 'twice'});
1058
$rows   = $result->all;
1059
is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join : omit");
1060

            
1061
test 'each_column';
1062
$dbi = DBIx::Custom->connect;
1063
eval { $dbi->execute("drop table ${q}table$p") };
1064
eval { $dbi->execute('drop table table1') };
1065
eval { $dbi->execute('drop table table2') };
test cleranup
Yuki Kimoto authored on 2011-08-10
1066
eval { $dbi->execute('drop table table3') };
test cleanup
Yuki Kimoto authored on 2011-08-10
1067
$dbi->execute($create_table1_type);
1068
$dbi->execute($create_table2);
1069

            
1070
$infos = [];
1071
$dbi->each_column(sub {
1072
    my ($self, $table, $column, $cinfo) = @_;
1073
    
1074
    if ($table =~ /^table\d/) {
1075
         my $info = [$table, $column, $cinfo->{COLUMN_NAME}];
1076
         push @$infos, $info;
1077
    }
1078
});
1079
$infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
1080
is_deeply($infos, 
1081
    [
1082
        ['table1', 'key1', 'key1'],
1083
        ['table1', 'key2', 'key2'],
1084
        ['table2', 'key1', 'key1'],
1085
        ['table2', 'key3', 'key3']
1086
    ]
1087
    
1088
);
1089
test 'each_table';
1090
$dbi = DBIx::Custom->connect;
1091
eval { $dbi->execute('drop table table1') };
1092
eval { $dbi->execute('drop table table2') };
1093
$dbi->execute($create_table2);
1094
$dbi->execute($create_table1_type);
1095

            
1096
$infos = [];
1097
$dbi->each_table(sub {
1098
    my ($self, $table, $table_info) = @_;
1099
    
1100
    if ($table =~ /^table\d/) {
1101
         my $info = [$table, $table_info->{TABLE_NAME}];
1102
         push @$infos, $info;
1103
    }
1104
});
1105
$infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
1106
is_deeply($infos, 
1107
    [
1108
        ['table1', 'table1'],
1109
        ['table2', 'table2'],
1110
    ]
1111
);
1112

            
1113
test 'limit';
1114
$dbi = DBIx::Custom->connect;
1115
eval { $dbi->execute('drop table table1') };
1116
$dbi->execute($create_table1);
1117
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1118
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 4});
1119
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 6});
1120
$dbi->register_tag(
1121
    limit => sub {
1122
        my ($count, $offset) = @_;
1123
        
1124
        my $s = '';
1125
        $s .= "limit $count";
1126
        $s .= " offset $offset" if defined $offset;
1127
        
1128
        return [$s, []];
1129
    }
1130
);
1131
$rows = $dbi->select(
1132
  table => 'table1',
1133
  where => {key1 => 1},
1134
  append => "order by key2 {limit 1 0}"
1135
)->all;
1136
is_deeply($rows, [{key1 => 1, key2 => 2}]);
1137
$rows = $dbi->select(
1138
  table => 'table1',
1139
  where => {key1 => 1},
1140
  append => "order by key2 {limit 2 1}"
1141
)->all;
1142
is_deeply($rows, [{key1 => 1, key2 => 4},{key1 => 1, key2 => 6}]);
1143
$rows = $dbi->select(
1144
  table => 'table1',
1145
  where => {key1 => 1},
1146
  append => "order by key2 {limit 1}"
1147
)->all;
1148
is_deeply($rows, [{key1 => 1, key2 => 2}]);
1149

            
1150
test 'connect super';
test cleanup
Yuki Kimoto authored on 2011-08-10
1151
$dbi = DBIx::Custom->connect;
test cleanup
Yuki Kimoto authored on 2011-08-10
1152
eval { $dbi->execute('drop table table1') };
1153
$dbi->execute($create_table1);
1154
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1155
is($dbi->select(table => 'table1')->one->{key1}, 1);
1156

            
test cleanup
Yuki Kimoto authored on 2011-08-10
1157
$dbi = DBIx::Custom->new;
test cleanup
Yuki Kimoto authored on 2011-08-10
1158
$dbi->connect;
1159
eval { $dbi->execute('drop table table1') };
1160
$dbi->execute($create_table1);
1161
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1162
is($dbi->select(table => 'table1')->one->{key1}, 1);
1163

            
test cleanup
Yuki Kimoto authored on 2011-08-10
1164
$dbi = DBIx::Custom->connect;
test cleanup
Yuki Kimoto authored on 2011-08-10
1165
eval { $dbi->execute('drop table table1') };
1166
$dbi->execute($create_table1);
1167
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1168
is($dbi->select(table => 'table1')->one->{key1}, 1);
1169

            
1170
test 'end_filter';
1171
$dbi = DBIx::Custom->connect;
1172
eval { $dbi->execute('drop table table1') };
1173
$dbi->execute($create_table1);
1174
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1175
$result = $dbi->select(table => 'table1');
1176
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1177
$result->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 });
1178
$row = $result->fetch_first;
1179
is_deeply($row, [6, 40]);
1180

            
1181
$dbi = DBIx::Custom->connect;
1182
eval { $dbi->execute('drop table table1') };
1183
$dbi->execute($create_table1);
1184
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1185
$result = $dbi->select(table => 'table1');
1186
$result->filter([qw/key1 key2/] => sub { $_[0] * 2 });
1187
$result->end_filter([[qw/key1 key2/] => sub { $_[0] * 3 }]);
1188
$row = $result->fetch_first;
1189
is_deeply($row, [6, 12]);
1190

            
1191
$dbi = DBIx::Custom->connect;
1192
eval { $dbi->execute('drop table table1') };
1193
$dbi->execute($create_table1);
1194
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1195
$result = $dbi->select(table => 'table1');
1196
$result->filter([[qw/key1 key2/] => sub { $_[0] * 2 }]);
1197
$result->end_filter([qw/key1 key2/] => sub { $_[0] * 3 });
1198
$row = $result->fetch_first;
1199
is_deeply($row, [6, 12]);
1200

            
1201
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1202
$result = $dbi->select(table => 'table1');
1203
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1204
$result->end_filter({key1 => sub { $_[0] * 3 }, key2 => 'five_times' });
1205
$row = $result->one;
1206
is_deeply($row, {key1 => 6, key2 => 40});
1207

            
1208
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1209
$dbi->apply_filter('table1',
1210
    key1 => {end => sub { $_[0] * 3 } },
1211
    key2 => {end => 'five_times'}
1212
);
1213
$result = $dbi->select(table => 'table1');
1214
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1215
$row = $result->one;
1216
is_deeply($row, {key1 => 6, key2 => 40}, 'apply_filter');
1217

            
1218
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1219
$dbi->apply_filter('table1',
1220
    key1 => {end => sub { $_[0] * 3 } },
1221
    key2 => {end => 'five_times'}
1222
);
1223
$result = $dbi->select(table => 'table1');
1224
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1225
$result->filter(key1 => undef);
1226
$result->end_filter(key1 => undef);
1227
$row = $result->one;
1228
is_deeply($row, {key1 => 1, key2 => 40}, 'apply_filter overwrite');
1229

            
test cleanup
Yuki Kimoto authored on 2011-08-10
1230
test 'remove_end_filter and remove_filter';
1231
$dbi = DBIx::Custom->connect;
1232
eval { $dbi->execute('drop table table1') };
1233
$dbi->execute($create_table1);
1234
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1235
$result = $dbi->select(table => 'table1');
1236
$row = $result
1237
       ->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 })
1238
       ->remove_filter
1239
       ->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 })
1240
       ->remove_end_filter
1241
       ->fetch_first;
1242
is_deeply($row, [1, 2]);
1243

            
1244
test 'empty where select';
1245
$dbi = DBIx::Custom->connect;
1246
eval { $dbi->execute('drop table table1') };
1247
$dbi->execute($create_table1);
1248
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1249
$result = $dbi->select(table => 'table1', where => {});
1250
$row = $result->one;
1251
is_deeply($row, {key1 => 1, key2 => 2});
1252

            
1253
test 'select query option';
1254
$dbi = DBIx::Custom->connect;
1255
eval { $dbi->execute('drop table table1') };
1256
$dbi->execute($create_table1);
1257
$query = $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, query => 1);
1258
is(ref $query, 'DBIx::Custom::Query');
1259
$query = $dbi->update(table => 'table1', where => {key1 => 1}, param => {key2 => 2}, query => 1);
1260
is(ref $query, 'DBIx::Custom::Query');
1261
$query = $dbi->delete(table => 'table1', where => {key1 => 1}, query => 1);
1262
is(ref $query, 'DBIx::Custom::Query');
1263
$query = $dbi->select(table => 'table1', where => {key1 => 1, key2 => 2}, query => 1);
1264
is(ref $query, 'DBIx::Custom::Query');
1265

            
1266
test 'where';
1267
$dbi = DBIx::Custom->connect;
1268
eval { $dbi->execute('drop table table1') };
1269
$dbi->execute($create_table1);
1270
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1271
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1272
$where = $dbi->where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
1273
is("$where", "where ( key1 = :key1 and key2 = :key2 )", 'no param');
1274

            
1275
$where = $dbi->where
1276
             ->clause(['and', 'key1 = :key1', 'key2 = :key2'])
1277
             ->param({key1 => 1});
1278

            
1279
$result = $dbi->select(
1280
    table => 'table1',
1281
    where => $where
1282
);
1283
$row = $result->all;
1284
is_deeply($row, [{key1 => 1, key2 => 2}]);
1285

            
1286
$result = $dbi->select(
1287
    table => 'table1',
1288
    where => [
1289
        ['and', 'key1 = :key1', 'key2 = :key2'],
1290
        {key1 => 1}
1291
    ]
1292
);
1293
$row = $result->all;
1294
is_deeply($row, [{key1 => 1, key2 => 2}]);
1295

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

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

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

            
1326
$where = $dbi->where;
1327
$result = $dbi->select(
1328
    table => 'table1',
1329
    where => $where
1330
);
1331
$row = $result->all;
1332
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1333

            
1334
eval {
1335
$where = $dbi->where
1336
             ->clause(['uuu']);
1337
$result = $dbi->select(
1338
    table => 'table1',
1339
    where => $where
1340
);
1341
};
1342
ok($@);
1343

            
1344
$where = $dbi->where;
1345
is("$where", '');
1346

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

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

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

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

            
1387
$where = $dbi->where
1388
             ->clause('key1 = :key1 key2 = :key2')
1389
             ->param({key1 => 1});
1390
eval{$where->to_string};
1391
like($@, qr/one column/);
1392

            
1393
$where = $dbi->where
1394
             ->clause(['or', ('key1 = :key1') x 3])
1395
             ->param({key1 => [$dbi->not_exists, 1, 3]});
1396
$result = $dbi->select(
1397
    table => 'table1',
1398
    where => $where,
1399
);
1400
$row = $result->all;
1401
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1402

            
1403
$where = $dbi->where
1404
             ->clause(['or', ('key1 = :key1') x 3])
1405
             ->param({key1 => [1, $dbi->not_exists, 3]});
1406
$result = $dbi->select(
1407
    table => 'table1',
1408
    where => $where,
1409
);
1410
$row = $result->all;
1411
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1412

            
1413
$where = $dbi->where
1414
             ->clause(['or', ('key1 = :key1') x 3])
1415
             ->param({key1 => [1, 3, $dbi->not_exists]});
1416
$result = $dbi->select(
1417
    table => 'table1',
1418
    where => $where,
1419
);
1420
$row = $result->all;
1421
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1422

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

            
1433
$where = $dbi->where
1434
             ->clause(['or', ('key1 = :key1') x 3])
1435
             ->param({key1 => [$dbi->not_exists, 1, $dbi->not_exists]});
1436
$result = $dbi->select(
1437
    table => 'table1',
1438
    where => $where,
1439
);
1440
$row = $result->all;
1441
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1442

            
1443
$where = $dbi->where
1444
             ->clause(['or', ('key1 = :key1') x 3])
1445
             ->param({key1 => [$dbi->not_exists, $dbi->not_exists, 1]});
1446
$result = $dbi->select(
1447
    table => 'table1',
1448
    where => $where,
1449
);
1450
$row = $result->all;
1451
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1452

            
1453
$where = $dbi->where
1454
             ->clause(['or', ('key1 = :key1') x 3])
1455
             ->param({key1 => [$dbi->not_exists, $dbi->not_exists, $dbi->not_exists]});
1456
$result = $dbi->select(
1457
    table => 'table1',
1458
    where => $where,
1459
);
1460
$row = $result->all;
1461
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1462

            
1463
$where = $dbi->where
1464
             ->clause(['or', ('key1 = :key1') x 3])
1465
             ->param({key1 => []});
1466
$result = $dbi->select(
1467
    table => 'table1',
1468
    where => $where,
1469
);
1470
$row = $result->all;
1471
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1472

            
1473
$where = $dbi->where
1474
             ->clause(['and', '{> key1}', '{< key1}' ])
1475
             ->param({key1 => [2, $dbi->not_exists]});
1476
$result = $dbi->select(
1477
    table => 'table1',
1478
    where => $where,
1479
);
1480
$row = $result->all;
1481
is_deeply($row, [{key1 => 3, key2 => 4}], 'not_exists');
1482

            
1483
$where = $dbi->where
1484
             ->clause(['and', '{> key1}', '{< key1}' ])
1485
             ->param({key1 => [$dbi->not_exists, 2]});
1486
$result = $dbi->select(
1487
    table => 'table1',
1488
    where => $where,
1489
);
1490
$row = $result->all;
1491
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1492

            
1493
$where = $dbi->where
1494
             ->clause(['and', '{> key1}', '{< key1}' ])
1495
             ->param({key1 => [$dbi->not_exists, $dbi->not_exists]});
1496
$result = $dbi->select(
1497
    table => 'table1',
1498
    where => $where,
1499
);
1500
$row = $result->all;
1501
is_deeply($row, [{key1 => 1, key2 => 2},{key1 => 3, key2 => 4}], 'not_exists');
1502

            
1503
$where = $dbi->where
1504
             ->clause(['and', '{> key1}', '{< key1}' ])
1505
             ->param({key1 => [0, 2]});
1506
$result = $dbi->select(
1507
    table => 'table1',
1508
    where => $where,
1509
);
1510
$row = $result->all;
1511
is_deeply($row, [{key1 => 1, key2 => 2}], 'not_exists');
1512

            
1513
$where = $dbi->where
1514
             ->clause(['and', 'key1 is not null', 'key2 is not null' ]);
1515
$result = $dbi->select(
1516
    table => 'table1',
1517
    where => $where,
1518
);
1519
$row = $result->all;
1520
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1521

            
1522
eval {$dbi->where(ppp => 1) };
1523
like($@, qr/invalid/);
1524

            
1525
$where = $dbi->where(
1526
    clause => ['and', ['or'], ['and', 'key1 = :key1', 'key2 = :key2']],
1527
    param => {key1 => 1, key2 => 2}
1528
);
1529
$result = $dbi->select(
1530
    table => 'table1',
1531
    where => $where,
1532
);
1533
$row = $result->all;
1534
is_deeply($row, [{key1 => 1, key2 => 2}]);
1535

            
1536

            
1537
$where = $dbi->where(
1538
    clause => ['and', ['or'], ['or', ':key1', ':key2']],
1539
    param => {}
1540
);
1541
$result = $dbi->select(
1542
    table => 'table1',
1543
    where => $where,
1544
);
1545
$row = $result->all;
1546
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1547

            
1548
$where = $dbi->where;
1549
$where->clause(['and', ':key1{=}']);
1550
$where->param({key1 => undef});
1551
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
1552
$row = $result->all;
1553
is_deeply($row, [{key1 => 1, key2 => 2}]);
1554

            
1555
$where = $dbi->where;
1556
$where->clause(['and', ':key1{=}']);
1557
$where->param({key1 => undef});
1558
$where->if('defined');
1559
$where->map;
1560
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
1561
$row = $result->all;
1562
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1563

            
1564
$where = $dbi->where;
1565
$where->clause(['or', ':key1{=}', ':key1{=}']);
1566
$where->param({key1 => [undef, undef]});
1567
$result = $dbi->execute("select * from table1 $where", {key1 => [1, 0]});
1568
$row = $result->all;
1569
is_deeply($row, [{key1 => 1, key2 => 2}]);
1570
$result = $dbi->execute("select * from table1 $where", {key1 => [0, 1]});
1571
$row = $result->all;
1572
is_deeply($row, [{key1 => 1, key2 => 2}]);
1573

            
1574
$where = $dbi->where;
1575
$where->clause(['and', ':key1{=}']);
1576
$where->param({key1 => [undef, undef]});
1577
$where->if('defined');
1578
$where->map;
1579
$result = $dbi->execute("select * from table1 $where", {key1 => [1, 0]});
1580
$row = $result->all;
1581
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1582
$result = $dbi->execute("select * from table1 $where", {key1 => [0, 1]});
1583
$row = $result->all;
1584
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1585

            
1586
$where = $dbi->where;
1587
$where->clause(['and', ':key1{=}']);
1588
$where->param({key1 => 0});
1589
$where->if('length');
1590
$where->map;
1591
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
1592
$row = $result->all;
1593
is_deeply($row, [{key1 => 1, key2 => 2}]);
1594

            
1595
$where = $dbi->where;
1596
$where->clause(['and', ':key1{=}']);
1597
$where->param({key1 => ''});
1598
$where->if('length');
1599
$where->map;
1600
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
1601
$row = $result->all;
1602
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1603

            
1604
$where = $dbi->where;
1605
$where->clause(['and', ':key1{=}']);
1606
$where->param({key1 => 5});
1607
$where->if(sub { ($_[0] || '') eq 5 });
1608
$where->map;
1609
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
1610
$row = $result->all;
1611
is_deeply($row, [{key1 => 1, key2 => 2}]);
1612

            
1613
$where = $dbi->where;
1614
$where->clause(['and', ':key1{=}']);
1615
$where->param({key1 => 7});
1616
$where->if(sub { ($_[0] || '') eq 5 });
1617
$where->map;
1618
$result = $dbi->execute("select * from table1 $where", {key1 => 1});
1619
$row = $result->all;
1620
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1621

            
1622
$where = $dbi->where;
1623
$where->param({id => 1, author => 'Ken', price => 1900});
1624
$where->map(id => 'book.id',
1625
    author => ['book.author', sub { '%' . $_[0] . '%' }],
1626
    price => ['book.price', {if => sub { $_[0] eq 1900 }}]
1627
);
1628
is_deeply($where->param, {'book.id' => 1, 'book.author' => '%Ken%',
1629
  'book.price' => 1900});
1630

            
1631
$where = $dbi->where;
1632
$where->param({id => 0, author => 0, price => 0});
1633
$where->map(
1634
    id => 'book.id',
1635
    author => ['book.author', sub { '%' . $_[0] . '%' }],
1636
    price => ['book.price', sub { '%' . $_[0] . '%' },
1637
      {if => sub { $_[0] eq 0 }}]
1638
);
1639
is_deeply($where->param, {'book.id' => 0, 'book.author' => '%0%', 'book.price' => '%0%'});
1640

            
1641
$where = $dbi->where;
1642
$where->param({id => '', author => '', price => ''});
1643
$where->if('length');
1644
$where->map(
1645
    id => 'book.id',
1646
    author => ['book.author', sub { '%' . $_[0] . '%' }],
1647
    price => ['book.price', sub { '%' . $_[0] . '%' },
1648
      {if => sub { $_[0] eq 1 }}]
1649
);
1650
is_deeply($where->param, {});
1651

            
1652
$where = $dbi->where;
1653
$where->param({id => undef, author => undef, price => undef});
1654
$where->if('length');
1655
$where->map(
1656
    id => 'book.id',
1657
    price => ['book.price', {if => 'exists'}]
1658
);
1659
is_deeply($where->param, {'book.price' => undef});
1660

            
1661
$where = $dbi->where;
1662
$where->param({price => 'a'});
1663
$where->if('length');
1664
$where->map(
1665
    id => ['book.id', {if => 'exists'}],
1666
    price => ['book.price', sub { '%' . $_[0] }, {if => 'exists'}]
1667
);
1668
is_deeply($where->param, {'book.price' => '%a'});
1669

            
1670
$where = $dbi->where;
1671
$where->param({id => [1, 2], author => 'Ken', price => 1900});
1672
$where->map(
1673
    id => 'book.id',
1674
    author => ['book.author', sub { '%' . $_[0] . '%' }],
1675
    price => ['book.price', {if => sub { $_[0] eq 1900 }}]
1676
);
1677
is_deeply($where->param, {'book.id' => [1, 2], 'book.author' => '%Ken%',
1678
  'book.price' => 1900});
1679

            
1680
$where = $dbi->where;
1681
$where->if('length');
1682
$where->param({id => ['', ''], author => 'Ken', price => 1900});
1683
$where->map(
1684
    id => 'book.id',
1685
    author => ['book.author', sub { '%' . $_[0] . '%' }],
1686
    price => ['book.price', {if => sub { $_[0] eq 1900 }}]
1687
);
1688
is_deeply($where->param, {'book.id' => [$dbi->not_exists, $dbi->not_exists], 'book.author' => '%Ken%',
1689
  'book.price' => 1900});
1690

            
1691
$where = $dbi->where;
1692
$where->param({id => ['', ''], author => 'Ken', price => 1900});
1693
$where->map(
1694
    id => ['book.id', {if => 'length'}],
1695
    author => ['book.author', sub { '%' . $_[0] . '%' }, {if => 'defined'}],
1696
    price => ['book.price', {if => sub { $_[0] eq 1900 }}]
1697
);
1698
is_deeply($where->param, {'book.id' => [$dbi->not_exists, $dbi->not_exists], 'book.author' => '%Ken%',
1699
  'book.price' => 1900});
1700

            
1701
test 'dbi_option default';
1702
$dbi = DBIx::Custom->new;
1703
is_deeply($dbi->dbi_option, {});
1704

            
1705
test 'register_tag_processor';
1706
$dbi = DBIx::Custom->connect;
1707
$dbi->register_tag_processor(
1708
    a => sub { 1 }
1709
);
1710
is($dbi->query_builder->tag_processors->{a}->(), 1);
1711

            
1712
test 'register_tag';
1713
$dbi = DBIx::Custom->connect;
1714
$dbi->register_tag(
1715
    b => sub { 2 }
1716
);
1717
is($dbi->query_builder->tags->{b}->(), 2);
1718

            
1719
test 'table not specify exception';
1720
$dbi = DBIx::Custom->connect;
1721
eval {$dbi->insert};
1722
like($@, qr/table/);
1723
eval {$dbi->update};
1724
like($@, qr/table/);
1725
eval {$dbi->delete};
1726
like($@, qr/table/);
1727
eval {$dbi->select};
1728
like($@, qr/table/);
test cleanup
Yuki Kimoto authored on 2011-08-10
1729

            
test cleanup
Yuki Kimoto authored on 2011-08-10
1730
test 'more tests';
1731
$dbi = DBIx::Custom->connect;
1732
eval{$dbi->apply_filter('table', 'column', [])};
1733
like($@, qr/apply_filter/);
1734

            
1735
eval{$dbi->apply_filter('table', 'column', {outer => 2})};
1736
like($@, qr/apply_filter/);
1737

            
1738
$dbi->apply_filter(
1739

            
1740
);
1741
$dbi = DBIx::Custom->connect;
1742
eval { $dbi->execute('drop table table1') };
1743
$dbi->execute($create_table1);
1744
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1745
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1746
$dbi->apply_filter('table1', 'key2', 
1747
                   {in => sub { $_[0] * 3 }, out => sub { $_[0] * 2 }});
1748
$rows = $dbi->select(table => 'table1', where => {key2 => 1})->all;
1749
is_deeply($rows, [{key1 => 1, key2 => 6}]);
1750

            
1751
$dbi = DBIx::Custom->connect;
1752
eval { $dbi->execute('drop table table1') };
1753
$dbi->execute($create_table1);
1754
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1755
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1756
$dbi->apply_filter('table1', 'key2', {});
1757
$rows = $dbi->select(table => 'table1', where => {key2 => 2})->all;
1758
is_deeply($rows, [{key1 => 1, key2 => 2}]);
1759

            
1760
$dbi = DBIx::Custom->connect;
1761
eval {$dbi->apply_filter('table1', 'key2', {out => 'no'})};
1762
like($@, qr/not registered/);
1763
eval {$dbi->apply_filter('table1', 'key2', {in => 'no'})};
1764
like($@, qr/not registered/);
1765
$dbi->method({one => sub { 1 }});
1766
is($dbi->one, 1);
1767

            
1768
eval{DBIx::Custom->connect(dsn => undef)};
1769
like($@, qr/_connect/);
1770

            
1771
$dbi = DBIx::Custom->connect;
1772
eval { $dbi->execute('drop table table1') };
1773
$dbi->execute($create_table1);
1774
$dbi->register_filter(twice => sub { $_[0] * 2 });
1775
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
1776
             filter => {key1 => 'twice'});
1777
$row = $dbi->select(table => 'table1')->one;
1778
is_deeply($row, {key1 => 2, key2 => 2});
1779
eval {$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
1780
             filter => {key1 => 'no'}) };
1781
like($@, qr//);
1782

            
1783
$dbi->register_filter(one => sub { });
1784
$dbi->default_fetch_filter('one');
1785
ok($dbi->default_fetch_filter);
1786
$dbi->default_bind_filter('one');
1787
ok($dbi->default_bind_filter);
1788
eval{$dbi->default_fetch_filter('no')};
1789
like($@, qr/not registered/);
1790
eval{$dbi->default_bind_filter('no')};
1791
like($@, qr/not registered/);
1792
$dbi->default_bind_filter(undef);
1793
ok(!defined $dbi->default_bind_filter);
1794
$dbi->default_fetch_filter(undef);
1795
ok(!defined $dbi->default_fetch_filter);
1796
eval {$dbi->execute('select * from table1 {} {= author') };
1797
like($@, qr/Tag not finished/);
1798

            
1799
$dbi = DBIx::Custom->connect;
1800
eval { $dbi->execute('drop table table1') };
1801
$dbi->execute($create_table1);
1802
$dbi->register_filter(one => sub { 1 });
1803
$result = $dbi->select(table => 'table1');
1804
eval {$result->filter(key1 => 'no')};
1805
like($@, qr/not registered/);
1806
eval {$result->end_filter(key1 => 'no')};
1807
like($@, qr/not registered/);
1808
$result->default_filter(undef);
1809
ok(!defined $result->default_filter);
1810
$result->default_filter('one');
1811
is($result->default_filter->(), 1);
1812

            
1813
test 'dbi_option';
1814
$dbi = DBIx::Custom->connect(dbi_option => {PrintError => 1});
1815
ok($dbi->dbh->{PrintError});
1816
$dbi = DBIx::Custom->connect(dbi_options => {PrintError => 1});
1817
ok($dbi->dbh->{PrintError});
1818

            
1819
test 'DBIx::Custom::Result stash()';
1820
$result = DBIx::Custom::Result->new;
1821
is_deeply($result->stash, {}, 'default');
1822
$result->stash->{foo} = 1;
1823
is($result->stash->{foo}, 1, 'get and set');
test cleanup
Yuki Kimoto authored on 2011-08-10
1824

            
cleanup test
Yuki Kimoto authored on 2011-08-10
1825
test 'delete_at';
1826
$dbi = DBIx::Custom->connect;
1827
eval { $dbi->execute('drop table table1') };
1828
$dbi->execute($create_table1_2);
1829
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1830
$dbi->delete_at(
1831
    table => 'table1',
1832
    primary_key => ['key1', 'key2'],
1833
    where => [1, 2],
1834
);
1835
is_deeply($dbi->select(table => 'table1')->all, []);
1836

            
1837
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1838
$dbi->delete_at(
1839
    table => 'table1',
1840
    primary_key => 'key1',
1841
    where => 1,
1842
);
1843
is_deeply($dbi->select(table => 'table1')->all, []);
1844

            
1845
test 'insert_at';
1846
$dbi = DBIx::Custom->connect;
1847
eval { $dbi->execute('drop table table1') };
1848
$dbi->execute($create_table1_2);
1849
$dbi->insert_at(
1850
    primary_key => ['key1', 'key2'], 
1851
    table => 'table1',
1852
    where => [1, 2],
1853
    param => {key3 => 3}
1854
);
1855
is($dbi->select(table => 'table1')->one->{key1}, 1);
1856
is($dbi->select(table => 'table1')->one->{key2}, 2);
1857
is($dbi->select(table => 'table1')->one->{key3}, 3);
1858

            
1859
$dbi->delete_all(table => 'table1');
1860
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1861
$dbi->insert_at(
1862
    primary_key => 'key1', 
1863
    table => 'table1',
1864
    where => 1,
1865
    param => {key2 => 2, key3 => 3}
1866
);
1867

            
1868
is($dbi->select(table => 'table1')->one->{key1}, 1);
1869
is($dbi->select(table => 'table1')->one->{key2}, 2);
1870
is($dbi->select(table => 'table1')->one->{key3}, 3);
1871

            
1872
eval {
1873
    $dbi->insert_at(
1874
        table => 'table1',
1875
        primary_key => ['key1', 'key2'],
1876
        where => {},
1877
        param => {key1 => 1, key2 => 2, key3 => 3},
1878
    );
1879
};
1880
like($@, qr/must be/);
1881

            
1882
$dbi = DBIx::Custom->connect;
1883
eval { $dbi->execute('drop table table1') };
1884
$dbi->execute($create_table1_2);
1885
$dbi->insert_at(
1886
    {key3 => 3},
1887
    primary_key => ['key1', 'key2'], 
1888
    table => 'table1',
1889
    where => [1, 2],
1890
);
1891
is($dbi->select(table => 'table1')->one->{key1}, 1);
1892
is($dbi->select(table => 'table1')->one->{key2}, 2);
1893
is($dbi->select(table => 'table1')->one->{key3}, 3);
1894

            
1895
test 'update_at';
1896
$dbi = DBIx::Custom->connect;
1897
eval { $dbi->execute('drop table table1') };
1898
$dbi->execute($create_table1_2);
1899
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1900
$dbi->update_at(
1901
    table => 'table1',
1902
    primary_key => ['key1', 'key2'],
1903
    where => [1, 2],
1904
    param => {key3 => 4}
1905
);
1906
is($dbi->select(table => 'table1')->one->{key1}, 1);
1907
is($dbi->select(table => 'table1')->one->{key2}, 2);
1908
is($dbi->select(table => 'table1')->one->{key3}, 4);
1909

            
1910
$dbi->delete_all(table => 'table1');
1911
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1912
$dbi->update_at(
1913
    table => 'table1',
1914
    primary_key => 'key1',
1915
    where => 1,
1916
    param => {key3 => 4}
1917
);
1918
is($dbi->select(table => 'table1')->one->{key1}, 1);
1919
is($dbi->select(table => 'table1')->one->{key2}, 2);
1920
is($dbi->select(table => 'table1')->one->{key3}, 4);
1921

            
1922
$dbi = DBIx::Custom->connect;
1923
eval { $dbi->execute('drop table table1') };
1924
$dbi->execute($create_table1_2);
1925
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1926
$dbi->update_at(
1927
    {key3 => 4},
1928
    table => 'table1',
1929
    primary_key => ['key1', 'key2'],
1930
    where => [1, 2]
1931
);
1932
is($dbi->select(table => 'table1')->one->{key1}, 1);
1933
is($dbi->select(table => 'table1')->one->{key2}, 2);
1934
is($dbi->select(table => 'table1')->one->{key3}, 4);
1935

            
1936
test 'select_at';
1937
$dbi = DBIx::Custom->connect;
1938
eval { $dbi->execute('drop table table1') };
1939
$dbi->execute($create_table1_2);
1940
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1941
$result = $dbi->select_at(
1942
    table => 'table1',
1943
    primary_key => ['key1', 'key2'],
1944
    where => [1, 2]
1945
);
1946
$row = $result->one;
1947
is($row->{key1}, 1);
1948
is($row->{key2}, 2);
1949
is($row->{key3}, 3);
1950

            
1951
$dbi->delete_all(table => 'table1');
1952
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1953
$result = $dbi->select_at(
1954
    table => 'table1',
1955
    primary_key => 'key1',
1956
    where => 1,
1957
);
1958
$row = $result->one;
1959
is($row->{key1}, 1);
1960
is($row->{key2}, 2);
1961
is($row->{key3}, 3);
1962

            
1963
$dbi->delete_all(table => 'table1');
1964
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1965
$result = $dbi->select_at(
1966
    table => 'table1',
1967
    primary_key => ['key1', 'key2'],
1968
    where => [1, 2]
1969
);
1970
$row = $result->one;
1971
is($row->{key1}, 1);
1972
is($row->{key2}, 2);
1973
is($row->{key3}, 3);
1974

            
1975
eval {
1976
    $result = $dbi->select_at(
1977
        table => 'table1',
1978
        primary_key => ['key1', 'key2'],
1979
        where => {},
1980
    );
1981
};
1982
like($@, qr/must be/);
1983

            
1984
eval {
1985
    $result = $dbi->select_at(
1986
        table => 'table1',
1987
        primary_key => ['key1', 'key2'],
1988
        where => [1],
1989
    );
1990
};
1991
like($@, qr/same/);
1992

            
1993
eval {
1994
    $result = $dbi->update_at(
1995
        table => 'table1',
1996
        primary_key => ['key1', 'key2'],
1997
        where => {},
1998
        param => {key1 => 1, key2 => 2},
1999
    );
2000
};
2001
like($@, qr/must be/);
2002

            
2003
eval {
2004
    $result = $dbi->delete_at(
2005
        table => 'table1',
2006
        primary_key => ['key1', 'key2'],
2007
        where => {},
2008
    );
2009
};
2010
like($@, qr/must be/);
2011

            
2012
test 'columns';
2013
use MyDBI1;
2014
$dbi = MyDBI1->connect;
2015
$model = $dbi->model('book');
2016

            
2017

            
2018
test 'model delete_at';
2019
$dbi = MyDBI6->connect;
2020
eval { $dbi->execute('drop table table1') };
2021
eval { $dbi->execute('drop table table2') };
2022
eval { $dbi->execute('drop table table3') };
2023
$dbi->execute($create_table1_2);
2024
$dbi->execute($create_table2_2);
2025
$dbi->execute($create_table3);
2026
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2027
$dbi->model('table1')->delete_at(where => [1, 2]);
2028
is_deeply($dbi->select(table => 'table1')->all, []);
2029
$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
2030
$dbi->model('table1_1')->delete_at(where => [1, 2]);
2031
is_deeply($dbi->select(table => 'table1')->all, []);
2032
$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
2033
$dbi->model('table1_3')->delete_at(where => [1, 2]);
2034
is_deeply($dbi->select(table => 'table1')->all, []);
2035

            
2036
test 'model insert_at';
2037
$dbi = MyDBI6->connect;
2038
eval { $dbi->execute('drop table table1') };
2039
$dbi->execute($create_table1_2);
2040
$dbi->model('table1')->insert_at(
2041
    where => [1, 2],
2042
    param => {key3 => 3}
2043
);
2044
$result = $dbi->model('table1')->select;
2045
$row = $result->one;
2046
is($row->{key1}, 1);
2047
is($row->{key2}, 2);
2048
is($row->{key3}, 3);
2049

            
2050
test 'model update_at';
2051
$dbi = MyDBI6->connect;
2052
eval { $dbi->execute('drop table table1') };
2053
$dbi->execute($create_table1_2);
2054
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2055
$dbi->model('table1')->update_at(
2056
    where => [1, 2],
2057
    param => {key3 => 4}
2058
);
2059
$result = $dbi->model('table1')->select;
2060
$row = $result->one;
2061
is($row->{key1}, 1);
2062
is($row->{key2}, 2);
2063
is($row->{key3}, 4);
2064

            
2065
test 'model select_at';
2066
$dbi = MyDBI6->connect;
2067
eval { $dbi->execute('drop table table1') };
2068
$dbi->execute($create_table1_2);
2069
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2070
$result = $dbi->model('table1')->select_at(where => [1, 2]);
2071
$row = $result->one;
2072
is($row->{key1}, 1);
2073
is($row->{key2}, 2);
2074
is($row->{key3}, 3);
2075

            
2076

            
2077
test 'mycolumn and column';
2078
$dbi = MyDBI7->connect;
2079
eval { $dbi->execute('drop table table1') };
2080
eval { $dbi->execute('drop table table2') };
2081
$dbi->execute($create_table1);
2082
$dbi->execute($create_table2);
2083
$dbi->separator('__');
2084
$dbi->setup_model;
2085
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2086
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2087
$model = $dbi->model('table1');
2088
$result = $model->select(
2089
    column => [$model->mycolumn, $model->column('table2')],
2090
    where => {'table1.key1' => 1}
2091
);
2092
is_deeply($result->one,
2093
          {key1 => 1, key2 => 2, 'table2__key1' => 1, 'table2__key3' => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
2094

            
test cleanup
Yuki Kimoto authored on 2011-08-10
2095
test 'insert_param';
2096
$dbi = DBIx::Custom->connect;
2097
eval { $dbi->execute('drop table table1') };
2098
$dbi->execute($create_table1_2);
2099
$param = {key1 => 1, key2 => 2};
2100
$insert_param = $dbi->insert_param($param);
2101
$sql = <<"EOS";
2102
insert into table1 $insert_param
2103
EOS
2104
$dbi->execute($sql, param => $param, table => 'table1');
2105
is($dbi->select(table => 'table1')->one->{key1}, 1);
2106
is($dbi->select(table => 'table1')->one->{key2}, 2);
2107

            
2108
$dbi = DBIx::Custom->connect;
2109
eval { $dbi->execute('drop table table1') };
2110
$dbi->execute($create_table1_2);
2111
$param = {key1 => 1, key2 => 2};
2112
$insert_param = $dbi->insert_param($param);
2113
$sql = <<"EOS";
2114
insert into table1 $insert_param
2115
EOS
2116
$dbi->execute($sql, param => $param, table => 'table1');
2117
is($dbi->select(table => 'table1')->one->{key1}, 1);
2118
is($dbi->select(table => 'table1')->one->{key2}, 2);
2119

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

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2123
test 'mycolumn';
2124
$dbi = MyDBI8->connect;
2125
eval { $dbi->execute('drop table table1') };
2126
eval { $dbi->execute('drop table table2') };
2127
$dbi->execute($create_table1);
2128
$dbi->execute($create_table2);
2129
$dbi->setup_model;
2130
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2131
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2132
$model = $dbi->model('table1');
2133
$result = $model->select_at(
2134
    column => [
2135
        $model->mycolumn,
2136
        $model->column('table2')
2137
    ]
2138
);
2139
is_deeply($result->one,
2140
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
2141

            
2142
$result = $model->select_at(
2143
    column => [
2144
        $model->mycolumn(['key1']),
2145
        $model->column(table2 => ['key1'])
2146
    ]
2147
);
2148
is_deeply($result->one,
2149
          {key1 => 1, 'table2.key1' => 1});
2150
$result = $model->select_at(
2151
    column => [
2152
        $model->mycolumn(['key1']),
2153
        {table2 => ['key1']}
2154
    ]
2155
);
2156
is_deeply($result->one,
2157
          {key1 => 1, 'table2.key1' => 1});
2158

            
2159
$result = $model->select_at(
2160
    column => [
2161
        $model->mycolumn(['key1']),
2162
        ['table2.key1', as => 'table2.key1']
2163
    ]
2164
);
2165
is_deeply($result->one,
2166
          {key1 => 1, 'table2.key1' => 1});
2167

            
2168
$result = $model->select_at(
2169
    column => [
2170
        $model->mycolumn(['key1']),
2171
        ['table2.key1' => 'table2.key1']
2172
    ]
2173
);
2174
is_deeply($result->one,
2175
          {key1 => 1, 'table2.key1' => 1});
2176

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2177
test 'merge_param';
2178
$dbi = DBIx::Custom->new;
2179
$params = [
2180
    {key1 => 1, key2 => 2, key3 => 3},
2181
    {key1 => 1, key2 => 2},
2182
    {key1 => 1}
2183
];
2184
$param = $dbi->merge_param($params->[0], $params->[1], $params->[2]);
2185
is_deeply($param, {key1 => [1, 1, 1], key2 => [2, 2], key3 => 3});
2186

            
2187
$params = [
2188
    {key1 => [1, 2], key2 => 1, key3 => [1, 2]},
2189
    {key1 => [3, 4], key2 => [2, 3], key3 => 3}
2190
];
2191
$param = $dbi->merge_param($params->[0], $params->[1]);
2192
is_deeply($param, {key1 => [1, 2, 3, 4], key2 => [1, 2, 3], key3 => [1, 2, 3]});
2193

            
2194
test 'select() param option';
2195
$dbi = DBIx::Custom->connect;
2196
eval { $dbi->execute('drop table table1') };
2197
$dbi->execute($create_table1);
2198
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2199
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2200
eval { $dbi->execute('drop table table2') };
2201
$dbi->execute($create_table2);
2202
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
2203
$dbi->insert(table => 'table2', param => {key1 => 2, key3 => 5});
2204
$rows = $dbi->select(
2205
    table => 'table1',
2206
    column => 'table1.key1 as table1_key1, key2, key3',
2207
    where   => {'table1.key2' => 3},
2208
    join  => ['inner join (select * from table2 where {= table2.key3})' . 
2209
              ' as table2 on table1.key1 = table2.key1'],
2210
    param => {'table2.key3' => 5}
2211
)->all;
2212
is_deeply($rows, [{table1_key1 => 2, key2 => 3, key3 => 5}]);
2213

            
2214
test 'select() string where';
2215
$dbi = DBIx::Custom->connect;
2216
eval { $dbi->execute('drop table table1') };
2217
$dbi->execute($create_table1);
2218
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2219
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2220
$rows = $dbi->select(
2221
    table => 'table1',
2222
    where => 'key1 = :key1 and key2 = :key2',
2223
    where_param => {key1 => 1, key2 => 2}
2224
)->all;
2225
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2226

            
2227
$dbi = DBIx::Custom->connect;
2228
eval { $dbi->execute('drop table table1') };
2229
$dbi->execute($create_table1);
2230
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2231
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2232
$rows = $dbi->select(
2233
    table => 'table1',
2234
    where => [
2235
        'key1 = :key1 and key2 = :key2',
2236
        {key1 => 1, key2 => 2}
2237
    ]
2238
)->all;
2239
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2240

            
2241
test 'delete() string where';
2242
$dbi = DBIx::Custom->connect;
2243
eval { $dbi->execute('drop table table1') };
2244
$dbi->execute($create_table1);
2245
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2246
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2247
$dbi->delete(
2248
    table => 'table1',
2249
    where => 'key1 = :key1 and key2 = :key2',
2250
    where_param => {key1 => 1, key2 => 2}
2251
);
2252
$rows = $dbi->select(table => 'table1')->all;
2253
is_deeply($rows, [{key1 => 2, key2 => 3}]);
2254

            
2255
$dbi = DBIx::Custom->connect;
2256
eval { $dbi->execute('drop table table1') };
2257
$dbi->execute($create_table1);
2258
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2259
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2260
$dbi->delete(
2261
    table => 'table1',
2262
    where => [
2263
        'key1 = :key1 and key2 = :key2',
2264
         {key1 => 1, key2 => 2}
2265
    ]
2266
);
2267
$rows = $dbi->select(table => 'table1')->all;
2268
is_deeply($rows, [{key1 => 2, key2 => 3}]);
2269

            
2270

            
2271
test 'update() string where';
2272
$dbi = DBIx::Custom->connect;
2273
eval { $dbi->execute('drop table table1') };
2274
$dbi->execute($create_table1);
2275
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2276
$dbi->update(
2277
    table => 'table1',
2278
    param => {key1 => 5},
2279
    where => 'key1 = :key1 and key2 = :key2',
2280
    where_param => {key1 => 1, key2 => 2}
2281
);
2282
$rows = $dbi->select(table => 'table1')->all;
2283
is_deeply($rows, [{key1 => 5, key2 => 2}]);
2284

            
2285
$dbi = DBIx::Custom->connect;
2286
eval { $dbi->execute('drop table table1') };
2287
$dbi->execute($create_table1);
2288
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2289
$dbi->update(
2290
    table => 'table1',
2291
    param => {key1 => 5},
2292
    where => [
2293
        'key1 = :key1 and key2 = :key2',
2294
        {key1 => 1, key2 => 2}
2295
    ]
2296
);
2297
$rows = $dbi->select(table => 'table1')->all;
2298
is_deeply($rows, [{key1 => 5, key2 => 2}]);
2299

            
2300
test 'insert id and primary_key option';
2301
$dbi = DBIx::Custom->connect;
2302
eval { $dbi->execute('drop table table1') };
2303
$dbi->execute($create_table1_2);
2304
$dbi->insert(
2305
    primary_key => ['key1', 'key2'], 
2306
    table => 'table1',
2307
    id => [1, 2],
2308
    param => {key3 => 3}
2309
);
2310
is($dbi->select(table => 'table1')->one->{key1}, 1);
2311
is($dbi->select(table => 'table1')->one->{key2}, 2);
2312
is($dbi->select(table => 'table1')->one->{key3}, 3);
2313

            
2314
$dbi->delete_all(table => 'table1');
2315
$dbi->insert(
2316
    primary_key => 'key1', 
2317
    table => 'table1',
2318
    id => 0,
2319
    param => {key2 => 2, key3 => 3}
2320
);
2321

            
2322
is($dbi->select(table => 'table1')->one->{key1}, 0);
2323
is($dbi->select(table => 'table1')->one->{key2}, 2);
2324
is($dbi->select(table => 'table1')->one->{key3}, 3);
2325

            
2326
$dbi = DBIx::Custom->connect;
2327
eval { $dbi->execute('drop table table1') };
2328
$dbi->execute($create_table1_2);
2329
$dbi->insert(
2330
    {key3 => 3},
2331
    primary_key => ['key1', 'key2'], 
2332
    table => 'table1',
2333
    id => [1, 2],
2334
);
2335
is($dbi->select(table => 'table1')->one->{key1}, 1);
2336
is($dbi->select(table => 'table1')->one->{key2}, 2);
2337
is($dbi->select(table => 'table1')->one->{key3}, 3);
test cleanup
Yuki Kimoto authored on 2011-08-10
2338

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2339
test 'model insert id and primary_key option';
2340
$dbi = MyDBI6->connect;
2341
eval { $dbi->execute('drop table table1') };
2342
$dbi->execute($create_table1_2);
2343
$dbi->model('table1')->insert(
2344
    id => [1, 2],
2345
    param => {key3 => 3}
2346
);
2347
$result = $dbi->model('table1')->select;
2348
$row = $result->one;
2349
is($row->{key1}, 1);
2350
is($row->{key2}, 2);
2351
is($row->{key3}, 3);
2352

            
2353
$dbi = MyDBI6->connect;
2354
eval { $dbi->execute('drop table table1') };
2355
$dbi->execute($create_table1_2);
2356
$dbi->model('table1')->insert(
2357
    {key3 => 3},
2358
    id => [1, 2]
2359
);
2360
$result = $dbi->model('table1')->select;
2361
$row = $result->one;
2362
is($row->{key1}, 1);
2363
is($row->{key2}, 2);
2364
is($row->{key3}, 3);
2365

            
2366
test 'update and id option';
2367
$dbi = DBIx::Custom->connect;
2368
eval { $dbi->execute('drop table table1') };
2369
$dbi->execute($create_table1_2);
2370
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2371
$dbi->update(
2372
    table => 'table1',
2373
    primary_key => ['key1', 'key2'],
2374
    id => [1, 2],
2375
    param => {key3 => 4}
2376
);
2377
is($dbi->select(table => 'table1')->one->{key1}, 1);
2378
is($dbi->select(table => 'table1')->one->{key2}, 2);
2379
is($dbi->select(table => 'table1')->one->{key3}, 4);
2380

            
2381
$dbi->delete_all(table => 'table1');
2382
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2383
$dbi->update(
2384
    table => 'table1',
2385
    primary_key => 'key1',
2386
    id => 0,
2387
    param => {key3 => 4}
2388
);
2389
is($dbi->select(table => 'table1')->one->{key1}, 0);
2390
is($dbi->select(table => 'table1')->one->{key2}, 2);
2391
is($dbi->select(table => 'table1')->one->{key3}, 4);
2392

            
2393
$dbi = DBIx::Custom->connect;
2394
eval { $dbi->execute('drop table table1') };
2395
$dbi->execute($create_table1_2);
2396
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2397
$dbi->update(
2398
    {key3 => 4},
2399
    table => 'table1',
2400
    primary_key => ['key1', 'key2'],
2401
    id => [1, 2]
2402
);
2403
is($dbi->select(table => 'table1')->one->{key1}, 1);
2404
is($dbi->select(table => 'table1')->one->{key2}, 2);
2405
is($dbi->select(table => 'table1')->one->{key3}, 4);
2406

            
2407

            
2408
test 'model update and id option';
2409
$dbi = MyDBI6->connect;
2410
eval { $dbi->execute('drop table table1') };
2411
$dbi->execute($create_table1_2);
2412
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2413
$dbi->model('table1')->update(
2414
    id => [1, 2],
2415
    param => {key3 => 4}
2416
);
2417
$result = $dbi->model('table1')->select;
2418
$row = $result->one;
2419
is($row->{key1}, 1);
2420
is($row->{key2}, 2);
2421
is($row->{key3}, 4);
2422

            
2423

            
2424
test 'delete and id option';
2425
$dbi = DBIx::Custom->connect;
2426
eval { $dbi->execute('drop table table1') };
2427
$dbi->execute($create_table1_2);
2428
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2429
$dbi->delete(
2430
    table => 'table1',
2431
    primary_key => ['key1', 'key2'],
2432
    id => [1, 2],
2433
);
2434
is_deeply($dbi->select(table => 'table1')->all, []);
2435

            
2436
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2437
$dbi->delete(
2438
    table => 'table1',
2439
    primary_key => 'key1',
2440
    id => 0,
2441
);
2442
is_deeply($dbi->select(table => 'table1')->all, []);
2443

            
2444

            
2445
test 'model delete and id option';
2446
$dbi = MyDBI6->connect;
2447
eval { $dbi->execute('drop table table1') };
2448
eval { $dbi->execute('drop table table2') };
2449
eval { $dbi->execute('drop table table3') };
2450
$dbi->execute($create_table1_2);
2451
$dbi->execute($create_table2_2);
2452
$dbi->execute($create_table3);
2453
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2454
$dbi->model('table1')->delete(id => [1, 2]);
2455
is_deeply($dbi->select(table => 'table1')->all, []);
2456
$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
2457
$dbi->model('table1_1')->delete(id => [1, 2]);
2458
is_deeply($dbi->select(table => 'table1')->all, []);
2459
$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
2460
$dbi->model('table1_3')->delete(id => [1, 2]);
2461
is_deeply($dbi->select(table => 'table1')->all, []);
2462

            
2463

            
2464
test 'select and id option';
2465
$dbi = DBIx::Custom->connect;
2466
eval { $dbi->execute('drop table table1') };
2467
$dbi->execute($create_table1_2);
2468
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2469
$result = $dbi->select(
2470
    table => 'table1',
2471
    primary_key => ['key1', 'key2'],
2472
    id => [1, 2]
2473
);
2474
$row = $result->one;
2475
is($row->{key1}, 1);
2476
is($row->{key2}, 2);
2477
is($row->{key3}, 3);
2478

            
2479
$dbi->delete_all(table => 'table1');
2480
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2481
$result = $dbi->select(
2482
    table => 'table1',
2483
    primary_key => 'key1',
2484
    id => 0,
2485
);
2486
$row = $result->one;
2487
is($row->{key1}, 0);
2488
is($row->{key2}, 2);
2489
is($row->{key3}, 3);
2490

            
2491
$dbi->delete_all(table => 'table1');
2492
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2493
$result = $dbi->select(
2494
    table => 'table1',
2495
    primary_key => ['key1', 'key2'],
2496
    id => [1, 2]
2497
);
2498
$row = $result->one;
2499
is($row->{key1}, 1);
2500
is($row->{key2}, 2);
2501
is($row->{key3}, 3);
2502

            
2503

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

            
2515
test 'column separator is default .';
2516
$dbi = MyDBI7->connect;
2517
eval { $dbi->execute('drop table table1') };
2518
eval { $dbi->execute('drop table table2') };
2519
$dbi->execute($create_table1);
2520
$dbi->execute($create_table2);
2521
$dbi->setup_model;
2522
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2523
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2524
$model = $dbi->model('table1');
2525
$result = $model->select(
2526
    column => [$model->column('table2')],
2527
    where => {'table1.key1' => 1}
2528
);
2529
is_deeply($result->one,
2530
          {'table2.key1' => 1, 'table2.key3' => 3});
2531

            
2532
$result = $model->select(
2533
    column => [$model->column('table2' => [qw/key1 key3/])],
2534
    where => {'table1.key1' => 1}
2535
);
2536
is_deeply($result->one,
2537
          {'table2.key1' => 1, 'table2.key3' => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
2538

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2539
test 'separator';
2540
$dbi = DBIx::Custom->connect;
2541
eval { $dbi->execute('drop table table1') };
2542
eval { $dbi->execute('drop table table2') };
2543
$dbi->execute($create_table1);
2544
$dbi->execute($create_table2);
test cleanup
Yuki Kimoto authored on 2011-08-10
2545

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2546
$dbi->create_model(
2547
    table => 'table1',
2548
    join => [
2549
       'left outer join table2 on table1.key1 = table2.key1'
2550
    ],
2551
    primary_key => ['key1'],
2552
);
2553
$model2 = $dbi->create_model(
2554
    table => 'table2',
2555
);
2556
$dbi->setup_model;
2557
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2558
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2559
$model = $dbi->model('table1');
2560
$result = $model->select(
2561
    column => [
2562
        $model->mycolumn,
2563
        {table2 => [qw/key1 key3/]}
2564
    ],
2565
    where => {'table1.key1' => 1}
2566
);
2567
is_deeply($result->one,
2568
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
2569
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
2570

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2571
$dbi->separator('__');
2572
$model = $dbi->model('table1');
2573
$result = $model->select(
2574
    column => [
2575
        $model->mycolumn,
2576
        {table2 => [qw/key1 key3/]}
2577
    ],
2578
    where => {'table1.key1' => 1}
2579
);
2580
is_deeply($result->one,
2581
          {key1 => 1, key2 => 2, 'table2__key1' => 1, 'table2__key3' => 3});
2582
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
2583

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2584
$dbi->separator('-');
2585
$model = $dbi->model('table1');
2586
$result = $model->select(
2587
    column => [
2588
        $model->mycolumn,
2589
        {table2 => [qw/key1 key3/]}
2590
    ],
2591
    where => {'table1.key1' => 1}
2592
);
2593
is_deeply($result->one,
2594
          {key1 => 1, key2 => 2, 'table2-key1' => 1, 'table2-key3' => 3});
2595
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
test cleanup
Yuki Kimoto authored on 2011-08-10
2596

            
2597

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2598
test 'filter_off';
2599
$dbi = DBIx::Custom->connect;
2600
eval { $dbi->execute('drop table table1') };
2601
eval { $dbi->execute('drop table table2') };
2602
$dbi->execute($create_table1);
2603
$dbi->execute($create_table2);
test cleanup
Yuki Kimoto authored on 2011-08-10
2604

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2605
$dbi->create_model(
2606
    table => 'table1',
2607
    join => [
2608
       'left outer join table2 on table1.key1 = table2.key1'
2609
    ],
2610
    primary_key => ['key1'],
2611
);
2612
$dbi->setup_model;
2613
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2614
$model = $dbi->model('table1');
2615
$result = $model->select(column => 'key1');
2616
$result->filter(key1 => sub { $_[0] * 2 });
2617
is_deeply($result->one, {key1 => 2});
test cleanup
Yuki Kimoto authored on 2011-08-10
2618

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2619
test 'available_datetype';
2620
$dbi = DBIx::Custom->connect;
2621
ok($dbi->can('available_datatype'));
test cleanup
Yuki Kimoto authored on 2011-08-10
2622

            
2623

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2624
test 'select prefix option';
2625
$dbi = DBIx::Custom->connect;
2626
eval { $dbi->execute('drop table table1') };
2627
$dbi->execute($create_table1);
2628
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2629
$rows = $dbi->select(prefix => 'key1,', column => 'key2', table => 'table1')->all;
2630
is_deeply($rows, [{key1 => 1, key2 => 2}], "table");
test cleanup
Yuki Kimoto authored on 2011-08-10
2631

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2632
test 'map_param';
2633
$dbi = DBIx::Custom->connect;
2634
$param = $dbi->map_param(
2635
    {id => 1, author => 'Ken', price => 1900},
2636
    id => 'book.id',
2637
    author => ['book.author', sub { '%' . $_[0] . '%' }],
2638
    price => ['book.price', {if => sub { $_[0] eq 1900 }}]
2639
);
2640
is_deeply($param, {'book.id' => 1, 'book.author' => '%Ken%',
2641
  'book.price' => 1900});
test cleanup
Yuki Kimoto authored on 2011-08-10
2642

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2643
$param = $dbi->map_param(
2644
    {id => 0, author => 0, price => 0},
2645
    id => 'book.id',
2646
    author => ['book.author', sub { '%' . $_[0] . '%' }],
2647
    price => ['book.price', sub { '%' . $_[0] . '%' },
2648
      {if => sub { $_[0] eq 0 }}]
2649
);
2650
is_deeply($param, {'book.id' => 0, 'book.author' => '%0%', 'book.price' => '%0%'});
test cleanup
Yuki Kimoto authored on 2011-08-10
2651

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2652
$param = $dbi->map_param(
2653
    {id => '', author => '', price => ''},
2654
    id => 'book.id',
2655
    author => ['book.author', sub { '%' . $_[0] . '%' }],
2656
    price => ['book.price', sub { '%' . $_[0] . '%' },
2657
      {if => sub { $_[0] eq 1 }}]
2658
);
2659
is_deeply($param, {});
test cleanup
Yuki Kimoto authored on 2011-08-10
2660

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2661
$param = $dbi->map_param(
2662
    {id => undef, author => undef, price => undef},
2663
    id => 'book.id',
2664
    price => ['book.price', {if => 'exists'}]
2665
);
2666
is_deeply($param, {'book.price' => undef});
test cleanup
Yuki Kimoto authored on 2011-08-10
2667

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2668
$param = $dbi->map_param(
2669
    {price => 'a'},
2670
    id => ['book.id', {if => 'exists'}],
2671
    price => ['book.price', sub { '%' . $_[0] }, {if => 'exists'}]
2672
);
2673
is_deeply($param, {'book.price' => '%a'});
test cleanup
Yuki Kimoto authored on 2011-08-10
2674

            
test cleanup
Yuki Kimoto authored on 2011-08-10
2675
test 'order';
2676
$dbi = DBIx::Custom->connect;
2677
eval { $dbi->execute('drop table table1') };
2678
$dbi->execute($create_table1);
2679
$dbi->insert({key1 => 1, key2 => 1}, table => 'table1');
2680
$dbi->insert({key1 => 1, key2 => 3}, table => 'table1');
2681
$dbi->insert({key1 => 2, key2 => 2}, table => 'table1');
2682
$dbi->insert({key1 => 2, key2 => 4}, table => 'table1');
2683
my $order = $dbi->order;
2684
$order->prepend('key1', 'key2 desc');
2685
$result = $dbi->select(table => 'table1', append => "$order");
2686
is_deeply($result->all, [{key1 => 1, key2 => 3}, {key1 => 1, key2 => 1},
2687
  {key1 => 2, key2 => 4}, {key1 => 2, key2 => 2}]);
2688
$order->prepend('key1 desc');
2689
$result = $dbi->select(table => 'table1', append => "$order");
2690
is_deeply($result->all, [{key1 => 2, key2 => 4}, {key1 => 2, key2 => 2},
2691
  {key1 => 1, key2 => 3}, {key1 => 1, key2 => 1}]);
2692

            
2693
$order = $dbi->order;
2694
$order->prepend(['table1-key1'], [qw/table1-key2 desc/]);
2695
$result = $dbi->select(table => 'table1',
2696
  column => [[key1 => 'table1-key1'], [key2 => 'table1-key2']],
2697
  append => "$order");
2698
is_deeply($result->all, [{'table1-key1' => 1, 'table1-key2' => 3},
2699
  {'table1-key1' => 1, 'table1-key2' => 1},
2700
  {'table1-key1' => 2, 'table1-key2' => 4},
2701
  {'table1-key1' => 2, 'table1-key2' => 2}]);
2702

            
2703
test 'tag_parse';
2704
$dbi = DBIx::Custom->connect;
2705
$dbi->tag_parse(0);
2706
eval { $dbi->execute('drop table table1') };
2707
$dbi->execute($create_table1);
2708
$dbi->insert({key1 => 1, key2 => 1}, table => 'table1');
2709
eval {$dbi->execute("select * from table1 where {= key1}", {key1 => 1})};
2710
ok($@);
2711

            
2712
test 'last_sql';
2713
$dbi = DBIx::Custom->connect;
2714
eval { $dbi->execute('drop table table1') };
2715
$dbi->execute($create_table1);
2716
$dbi->execute('select * from table1');
2717
is($dbi->last_sql, 'select * from table1;');
2718

            
2719
eval{$dbi->execute("aaa")};
2720
is($dbi->last_sql, 'aaa;');
2721

            
2722
test 'DBIx::Custom header';
2723
$dbi = DBIx::Custom->connect;
2724
eval { $dbi->execute('drop table table1') };
2725
$dbi->execute($create_table1);
2726
$result = $dbi->execute('select key1 as h1, key2 as h2 from table1');
2727
is_deeply($result->header, [qw/h1 h2/]);
2728

            
2729
test 'Named placeholder :name(operater) syntax';
2730
$dbi->execute('drop table table1');
2731
$dbi->execute($create_table1_2);
2732
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2733
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2734

            
2735
$source = "select * from table1 where :key1{=} and :key2{=}";
2736
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
2737
$rows = $result->all;
2738
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
2739

            
2740
$source = "select * from table1 where :key1{ = } and :key2{=}";
2741
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
2742
$rows = $result->all;
2743
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
2744

            
2745
$source = "select * from table1 where :key1{<} and :key2{=}";
2746
$result = $dbi->execute($source, param => {key1 => 5, key2 => 2});
2747
$rows = $result->all;
2748
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
2749

            
2750
$source = "select * from table1 where :table1.key1{=} and :table1.key2{=}";
2751
$result = $dbi->execute(
2752
    $source,
2753
    param => {'table1.key1' => 1, 'table1.key2' => 1},
2754
    filter => {'table1.key2' => sub { $_[0] * 2 }}
2755
);
2756
$rows = $result->all;
2757
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
2758

            
2759
test 'high perfomance way';
2760
$dbi->execute('drop table table1');
2761
$dbi->execute($create_table1_highperformance);
2762
$rows = [
2763
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
2764
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
2765
];
2766
{
2767
    my $query;
2768
    foreach my $row (@$rows) {
2769
      $query ||= $dbi->insert($row, table => 'table1', query => 1);
2770
      $dbi->execute($query, $row, filter => {ab => sub { $_[0] * 2 }});
2771
    }
2772
    is_deeply($dbi->select(table => 'table1')->all,
2773
      [
2774
          {ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
2775
          {ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
2776
      ]
2777
    );
2778
}
2779

            
2780
$dbi->execute('drop table table1');
2781
$dbi->execute($create_table1_highperformance);
2782
$rows = [
2783
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
2784
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
2785
];
2786
{
2787
    my $query;
2788
    my $sth;
2789
    foreach my $row (@$rows) {
2790
      $query ||= $dbi->insert($row, table => 'table1', query => 1);
2791
      $sth ||= $query->sth;
2792
      $sth->execute(map { $row->{$_} } sort keys %$row);
2793
    }
2794
    is_deeply($dbi->select(table => 'table1')->all,
2795
      [
2796
          {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
2797
          {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
2798
      ]
2799
    );
2800
}
2801

            
2802
test 'result';
2803
$dbi = DBIx::Custom->connect;
2804
eval { $dbi->execute('drop table table1') };
2805
$dbi->execute($create_table1);
2806
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
2807
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
2808

            
2809
$result = $dbi->select(table => 'table1');
2810
@rows = ();
2811
while (my $row = $result->fetch) {
2812
    push @rows, [@$row];
2813
}
2814
is_deeply(\@rows, [[1, 2], [3, 4]]);
2815

            
2816
$result = $dbi->select(table => 'table1');
2817
@rows = ();
2818
while (my $row = $result->fetch_hash) {
2819
    push @rows, {%$row};
2820
}
2821
is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
2822

            
2823
$dbi = DBIx::Custom->connect;
2824
eval { $dbi->execute('drop table table1') };
2825
$dbi->execute($create_table1);
2826
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
2827
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
2828

            
2829
test 'fetch_all';
2830
$result = $dbi->select(table => 'table1');
2831
$rows = $result->fetch_all;
2832
is_deeply($rows, [[1, 2], [3, 4]]);
2833

            
2834
$result = $dbi->select(table => 'table1');
2835
$rows = $result->fetch_hash_all;
2836
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
2837

            
2838
$result = $dbi->select(table => 'table1');
2839
$result->dbi->filters({three_times => sub { $_[0] * 3}});
2840
$result->filter({key1 => 'three_times'});
2841

            
2842
$rows = $result->fetch_all;
2843
is_deeply($rows, [[3, 2], [9, 4]], "array");
2844

            
2845
$result = $dbi->select(table => 'table1');
2846
$result->dbi->filters({three_times => sub { $_[0] * 3}});
2847
$result->filter({key1 => 'three_times'});
2848
$rows = $result->fetch_hash_all;
2849
is_deeply($rows, [{key1 => 3, key2 => 2}, {key1 => 9, key2 => 4}], "hash");
2850

            
2851
test "query_builder";
2852
$datas = [
2853
    # Basic tests
2854
    {   name            => 'placeholder basic',
2855
        source            => "a {?  k1} b {=  k2} {<> k3} {>  k4} {<  k5} {>= k6} {<= k7} {like k8}", ,
2856
        sql_expected    => "a ? b k2 = ? k3 <> ? k4 > ? k5 < ? k6 >= ? k7 <= ? k8 like ?;",
2857
        columns_expected   => [qw/k1 k2 k3 k4 k5 k6 k7 k8/]
2858
    },
2859
    {
2860
        name            => 'placeholder in',
2861
        source            => "{in k1 3};",
2862
        sql_expected    => "k1 in (?, ?, ?);",
2863
        columns_expected   => [qw/k1 k1 k1/]
2864
    },
2865
    
2866
    # Table name
2867
    {
2868
        name            => 'placeholder with table name',
2869
        source            => "{= a.k1} {= a.k2}",
2870
        sql_expected    => "a.k1 = ? a.k2 = ?;",
2871
        columns_expected  => [qw/a.k1 a.k2/]
2872
    },
2873
    {   
2874
        name            => 'placeholder in with table name',
2875
        source            => "{in a.k1 2} {in b.k2 2}",
2876
        sql_expected    => "a.k1 in (?, ?) b.k2 in (?, ?);",
2877
        columns_expected  => [qw/a.k1 a.k1 b.k2 b.k2/]
2878
    },
2879
    {
2880
        name            => 'not contain tag',
2881
        source            => "aaa",
2882
        sql_expected    => "aaa;",
2883
        columns_expected  => [],
2884
    }
2885
];
2886

            
2887
for (my $i = 0; $i < @$datas; $i++) {
2888
    my $data = $datas->[$i];
2889
    my $builder = DBIx::Custom->new->query_builder;
2890
    my $query = $builder->build_query($data->{source});
2891
    is($query->{sql}, $data->{sql_expected}, "$data->{name} : sql");
2892
    is_deeply($query->columns, $data->{columns_expected}, "$data->{name} : columns");
2893
}
2894

            
2895
$builder = DBIx::Custom->new->query_builder;
2896
$ret_val = $builder->register_tag(
2897
    p => sub {
2898
        my @args = @_;
2899
        
2900
        my $expand    = "? $args[0] $args[1]";
2901
        my $columns = [2];
2902
        return [$expand, $columns];
2903
    }
2904
);
2905

            
2906
$query = $builder->build_query("{p a b}");
2907
is($query->{sql}, "? a b;", "register_tag sql");
2908
is_deeply($query->{columns}, [2], "register_tag columns");
2909
isa_ok($ret_val, 'DBIx::Custom::QueryBuilder');
2910

            
2911
$builder = DBIx::Custom->new->query_builder;
2912

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

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

            
2919
$builder->register_tag({
2920
    q => 'string'
2921
});
2922

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

            
2926
$builder->register_tag({
2927
   r => sub {} 
2928
});
2929

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

            
2933
$builder->register_tag({
2934
   s => sub { return ["a", ""]} 
2935
});
2936

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

            
2940
$builder->register_tag(
2941
    t => sub {return ["a", []]}
2942
);
2943

            
2944

            
2945
test 'General error case';
2946
$builder = DBIx::Custom->new->query_builder;
2947
$builder->register_tag(
2948
    a => sub {
2949
        return ["? ? ?", ['']];
2950
    }
2951
);
2952
eval{$builder->build_query("{a}")};
2953
like($@, qr/\QPlaceholder count/, "placeholder count is invalid");
2954

            
2955

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

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

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

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

            
2971
test 'variouse source';
2972
$source = "a {= b} c \\{ \\} {= \\{} {= \\}} d;";
2973
$query = $builder->build_query($source);
2974
is($query->sql, 'a b = ? c { } { = ? } = ? d;', "basic : 1");
2975

            
2976
$source = "abc;";
2977
$query = $builder->build_query($source);
2978
is($query->sql, 'abc;', "basic : 2");
2979

            
2980
$source = "{= a}";
2981
$query = $builder->build_query($source);
2982
is($query->sql, 'a = ?;', "only tag");
2983

            
2984
$source = "000;";
2985
$query = $builder->build_query($source);
2986
is($query->sql, '000;', "contain 0 value");
2987

            
2988
$source = "a {= b} }";
2989
eval{$builder->build_query($source)};
2990
like($@, qr/unexpected "}"/, "error : 1");
2991

            
2992
$source = "a {= {}";
2993
eval{$builder->build_query($source)};
2994
like($@, qr/unexpected "{"/, "error : 2");
2995

            
cleanup test
Yuki Kimoto authored on 2011-08-10
2996
test 'select() wrap option';
2997
$dbi = DBIx::Custom->connect;
2998
eval { $dbi->execute('drop table table1') };
2999
$dbi->execute($create_table1);
3000
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3001
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
3002
$rows = $dbi->select(
3003
    table => 'table1',
3004
    column => 'key1',
3005
    wrap => ['select * from (', ') as t where key1 = 1']
3006
)->all;
3007
is_deeply($rows, [{key1 => 1}]);
3008

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3009
eval {
3010
$dbi->select(
3011
    table => 'table1',
3012
    column => 'key1',
3013
    wrap => 'select * from ('
3014
)
3015
};
3016
like($@, qr/array/);
3017

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3018
test 'dbi method from model';
3019
$dbi = MyDBI9->connect;
3020
eval { $dbi->execute('drop table table1') };
3021
$dbi->execute($create_table1);
3022
$dbi->setup_model;
3023
$model = $dbi->model('table1');
3024
eval{$model->execute('select * from table1')};
3025
ok(!$@);
3026

            
3027
test 'column table option';
3028
$dbi = MyDBI9->connect;
3029
eval { $dbi->execute('drop table table1') };
3030
$dbi->execute($create_table1);
3031
eval { $dbi->execute('drop table table2') };
3032
$dbi->execute($create_table2);
3033
$dbi->setup_model;
3034
$dbi->execute('insert into table1 (key1, key2) values (1, 2);');
3035
$dbi->execute('insert into table2 (key1, key3) values (1, 4);');
3036
$model = $dbi->model('table1');
3037
$result = $model->select(
3038
    column => [
3039
        $model->column('table2', {alias => 'table2_alias'})
3040
    ],
3041
    where => {'table2_alias.key3' => 4}
3042
);
3043
is_deeply($result->one, 
3044
          {'table2_alias.key1' => 1, 'table2_alias.key3' => 4});
3045

            
3046
$dbi->separator('__');
3047
$result = $model->select(
3048
    column => [
3049
        $model->column('table2', {alias => 'table2_alias'})
3050
    ],
3051
    where => {'table2_alias.key3' => 4}
3052
);
3053
is_deeply($result->one, 
3054
          {'table2_alias__key1' => 1, 'table2_alias__key3' => 4});
3055

            
3056
$dbi->separator('-');
3057
$result = $model->select(
3058
    column => [
3059
        $model->column('table2', {alias => 'table2_alias'})
3060
    ],
3061
    where => {'table2_alias.key3' => 4}
3062
);
3063
is_deeply($result->one, 
3064
          {'table2_alias-key1' => 1, 'table2_alias-key3' => 4});
3065

            
3066
test 'create_model';
3067
$dbi = DBIx::Custom->connect;
3068
eval { $dbi->execute('drop table table1') };
3069
eval { $dbi->execute('drop table table2') };
3070
$dbi->execute($create_table1);
3071
$dbi->execute($create_table2);
3072

            
3073
$dbi->create_model(
3074
    table => 'table1',
3075
    join => [
3076
       'left outer join table2 on table1.key1 = table2.key1'
3077
    ],
3078
    primary_key => ['key1']
3079
);
3080
$model2 = $dbi->create_model(
3081
    table => 'table2'
3082
);
3083
$dbi->create_model(
3084
    table => 'table3',
3085
    filter => [
3086
        key1 => {in => sub { uc $_[0] }}
3087
    ]
3088
);
3089
$dbi->setup_model;
3090
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3091
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
3092
$model = $dbi->model('table1');
3093
$result = $model->select(
3094
    column => [$model->mycolumn, $model->column('table2')],
3095
    where => {'table1.key1' => 1}
3096
);
3097
is_deeply($result->one,
3098
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
3099
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
3100

            
3101
test 'model method';
3102
$dbi = DBIx::Custom->connect;
3103
eval { $dbi->execute('drop table table2') };
3104
$dbi->execute($create_table2);
3105
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
3106
$model = $dbi->create_model(
3107
    table => 'table2'
3108
);
3109
$model->method(foo => sub { shift->select(@_) });
3110
is_deeply($model->foo->one, {key1 => 1, key3 => 3});
3111

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3112
test 'update_param';
3113
$dbi = DBIx::Custom->connect;
3114
eval { $dbi->execute('drop table table1') };
3115
$dbi->execute($create_table1_2);
3116
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3117
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3118

            
3119
$param = {key2 => 11};
3120
$update_param = $dbi->update_param($param);
3121
$sql = <<"EOS";
3122
update table1 $update_param
3123
where key1 = 1
3124
EOS
3125
$dbi->execute($sql, param => $param);
3126
$result = $dbi->execute('select * from table1 order by key1;', table => 'table1');
3127
$rows   = $result->all;
3128
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
3129
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
3130
                  "basic");
3131

            
3132

            
3133
$dbi = DBIx::Custom->connect;
3134
eval { $dbi->execute('drop table table1') };
3135
$dbi->execute($create_table1_2);
3136
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3137
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3138

            
3139
$param = {key2 => 11, key3 => 33};
3140
$update_param = $dbi->update_param($param);
3141
$sql = <<"EOS";
3142
update table1 $update_param
3143
where key1 = 1
3144
EOS
3145
$dbi->execute($sql, param => $param);
3146
$result = $dbi->execute('select * from table1 order by key1;', table => 'table1');
3147
$rows   = $result->all;
3148
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 33, key4 => 4, key5 => 5},
3149
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
3150
                  "basic");
3151

            
3152
$dbi = DBIx::Custom->connect;
3153
eval { $dbi->execute('drop table table1') };
3154
$dbi->execute($create_table1_2);
3155
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3156
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3157

            
3158
$param = {key2 => 11, key3 => 33};
3159
$update_param = $dbi->update_param($param, {no_set => 1});
3160
$sql = <<"EOS";
3161
update table1 set $update_param
3162
where key1 = 1
3163
EOS
3164
$dbi->execute($sql, param => $param);
3165
$result = $dbi->execute('select * from table1 order by key1;', table => 'table1');
3166
$rows   = $result->all;
3167
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 33, key4 => 4, key5 => 5},
3168
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
3169
                  "update param no_set");
3170

            
3171
            
3172
eval { $dbi->update_param({";" => 1}) };
3173
like($@, qr/not safety/);
3174

            
3175

            
3176
test 'update_param';
3177
$dbi = DBIx::Custom->connect;
3178
eval { $dbi->execute('drop table table1') };
3179
$dbi->execute($create_table1_2);
3180
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3181
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3182

            
3183
$param = {key2 => 11};
3184
$update_param = $dbi->assign_param($param);
3185
$sql = <<"EOS";
3186
update table1 set $update_param
3187
where key1 = 1
3188
EOS
3189
$dbi->execute($sql, param => $param, table => 'table1');
3190
$result = $dbi->execute('select * from table1 order by key1;');
3191
$rows   = $result->all;
3192
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
3193
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
3194
                  "basic");
3195

            
cleanup test
Yuki Kimoto authored on 2011-08-10
3196
test 'join';
3197
$dbi = DBIx::Custom->connect;
3198
eval { $dbi->execute('drop table table1') };
3199
$dbi->execute($create_table1);
3200
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3201
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
3202
eval { $dbi->execute('drop table table2') };
3203
$dbi->execute($create_table2);
3204
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
3205
eval { $dbi->execute('drop table table3') };
3206
$dbi->execute('create table table3 (key3 int, key4 int);');
3207
$dbi->insert(table => 'table3', param => {key3 => 5, key4 => 4});
3208
$rows = $dbi->select(
3209
    table => 'table1',
3210
    column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
3211
    where   => {'table1.key2' => 2},
3212
    join  => ['left outer join table2 on table1.key1 = table2.key1']
3213
)->all;
3214
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}]);
3215

            
3216
$rows = $dbi->select(
3217
    table => 'table1',
3218
    where   => {'key1' => 1},
3219
    join  => ['left outer join table2 on table1.key1 = table2.key1']
3220
)->all;
3221
is_deeply($rows, [{key1 => 1, key2 => 2}]);
3222

            
3223
eval {
3224
    $rows = $dbi->select(
3225
        table => 'table1',
3226
        column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
3227
        where   => {'table1.key2' => 2},
3228
        join  => {'table1.key1' => 'table2.key1'}
3229
    );
3230
};
3231
like ($@, qr/array/);
3232

            
3233
$rows = $dbi->select(
3234
    table => 'table1',
3235
    where   => {'key1' => 1},
3236
    join  => ['left outer join table2 on table1.key1 = table2.key1',
3237
              'left outer join table3 on table2.key3 = table3.key3']
3238
)->all;
3239
is_deeply($rows, [{key1 => 1, key2 => 2}]);
3240

            
3241
$rows = $dbi->select(
3242
    column => 'table3.key4 as table3__key4',
3243
    table => 'table1',
3244
    where   => {'table1.key1' => 1},
3245
    join  => ['left outer join table2 on table1.key1 = table2.key1',
3246
              'left outer join table3 on table2.key3 = table3.key3']
3247
)->all;
3248
is_deeply($rows, [{table3__key4 => 4}]);
3249

            
3250
$rows = $dbi->select(
3251
    column => 'table1.key1 as table1__key1',
3252
    table => 'table1',
3253
    where   => {'table3.key4' => 4},
3254
    join  => ['left outer join table2 on table1.key1 = table2.key1',
3255
              'left outer join table3 on table2.key3 = table3.key3']
3256
)->all;
3257
is_deeply($rows, [{table1__key1 => 1}]);
3258

            
3259
$dbi = DBIx::Custom->connect;
3260
eval { $dbi->execute('drop table table1') };
3261
$dbi->execute($create_table1);
3262
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3263
eval { $dbi->execute('drop table table2') };
3264
$dbi->execute($create_table2);
3265
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
3266
$rows = $dbi->select(
3267
    table => 'table1',
3268
    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",
3269
    where   => {'table1.key2' => 2},
3270
    join  => ["left outer join ${q}table2$p on ${q}table1$p.${q}key1$p = ${q}table2$p.${q}key1$p"],
3271
)->all;
3272
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}],
3273
          'quote');
3274

            
3275

            
3276
$dbi = DBIx::Custom->connect;
3277
eval { $dbi->execute('drop table table1') };
3278
$dbi->execute($create_table1);
3279
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3280
$sql = <<"EOS";
3281
left outer join (
3282
  select * from table1 as t1
3283
  where t1.key2 = (
3284
    select max(t2.key2) from table1 as t2
3285
    where t1.key1 = t2.key1
3286
  )
3287
) as latest_table1 on table1.key1 = latest_table1.key1
3288
EOS
3289
$join = [$sql];
3290
$rows = $dbi->select(
3291
    table => 'table1',
3292
    column => 'latest_table1.key1 as latest_table1__key1',
3293
    join  => $join
3294
)->all;
3295
is_deeply($rows, [{latest_table1__key1 => 1}]);
3296

            
3297
$dbi = DBIx::Custom->connect;
3298
eval { $dbi->execute('drop table table1') };
3299
eval { $dbi->execute('drop table table2') };
3300
$dbi->execute($create_table1);
3301
$dbi->execute($create_table2);
3302
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3303
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
3304
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
3305
$result = $dbi->select(
3306
    table => 'table1',
3307
    join => [
3308
        "left outer join table2 on table2.key2 = '4' and table1.key1 = table2.key1"
3309
    ]
3310
);
3311
is_deeply($result->all, [{key1 => 1, key2 => 2}]);
3312
$result = $dbi->select(
3313
    table => 'table1',
3314
    column => [{table2 => ['key3']}],
3315
    join => [
3316
        "left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1"
3317
    ]
3318
);
3319
is_deeply($result->all, [{'table2.key3' => 4}]);
3320
$result = $dbi->select(
3321
    table => 'table1',
3322
    column => [{table2 => ['key3']}],
3323
    join => [
3324
        "left outer join table2 on table1.key1 = table2.key1 and table2.key3 = '4'"
3325
    ]
3326
);
3327
is_deeply($result->all, [{'table2.key3' => 4}]);
3328

            
3329
$dbi = DBIx::Custom->connect;
3330
eval { $dbi->execute('drop table table1') };
3331
eval { $dbi->execute('drop table table2') };
3332
$dbi->execute($create_table1);
3333
$dbi->execute($create_table2);
3334
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3335
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
3336
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
3337
$result = $dbi->select(
3338
    table => 'table1',
3339
    column => [{table2 => ['key3']}],
3340
    join => [
3341
        {
3342
            clause => "left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1",
3343
            table => ['table1', 'table2']
3344
        }
3345
    ]
3346
);
3347
is_deeply($result->all, [{'table2.key3' => 4}]);
3348

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

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

            
3351

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