DBIx-Custom / t / basic.t /
Newer Older
3518 lines | 116.401kb
cleanup test
Yuki Kimoto authored on 2011-08-06
1
use Test::More;
2
use strict;
3
use warnings;
4
use utf8;
5
use Encode qw/encode_utf8 decode_utf8/;
test cleanup
Yuki Kimoto authored on 2011-08-06
6
use FindBin;
7
use lib "$FindBin::Bin/basic";
cleanup test
Yuki Kimoto authored on 2011-08-06
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

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

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

            
31
# Variables
32
my $dbi;
33
my $sth;
34
my $source;
35
my @sources;
36
my $select_SOURCE;
37
my $insert_SOURCE;
38
my $update_SOURCE;
39
my $param;
40
my $params;
41
my $sql;
42
my $result;
43
my $row;
44
my @rows;
45
my $rows;
46
my $query;
47
my @queries;
48
my $select_query;
49
my $insert_query;
50
my $update_query;
51
my $ret_val;
52
my $infos;
53
my $model;
54
my $model2;
55
my $where;
56
my $update_param;
57
my $insert_param;
58
my $join;
59

            
60
# Prepare table
cleanup test
Yuki Kimoto authored on 2011-08-06
61
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
62
$dbi->execute($CREATE_TABLE->{0});
63
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
64
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
65

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

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

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

            
84
$result = $dbi->execute($query);
85
$rows = $result->fetch_all;
86
is_deeply($rows, [[1, 2], [3, 4]], "fetch_all");
87

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

            
92
test 'Insert query return value';
cleanup test
Yuki Kimoto authored on 2011-08-06
93
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
94
$dbi->execute($CREATE_TABLE->{0});
95
$source = "insert into table1 {insert_param key1 key2}";
96
$query = $dbi->execute($source, {}, query => 1);
97
$ret_val = $dbi->execute($query, param => {key1 => 1, key2 => 2});
98
ok($ret_val);
99

            
100

            
101
test 'Direct query';
cleanup test
Yuki Kimoto authored on 2011-08-06
102
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
103
$dbi->execute($CREATE_TABLE->{0});
104
$insert_SOURCE = "insert into table1 {insert_param key1 key2}";
105
$dbi->execute($insert_SOURCE, param => {key1 => 1, key2 => 2});
test cleanup
Yuki Kimoto authored on 2011-08-06
106
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
107
$rows = $result->all;
108
is_deeply($rows, [{key1 => 1, key2 => 2}]);
109

            
110
test 'Filter basic';
cleanup test
Yuki Kimoto authored on 2011-08-06
111
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
112
$dbi->execute($CREATE_TABLE->{0});
113
$dbi->register_filter(twice       => sub { $_[0] * 2}, 
114
                    three_times => sub { $_[0] * 3});
115

            
116
$insert_SOURCE  = "insert into table1 {insert_param key1 key2};";
117
$insert_query = $dbi->execute($insert_SOURCE, {}, query => 1);
118
$insert_query->filter({key1 => 'twice'});
119
$dbi->execute($insert_query, param => {key1 => 1, key2 => 2});
test cleanup
Yuki Kimoto authored on 2011-08-06
120
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
121
$rows = $result->filter({key2 => 'three_times'})->all;
122
is_deeply($rows, [{key1 => 2, key2 => 6}], "filter fetch_filter");
cleanup test
Yuki Kimoto authored on 2011-08-06
123
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
124

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

            
137
test 'DBIx::Custom::SQLTemplate basic tag';
cleanup test
Yuki Kimoto authored on 2011-08-06
138
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
139
$dbi->execute($CREATE_TABLE->{1});
140
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
141
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
142

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

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

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

            
161
test 'DIB::Custom::SQLTemplate in tag';
cleanup test
Yuki Kimoto authored on 2011-08-06
162
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
163
$dbi->execute($CREATE_TABLE->{1});
164
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
165
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
166

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

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

            
test cleanup
Yuki Kimoto authored on 2011-08-06
178
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
179
$rows = $result->all;
180
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic");
181

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

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

            
test cleanup
Yuki Kimoto authored on 2011-08-06
191
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
192
$rows = $result->all;
193
is_deeply($rows, [{key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5},
194
                  {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}], "basic");
195

            
196

            
197
test 'Named placeholder';
cleanup test
Yuki Kimoto authored on 2011-08-06
198
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
199
$dbi->execute($CREATE_TABLE->{1});
200
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
201
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
202

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

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
227
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
228
$dbi->execute($CREATE_TABLE->{0});
229
$dbi->insert(table => 'table1', param => {key1 => '2011-10-14 12:19:18', key2 => 2});
230
$source = "select * from table1 where key1 = '2011-10-14 12:19:18' and key2 = :key2";
231
$result = $dbi->execute(
232
    $source,
233
    param => {'key2' => 2},
234
);
235

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
239
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
240
$dbi->execute($CREATE_TABLE->{0});
241
$dbi->insert(table => 'table1', param => {key1 => 'a:b c:d', key2 => 2});
242
$source = "select * from table1 where key1 = 'a\\:b c\\:d' and key2 = :key2";
243
$result = $dbi->execute(
244
    $source,
245
    param => {'key2' => 2},
246
);
247
$rows = $result->all;
248
is_deeply($rows, [{key1 => 'a:b c:d', key2 => 2}]);
249

            
250

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
255
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
256
eval{$dbi->execute("{p }", {}, query => 1)};
257
ok($@, "create_query invalid SQL template");
258

            
259
test 'insert';
cleanup test
Yuki Kimoto authored on 2011-08-06
260
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
261
$dbi->execute($CREATE_TABLE->{0});
262
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
263
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
test cleanup
Yuki Kimoto authored on 2011-08-06
264
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
265
$rows   = $result->all;
266
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
267

            
268
$dbi->execute('delete from table1');
269
$dbi->register_filter(
270
    twice       => sub { $_[0] * 2 },
271
    three_times => sub { $_[0] * 3 }
272
);
273
$dbi->default_bind_filter('twice');
274
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => 'three_times'});
test cleanup
Yuki Kimoto authored on 2011-08-06
275
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
276
$rows   = $result->all;
277
is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
278
$dbi->default_bind_filter(undef);
279

            
cleanup test
Yuki Kimoto authored on 2011-08-06
280
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
281
$dbi->execute($CREATE_TABLE->{0});
282
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, append => '   ');
283
$rows = $dbi->select(table => 'table1')->all;
284
is_deeply($rows, [{key1 => 1, key2 => 2}], 'insert append');
285

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

            
289
eval{$dbi->insert(table => 'table', param => {';' => 1})};
290
like($@, qr/safety/);
291

            
cleanup test
Yuki Kimoto authored on 2011-08-06
292
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
293
$dbi->quote('"');
294
$dbi->execute('create table "table" ("select")');
295
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
296
$dbi->insert(table => 'table', param => {select => 1});
297
$result = $dbi->execute('select * from "table"');
298
$rows   = $result->all;
299
is_deeply($rows, [{select => 2}], "reserved word");
300

            
cleanup test
Yuki Kimoto authored on 2011-08-06
301
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
302
$dbi->execute($CREATE_TABLE->{0});
303
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
304
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
test cleanup
Yuki Kimoto authored on 2011-08-06
305
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
306
$rows   = $result->all;
307
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
308

            
cleanup test
Yuki Kimoto authored on 2011-08-06
309
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
310
$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");
311
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
312
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 4}, prefix => 'or replace');
test cleanup
Yuki Kimoto authored on 2011-08-06
313
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
314
$rows   = $result->all;
315
is_deeply($rows, [{key1 => 1, key2 => 4}], "basic");
316

            
cleanup test
Yuki Kimoto authored on 2011-08-06
317
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
318
$dbi->execute($CREATE_TABLE->{0});
319
$dbi->insert(table => 'table1', param => {key1 => \"'1'", key2 => 2});
320
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
test cleanup
Yuki Kimoto authored on 2011-08-06
321
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
322
$rows   = $result->all;
323
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
324

            
325
test 'update';
cleanup test
Yuki Kimoto authored on 2011-08-06
326
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
327
$dbi->execute($CREATE_TABLE->{1});
328
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
329
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
330
$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1});
test cleanup
Yuki Kimoto authored on 2011-08-06
331
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
332
$rows   = $result->all;
333
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
334
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
335
                  "basic");
336
                  
337
$dbi->execute("delete from table1");
338
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
339
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
340
$dbi->update(table => 'table1', param => {key2 => 12}, where => {key2 => 2, key3 => 3});
test cleanup
Yuki Kimoto authored on 2011-08-06
341
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
342
$rows   = $result->all;
343
is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
344
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
345
                  "update key same as search key");
346

            
347
$dbi->update(table => 'table1', param => {key2 => [12]}, where => {key2 => 2, key3 => 3});
test cleanup
Yuki Kimoto authored on 2011-08-06
348
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
349
$rows   = $result->all;
350
is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
351
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
352
                  "update key same as search key : param is array ref");
