DBIx-Custom / t / basic.t /
Newer Older
3513 lines | 115.649kb
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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
22
# Constant
23
my %memory = (dsn => 'dbi:SQLite:dbname=:memory:');
24
my $create_table_default = 'create table table1 (key1 char(255), key2 char(255));';
25

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

            
55
# Prepare table
cleanup test
Yuki Kimoto authored on 2011-08-06
56
$dbi = DBIx::Custom->connect(%memory);
57
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
58
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
59
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
60

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

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

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

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

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

            
87
test 'Insert query return value';
cleanup test
Yuki Kimoto authored on 2011-08-06
88
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
89
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
90
$source = "insert into table1 {insert_param key1 key2}";
91
$query = $dbi->execute($source, {}, query => 1);
92
$ret_val = $dbi->execute($query, param => {key1 => 1, key2 => 2});
93
ok($ret_val);
94

            
95

            
96
test 'Direct query';
cleanup test
Yuki Kimoto authored on 2011-08-06
97
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
98
$dbi->execute($create_table_default);
99
$insert_source = "insert into table1 {insert_param key1 key2}";
100
$dbi->execute($insert_source, param => {key1 => 1, key2 => 2});
test cleanup
Yuki Kimoto authored on 2011-08-06
101
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
102
$rows = $result->all;
103
is_deeply($rows, [{key1 => 1, key2 => 2}]);
104

            
105
test 'Filter basic';
cleanup test
Yuki Kimoto authored on 2011-08-06
106
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
107
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
108
$dbi->register_filter(twice       => sub { $_[0] * 2}, 
109
                    three_times => sub { $_[0] * 3});
