Newer Older
3750 lines | 123.351kb
added common test executing ...
Yuki Kimoto authored on 2011-08-07
1
use Test::More;
2
use strict;
3
use warnings;
4
use utf8;
5
use Encode qw/encode_utf8 decode_utf8/;
6
use FindBin;
7
use lib "$FindBin::Bin/basic";
8

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

            
15
    plan 'no_plan';
16
    use_ok('DBIx::Custom');
17
}
18

            
19
$SIG{__WARN__} = sub { warn $_[0] unless $_[0] =~ /DEPRECATED/};
20
sub test { print "# $_[0]\n" }
21

            
22
# Constant
23
my %memory = (dsn => 'dbi:SQLite:dbname=:memory:');
24
my $create_table_default = 'create table table1 (key1 char(255), key2 char(255));';
25

            
26
# Variables
27
my $builder;
28
my $datas;
29
my $dbi;
30
my $sth;
31
my $source;
32
my @sources;
33
my $select_source;
34
my $insert_source;
35
my $update_source;
36
my $param;
37
my $params;
38
my $sql;
39
my $result;
40
my $row;
41
my @rows;
42
my $rows;
43
my $query;
44
my @queries;
45
my $select_query;
46
my $insert_query;
47
my $update_query;
48
my $ret_val;
49
my $infos;
50
my $model;
51
my $model2;
52
my $where;
53
my $update_param;
54
my $insert_param;
55
my $join;
56

            
57
# Prepare table
58
$dbi = DBIx::Custom->connect(%memory);
59
$dbi->execute($create_table_default);
60
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
61
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
62

            
63
test 'DBIx::Custom::Result test';
64
$source = "select key1, key2 from table1";
65
$query = $dbi->create_query($source);
66
$result = $dbi->execute($query);
67

            
68
@rows = ();
69
while (my $row = $result->fetch) {
70
    push @rows, [@$row];
71
}
72
is_deeply(\@rows, [[1, 2], [3, 4]], "fetch");
73

            
74
$result = $dbi->execute($query);
75
@rows = ();
76
while (my $row = $result->fetch_hash) {
77
    push @rows, {%$row};
78
}
79
is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "fetch_hash");
80

            
81
$result = $dbi->execute($query);
82
$rows = $result->fetch_all;
83
is_deeply($rows, [[1, 2], [3, 4]], "fetch_all");
84

            
85
$result = $dbi->execute($query);
86
$rows = $result->fetch_hash_all;
87
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "all");
88

            
89
test 'Insert query return value';
90
$dbi->execute('drop table table1');
91
$dbi->execute($create_table_default);
92
$source = "insert into table1 {insert_param key1 key2}";
93
$query = $dbi->execute($source, {}, query => 1);
94
$ret_val = $dbi->execute($query, param => {key1 => 1, key2 => 2});
95
ok($ret_val);
96

            
97

            
98
test 'Direct query';
99
$dbi->execute('drop table table1');
100
$dbi->execute($create_table_default);
101
$insert_source = "insert into table1 {insert_param key1 key2}";
102
$dbi->execute($insert_source, param => {key1 => 1, key2 => 2});
103
$result = $dbi->execute('select * from table1;');
104
$rows = $result->all;
105
is_deeply($rows, [{key1 => 1, key2 => 2}]);
106

            
107
test 'Filter basic';
108
$dbi->execute('drop table table1');
109
$dbi->execute($create_table_default);
110
$dbi->register_filter(twice       => sub { $_[0] * 2}, 
111
                    three_times => sub { $_[0] * 3});
112

            
113
$insert_source  = "insert into table1 {insert_param key1 key2};";
114
$insert_query = $dbi->execute($insert_source, {}, query => 1);
115
$insert_query->filter({key1 => 'twice'});
116
$dbi->execute($insert_query, param => {key1 => 1, key2 => 2});
117
$result = $dbi->execute('select * from table1;');
118
$rows = $result->filter({key2 => 'three_times'})->all;
119
is_deeply($rows, [{key1 => 2, key2 => 6}], "filter fetch_filter");
120
$dbi->execute('drop table table1');
121

            
122
test 'Filter in';
123
$dbi->execute($create_table_default);
124
$insert_source  = "insert into table1 {insert_param key1 key2};";
125
$insert_query = $dbi->execute($insert_source, {}, query => 1);
126
$dbi->execute($insert_query, param => {key1 => 2, key2 => 4});
127
$select_source = "select * from table1 where {in table1.key1 2} and {in table1.key2 2}";
128
$select_query = $dbi->execute($select_source,{}, query => 1);
129
$select_query->filter({'table1.key1' => 'twice'});
130
$result = $dbi->execute($select_query, param => {'table1.key1' => [1,5], 'table1.key2' => [2,4]});
131
$rows = $result->all;
132
is_deeply($rows, [{key1 => 2, key2 => 4}], "filter");
133

            
134
test 'DBIx::Custom::SQLTemplate basic tag';
135
$dbi->execute('drop table table1');
136
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
137
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
138
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
139

            
140
$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";
141
$query = $dbi->execute($source, {}, query => 1);
142
$result = $dbi->execute($query, param => {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
143
$rows = $result->all;
144
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");
145

            
146
$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";
147
$query = $dbi->execute($source, {}, query => 1);
148
$result = $dbi->execute($query, {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
149
$rows = $result->all;
150
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");
151

            
152
$source = "select * from table1 where {<= key1} and {like key2};";
153
$query = $dbi->execute($source, {}, query => 1);
154
$result = $dbi->execute($query, param => {key1 => 1, key2 => '%2%'});
155
$rows = $result->all;
156
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag2");
157

            
158
test 'DIB::Custom::SQLTemplate in tag';
159
$dbi->execute('drop table table1');
160
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
161
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
162
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
163

            
164
$source = "select * from table1 where {in key1 2};";
165
$query = $dbi->execute($source, {}, query => 1);
166
$result = $dbi->execute($query, param => {key1 => [9, 1]});
167
$rows = $result->all;
168
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic");
169

            
170
test 'DBIx::Custom::SQLTemplate insert tag';
171
$dbi->execute("delete from table1");
172
$insert_source = 'insert into table1 {insert_param key1 key2 key3 key4 key5}';
173
$dbi->execute($insert_source, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
174

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

            
179
test 'DBIx::Custom::SQLTemplate update tag';
180
$dbi->execute("delete from table1");
181
$insert_source = "insert into table1 {insert_param key1 key2 key3 key4 key5}";
182
$dbi->execute($insert_source, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
183
$dbi->execute($insert_source, param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
184

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

            
188
$result = $dbi->execute('select * from table1;');
189
$rows = $result->all;
190
is_deeply($rows, [{key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5},
191
                  {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}], "basic");
192

            
193

            
194
test 'Named placeholder';
195
$dbi->execute('drop table table1');
196
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
197
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
198
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
199

            
200
$source = "select * from table1 where key1 = :key1 and key2 = :key2";
201
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
202
$rows = $result->all;
203
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
204

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

            
210
$source = "select * from table1 where key1 = :key1 or key1 = :key1";
211
$result = $dbi->execute($source, param => {key1 => [1, 2]});
212
$rows = $result->all;
213
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
214

            
215
$source = "select * from table1 where key1 = :table1.key1 and key2 = :table1.key2";
216
$result = $dbi->execute(
217
    $source,
218
    param => {'table1.key1' => 1, 'table1.key2' => 1},
219
    filter => {'table1.key2' => sub { $_[0] * 2 }}
220
);
221
$rows = $result->all;
222
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
223

            
224
$dbi->execute('drop table table1');
225
$dbi->execute($create_table_default);
226
$dbi->insert(table => 'table1', param => {key1 => '2011-10-14 12:19:18', key2 => 2});
227
$source = "select * from table1 where key1 = '2011-10-14 12:19:18' and key2 = :key2";
228
$result = $dbi->execute(
229
    $source,
230
    param => {'key2' => 2},
231
);
232

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

            
236
$dbi = DBIx::Custom->connect(%memory);
237
$dbi->execute($create_table_default);
238
$dbi->insert(table => 'table1', param => {key1 => 'a:b c:d', key2 => 2});
239
$source = "select * from table1 where key1 = 'a\\:b c\\:d' and key2 = :key2";
240
$result = $dbi->execute(
241
    $source,
242
    param => {'key2' => 2},
243
);
244
$rows = $result->all;
245
is_deeply($rows, [{key1 => 'a:b c:d', key2 => 2}]);
246

            
247

            
248
test 'Error case';
249
eval {DBIx::Custom->connect(dsn => 'dbi:SQLit')};
250
ok($@, "connect error");
251

            
252
$dbi = DBIx::Custom->connect(%memory);
253
eval{$dbi->execute("{p }", {}, query => 1)};
254
ok($@, "create_query invalid SQL template");
255

            
256
test 'insert';
257
$dbi = DBIx::Custom->connect(%memory);
258
$dbi->execute($create_table_default);
259
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
260
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
261
$result = $dbi->execute('select * from table1;');
262
$rows   = $result->all;
263
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
264

            
265
$dbi->execute('delete from table1');
266
$dbi->register_filter(
267
    twice       => sub { $_[0] * 2 },
268
    three_times => sub { $_[0] * 3 }
269
);
270
$dbi->default_bind_filter('twice');
271
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => 'three_times'});
272
$result = $dbi->execute('select * from table1;');
273
$rows   = $result->all;
274
is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
275
$dbi->default_bind_filter(undef);
276

            
277
$dbi->execute('drop table table1');
278
$dbi->execute($create_table_default);
279
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, append => '   ');
280
$rows = $dbi->select(table => 'table1')->all;
281
is_deeply($rows, [{key1 => 1, key2 => 2}], 'insert append');
282

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

            
286
eval{$dbi->insert(table => 'table', param => {';' => 1})};
287
like($@, qr/safety/);
288

            
289
$dbi = DBIx::Custom->connect(%memory);
290
$dbi->quote('"');
291
$dbi->execute('create table "table" ("select")');
292
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
293
$dbi->insert(table => 'table', param => {select => 1});
294
$result = $dbi->execute('select * from "table"');
295
$rows   = $result->all;
296
is_deeply($rows, [{select => 2}], "reserved word");
297

            
298
$dbi = DBIx::Custom->connect(%memory);
299
$dbi->execute($create_table_default);
300
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
301
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
302
$result = $dbi->execute('select * from table1;');
303
$rows   = $result->all;
304
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
305

            
306
$dbi = DBIx::Custom->connect(%memory);
307
$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");
308
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
309
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 4}, prefix => 'or replace');
310
$result = $dbi->execute('select * from table1;');
311
$rows   = $result->all;
312
is_deeply($rows, [{key1 => 1, key2 => 4}], "basic");
313

            
314
$dbi = DBIx::Custom->connect(%memory);
315
$dbi->execute($create_table_default);
316
$dbi->insert(table => 'table1', param => {key1 => \"'1'", key2 => 2});
317
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
318
$result = $dbi->execute('select * from table1;');
319
$rows   = $result->all;
320
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
321

            
322
test 'update';
323
$dbi = DBIx::Custom->connect(%memory);
324
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
325
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
326
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
327
$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1});
328
$result = $dbi->execute('select * from table1;');
329
$rows   = $result->all;
330
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
331
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
332
                  "basic");
333
                  
334
$dbi->execute("delete from table1");
335
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
336
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
337
$dbi->update(table => 'table1', param => {key2 => 12}, where => {key2 => 2, key3 => 3});
338
$result = $dbi->execute('select * from table1;');
339
$rows   = $result->all;
340
is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
341
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
342
                  "update key same as search key");
343

            
344
$dbi->update(table => 'table1', param => {key2 => [12]}, where => {key2 => 2, key3 => 3});
345
$result = $dbi->execute('select * from table1;');
346
$rows   = $result->all;
347
is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
348
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
349
                  "update key same as search key : param is array ref");
350

            
351
$dbi->execute("delete from table1");
352
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
353
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
354
$dbi->register_filter(twice => sub { $_[0] * 2 });
355
$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1},
356
              filter => {key2 => sub { $_[0] * 2 }});
357
$result = $dbi->execute('select * from table1;');
358
$rows   = $result->all;
359
is_deeply($rows, [{key1 => 1, key2 => 22, key3 => 3, key4 => 4, key5 => 5},
360
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
361
                  "filter");
362

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

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

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

            
371
$dbi = DBIx::Custom->connect(%memory);
372
$dbi->execute($create_table_default);
373
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
374
$where = $dbi->where;
375
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
376
$where->param({key1 => 1, key2 => 2});
377
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
378
$result = $dbi->select(table => 'table1');
379
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
380

            
381
$dbi = DBIx::Custom->connect(%memory);
382
$dbi->execute($create_table_default);
383
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
384
$dbi->update(
385
    table => 'table1',
386
    param => {key1 => 3},
387
    where => [
388
        ['and', 'key1 = :key1', 'key2 = :key2'],
389
        {key1 => 1, key2 => 2}
390
    ]
391
);
392
$result = $dbi->select(table => 'table1');
393
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
394

            
395
$dbi = DBIx::Custom->connect(%memory);
396
$dbi->execute($create_table_default);
397
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
398
$where = $dbi->where;
399
$where->clause(['and', 'key2 = :key2']);
400
$where->param({key2 => 2});
401
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
402
$result = $dbi->select(table => 'table1');
403
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
404

            
405
eval{$dbi->update(table => 'table1', param => {';' => 1})};
406
like($@, qr/safety/);
407

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

            
411
$dbi = DBIx::Custom->connect(%memory);
412
$dbi->quote('"');
413
$dbi->execute('create table "table" ("select", "update")');
414
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
415
$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
416
$dbi->insert(table => 'table', param => {select => 1});
417
$dbi->update(table => 'table', where => {select => 1}, param => {update => 2});
418
$result = $dbi->execute('select * from "table"');
419
$rows   = $result->all;
420
is_deeply($rows, [{select => 2, update => 6}], "reserved word");
421

            
422
eval {$dbi->update_all(table => 'table', param => {';' => 2}) };
423
like($@, qr/safety/);
424

            
425
$dbi = DBIx::Custom->connect(%memory);
426
$dbi->reserved_word_quote('"');
427
$dbi->execute('create table "table" ("select", "update")');
428
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
429
$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
430
$dbi->insert(table => 'table', param => {select => 1});
431
$dbi->update(table => 'table', where => {'table.select' => 1}, param => {update => 2});
432
$result = $dbi->execute('select * from "table"');
433
$rows   = $result->all;
434
is_deeply($rows, [{select => 2, update => 6}], "reserved word");
435

            
436
$dbi = DBIx::Custom->connect(%memory);
437
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
438
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
439
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
440
$dbi->update({key2 => 11}, table => 'table1', where => {key1 => 1});
441
$result = $dbi->execute('select * from table1;');
442
$rows   = $result->all;
443
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
444
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
445
                  "basic");
446

            
447
$dbi = DBIx::Custom->connect(%memory);
448
$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");
449
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
450
$dbi->update(table => 'table1', param => {key2 => 4},
451
  where => {key1 => 1}, prefix => 'or replace');
