DBIx-Custom / t / dbix-custom-core-sqlite.t /
Newer Older
704 lines | 27.122kb
packaging one directory
yuki-kimoto authored on 2009-11-16
1
use Test::More;
2
use strict;
3
use warnings;
4

            
5
BEGIN {
6
    eval { require DBD::SQLite; 1 }
7
        or plan skip_all => 'DBD::SQLite required';
8
    eval { DBD::SQLite->VERSION >= 1.25 }
9
        or plan skip_all => 'DBD::SQLite >= 1.25 required';
10

            
11
    plan 'no_plan';
12
    use_ok('DBIx::Custom');
13
}
14

            
15
# Function for test name
16
my $test;
17
sub test {
18
    $test = shift;
19
}
20

            
21
# Constant varialbes for test
22
my $CREATE_TABLE = {
23
    0 => 'create table table1 (key1 char(255), key2 char(255));',
24
    1 => 'create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));',
25
    2 => 'create table table2 (key1 char(255), key3 char(255));'
26
};
27

            
many change
yuki-kimoto authored on 2010-02-11
28
my $SELECT_TMPLS = {
packaging one directory
yuki-kimoto authored on 2009-11-16
29
    0 => 'select * from table1;'
30
};
31

            
32
my $DROP_TABLE = {
33
    0 => 'drop table table1'
34
};
35

            
36
my $NEW_ARGS = {
37
    0 => {data_source => 'dbi:SQLite:dbname=:memory:'}
38
};
39

            
40
# Variables for test
41
my $dbi;
42
my $sth;
43
my $tmpl;
44
my @tmpls;
45
my $select_tmpl;
46
my $insert_tmpl;
47
my $update_tmpl;
48
my $params;
49
my $sql;
50
my $result;
51
my @rows;
52
my $rows;
53
my $query;
54
my @queries;
55
my $select_query;
56
my $insert_query;
57
my $update_query;
58
my $ret_val;
59

            
60

            
61
test 'disconnect';
62
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
63
$dbi->connect;
64
$dbi->disconnect;
65
ok(!$dbi->dbh, $test);
66

            
67

            
68
test 'connected';
69
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
70
ok(!$dbi->connected, "$test : not connected");
71
$dbi->connect;
72
ok($dbi->connected, "$test : connected");
73

            
74

            
75
test 'preapare';
76
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
77
$sth = $dbi->prepare($CREATE_TABLE->{0});
78
ok($sth, "$test : auto connect");
79
$sth->execute;
80
$sth = $dbi->prepare($DROP_TABLE->{0});
81
ok($sth, "$test : basic");
82

            
83

            
84
test 'do';
85
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
86
$ret_val = $dbi->do($CREATE_TABLE->{0});
87
ok(defined $ret_val, "$test : auto connect");
88
$ret_val = $dbi->do($DROP_TABLE->{0});
89
ok(defined $ret_val, "$test : basic");
90

            
version 0.0901
yuki-kimoto authored on 2009-12-17
91
test 'create_table';
92
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
93
$ret_val = $dbi->create_table(
94
                   'table1',
95
                   'key1 char(255)',
96
                   'key2 char(255)'
97
                 );