110

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

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

            
132
test 'DBIx::Custom::SQLTemplate basic tag';
cleanup test
Yuki Kimoto authored on 2011-08-06
133
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
134
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
135
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
136
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
137

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

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

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

            
156
test 'DIB::Custom::SQLTemplate in tag';
cleanup test
Yuki Kimoto authored on 2011-08-06
157
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
158
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
159
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
160
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
161

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

            
168
test 'DBIx::Custom::SQLTemplate insert tag';
169
$dbi->execute("delete from table1");
cleanup test
Yuki Kimoto authored on 2011-08-06
170
$insert_source = 'insert into table1 {insert_param key1 key2 key3 key4 key5}';
171
$dbi->execute($insert_source, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
cleanup test
Yuki Kimoto authored on 2011-08-06
172

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
183
$update_source = 'update table1 {update_param key1 key2 key3 key4} where {= key5}';
184
$dbi->execute($update_source, param => {key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5});
cleanup test
Yuki Kimoto authored on 2011-08-06
185

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

            
191

            
192
test 'Named placeholder';
cleanup test
Yuki Kimoto authored on 2011-08-06
193
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
194
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
195
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
196
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
197

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

            
203
$source = "select * from table1 where key1 = \n:key1\n 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 = :key1 or key1 = :key1";
209
$result = $dbi->execute($source, param => {key1 => [1, 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 = :table1.key1 and key2 = :table1.key2";
214
$result = $dbi->execute(
215
    $source,
216
    param => {'table1.key1' => 1, 'table1.key2' => 1},
217
    filter => {'table1.key2' => sub { $_[0] * 2 }}
218
);
219
$rows = $result->all;
220
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
221

            
cleanup test
Yuki Kimoto authored on 2011-08-06
222
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
223
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
224
$dbi->insert(table => 'table1', param => {key1 => '2011-10-14 12:19:18', key2 => 2});
225
$source = "select * from table1 where key1 = '2011-10-14 12:19:18' and key2 = :key2";
226
$result = $dbi->execute(
227
    $source,
228
    param => {'key2' => 2},
229
);
230

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

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

            
245

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
250
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
251
eval{$dbi->execute("{p }", {}, query => 1)};
252
ok($@, "create_query invalid SQL template");
253

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
275
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
276
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
277
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, append => '   ');
278
$rows = $dbi->select(table => 'table1')->all;
279
is_deeply($rows, [{key1 => 1, key2 => 2}], 'insert append');
280

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
287
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
288
$dbi->quote('"');
289
$dbi->execute('create table "table" ("select")');
290
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
291
$dbi->insert(table => 'table', param => {select => 1});
292
$result = $dbi->execute('select * from "table"');
293
$rows   = $result->all;
294
is_deeply($rows, [{select => 2}], "reserved word");
295

            
cleanup test
Yuki Kimoto authored on 2011-08-06
296
$dbi = DBIx::Custom->connect(%memory);
297
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
298
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
299
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
test cleanup
Yuki Kimoto authored on 2011-08-06
300
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
301
$rows   = $result->all;
302
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
303

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
312
$dbi = DBIx::Custom->connect(%memory);
313
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
314
$dbi->insert(table => 'table1', param => {key1 => \"'1'", key2 => 2});
315
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
test cleanup
Yuki Kimoto authored on 2011-08-06
316
$result = $dbi->execute('select * from table1;');
cleanup test
Yuki Kimoto authored on 2011-08-06
317
$rows   = $result->all;
318
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");
319

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

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

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

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
369
$dbi = DBIx::Custom->connect(%memory);
370
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
371
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
372
$where = $dbi->where;
373
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
374
$where->param({key1 => 1, key2 => 2});
375
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
376
$result = $dbi->select(table => 'table1');
377
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
378

            
cleanup test
Yuki Kimoto authored on 2011-08-06
379
$dbi = DBIx::Custom->connect(%memory);
380
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
381
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
382
$dbi->update(
383
    table => 'table1',
384
    param => {key1 => 3},
385
    where => [
386
        ['and', 'key1 = :key1', 'key2 = :key2'],
387
        {key1 => 1, key2 => 2}
388
    ]
389
);
390
$result = $dbi->select(table => 'table1');
391
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
392

            
cleanup test
Yuki Kimoto authored on 2011-08-06
393
$dbi = DBIx::Custom->connect(%memory);
394
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
395
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
396
$where = $dbi->where;
397
$where->clause(['and', 'key2 = :key2']);
398
$where->param({key2 => 2});
399
$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);
400
$result = $dbi->select(table => 'table1');
401
is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');
402

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

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

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

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

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

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

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

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

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

            
478

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

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

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
510
$dbi = DBIx::Custom->connect(%memory);
511
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
512
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
513
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
514
$where = $dbi->where;
515
$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
516
$where->param({ke1 => 1, key2 => 2});
517
$dbi->delete(table => 'table1', where => $where);
518
$result = $dbi->select(table => 'table1');
519
is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');
520

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

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

            
543
test 'delete error';
cleanup test
Yuki Kimoto authored on 2011-08-06
544
$dbi = DBIx::Custom->connect(%memory);
545
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
546
eval{$dbi->delete(table => 'table1')};
547
like($@, qr/"where" must be specified/,
548
         "where key-value pairs not specified");
549

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

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

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

            
573

            
574
test 'select';
cleanup test
Yuki Kimoto authored on 2011-08-06
575
$dbi = DBIx::Custom->connect(%memory);
576
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
577
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
578
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
579
$rows = $dbi->select(table => 'table1')->all;
580
is_deeply($rows, [{key1 => 1, key2 => 2},
581
                  {key1 => 3, key2 => 4}], "table");
582

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

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

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

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

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

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

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

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

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

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

            
643
test 'filters';
644
$dbi = DBIx::Custom->new;
645

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
663
$dbi = DBIx::Custom->connect(%memory);
664
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
665
$dbi->dbh->begin_work(0);
666
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
667
$dbi->dbh->rollback;
668

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
681
$dbi = DBIx::Custom->connect(%memory);
682
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
683
$dbi->{_cached} = {};
684
$dbi->cache(0);
685
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
686
is(scalar keys %{$dbi->{_cached}}, 0, 'not cache');
687

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

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

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

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

            
722

            
723
test 'transaction';
cleanup test
Yuki Kimoto authored on 2011-08-06
724
$dbi = DBIx::Custom->connect(%memory);
725
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
726

            
727
$dbi->begin_work;
728

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

            
735
$dbi->rollback if $@;
736

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

            
741
$dbi->begin_work;
742

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

            
748
$dbi->commit unless $@;
749

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

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

            
759

            
760
test 'method';
cleanup test
Yuki Kimoto authored on 2011-08-06
761
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
762
$dbi->method(
763
    one => sub { 1 }
764
);
765
$dbi->method(
766
    two => sub { 2 }
767
);
768
$dbi->method({
769
    twice => sub {
770
        my $self = shift;
771
        return $_[0] * 2;
772
    }
773
});
774

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

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

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

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

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

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

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

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

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

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

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

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

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

            
905
test 'each_column';
cleanup test
Yuki Kimoto authored on 2011-08-06
906
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
907
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
908
$dbi->execute('create table table1 (key1 Date, key2 datetime);');
cleanup test
Yuki Kimoto authored on 2011-08-06
909

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

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1005
$dbi = MyDBI->connect(%memory);
1006
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
1007
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1008
is($dbi->select(table => 'table1')->one->{key1}, 1);
1009

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1028
$dbi = MyDBI->connect(%memory);
1029
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
1030
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1031
is($dbi->select(table => 'table1')->one->{key1}, 1);
1032

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

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

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

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

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

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

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

            
1103
test 'empty where select';
cleanup test
Yuki Kimoto authored on 2011-08-06
1104
$dbi = DBIx::Custom->connect(%memory);
1105
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
1106
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1107
$result = $dbi->select(table => 'table1', where => {});
1108
$row = $result->one;
1109
is_deeply($row, {key1 => 1, key2 => 2});
1110

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

            
1123
test 'DBIx::Custom::Where';
cleanup test
Yuki Kimoto authored on 2011-08-06
1124
$dbi = DBIx::Custom->connect(%memory);
1125
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
1126
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1127
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1128
$where = $dbi->where->clause(['and', 'key1 = :key1', 'key2 = :key2']);
1129
is("$where", "where ( key1 = :key1 and key2 = :key2 )", 'no param');
1130

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

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

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

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

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

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

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

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

            
1200
$where = $dbi->where;
1201
is("$where", '');
1202

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            
1398

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

            
1410

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

            
1415
test 'register_tag_processor';
cleanup test
Yuki Kimoto authored on 2011-08-06
1416
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1417
$dbi->register_tag_processor(
1418
    a => sub { 1 }
1419
);
1420
is($dbi->query_builder->tag_processors->{a}->(), 1);
1421

            
1422
test 'register_tag';
cleanup test
Yuki Kimoto authored on 2011-08-06
1423
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1424
$dbi->register_tag(
1425
    b => sub { 2 }
1426
);
1427
is($dbi->query_builder->tags->{b}->(), 2);
1428

            
1429
test 'table not specify exception';
cleanup test
Yuki Kimoto authored on 2011-08-06
1430
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1431
eval {$dbi->insert};
1432
like($@, qr/table/);
1433
eval {$dbi->update};
1434
like($@, qr/table/);
1435
eval {$dbi->delete};
1436
like($@, qr/table/);
1437
eval {$dbi->select};
1438
like($@, qr/table/);
1439

            
1440

            
1441
test 'more tests';
cleanup test
Yuki Kimoto authored on 2011-08-06
1442
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1443
eval{$dbi->apply_filter('table', 'column', [])};
1444
like($@, qr/apply_filter/);
1445

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

            
1449
$dbi->apply_filter(
1450

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1461
$dbi = DBIx::Custom->connect(%memory);
1462
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
1463
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
1464
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
1465
$dbi->apply_filter('table1', 'key2', {});
1466
$rows = $dbi->select(table => 'table1', where => {key2 => 2})->all;
1467
is_deeply($rows, [{key1 => 1, key2 => 2}]);
1468

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1469
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1470
eval {$dbi->apply_filter('table1', 'key2', {out => 'no'})};
1471
like($@, qr/not registered/);
1472
eval {$dbi->apply_filter('table1', 'key2', {in => 'no'})};
1473
like($@, qr/not registered/);
1474
$dbi->method({one => sub { 1 }});
1475
is($dbi->one, 1);
1476

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

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

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

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

            
1520
test 'dbi_option';
cleanup test
Yuki Kimoto authored on 2011-08-06
1521
$dbi = DBIx::Custom->connect(%memory,
cleanup test
Yuki Kimoto authored on 2011-08-06
1522
                             dbi_option => {PrintError => 1});
1523
ok($dbi->dbh->{PrintError});
cleanup test
Yuki Kimoto authored on 2011-08-06
1524
$dbi = DBIx::Custom->connect(%memory,
cleanup test
Yuki Kimoto authored on 2011-08-06
1525
                             dbi_options => {PrintError => 1});
1526
ok($dbi->dbh->{PrintError});
1527

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

            
1534
test 'filter __ expression';
cleanup test
Yuki Kimoto authored on 2011-08-06
1535
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1536
$dbi->execute('create table company (id, name, location_id)');
1537
$dbi->execute('create table location (id, name)');
1538
$dbi->apply_filter('location',
1539
  name => {in => sub { uc $_[0] } }
1540
);
1541

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

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

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

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

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

            
1577
{
1578
    package MyDBI4;
1579

            
1580
    use strict;
1581
    use warnings;
1582

            
1583
    use base 'DBIx::Custom';
1584

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

            
1596
    package MyModel2::Base1;
1597

            
1598
    use strict;
1599
    use warnings;
1600

            
1601
    use base 'DBIx::Custom::Model';
1602

            
1603
    package MyModel2::book;
1604

            
1605
    use strict;
1606
    use warnings;
1607

            
1608
    use base 'MyModel2::Base1';
1609

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

            
1616
    sub list { shift->select; }
1617

            
1618
    package MyModel2::Company;
1619

            
1620
    use strict;
1621
    use warnings;
1622

            
1623
    use base 'MyModel2::Base1';
1624

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

            
1631
    sub list { shift->select; }
1632
}
cleanup test
Yuki Kimoto authored on 2011-08-06
1633
$dbi = MyDBI4->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1634
$dbi->execute("create table book (title, author)");
1635
$model = $dbi->model('book');
1636
$model->insert({title => 'a', author => 'b'});
1637
is_deeply($model->list->all, [{title => 'a', author => 'b'}], 'basic');
1638
$dbi->execute("create table company (name)");
1639
$model = $dbi->model('company');
1640
$model->insert({name => 'a'});
1641
is_deeply($model->list->all, [{name => 'a'}], 'basic');
1642

            
1643
{
1644
     package MyDBI5;
1645

            
1646
    use strict;
1647
    use warnings;
1648

            
1649
    use base 'DBIx::Custom';
1650

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

            
1667
test 'primary_key';
1668
use MyDBI1;
cleanup test
Yuki Kimoto authored on 2011-08-06
1669
$dbi = MyDBI1->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1670
$model = $dbi->model('book');
1671
$model->primary_key(['id', 'number']);
1672
is_deeply($model->primary_key, ['id', 'number']);
1673

            
1674
test 'columns';
1675
use MyDBI1;
cleanup test
Yuki Kimoto authored on 2011-08-06
1676
$dbi = MyDBI1->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1677
$model = $dbi->model('book');
1678
$model->columns(['id', 'number']);
1679
is_deeply($model->columns, ['id', 'number']);
1680

            
1681
test 'setup_model';
1682
use MyDBI1;
cleanup test
Yuki Kimoto authored on 2011-08-06
1683
$dbi = MyDBI1->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1684
$dbi->execute('create table book (id)');
1685
$dbi->execute('create table company (id, name);');
1686
$dbi->execute('create table test (id, name, primary key (id, name));');
1687
$dbi->setup_model;
1688
is_deeply($dbi->model('book')->columns, ['id']);
1689
is_deeply($dbi->model('company')->columns, ['id', 'name']);
1690

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

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

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

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1746
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1747
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
1748
$dbi->insert_at(
1749
    {key3 => 3},
1750
    primary_key => ['key1', 'key2'], 
1751
    table => 'table1',
1752
    where => [1, 2],
1753
);
1754
is($dbi->select(table => 'table1')->one->{key1}, 1);
1755
is($dbi->select(table => 'table1')->one->{key2}, 2);
1756
is($dbi->select(table => 'table1')->one->{key3}, 3);
1757

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

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

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

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

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

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

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

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

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

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

            
1872
test 'columns';
1873
use MyDBI1;
cleanup test
Yuki Kimoto authored on 2011-08-06
1874
$dbi = MyDBI1->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1875
$model = $dbi->model('book');
1876

            
1877

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

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

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

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

            
1943

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

            
1974
test 'update_param';
cleanup test
Yuki Kimoto authored on 2011-08-06
1975
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1976
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
1977
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
1978
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
1979

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

            
1993

            
cleanup test
Yuki Kimoto authored on 2011-08-06
1994
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
1995
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
1996
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
1997
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
1998

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2012
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
2013
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
2014
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2015
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2016

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

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

            
2034

            
2035
test 'update_param';
cleanup test
Yuki Kimoto authored on 2011-08-06
2036
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
2037
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
2038
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
2039
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
2040

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

            
2054

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2082

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

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

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

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

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

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

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

            
2158
{
2159
    package MyDBI8;
2160
    
2161
    use base 'DBIx::Custom';
2162
    
2163
    sub connect {
2164
        my $self = shift->SUPER::connect(@_);
2165
        
2166
        $self->include_model('MyModel7');
2167
        
2168
        return $self;
2169
    }
2170
}
cleanup test
Yuki Kimoto authored on 2011-08-06
2171

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

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

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

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

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

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

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

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

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

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

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

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

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

            
2374

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

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

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

            
2418
test 'create_model';
cleanup test
Yuki Kimoto authored on 2011-08-06
2419
$dbi = DBIx::Custom->connect(%memory);
2420
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
2421
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
2422

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

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

            
2462
test 'merge_param';
cleanup test
Yuki Kimoto authored on 2011-08-06
2463
$dbi = DBIx::Custom->new;
2464
$params = [
2465
    {key1 => 1, key2 => 2, key3 => 3},
2466
    {key1 => 1, key2 => 2},
2467
    {key1 => 1}
2468
];
2469
$param = $dbi->merge_param($params->[0], $params->[1], $params->[2]);
2470
is_deeply($param, {key1 => [1, 1, 1], key2 => [2, 2], key3 => 3});
2471

            
2472
$params = [
2473
    {key1 => [1, 2], key2 => 1, key3 => [1, 2]},
2474
    {key1 => [3, 4], key2 => [2, 3], key3 => 3}
2475
];
2476
$param = $dbi->merge_param($params->[0], $params->[1]);
2477
is_deeply($param, {key1 => [1, 2, 3, 4], key2 => [1, 2, 3], key3 => [1, 2, 3]});
cleanup test
Yuki Kimoto authored on 2011-08-06
2478

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

            
2497

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2531
$dbi = DBIx::Custom->connect(%memory);
2532
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
2533
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2534
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2535
$rows = $dbi->select(
2536
    table => 'table1',
2537
    where => [
2538
        'key1 = :key1 and key2 = :key2',
2539
        {key1 => 1, key2 => 2}
2540
    ]
2541
)->all;
2542
is_deeply($rows, [{key1 => 1, key2 => 2}]);
2543

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2557
$dbi = DBIx::Custom->connect(%memory);
2558
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
2559
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2560
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
2561
$dbi->delete(
2562
    table => 'table1',
2563
    where => [
2564
        'key1 = :key1 and key2 = :key2',
2565
         {key1 => 1, key2 => 2}
2566
    ]
2567
);
2568
$rows = $dbi->select(table => 'table1')->all;
2569
is_deeply($rows, [{key1 => 2, key2 => 3}]);
2570

            
2571

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2585
$dbi = DBIx::Custom->connect(%memory);
2586
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
2587
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
2588
$dbi->update(
2589
    table => 'table1',
2590
    param => {key1 => 5},
2591
    where => [
2592
        'key1 = :key1 and key2 = :key2',
2593
        {key1 => 1, key2 => 2}
2594
    ]
2595
);
2596
$rows = $dbi->select(table => 'table1')->all;
2597
is_deeply($rows, [{key1 => 5, key2 => 2}]);
2598

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2624
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
2625
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
2626
$dbi->insert(
2627
    {key3 => 3},
2628
    primary_key => ['key1', 'key2'], 
2629
    table => 'table1',
2630
    id => [1, 2],
2631
);
2632
is($dbi->select(table => 'table1')->one->{key1}, 1);
2633
is($dbi->select(table => 'table1')->one->{key2}, 2);
2634
is($dbi->select(table => 'table1')->one->{key3}, 3);
2635

            
2636

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
2650
$dbi = MyDBI6->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
2651
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
2652
$dbi->model('table1')->insert(
2653
    {key3 => 3},
2654
    id => [1, 2]
2655
);
2656
$result = $dbi->model('table1')->select;
2657
$row = $result->one;
2658
is($row->{key1}, 1);
2659
is($row->{key2}, 2);
2660
is($row->{key3}, 3);
2661

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

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

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

            
2701

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

            
2716

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

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

            
2736

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

            
2752

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

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

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

            
2791

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

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

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

            
2824

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

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

            
2840

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

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

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

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

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

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

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

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

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

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

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

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
3035
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
3036
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
3037
eval {
3038
    $dbi->type_rule(
3039
        from1 => {
3040
            Date => sub { $_[0] * 2 },
3041
        }
3042
    );
3043
};
3044
like($@, qr/lower/);
3045

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

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

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

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

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

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

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

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

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

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

            
3221
test 'separator';
cleanup test
Yuki Kimoto authored on 2011-08-06
3222
$dbi = DBIx::Custom->connect(%memory);
3223
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
3224
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
3225

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

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

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

            
3277

            
3278
test 'filter_off';
cleanup test
Yuki Kimoto authored on 2011-08-06
3279
$dbi = DBIx::Custom->connect(%memory);
3280
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
3281
$dbi->execute('create table table2 (key1 char(255), key3 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
3282

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

            
3297
test 'available_date_type';
cleanup test
Yuki Kimoto authored on 2011-08-06
3298
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
3299
ok($dbi->can('available_data_type'));
3300

            
3301

            
3302
test 'select prefix option';
cleanup test
Yuki Kimoto authored on 2011-08-06
3303
$dbi = DBIx::Custom->connect(%memory);
3304
$dbi->execute($create_table_default);
cleanup test
Yuki Kimoto authored on 2011-08-06
3305
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
3306
$rows = $dbi->select(prefix => 'key1,', column => 'key2', table => 'table1')->all;
3307
is_deeply($rows, [{key1 => 1, key2 => 2}], "table");
3308

            
3309

            
3310
test 'separator';
cleanup test
Yuki Kimoto authored on 2011-08-06
3311
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
3312
is($dbi->separator, '.');
3313
$dbi->separator('-');
3314
is($dbi->separator, '-');
3315
$dbi->separator('__');
3316
is($dbi->separator, '__');
3317
eval { $dbi->separator('?') };
3318
like($@, qr/Separator/);
3319

            
3320

            
3321
test 'map_param';
cleanup test
Yuki Kimoto authored on 2011-08-06
3322
$dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
3323
$param = $dbi->map_param(
3324
    {id => 1, author => 'Ken', price => 1900},
3325
    id => 'book.id',
3326
    author => ['book.author', sub { '%' . $_[0] . '%' }],
3327
    price => ['book.price', {if => sub { $_[0] eq 1900 }}]
3328
);
3329
is_deeply($param, {'book.id' => 1, 'book.author' => '%Ken%',
3330
  'book.price' => 1900});
3331

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

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

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

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

            
3364

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

            
3378

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

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

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

            
3418
test 'last_sql';
3419
{
cleanup test
Yuki Kimoto authored on 2011-08-06
3420
    my $dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
3421
    $dbi->execute("create table table1 (key1, key2)");
3422
    $dbi->execute('select * from table1');
3423
    is($dbi->last_sql, 'select * from table1;');
3424
    
3425
    eval{$dbi->execute("aaa")};
3426
    is($dbi->last_sql, 'aaa;');
3427
    
3428
}
3429

            
3430
test 'DBIx::Custom header';
3431
{
cleanup test
Yuki Kimoto authored on 2011-08-06
3432
    my $dbi = DBIx::Custom->connect(%memory);
cleanup test
Yuki Kimoto authored on 2011-08-06
3433
    $dbi->execute("create table table1 (key1, key2)");
3434
    my $result = $dbi->execute('select key1 as h1, key2 as h2 from table1');
3435
    
3436
    is_deeply($result->header, [qw/h1 h2/]);
3437
    
3438
}
3439

            
3440
test 'Named placeholder :name(operater) syntax';
cleanup test
Yuki Kimoto authored on 2011-08-06
3441
$dbi->execute('drop table table1');
cleanup test
Yuki Kimoto authored on 2011-08-06
3442
$dbi->execute('create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));');
cleanup test
Yuki Kimoto authored on 2011-08-06
3443
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
3444
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
3445

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

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

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

            
3461
$source = "select * from table1 where :table1.key1{=} and :table1.key2{=}";
3462
$result = $dbi->execute(
3463
    $source,
3464
    param => {'table1.key1' => 1, 'table1.key2' => 1},
3465
    filter => {'table1.key2' => sub { $_[0] * 2 }}
3466
);
3467
$rows = $result->all;
3468
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);
3469

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

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

            
cleanup test
Yuki Kimoto authored on 2011-08-06
3513
=cut