452
$result = $dbi->execute('select * from table1;');
453
$rows   = $result->all;
454
is_deeply($rows, [{key1 => 1, key2 => 4}], "basic");
455

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

            
467
test 'update_all';
468
$dbi = DBIx::Custom->connect(%memory);
469
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
470
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
471
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
472
$dbi->register_filter(twice => sub { $_[0] * 2 });
473
$dbi->update_all(table => 'table1', param => {key2 => 10}, filter => {key2 => 'twice'});
474
$result = $dbi->execute('select * from table1;');
475
$rows   = $result->all;
476
is_deeply($rows, [{key1 => 1, key2 => 20, key3 => 3, key4 => 4, key5 => 5},
477
                  {key1 => 6, key2 => 20, key3 => 8, key4 => 9, key5 => 10}],
478
                  "filter");
479

            
480

            
481
test 'delete';
482
$dbi = DBIx::Custom->connect(%memory);
483
$dbi->execute($create_table_default);
484
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
485
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
486
$dbi->delete(table => 'table1', where => {key1 => 1});
487
$result = $dbi->execute('select * from table1;');
488
$rows   = $result->all;
489
is_deeply($rows, [{key1 => 3, key2 => 4}], "basic");
490

            
491
$dbi->execute("delete from table1;");
492
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
493
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
494
$dbi->register_filter(twice => sub { $_[0] * 2 });
495
$dbi->delete(table => 'table1', where => {key2 => 1}, filter => {key2 => 'twice'});
496
$result = $dbi->execute('select * from table1;');
497
$rows   = $result->all;
498
is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
499

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

            
502
$dbi->delete_all(table => 'table1');
503
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
504
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
505
$dbi->delete(table => 'table1', where => {key1 => 1, key2 => 2});
506
$rows = $dbi->select(table => 'table1')->all;
507
is_deeply($rows, [{key1 => 3, key2 => 4}], "delete multi key");
508

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

            
512
$dbi = DBIx::Custom->connect(%memory);
513
$dbi->execute($create_table_default);
514
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
515
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
516
$where = $dbi->where;
517
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
518
$where->param({ke1 => 1, key2 => 2});
519
$dbi->delete(table => 'table1', where => $where);
520
$result = $dbi->select(table => 'table1');
521
is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
522

            
523
$dbi = DBIx::Custom->connect(%memory);
524
$dbi->execute($create_table_default);
525
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
526
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
527
$dbi->delete(
528
    table => 'table1',
529
    where => [
530
        ['and', 'key1 = :key1', 'key2 = :key2'],
531
        {ke1 => 1, key2 => 2}
532
    ]
533
);
534
$result = $dbi->select(table => 'table1');
535
is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
536

            
537
$dbi = DBIx::Custom->connect(%memory);
538
$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");
539
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
540
$dbi->delete(table => 'table1', where => {key1 => 1}, prefix => '    ');
541
$result = $dbi->execute('select * from table1;');
542
$rows   = $result->all;
543
is_deeply($rows, [], "basic");
544

            
545
test 'delete error';
546
$dbi = DBIx::Custom->connect(%memory);
547
$dbi->execute($create_table_default);
548
eval{$dbi->delete(table => 'table1')};
549
like($@, qr/"where" must be specified/,
550
         "where key-value pairs not specified");
551

            
552
eval{$dbi->delete(table => 'table1', where => {';' => 1})};
553
like($@, qr/safety/);
554

            
555
$dbi = DBIx::Custom->connect(%memory);
556
$dbi->quote('"');
557
$dbi->execute('create table "table" ("select", "update")');
558
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
559
$dbi->insert(table => 'table', param => {select => 1});
560
$dbi->delete(table => 'table', where => {select => 1});
561
$result = $dbi->execute('select * from "table"');
562
$rows   = $result->all;
563
is_deeply($rows, [], "reserved word");
564

            
565
test 'delete_all';
566
$dbi = DBIx::Custom->connect(%memory);
567
$dbi->execute($create_table_default);
568
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
569
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
570
$dbi->delete_all(table => 'table1');
571
$result = $dbi->execute('select * from table1;');
572
$rows   = $result->all;
573
is_deeply($rows, [], "basic");
574

            
575

            
576
test 'select';
577
$dbi = DBIx::Custom->connect(%memory);
578
$dbi->execute($create_table_default);
579
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
580
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
581
$rows = $dbi->select(table => 'table1')->all;
582
is_deeply($rows, [{key1 => 1, key2 => 2},
583
                  {key1 => 3, key2 => 4}], "table");
584

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

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

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

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

            
597
$dbi->register_filter(decrement => sub { $_[0] - 1 });
598
$rows = $dbi->select(table => 'table1', where => {key1 => 2}, filter => {key1 => 'decrement'})
599
            ->all;
600
is_deeply($rows, [{key1 => 1, key2 => 2}], "filter");
601

            
602
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
603
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
604
$rows = $dbi->select(
605
    table => [qw/table1 table2/],
606
    column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
607
    where   => {'table1.key2' => 2},
608
    relation  => {'table1.key1' => 'table2.key1'}
609
)->all;
610
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : exists where");
611

            
612
$rows = $dbi->select(
613
    table => [qw/table1 table2/],
614
    column => ['table1.key1 as table1_key1', 'table2.key1 as table2_key1', 'key2', 'key3'],
615
    relation  => {'table1.key1' => 'table2.key1'}
616
)->all;
617
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : no exists where");
618

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

            
622
$dbi = DBIx::Custom->connect(%memory);
623
$dbi->quote('"');
624
$dbi->execute('create table "table" ("select", "update")');
625
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
626
$dbi->insert(table => 'table', param => {select => 1, update => 2});
627
$result = $dbi->select(table => 'table', where => {select => 1});
628
$rows   = $result->all;
629
is_deeply($rows, [{select => 2, update => 2}], "reserved word");
630

            
631
test 'fetch filter';
632
$dbi = DBIx::Custom->connect(%memory);
633
$dbi->register_filter(
634
    twice       => sub { $_[0] * 2 },
635
    three_times => sub { $_[0] * 3 }
636
);
637
$dbi->default_fetch_filter('twice');
638
$dbi->execute($create_table_default);
639
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
640
$result = $dbi->select(table => 'table1');
641
$result->filter({key1 => 'three_times'});
642
$row = $result->one;
643
is_deeply($row, {key1 => 3, key2 => 4}, "default_fetch_filter and filter");
644

            
645
test 'filters';
646
$dbi = DBIx::Custom->new;
647

            
648
is($dbi->filters->{decode_utf8}->(encode_utf8('あ')),
649
   'あ', "decode_utf8");
650

            
651
is($dbi->filters->{encode_utf8}->('あ'),
652
   encode_utf8('あ'), "encode_utf8");
653

            
654
test 'transaction';
655
$dbi = DBIx::Custom->connect(%memory);
656
$dbi->execute($create_table_default);
657
$dbi->dbh->begin_work;
658
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
659
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
660
$dbi->dbh->commit;
661
$result = $dbi->select(table => 'table1');
662
is_deeply(scalar $result->all, [{key1 => 1, key2 => 2}, {key1 => 2, key2 => 3}],
663
          "commit");
664

            
665
$dbi = DBIx::Custom->connect(%memory);
666
$dbi->execute($create_table_default);
667
$dbi->dbh->begin_work(0);
668
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
669
$dbi->dbh->rollback;
670

            
671
$result = $dbi->select(table => 'table1');
672
ok(! $result->fetch_first, "rollback");
673

            
674
test 'cache';
675
$dbi = DBIx::Custom->connect(%memory);
676
$dbi->cache(1);
677
$dbi->execute($create_table_default);
678
$source = 'select * from table1 where key1 = :key1 and key2 = :key2;';
679
$dbi->execute($source, {}, query => 1);
680
is_deeply($dbi->{_cached}->{$source}, 
681
          {sql => "select * from table1 where key1 = ? and key2 = ?;", columns => ['key1', 'key2'], tables => []}, "cache");
682

            
683
$dbi = DBIx::Custom->connect(%memory);
684
$dbi->execute($create_table_default);
685
$dbi->{_cached} = {};
686
$dbi->cache(0);
687
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
688
is(scalar keys %{$dbi->{_cached}}, 0, 'not cache');
689

            
690
test 'execute';
691
$dbi = DBIx::Custom->connect(%memory);
692
$dbi->execute($create_table_default);
693
{
694
    local $Carp::Verbose = 0;
695
    eval{$dbi->execute('select * frm table1')};
696
    like($@, qr/\Qselect * frm table1;/, "fail prepare");
697
    like($@, qr/\.t /, "fail : not verbose");
698
}
699
{
700
    local $Carp::Verbose = 1;
701
    eval{$dbi->execute('select * frm table1')};
702
    like($@, qr/Custom.*\.t /s, "fail : verbose");
703
}
704

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

            
708
$query = $dbi->execute('select * from table1 where key1 = :key1', {}, query => 1);
709
$dbi->dbh->disconnect;
710
eval{$dbi->execute($query, param => {key1 => {a => 1}})};
711
ok($@, "execute fail");
712

            
713
{
714
    local $Carp::Verbose = 0;
715
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
716
    like($@, qr/\Q.t /, "caller spec : not vebose");
717
}
718
{
719
    local $Carp::Verbose = 1;
720
    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};
721
    like($@, qr/QueryBuilder.*\.t /s, "caller spec : not vebose");
722
}
723

            
724

            
725
test 'transaction';
726
$dbi = DBIx::Custom->connect(%memory);
727
$dbi->execute($create_table_default);
728

            
729
$dbi->begin_work;
730

            
731
eval {
732
    $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
733
    die "Error";
734
    $dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
735
};
736

            
737
$dbi->rollback if $@;
738

            
739
$result = $dbi->select(table => 'table1');
740
$rows = $result->all;
741
is_deeply($rows, [], "rollback");
742

            
743
$dbi->begin_work;
744

            
745
eval {
746
    $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
747
    $dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
748
};
749

            
750
$dbi->commit unless $@;
751

            
752
$result = $dbi->select(table => 'table1');
753
$rows = $result->all;
754
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "commit");
755

            
756
$dbi->dbh->{AutoCommit} = 0;
757
eval{ $dbi->begin_work };
758
ok($@, "exception");
759
$dbi->dbh->{AutoCommit} = 1;
760

            
761

            
762
test 'method';
763
$dbi = DBIx::Custom->connect(%memory);
764
$dbi->method(
765
    one => sub { 1 }
766
);
767
$dbi->method(
768
    two => sub { 2 }
769
);
770
$dbi->method({
771
    twice => sub {
772
        my $self = shift;
773
        return $_[0] * 2;
774
    }
775
});
776

            
777
is($dbi->one, 1, "first");
778
is($dbi->two, 2, "second");
779
is($dbi->twice(5), 10 , "second");
780

            
781
eval {$dbi->XXXXXX};
782
ok($@, "not exists");
783

            
784
test 'out filter';
785
$dbi = DBIx::Custom->connect(%memory);
786
$dbi->execute($create_table_default);
787
$dbi->register_filter(twice => sub { $_[0] * 2 });
788
$dbi->register_filter(three_times => sub { $_[0] * 3});
789
$dbi->apply_filter(
790
    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 
791
              'key2' => {out => 'three_times', in => 'twice'});
792
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
793
$result = $dbi->execute('select * from table1;');
794
$row   = $result->fetch_hash_first;
795
is_deeply($row, {key1 => 2, key2 => 6}, "insert");
796
$result = $dbi->select(table => 'table1');
797
$row   = $result->one;
798
is_deeply($row, {key1 => 6, key2 => 12}, "insert");
799

            
800
$dbi = DBIx::Custom->connect(%memory);
801
$dbi->execute($create_table_default);
802
$dbi->register_filter(twice => sub { $_[0] * 2 });
803
$dbi->register_filter(three_times => sub { $_[0] * 3});
804
$dbi->apply_filter(
805
    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 
806
              'key2' => {out => 'three_times', in => 'twice'});
807
$dbi->apply_filter(
808
    'table1', 'key1' => {out => undef}
809
); 
810
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
811
$result = $dbi->execute('select * from table1;');
812
$row   = $result->one;
813
is_deeply($row, {key1 => 1, key2 => 6}, "insert");
814

            
815
$dbi = DBIx::Custom->connect(%memory);
816
$dbi->execute($create_table_default);
817
$dbi->register_filter(twice => sub { $_[0] * 2 });
818
$dbi->apply_filter(
819
    'table1', 'key1' => {out => 'twice', in => 'twice'}
820
);
821
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => undef});
822
$dbi->update(table => 'table1', param => {key1 => 2}, where => {key2 => 2});
823
$result = $dbi->execute('select * from table1;');
824
$row   = $result->one;
825
is_deeply($row, {key1 => 4, key2 => 2}, "update");
826

            
827
$dbi = DBIx::Custom->connect(%memory);
828
$dbi->execute($create_table_default);
829
$dbi->register_filter(twice => sub { $_[0] * 2 });
830
$dbi->apply_filter(
831
    'table1', 'key1' => {out => 'twice', in => 'twice'}
832
);
833
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1=> undef});
834
$dbi->delete(table => 'table1', where => {key1 => 1});
835
$result = $dbi->execute('select * from table1;');
836
$rows   = $result->all;
837
is_deeply($rows, [], "delete");
838

            
839
$dbi = DBIx::Custom->connect(%memory);
840
$dbi->execute($create_table_default);
841
$dbi->register_filter(twice => sub { $_[0] * 2 });
842
$dbi->apply_filter(
843
    'table1', 'key1' => {out => 'twice', in => 'twice'}
844
);
845
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
846
$result = $dbi->select(table => 'table1', where => {key1 => 1});
847
$result->filter({'key2' => 'twice'});
848
$rows   = $result->all;
849
is_deeply($rows, [{key1 => 4, key2 => 4}], "select");
850

            
851
$dbi = DBIx::Custom->connect(%memory);
852
$dbi->execute($create_table_default);
853
$dbi->register_filter(twice => sub { $_[0] * 2 });
854
$dbi->apply_filter(
855
    'table1', 'key1' => {out => 'twice', in => 'twice'}
856
);
857
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
858
$result = $dbi->execute("select * from table1 where key1 = :key1 and key2 = :key2;",
859
                        param => {key1 => 1, key2 => 2},
860
                        table => ['table1']);
861
$rows   = $result->all;
862
is_deeply($rows, [{key1 => 4, key2 => 2}], "execute");
863

            
864
$dbi = DBIx::Custom->connect(%memory);
865
$dbi->execute($create_table_default);
866
$dbi->register_filter(twice => sub { $_[0] * 2 });
867
$dbi->apply_filter(
868
    'table1', 'key1' => {out => 'twice', in => 'twice'}
869
);
870
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
871
$result = $dbi->execute("select * from {table table1} where key1 = :key1 and key2 = :key2;",
872
                        param => {key1 => 1, key2 => 2});
873
$rows   = $result->all;
874
is_deeply($rows, [{key1 => 4, key2 => 2}], "execute table tag");
875

            
876
$dbi = DBIx::Custom->connect(%memory);
877
$dbi->execute($create_table_default);
878
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
879
$dbi->register_filter(twice => sub { $_[0] * 2 });
880
$dbi->register_filter(three_times => sub { $_[0] * 3 });
881
$dbi->apply_filter(
882
    'table1', 'key2' => {out => 'twice', in => 'twice'}
883
);
884
$dbi->apply_filter(
885
    'table2', 'key3' => {out => 'three_times', in => 'three_times'}
886
);
887
$dbi->insert(table => 'table1', param => {key1 => 5, key2 => 2}, filter => {key2 => undef});
888
$dbi->insert(table => 'table2', param => {key1 => 5, key3 => 6}, filter => {key3 => undef});
889
$result = $dbi->select(
890
     table => ['table1', 'table2'],
891
     column => ['key2', 'key3'],
892
     where => {'table1.key2' => 1, 'table2.key3' => 2}, relation => {'table1.key1' => 'table2.key1'});