353

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

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
374
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
375
$dbi->execute($CREATE_TABLE->{0});
376
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
377
$where = $dbi->where;
378
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
379
$where->param({key1 => 1, key2 => 2});
380
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
381
$result = $dbi->select(table => 'table1');
382
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
383

            
cleanup test
Yuki Kimoto authored on 2011-08-06
384
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
385
$dbi->execute($CREATE_TABLE->{0});
386
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
387
$dbi->update(
388
    table => 'table1',
389
    param => {key1 => 3},
390
    where => [
391
        ['and', 'key1 = :key1', 'key2 = :key2'],
392
        {key1 => 1, key2 => 2}
393
    ]
394
);
395
$result = $dbi->select(table => 'table1');
396
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
397

            
cleanup test
Yuki Kimoto authored on 2011-08-06
398
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
399
$dbi->execute($CREATE_TABLE->{0});
400
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
401
$where = $dbi->where;
402
$where->clause(['and', 'key2 = :key2']);
403
$where->param({key2 => 2});
404
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
405
$result = $dbi->select(table => 'table1');
406
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
407

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
414
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
415
$dbi->quote('"');
416
$dbi->execute('create table "table" ("select", "update")');
417
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
418
$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
419
$dbi->insert(table => 'table', param => {select => 1});
420
$dbi->update(table => 'table', where => {select => 1}, param => {update => 2});
421
$result = $dbi->execute('select * from "table"');
422
$rows   = $result->all;
423
is_deeply($rows, [{select => 2, update => 6}], "reserved word");
424

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
428
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
429
$dbi->reserved_word_quote('"');
430
$dbi->execute('create table "table" ("select", "update")');
431
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
432
$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
433
$dbi->insert(table => 'table', param => {select => 1});
434
$dbi->update(table => 'table', where => {'table.select' => 1}, param => {update => 2});
435
$result = $dbi->execute('select * from "table"');
436
$rows   = $result->all;
437
is_deeply($rows, [{select => 2, update => 6}], "reserved word");
438

            
cleanup test
Yuki Kimoto authored on 2011-08-06
439
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
440
$dbi->execute($CREATE_TABLE->{1});
441
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
442
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
443
$dbi->update({key2 => 11}, table => 'table1', where => {key1 => 1});
test cleanup
Yuki Kimoto authored on 2011-08-06
444
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
445
$rows   = $result->all;
446
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
447
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
448
                  "basic");
449

            
cleanup test
Yuki Kimoto authored on 2011-08-06
450
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
451
$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");
452
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
453
$dbi->update(table => 'table1', param => {key2 => 4},
454
  where => {key1 => 1}, prefix => 'or replace');
test cleanup
Yuki Kimoto authored on 2011-08-06
455
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
456
$rows   = $result->all;
457
is_deeply($rows, [{key1 => 1, key2 => 4}], "basic");
458

            
cleanup test
Yuki Kimoto authored on 2011-08-06
459
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
460
$dbi->execute($CREATE_TABLE->{1});
461
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
462
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
463
$dbi->update(table => 'table1', param => {key2 => \"'11'"}, where => {key1 => 1});
test cleanup
Yuki Kimoto authored on 2011-08-06
464
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
465
$rows   = $result->all;
466
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
467
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
468
                  "basic");
469

            
470
test 'update_all';
cleanup test
Yuki Kimoto authored on 2011-08-06
471
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
472
$dbi->execute($CREATE_TABLE->{1});
473
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
474
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
475
$dbi->register_filter(twice => sub { $_[0] * 2 });
476
$dbi->update_all(table => 'table1', param => {key2 => 10}, filter => {key2 => 'twice'});
test cleanup
Yuki Kimoto authored on 2011-08-06
477
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
478
$rows   = $result->all;
479
is_deeply($rows, [{key1 => 1, key2 => 20, key3 => 3, key4 => 4, key5 => 5},
480
                  {key1 => 6, key2 => 20, key3 => 8, key4 => 9, key5 => 10}],
481
                  "filter");
482

            
483

            
484
test 'delete';
cleanup test
Yuki Kimoto authored on 2011-08-06
485
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
486
$dbi->execute($CREATE_TABLE->{0});
487
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
488
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
489
$dbi->delete(table => 'table1', where => {key1 => 1});
test cleanup
Yuki Kimoto authored on 2011-08-06
490
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
491
$rows   = $result->all;
492
is_deeply($rows, [{key1 => 3, key2 => 4}], "basic");
493

            
494
$dbi->execute("delete from table1;");
495
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
496
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
497
$dbi->register_filter(twice => sub { $_[0] * 2 });
498
$dbi->delete(table => 'table1', where => {key2 => 1}, filter => {key2 => 'twice'});
test cleanup
Yuki Kimoto authored on 2011-08-06
499
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
500
$rows   = $result->all;
501
is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");
502

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
515
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
516
$dbi->execute($CREATE_TABLE->{0});
517
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
518
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
519
$where = $dbi->where;
520
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
521
$where->param({ke1 => 1, key2 => 2});
522
$dbi->delete(table => 'table1', where => $where);
523
$result = $dbi->select(table => 'table1');
524
is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
525

            
cleanup test
Yuki Kimoto authored on 2011-08-06
526
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
527
$dbi->execute($CREATE_TABLE->{0});
528
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
529
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
530
$dbi->delete(
531
    table => 'table1',
532
    where => [
533
        ['and', 'key1 = :key1', 'key2 = :key2'],
534
        {ke1 => 1, key2 => 2}
535
    ]
536
);
537
$result = $dbi->select(table => 'table1');
538
is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
539

            
cleanup test
Yuki Kimoto authored on 2011-08-06
540
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
541
$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");
542
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
543
$dbi->delete(table => 'table1', where => {key1 => 1}, prefix => '    ');
test cleanup
Yuki Kimoto authored on 2011-08-06
544
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
545
$rows   = $result->all;
546
is_deeply($rows, [], "basic");
547

            
548
test 'delete error';
cleanup test
Yuki Kimoto authored on 2011-08-06
549
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
550
$dbi->execute($CREATE_TABLE->{0});
551
eval{$dbi->delete(table => 'table1')};
552
like($@, qr/"where" must be specified/,
553
         "where key-value pairs not specified");
554

            
555
eval{$dbi->delete(table => 'table1', where => {';' => 1})};
556
like($@, qr/safety/);
557

            
cleanup test
Yuki Kimoto authored on 2011-08-06
558
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
559
$dbi->quote('"');
560
$dbi->execute('create table "table" ("select", "update")');
561
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
562
$dbi->insert(table => 'table', param => {select => 1});
563
$dbi->delete(table => 'table', where => {select => 1});
564
$result = $dbi->execute('select * from "table"');
565
$rows   = $result->all;
566
is_deeply($rows, [], "reserved word");
567

            
568
test 'delete_all';
cleanup test
Yuki Kimoto authored on 2011-08-06
569
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
570
$dbi->execute($CREATE_TABLE->{0});
571
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
572
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
573
$dbi->delete_all(table => 'table1');
test cleanup
Yuki Kimoto authored on 2011-08-06
574
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
575
$rows   = $result->all;
576
is_deeply($rows, [], "basic");
577

            
578

            
579
test 'select';
cleanup test
Yuki Kimoto authored on 2011-08-06
580
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
581
$dbi->execute($CREATE_TABLE->{0});
582
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
583
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
584
$rows = $dbi->select(table => 'table1')->all;
585
is_deeply($rows, [{key1 => 1, key2 => 2},
586
                  {key1 => 3, key2 => 4}], "table");
587

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

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

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

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

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

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
625
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
626
$dbi->quote('"');
627
$dbi->execute('create table "table" ("select", "update")');
628
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
629
$dbi->insert(table => 'table', param => {select => 1, update => 2});
630
$result = $dbi->select(table => 'table', where => {select => 1});
631
$rows   = $result->all;
632
is_deeply($rows, [{select => 2, update => 2}], "reserved word");
633

            
634
test 'fetch filter';
cleanup test
Yuki Kimoto authored on 2011-08-06
635
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
636
$dbi->register_filter(
637
    twice       => sub { $_[0] * 2 },
638
    three_times => sub { $_[0] * 3 }
639
);
640
$dbi->default_fetch_filter('twice');
641
$dbi->execute($CREATE_TABLE->{0});
642
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
643
$result = $dbi->select(table => 'table1');
644
$result->filter({key1 => 'three_times'});
645
$row = $result->one;
646
is_deeply($row, {key1 => 3, key2 => 4}, "default_fetch_filter and filter");
647

            
648
test 'filters';
649
$dbi = DBIx::Custom->new;
650

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

            
654
is($dbi->filters->{encode_utf8}->('あ'),
655
   encode_utf8('あ'), "encode_utf8");
656

            
657
test 'transaction';
cleanup test
Yuki Kimoto authored on 2011-08-06
658
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
659
$dbi->execute($CREATE_TABLE->{0});
660
$dbi->dbh->begin_work;
661
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
662
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
663
$dbi->dbh->commit;
664
$result = $dbi->select(table => 'table1');
665
is_deeply(scalar $result->all, [{key1 => 1, key2 => 2}, {key1 => 2, key2 => 3}],
666
          "commit");
667

            
cleanup test
Yuki Kimoto authored on 2011-08-06
668
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
669
$dbi->execute($CREATE_TABLE->{0});
670
$dbi->dbh->begin_work(0);
671
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
672
$dbi->dbh->rollback;
673

            
674
$result = $dbi->select(table => 'table1');
675
ok(! $result->fetch_first, "rollback");
676

            
677
test 'cache';
cleanup test
Yuki Kimoto authored on 2011-08-06
678
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
679
$dbi->cache(1);
680
$dbi->execute($CREATE_TABLE->{0});
681
$source = 'select * from table1 where key1 = :key1 and key2 = :key2;';
682
$dbi->execute($source, {}, query => 1);
683
is_deeply($dbi->{_cached}->{$source}, 
684
          {sql => "select * from table1 where key1 = ? and key2 = ?;", columns => ['key1', 'key2'], tables => []}, "cache");