98
ok(defined $ret_val, "$test : create_table");
99

            
cleanup
yuki-kimoto authored on 2010-02-11
100
$dbi->insert('table1', {key1 => 1, key2 => 2});
version 0.0901
yuki-kimoto authored on 2009-12-17
101
ok(!$@, "$test : table exist");
102

            
103
$ret_val = $dbi->drop_table('table1');
104
ok(defined $ret_val, "$test : drop table");
105

            
106
eval{$dbi->select('table1')};
107
ok($@, "$test : table not exist");
packaging one directory
yuki-kimoto authored on 2009-11-16
108

            
109
# Prepare table
110
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
111
$dbi->connect;
112
$dbi->do($CREATE_TABLE->{0});
113
$sth = $dbi->prepare("insert into table1 (key1, key2) values (?, ?);");
114
$sth->execute(1, 2);
115
$sth->execute(3, 4);
116

            
117

            
118
test 'DBIx::Custom::Result test';
119
$tmpl = "select key1, key2 from table1";
120
$query = $dbi->create_query($tmpl);
version 0.0901
yuki-kimoto authored on 2009-12-17
121
$result = $dbi->query($query);
packaging one directory
yuki-kimoto authored on 2009-11-16
122

            
123
@rows = ();
124
while (my $row = $result->fetch) {
125
    push @rows, [@$row];
126
}
127
is_deeply(\@rows, [[1, 2], [3, 4]], "$test : fetch scalar context");
128

            
version 0.0901
yuki-kimoto authored on 2009-12-17
129
$result = $dbi->query($query);
packaging one directory
yuki-kimoto authored on 2009-11-16
130
@rows = ();
131
while (my @row = $result->fetch) {
132
    push @rows, [@row];
133
}
134
is_deeply(\@rows, [[1, 2], [3, 4]], "$test : fetch list context");
135

            
version 0.0901
yuki-kimoto authored on 2009-12-17
136
$result = $dbi->query($query);
packaging one directory
yuki-kimoto authored on 2009-11-16
137
@rows = ();
138
while (my $row = $result->fetch_hash) {
139
    push @rows, {%$row};
140
}
141
is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "$test : fetch_hash scalar context");
142

            
version 0.0901
yuki-kimoto authored on 2009-12-17
143
$result = $dbi->query($query);
packaging one directory
yuki-kimoto authored on 2009-11-16
144
@rows = ();
145
while (my %row = $result->fetch_hash) {
146
    push @rows, {%row};
147
}
148
is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "$test : fetch hash list context");
149

            
version 0.0901
yuki-kimoto authored on 2009-12-17
150
$result = $dbi->query($query);
packaging one directory
yuki-kimoto authored on 2009-11-16
151
$rows = $result->fetch_all;
152
is_deeply($rows, [[1, 2], [3, 4]], "$test : fetch_all scalar context");
153

            
version 0.0901
yuki-kimoto authored on 2009-12-17
154
$result = $dbi->query($query);
packaging one directory
yuki-kimoto authored on 2009-11-16
155
@rows = $result->fetch_all;
156
is_deeply(\@rows, [[1, 2], [3, 4]], "$test : fetch_all list context");
157

            
version 0.0901
yuki-kimoto authored on 2009-12-17
158
$result = $dbi->query($query);
packaging one directory
yuki-kimoto authored on 2009-11-16
159
@rows = $result->fetch_hash_all;
160
is_deeply($rows, [[1, 2], [3, 4]], "$test : fetch_hash_all scalar context");
161

            
version 0.0901
yuki-kimoto authored on 2009-12-17
162
$result = $dbi->query($query);
packaging one directory
yuki-kimoto authored on 2009-11-16
163
@rows = $result->fetch_all;
164
is_deeply(\@rows, [[1, 2], [3, 4]], "$test : fetch_hash_all list context");
165

            
166

            
167
test 'Insert query return value';
168
$dbi->do($DROP_TABLE->{0});
169
$dbi->do($CREATE_TABLE->{0});
170
$tmpl = "insert into table1 {insert key1 key2}";
171
$query = $dbi->create_query($tmpl);
version 0.0901
yuki-kimoto authored on 2009-12-17
172
$ret_val = $dbi->query($query, {key1 => 1, key2 => 2});
packaging one directory
yuki-kimoto authored on 2009-11-16
173
ok($ret_val, $test);
174

            
175

            
version 0.0901
yuki-kimoto authored on 2009-12-17
176
test 'Direct query';
packaging one directory
yuki-kimoto authored on 2009-11-16
177
$dbi->do($DROP_TABLE->{0});
178
$dbi->do($CREATE_TABLE->{0});
179
$insert_tmpl = "insert into table1 {insert key1 key2}";
version 0.0901
yuki-kimoto authored on 2009-12-17
180
$dbi->query($insert_tmpl, {key1 => 1, key2 => 2}, sub {
packaging one directory
yuki-kimoto authored on 2009-11-16
181
    my $query = shift;
182
    $query->bind_filter(sub {
many change
yuki-kimoto authored on 2010-02-11
183
        my ($value, $table, $column, $dbi) = @_;
184
        if ($column eq 'key2' && $dbi->isa('DBIx::Custom')) {
packaging one directory
yuki-kimoto authored on 2009-11-16
185
            return $value + 1;
186
        }
187
        return $value;
188
    });
189
});
many change
yuki-kimoto authored on 2010-02-11
190
$result = $dbi->query(['table1', $SELECT_TMPLS->{0}]);
packaging one directory
yuki-kimoto authored on 2009-11-16
191
$rows = $result->fetch_hash_all;
192
is_deeply($rows, [{key1 => 1, key2 => 3}], $test);
193

            
194
test 'Filter basic';
195
$dbi->do($DROP_TABLE->{0});
196
$dbi->do($CREATE_TABLE->{0});
197

            
198
$insert_tmpl  = "insert into table1 {insert key1 key2};";
199
$insert_query = $dbi->create_query($insert_tmpl);
200
$insert_query->bind_filter(sub {
many change
yuki-kimoto authored on 2010-02-11
201
    my ($value, $table, $column, $dbi) = @_;
bind_filter argument is chan...
yuki-kimoto authored on 2009-11-19
202
    
Simplify key search
yuki-kimoto authored on 2010-02-11
203
    if ($table eq '' && $column eq 'key1')
bind_filter argument is chan...
yuki-kimoto authored on 2009-11-19
204
    {
packaging one directory
yuki-kimoto authored on 2009-11-16
205
        return $value * 2;
206
    }
207
    return $value;
208
});
version 0.0901
yuki-kimoto authored on 2009-12-17
209
$dbi->query($insert_query, {key1 => 1, key2 => 2});
many change
yuki-kimoto authored on 2010-02-11
210
$select_query = $dbi->create_query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
211
$select_query->fetch_filter(sub {
bind_filter argument is chan...
yuki-kimoto authored on 2009-11-19
212
    my ($value, $key, $dbi, $infos) = @_;
213
    my ($type, $sth, $i) = @{$infos}{qw/type sth index/};
214
    
215
    if ($key eq 'key2' && $type =~ /char/ && ref $sth eq 'DBI::st' 
216
        && $i == 1 && $dbi->isa('DBIx::Custom'))
217
    {
packaging one directory
yuki-kimoto authored on 2009-11-16
218
        return $value * 3;
219
    }
220
    return $value;
221
});
version 0.0901
yuki-kimoto authored on 2009-12-17
222
$result = $dbi->query($select_query);
packaging one directory
yuki-kimoto authored on 2009-11-16
223
$rows = $result->fetch_hash_all;
224
is_deeply($rows, [{key1 => 2, key2 => 6}], "$test : bind_filter fetch_filter");
225

            
226
$dbi->do($DROP_TABLE->{0});
227
$dbi->do($CREATE_TABLE->{0});
228
$insert_tmpl  = "insert into table1 {insert table1.key1 table1.key2}";
229
$insert_query = $dbi->create_query($insert_tmpl);
230
$insert_query->bind_filter(sub {
many change
yuki-kimoto authored on 2010-02-11
231
    my ($value, $table, $column, $dbi) = @_;
bind_filter argument is chan...
yuki-kimoto authored on 2009-11-19
232
    
many change
yuki-kimoto authored on 2010-02-11
233
    if ($table eq 'table1' && $column eq 'key1') {
packaging one directory
yuki-kimoto authored on 2009-11-16
234
        return $value * 3;
235
    }
236
    return $value;
237
});
many change
yuki-kimoto authored on 2010-02-11
238
$dbi->query($insert_query, {key1 => 1, key2 => 2});
239
$select_query = $dbi->create_query($SELECT_TMPLS->{0});
version 0.0901
yuki-kimoto authored on 2009-12-17
240
$result       = $dbi->query($select_query);
packaging one directory
yuki-kimoto authored on 2009-11-16
241
$rows = $result->fetch_hash_all;
many change
yuki-kimoto authored on 2010-02-11
242
is_deeply($rows, [{key1 => 3, key2 => 2}], "$test : insert with id");
packaging one directory
yuki-kimoto authored on 2009-11-16
243

            
244
test 'Filter in';
245
$insert_tmpl  = "insert into table1 {insert key1 key2};";
246
$insert_query = $dbi->create_query($insert_tmpl);
version 0.0901
yuki-kimoto authored on 2009-12-17
247
$dbi->query($insert_query, {key1 => 2, key2 => 4});
packaging one directory
yuki-kimoto authored on 2009-11-16
248
$select_tmpl = "select * from table1 where {in table1.key1 2} and {in table1.key2 2}";
249
$select_query = $dbi->create_query($select_tmpl);
250
$select_query->bind_filter(sub {
many change
yuki-kimoto authored on 2010-02-11
251
    my ($value, $table, $column, $dbi) = @_;
bind_filter argument is chan...
yuki-kimoto authored on 2009-11-19
252
    
many change
yuki-kimoto authored on 2010-02-11
253
    if ($table eq 'table1' && $column eq 'key1') {
packaging one directory
yuki-kimoto authored on 2009-11-16
254
        return $value * 2;
255
    }
256
    return $value;
257
});
many change
yuki-kimoto authored on 2010-02-11
258
$result = $dbi->query($select_query, {key1 => [1,5], key2 => [2,4]});
packaging one directory
yuki-kimoto authored on 2009-11-16
259
$rows = $result->fetch_hash_all;
260
is_deeply($rows, [{key1 => 2, key2 => 4}], "$test : bind_filter");
261

            
262

            
263
test 'DBIx::Custom::SQL::Template basic tag';
264
$dbi->do($DROP_TABLE->{0});
265
$dbi->do($CREATE_TABLE->{1});
266
$sth = $dbi->prepare("insert into table1 (key1, key2, key3, key4, key5) values (?, ?, ?, ?, ?);");
267
$sth->execute(1, 2, 3, 4, 5);
268
$sth->execute(6, 7, 8, 9, 10);
269

            
270
$tmpl = "select * from table1 where {= key1} and {<> key2} and {< key3} and {> key4} and {>= key5};";
271
$query = $dbi->create_query($tmpl);
version 0.0901
yuki-kimoto authored on 2009-12-17
272
$result = $dbi->query($query, {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
packaging one directory
yuki-kimoto authored on 2009-11-16
273
$rows = $result->fetch_hash_all;
274
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic tag1");
275

            
many change
yuki-kimoto authored on 2010-02-11
276
$tmpl = "select * from table1 where {= table1.key1#id} and {<> table1.key2#id} and {< table1.key3#id} and {> table1.key4#id} and {>= table1.key5#id};";
packaging one directory
yuki-kimoto authored on 2009-11-16
277
$query = $dbi->create_query($tmpl);
many change
yuki-kimoto authored on 2010-02-11
278
$result = $dbi->query($query, {id => {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5}});
packaging one directory
yuki-kimoto authored on 2009-11-16
279
$rows = $result->fetch_hash_all;
many change
yuki-kimoto authored on 2010-02-11
280
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic tag1 with id");
packaging one directory
yuki-kimoto authored on 2009-11-16
281

            
282
$tmpl = "select * from table1 where {= table1.key1} and {<> table1.key2} and {< table1.key3} and {> table1.key4} and {>= table1.key5};";
283
$query = $dbi->create_query($tmpl);
many change
yuki-kimoto authored on 2010-02-11
284
$result = $dbi->query($query, {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
packaging one directory
yuki-kimoto authored on 2009-11-16
285
$rows = $result->fetch_hash_all;
286
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic tag1 with table dot");
287

            
288
$tmpl = "select * from table1 where {<= key1} and {like key2};";
289
$query = $dbi->create_query($tmpl);
version 0.0901
yuki-kimoto authored on 2009-12-17
290
$result = $dbi->query($query, {key1 => 1, key2 => '%2%'});
packaging one directory
yuki-kimoto authored on 2009-11-16
291
$rows = $result->fetch_hash_all;
292
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic tag2");
293

            
many change
yuki-kimoto authored on 2010-02-11
294
$tmpl = "select * from table1 where {<= table1.key1#id} and {like table1.key2#id};";
packaging one directory
yuki-kimoto authored on 2009-11-16
295
$query = $dbi->create_query($tmpl);
many change
yuki-kimoto authored on 2010-02-11
296
$result = $dbi->query($query, {id => {key1 => 1, key2 => '%2%'}});
packaging one directory
yuki-kimoto authored on 2009-11-16
297
$rows = $result->fetch_hash_all;
298
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic tag2 with table");
299

            
300
$tmpl = "select * from table1 where {<= table1.key1} and {like table1.key2};";
301
$query = $dbi->create_query($tmpl);
many change
yuki-kimoto authored on 2010-02-11
302
$result = $dbi->query($query, {'key1' => 1, 'key2' => '%2%'});
packaging one directory
yuki-kimoto authored on 2009-11-16
303
$rows = $result->fetch_hash_all;
304
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic tag2 with table dot");
305

            
306

            
307
test 'DIB::Custom::SQL::Template in tag';
308
$dbi->do($DROP_TABLE->{0});
309
$dbi->do($CREATE_TABLE->{1});
310
$sth = $dbi->prepare("insert into table1 (key1, key2, key3, key4, key5) values (?, ?, ?, ?, ?);");
311
$sth->execute(1, 2, 3, 4, 5);
312
$sth->execute(6, 7, 8, 9, 10);
313

            
314
$tmpl = "select * from table1 where {in key1 2};";
315
$query = $dbi->create_query($tmpl);
version 0.0901
yuki-kimoto authored on 2009-12-17
316
$result = $dbi->query($query, {key1 => [9, 1]});
packaging one directory
yuki-kimoto authored on 2009-11-16
317
$rows = $result->fetch_hash_all;
318
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic");
319

            
many change
yuki-kimoto authored on 2010-02-11
320
$tmpl = "select * from table1 where {in table1.key1#id 2};";
packaging one directory
yuki-kimoto authored on 2009-11-16
321
$query = $dbi->create_query($tmpl);
many change
yuki-kimoto authored on 2010-02-11
322
$result = $dbi->query($query, {id => {key1 => [9, 1]}});
packaging one directory
yuki-kimoto authored on 2009-11-16
323
$rows = $result->fetch_hash_all;
324
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : with table");
325

            
many change
yuki-kimoto authored on 2010-02-11
326
$tmpl = "select * from table1 where {in table1.key1#id 2};";
packaging one directory
yuki-kimoto authored on 2009-11-16
327
$query = $dbi->create_query($tmpl);
many change
yuki-kimoto authored on 2010-02-11
328
$result = $dbi->query($query, {id => {'key1' => [9, 1]}});
packaging one directory
yuki-kimoto authored on 2009-11-16
329
$rows = $result->fetch_hash_all;
330
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : with table dot");
331

            
332

            
333
test 'DBIx::Custom::SQL::Template insert tag';
334
$dbi->do("delete from table1");
335
$insert_tmpl = 'insert into table1 {insert key1 key2 key3 key4 key5}';
version 0.0901
yuki-kimoto authored on 2009-12-17
336
$dbi->query($insert_tmpl, {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
packaging one directory
yuki-kimoto authored on 2009-11-16
337

            
many change
yuki-kimoto authored on 2010-02-11
338
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
339
$rows = $result->fetch_hash_all;
340
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic");
341

            
342
$dbi->do("delete from table1");
many change
yuki-kimoto authored on 2010-02-11
343
$insert_tmpl = 'insert into table1 {insert table1.key1#id table1.key2#id table1.key3#id table1.key4#id table1.key5#id}';
344
$dbi->query($insert_tmpl, {id => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}});
345
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
346
$rows = $result->fetch_hash_all;
347
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : with table name");
348

            
349
test 'DBIx::Custom::SQL::Template update tag';
350
$dbi->do("delete from table1");
351
$insert_tmpl = "insert into table1 {insert key1 key2 key3 key4 key5}";
version 0.0901
yuki-kimoto authored on 2009-12-17
352
$dbi->query($insert_tmpl, {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
353
$dbi->query($insert_tmpl, {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
packaging one directory
yuki-kimoto authored on 2009-11-16
354

            
355
$update_tmpl = 'update table1 {update key1 key2 key3 key4} where {= key5}';
version 0.0901
yuki-kimoto authored on 2009-12-17
356
$dbi->query($update_tmpl, {key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5});
packaging one directory
yuki-kimoto authored on 2009-11-16
357

            
many change
yuki-kimoto authored on 2010-02-11
358
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
359
$rows = $result->fetch_hash_all;
360
is_deeply($rows, [{key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5},
361
                  {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}], "$test : basic");
362

            
many change
yuki-kimoto authored on 2010-02-11
363
$update_tmpl = 'update table1 {update table1.key1#id table1.key2#id table1.key3#id table1.key4#id} where {= table1.key5#id}';
364
$dbi->query($update_tmpl, {id => {key1 => 3, key2 => 3, key3 => 3, key4 => 3, key5 => 5}});
365
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
366
$rows = $result->fetch_hash_all;
367
is_deeply($rows, [{key1 => 3, key2 => 3, key3 => 3, key4 => 3, key5 => 5},
368
                  {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}], "$test : with table name");
369

            
remove run_transaction().
yuki-kimoto authored on 2010-01-30
370
test 'transaction';
packaging one directory
yuki-kimoto authored on 2009-11-16
371
$dbi->do($DROP_TABLE->{0});
372
$dbi->do($CREATE_TABLE->{0});
remove DBIx::Custom::Transac...
yuki-kimoto authored on 2010-02-11
373
$dbi->run_transaction(sub {
packaging one directory
yuki-kimoto authored on 2009-11-16
374
    $insert_tmpl = 'insert into table1 {insert key1 key2}';
version 0.0901
yuki-kimoto authored on 2009-12-17
375
    $dbi->query($insert_tmpl, {key1 => 1, key2 => 2});
376
    $dbi->query($insert_tmpl, {key1 => 3, key2 => 4});
packaging one directory
yuki-kimoto authored on 2009-11-16
377
});
many change
yuki-kimoto authored on 2010-02-11
378
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
379
$rows   = $result->fetch_hash_all;
380
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "$test : commit");
381

            
382
$dbi->do($DROP_TABLE->{0});
383
$dbi->do($CREATE_TABLE->{0});
384
$dbi->dbh->{RaiseError} = 0;
385
eval{
remove DBIx::Custom::Transac...
yuki-kimoto authored on 2010-02-11
386
    $dbi->run_transaction(sub {
packaging one directory
yuki-kimoto authored on 2009-11-16
387
        $insert_tmpl = 'insert into table1 {insert key1 key2}';
version 0.0901
yuki-kimoto authored on 2009-12-17
388
        $dbi->query($insert_tmpl, {key1 => 1, key2 => 2});
packaging one directory
yuki-kimoto authored on 2009-11-16
389
        die "Fatal Error";
version 0.0901
yuki-kimoto authored on 2009-12-17
390
        $dbi->query($insert_tmpl, {key1 => 3, key2 => 4});
packaging one directory
yuki-kimoto authored on 2009-11-16
391
    })
392
};
393
like($@, qr/Fatal Error.*Rollback is success/ms, "$test : Rollback success message");
394
ok(!$dbi->dbh->{RaiseError}, "$test : restore RaiseError value");
many change
yuki-kimoto authored on 2010-02-11
395
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
396
$rows   = $result->fetch_hash_all;
397
is_deeply($rows, [], "$test : rollback");
398

            
399

            
remove run_transaction().
yuki-kimoto authored on 2010-01-30
400

            
packaging one directory
yuki-kimoto authored on 2009-11-16
401
test 'Error case';
402
$dbi = DBIx::Custom->new;
remove DBIx::Custom::Transac...
yuki-kimoto authored on 2010-02-11
403
eval{$dbi->run_transaction};
packaging one directory
yuki-kimoto authored on 2009-11-16
404
like($@, qr/Not yet connect to database/, "$test : Yet Connected");
405

            
406
$dbi = DBIx::Custom->new(data_source => 'dbi:SQLit');
407
eval{$dbi->connect;};
408
ok($@, "$test : connect error");
409

            
410
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
411
$dbi->connect;
412
$dbi->dbh->{AutoCommit} = 0;
remove DBIx::Custom::Transac...
yuki-kimoto authored on 2010-02-11
413
eval{$dbi->run_transaction};
packaging one directory
yuki-kimoto authored on 2009-11-16
414
like($@, qr/AutoCommit must be true before transaction start/,
remove run_transaction().
yuki-kimoto authored on 2010-01-30
415
         "$test : transaction auto commit is false");
packaging one directory
yuki-kimoto authored on 2009-11-16
416

            
417
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
418
$sql = 'laksjdf';
419
eval{$dbi->prepare($sql)};
420
like($@, qr/$sql/, "$test : prepare fail");
421

            
422
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
423
$sql = 'laksjdf';
424
eval{$dbi->do($sql, qw/1 2 3/)};
425
like($@, qr/$sql/, "$test : do fail");
426

            
427
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
428
eval{$dbi->create_query("{p }")};
429
ok($@, "$test : create_query invalid SQL template");
430

            
431
test 'insert';
432
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
433
$dbi->do($CREATE_TABLE->{0});
434
$dbi->insert('table1', {key1 => 1, key2 => 2});
435
$dbi->insert('table1', {key1 => 3, key2 => 4});
many change
yuki-kimoto authored on 2010-02-11
436
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
437
$rows   = $result->fetch_hash_all;
438
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "$test : basic");
439

            
440
$dbi->do('delete from table1');
cleanup insert
yuki-kimoto authored on 2010-04-28
441
$dbi->insert('table1', {key1 => 1, key2 => 2}, 
442
    {
443
        query_edit_cb => sub {
444
            my $query = shift;
445
            $query->bind_filter(sub {
446
                my ($value, $table, $column, $dbi) = @_;
447
                if ($column eq 'key1') {
448
                    return $value * 3;
449
                }
450
                return $value;
451
            });
packaging one directory
yuki-kimoto authored on 2009-11-16
452
        }
cleanup insert
yuki-kimoto authored on 2010-04-28
453
    }
454
);
many change
yuki-kimoto authored on 2010-02-11
455
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
456
$rows   = $result->fetch_hash_all;
457
is_deeply($rows, [{key1 => 3, key2 => 2}], "$test : edit_query_callback");
458

            
cleanup insert
yuki-kimoto authored on 2010-04-28
459
$dbi->insert('table1', {key1 => 1, key2 => 2}, {append => '   ', query_edit_cb => sub {
insert, update, delete appnd...
yuki-kimoto authored on 2009-11-16
460
    my $query = shift;
461
    like($query->sql, qr/insert into table1 \(.+\) values \(\?, \?\)    ;/, 
462
        "$test: append statement");
cleanup insert
yuki-kimoto authored on 2010-04-28
463
}});
packaging one directory
yuki-kimoto authored on 2009-11-16
464

            
465
test 'insert error';
466
eval{$dbi->insert('table1')};
467
like($@, qr/Key-value pairs for insert must be specified to 'insert' second argument/, "$test : insert key-value not specifed");
468

            
cleanup insert
yuki-kimoto authored on 2010-04-28
469
eval{$dbi->insert('table1', {key1 => 1, key2 => 2}, {append => '', query_edit_cb => 'aaa'})};
packaging one directory
yuki-kimoto authored on 2009-11-16
470
like($@, qr/Query edit callback must be code reference/, "$test : query edit callback not code ref");
471

            
472

            
473
test 'update';
474
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
475
$dbi->do($CREATE_TABLE->{1});
476
$dbi->insert('table1', {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
477
$dbi->insert('table1', {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
cleanup update and update_al...
yuki-kimoto authored on 2010-04-28
478
$dbi->update('table1', {key2 => 11}, {where => {key1 => 1}});
many change
yuki-kimoto authored on 2010-02-11
479
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
480
$rows   = $result->fetch_hash_all;
481
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
482
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
483
                  "$test : basic");
484
                  
many change
yuki-kimoto authored on 2010-02-11
485
$dbi->do("delete from table1");
486
$dbi->insert('table1', {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
487
$dbi->insert('table1', {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
cleanup update and update_al...
yuki-kimoto authored on 2010-04-28
488
$dbi->update('table1', {key2 => 12}, {where => {key2 => 2, key3 => 3}});
many change
yuki-kimoto authored on 2010-02-11
489
$result = $dbi->query($SELECT_TMPLS->{0});
490
$rows   = $result->fetch_hash_all;
491
is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
492
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
493
                  "$test : update key same as search key");
packaging one directory
yuki-kimoto authored on 2009-11-16
494

            
495
$dbi->do("delete from table1");
496
$dbi->insert('table1', {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
497
$dbi->insert('table1', {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
cleanup update and update_al...
yuki-kimoto authored on 2010-04-28
498
$dbi->update('table1', {key2 => 11}, {where => {key1 => 1}, query_edit_cb => sub {
packaging one directory
yuki-kimoto authored on 2009-11-16
499
    my $query = shift;
500
    $query->bind_filter(sub {
many change
yuki-kimoto authored on 2010-02-11
501
        my ($value, $table, $column, $dbi) = @_;
502
        if ($column eq 'key2') {
packaging one directory
yuki-kimoto authored on 2009-11-16
503
            return $value * 2;
504
        }
505
        return $value;
506
    });
cleanup update and update_al...
yuki-kimoto authored on 2010-04-28
507
}});
many change
yuki-kimoto authored on 2010-02-11
508
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
509
$rows   = $result->fetch_hash_all;
510
is_deeply($rows, [{key1 => 1, key2 => 22, key3 => 3, key4 => 4, key5 => 5},
511
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
512
                  "$test : query edit callback");
513

            
cleanup update and update_al...
yuki-kimoto authored on 2010-04-28
514
$dbi->update('table1', {key2 => 11}, {where => {key1 => 1}, append => '   ', query_edit_cb => sub {
insert, update, delete appnd...
yuki-kimoto authored on 2009-11-16
515
    my $query = shift;
many change
yuki-kimoto authored on 2010-02-11
516
    is($query->sql, 'update table1 set key2 = ? where table1.key1 = ?    ;',
insert, update, delete appnd...
yuki-kimoto authored on 2009-11-16
517
       "$test: append statement");
cleanup update and update_al...
yuki-kimoto authored on 2010-04-28
518
}});
insert, update, delete appnd...
yuki-kimoto authored on 2009-11-16
519

            
packaging one directory
yuki-kimoto authored on 2009-11-16
520

            
521
test 'update error';
522
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
523
$dbi->do($CREATE_TABLE->{1});
524
eval{$dbi->update('table1')};
525
like($@, qr/Key-value pairs for update must be specified to 'update' second argument/,
526
         "$test : update key-value pairs not specified");
527

            
528
eval{$dbi->update('table1', {key2 => 1})};
529
like($@, qr/Key-value pairs for where clause must be specified to 'update' third argument/,
530
         "$test : where key-value pairs not specified");
531

            
cleanup update and update_al...
yuki-kimoto authored on 2010-04-28
532
eval{$dbi->update('table1', {key2 => 1}, {where => {key2 => 3}, append => '', query_edit_cb => 'aaa'})};
packaging one directory
yuki-kimoto authored on 2009-11-16
533
like($@, qr/Query edit callback must be code reference/, 
534
         "$test : query edit callback not code reference");
535

            
536

            
537
test 'update_all';
538
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
539
$dbi->do($CREATE_TABLE->{1});
540
$dbi->insert('table1', {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
541
$dbi->insert('table1', {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
cleanup update and update_al...
yuki-kimoto authored on 2010-04-28
542
$dbi->update_all('table1', {key2 => 10}, {query_edit_cb => sub {
packaging one directory
yuki-kimoto authored on 2009-11-16
543
    my $query = shift;
544
    $query->bind_filter(sub {
many change
yuki-kimoto authored on 2010-02-11
545
        my ($value, $table, $column, $dbi) = @_;
packaging one directory
yuki-kimoto authored on 2009-11-16
546
        return $value * 2;
547
    })
cleanup update and update_al...
yuki-kimoto authored on 2010-04-28
548
}});
many change
yuki-kimoto authored on 2010-02-11
549
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
550
$rows   = $result->fetch_hash_all;
551
is_deeply($rows, [{key1 => 1, key2 => 20, key3 => 3, key4 => 4, key5 => 5},
552
                  {key1 => 6, key2 => 20, key3 => 8, key4 => 9, key5 => 10}],
553
                  "$test : query edit callback");
554

            
555

            
556
test 'delete';
557
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
558
$dbi->do($CREATE_TABLE->{0});
559
$dbi->insert('table1', {key1 => 1, key2 => 2});
560
$dbi->insert('table1', {key1 => 3, key2 => 4});
561
$dbi->delete('table1', {key1 => 1});
many change
yuki-kimoto authored on 2010-02-11
562
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
563
$rows   = $result->fetch_hash_all;
564
is_deeply($rows, [{key1 => 3, key2 => 4}], "$test : basic");
565

            
566
$dbi->do("delete from table1;");
567
$dbi->insert('table1', {key1 => 1, key2 => 2});
568
$dbi->insert('table1', {key1 => 3, key2 => 4});
569
$dbi->delete('table1', {key2 => 1}, sub {
570
    my $query = shift;
571
    $query->bind_filter(sub {
many change
yuki-kimoto authored on 2010-02-11
572
        my ($value, $table, $column, $dbi) = @_;
packaging one directory
yuki-kimoto authored on 2009-11-16
573
        return $value * 2;
574
    });
575
});
many change
yuki-kimoto authored on 2010-02-11
576
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
577
$rows   = $result->fetch_hash_all;
578
is_deeply($rows, [{key1 => 3, key2 => 4}], "$test : query edit callback");
579

            
insert, update, delete appnd...
yuki-kimoto authored on 2009-11-16
580
$dbi->delete('table1', {key1 => 1}, '   ', sub {
581
    my $query = shift;
many change
yuki-kimoto authored on 2010-02-11
582
    is($query->sql, 'delete from table1 where table1.key1 = ?    ;',
insert, update, delete appnd...
yuki-kimoto authored on 2009-11-16
583
       "$test: append statement");
584
});
585

            
586

            
packaging one directory
yuki-kimoto authored on 2009-11-16
587
$dbi->delete_all('table1');
588
$dbi->insert('table1', {key1 => 1, key2 => 2});
589
$dbi->insert('table1', {key1 => 3, key2 => 4});
590
$dbi->delete('table1', {key1 => 1, key2 => 2});
591
$rows = $dbi->select('table1')->fetch_hash_all;
592
is_deeply($rows, [{key1 => 3, key2 => 4}], "$test : delete multi key");
593

            
594

            
595
test 'delete error';
596
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
597
$dbi->do($CREATE_TABLE->{0});
598
eval{$dbi->delete('table1')};
599
like($@, qr/Key-value pairs for where clause must be specified to 'delete' second argument/,
600
         "$test : where key-value pairs not specified");
601

            
insert, update, delete appnd...
yuki-kimoto authored on 2009-11-16
602
eval{$dbi->delete('table1', {key1 => 1}, '', 'aaa')};
packaging one directory
yuki-kimoto authored on 2009-11-16
603
like($@, qr/Query edit callback must be code reference/, 
604
         "$test : query edit callback not code ref");
605

            
606

            
607
test 'delete_all';
608
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
609
$dbi->do($CREATE_TABLE->{0});
610
$dbi->insert('table1', {key1 => 1, key2 => 2});
611
$dbi->insert('table1', {key1 => 3, key2 => 4});
612
$dbi->delete_all('table1');
many change
yuki-kimoto authored on 2010-02-11
613
$result = $dbi->query($SELECT_TMPLS->{0});
packaging one directory
yuki-kimoto authored on 2009-11-16
614
$rows   = $result->fetch_hash_all;
615
is_deeply($rows, [], "$test : basic");
616

            
617

            
618
test 'select';
619
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
620
$dbi->do($CREATE_TABLE->{0});
621
$dbi->insert('table1', {key1 => 1, key2 => 2});
622
$dbi->insert('table1', {key1 => 3, key2 => 4});
623
$rows = $dbi->select('table1')->fetch_hash_all;
624
is_deeply($rows, [{key1 => 1, key2 => 2},
625
                  {key1 => 3, key2 => 4}], "$test : table");
626

            
627
$rows = $dbi->select('table1', ['key1'])->fetch_hash_all;
628
is_deeply($rows, [{key1 => 1}, {key1 => 3}], "$test : table and columns and where key");
629

            
630
$rows = $dbi->select('table1', {key1 => 1})->fetch_hash_all;
631
is_deeply($rows, [{key1 => 1, key2 => 2}], "$test : table and columns and where key");
632

            
633
$rows = $dbi->select('table1', ['key1'], {key1 => 3})->fetch_hash_all;
634
is_deeply($rows, [{key1 => 3}], "$test : table and columns and where key");
635

            
636
$rows = $dbi->select('table1', "order by key1 desc limit 1")->fetch_hash_all;
637
is_deeply($rows, [{key1 => 3, key2 => 4}], "$test : append statement");
638

            
639
$rows = $dbi->select('table1', {key1 => 2}, sub {
640
    my $query = shift;
641
    $query->bind_filter(sub {
many change
yuki-kimoto authored on 2010-02-11
642
        my ($value, $table, $column, $dbi) = @_;
643
        if ($column eq 'key1') {
packaging one directory
yuki-kimoto authored on 2009-11-16
644
            return $value - 1;
645
        }
646
        return $value;
647
    });
648
})->fetch_hash_all;
649
is_deeply($rows, [{key1 => 1, key2 => 2}], "$test : query edit call back");
650

            
651
$dbi->do($CREATE_TABLE->{2});
652
$dbi->insert('table2', {key1 => 1, key3 => 5});
653
$rows = $dbi->select([qw/table1 table2/],
654
                     ['table1.key1 as table1_key1', 'table2.key1 as table2_key1', 'key2', 'key3'],
655
                     {'table1.key2' => 2},
656
                     "where table1.key1 = table2.key1")->fetch_hash_all;
657
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "$test : join");
658

            
659
test 'Cache';
660
$dbi = DBIx::Custom->new($NEW_ARGS->{0});
661
DBIx::Custom->query_cache_max(2);
662
$dbi->do($CREATE_TABLE->{0});
cleanup
yuki-kimoto authored on 2009-12-22
663
delete $DBIx::Custom::CLASS_ATTRS->{_query_caches};
664
delete $DBIx::Custom::CLASS_ATTRS->{_query_cache_keys};
packaging one directory
yuki-kimoto authored on 2009-11-16
665
$tmpls[0] = "insert into table1 {insert key1 key2}";
666
$queries[0] = $dbi->create_query($tmpls[0]);
667
is(DBIx::Custom->_query_caches->{$tmpls[0]}{sql}, $queries[0]->sql, "$test : sql first");
668
is(DBIx::Custom->_query_caches->{$tmpls[0]}{key_infos}, $queries[0]->key_infos, "$test : key_infos first");
669
is_deeply(DBIx::Custom->_query_cache_keys, [@tmpls], "$test : cache key first");
670

            
671
$tmpls[1] = "select * from table1";
672
$queries[1] = $dbi->create_query($tmpls[1]);
673
is(DBIx::Custom->_query_caches->{$tmpls[0]}{sql}, $queries[0]->sql, "$test : sql first");
674
is(DBIx::Custom->_query_caches->{$tmpls[0]}{key_infos}, $queries[0]->key_infos, "$test : key_infos first");
675
is(DBIx::Custom->_query_caches->{$tmpls[1]}{sql}, $queries[1]->sql, "$test : sql second");
676
is(DBIx::Custom->_query_caches->{$tmpls[1]}{key_infos}, $queries[1]->key_infos, "$test : key_infos second");
677
is_deeply(DBIx::Custom->_query_cache_keys, [@tmpls], "$test : cache key second");
678

            
679
$tmpls[2] = "select key1, key2 from table1";
680
$queries[2] = $dbi->create_query($tmpls[2]);
681
ok(!exists DBIx::Custom->_query_caches->{$tmpls[0]}, "$test : cache overflow deleted key");
682
is(DBIx::Custom->_query_caches->{$tmpls[1]}{sql}, $queries[1]->sql, "$test : sql cache overflow deleted key");
683
is(DBIx::Custom->_query_caches->{$tmpls[1]}{key_infos}, $queries[1]->key_infos, "$test : key_infos cache overflow deleted key");
684
is(DBIx::Custom->_query_caches->{$tmpls[2]}{sql}, $queries[2]->sql, "$test : sql cache overflow deleted key");
685
is(DBIx::Custom->_query_caches->{$tmpls[2]}{key_infos}, $queries[2]->key_infos, "$test : key_infos cache overflow deleted key");
686
is_deeply(DBIx::Custom->_query_cache_keys, [@tmpls[1, 2]], "$test : cache key third");
687

            
688
$queries[1] = $dbi->create_query($tmpls[1]);
689
ok(!exists DBIx::Custom->_query_caches->{$tmpls[0]}, "$test : cache overflow deleted key");
690
is(DBIx::Custom->_query_caches->{$tmpls[1]}{sql}, $queries[1]->sql, "$test : sql cache overflow deleted key");
fix timeformat tests
yuki-kimoto authored on 2009-11-23
691
is_deeply(DBIx::Custom->_query_caches->{$tmpls[1]}{key_infos}, $queries[1]->key_infos, "$test : key_infos cache overflow deleted key");
packaging one directory
yuki-kimoto authored on 2009-11-16
692
is(DBIx::Custom->_query_caches->{$tmpls[2]}{sql}, $queries[2]->sql, "$test : sql cache overflow deleted key");
fix timeformat tests
yuki-kimoto authored on 2009-11-23
693
is_deeply(DBIx::Custom->_query_caches->{$tmpls[2]}{key_infos}, $queries[2]->key_infos, "$test : key_infos cache overflow deleted key");
packaging one directory
yuki-kimoto authored on 2009-11-16
694
is_deeply(DBIx::Custom->_query_cache_keys, [@tmpls[1, 2]], "$test : cache key third");
695

            
fix timeformat tests
yuki-kimoto authored on 2009-11-23
696
$query = $dbi->create_query($tmpls[0]);
697
$query->bind_filter('aaa');
698
$query = $dbi->create_query($tmpls[0]);
699
ok(!$query->bind_filter, "$test : only cached sql and key_infos");
700
$query->bind_filter('bbb');
701
$query = $dbi->create_query($tmpls[0]);
702
ok(!$query->bind_filter, "$test : only cached sql and key_infos");
703

            
704