893

            
894
$result->filter({'key2' => 'twice'});
895
$rows   = $result->all;
896
is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join");
897

            
898
$result = $dbi->select(
899
     table => ['table1', 'table2'],
900
     column => ['key2', 'key3'],
901
     where => {'key2' => 1, 'key3' => 2}, relation => {'table1.key1' => 'table2.key1'});
902

            
903
$result->filter({'key2' => 'twice'});
904
$rows   = $result->all;
905
is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join : omit");
906

            
907
test 'each_column';
908
$dbi = DBIx::Custom->connect(%memory);
909
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
910
$dbi->execute('create table table1 (key1 Date, key2 datetime);');
911

            
912
$infos = [];
913
$dbi->each_column(sub {
914
    my ($self, $table, $column, $cinfo) = @_;
915
    
916
    if ($table =~ /^table/) {
917
         my $info = [$table, $column, $cinfo->{COLUMN_NAME}];
918
         push @$infos, $info;
919
    }
920
});
921
$infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
922
is_deeply($infos, 
923
    [
924
        ['table1', 'key1', 'key1'],
925
        ['table1', 'key2', 'key2'],
926
        ['table2', 'key1', 'key1'],
927
        ['table2', 'key3', 'key3']
928
    ]
929
    
930
);
931
test 'each_table';
932
$dbi = DBIx::Custom->connect(%memory);
933
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
934
$dbi->execute('create table table1 (key1 Date, key2 datetime);');
935

            
936
$infos = [];
937
$dbi->each_table(sub {
938
    my ($self, $table, $table_info) = @_;
939
    
940
    if ($table =~ /^table/) {
941
         my $info = [$table, $table_info->{TABLE_NAME}];
942
         push @$infos, $info;
943
    }
944
});
945
$infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
946
is_deeply($infos, 
947
    [
948
        ['table1', 'table1'],
949
        ['table2', 'table2'],
950
    ]
951
);
952

            
953
test 'limit';
954
$dbi = DBIx::Custom->connect(%memory);
955
$dbi->execute($create_table_default);
956
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
957
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 4});
958
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 6});
959
$dbi->register_tag(
960
    limit => sub {
961
        my ($count, $offset) = @_;
962
        
963
        my $s = '';
964
        $s .= "limit $count";
965
        $s .= " offset $offset" if defined $offset;
966
        
967
        return [$s, []];
968
    }
969
);
970
$rows = $dbi->select(
971
  table => 'table1',
972
  where => {key1 => 1},
973
  append => "order by key2 {limit 1 0}"
974
)->all;
975
is_deeply($rows, [{key1 => 1, key2 => 2}]);
976
$rows = $dbi->select(
977
  table => 'table1',
978
  where => {key1 => 1},
979
  append => "order by key2 {limit 2 1}"
980
)->all;
981
is_deeply($rows, [{key1 => 1, key2 => 4},{key1 => 1, key2 => 6}]);
982
$rows = $dbi->select(
983
  table => 'table1',
984
  where => {key1 => 1},
985
  append => "order by key2 {limit 1}"
986
)->all;
987
is_deeply($rows, [{key1 => 1, key2 => 2}]);
988

            
989
test 'connect super';
990
{
991
    package MyDBI;
992
    
993
    use base 'DBIx::Custom';
994
    sub connect {
995
        my $self = shift->SUPER::connect(@_);
996
        
997
        return $self;
998
    }
999
    
1000
    sub new {
1001
        my $self = shift->SUPER::new(@_);
1002
        
1003
        return $self;
1004
    }
1005
}
1006

            
1007
$dbi = MyDBI->connect(%memory);
1008
$dbi->execute($create_table_default);
1009
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1010
is($dbi->select(table => 'table1')->one->{key1}, 1);
1011

            
1012
$dbi = MyDBI->new(%memory);
1013
$dbi->connect;
1014
$dbi->execute($create_table_default);
1015
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1016
is($dbi->select(table => 'table1')->one->{key1}, 1);
1017

            
1018
{
1019
    package MyDBI2;
1020
    
1021
    use base 'DBIx::Custom';
1022
    sub connect {
1023
        my $self = shift->SUPER::new(@_);
1024
        $self->connect;
1025
        
1026
        return $self;
1027
    }
1028
}
1029

            
1030
$dbi = MyDBI->connect(%memory);
1031
$dbi->execute($create_table_default);
1032
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1033
is($dbi->select(table => 'table1')->one->{key1}, 1);
1034

            
1035
test 'end_filter';
1036
$dbi = DBIx::Custom->connect(%memory);
1037
$dbi->execute($create_table_default);
1038
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1039
$result = $dbi->select(table => 'table1');
1040
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1041
$result->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 });
1042
$row = $result->fetch_first;
1043
is_deeply($row, [6, 40]);
1044

            
1045
$dbi = DBIx::Custom->connect(%memory);
1046
$dbi->execute($create_table_default);
1047
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1048
$result = $dbi->select(table => 'table1');
1049
$result->filter([qw/key1 key2/] => sub { $_[0] * 2 });
1050
$result->end_filter([[qw/key1 key2/] => sub { $_[0] * 3 }]);
1051
$row = $result->fetch_first;
1052
is_deeply($row, [6, 12]);
1053

            
1054
$dbi = DBIx::Custom->connect(%memory);
1055
$dbi->execute($create_table_default);
1056
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1057
$result = $dbi->select(table => 'table1');
1058
$result->filter([[qw/key1 key2/] => sub { $_[0] * 2 }]);
1059
$result->end_filter([qw/key1 key2/] => sub { $_[0] * 3 });
1060
$row = $result->fetch_first;
1061
is_deeply($row, [6, 12]);
1062

            
1063
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1064
$result = $dbi->select(table => 'table1');
1065
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1066
$result->end_filter({key1 => sub { $_[0] * 3 }, key2 => 'five_times' });
1067
$row = $result->one;
1068
is_deeply($row, {key1 => 6, key2 => 40});
1069

            
1070
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1071
$dbi->apply_filter('table1',
1072
    key1 => {end => sub { $_[0] * 3 } },
1073
    key2 => {end => 'five_times'}
1074
);
1075
$result = $dbi->select(table => 'table1');
1076
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1077
$row = $result->one;
1078
is_deeply($row, {key1 => 6, key2 => 40}, 'apply_filter');
1079

            
1080
$dbi->register_filter(five_times => sub { $_[0] * 5 });
1081
$dbi->apply_filter('table1',
1082
    key1 => {end => sub { $_[0] * 3 } },
1083
    key2 => {end => 'five_times'}
1084
);
1085
$result = $dbi->select(table => 'table1');
1086
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1087
$result->filter(key1 => undef);
1088
$result->end_filter(key1 => undef);
1089
$row = $result->one;
1090
is_deeply($row, {key1 => 1, key2 => 40}, 'apply_filter overwrite');
1091

            
1092
test 'remove_end_filter and remove_filter';
1093
$dbi = DBIx::Custom->connect(%memory);
1094
$dbi->execute($create_table_default);
1095
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1096
$result = $dbi->select(table => 'table1');
1097
$row = $result
1098
       ->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 })
1099
       ->remove_filter
1100
       ->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 })
1101
       ->remove_end_filter
1102
       ->fetch_first;
1103
is_deeply($row, [1, 2]);
1104

            
1105
test 'empty where select';
1106
$dbi = DBIx::Custom->connect(%memory);
1107
$dbi->execute($create_table_default);
1108
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1109
$result = $dbi->select(table => 'table1', where => {});
1110
$row = $result->one;
1111
is_deeply($row, {key1 => 1, key2 => 2});
1112

            
1113
test 'select query option';
1114
$dbi = DBIx::Custom->connect(%memory);
1115
$dbi->execute($create_table_default);
1116
$query = $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, query => 1);
1117
is(ref $query, 'DBIx::Custom::Query');
1118
$query = $dbi->update(table => 'table1', where => {key1 => 1}, param => {key2 => 2}, query => 1);
1119
is(ref $query, 'DBIx::Custom::Query');
1120
$query = $dbi->delete(table => 'table1', where => {key1 => 1}, query => 1);
1121
is(ref $query, 'DBIx::Custom::Query');
1122
$query = $dbi->select(table => 'table1', where => {key1 => 1, key2 => 2}, query => 1);
1123
is(ref $query, 'DBIx::Custom::Query');
1124

            
1125
test 'DBIx::Custom::Where';
1126
$dbi = DBIx::Custom->connect(%memory);
1127
$dbi->execute($create_table_default);
1128
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1129
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1130
$where = $dbi->where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
1131
is("$where", "where ( key1 = :key1 and key2 = :key2 )", 'no param');
1132

            
1133
$where = $dbi->where
1134
             ->clause(['and', 'key1 = :key1', 'key2 = :key2'])
1135
             ->param({key1 => 1});
1136

            
1137
$result = $dbi->select(
1138
    table => 'table1',
1139
    where => $where
1140
);
1141
$row = $result->all;
1142
is_deeply($row, [{key1 => 1, key2 => 2}]);
1143

            
1144
$result = $dbi->select(
1145
    table => 'table1',
1146
    where => [
1147
        ['and', 'key1 = :key1', 'key2 = :key2'],
1148
        {key1 => 1}
1149
    ]
1150
);
1151
$row = $result->all;
1152
is_deeply($row, [{key1 => 1, key2 => 2}]);
1153

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

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

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

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

            
1192
eval {
1193
$where = $dbi->where
1194
             ->clause(['uuu']);
1195
$result = $dbi->select(
1196
    table => 'table1',
1197
    where => $where
1198
);
1199
};
1200
ok($@);
1201

            
1202
$where = $dbi->where;
1203
is("$where", '');
1204

            
1205
$where = $dbi->where
1206
             ->clause(['or', ('key1 = :key1') x 2])
1207
             ->param({key1 => [1, 3]});
1208
$result = $dbi->select(
1209
    table => 'table1',
1210
    where => $where,
1211
);
1212
$row = $result->all;
1213
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
1214

            
1215
$where = $dbi->where
1216
             ->clause(['or', ('key1 = :key1') x 2])
1217
             ->param({key1 => [1]});
1218
$result = $dbi->select(
1219
    table => 'table1',
1220
    where => $where,
1221
);
1222
$row = $result->all;
1223
is_deeply($row, [{key1 => 1, key2 => 2}]);
1224

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

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

            
1245
$where = $dbi->where
1246
             ->clause('key1 = :key1 key2 = :key2')
1247
             ->param({key1 => 1});
1248
eval{$where->to_string};
1249
like($@, qr/one column/);
1250

            
1251
$where = $dbi->where
1252
             ->clause('key1 = :key1')
1253
             ->param([]);
1254
eval{$where->to_string};
1255
like($@, qr/Parameter/);
1256

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

            
1267
$where = $dbi->where
1268
             ->clause(['or', ('key1 = :key1') x 3])
1269
             ->param({key1 => [1, $dbi->not_exists, 3]});
1270
$result = $dbi->select(
1271
    table => 'table1',
1272
    where => $where,
1273
);
1274
$row = $result->all;
1275
is_deeply($row, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], 'not_exists');
1276

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

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

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

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

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

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

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

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

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

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

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

            
1386
eval {$dbi->where(ppp => 1) };
1387
like($@, qr/invalid/);
1388

            
1389
$where = $dbi->where(
1390
    clause => ['and', ['or'], ['and', 'key1 = :key1', 'key2 = :key2']],
1391
    param => {key1 => 1, key2 => 2}
1392
);
1393
$result = $dbi->select(
1394
    table => 'table1',
1395
    where => $where,
1396
);
1397
$row = $result->all;
1398
is_deeply($row, [{key1 => 1, key2 => 2}]);
1399

            
1400

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

            
1412

            
1413
test 'dbi_option default';
1414
$dbi = DBIx::Custom->new;
1415
is_deeply($dbi->dbi_option, {});
1416

            
1417
test 'register_tag_processor';
1418
$dbi = DBIx::Custom->connect(%memory);
1419
$dbi->register_tag_processor(
1420
    a => sub { 1 }
1421
);
1422
is($dbi->query_builder->tag_processors->{a}->(), 1);
1423

            
1424
test 'register_tag';
1425
$dbi = DBIx::Custom->connect(%memory);
1426
$dbi->register_tag(
1427
    b => sub { 2 }
1428
);
1429
is($dbi->query_builder->tags->{b}->(), 2);
1430

            
1431
test 'table not specify exception';
1432
$dbi = DBIx::Custom->connect(%memory);
1433
eval {$dbi->insert};
1434
like($@, qr/table/);
1435
eval {$dbi->update};
1436
like($@, qr/table/);
1437
eval {$dbi->delete};
1438
like($@, qr/table/);
1439
eval {$dbi->select};
1440
like($@, qr/table/);
1441

            
1442

            
1443
test 'more tests';
1444
$dbi = DBIx::Custom->connect(%memory);
1445
eval{$dbi->apply_filter('table', 'column', [])};
1446
like($@, qr/apply_filter/);
1447

            
1448
eval{$dbi->apply_filter('table', 'column', {outer => 2})};
1449
like($@, qr/apply_filter/);
1450

            
1451
$dbi->apply_filter(
1452

            
1453
);
1454
$dbi = DBIx::Custom->connect(%memory);
1455
$dbi->execute($create_table_default);
1456
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1457
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1458
$dbi->apply_filter('table1', 'key2', 
1459
                   {in => sub { $_[0] * 3 }, out => sub { $_[0] * 2 }});
1460
$rows = $dbi->select(table => 'table1', where => {key2 => 1})->all;
1461
is_deeply($rows, [{key1 => 1, key2 => 6}]);
1462

            
1463
$dbi = DBIx::Custom->connect(%memory);
1464
$dbi->execute($create_table_default);
1465
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1466
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1467
$dbi->apply_filter('table1', 'key2', {});
1468
$rows = $dbi->select(table => 'table1', where => {key2 => 2})->all;
1469
is_deeply($rows, [{key1 => 1, key2 => 2}]);
1470

            
1471
$dbi = DBIx::Custom->connect(%memory);
1472
eval {$dbi->apply_filter('table1', 'key2', {out => 'no'})};
1473
like($@, qr/not registered/);
1474
eval {$dbi->apply_filter('table1', 'key2', {in => 'no'})};
1475
like($@, qr/not registered/);
1476
$dbi->method({one => sub { 1 }});
1477
is($dbi->one, 1);
1478

            
1479
eval{DBIx::Custom->connect()};
1480
like($@, qr/_connect/);
1481

            
1482
$dbi = DBIx::Custom->connect(%memory);
1483
$dbi->execute($create_table_default);
1484
$dbi->register_filter(twice => sub { $_[0] * 2 });
1485
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
1486
             filter => {key1 => 'twice'});
1487
$row = $dbi->select(table => 'table1')->one;
1488
is_deeply($row, {key1 => 2, key2 => 2});
1489
eval {$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
1490
             filter => {key1 => 'no'}) };