685

            
cleanup test
Yuki Kimoto authored on 2011-08-06
686
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
687
$dbi->execute($CREATE_TABLE->{0});
688
$dbi->{_cached} = {};
689
$dbi->cache(0);
690
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
691
is(scalar keys %{$dbi->{_cached}}, 0, 'not cache');
692

            
693
test 'execute';
cleanup test
Yuki Kimoto authored on 2011-08-06
694
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
695
$dbi->execute($CREATE_TABLE->{0});
696
{
697
    local $Carp::Verbose = 0;
698
    eval{$dbi->execute('select * frm table1')};
699
    like($@, qr/\Qselect * frm table1;/, "fail prepare");
700
    like($@, qr/\.t /, "fail : not verbose");
701
}
702
{
703
    local $Carp::Verbose = 1;
704
    eval{$dbi->execute('select * frm table1')};
705
    like($@, qr/Custom.*\.t /s, "fail : verbose");
706
}
707

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

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

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

            
727

            
728
test 'transaction';
cleanup test
Yuki Kimoto authored on 2011-08-06
729
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
730
$dbi->execute($CREATE_TABLE->{0});
731

            
732
$dbi->begin_work;
733

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

            
740
$dbi->rollback if $@;
741

            
742
$result = $dbi->select(table => 'table1');
743
$rows = $result->all;
744
is_deeply($rows, [], "rollback");
745

            
746
$dbi->begin_work;
747

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

            
753
$dbi->commit unless $@;
754

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

            
759
$dbi->dbh->{AutoCommit} = 0;
760
eval{ $dbi->begin_work };
761
ok($@, "exception");
762
$dbi->dbh->{AutoCommit} = 1;
763

            
764

            
765
test 'method';
cleanup test
Yuki Kimoto authored on 2011-08-06
766
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
767
$dbi->method(
768
    one => sub { 1 }
769
);
770
$dbi->method(
771
    two => sub { 2 }
772
);
773
$dbi->method({
774
    twice => sub {
775
        my $self = shift;
776
        return $_[0] * 2;
777
    }
778
});
779

            
780
is($dbi->one, 1, "first");
781
is($dbi->two, 2, "second");
782
is($dbi->twice(5), 10 , "second");
783

            
784
eval {$dbi->XXXXXX};
785
ok($@, "not exists");
786

            
787
test 'out filter';
cleanup test
Yuki Kimoto authored on 2011-08-06
788
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
789
$dbi->execute($CREATE_TABLE->{0});
790
$dbi->register_filter(twice => sub { $_[0] * 2 });
791
$dbi->register_filter(three_times => sub { $_[0] * 3});
792
$dbi->apply_filter(
793
    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 
794
              'key2' => {out => 'three_times', in => 'twice'});
795
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
test cleanup
Yuki Kimoto authored on 2011-08-06
796
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
797
$row   = $result->fetch_hash_first;
798
is_deeply($row, {key1 => 2, key2 => 6}, "insert");
799
$result = $dbi->select(table => 'table1');
800
$row   = $result->one;
801
is_deeply($row, {key1 => 6, key2 => 12}, "insert");
802

            
cleanup test
Yuki Kimoto authored on 2011-08-06
803
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
804
$dbi->execute($CREATE_TABLE->{0});
805
$dbi->register_filter(twice => sub { $_[0] * 2 });
806
$dbi->register_filter(three_times => sub { $_[0] * 3});
807
$dbi->apply_filter(
808
    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 
809
              'key2' => {out => 'three_times', in => 'twice'});
810
$dbi->apply_filter(
811
    'table1', 'key1' => {out => undef}
812
); 
813
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
test cleanup
Yuki Kimoto authored on 2011-08-06
814
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
815
$row   = $result->one;
816
is_deeply($row, {key1 => 1, key2 => 6}, "insert");
817

            
cleanup test
Yuki Kimoto authored on 2011-08-06
818
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
819
$dbi->execute($CREATE_TABLE->{0});
820
$dbi->register_filter(twice => sub { $_[0] * 2 });
821
$dbi->apply_filter(
822
    'table1', 'key1' => {out => 'twice', in => 'twice'}
823
);
824
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => undef});
825
$dbi->update(table => 'table1', param => {key1 => 2}, where => {key2 => 2});
test cleanup
Yuki Kimoto authored on 2011-08-06
826
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
827
$row   = $result->one;
828
is_deeply($row, {key1 => 4, key2 => 2}, "update");
829

            
cleanup test
Yuki Kimoto authored on 2011-08-06
830
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
831
$dbi->execute($CREATE_TABLE->{0});
832
$dbi->register_filter(twice => sub { $_[0] * 2 });
833
$dbi->apply_filter(
834
    'table1', 'key1' => {out => 'twice', in => 'twice'}
835
);
836
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1=> undef});
837
$dbi->delete(table => 'table1', where => {key1 => 1});
test cleanup
Yuki Kimoto authored on 2011-08-06
838
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
839
$rows   = $result->all;
840
is_deeply($rows, [], "delete");
841

            
cleanup test
Yuki Kimoto authored on 2011-08-06
842
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
843
$dbi->execute($CREATE_TABLE->{0});
844
$dbi->register_filter(twice => sub { $_[0] * 2 });
845
$dbi->apply_filter(
846
    'table1', 'key1' => {out => 'twice', in => 'twice'}
847
);
848
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
849
$result = $dbi->select(table => 'table1', where => {key1 => 1});
850
$result->filter({'key2' => 'twice'});
851
$rows   = $result->all;
852
is_deeply($rows, [{key1 => 4, key2 => 4}], "select");
853

            
cleanup test
Yuki Kimoto authored on 2011-08-06
854
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
855
$dbi->execute($CREATE_TABLE->{0});
856
$dbi->register_filter(twice => sub { $_[0] * 2 });
857
$dbi->apply_filter(
858
    'table1', 'key1' => {out => 'twice', in => 'twice'}
859
);
860
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
861
$result = $dbi->execute("select * from table1 where key1 = :key1 and key2 = :key2;",
862
                        param => {key1 => 1, key2 => 2},
863
                        table => ['table1']);
864
$rows   = $result->all;
865
is_deeply($rows, [{key1 => 4, key2 => 2}], "execute");
866

            
cleanup test
Yuki Kimoto authored on 2011-08-06
867
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
868
$dbi->execute($CREATE_TABLE->{0});
869
$dbi->register_filter(twice => sub { $_[0] * 2 });
870
$dbi->apply_filter(
871
    'table1', 'key1' => {out => 'twice', in => 'twice'}
872
);
873
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});
874
$result = $dbi->execute("select * from {table table1} where key1 = :key1 and key2 = :key2;",
875
                        param => {key1 => 1, key2 => 2});
876
$rows   = $result->all;
877
is_deeply($rows, [{key1 => 4, key2 => 2}], "execute table tag");
878

            
cleanup test
Yuki Kimoto authored on 2011-08-06
879
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
880
$dbi->execute($CREATE_TABLE->{0});
881
$dbi->execute($CREATE_TABLE->{2});
882
$dbi->register_filter(twice => sub { $_[0] * 2 });
883
$dbi->register_filter(three_times => sub { $_[0] * 3 });
884
$dbi->apply_filter(
885
    'table1', 'key2' => {out => 'twice', in => 'twice'}
886
);
887
$dbi->apply_filter(
888
    'table2', 'key3' => {out => 'three_times', in => 'three_times'}
889
);
890
$dbi->insert(table => 'table1', param => {key1 => 5, key2 => 2}, filter => {key2 => undef});
891
$dbi->insert(table => 'table2', param => {key1 => 5, key3 => 6}, filter => {key3 => undef});
892
$result = $dbi->select(
893
     table => ['table1', 'table2'],
894
     column => ['key2', 'key3'],
895
     where => {'table1.key2' => 1, 'table2.key3' => 2}, relation => {'table1.key1' => 'table2.key1'});
896

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

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

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

            
910
test 'each_column';
cleanup test
Yuki Kimoto authored on 2011-08-06
911
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
912
$dbi->execute($CREATE_TABLE->{2});
913
$dbi->execute($CREATE_TABLE->{3});
914

            
915
$infos = [];
916
$dbi->each_column(sub {
917
    my ($self, $table, $column, $cinfo) = @_;
918
    
919
    if ($table =~ /^table/) {
920
         my $info = [$table, $column, $cinfo->{COLUMN_NAME}];
921
         push @$infos, $info;
922
    }
923
});
924
$infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
925
is_deeply($infos, 
926
    [
927
        ['table1', 'key1', 'key1'],
928
        ['table1', 'key2', 'key2'],
929
        ['table2', 'key1', 'key1'],
930
        ['table2', 'key3', 'key3']
931
    ]
932
    
933
);
934
test 'each_table';
cleanup test
Yuki Kimoto authored on 2011-08-06
935
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
936
$dbi->execute($CREATE_TABLE->{2});
937
$dbi->execute($CREATE_TABLE->{3});
938

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1010
$dbi = MyDBI->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1011
$dbi->execute($CREATE_TABLE->{0});
1012
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1013
is($dbi->select(table => 'table1')->one->{key1}, 1);
1014

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1015
$dbi = MyDBI->new(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1016
$dbi->connect;
1017
$dbi->execute($CREATE_TABLE->{0});
1018
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1019
is($dbi->select(table => 'table1')->one->{key1}, 1);
1020

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1033
$dbi = MyDBI->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1034
$dbi->execute($CREATE_TABLE->{0});
1035
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1036
is($dbi->select(table => 'table1')->one->{key1}, 1);
1037

            
1038
test 'end_filter';
cleanup test
Yuki Kimoto authored on 2011-08-06
1039
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1040
$dbi->execute($CREATE_TABLE->{0});
1041
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1042
$result = $dbi->select(table => 'table1');
1043
$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });
1044
$result->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 });
1045
$row = $result->fetch_first;
1046
is_deeply($row, [6, 40]);
1047

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1048
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1049
$dbi->execute($CREATE_TABLE->{0});
1050
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1051
$result = $dbi->select(table => 'table1');
1052
$result->filter([qw/key1 key2/] => sub { $_[0] * 2 });
1053
$result->end_filter([[qw/key1 key2/] => sub { $_[0] * 3 }]);
1054
$row = $result->fetch_first;
1055
is_deeply($row, [6, 12]);
1056

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1057
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1058
$dbi->execute($CREATE_TABLE->{0});
1059
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1060
$result = $dbi->select(table => 'table1');
1061
$result->filter([[qw/key1 key2/] => sub { $_[0] * 2 }]);
1062
$result->end_filter([qw/key1 key2/] => sub { $_[0] * 3 });
1063
$row = $result->fetch_first;
1064
is_deeply($row, [6, 12]);
1065

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

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

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

            
1095
test 'remove_end_filter and remove_filter';
cleanup test
Yuki Kimoto authored on 2011-08-06
1096
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1097
$dbi->execute($CREATE_TABLE->{0});
1098
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1099
$result = $dbi->select(table => 'table1');
1100
$row = $result
1101
       ->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 })