1491
like($@, qr//);
1492

            
1493
$dbi->register_filter(one => sub { });
1494
$dbi->default_fetch_filter('one');
1495
ok($dbi->default_fetch_filter);
1496
$dbi->default_bind_filter('one');
1497
ok($dbi->default_bind_filter);
1498
eval{$dbi->default_fetch_filter('no')};
1499
like($@, qr/not registered/);
1500
eval{$dbi->default_bind_filter('no')};
1501
like($@, qr/not registered/);
1502
$dbi->default_bind_filter(undef);
1503
ok(!defined $dbi->default_bind_filter);
1504
$dbi->default_fetch_filter(undef);
1505
ok(!defined $dbi->default_fetch_filter);
1506
eval {$dbi->execute('select * from table1 {} {= author') };
1507
like($@, qr/Tag not finished/);
1508

            
1509
$dbi = DBIx::Custom->connect(%memory);
1510
$dbi->execute($create_table_default);
1511
$dbi->register_filter(one => sub { 1 });
1512
$result = $dbi->select(table => 'table1');
1513
eval {$result->filter(key1 => 'no')};
1514
like($@, qr/not registered/);
1515
eval {$result->end_filter(key1 => 'no')};
1516
like($@, qr/not registered/);
1517
$result->default_filter(undef);
1518
ok(!defined $result->default_filter);
1519
$result->default_filter('one');
1520
is($result->default_filter->(), 1);
1521

            
1522
test 'dbi_option';
1523
$dbi = DBIx::Custom->connect(%memory,
1524
                             dbi_option => {PrintError => 1});
1525
ok($dbi->dbh->{PrintError});
1526
$dbi = DBIx::Custom->connect(%memory,
1527
                             dbi_options => {PrintError => 1});
1528
ok($dbi->dbh->{PrintError});
1529

            
1530
test 'DBIx::Custom::Result stash()';
1531
$result = DBIx::Custom::Result->new;
1532
is_deeply($result->stash, {}, 'default');
1533
$result->stash->{foo} = 1;
1534
is($result->stash->{foo}, 1, 'get and set');
1535

            
1536
test 'filter __ expression';
1537
$dbi = DBIx::Custom->connect(%memory);
1538
$dbi->execute('create table company (id, name, location_id)');
1539
$dbi->execute('create table location (id, name)');
1540
$dbi->apply_filter('location',
1541
  name => {in => sub { uc $_[0] } }
1542
);
1543

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

            
1547
$result = $dbi->select(
1548
    table => ['company', 'location'], relation => {'company.location_id' => 'location.id'},
1549
    column => ['location.name as location__name']
1550
);
1551
is($result->fetch_first->[0], 'B');
1552

            
1553
$result = $dbi->select(
1554
    table => 'company', relation => {'company.location_id' => 'location.id'},
1555
    column => ['location.name as location__name']
1556
);
1557
is($result->fetch_first->[0], 'B');
1558

            
1559
$result = $dbi->select(
1560
    table => 'company', relation => {'company.location_id' => 'location.id'},
1561
    column => ['location.name as "location.name"']
1562
);
1563
is($result->fetch_first->[0], 'B');
1564

            
1565
test 'Model class';
1566
use MyDBI1;
1567
$dbi = MyDBI1->connect(%memory);
1568
$dbi->execute("create table book (title, author)");
1569
$model = $dbi->model('book');
1570
$model->insert({title => 'a', author => 'b'});
1571
is_deeply($model->list->all, [{title => 'a', author => 'b'}], 'basic');
1572
$dbi->execute("create table company (name)");
1573
$model = $dbi->model('company');
1574
$model->insert({name => 'a'});
1575
is_deeply($model->list->all, [{name => 'a'}], 'basic');
1576
is($dbi->models->{'book'}, $dbi->model('book'));
1577
is($dbi->models->{'company'}, $dbi->model('company'));
1578

            
1579
{
1580
    package MyDBI4;
1581

            
1582
    use strict;
1583
    use warnings;
1584

            
1585
    use base 'DBIx::Custom';
1586

            
1587
    sub connect {
1588
        my $self = shift->SUPER::connect(@_);
1589
        
1590
        $self->include_model(
1591
            MyModel2 => [
1592
                'book',
1593
                {class => 'Company', name => 'company'}
1594
            ]
1595
        );
1596
    }
1597

            
1598
    package MyModel2::Base1;
1599

            
1600
    use strict;
1601
    use warnings;
1602

            
1603
    use base 'DBIx::Custom::Model';
1604

            
1605
    package MyModel2::book;
1606

            
1607
    use strict;
1608
    use warnings;
1609

            
1610
    use base 'MyModel2::Base1';
1611

            
1612
    sub insert {
1613
        my ($self, $param) = @_;
1614
        
1615
        return $self->SUPER::insert(param => $param);
1616
    }
1617

            
1618
    sub list { shift->select; }
1619

            
1620
    package MyModel2::Company;
1621

            
1622
    use strict;
1623
    use warnings;
1624

            
1625
    use base 'MyModel2::Base1';
1626

            
1627
    sub insert {
1628
        my ($self, $param) = @_;
1629
        
1630
        return $self->SUPER::insert(param => $param);
1631
    }
1632

            
1633
    sub list { shift->select; }
1634
}
1635
$dbi = MyDBI4->connect(%memory);
1636
$dbi->execute("create table book (title, author)");
1637
$model = $dbi->model('book');
1638
$model->insert({title => 'a', author => 'b'});
1639
is_deeply($model->list->all, [{title => 'a', author => 'b'}], 'basic');
1640
$dbi->execute("create table company (name)");
1641
$model = $dbi->model('company');
1642
$model->insert({name => 'a'});
1643
is_deeply($model->list->all, [{name => 'a'}], 'basic');
1644

            
1645
{
1646
     package MyDBI5;
1647

            
1648
    use strict;
1649
    use warnings;
1650

            
1651
    use base 'DBIx::Custom';
1652

            
1653
    sub connect {
1654
        my $self = shift->SUPER::connect(@_);
1655
        
1656
        $self->include_model('MyModel4');
1657
    }
1658
}
1659
$dbi = MyDBI5->connect(%memory);
1660
$dbi->execute("create table company (name)");
1661
$dbi->execute("create table table1 (key1)");
1662
$model = $dbi->model('company');
1663
$model->insert({name => 'a'});
1664
is_deeply($model->list->all, [{name => 'a'}], 'include all model');
1665
$dbi->insert(table => 'table1', param => {key1 => 1});
1666
$model = $dbi->model('book');
1667
is_deeply($model->list->all, [{key1 => 1}], 'include all model');
1668

            
1669
test 'primary_key';
1670
use MyDBI1;
1671
$dbi = MyDBI1->connect(%memory);
1672
$model = $dbi->model('book');
1673
$model->primary_key(['id', 'number']);
1674
is_deeply($model->primary_key, ['id', 'number']);
1675

            
1676
test 'columns';
1677
use MyDBI1;
1678
$dbi = MyDBI1->connect(%memory);
1679
$model = $dbi->model('book');
1680
$model->columns(['id', 'number']);
1681
is_deeply($model->columns, ['id', 'number']);
1682

            
1683
test 'setup_model';
1684
use MyDBI1;
1685
$dbi = MyDBI1->connect(%memory);
1686
$dbi->execute('create table book (id)');
1687
$dbi->execute('create table company (id, name);');
1688
$dbi->execute('create table test (id, name, primary key (id, name));');
1689
$dbi->setup_model;
1690
is_deeply($dbi->model('book')->columns, ['id']);
1691
is_deeply($dbi->model('company')->columns, ['id', 'name']);
1692

            
1693
test 'delete_at';
1694
$dbi = DBIx::Custom->connect(%memory);
1695
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1696
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1697
$dbi->delete_at(
1698
    table => 'table1',
1699
    primary_key => ['key1', 'key2'],
1700
    where => [1, 2],
1701
);
1702
is_deeply($dbi->select(table => 'table1')->all, []);
1703

            
1704
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1705
$dbi->delete_at(
1706
    table => 'table1',
1707
    primary_key => 'key1',
1708
    where => 1,
1709
);
1710
is_deeply($dbi->select(table => 'table1')->all, []);
1711

            
1712
test 'insert_at';
1713
$dbi = DBIx::Custom->connect(%memory);
1714
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1715
$dbi->insert_at(
1716
    primary_key => ['key1', 'key2'], 
1717
    table => 'table1',
1718
    where => [1, 2],
1719
    param => {key3 => 3}
1720
);
1721
is($dbi->select(table => 'table1')->one->{key1}, 1);
1722
is($dbi->select(table => 'table1')->one->{key2}, 2);
1723
is($dbi->select(table => 'table1')->one->{key3}, 3);
1724

            
1725
$dbi->delete_all(table => 'table1');
1726
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1727
$dbi->insert_at(
1728
    primary_key => 'key1', 
1729
    table => 'table1',
1730
    where => 1,
1731
    param => {key2 => 2, key3 => 3}
1732
);
1733

            
1734
is($dbi->select(table => 'table1')->one->{key1}, 1);
1735
is($dbi->select(table => 'table1')->one->{key2}, 2);
1736
is($dbi->select(table => 'table1')->one->{key3}, 3);
1737

            
1738
eval {
1739
    $dbi->insert_at(
1740
        table => 'table1',
1741
        primary_key => ['key1', 'key2'],
1742
        where => {},
1743
        param => {key1 => 1, key2 => 2, key3 => 3},
1744
    );
1745
};
1746
like($@, qr/must be/);
1747

            
1748
$dbi = DBIx::Custom->connect(%memory);
1749
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1750
$dbi->insert_at(
1751
    {key3 => 3},
1752
    primary_key => ['key1', 'key2'], 
1753
    table => 'table1',
1754
    where => [1, 2],
1755
);
1756
is($dbi->select(table => 'table1')->one->{key1}, 1);
1757
is($dbi->select(table => 'table1')->one->{key2}, 2);
1758
is($dbi->select(table => 'table1')->one->{key3}, 3);
1759

            
1760
test 'update_at';
1761
$dbi = DBIx::Custom->connect(%memory);
1762
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1763
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1764
$dbi->update_at(
1765
    table => 'table1',
1766
    primary_key => ['key1', 'key2'],
1767
    where => [1, 2],
1768
    param => {key3 => 4}
1769
);
1770
is($dbi->select(table => 'table1')->one->{key1}, 1);
1771
is($dbi->select(table => 'table1')->one->{key2}, 2);
1772
is($dbi->select(table => 'table1')->one->{key3}, 4);
1773

            
1774
$dbi->delete_all(table => 'table1');
1775
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1776
$dbi->update_at(
1777
    table => 'table1',
1778
    primary_key => 'key1',
1779
    where => 1,
1780
    param => {key3 => 4}
1781
);
1782
is($dbi->select(table => 'table1')->one->{key1}, 1);
1783
is($dbi->select(table => 'table1')->one->{key2}, 2);
1784
is($dbi->select(table => 'table1')->one->{key3}, 4);
1785

            
1786
$dbi = DBIx::Custom->connect(%memory);
1787
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1788
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1789
$dbi->update_at(
1790
    {key3 => 4},
1791
    table => 'table1',
1792
    primary_key => ['key1', 'key2'],
1793
    where => [1, 2]
1794
);
1795
is($dbi->select(table => 'table1')->one->{key1}, 1);
1796
is($dbi->select(table => 'table1')->one->{key2}, 2);
1797
is($dbi->select(table => 'table1')->one->{key3}, 4);
1798

            
1799
test 'select_at';
1800
$dbi = DBIx::Custom->connect(%memory);
1801
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1802
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1803
$result = $dbi->select_at(
1804
    table => 'table1',
1805
    primary_key => ['key1', 'key2'],
1806
    where => [1, 2]
1807
);
1808
$row = $result->one;
1809
is($row->{key1}, 1);
1810
is($row->{key2}, 2);
1811
is($row->{key3}, 3);
1812

            
1813
$dbi->delete_all(table => 'table1');
1814
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1815
$result = $dbi->select_at(
1816
    table => 'table1',
1817
    primary_key => 'key1',
1818
    where => 1,
1819
);
1820
$row = $result->one;
1821
is($row->{key1}, 1);
1822
is($row->{key2}, 2);
1823
is($row->{key3}, 3);
1824

            
1825
$dbi->delete_all(table => 'table1');
1826
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1827
$result = $dbi->select_at(
1828
    table => 'table1',
1829
    primary_key => ['key1', 'key2'],
1830
    where => [1, 2]
1831
);
1832
$row = $result->one;
1833
is($row->{key1}, 1);
1834
is($row->{key2}, 2);
1835
is($row->{key3}, 3);
1836

            
1837
eval {
1838
    $result = $dbi->select_at(
1839
        table => 'table1',
1840
        primary_key => ['key1', 'key2'],
1841
        where => {},
1842
    );
1843
};
1844
like($@, qr/must be/);
1845

            
1846
eval {
1847
    $result = $dbi->select_at(
1848
        table => 'table1',
1849
        primary_key => ['key1', 'key2'],
1850
        where => [1],
1851
    );
1852
};
1853
like($@, qr/same/);
1854

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

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

            
1874
test 'columns';
1875
use MyDBI1;
1876
$dbi = MyDBI1->connect(%memory);
1877
$model = $dbi->model('book');
1878

            
1879

            
1880
test 'model delete_at';
1881
{
1882
    package MyDBI6;
1883
    
1884
    use base 'DBIx::Custom';
1885
    
1886
    sub connect {
1887
        my $self = shift->SUPER::connect(@_);
1888
        
1889
        $self->include_model('MyModel5');
1890
        
1891
        return $self;
1892
    }
1893
}
1894
$dbi = MyDBI6->connect(%memory);
1895
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1896
$dbi->execute("create table table2 (key1, key2, key3)");
1897
$dbi->execute("create table table3 (key1, key2, key3)");
1898
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1899
$dbi->model('table1')->delete_at(where => [1, 2]);
1900
is_deeply($dbi->select(table => 'table1')->all, []);
1901
$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
1902
$dbi->model('table1_1')->delete_at(where => [1, 2]);
1903
is_deeply($dbi->select(table => 'table1')->all, []);
1904
$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
1905
$dbi->model('table1_3')->delete_at(where => [1, 2]);
1906
is_deeply($dbi->select(table => 'table1')->all, []);
1907

            
1908
test 'model insert_at';
1909
$dbi = MyDBI6->connect(%memory);
1910
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1911
$dbi->model('table1')->insert_at(
1912
    where => [1, 2],
1913
    param => {key3 => 3}
1914
);
1915
$result = $dbi->model('table1')->select;
1916
$row = $result->one;
1917
is($row->{key1}, 1);
1918
is($row->{key2}, 2);
1919
is($row->{key3}, 3);
1920

            
1921
test 'model update_at';
1922
$dbi = MyDBI6->connect(%memory);
1923
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1924
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1925
$dbi->model('table1')->update_at(
1926
    where => [1, 2],
1927
    param => {key3 => 4}
1928
);
1929
$result = $dbi->model('table1')->select;
1930
$row = $result->one;
1931
is($row->{key1}, 1);
1932
is($row->{key2}, 2);
1933
is($row->{key3}, 4);
1934

            
1935
test 'model select_at';
1936
$dbi = MyDBI6->connect(%memory);
1937
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1938
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1939
$result = $dbi->model('table1')->select_at(where => [1, 2]);
1940
$row = $result->one;
1941
is($row->{key1}, 1);
1942
is($row->{key2}, 2);
1943
is($row->{key3}, 3);
1944

            
1945

            
1946
test 'mycolumn and column';
1947
{
1948
    package MyDBI7;
1949
    
1950
    use base 'DBIx::Custom';
1951
    
1952
    sub connect {
1953
        my $self = shift->SUPER::connect(@_);
1954
        
1955
        $self->include_model('MyModel6');
1956
        
1957
        
1958
        return $self;
1959
    }
1960
}
1961
$dbi = MyDBI7->connect(%memory);
1962
$dbi->execute($create_table_default);
1963
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
1964
$dbi->separator('__');
1965
$dbi->setup_model;
1966
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1967
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
1968
$model = $dbi->model('table1');
1969
$result = $model->select(
1970
    column => [$model->mycolumn, $model->column('table2')],
1971
    where => {'table1.key1' => 1}
1972
);
1973
is_deeply($result->one,
1974
          {key1 => 1, key2 => 2, 'table2__key1' => 1, 'table2__key3' => 3});
1975

            
1976
test 'update_param';
1977
$dbi = DBIx::Custom->connect(%memory);
1978
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1979
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
1980
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
1981

            
1982
$param = {key2 => 11};
1983
$update_param = $dbi->update_param($param);
1984
$sql = <<"EOS";
1985
update table1 $update_param
1986
where key1 = 1
1987
EOS
1988
$dbi->execute($sql, param => $param);
1989
$result = $dbi->execute('select * from table1;', table => 'table1');
1990
$rows   = $result->all;
1991
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
1992
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
1993
                  "basic");
1994

            
1995

            
1996
$dbi = DBIx::Custom->connect(%memory);
1997
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
1998
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
1999
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2000

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

            
2014
$dbi = DBIx::Custom->connect(%memory);
2015
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2016
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2017
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2018

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

            
2032
            
2033
eval { $dbi->update_param({";" => 1}) };
2034
like($@, qr/not safety/);
2035

            
2036

            
2037
test 'update_param';
2038
$dbi = DBIx::Custom->connect(%memory);
2039
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2040
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2041
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2042

            
2043
$param = {key2 => 11};
2044
$update_param = $dbi->assign_param($param);
2045
$sql = <<"EOS";
2046
update table1 set $update_param
2047
where key1 = 1
2048
EOS
2049
$dbi->execute($sql, param => $param, table => 'table1');
2050
$result = $dbi->execute('select * from table1;');
2051
$rows   = $result->all;
2052
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
2053
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
2054
                  "basic");
2055

            
2056

            
2057
test 'insert_param';
2058
$dbi = DBIx::Custom->connect(%memory);
2059
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2060
$param = {key1 => 1, key2 => 2};
2061
$insert_param = $dbi->insert_param($param);
2062
$sql = <<"EOS";
2063
insert into table1 $insert_param
2064
EOS
2065
$dbi->execute($sql, param => $param, table => 'table1');
2066
is($dbi->select(table => 'table1')->one->{key1}, 1);
2067
is($dbi->select(table => 'table1')->one->{key2}, 2);
2068

            
2069
$dbi = DBIx::Custom->connect(%memory);
2070
$dbi->quote('"');
2071
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2072
$param = {key1 => 1, key2 => 2};
2073
$insert_param = $dbi->insert_param($param);
2074
$sql = <<"EOS";
2075
insert into table1 $insert_param
2076
EOS
2077
$dbi->execute($sql, param => $param, table => 'table1');
2078
is($dbi->select(table => 'table1')->one->{key1}, 1);
2079
is($dbi->select(table => 'table1')->one->{key2}, 2);
2080

            
2081
eval { $dbi->insert_param({";" => 1}) };
2082
like($@, qr/not safety/);
2083

            
2084

            
2085
test 'join';
2086
$dbi = DBIx::Custom->connect(%memory);
2087
$dbi->execute($create_table_default);
2088
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2089
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
2090
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
2091
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2092
$dbi->execute('create table table3 (key3 int, key4 int);');
2093
$dbi->insert(table => 'table3', param => {key3 => 5, key4 => 4});
2094
$rows = $dbi->select(
2095
    table => 'table1',
2096
    column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
2097
    where   => {'table1.key2' => 2},
2098
    join  => ['left outer join table2 on table1.key1 = table2.key1']
2099
)->all;
2100
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}]);
2101

            
2102
$rows = $dbi->select(
2103
    table => 'table1',
2104
    where   => {'key1' => 1},
2105
    join  => ['left outer join table2 on table1.key1 = table2.key1']
2106
)->all;
2107
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2108

            
2109
eval {
2110
    $rows = $dbi->select(
2111
        table => 'table1',
2112
        column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
2113
        where   => {'table1.key2' => 2},
2114
        join  => {'table1.key1' => 'table2.key1'}
2115
    );
2116
};
2117
like ($@, qr/array/);
2118

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

            
2127
$rows = $dbi->select(
2128
    column => 'table3.key4 as table3__key4',
2129
    table => 'table1',
2130
    where   => {'table1.key1' => 1},
2131
    join  => ['left outer join table2 on table1.key1 = table2.key1',
2132
              'left outer join table3 on table2.key3 = table3.key3']
2133
)->all;
2134
is_deeply($rows, [{table3__key4 => 4}]);
2135

            
2136
$rows = $dbi->select(
2137
    column => 'table1.key1 as table1__key1',
2138
    table => 'table1',
2139
    where   => {'table3.key4' => 4},
2140
    join  => ['left outer join table2 on table1.key1 = table2.key1',
2141
              'left outer join table3 on table2.key3 = table3.key3']
2142
)->all;
2143
is_deeply($rows, [{table1__key1 => 1}]);
2144

            
2145
$dbi = DBIx::Custom->connect(%memory);
2146
$dbi->quote('"');
2147
$dbi->execute($create_table_default);
2148
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2149
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
2150
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2151
$rows = $dbi->select(
2152
    table => 'table1',
2153
    column => '"table1"."key1" as "table1_key1", "table2"."key1" as "table2_key1", "key2", "key3"',
2154
    where   => {'table1.key2' => 2},
2155
    join  => ['left outer join "table2" on "table1"."key1" = "table2"."key1"'],
2156
)->all;
2157
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}],
2158
          'quote');