1102
       ->remove_filter
1103
       ->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 })
1104
       ->remove_end_filter
1105
       ->fetch_first;
1106
is_deeply($row, [1, 2]);
1107

            
1108
test 'empty where select';
cleanup test
Yuki Kimoto authored on 2011-08-06
1109
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1110
$dbi->execute($CREATE_TABLE->{0});
1111
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1112
$result = $dbi->select(table => 'table1', where => {});
1113
$row = $result->one;
1114
is_deeply($row, {key1 => 1, key2 => 2});
1115

            
1116
test 'select query option';
cleanup test
Yuki Kimoto authored on 2011-08-06
1117
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1118
$dbi->execute($CREATE_TABLE->{0});
1119
$query = $dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, query => 1);
1120
is(ref $query, 'DBIx::Custom::Query');
1121
$query = $dbi->update(table => 'table1', where => {key1 => 1}, param => {key2 => 2}, query => 1);
1122
is(ref $query, 'DBIx::Custom::Query');
1123
$query = $dbi->delete(table => 'table1', where => {key1 => 1}, query => 1);
1124
is(ref $query, 'DBIx::Custom::Query');
1125
$query = $dbi->select(table => 'table1', where => {key1 => 1, key2 => 2}, query => 1);
1126
is(ref $query, 'DBIx::Custom::Query');
1127

            
1128
test 'DBIx::Custom::Where';
cleanup test
Yuki Kimoto authored on 2011-08-06
1129
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1130
$dbi->execute($CREATE_TABLE->{0});
1131
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1132
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1133
$where = $dbi->where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
1134
is("$where", "where ( key1 = :key1 and key2 = :key2 )", 'no param');
1135

            
1136
$where = $dbi->where
1137
             ->clause(['and', 'key1 = :key1', 'key2 = :key2'])
1138
             ->param({key1 => 1});
1139

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

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

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

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

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

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

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

            
1205
$where = $dbi->where;
1206
is("$where", '');
1207

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            
1389
eval {$dbi->where(ppp => 1) };
1390
like($@, qr/invalid/);
1391

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

            
1403

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

            
1415

            
1416
test 'dbi_option default';
1417
$dbi = DBIx::Custom->new;
1418
is_deeply($dbi->dbi_option, {});
1419

            
1420
test 'register_tag_processor';
cleanup test
Yuki Kimoto authored on 2011-08-06
1421
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1422
$dbi->register_tag_processor(
1423
    a => sub { 1 }
1424
);
1425
is($dbi->query_builder->tag_processors->{a}->(), 1);
1426

            
1427
test 'register_tag';
cleanup test
Yuki Kimoto authored on 2011-08-06
1428
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1429
$dbi->register_tag(
1430
    b => sub { 2 }
1431
);
1432
is($dbi->query_builder->tags->{b}->(), 2);
1433

            
1434
test 'table not specify exception';
cleanup test
Yuki Kimoto authored on 2011-08-06
1435
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1436
eval {$dbi->insert};
1437
like($@, qr/table/);
1438
eval {$dbi->update};
1439
like($@, qr/table/);
1440
eval {$dbi->delete};
1441
like($@, qr/table/);
1442
eval {$dbi->select};
1443
like($@, qr/table/);
1444

            
1445

            
1446
test 'more tests';
cleanup test
Yuki Kimoto authored on 2011-08-06
1447
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1448
eval{$dbi->apply_filter('table', 'column', [])};
1449
like($@, qr/apply_filter/);
1450

            
1451
eval{$dbi->apply_filter('table', 'column', {outer => 2})};
1452
like($@, qr/apply_filter/);
1453

            
1454
$dbi->apply_filter(
1455

            
1456
);
cleanup test
Yuki Kimoto authored on 2011-08-06
1457
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1458
$dbi->execute($CREATE_TABLE->{0});
1459
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1460
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1461
$dbi->apply_filter('table1', 'key2', 
1462
                   {in => sub { $_[0] * 3 }, out => sub { $_[0] * 2 }});
1463
$rows = $dbi->select(table => 'table1', where => {key2 => 1})->all;
1464
is_deeply($rows, [{key1 => 1, key2 => 6}]);
1465

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1466
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1467
$dbi->execute($CREATE_TABLE->{0});
1468
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1469
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1470
$dbi->apply_filter('table1', 'key2', {});
1471
$rows = $dbi->select(table => 'table1', where => {key2 => 2})->all;
1472
is_deeply($rows, [{key1 => 1, key2 => 2}]);
1473

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1474
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1475
eval {$dbi->apply_filter('table1', 'key2', {out => 'no'})};
1476
like($@, qr/not registered/);
1477
eval {$dbi->apply_filter('table1', 'key2', {in => 'no'})};
1478
like($@, qr/not registered/);
1479
$dbi->method({one => sub { 1 }});
1480
is($dbi->one, 1);
1481

            
1482
eval{DBIx::Custom->connect()};
1483
like($@, qr/_connect/);
1484

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1485
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1486
$dbi->execute($CREATE_TABLE->{0});
1487
$dbi->register_filter(twice => sub { $_[0] * 2 });
1488
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
1489
             filter => {key1 => 'twice'});
1490
$row = $dbi->select(table => 'table1')->one;
1491
is_deeply($row, {key1 => 2, key2 => 2});
1492
eval {$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2},
1493
             filter => {key1 => 'no'}) };
1494
like($@, qr//);
1495

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1512
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1513
$dbi->execute($CREATE_TABLE->{0});
1514
$dbi->register_filter(one => sub { 1 });
1515
$result = $dbi->select(table => 'table1');
1516
eval {$result->filter(key1 => 'no')};
1517
like($@, qr/not registered/);
1518
eval {$result->end_filter(key1 => 'no')};
1519
like($@, qr/not registered/);
1520
$result->default_filter(undef);
1521
ok(!defined $result->default_filter);
1522
$result->default_filter('one');
1523
is($result->default_filter->(), 1);
1524

            
1525
test 'dbi_option';
1526
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:',
1527
                             dbi_option => {PrintError => 1});
1528
ok($dbi->dbh->{PrintError});
1529
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:',
1530
                             dbi_options => {PrintError => 1});