2159

            
2160
{
2161
    package MyDBI8;
2162
    
2163
    use base 'DBIx::Custom';
2164
    
2165
    sub connect {
2166
        my $self = shift->SUPER::connect(@_);
2167
        
2168
        $self->include_model('MyModel7');
2169
        
2170
        return $self;
2171
    }
2172
}
2173

            
2174
$dbi = DBIx::Custom->connect(%memory);
2175
$dbi->execute($create_table_default);
2176
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2177
$sql = <<"EOS";
2178
left outer join (
2179
  select * from table1 as t1
2180
  where t1.key2 = (
2181
    select max(t2.key2) from table1 as t2
2182
    where t1.key1 = t2.key1
2183
  )
2184
) as latest_table1 on table1.key1 = latest_table1.key1
2185
EOS
2186
$join = [$sql];
2187
$rows = $dbi->select(
2188
    table => 'table1',
2189
    column => 'latest_table1.key1 as latest_table1__key1',
2190
    join  => $join
2191
)->all;
2192
is_deeply($rows, [{latest_table1__key1 => 1}]);
2193

            
2194
$dbi = DBIx::Custom->connect(%memory);
2195
$dbi->execute($create_table_default);
2196
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
2197
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2198
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
2199
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2200
$result = $dbi->select(
2201
    table => 'table1',
2202
    join => [
2203
        "left outer join table2 on table2.key2 = '4' and table1.key1 = table2.key1"
2204
    ]
2205
);
2206
is_deeply($result->all, [{key1 => 1, key2 => 2}]);
2207
$result = $dbi->select(
2208
    table => 'table1',
2209
    column => [{table2 => ['key3']}],
2210
    join => [
2211
        "left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1"
2212
    ]
2213
);
2214
is_deeply($result->all, [{'table2.key3' => 4}]);
2215
$result = $dbi->select(
2216
    table => 'table1',
2217
    column => [{table2 => ['key3']}],
2218
    join => [
2219
        "left outer join table2 on table1.key1 = table2.key1 and table2.key3 = '4'"
2220
    ]
2221
);
2222
is_deeply($result->all, [{'table2.key3' => 4}]);
2223

            
2224
$dbi = DBIx::Custom->connect(%memory);
2225
$dbi->execute($create_table_default);
2226
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
2227
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2228
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
2229
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2230
$result = $dbi->select(
2231
    table => 'table1',
2232
    column => [{table2 => ['key3']}],
2233
    join => [
2234
        {
2235
            clause => "left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1",
2236
            table => ['table1', 'table2']
2237
        }
2238
    ]
2239
);
2240
is_deeply($result->all, [{'table2.key3' => 4}]);
2241

            
2242
test 'mycolumn';
2243
$dbi = MyDBI8->connect(%memory);
2244
$dbi->execute($create_table_default);
2245
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
2246
$dbi->setup_model;
2247
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2248
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2249
$model = $dbi->model('table1');
2250
$result = $model->select_at(
2251
    column => [
2252
        $model->mycolumn,
2253
        $model->column('table2')
2254
    ]
2255
);
2256
is_deeply($result->one,
2257
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
2258

            
2259
$result = $model->select_at(
2260
    column => [
2261
        $model->mycolumn(['key1']),
2262
        $model->column(table2 => ['key1'])
2263
    ]
2264
);
2265
is_deeply($result->one,
2266
          {key1 => 1, 'table2.key1' => 1});
2267
$result = $model->select_at(
2268
    column => [
2269
        $model->mycolumn(['key1']),
2270
        {table2 => ['key1']}
2271
    ]
2272
);
2273
is_deeply($result->one,
2274
          {key1 => 1, 'table2.key1' => 1});
2275

            
2276
$result = $model->select_at(
2277
    column => [
2278
        $model->mycolumn(['key1']),
2279
        ['table2.key1', as => 'table2.key1']
2280
    ]
2281
);
2282
is_deeply($result->one,
2283
          {key1 => 1, 'table2.key1' => 1});
2284

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

            
2294
test 'dbi method from model';
2295
{
2296
    package MyDBI9;
2297
    
2298
    use base 'DBIx::Custom';
2299
    
2300
    sub connect {
2301
        my $self = shift->SUPER::connect(@_);
2302
        
2303
        $self->include_model('MyModel8')->setup_model;
2304
        
2305
        return $self;
2306
    }
2307
}
2308
$dbi = MyDBI9->connect(%memory);
2309
$dbi->execute($create_table_default);
2310
$model = $dbi->model('table1');
2311
eval{$model->execute('select * from table1')};
2312
ok(!$@);
2313

            
2314
test 'column table option';
2315
$dbi = MyDBI9->connect(%memory);
2316
$dbi->execute($create_table_default);
2317
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
2318
$dbi->setup_model;
2319
$dbi->execute('insert into table1 (key1, key2) values (1, 2);');
2320
$dbi->execute('insert into table2 (key1, key3) values (1, 4);');
2321
$model = $dbi->model('table1');
2322
$result = $model->select(
2323
    column => [
2324
        $model->column('table2', {alias => 'table2_alias'})
2325
    ],
2326
    where => {'table2_alias.key3' => 4}
2327
);
2328
is_deeply($result->one, 
2329
          {'table2_alias.key1' => 1, 'table2_alias.key3' => 4});
2330

            
2331
$dbi->separator('__');
2332
$result = $model->select(
2333
    column => [
2334
        $model->column('table2', {alias => 'table2_alias'})
2335
    ],
2336
    where => {'table2_alias.key3' => 4}
2337
);
2338
is_deeply($result->one, 
2339
          {'table2_alias__key1' => 1, 'table2_alias__key3' => 4});
2340

            
2341
$dbi->separator('-');
2342
$result = $model->select(
2343
    column => [
2344
        $model->column('table2', {alias => 'table2_alias'})
2345
    ],
2346
    where => {'table2_alias.key3' => 4}
2347
);
2348
is_deeply($result->one, 
2349
          {'table2_alias-key1' => 1, 'table2_alias-key3' => 4});
2350

            
2351
test 'type option'; # DEPRECATED!
2352
$dbi = DBIx::Custom->connect(
2353
    data_source => 'dbi:SQLite:dbname=:memory:',
2354
    dbi_option => {
2355
        $DBD::SQLite::VERSION > 1.26 ? (sqlite_unicode => 1) : (unicode => 1)
2356
    }
2357
);
2358
my $binary = pack("I3", 1, 2, 3);
2359
$dbi->execute('create table table1(key1, key2)');
2360
$dbi->insert(table => 'table1', param => {key1 => $binary, key2 => 'あ'}, type => [key1 => DBI::SQL_BLOB]);
2361
$result = $dbi->select(table => 'table1');
2362
$row   = $result->one;
2363
is_deeply($row, {key1 => $binary, key2 => 'あ'}, "basic");
2364
$result = $dbi->execute('select length(key1) as key1_length from table1');
2365
$row = $result->one;
2366
is($row->{key1_length}, length $binary);
2367

            
2368
$dbi->insert(table => 'table1', param => {key1 => $binary, key2 => 'あ'}, type => [['key1'] => DBI::SQL_BLOB]);
2369
$result = $dbi->select(table => 'table1');
2370
$row   = $result->one;
2371
is_deeply($row, {key1 => $binary, key2 => 'あ'}, "basic");
2372
$result = $dbi->execute('select length(key1) as key1_length from table1');
2373
$row = $result->one;
2374
is($row->{key1_length}, length $binary);
2375

            
2376

            
2377
test 'bind_type option';
2378
$dbi = DBIx::Custom->connect(
2379
    data_source => 'dbi:SQLite:dbname=:memory:',
2380
    dbi_option => {
2381
        $DBD::SQLite::VERSION > 1.26 ? (sqlite_unicode => 1) : (unicode => 1)
2382
    }
2383
);
2384
$binary = pack("I3", 1, 2, 3);
2385
$dbi->execute('create table table1(key1, key2)');
2386
$dbi->insert(table => 'table1', param => {key1 => $binary, key2 => 'あ'}, bind_type => [key1 => DBI::SQL_BLOB]);
2387
$result = $dbi->select(table => 'table1');
2388
$row   = $result->one;
2389
is_deeply($row, {key1 => $binary, key2 => 'あ'}, "basic");
2390
$result = $dbi->execute('select length(key1) as key1_length from table1');
2391
$row = $result->one;
2392
is($row->{key1_length}, length $binary);
2393

            
2394
$dbi->insert(table => 'table1', param => {key1 => $binary, key2 => 'あ'}, bind_type => [['key1'] => DBI::SQL_BLOB]);
2395
$result = $dbi->select(table => 'table1');
2396
$row   = $result->one;
2397
is_deeply($row, {key1 => $binary, key2 => 'あ'}, "basic");
2398
$result = $dbi->execute('select length(key1) as key1_length from table1');
2399
$row = $result->one;
2400
is($row->{key1_length}, length $binary);
2401

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

            
2420
test 'create_model';
2421
$dbi = DBIx::Custom->connect(%memory);
2422
$dbi->execute($create_table_default);
2423
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
2424

            
2425
$dbi->create_model(
2426
    table => 'table1',
2427
    join => [
2428
       'left outer join table2 on table1.key1 = table2.key1'
2429
    ],
2430
    primary_key => ['key1']
2431
);
2432
$model2 = $dbi->create_model(
2433
    table => 'table2'
2434
);
2435
$dbi->create_model(
2436
    table => 'table3',
2437
    filter => [
2438
        key1 => {in => sub { uc $_[0] }}
2439
    ]
2440
);
2441
$dbi->setup_model;
2442
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2443
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2444
$model = $dbi->model('table1');
2445
$result = $model->select(
2446
    column => [$model->mycolumn, $model->column('table2')],
2447
    where => {'table1.key1' => 1}
2448
);
2449
is_deeply($result->one,
2450
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
2451
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
2452

            
2453
test 'model method';
2454
test 'create_model';
2455
$dbi = DBIx::Custom->connect(%memory);
2456
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
2457
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2458
$model = $dbi->create_model(
2459
    table => 'table2'
2460
);
2461
$model->method(foo => sub { shift->select(@_) });
2462
is_deeply($model->foo->one, {key1 => 1, key3 => 3});
2463

            
2464
test 'merge_param';
2465
$dbi = DBIx::Custom->new;
2466
$params = [
2467
    {key1 => 1, key2 => 2, key3 => 3},
2468
    {key1 => 1, key2 => 2},
2469
    {key1 => 1}
2470
];
2471
$param = $dbi->merge_param($params->[0], $params->[1], $params->[2]);
2472
is_deeply($param, {key1 => [1, 1, 1], key2 => [2, 2], key3 => 3});
2473

            
2474
$params = [
2475
    {key1 => [1, 2], key2 => 1, key3 => [1, 2]},
2476
    {key1 => [3, 4], key2 => [2, 3], key3 => 3}
2477
];
2478
$param = $dbi->merge_param($params->[0], $params->[1]);
2479
is_deeply($param, {key1 => [1, 2, 3, 4], key2 => [1, 2, 3], key3 => [1, 2, 3]});
2480

            
2481
test 'select() param option';
2482
$dbi = DBIx::Custom->connect(%memory);
2483
$dbi->execute($create_table_default);
2484
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2485
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2486
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
2487
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
2488
$dbi->insert(table => 'table2', param => {key1 => 2, key3 => 5});
2489
$rows = $dbi->select(
2490
    table => 'table1',
2491
    column => 'table1.key1 as table1_key1, key2, key3',
2492
    where   => {'table1.key2' => 3},
2493
    join  => ['inner join (select * from table2 where {= table2.key3})' . 
2494
              ' as table2 on table1.key1 = table2.key1'],
2495
    param => {'table2.key3' => 5}
2496
)->all;
2497
is_deeply($rows, [{table1_key1 => 2, key2 => 3, key3 => 5}]);
2498

            
2499

            
2500
test 'select() wrap option';
2501
$dbi = DBIx::Custom->connect(%memory);
2502
$dbi->execute($create_table_default);
2503
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2504
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2505
$rows = $dbi->select(
2506
    table => 'table1',
2507
    column => 'key1',
2508
    wrap => ['select * from (', ') as t where key1 = 1']
2509
)->all;
2510
is_deeply($rows, [{key1 => 1}]);
2511

            
2512
eval {
2513
$dbi->select(
2514
    table => 'table1',
2515
    column => 'key1',
2516
    wrap => 'select * from ('
2517
)
2518
};
2519
like($@, qr/array/);
2520

            
2521
test 'select() string where';
2522
$dbi = DBIx::Custom->connect(%memory);
2523
$dbi->execute($create_table_default);
2524
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2525
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2526
$rows = $dbi->select(
2527
    table => 'table1',
2528
    where => 'key1 = :key1 and key2 = :key2',
2529
    where_param => {key1 => 1, key2 => 2}
2530
)->all;
2531
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2532

            
2533
$dbi = DBIx::Custom->connect(%memory);
2534
$dbi->execute($create_table_default);
2535
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2536
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2537
$rows = $dbi->select(
2538
    table => 'table1',
2539
    where => [
2540
        'key1 = :key1 and key2 = :key2',
2541
        {key1 => 1, key2 => 2}
2542
    ]
2543
)->all;
2544
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2545

            
2546
test 'delete() string where';
2547
$dbi = DBIx::Custom->connect(%memory);
2548
$dbi->execute($create_table_default);
2549
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2550
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2551
$dbi->delete(
2552
    table => 'table1',
2553
    where => 'key1 = :key1 and key2 = :key2',
2554
    where_param => {key1 => 1, key2 => 2}
2555
);
2556
$rows = $dbi->select(table => 'table1')->all;
2557
is_deeply($rows, [{key1 => 2, key2 => 3}]);
2558

            
2559
$dbi = DBIx::Custom->connect(%memory);
2560
$dbi->execute($create_table_default);
2561
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2562
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2563
$dbi->delete(
2564
    table => 'table1',
2565
    where => [
2566
        'key1 = :key1 and key2 = :key2',
2567
         {key1 => 1, key2 => 2}
2568
    ]
2569
);
2570
$rows = $dbi->select(table => 'table1')->all;
2571
is_deeply($rows, [{key1 => 2, key2 => 3}]);
2572

            
2573

            
2574
test 'update() string where';
2575
$dbi = DBIx::Custom->connect(%memory);
2576
$dbi->execute($create_table_default);
2577
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2578
$dbi->update(
2579
    table => 'table1',
2580
    param => {key1 => 5},
2581
    where => 'key1 = :key1 and key2 = :key2',
2582
    where_param => {key1 => 1, key2 => 2}
2583
);
2584
$rows = $dbi->select(table => 'table1')->all;
2585
is_deeply($rows, [{key1 => 5, key2 => 2}]);
2586

            
2587
$dbi = DBIx::Custom->connect(%memory);
2588
$dbi->execute($create_table_default);
2589
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2590
$dbi->update(
2591
    table => 'table1',
2592
    param => {key1 => 5},
2593
    where => [
2594
        'key1 = :key1 and key2 = :key2',
2595
        {key1 => 1, key2 => 2}
2596
    ]
2597
);
2598
$rows = $dbi->select(table => 'table1')->all;
2599
is_deeply($rows, [{key1 => 5, key2 => 2}]);
2600

            
2601
test 'insert id and primary_key option';
2602
$dbi = DBIx::Custom->connect(%memory);
2603
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2604
$dbi->insert(
2605
    primary_key => ['key1', 'key2'], 
2606
    table => 'table1',
2607
    id => [1, 2],
2608
    param => {key3 => 3}
2609
);
2610
is($dbi->select(table => 'table1')->one->{key1}, 1);
2611
is($dbi->select(table => 'table1')->one->{key2}, 2);
2612
is($dbi->select(table => 'table1')->one->{key3}, 3);
2613

            
2614
$dbi->delete_all(table => 'table1');
2615
$dbi->insert(
2616
    primary_key => 'key1', 
2617
    table => 'table1',
2618
    id => 0,
2619
    param => {key2 => 2, key3 => 3}
2620
);
2621

            
2622
is($dbi->select(table => 'table1')->one->{key1}, 0);
2623
is($dbi->select(table => 'table1')->one->{key2}, 2);
2624
is($dbi->select(table => 'table1')->one->{key3}, 3);
2625

            
2626
$dbi = DBIx::Custom->connect(%memory);
2627
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2628
$dbi->insert(
2629
    {key3 => 3},
2630
    primary_key => ['key1', 'key2'], 
2631
    table => 'table1',
2632
    id => [1, 2],
2633
);
2634
is($dbi->select(table => 'table1')->one->{key1}, 1);
2635
is($dbi->select(table => 'table1')->one->{key2}, 2);
2636
is($dbi->select(table => 'table1')->one->{key3}, 3);
2637

            
2638

            
2639
test 'model insert id and primary_key option';
2640
$dbi = MyDBI6->connect(%memory);
2641
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2642
$dbi->model('table1')->insert(
2643
    id => [1, 2],
2644
    param => {key3 => 3}
2645
);
2646
$result = $dbi->model('table1')->select;
2647
$row = $result->one;
2648
is($row->{key1}, 1);
2649
is($row->{key2}, 2);
2650
is($row->{key3}, 3);
2651

            
2652
$dbi = MyDBI6->connect(%memory);
2653
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2654
$dbi->model('table1')->insert(
2655
    {key3 => 3},
2656
    id => [1, 2]
2657
);
2658
$result = $dbi->model('table1')->select;
2659
$row = $result->one;
2660
is($row->{key1}, 1);
2661
is($row->{key2}, 2);
2662
is($row->{key3}, 3);
2663

            
2664
test 'update and id option';
2665
$dbi = DBIx::Custom->connect(%memory);
2666
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2667
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2668
$dbi->update(
2669
    table => 'table1',
2670
    primary_key => ['key1', 'key2'],
2671
    id => [1, 2],
2672
    param => {key3 => 4}
2673
);
2674
is($dbi->select(table => 'table1')->one->{key1}, 1);
2675
is($dbi->select(table => 'table1')->one->{key2}, 2);
2676
is($dbi->select(table => 'table1')->one->{key3}, 4);
2677

            
2678
$dbi->delete_all(table => 'table1');
2679
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2680
$dbi->update(
2681
    table => 'table1',
2682
    primary_key => 'key1',
2683
    id => 0,
2684
    param => {key3 => 4}
2685
);
2686
is($dbi->select(table => 'table1')->one->{key1}, 0);
2687
is($dbi->select(table => 'table1')->one->{key2}, 2);
2688
is($dbi->select(table => 'table1')->one->{key3}, 4);
2689

            
2690
$dbi = DBIx::Custom->connect(%memory);
2691
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2692
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2693
$dbi->update(
2694
    {key3 => 4},
2695
    table => 'table1',
2696
    primary_key => ['key1', 'key2'],
2697
    id => [1, 2]
2698
);
2699
is($dbi->select(table => 'table1')->one->{key1}, 1);
2700
is($dbi->select(table => 'table1')->one->{key2}, 2);
2701
is($dbi->select(table => 'table1')->one->{key3}, 4);
2702

            
2703

            
2704
test 'model update and id option';
2705
$dbi = MyDBI6->connect(%memory);
2706
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2707
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2708
$dbi->model('table1')->update(
2709
    id => [1, 2],
2710
    param => {key3 => 4}
2711
);
2712
$result = $dbi->model('table1')->select;
2713
$row = $result->one;
2714
is($row->{key1}, 1);
2715
is($row->{key2}, 2);
2716
is($row->{key3}, 4);
2717

            
2718

            
2719
test 'delete and id option';
2720
$dbi = DBIx::Custom->connect(%memory);
2721
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2722
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2723
$dbi->delete(
2724
    table => 'table1',
2725
    primary_key => ['key1', 'key2'],
2726
    id => [1, 2],
2727
);
2728
is_deeply($dbi->select(table => 'table1')->all, []);
2729

            
2730
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2731
$dbi->delete(
2732
    table => 'table1',
2733
    primary_key => 'key1',
2734
    id => 0,
2735
);
2736
is_deeply($dbi->select(table => 'table1')->all, []);
2737

            
2738

            
2739
test 'model delete and id option';
2740
$dbi = MyDBI6->connect(%memory);
2741
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2742
$dbi->execute("create table table2 (key1, key2, key3)");
2743
$dbi->execute("create table table3 (key1, key2, key3)");
2744
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2745
$dbi->model('table1')->delete(id => [1, 2]);
2746
is_deeply($dbi->select(table => 'table1')->all, []);
2747
$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
2748
$dbi->model('table1_1')->delete(id => [1, 2]);
2749
is_deeply($dbi->select(table => 'table1')->all, []);
2750
$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
2751
$dbi->model('table1_3')->delete(id => [1, 2]);
2752
is_deeply($dbi->select(table => 'table1')->all, []);
2753

            
2754

            
2755
test 'select and id option';
2756
$dbi = DBIx::Custom->connect(%memory);
2757
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2758
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2759
$result = $dbi->select(
2760
    table => 'table1',
2761
    primary_key => ['key1', 'key2'],
2762
    id => [1, 2]
2763
);
2764
$row = $result->one;
2765
is($row->{key1}, 1);
2766
is($row->{key2}, 2);
2767
is($row->{key3}, 3);
2768

            
2769
$dbi->delete_all(table => 'table1');
2770
$dbi->insert(table => 'table1', param => {key1 => 0, key2 => 2, key3 => 3});
2771
$result = $dbi->select(
2772
    table => 'table1',
2773
    primary_key => 'key1',
2774
    id => 0,
2775
);
2776
$row = $result->one;
2777
is($row->{key1}, 0);
2778
is($row->{key2}, 2);
2779
is($row->{key3}, 3);
2780

            
2781
$dbi->delete_all(table => 'table1');
2782
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2783
$result = $dbi->select(
2784
    table => 'table1',
2785
    primary_key => ['key1', 'key2'],
2786
    id => [1, 2]
2787
);
2788
$row = $result->one;
2789
is($row->{key1}, 1);
2790
is($row->{key2}, 2);
2791
is($row->{key3}, 3);
2792

            
2793

            
2794
test 'model select_at';
2795
$dbi = MyDBI6->connect(%memory);
2796
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
2797
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2798
$result = $dbi->model('table1')->select(id => [1, 2]);
2799
$row = $result->one;
2800
is($row->{key1}, 1);
2801
is($row->{key2}, 2);
2802
is($row->{key3}, 3);
2803

            
2804
test 'column separator is default .';
2805
$dbi = MyDBI7->connect(%memory);
2806
$dbi->execute($create_table_default);
2807
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
2808
$dbi->setup_model;
2809
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2810
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2811
$model = $dbi->model('table1');
2812
$result = $model->select(
2813
    column => [$model->column('table2')],
2814
    where => {'table1.key1' => 1}
2815
);
2816
is_deeply($result->one,
2817
          {'table2.key1' => 1, 'table2.key3' => 3});
2818

            
2819
$result = $model->select(
2820
    column => [$model->column('table2' => [qw/key1 key3/])],
2821
    where => {'table1.key1' => 1}
2822
);
2823
is_deeply($result->one,
2824
          {'table2.key1' => 1, 'table2.key3' => 3});
2825

            
2826

            
2827
test 'type_rule from';
2828
$dbi = DBIx::Custom->connect(%memory);
2829
$dbi->type_rule(
2830
    from1 => {
2831
        date => sub { uc $_[0] }
2832
    }
2833
);
2834
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2835
$dbi->insert({key1 => 'a'}, table => 'table1');
2836
$result = $dbi->select(table => 'table1');
2837
is($result->fetch_first->[0], 'A');
2838

            
2839
$result = $dbi->select(table => 'table1');
2840
is($result->one->{key1}, 'A');
2841

            
2842

            
2843
test 'type_rule into';
2844
$dbi = DBIx::Custom->connect(%memory);
2845
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2846
$dbi->type_rule(
2847
    into1 => {
2848
        date => sub { uc $_[0] }
2849
    }
2850
);
2851
$dbi->insert({key1 => 'a'}, table => 'table1');
2852
$result = $dbi->select(table => 'table1');
2853
is($result->one->{key1}, 'A');
2854

            
2855
$dbi = DBIx::Custom->connect(%memory);
2856
$dbi->execute("create table table1 (key1 date, key2 datetime)");
2857
$dbi->type_rule(
2858
    into1 => [
2859
         [qw/date datetime/] => sub { uc $_[0] }
2860
    ]
2861
);
2862
$dbi->insert({key1 => 'a', key2 => 'b'}, table => 'table1');
2863
$result = $dbi->select(table => 'table1');
2864
$row = $result->one;
2865
is($row->{key1}, 'A');
2866
is($row->{key2}, 'B');
2867

            
2868
$dbi = DBIx::Custom->connect(%memory);
2869
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2870
$dbi->insert({key1 => 'a', key2 => 'B'}, table => 'table1');
2871
$dbi->type_rule(
2872
    into1 => [
2873
        [qw/date datetime/] => sub { uc $_[0] }
2874
    ]
2875
);
2876
$result = $dbi->execute(
2877
    "select * from table1 where key1 = :key1 and key2 = :table1.key2;",
2878
    param => {key1 => 'a', 'table1.key2' => 'b'}
2879
);
2880
$row = $result->one;
2881
is($row->{key1}, 'a');
2882
is($row->{key2}, 'B');
2883

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

            
2901
$dbi = DBIx::Custom->connect(%memory);
2902
$dbi->execute("create table table1 (key1 date, key2 datetime)");
2903
$dbi->register_filter(twice => sub { $_[0] * 2 });
2904
$dbi->type_rule(
2905
    from1 => {
2906
        date => 'twice',
2907
    },
2908
    into1 => {
2909
        date => 'twice',
2910
    }
2911
);
2912
$dbi->insert({key1 => 2}, table => 'table1');
2913
$result = $dbi->select(table => 'table1');
2914
is($result->fetch->[0], 8);
2915

            
2916
test 'type_rule and filter order';
2917
$dbi = DBIx::Custom->connect(%memory);
2918
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2919
$dbi->type_rule(
2920
    into1 => {
2921
        date => sub { $_[0] . 'b' }
2922
    },
2923
    into2 => {
2924
        date => sub { $_[0] . 'c' }
2925
    },
2926
    from1 => {
2927
        date => sub { $_[0] . 'd' }
2928
    },
2929
    from2 => {
2930
        date => sub { $_[0] . 'e' }
2931
    }
2932
);
2933
$dbi->insert({key1 => '1'}, table => 'table1', filter => {key1 => sub { $_[0] . 'a' }});
2934
$result = $dbi->select(table => 'table1');
2935
$result->filter(key1 => sub { $_[0] . 'f' });
2936
is($result->fetch_first->[0], '1abcdef');
2937

            
2938
$dbi = DBIx::Custom->connect(%memory);
2939
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2940
$dbi->type_rule(
2941
    from1 => {
2942
        date => sub { $_[0] . 'p' }
2943
    },
2944
    from2 => {
2945
        date => sub { $_[0] . 'q' }
2946
    },
2947
);
2948
$dbi->insert({key1 => '1'}, table => 'table1');
2949
$result = $dbi->select(table => 'table1');
2950
$result->type_rule(
2951
    from1 => {
2952
        date => sub { $_[0] . 'd' }
2953
    },
2954
    from2 => {
2955
        date => sub { $_[0] . 'e' }
2956
    }
2957
);
2958
$result->filter(key1 => sub { $_[0] . 'f' });
2959
is($result->fetch_first->[0], '1def');
2960

            
2961
test 'type_rule_off';
2962
$dbi = DBIx::Custom->connect(%memory);
2963
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2964
$dbi->type_rule(
2965
    from1 => {
2966
        date => sub { $_[0] * 2 },
2967
    },
2968
    into1 => {
2969
        date => sub { $_[0] * 2 },
2970
    }
2971
);
2972
$dbi->insert({key1 => 2}, table => 'table1', type_rule_off => 1);
2973
$result = $dbi->select(table => 'table1', type_rule_off => 1);
2974
is($result->type_rule_off->fetch->[0], 2);
2975

            
2976
$dbi = DBIx::Custom->connect(%memory);
2977
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2978
$dbi->type_rule(
2979
    from1 => {
2980
        date => sub { $_[0] * 2 },
2981
    },
2982
    into1 => {
2983
        date => sub { $_[0] * 3 },
2984
    }
2985
);
2986
$dbi->insert({key1 => 2}, table => 'table1', type_rule_off => 1);
2987
$result = $dbi->select(table => 'table1', type_rule_off => 1);
2988
is($result->one->{key1}, 4);
2989

            
2990
$dbi = DBIx::Custom->connect(%memory);
2991
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
2992
$dbi->type_rule(
2993
    from1 => {
2994
        date => sub { $_[0] * 2 },
2995
    },
2996
    into1 => {
2997
        date => sub { $_[0] * 3 },
2998
    }
2999
);
3000
$dbi->insert({key1 => 2}, table => 'table1');
3001
$result = $dbi->select(table => 'table1');
3002
is($result->one->{key1}, 12);
3003

            
3004
$dbi = DBIx::Custom->connect(%memory);
3005
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3006
$dbi->type_rule(
3007
    from1 => {
3008
        date => sub { $_[0] * 2 },
3009
    },
3010
    into1 => {
3011
        date => sub { $_[0] * 3 },
3012
    }
3013
);
3014
$dbi->insert({key1 => 2}, table => 'table1');
3015
$result = $dbi->select(table => 'table1');
3016
is($result->fetch->[0], 12);
3017

            
3018
$dbi = DBIx::Custom->connect(%memory);
3019
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3020
$dbi->register_filter(ppp => sub { uc $_[0] });
3021
$dbi->type_rule(
3022
    into1 => {
3023
        date => 'ppp'
3024
    }
3025
);
3026
$dbi->insert({key1 => 'a'}, table => 'table1');
3027
$result = $dbi->select(table => 'table1');
3028
is($result->one->{key1}, 'A');
3029

            
3030
eval{$dbi->type_rule(
3031
    into1 => {
3032
        date => 'pp'
3033
    }
3034
)};
3035
like($@, qr/not registered/);
3036

            
3037
$dbi = DBIx::Custom->connect(%memory);
3038
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3039
eval {
3040
    $dbi->type_rule(
3041
        from1 => {
3042
            Date => sub { $_[0] * 2 },
3043
        }
3044
    );
3045
};
3046
like($@, qr/lower/);
3047

            
3048
eval {
3049
    $dbi->type_rule(
3050
        into1 => {
3051
            Date => sub { $_[0] * 2 },
3052
        }
3053
    );
3054
};
3055
like($@, qr/lower/);
3056

            
3057
$dbi = DBIx::Custom->connect(%memory);
3058
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3059
$dbi->type_rule(
3060
    from1 => {
3061
        date => sub { $_[0] * 2 },
3062
    },
3063
    into1 => {
3064
        date => sub { $_[0] * 3 },
3065
    }
3066
);
3067
$dbi->insert({key1 => 2}, table => 'table1');
3068
$result = $dbi->select(table => 'table1');
3069
$result->type_rule_off;
3070
is($result->one->{key1}, 6);
3071

            
3072
$dbi = DBIx::Custom->connect(%memory);
3073
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3074
$dbi->type_rule(
3075
    from1 => {
3076
        date => sub { $_[0] * 2 },
3077
        datetime => sub { $_[0] * 4 },
3078
    },
3079
);
3080
$dbi->insert({key1 => 2, key2 => 2}, table => 'table1');
3081
$result = $dbi->select(table => 'table1');
3082
$result->type_rule(
3083
    from1 => {
3084
        date => sub { $_[0] * 3 }
3085
    }
3086
);
3087
$row = $result->one;
3088
is($row->{key1}, 6);
3089
is($row->{key2}, 2);
3090

            
3091
$result = $dbi->select(table => 'table1');
3092
$result->type_rule(
3093
    from1 => {
3094
        date => sub { $_[0] * 3 }
3095
    }
3096
);
3097
$row = $result->one;
3098
is($row->{key1}, 6);
3099
is($row->{key2}, 2);
3100

            
3101
$result = $dbi->select(table => 'table1');
3102
$result->type_rule(
3103
    from1 => {
3104
        date => sub { $_[0] * 3 }
3105
    }
3106
);
3107
$row = $result->one;
3108
is($row->{key1}, 6);
3109
is($row->{key2}, 2);
3110
$result = $dbi->select(table => 'table1');
3111
$result->type_rule(
3112
    from1 => [date => sub { $_[0] * 3 }]
3113
);
3114
$row = $result->one;
3115
is($row->{key1}, 6);
3116
is($row->{key2}, 2);
3117
$dbi->register_filter(fivetimes => sub { $_[0] * 5});
3118
$result = $dbi->select(table => 'table1');
3119
$result->type_rule(
3120
    from1 => [date => 'fivetimes']
3121
);
3122
$row = $result->one;
3123
is($row->{key1}, 10);
3124
is($row->{key2}, 2);
3125
$result = $dbi->select(table => 'table1');
3126
$result->type_rule(
3127
    from1 => [date => undef]
3128
);
3129
$row = $result->one;
3130
is($row->{key1}, 2);
3131
is($row->{key2}, 2);
3132

            
3133
$dbi = DBIx::Custom->connect(%memory);
3134
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3135
$dbi->type_rule(
3136
    from1 => {
3137
        date => sub { $_[0] * 2 },
3138
    },
3139
);
3140
$dbi->insert({key1 => 2}, table => 'table1');
3141
$result = $dbi->select(table => 'table1');
3142
$result->filter(key1 => sub { $_[0] * 3 });
3143
is($result->one->{key1}, 12);
3144

            
3145
$dbi = DBIx::Custom->connect(%memory);
3146
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3147
$dbi->type_rule(
3148
    from1 => {
3149
        date => sub { $_[0] * 2 },
3150
    },
3151
);
3152
$dbi->insert({key1 => 2}, table => 'table1');
3153
$result = $dbi->select(table => 'table1');
3154
$result->filter(key1 => sub { $_[0] * 3 });
3155
is($result->fetch->[0], 12);
3156

            
3157
$dbi = DBIx::Custom->connect(%memory);
3158
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3159
$dbi->type_rule(
3160
    into1 => {
3161
        date => sub { $_[0] . 'b' }
3162
    },
3163
    into2 => {
3164
        date => sub { $_[0] . 'c' }
3165
    },
3166
    from1 => {
3167
        date => sub { $_[0] . 'd' }
3168
    },
3169
    from2 => {
3170
        date => sub { $_[0] . 'e' }
3171
    }
3172
);
3173
$dbi->insert({key1 => '1'}, table => 'table1', type_rule_off => 1);
3174
$result = $dbi->select(table => 'table1');
3175
is($result->type_rule_off->fetch_first->[0], '1');
3176
$result = $dbi->select(table => 'table1');
3177
is($result->type_rule_on->fetch_first->[0], '1de');
3178

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

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

            
3223
test 'separator';
3224
$dbi = DBIx::Custom->connect(%memory);
3225
$dbi->execute($create_table_default);
3226
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
3227

            
3228
$dbi->create_model(
3229
    table => 'table1',
3230
    join => [
3231
       'left outer join table2 on table1.key1 = table2.key1'
3232
    ],
3233
    primary_key => ['key1'],
3234
);
3235
$model2 = $dbi->create_model(
3236
    table => 'table2',
3237
);
3238
$dbi->setup_model;
3239
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3240
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
3241
$model = $dbi->model('table1');
3242
$result = $model->select(
3243
    column => [
3244
        $model->mycolumn,
3245
        {table2 => [qw/key1 key3/]}
3246
    ],
3247
    where => {'table1.key1' => 1}
3248
);
3249
is_deeply($result->one,
3250
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
3251
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
3252

            
3253
$dbi->separator('__');
3254
$model = $dbi->model('table1');
3255
$result = $model->select(
3256
    column => [
3257
        $model->mycolumn,
3258
        {table2 => [qw/key1 key3/]}
3259
    ],
3260
    where => {'table1.key1' => 1}
3261
);
3262
is_deeply($result->one,
3263
          {key1 => 1, key2 => 2, 'table2__key1' => 1, 'table2__key3' => 3});
3264
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
3265

            
3266
$dbi->separator('-');
3267
$model = $dbi->model('table1');
3268
$result = $model->select(
3269
    column => [
3270
        $model->mycolumn,
3271
        {table2 => [qw/key1 key3/]}
3272
    ],
3273
    where => {'table1.key1' => 1}
3274
);
3275
is_deeply($result->one,
3276
          {key1 => 1, key2 => 2, 'table2-key1' => 1, 'table2-key3' => 3});
3277
is_deeply($model2->select->one, {key1 => 1, key3 => 3});
3278

            
3279

            
3280
test 'filter_off';
3281
$dbi = DBIx::Custom->connect(%memory);
3282
$dbi->execute($create_table_default);
3283
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
3284

            
3285
$dbi->create_model(
3286
    table => 'table1',
3287
    join => [
3288
       'left outer join table2 on table1.key1 = table2.key1'
3289
    ],
3290
    primary_key => ['key1'],
3291
);
3292
$dbi->setup_model;
3293
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3294
$model = $dbi->model('table1');
3295
$result = $model->select(column => 'key1');
3296
$result->filter(key1 => sub { $_[0] * 2 });
3297
is_deeply($result->one, {key1 => 2});
3298

            
3299
test 'available_date_type';
3300
$dbi = DBIx::Custom->connect(%memory);
3301
ok($dbi->can('available_data_type'));
3302

            
3303

            
3304
test 'select prefix option';
3305
$dbi = DBIx::Custom->connect(%memory);
3306
$dbi->execute($create_table_default);
3307
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3308
$rows = $dbi->select(prefix => 'key1,', column => 'key2', table => 'table1')->all;
3309
is_deeply($rows, [{key1 => 1, key2 => 2}], "table");
3310

            
3311

            
3312
test 'separator';
3313
$dbi = DBIx::Custom->connect(%memory);
3314
is($dbi->separator, '.');
3315
$dbi->separator('-');
3316
is($dbi->separator, '-');
3317
$dbi->separator('__');
3318
is($dbi->separator, '__');
3319
eval { $dbi->separator('?') };
3320
like($@, qr/Separator/);
3321

            
3322

            
3323
test 'map_param';
3324
$dbi = DBIx::Custom->connect(%memory);
3325
$param = $dbi->map_param(
3326
    {id => 1, author => 'Ken', price => 1900},
3327
    id => 'book.id',
3328
    author => ['book.author', sub { '%' . $_[0] . '%' }],
3329
    price => ['book.price', {if => sub { $_[0] eq 1900 }}]
3330
);
3331
is_deeply($param, {'book.id' => 1, 'book.author' => '%Ken%',
3332
  'book.price' => 1900});
3333

            
3334
$param = $dbi->map_param(
3335
    {id => 0, author => 0, price => 0},
3336
    id => 'book.id',
3337
    author => ['book.author', sub { '%' . $_[0] . '%' }],
3338
    price => ['book.price', sub { '%' . $_[0] . '%' },
3339
      {if => sub { $_[0] eq 0 }}]
3340
);
3341
is_deeply($param, {'book.id' => 0, 'book.author' => '%0%', 'book.price' => '%0%'});
3342

            
3343
$param = $dbi->map_param(
3344
    {id => '', author => '', price => ''},
3345
    id => 'book.id',
3346
    author => ['book.author', sub { '%' . $_[0] . '%' }],
3347
    price => ['book.price', sub { '%' . $_[0] . '%' },
3348
      {if => sub { $_[0] eq 1 }}]
3349
);
3350
is_deeply($param, {});
3351

            
3352
$param = $dbi->map_param(
3353
    {id => undef, author => undef, price => undef},
3354
    id => 'book.id',
3355
    price => ['book.price', {if => 'exists'}]
3356
);
3357
is_deeply($param, {'book.price' => undef});
3358

            
3359
$param = $dbi->map_param(
3360
    {price => 'a'},
3361
    id => ['book.id', {if => 'exists'}],
3362
    price => ['book.price', sub { '%' . $_[0] }, {if => 'exists'}]
3363
);
3364
is_deeply($param, {'book.price' => '%a'});
3365

            
3366

            
3367
test 'table_alias';
3368
$dbi = DBIx::Custom->connect(%memory);
3369
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3370
$dbi->type_rule(
3371
    into1 => {
3372
        date => sub { uc $_[0] }
3373
    }
3374
);
3375
$dbi->execute("insert into table1 (key1) values (:table2.key1)", {'table2.key1' => 'a'},
3376
  table_alias => {table2 => 'table1'});
3377
$result = $dbi->select(table => 'table1');
3378
is($result->one->{key1}, 'A');
3379

            
3380

            
3381
test 'order';
3382
$dbi = DBIx::Custom->connect(%memory);
3383
$dbi->execute("create table table1 (key1, key2)");
3384
$dbi->insert({key1 => 1, key2 => 1}, table => 'table1');
3385
$dbi->insert({key1 => 1, key2 => 3}, table => 'table1');
3386
$dbi->insert({key1 => 2, key2 => 2}, table => 'table1');
3387
$dbi->insert({key1 => 2, key2 => 4}, table => 'table1');
3388
my $order = $dbi->order;
3389
$order->prepend('key1', 'key2 desc');
3390
$result = $dbi->select(table => 'table1', append => "$order");
3391
is_deeply($result->all, [{key1 => 1, key2 => 3}, {key1 => 1, key2 => 1},
3392
  {key1 => 2, key2 => 4}, {key1 => 2, key2 => 2}]);
3393
$order->prepend('key1 desc');
3394
$result = $dbi->select(table => 'table1', append => "$order");
3395
is_deeply($result->all, [{key1 => 2, key2 => 4}, {key1 => 2, key2 => 2},
3396
  {key1 => 1, key2 => 3}, {key1 => 1, key2 => 1}]);
3397

            
3398
$order = $dbi->order;
3399
$order->prepend(['table1-key1'], [qw/table1-key2 desc/]);
3400
$result = $dbi->select(table => 'table1',
3401
  column => [[key1 => 'table1-key1'], [key2 => 'table1-key2']],
3402
  append => "$order");
3403
is_deeply($result->all, [{'table1-key1' => 1, 'table1-key2' => 3},
3404
  {'table1-key1' => 1, 'table1-key2' => 1},
3405
  {'table1-key1' => 2, 'table1-key2' => 4},
3406
  {'table1-key1' => 2, 'table1-key2' => 2}]);
3407

            
3408
test 'tag_parse';
3409
$dbi = DBIx::Custom->connect(%memory);
3410
$dbi->tag_parse(0);
3411
$dbi->execute("create table table1 (key1, key2)");
3412
$dbi->insert({key1 => 1, key2 => 1}, table => 'table1');
3413
eval {$dbi->execute("select * from table1 where {= key1}", {key1 => 1})};
3414
ok($@);
3415

            
3416
test 'last_sql';
3417
$dbi = DBIx::Custom->connect(%memory);
3418
$dbi->execute("create table table1 (key1, key2)");
3419
$dbi->execute('select * from table1');
3420
is($dbi->last_sql, 'select * from table1;');
3421

            
3422
eval{$dbi->execute("aaa")};
3423
is($dbi->last_sql, 'aaa;');
3424

            
3425
test 'DBIx::Custom header';
3426
$dbi = DBIx::Custom->connect(%memory);
3427
$dbi->execute("create table table1 (key1, key2)");
3428
$result = $dbi->execute('select key1 as h1, key2 as h2 from table1');
3429
is_deeply($result->header, [qw/h1 h2/]);
3430

            
3431
test 'Named placeholder :name(operater) syntax';
3432
$dbi->execute('drop table table1');
3433
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
3434
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3435
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3436

            
3437
$source = "select * from table1 where :key1{=} and :key2{=}";
3438
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
3439
$rows = $result->all;
3440
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3441

            
3442
$source = "select * from table1 where :key1{ = } and :key2{=}";
3443
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
3444
$rows = $result->all;
3445
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3446

            
3447
$source = "select * from table1 where :key1{<} and :key2{=}";
3448
$result = $dbi->execute($source, param => {key1 => 5, key2 => 2});
3449
$rows = $result->all;
3450
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3451

            
3452
$source = "select * from table1 where :table1.key1{=} and :table1.key2{=}";
3453
$result = $dbi->execute(
3454
    $source,
3455
    param => {'table1.key1' => 1, 'table1.key2' => 1},
3456
    filter => {'table1.key2' => sub { $_[0] * 2 }}
3457
);
3458
$rows = $result->all;
3459
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3460

            
3461
test 'high perfomance way';
3462
$dbi->execute('drop table table1');
3463
$dbi->execute("create table table1 (ab, bc, ik, hi, ui, pq, dc);");
3464
$rows = [
3465
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3466
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3467
];
3468
{
3469
    my $query;
3470
    foreach my $row (@$rows) {
3471
      $query ||= $dbi->insert($row, table => 'table1', query => 1);
3472
      $dbi->execute($query, $row, filter => {ab => sub { $_[0] * 2 }});
3473
    }
3474
    is_deeply($dbi->select(table => 'table1')->all,
3475
      [
3476
          {ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3477
          {ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3478
      ]
3479
    );
3480
}
3481

            
3482
$dbi->execute('drop table table1');
3483
$dbi->execute("create table table1 (ab, bc, ik, hi, ui, pq, dc);");
3484
$rows = [
3485
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3486
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3487
];
3488
{
3489
    my $query;
3490
    my $sth;
3491
    foreach my $row (@$rows) {
3492
      $query ||= $dbi->insert($row, table => 'table1', query => 1);
3493
      $sth ||= $query->sth;
3494
      $sth->execute(map { $row->{$_} } sort keys %$row);
3495
    }
3496
    is_deeply($dbi->select(table => 'table1')->all,
3497
      [
3498
          {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3499
          {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3500
      ]
3501
    );
3502
}
3503

            
3504
test 'result';
3505
$dbi = DBIx::Custom->connect(%memory);
3506
$dbi->execute($create_table_default);
3507
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
3508
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
3509

            
3510
$result = $dbi->select(table => 'table1');
3511
@rows = ();
3512
while (my $row = $result->fetch) {
3513
    push @rows, [@$row];
3514
}
3515
is_deeply(\@rows, [[1, 2], [3, 4]]);
3516

            
3517
$result = $dbi->select(table => 'table1');
3518
@rows = ();
3519
while (my $row = $result->fetch_hash) {
3520
    push @rows, {%$row};
3521
}
3522
is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
3523

            
3524
$result = $dbi->select(table => 'table1');
3525
$row = $result->fetch_first;
3526
is_deeply($row, [1, 2], "row");
3527
$row = $result->fetch;
3528
ok(!$row, "finished");
3529

            
3530
$result = $dbi->select(table => 'table1');
3531
$row = $result->fetch_hash_first;
3532
is_deeply($row, {key1 => 1, key2 => 2}, "row");
3533
$row = $result->fetch_hash;
3534
ok(!$row, "finished");
3535

            
3536
$dbi->execute('create table table2 (key1, key2);');
3537
$result = $dbi->select(table => 'table2');
3538
$row = $result->fetch_hash_first;
3539
ok(!$row, "no row fetch");
3540

            
3541
$dbi = DBIx::Custom->connect(%memory);
3542
$dbi->execute($create_table_default);
3543
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
3544
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
3545
$dbi->insert({key1 => 5, key2 => 6}, table => 'table1');
3546
$dbi->insert({key1 => 7, key2 => 8}, table => 'table1');
3547
$dbi->insert({key1 => 9, key2 => 10}, table => 'table1');
3548
$result = $dbi->select(table => 'table1');
3549
$rows = $result->fetch_multi(2);
3550
is_deeply($rows, [[1, 2],
3551
                  [3, 4]], "fetch_multi first");
3552
$rows = $result->fetch_multi(2);
3553
is_deeply($rows, [[5, 6],
3554
                  [7, 8]], "fetch_multi secound");
3555
$rows = $result->fetch_multi(2);
3556
is_deeply($rows, [[9, 10]], "fetch_multi third");
3557
$rows = $result->fetch_multi(2);
3558
ok(!$rows);
3559

            
3560
$result = $dbi->select(table => 'table1');
3561
eval {$result->fetch_multi};
3562
like($@, qr/Row count must be specified/, "Not specified row count");
3563

            
3564
$result = $dbi->select(table => 'table1');
3565
$rows = $result->fetch_hash_multi(2);
3566
is_deeply($rows, [{key1 => 1, key2 => 2},
3567
                  {key1 => 3, key2 => 4}], "fetch_multi first");
3568
$rows = $result->fetch_hash_multi(2);
3569
is_deeply($rows, [{key1 => 5, key2 => 6},
3570
                  {key1 => 7, key2 => 8}], "fetch_multi secound");
3571
$rows = $result->fetch_hash_multi(2);
3572
is_deeply($rows, [{key1 => 9, key2 => 10}], "fetch_multi third");
3573
$rows = $result->fetch_hash_multi(2);
3574
ok(!$rows);
3575

            
3576
$result = $dbi->select(table => 'table1');
3577
eval {$result->fetch_hash_multi};
3578
like($@, qr/Row count must be specified/, "Not specified row count");
3579

            
3580
$dbi = DBIx::Custom->connect(%memory);
3581
$dbi->execute($create_table_default);
3582
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
3583
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
3584

            
3585
test 'fetch_all';
3586
$result = $dbi->select(table => 'table1');
3587
$rows = $result->fetch_all;
3588
is_deeply($rows, [[1, 2], [3, 4]]);
3589

            
3590
$result = $dbi->select(table => 'table1');
3591
$rows = $result->fetch_hash_all;
3592
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}]);
3593

            
3594
$result = $dbi->select(table => 'table1');
3595
$result->dbi->filters({three_times => sub { $_[0] * 3}});
3596
$result->filter({key1 => 'three_times'});
3597

            
3598
$rows = $result->fetch_all;
3599
is_deeply($rows, [[3, 2], [9, 4]], "array");
3600

            
3601
$result = $dbi->select(table => 'table1');
3602
$result->dbi->filters({three_times => sub { $_[0] * 3}});
3603
$result->filter({key1 => 'three_times'});
3604
$rows = $result->fetch_hash_all;
3605
is_deeply($rows, [{key1 => 3, key2 => 2}, {key1 => 9, key2 => 4}], "hash");
3606

            
3607
test "query_builder";
3608
$datas = [
3609
    # Basic tests
3610
    {   name            => 'placeholder basic',
3611
        source            => "a {?  k1} b {=  k2} {<> k3} {>  k4} {<  k5} {>= k6} {<= k7} {like k8}", ,
3612
        sql_expected    => "a ? b k2 = ? k3 <> ? k4 > ? k5 < ? k6 >= ? k7 <= ? k8 like ?;",
3613
        columns_expected   => [qw/k1 k2 k3 k4 k5 k6 k7 k8/]
3614
    },
3615
    {
3616
        name            => 'placeholder in',
3617
        source            => "{in k1 3};",
3618
        sql_expected    => "k1 in (?, ?, ?);",
3619
        columns_expected   => [qw/k1 k1 k1/]
3620
    },
3621
    
3622
    # Table name
3623
    {
3624
        name            => 'placeholder with table name',
3625
        source            => "{= a.k1} {= a.k2}",
3626
        sql_expected    => "a.k1 = ? a.k2 = ?;",
3627
        columns_expected  => [qw/a.k1 a.k2/]
3628
    },
3629
    {   
3630
        name            => 'placeholder in with table name',
3631
        source            => "{in a.k1 2} {in b.k2 2}",
3632
        sql_expected    => "a.k1 in (?, ?) b.k2 in (?, ?);",
3633
        columns_expected  => [qw/a.k1 a.k1 b.k2 b.k2/]
3634
    },
3635
    {
3636
        name            => 'not contain tag',
3637
        source            => "aaa",
3638
        sql_expected    => "aaa;",
3639
        columns_expected  => [],
3640
    }
3641
];
3642

            
3643
for (my $i = 0; $i < @$datas; $i++) {
3644
    my $data = $datas->[$i];
3645
    my $builder = DBIx::Custom->new->query_builder;
3646
    my $query = $builder->build_query($data->{source});
3647
    is($query->{sql}, $data->{sql_expected}, "$data->{name} : sql");
3648
    is_deeply($query->columns, $data->{columns_expected}, "$data->{name} : columns");
3649
}
3650

            
3651
$builder = DBIx::Custom->new->query_builder;
3652
$ret_val = $builder->register_tag(
3653
    p => sub {
3654
        my @args = @_;
3655
        
3656
        my $expand    = "? $args[0] $args[1]";
3657
        my $columns = [2];
3658
        return [$expand, $columns];
3659
    }
3660
);
3661

            
3662
$query = $builder->build_query("{p a b}");
3663
is($query->{sql}, "? a b;", "register_tag sql");
3664
is_deeply($query->{columns}, [2], "register_tag columns");
3665
isa_ok($ret_val, 'DBIx::Custom::QueryBuilder');
3666

            
3667
$builder = DBIx::Custom->new->query_builder;
3668

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

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

            
3675
$builder->register_tag({
3676
    q => 'string'
3677
});
3678

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

            
3682
$builder->register_tag({
3683
   r => sub {} 
3684
});
3685

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

            
3689
$builder->register_tag({
3690
   s => sub { return ["a", ""]} 
3691
});
3692

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

            
3696
$builder->register_tag(
3697
    t => sub {return ["a", []]}
3698
);
3699

            
3700

            
3701
test 'General error case';
3702
$builder = DBIx::Custom->new->query_builder;
3703
$builder->register_tag(
3704
    a => sub {
3705
        return ["? ? ?", ['']];
3706
    }
3707
);
3708
eval{$builder->build_query("{a}")};
3709
like($@, qr/\QPlaceholder count/, "placeholder count is invalid");
3710

            
3711

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

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

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

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

            
3727
test 'variouse source';
3728
$source = "a {= b} c \\{ \\} {= \\{} {= \\}} d;";
3729
$query = $builder->build_query($source);
3730
is($query->sql, 'a b = ? c { } { = ? } = ? d;', "basic : 1");
3731

            
3732
$source = "abc;";
3733
$query = $builder->build_query($source);
3734
is($query->sql, 'abc;', "basic : 2");
3735

            
3736
$source = "{= a}";
3737
$query = $builder->build_query($source);
3738
is($query->sql, 'a = ?;', "only tag");
3739

            
3740
$source = "000;";
3741
$query = $builder->build_query($source);
3742
is($query->sql, '000;', "contain 0 value");
3743

            
3744
$source = "a {= b} }";
3745
eval{$builder->build_query($source)};
3746
like($@, qr/unexpected "}"/, "error : 1");
3747

            
3748
$source = "a {= {}";
3749
eval{$builder->build_query($source)};
3750
like($@, qr/unexpected "{"/, "error : 2");