1531
ok($dbi->dbh->{PrintError});
1532

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

            
1539
test 'filter __ expression';
cleanup test
Yuki Kimoto authored on 2011-08-06
1540
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1541
$dbi->execute('create table company (id, name, location_id)');
1542
$dbi->execute('create table location (id, name)');
1543
$dbi->apply_filter('location',
1544
  name => {in => sub { uc $_[0] } }
1545
);
1546

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

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

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

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

            
1568
test 'Model class';
1569
use MyDBI1;
cleanup test
Yuki Kimoto authored on 2011-08-06
1570
$dbi = MyDBI1->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1571
$dbi->execute("create table book (title, author)");
1572
$model = $dbi->model('book');
1573
$model->insert({title => 'a', author => 'b'});
1574
is_deeply($model->list->all, [{title => 'a', author => 'b'}], 'basic');
1575
$dbi->execute("create table company (name)");
1576
$model = $dbi->model('company');
1577
$model->insert({name => 'a'});
1578
is_deeply($model->list->all, [{name => 'a'}], 'basic');
1579
is($dbi->models->{'book'}, $dbi->model('book'));
1580
is($dbi->models->{'company'}, $dbi->model('company'));
1581

            
1582
{
1583
    package MyDBI4;
1584

            
1585
    use strict;
1586
    use warnings;
1587

            
1588
    use base 'DBIx::Custom';
1589

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

            
1601
    package MyModel2::Base1;
1602

            
1603
    use strict;
1604
    use warnings;
1605

            
1606
    use base 'DBIx::Custom::Model';
1607

            
1608
    package MyModel2::book;
1609

            
1610
    use strict;
1611
    use warnings;
1612

            
1613
    use base 'MyModel2::Base1';
1614

            
1615
    sub insert {
1616
        my ($self, $param) = @_;
1617
        
1618
        return $self->SUPER::insert(param => $param);
1619
    }
1620

            
1621
    sub list { shift->select; }
1622

            
1623
    package MyModel2::Company;
1624

            
1625
    use strict;
1626
    use warnings;
1627

            
1628
    use base 'MyModel2::Base1';
1629

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

            
1636
    sub list { shift->select; }
1637
}
cleanup test
Yuki Kimoto authored on 2011-08-06
1638
$dbi = MyDBI4->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1639
$dbi->execute("create table book (title, author)");
1640
$model = $dbi->model('book');
1641
$model->insert({title => 'a', author => 'b'});
1642
is_deeply($model->list->all, [{title => 'a', author => 'b'}], 'basic');
1643
$dbi->execute("create table company (name)");
1644
$model = $dbi->model('company');
1645
$model->insert({name => 'a'});
1646
is_deeply($model->list->all, [{name => 'a'}], 'basic');
1647

            
1648
{
1649
     package MyDBI5;
1650

            
1651
    use strict;
1652
    use warnings;
1653

            
1654
    use base 'DBIx::Custom';
1655

            
1656
    sub connect {
1657
        my $self = shift->SUPER::connect(@_);
1658
        
1659
        $self->include_model('MyModel4');
1660
    }
1661
}
cleanup test
Yuki Kimoto authored on 2011-08-06
1662
$dbi = MyDBI5->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1663
$dbi->execute("create table company (name)");
1664
$dbi->execute("create table table1 (key1)");
1665
$model = $dbi->model('company');
1666
$model->insert({name => 'a'});
1667
is_deeply($model->list->all, [{name => 'a'}], 'include all model');
1668
$dbi->insert(table => 'table1', param => {key1 => 1});
1669
$model = $dbi->model('book');
1670
is_deeply($model->list->all, [{key1 => 1}], 'include all model');
1671

            
1672
test 'primary_key';
1673
use MyDBI1;
cleanup test
Yuki Kimoto authored on 2011-08-06
1674
$dbi = MyDBI1->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1675
$model = $dbi->model('book');
1676
$model->primary_key(['id', 'number']);
1677
is_deeply($model->primary_key, ['id', 'number']);
1678

            
1679
test 'columns';
1680
use MyDBI1;
cleanup test
Yuki Kimoto authored on 2011-08-06
1681
$dbi = MyDBI1->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1682
$model = $dbi->model('book');
1683
$model->columns(['id', 'number']);
1684
is_deeply($model->columns, ['id', 'number']);
1685

            
1686
test 'setup_model';
1687
use MyDBI1;
cleanup test
Yuki Kimoto authored on 2011-08-06
1688
$dbi = MyDBI1->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1689
$dbi->execute('create table book (id)');
1690
$dbi->execute('create table company (id, name);');
1691
$dbi->execute('create table test (id, name, primary key (id, name));');
1692
$dbi->setup_model;
1693
is_deeply($dbi->model('book')->columns, ['id']);
1694
is_deeply($dbi->model('company')->columns, ['id', 'name']);
1695

            
1696
test 'delete_at';
cleanup test
Yuki Kimoto authored on 2011-08-06
1697
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1698
$dbi->execute($CREATE_TABLE->{1});
1699
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1700
$dbi->delete_at(
1701
    table => 'table1',
1702
    primary_key => ['key1', 'key2'],
1703
    where => [1, 2],
1704
);
1705
is_deeply($dbi->select(table => 'table1')->all, []);
1706

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

            
1715
test 'insert_at';
cleanup test
Yuki Kimoto authored on 2011-08-06
1716
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1717
$dbi->execute($CREATE_TABLE->{1});
1718
$dbi->insert_at(
1719
    primary_key => ['key1', 'key2'], 
1720
    table => 'table1',
1721
    where => [1, 2],
1722
    param => {key3 => 3}
1723
);
1724
is($dbi->select(table => 'table1')->one->{key1}, 1);
1725
is($dbi->select(table => 'table1')->one->{key2}, 2);
1726
is($dbi->select(table => 'table1')->one->{key3}, 3);
1727

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1751
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1752
$dbi->execute($CREATE_TABLE->{1});
1753
$dbi->insert_at(
1754
    {key3 => 3},
1755
    primary_key => ['key1', 'key2'], 
1756
    table => 'table1',
1757
    where => [1, 2],
1758
);
1759
is($dbi->select(table => 'table1')->one->{key1}, 1);
1760
is($dbi->select(table => 'table1')->one->{key2}, 2);
1761
is($dbi->select(table => 'table1')->one->{key3}, 3);
1762

            
1763
test 'update_at';
cleanup test
Yuki Kimoto authored on 2011-08-06
1764
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1765
$dbi->execute($CREATE_TABLE->{1});
1766
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1767
$dbi->update_at(
1768
    table => 'table1',
1769
    primary_key => ['key1', 'key2'],
1770
    where => [1, 2],
1771
    param => {key3 => 4}
1772
);
1773
is($dbi->select(table => 'table1')->one->{key1}, 1);
1774
is($dbi->select(table => 'table1')->one->{key2}, 2);
1775
is($dbi->select(table => 'table1')->one->{key3}, 4);
1776

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1789
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1790
$dbi->execute($CREATE_TABLE->{1});
1791
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1792
$dbi->update_at(
1793
    {key3 => 4},
1794
    table => 'table1',
1795
    primary_key => ['key1', 'key2'],
1796
    where => [1, 2]
1797
);
1798
is($dbi->select(table => 'table1')->one->{key1}, 1);
1799
is($dbi->select(table => 'table1')->one->{key2}, 2);
1800
is($dbi->select(table => 'table1')->one->{key3}, 4);
1801

            
1802
test 'select_at';
cleanup test
Yuki Kimoto authored on 2011-08-06
1803
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1804
$dbi->execute($CREATE_TABLE->{1});
1805
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1806
$result = $dbi->select_at(
1807
    table => 'table1',
1808
    primary_key => ['key1', 'key2'],
1809
    where => [1, 2]
1810
);
1811
$row = $result->one;
1812
is($row->{key1}, 1);
1813
is($row->{key2}, 2);
1814
is($row->{key3}, 3);
1815

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

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

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

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

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

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

            
1877
test 'columns';
1878
use MyDBI1;
cleanup test
Yuki Kimoto authored on 2011-08-06
1879
$dbi = MyDBI1->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1880
$model = $dbi->model('book');
1881

            
1882

            
1883
test 'model delete_at';
1884
{
1885
    package MyDBI6;
1886
    
1887
    use base 'DBIx::Custom';
1888
    
1889
    sub connect {
1890
        my $self = shift->SUPER::connect(@_);
1891
        
1892
        $self->include_model('MyModel5');
1893
        
1894
        return $self;
1895
    }
1896
}
cleanup test
Yuki Kimoto authored on 2011-08-06
1897
$dbi = MyDBI6->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1898
$dbi->execute($CREATE_TABLE->{1});
1899
$dbi->execute("create table table2 (key1, key2, key3)");
1900
$dbi->execute("create table table3 (key1, key2, key3)");
1901
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1902
$dbi->model('table1')->delete_at(where => [1, 2]);
1903
is_deeply($dbi->select(table => 'table1')->all, []);
1904
$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
1905
$dbi->model('table1_1')->delete_at(where => [1, 2]);
1906
is_deeply($dbi->select(table => 'table1')->all, []);
1907
$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
1908
$dbi->model('table1_3')->delete_at(where => [1, 2]);
1909
is_deeply($dbi->select(table => 'table1')->all, []);
1910

            
1911
test 'model insert_at';
cleanup test
Yuki Kimoto authored on 2011-08-06
1912
$dbi = MyDBI6->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1913
$dbi->execute($CREATE_TABLE->{1});
1914
$dbi->model('table1')->insert_at(
1915
    where => [1, 2],
1916
    param => {key3 => 3}
1917
);
1918
$result = $dbi->model('table1')->select;
1919
$row = $result->one;
1920
is($row->{key1}, 1);
1921
is($row->{key2}, 2);
1922
is($row->{key3}, 3);
1923

            
1924
test 'model update_at';
cleanup test
Yuki Kimoto authored on 2011-08-06
1925
$dbi = MyDBI6->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1926
$dbi->execute($CREATE_TABLE->{1});
1927
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1928
$dbi->model('table1')->update_at(
1929
    where => [1, 2],
1930
    param => {key3 => 4}
1931
);
1932
$result = $dbi->model('table1')->select;
1933
$row = $result->one;
1934
is($row->{key1}, 1);
1935
is($row->{key2}, 2);
1936
is($row->{key3}, 4);
1937

            
1938
test 'model select_at';
cleanup test
Yuki Kimoto authored on 2011-08-06
1939
$dbi = MyDBI6->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1940
$dbi->execute($CREATE_TABLE->{1});
1941
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
1942
$result = $dbi->model('table1')->select_at(where => [1, 2]);
1943
$row = $result->one;
1944
is($row->{key1}, 1);
1945
is($row->{key2}, 2);
1946
is($row->{key3}, 3);
1947

            
1948

            
1949
test 'mycolumn and column';
1950
{
1951
    package MyDBI7;
1952
    
1953
    use base 'DBIx::Custom';
1954
    
1955
    sub connect {
1956
        my $self = shift->SUPER::connect(@_);
1957
        
1958
        $self->include_model('MyModel6');
1959
        
1960
        
1961
        return $self;
1962
    }
1963
}
cleanup test
Yuki Kimoto authored on 2011-08-06
1964
$dbi = MyDBI7->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1965
$dbi->execute($CREATE_TABLE->{0});
1966
$dbi->execute($CREATE_TABLE->{2});
1967
$dbi->separator('__');
1968
$dbi->setup_model;
1969
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1970
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
1971
$model = $dbi->model('table1');
1972
$result = $model->select(
1973
    column => [$model->mycolumn, $model->column('table2')],
1974
    where => {'table1.key1' => 1}
1975
);
1976
is_deeply($result->one,
1977
          {key1 => 1, key2 => 2, 'table2__key1' => 1, 'table2__key3' => 3});
1978

            
1979
test 'update_param';
cleanup test
Yuki Kimoto authored on 2011-08-06
1980
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
1981
$dbi->execute($CREATE_TABLE->{1});
1982
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
1983
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
1984

            
1985
$param = {key2 => 11};
1986
$update_param = $dbi->update_param($param);
1987
$sql = <<"EOS";
1988
update table1 $update_param
1989
where key1 = 1
1990
EOS
1991
$dbi->execute($sql, param => $param);
test cleanup
Yuki Kimoto authored on 2011-08-06
1992
$result = $dbi->execute('select * from table1;', table => 'table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
1993
$rows   = $result->all;
1994
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
1995
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
1996
                  "basic");
1997

            
1998

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1999
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2000
$dbi->execute($CREATE_TABLE->{1});
2001
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2002
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2003

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2017
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2018
$dbi->execute($CREATE_TABLE->{1});
2019
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2020
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2021

            
2022
$param = {key2 => 11, key3 => 33};
2023
$update_param = $dbi->update_param($param, {no_set => 1});
2024
$sql = <<"EOS";
2025
update table1 set $update_param
2026
where key1 = 1
2027
EOS
2028
$dbi->execute($sql, param => $param);
test cleanup
Yuki Kimoto authored on 2011-08-06
2029
$result = $dbi->execute('select * from table1;', table => 'table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
2030
$rows   = $result->all;
2031
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 33, key4 => 4, key5 => 5},
2032
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
2033
                  "update param no_set");
2034

            
2035
            
2036
eval { $dbi->update_param({";" => 1}) };
2037
like($@, qr/not safety/);
2038

            
2039

            
2040
test 'update_param';
cleanup test
Yuki Kimoto authored on 2011-08-06
2041
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2042
$dbi->execute($CREATE_TABLE->{1});
2043
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2044
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2045

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

            
2059

            
2060
test 'insert_param';
cleanup test
Yuki Kimoto authored on 2011-08-06
2061
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2062
$dbi->execute($CREATE_TABLE->{1});
2063
$param = {key1 => 1, key2 => 2};
2064
$insert_param = $dbi->insert_param($param);
2065
$sql = <<"EOS";
2066
insert into table1 $insert_param
2067
EOS
2068
$dbi->execute($sql, param => $param, table => 'table1');
2069
is($dbi->select(table => 'table1')->one->{key1}, 1);
2070
is($dbi->select(table => 'table1')->one->{key2}, 2);
2071

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2072
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2073
$dbi->quote('"');
2074
$dbi->execute($CREATE_TABLE->{1});
2075
$param = {key1 => 1, key2 => 2};
2076
$insert_param = $dbi->insert_param($param);
2077
$sql = <<"EOS";
2078
insert into table1 $insert_param
2079
EOS
2080
$dbi->execute($sql, param => $param, table => 'table1');
2081
is($dbi->select(table => 'table1')->one->{key1}, 1);
2082
is($dbi->select(table => 'table1')->one->{key2}, 2);
2083

            
2084
eval { $dbi->insert_param({";" => 1}) };
2085
like($@, qr/not safety/);
2086

            
2087

            
2088
test 'join';
cleanup test
Yuki Kimoto authored on 2011-08-06
2089
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2090
$dbi->execute($CREATE_TABLE->{0});
2091
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2092
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
2093
$dbi->execute($CREATE_TABLE->{2});
2094
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2095
$dbi->execute($CREATE_TABLE->{4});
2096
$dbi->insert(table => 'table3', param => {key3 => 5, key4 => 4});
2097
$rows = $dbi->select(
2098
    table => 'table1',
2099
    column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',
2100
    where   => {'table1.key2' => 2},
2101
    join  => ['left outer join table2 on table1.key1 = table2.key1']
2102
)->all;
2103
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}]);
2104

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

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

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2148
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2149
$dbi->quote('"');
2150
$dbi->execute($CREATE_TABLE->{0});
2151
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2152
$dbi->execute($CREATE_TABLE->{2});
2153
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2154
$rows = $dbi->select(
2155
    table => 'table1',
2156
    column => '"table1"."key1" as "table1_key1", "table2"."key1" as "table2_key1", "key2", "key3"',
2157
    where   => {'table1.key2' => 2},
2158
    join  => ['left outer join "table2" on "table1"."key1" = "table2"."key1"'],
2159
)->all;
2160
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}],
2161
          'quote');
2162

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2177
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2178
$dbi->execute($CREATE_TABLE->{0});
2179
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2180
$sql = <<"EOS";
2181
left outer join (
2182
  select * from table1 as t1
2183
  where t1.key2 = (
2184
    select max(t2.key2) from table1 as t2
2185
    where t1.key1 = t2.key1
2186
  )
2187
) as latest_table1 on table1.key1 = latest_table1.key1
2188
EOS
2189
$join = [$sql];
2190
$rows = $dbi->select(
2191
    table => 'table1',
2192
    column => 'latest_table1.key1 as latest_table1__key1',
2193
    join  => $join
2194
)->all;
2195
is_deeply($rows, [{latest_table1__key1 => 1}]);
2196

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2197
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2198
$dbi->execute($CREATE_TABLE->{0});
2199
$dbi->execute($CREATE_TABLE->{2});
2200
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2201
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
2202
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2203
$result = $dbi->select(
2204
    table => 'table1',
2205
    join => [
2206
        "left outer join table2 on table2.key2 = '4' and table1.key1 = table2.key1"
2207
    ]
2208
);
2209
is_deeply($result->all, [{key1 => 1, key2 => 2}]);
2210
$result = $dbi->select(
2211
    table => 'table1',
2212
    column => [{table2 => ['key3']}],
2213
    join => [
2214
        "left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1"
2215
    ]
2216
);
2217
is_deeply($result->all, [{'table2.key3' => 4}]);
2218
$result = $dbi->select(
2219
    table => 'table1',
2220
    column => [{table2 => ['key3']}],
2221
    join => [
2222
        "left outer join table2 on table1.key1 = table2.key1 and table2.key3 = '4'"
2223
    ]
2224
);
2225
is_deeply($result->all, [{'table2.key3' => 4}]);
2226

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2227
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2228
$dbi->execute($CREATE_TABLE->{0});
2229
$dbi->execute($CREATE_TABLE->{2});
2230
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2231
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
2232
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
2233
$result = $dbi->select(
2234
    table => 'table1',
2235
    column => [{table2 => ['key3']}],
2236
    join => [
2237
        {
2238
            clause => "left outer join table2 on table2.key3 = '4' and table1.key1 = table2.key1",
2239
            table => ['table1', 'table2']
2240
        }
2241
    ]
2242
);
2243
is_deeply($result->all, [{'table2.key3' => 4}]);
2244

            
2245
test 'mycolumn';
cleanup test
Yuki Kimoto authored on 2011-08-06
2246
$dbi = MyDBI8->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2247
$dbi->execute($CREATE_TABLE->{0});
2248
$dbi->execute($CREATE_TABLE->{2});
2249
$dbi->setup_model;
2250
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2251
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2252
$model = $dbi->model('table1');
2253
$result = $model->select_at(
2254
    column => [
2255
        $model->mycolumn,
2256
        $model->column('table2')
2257
    ]
2258
);
2259
is_deeply($result->one,
2260
          {key1 => 1, key2 => 2, 'table2.key1' => 1, 'table2.key3' => 3});
2261

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

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

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

            
2297
test 'dbi method from model';
2298
{
2299
    package MyDBI9;
2300
    
2301
    use base 'DBIx::Custom';
2302
    
2303
    sub connect {
2304
        my $self = shift->SUPER::connect(@_);
2305
        
2306
        $self->include_model('MyModel8')->setup_model;
2307
        
2308
        return $self;
2309
    }
2310
}
cleanup test
Yuki Kimoto authored on 2011-08-06
2311
$dbi = MyDBI9->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2312
$dbi->execute($CREATE_TABLE->{0});
2313
$model = $dbi->model('table1');
2314
eval{$model->execute('select * from table1')};
2315
ok(!$@);
2316

            
2317
test 'column table option';
cleanup test
Yuki Kimoto authored on 2011-08-06
2318
$dbi = MyDBI9->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2319
$dbi->execute($CREATE_TABLE->{0});
2320
$dbi->execute($CREATE_TABLE->{2});
2321
$dbi->setup_model;
2322
$dbi->execute('insert into table1 (key1, key2) values (1, 2);');
2323
$dbi->execute('insert into table2 (key1, key3) values (1, 4);');
2324
$model = $dbi->model('table1');
2325
$result = $model->select(
2326
    column => [
2327
        $model->column('table2', {alias => 'table2_alias'})
2328
    ],
2329
    where => {'table2_alias.key3' => 4}
2330
);
2331
is_deeply($result->one, 
2332
          {'table2_alias.key1' => 1, 'table2_alias.key3' => 4});
2333

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

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

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

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

            
2379

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

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

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

            
2423
test 'create_model';
cleanup test
Yuki Kimoto authored on 2011-08-06
2424
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2425
$dbi->execute($CREATE_TABLE->{0});
2426
$dbi->execute($CREATE_TABLE->{2});
2427

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

            
2456
test 'model method';
2457
test 'create_model';
cleanup test
Yuki Kimoto authored on 2011-08-06
2458
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2459
$dbi->execute($CREATE_TABLE->{2});
2460
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2461
$model = $dbi->create_model(
2462
    table => 'table2'
2463
);
2464
$model->method(foo => sub { shift->select(@_) });
2465
is_deeply($model->foo->one, {key1 => 1, key3 => 3});
2466

            
2467
test 'merge_param';
2468
{
2469
    my $dbi = DBIx::Custom->new;
2470
    my $param1 = {key1 => 1, key2 => 2, key3 => 3};
2471
    my $param2 = {key1 => 1, key2 => 2};
2472
    my $param3 = {key1 => 1};
2473
    my $param = $dbi->merge_param($param1, $param2, $param3);
2474
    is_deeply($param, {key1 => [1, 1, 1], key2 => [2, 2], key3 => 3});
2475
}
2476

            
2477
{
2478
    my $dbi = DBIx::Custom->new;
2479
    my $param1 = {key1 => [1, 2], key2 => 1, key3 => [1, 2]};
2480
    my $param2 = {key1 => [3, 4], key2 => [2, 3], key3 => 3};
2481
    my $param = $dbi->merge_param($param1, $param2);
2482
    is_deeply($param, {key1 => [1, 2, 3, 4], key2 => [1, 2, 3], key3 => [1, 2, 3]});
2483
}
2484

            
2485
test 'select() param option';
cleanup test
Yuki Kimoto authored on 2011-08-06
2486
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2487
$dbi->execute($CREATE_TABLE->{0});
2488
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2489
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2490
$dbi->execute($CREATE_TABLE->{2});
2491
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 4});
2492
$dbi->insert(table => 'table2', param => {key1 => 2, key3 => 5});
2493
$rows = $dbi->select(
2494
    table => 'table1',
2495
    column => 'table1.key1 as table1_key1, key2, key3',
2496
    where   => {'table1.key2' => 3},
2497
    join  => ['inner join (select * from table2 where {= table2.key3})' . 
2498
              ' as table2 on table1.key1 = table2.key1'],
2499
    param => {'table2.key3' => 5}
2500
)->all;
2501
is_deeply($rows, [{table1_key1 => 2, key2 => 3, key3 => 5}]);
2502

            
2503

            
2504
test 'select() wrap option';
cleanup test
Yuki Kimoto authored on 2011-08-06
2505
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2506
$dbi->execute($CREATE_TABLE->{0});
2507
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2508
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2509
$rows = $dbi->select(
2510
    table => 'table1',
2511
    column => 'key1',
2512
    wrap => ['select * from (', ') as t where key1 = 1']
2513
)->all;
2514
is_deeply($rows, [{key1 => 1}]);
2515

            
2516
eval {
2517
$dbi->select(
2518
    table => 'table1',
2519
    column => 'key1',
2520
    wrap => 'select * from ('
2521
)
2522
};
2523
like($@, qr/array/);
2524

            
2525
test 'select() string where';
cleanup test
Yuki Kimoto authored on 2011-08-06
2526
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2527
$dbi->execute($CREATE_TABLE->{0});
2528
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2529
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2530
$rows = $dbi->select(
2531
    table => 'table1',
2532
    where => 'key1 = :key1 and key2 = :key2',
2533
    where_param => {key1 => 1, key2 => 2}
2534
)->all;
2535
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2536

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2537
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2538
$dbi->execute($CREATE_TABLE->{0});
2539
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2540
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2541
$rows = $dbi->select(
2542
    table => 'table1',
2543
    where => [
2544
        'key1 = :key1 and key2 = :key2',
2545
        {key1 => 1, key2 => 2}
2546
    ]
2547
)->all;
2548
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2549

            
2550
test 'delete() string where';
cleanup test
Yuki Kimoto authored on 2011-08-06
2551
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2552
$dbi->execute($CREATE_TABLE->{0});
2553
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2554
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2555
$dbi->delete(
2556
    table => 'table1',
2557
    where => 'key1 = :key1 and key2 = :key2',
2558
    where_param => {key1 => 1, key2 => 2}
2559
);
2560
$rows = $dbi->select(table => 'table1')->all;
2561
is_deeply($rows, [{key1 => 2, key2 => 3}]);
2562

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2563
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2564
$dbi->execute($CREATE_TABLE->{0});
2565
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2566
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2567
$dbi->delete(
2568
    table => 'table1',
2569
    where => [
2570
        'key1 = :key1 and key2 = :key2',
2571
         {key1 => 1, key2 => 2}
2572
    ]
2573
);
2574
$rows = $dbi->select(table => 'table1')->all;
2575
is_deeply($rows, [{key1 => 2, key2 => 3}]);
2576

            
2577

            
2578
test 'update() string where';
cleanup test
Yuki Kimoto authored on 2011-08-06
2579
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2580
$dbi->execute($CREATE_TABLE->{0});
2581
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2582
$dbi->update(
2583
    table => 'table1',
2584
    param => {key1 => 5},
2585
    where => 'key1 = :key1 and key2 = :key2',
2586
    where_param => {key1 => 1, key2 => 2}
2587
);
2588
$rows = $dbi->select(table => 'table1')->all;
2589
is_deeply($rows, [{key1 => 5, key2 => 2}]);
2590

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2591
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2592
$dbi->execute($CREATE_TABLE->{0});
2593
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2594
$dbi->update(
2595
    table => 'table1',
2596
    param => {key1 => 5},
2597
    where => [
2598
        'key1 = :key1 and key2 = :key2',
2599
        {key1 => 1, key2 => 2}
2600
    ]
2601
);
2602
$rows = $dbi->select(table => 'table1')->all;
2603
is_deeply($rows, [{key1 => 5, key2 => 2}]);
2604

            
2605
test 'insert id and primary_key option';
cleanup test
Yuki Kimoto authored on 2011-08-06
2606
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2607
$dbi->execute($CREATE_TABLE->{1});
2608
$dbi->insert(
2609
    primary_key => ['key1', 'key2'], 
2610
    table => 'table1',
2611
    id => [1, 2],
2612
    param => {key3 => 3}
2613
);
2614
is($dbi->select(table => 'table1')->one->{key1}, 1);
2615
is($dbi->select(table => 'table1')->one->{key2}, 2);
2616
is($dbi->select(table => 'table1')->one->{key3}, 3);
2617

            
2618
$dbi->delete_all(table => 'table1');
2619
$dbi->insert(
2620
    primary_key => 'key1', 
2621
    table => 'table1',
2622
    id => 0,
2623
    param => {key2 => 2, key3 => 3}
2624
);
2625

            
2626
is($dbi->select(table => 'table1')->one->{key1}, 0);
2627
is($dbi->select(table => 'table1')->one->{key2}, 2);
2628
is($dbi->select(table => 'table1')->one->{key3}, 3);
2629

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2630
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2631
$dbi->execute($CREATE_TABLE->{1});
2632
$dbi->insert(
2633
    {key3 => 3},
2634
    primary_key => ['key1', 'key2'], 
2635
    table => 'table1',
2636
    id => [1, 2],
2637
);
2638
is($dbi->select(table => 'table1')->one->{key1}, 1);
2639
is($dbi->select(table => 'table1')->one->{key2}, 2);
2640
is($dbi->select(table => 'table1')->one->{key3}, 3);
2641

            
2642

            
2643
test 'model insert id and primary_key option';
cleanup test
Yuki Kimoto authored on 2011-08-06
2644
$dbi = MyDBI6->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2645
$dbi->execute($CREATE_TABLE->{1});
2646
$dbi->model('table1')->insert(
2647
    id => [1, 2],
2648
    param => {key3 => 3}
2649
);
2650
$result = $dbi->model('table1')->select;
2651
$row = $result->one;
2652
is($row->{key1}, 1);
2653
is($row->{key2}, 2);
2654
is($row->{key3}, 3);
2655

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2656
$dbi = MyDBI6->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2657
$dbi->execute($CREATE_TABLE->{1});
2658
$dbi->model('table1')->insert(
2659
    {key3 => 3},
2660
    id => [1, 2]
2661
);
2662
$result = $dbi->model('table1')->select;
2663
$row = $result->one;
2664
is($row->{key1}, 1);
2665
is($row->{key2}, 2);
2666
is($row->{key3}, 3);
2667

            
2668
test 'update and id option';
cleanup test
Yuki Kimoto authored on 2011-08-06
2669
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2670
$dbi->execute($CREATE_TABLE->{1});
2671
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2672
$dbi->update(
2673
    table => 'table1',
2674
    primary_key => ['key1', 'key2'],
2675
    id => [1, 2],
2676
    param => {key3 => 4}
2677
);
2678
is($dbi->select(table => 'table1')->one->{key1}, 1);
2679
is($dbi->select(table => 'table1')->one->{key2}, 2);
2680
is($dbi->select(table => 'table1')->one->{key3}, 4);
2681

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2694
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2695
$dbi->execute($CREATE_TABLE->{1});
2696
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2697
$dbi->update(
2698
    {key3 => 4},
2699
    table => 'table1',
2700
    primary_key => ['key1', 'key2'],
2701
    id => [1, 2]
2702
);
2703
is($dbi->select(table => 'table1')->one->{key1}, 1);
2704
is($dbi->select(table => 'table1')->one->{key2}, 2);
2705
is($dbi->select(table => 'table1')->one->{key3}, 4);
2706

            
2707

            
2708
test 'model update and id option';
cleanup test
Yuki Kimoto authored on 2011-08-06
2709
$dbi = MyDBI6->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2710
$dbi->execute($CREATE_TABLE->{1});
2711
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2712
$dbi->model('table1')->update(
2713
    id => [1, 2],
2714
    param => {key3 => 4}
2715
);
2716
$result = $dbi->model('table1')->select;
2717
$row = $result->one;
2718
is($row->{key1}, 1);
2719
is($row->{key2}, 2);
2720
is($row->{key3}, 4);
2721

            
2722

            
2723
test 'delete and id option';
cleanup test
Yuki Kimoto authored on 2011-08-06
2724
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2725
$dbi->execute($CREATE_TABLE->{1});
2726
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2727
$dbi->delete(
2728
    table => 'table1',
2729
    primary_key => ['key1', 'key2'],
2730
    id => [1, 2],
2731
);
2732
is_deeply($dbi->select(table => 'table1')->all, []);
2733

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

            
2742

            
2743
test 'model delete and id option';
cleanup test
Yuki Kimoto authored on 2011-08-06
2744
$dbi = MyDBI6->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2745
$dbi->execute($CREATE_TABLE->{1});
2746
$dbi->execute("create table table2 (key1, key2, key3)");
2747
$dbi->execute("create table table3 (key1, key2, key3)");
2748
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2749
$dbi->model('table1')->delete(id => [1, 2]);
2750
is_deeply($dbi->select(table => 'table1')->all, []);
2751
$dbi->insert(table => 'table2', param => {key1 => 1, key2 => 2, key3 => 3});
2752
$dbi->model('table1_1')->delete(id => [1, 2]);
2753
is_deeply($dbi->select(table => 'table1')->all, []);
2754
$dbi->insert(table => 'table3', param => {key1 => 1, key2 => 2, key3 => 3});
2755
$dbi->model('table1_3')->delete(id => [1, 2]);
2756
is_deeply($dbi->select(table => 'table1')->all, []);
2757

            
2758

            
2759
test 'select and id option';
cleanup test
Yuki Kimoto authored on 2011-08-06
2760
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2761
$dbi->execute($CREATE_TABLE->{1});
2762
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2763
$result = $dbi->select(
2764
    table => 'table1',
2765
    primary_key => ['key1', 'key2'],
2766
    id => [1, 2]
2767
);
2768
$row = $result->one;
2769
is($row->{key1}, 1);
2770
is($row->{key2}, 2);
2771
is($row->{key3}, 3);
2772

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

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

            
2797

            
2798
test 'model select_at';
cleanup test
Yuki Kimoto authored on 2011-08-06
2799
$dbi = MyDBI6->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2800
$dbi->execute($CREATE_TABLE->{1});
2801
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3});
2802
$result = $dbi->model('table1')->select(id => [1, 2]);
2803
$row = $result->one;
2804
is($row->{key1}, 1);
2805
is($row->{key2}, 2);
2806
is($row->{key3}, 3);
2807

            
2808
test 'column separator is default .';
cleanup test
Yuki Kimoto authored on 2011-08-06
2809
$dbi = MyDBI7->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
2810
$dbi->execute($CREATE_TABLE->{0});
2811
$dbi->execute($CREATE_TABLE->{2});
2812
$dbi->setup_model;
2813
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2814
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 3});
2815
$model = $dbi->model('table1');
2816
$result = $model->select(
2817
    column => [$model->column('table2')],
2818
    where => {'table1.key1' => 1}
2819
);
2820
is_deeply($result->one,
2821
          {'table2.key1' => 1, 'table2.key3' => 3});
2822

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

            
2830

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

            
2843
$result = $dbi->select(table => 'table1');
2844
is($result->one->{key1}, 'A');
2845

            
2846

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

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

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

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

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

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

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

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

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

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

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

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

            
3034
eval{$dbi->type_rule(
3035
    into1 => {
3036
        date => 'pp'
3037
    }
3038
)};
3039
like($@, qr/not registered/);
3040

            
3041
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3042
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3043
eval {
3044
    $dbi->type_rule(
3045
        from1 => {
3046
            Date => sub { $_[0] * 2 },
3047
        }
3048
    );
3049
};
3050
like($@, qr/lower/);
3051

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

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

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

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

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

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

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

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

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

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

            
3227
test 'separator';
cleanup test
Yuki Kimoto authored on 2011-08-06
3228
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
3229
$dbi->execute($CREATE_TABLE->{0});
3230
$dbi->execute($CREATE_TABLE->{2});
3231

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

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

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

            
3283

            
3284
test 'filter_off';
cleanup test
Yuki Kimoto authored on 2011-08-06
3285
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
3286
$dbi->execute($CREATE_TABLE->{0});
3287
$dbi->execute($CREATE_TABLE->{2});
3288

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

            
3303
test 'available_date_type';
cleanup test
Yuki Kimoto authored on 2011-08-06
3304
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
3305
ok($dbi->can('available_data_type'));
3306

            
3307

            
3308
test 'select prefix option';
cleanup test
Yuki Kimoto authored on 2011-08-06
3309
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
3310
$dbi->execute($CREATE_TABLE->{0});
3311
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3312
$rows = $dbi->select(prefix => 'key1,', column => 'key2', table => 'table1')->all;
3313
is_deeply($rows, [{key1 => 1, key2 => 2}], "table");
3314

            
3315

            
3316
test 'separator';
cleanup test
Yuki Kimoto authored on 2011-08-06
3317
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
3318
is($dbi->separator, '.');
3319
$dbi->separator('-');
3320
is($dbi->separator, '-');
3321
$dbi->separator('__');
3322
is($dbi->separator, '__');
3323
eval { $dbi->separator('?') };
3324
like($@, qr/Separator/);
3325

            
3326

            
3327
test 'map_param';
cleanup test
Yuki Kimoto authored on 2011-08-06
3328
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
cleanup test
Yuki Kimoto authored on 2011-08-06
3329
$param = $dbi->map_param(
3330
    {id => 1, author => 'Ken', price => 1900},
3331
    id => 'book.id',
3332
    author => ['book.author', sub { '%' . $_[0] . '%' }],
3333
    price => ['book.price', {if => sub { $_[0] eq 1900 }}]
3334
);
3335
is_deeply($param, {'book.id' => 1, 'book.author' => '%Ken%',
3336
  'book.price' => 1900});
3337

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

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

            
3356
$param = $dbi->map_param(
3357
    {id => undef, author => undef, price => undef},
3358
    id => 'book.id',
3359
    price => ['book.price', {if => 'exists'}]
3360
);
3361
is_deeply($param, {'book.price' => undef});
3362

            
3363
$param = $dbi->map_param(
3364
    {price => 'a'},
3365
    id => ['book.id', {if => 'exists'}],
3366
    price => ['book.price', sub { '%' . $_[0] }, {if => 'exists'}]
3367
);
3368
is_deeply($param, {'book.price' => '%a'});
3369

            
3370

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

            
3384

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

            
3403
    $order = $dbi->order;
3404
    $order->prepend(['table1-key1'], [qw/table1-key2 desc/]);
3405
    $result = $dbi->select(table => 'table1',
3406
      column => [[key1 => 'table1-key1'], [key2 => 'table1-key2']],
3407
      append => "$order");
3408
    is_deeply($result->all, [{'table1-key1' => 1, 'table1-key2' => 3},
3409
      {'table1-key1' => 1, 'table1-key2' => 1},
3410
      {'table1-key1' => 2, 'table1-key2' => 4},
3411
      {'table1-key1' => 2, 'table1-key2' => 2}]);
3412
}
3413

            
3414
test 'tag_parse';
3415
$dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3416
$dbi->tag_parse(0);
3417
{
3418
    $dbi->execute("create table table1 (key1, key2)");
3419
    $dbi->insert({key1 => 1, key2 => 1}, table => 'table1');
3420
    eval {$dbi->execute("select * from table1 where {= key1}", {key1 => 1})};
3421
    ok($@);
3422
}
3423

            
3424
test 'last_sql';
3425
{
3426
    my $dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3427
    $dbi->execute("create table table1 (key1, key2)");
3428
    $dbi->execute('select * from table1');
3429
    is($dbi->last_sql, 'select * from table1;');
3430
    
3431
    eval{$dbi->execute("aaa")};
3432
    is($dbi->last_sql, 'aaa;');
3433
    
3434
}
3435

            
3436
test 'DBIx::Custom header';
3437
{
3438
    my $dbi = DBIx::Custom->connect(dsn => 'dbi:SQLite:dbname=:memory:');
3439
    $dbi->execute("create table table1 (key1, key2)");
3440
    my $result = $dbi->execute('select key1 as h1, key2 as h2 from table1');
3441
    
3442
    is_deeply($result->header, [qw/h1 h2/]);
3443
    
3444
}
3445

            
3446
test 'Named placeholder :name(operater) syntax';
cleanup test
Yuki Kimoto authored on 2011-08-06
3447
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
3448
$dbi->execute($CREATE_TABLE->{1});
3449
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3450
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3451

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

            
3457
$source = "select * from table1 where :key1{ = } and :key2{=}";
3458
$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});
3459
$rows = $result->all;
3460
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3461

            
3462
$source = "select * from table1 where :key1{<} and :key2{=}";
3463
$result = $dbi->execute($source, param => {key1 => 5, key2 => 2});
3464
$rows = $result->all;
3465
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3466

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

            
3476
test 'high perfomance way';
cleanup test
Yuki Kimoto authored on 2011-08-06
3477
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
3478
$dbi->execute("create table table1 (ab, bc, ik, hi, ui, pq, dc);");
3479
$rows = [
3480
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3481
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3482
];
3483
{
3484
    my $query;
3485
    foreach my $row (@$rows) {
3486
      $query ||= $dbi->insert($row, table => 'table1', query => 1);
3487
      $dbi->execute($query, $row, filter => {ab => sub { $_[0] * 2 }});
3488
    }
3489
    is_deeply($dbi->select(table => 'table1')->all,
3490
      [
3491
          {ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3492
          {ab => 2, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3493
      ]
3494
    );
3495
}
3496

            
cleanup test
Yuki Kimoto authored on 2011-08-06
3497
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
3498
$dbi->execute("create table table1 (ab, bc, ik, hi, ui, pq, dc);");
3499
$rows = [
3500
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3501
    {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3502
];
3503
{
3504
    my $query;
3505
    my $sth;
3506
    foreach my $row (@$rows) {
3507
      $query ||= $dbi->insert($row, table => 'table1', query => 1);
3508
      $sth ||= $query->sth;
3509
      $sth->execute(map { $row->{$_} } sort keys %$row);
3510
    }
3511
    is_deeply($dbi->select(table => 'table1')->all,
3512
      [
3513
          {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 7},
3514
          {ab => 1, bc => 2, ik => 3, hi => 4, ui => 5, pq => 6, dc => 8},
3515
      ]
3516
    );
3517
}
3518
=cut