DBIx-Custom / t / dbix-custom-core-sqlite.t /
Newer Older
416 lines | 16.559kb
removed register_format()
yuki-kimoto authored on 2010-05-26
1
use Test::More;
2
use strict;
3
use warnings;
4

            
5
use utf8;
6
use Encode qw/encode_utf8 decode_utf8/;
7

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

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

            
18
# Function for test name
19
my $test;
20
sub test {
21
    $test = shift;
22
}
23

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

            
31
my $SELECT_TMPLS = {
32
    0 => 'select * from table1;'
33
};
34

            
35
my $DROP_TABLE = {
36
    0 => 'drop table table1'
37
};
38

            
39
my $NEW_ARGS = {
40
    0 => {data_source => 'dbi:SQLite:dbname=:memory:'}
41
};
42

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

            
64

            
65
test 'disconnect';
66
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
67
$dbi->disconnect;
68
ok(!$dbi->dbh, $test);
69

            
70
# Prepare table
71
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
72
$dbi->execute($CREATE_TABLE->{0});
73
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
74
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
75

            
76
test 'DBIx::Custom::Result test';
77
$tmpl = "select key1, key2 from table1";
78
$query = $dbi->create_query($tmpl);
79
$result = $dbi->execute($query);
80

            
81
@rows = ();
82
while (my $row = $result->fetch) {
83
    push @rows, [@$row];
84
}
removed reconnect method
yuki-kimoto authored on 2010-05-28
85
is_deeply(\@rows, [[1, 2], [3, 4]], "$test : fetch");
removed register_format()
yuki-kimoto authored on 2010-05-26
86

            
87
$result = $dbi->execute($query);
88
@rows = ();
89
while (my $row = $result->fetch_hash) {
90
    push @rows, {%$row};
91
}
removed reconnect method
yuki-kimoto authored on 2010-05-28
92
is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "$test : fetch_hash");
removed register_format()
yuki-kimoto authored on 2010-05-26
93

            
94
$result = $dbi->execute($query);
95
$rows = $result->fetch_all;
removed reconnect method
yuki-kimoto authored on 2010-05-28
96
is_deeply($rows, [[1, 2], [3, 4]], "$test : fetch_all");
removed register_format()
yuki-kimoto authored on 2010-05-26
97

            
98
$result = $dbi->execute($query);
removed reconnect method
yuki-kimoto authored on 2010-05-28
99
$rows = $result->fetch_hash_all;
100
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "$test : fetch_hash_all");
removed register_format()
yuki-kimoto authored on 2010-05-26
101

            
102
test 'Insert query return value';
103
$dbi->execute($DROP_TABLE->{0});
104
$dbi->execute($CREATE_TABLE->{0});
105
$tmpl = "insert into table1 {insert key1 key2}";
106
$query = $dbi->create_query($tmpl);
107
$ret_val = $dbi->execute($query, param => {key1 => 1, key2 => 2});
108
ok($ret_val, $test);
109

            
110

            
111
test 'Direct query';
112
$dbi->execute($DROP_TABLE->{0});
113
$dbi->execute($CREATE_TABLE->{0});
114
$insert_tmpl = "insert into table1 {insert key1 key2}";
115
$dbi->execute($insert_tmpl, param => {key1 => 1, key2 => 2});
116
$result = $dbi->execute($SELECT_TMPLS->{0});
117
$rows = $result->fetch_hash_all;
118
is_deeply($rows, [{key1 => 1, key2 => 2}], $test);
119

            
120
test 'Filter basic';
121
$dbi->execute($DROP_TABLE->{0});
122
$dbi->execute($CREATE_TABLE->{0});
123
$dbi->register_filter(twice       => sub { $_[0] * 2}, 
124
                    three_times => sub { $_[0] * 3});
125

            
126
$insert_tmpl  = "insert into table1 {insert key1 key2};";
127
$insert_query = $dbi->create_query($insert_tmpl);
128
$insert_query->filter({key1 => 'twice'});
129
$dbi->execute($insert_query, param => {key1 => 1, key2 => 2});
130
$result = $dbi->execute($SELECT_TMPLS->{0});
131
$rows = $result->filter({key2 => 'three_times'})->fetch_hash_all;
132
is_deeply($rows, [{key1 => 2, key2 => 6}], "$test : filter fetch_filter");
133
$dbi->execute($DROP_TABLE->{0});
134

            
135
test 'Filter in';
136
$dbi->execute($CREATE_TABLE->{0});
137
$insert_tmpl  = "insert into table1 {insert key1 key2};";
138
$insert_query = $dbi->create_query($insert_tmpl);
139
$dbi->execute($insert_query, param => {key1 => 2, key2 => 4});
140
$select_tmpl = "select * from table1 where {in table1.key1 2} and {in table1.key2 2}";
141
$select_query = $dbi->create_query($select_tmpl);
142
$select_query->filter({'table1.key1' => 'twice'});
143
$result = $dbi->execute($select_query, param => {'table1.key1' => [1,5], 'table1.key2' => [2,4]});
144
$rows = $result->fetch_hash_all;
145
is_deeply($rows, [{key1 => 2, key2 => 4}], "$test : filter");
146

            
147
test 'DBIx::Custom::SQLTemplate basic tag';
148
$dbi->execute($DROP_TABLE->{0});
149
$dbi->execute($CREATE_TABLE->{1});
150
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
151
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
152

            
153
$tmpl = "select * from table1 where {= key1} and {<> key2} and {< key3} and {> key4} and {>= key5};";
154
$query = $dbi->create_query($tmpl);
155
$result = $dbi->execute($query, param => {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});
156
$rows = $result->fetch_hash_all;
157
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic tag1");
158

            
159
$tmpl = "select * from table1 where {<= key1} and {like key2};";
160
$query = $dbi->create_query($tmpl);
161
$result = $dbi->execute($query, param => {key1 => 1, key2 => '%2%'});
162
$rows = $result->fetch_hash_all;
163
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic tag2");
164

            
165
test 'DIB::Custom::SQLTemplate in tag';
166
$dbi->execute($DROP_TABLE->{0});
167
$dbi->execute($CREATE_TABLE->{1});
168
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
169
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
170

            
171
$tmpl = "select * from table1 where {in key1 2};";
172
$query = $dbi->create_query($tmpl);
173
$result = $dbi->execute($query, param => {key1 => [9, 1]});
174
$rows = $result->fetch_hash_all;
175
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic");
176

            
177
test 'DBIx::Custom::SQLTemplate insert tag';
178
$dbi->execute("delete from table1");
179
$insert_tmpl = 'insert into table1 {insert key1 key2 key3 key4 key5}';
180
$dbi->execute($insert_tmpl, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
181

            
182
$result = $dbi->execute($SELECT_TMPLS->{0});
183
$rows = $result->fetch_hash_all;
184
is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "$test : basic");
185

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

            
192
$update_tmpl = 'update table1 {update key1 key2 key3 key4} where {= key5}';
193
$dbi->execute($update_tmpl, param => {key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5});
194

            
195
$result = $dbi->execute($SELECT_TMPLS->{0});
196
$rows = $result->fetch_hash_all;
197
is_deeply($rows, [{key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5},
198
                  {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}], "$test : basic");
199

            
200
test 'Error case';
201
eval {DBIx::Custom->connect(data_source => 'dbi:SQLit')};
202
ok($@, "$test : connect error");
203

            
204
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
205
eval{$dbi->create_query("{p }")};
206
ok($@, "$test : create_query invalid SQL template");
207

            
208
test 'insert';
209
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
210
$dbi->execute($CREATE_TABLE->{0});
211
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
212
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
213
$result = $dbi->execute($SELECT_TMPLS->{0});
214
$rows   = $result->fetch_hash_all;
215
is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "$test : basic");
216

            
217
$dbi->execute('delete from table1');
218
$dbi->register_filter(
219
    twice       => sub { $_[0] * 2 },
220
    three_times => sub { $_[0] * 3 }
221
);
222
$dbi->default_query_filter('twice');
223
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => 'three_times'});
224
$result = $dbi->execute($SELECT_TMPLS->{0});
225
$rows   = $result->fetch_hash_all;
226
is_deeply($rows, [{key1 => 3, key2 => 4}], "$test : filter");
227
$dbi->default_query_filter(undef);
228

            
229
$dbi->execute($DROP_TABLE->{0});
230
$dbi->execute($CREATE_TABLE->{0});
231
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, append => '   ');
232
$rows = $dbi->select(table => 'table1')->fetch_hash_all;
233
is_deeply($rows, [{key1 => 1, key2 => 2}], 'insert append');
234

            
235
test 'update';
236
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
237
$dbi->execute($CREATE_TABLE->{1});
238
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
239
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
240
$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1});
241
$result = $dbi->execute($SELECT_TMPLS->{0});
242
$rows   = $result->fetch_hash_all;
243
is_deeply($rows, [{key1 => 1, key2 => 11, key3 => 3, key4 => 4, key5 => 5},
244
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
245
                  "$test : basic");
246
                  
247
$dbi->execute("delete from table1");
248
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
249
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
250
$dbi->update(table => 'table1', param => {key2 => 12}, where => {key2 => 2, key3 => 3});
251
$result = $dbi->execute($SELECT_TMPLS->{0});
252
$rows   = $result->fetch_hash_all;
253
is_deeply($rows, [{key1 => 1, key2 => 12, key3 => 3, key4 => 4, key5 => 5},
254
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
255
                  "$test : update key same as search key");
256

            
257
$dbi->execute("delete from table1");
258
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
259
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
260
$dbi->register_filter(twice => sub { $_[0] * 2 });
261
$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1},
262
              filter => {key2 => 'twice'});
263
$result = $dbi->execute($SELECT_TMPLS->{0});
264
$rows   = $result->fetch_hash_all;
265
is_deeply($rows, [{key1 => 1, key2 => 22, key3 => 3, key4 => 4, key5 => 5},
266
                  {key1 => 6, key2 => 7,  key3 => 8, key4 => 9, key5 => 10}],
267
                  "$test : filter");
268

            
269

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

            
272
test 'update_all';
273
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
274
$dbi->execute($CREATE_TABLE->{1});
275
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});
276
$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});
277
$dbi->register_filter(twice => sub { $_[0] * 2 });
278
$dbi->update_all(table => 'table1', param => {key2 => 10}, filter => {key2 => 'twice'});
279
$result = $dbi->execute($SELECT_TMPLS->{0});
280
$rows   = $result->fetch_hash_all;
281
is_deeply($rows, [{key1 => 1, key2 => 20, key3 => 3, key4 => 4, key5 => 5},
282
                  {key1 => 6, key2 => 20, key3 => 8, key4 => 9, key5 => 10}],
283
                  "$test : filter");
284

            
285

            
286
test 'delete';
287
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
288
$dbi->execute($CREATE_TABLE->{0});
289
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
290
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
291
$dbi->delete(table => 'table1', where => {key1 => 1});
292
$result = $dbi->execute($SELECT_TMPLS->{0});
293
$rows   = $result->fetch_hash_all;
294
is_deeply($rows, [{key1 => 3, key2 => 4}], "$test : basic");
295

            
296
$dbi->execute("delete from table1;");
297
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
298
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
299
$dbi->register_filter(twice => sub { $_[0] * 2 });
300
$dbi->delete(table => 'table1', where => {key2 => 1}, filter => {key2 => 'twice'});
301
$result = $dbi->execute($SELECT_TMPLS->{0});
302
$rows   = $result->fetch_hash_all;
303
is_deeply($rows, [{key1 => 3, key2 => 4}], "$test : filter");
304

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

            
307
$dbi->delete_all(table => 'table1');
308
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
309
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
310
$dbi->delete(table => 'table1', where => {key1 => 1, key2 => 2});
311
$rows = $dbi->select(table => 'table1')->fetch_hash_all;
312
is_deeply($rows, [{key1 => 3, key2 => 4}], "$test : delete multi key");
313

            
314
test 'delete error';
315
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
316
$dbi->execute($CREATE_TABLE->{0});
317
eval{$dbi->delete(table => 'table1')};
318
like($@, qr/Key-value pairs for where clause must be specified to 'delete' second argument/,
319
         "$test : where key-value pairs not specified");
320

            
321
test 'delete_all';
322
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
323
$dbi->execute($CREATE_TABLE->{0});
324
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
325
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
326
$dbi->delete_all(table => 'table1');
327
$result = $dbi->execute($SELECT_TMPLS->{0});
328
$rows   = $result->fetch_hash_all;
329
is_deeply($rows, [], "$test : basic");
330

            
331

            
332
test 'select';
333
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
334
$dbi->execute($CREATE_TABLE->{0});
335
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
336
$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});
337
$rows = $dbi->select(table => 'table1')->fetch_hash_all;
338
is_deeply($rows, [{key1 => 1, key2 => 2},
339
                  {key1 => 3, key2 => 4}], "$test : table");
340

            
update document
yuki-kimoto authored on 2010-05-27
341
$rows = $dbi->select(table => 'table1', column => ['key1'])->fetch_hash_all;
removed register_format()
yuki-kimoto authored on 2010-05-26
342
is_deeply($rows, [{key1 => 1}, {key1 => 3}], "$test : table and columns and where key");
343

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

            
update document
yuki-kimoto authored on 2010-05-27
347
$rows = $dbi->select(table => 'table1', column => ['key1'], where => {key1 => 3})->fetch_hash_all;
removed register_format()
yuki-kimoto authored on 2010-05-26
348
is_deeply($rows, [{key1 => 3}], "$test : table and columns and where key");
349

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

            
353
$dbi->register_filter(decrement => sub { $_[0] - 1 });
update document
yuki-kimoto authored on 2010-05-27
354
$rows = $dbi->select(table => 'table1', where => {key1 => 2}, filter => {key1 => 'decrement'})
removed register_format()
yuki-kimoto authored on 2010-05-26
355
            ->fetch_hash_all;
356
is_deeply($rows, [{key1 => 1, key2 => 2}], "$test : filter");
357

            
358
$dbi->execute($CREATE_TABLE->{2});
359
$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});
360
$rows = $dbi->select(
361
    table => [qw/table1 table2/],
update document
yuki-kimoto authored on 2010-05-27
362
    column => ['table1.key1 as table1_key1', 'table2.key1 as table2_key1', 'key2', 'key3'],
removed register_format()
yuki-kimoto authored on 2010-05-26
363
    where   => {'table1.key2' => 2},
added commit method
yuki-kimoto authored on 2010-05-27
364
    relation  => {'table1.key1' => 'table2.key1'}
removed register_format()
yuki-kimoto authored on 2010-05-26
365
)->fetch_hash_all;
added commit method
yuki-kimoto authored on 2010-05-27
366
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "$test : relation : exists where");
367

            
368
$rows = $dbi->select(
369
    table => [qw/table1 table2/],
370
    column => ['table1.key1 as table1_key1', 'table2.key1 as table2_key1', 'key2', 'key3'],
371
    relation  => {'table1.key1' => 'table2.key1'}
372
)->fetch_hash_all;
373
is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "$test : relation : no exists where");
removed register_format()
yuki-kimoto authored on 2010-05-26
374

            
375
test 'fetch filter';
376
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
377
$dbi->register_filter(
378
    twice       => sub { $_[0] * 2 },
379
    three_times => sub { $_[0] * 3 }
380
);
381
$dbi->default_fetch_filter('twice');
382
$dbi->execute($CREATE_TABLE->{0});
383
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
384
$result = $dbi->select(table => 'table1');
385
$result->filter({key1 => 'three_times'});
removed reconnect method
yuki-kimoto authored on 2010-05-28
386
$row = $result->fetch_hash_first;
removed register_format()
yuki-kimoto authored on 2010-05-26
387
is_deeply($row, {key1 => 3, key2 => 4}, "$test: default_fetch_filter and filter");
388

            
389
test 'filters';
390
$dbi = DBIx::Custom->new;
391

            
update document
yuki-kimoto authored on 2010-05-27
392
is($dbi->filters->{decode_utf8}->(encode_utf8('あ')),
393
   'あ', "$test : decode_utf8");
removed register_format()
yuki-kimoto authored on 2010-05-26
394

            
395
is($dbi->filters->{encode_utf8}->('あ'),
396
   encode_utf8('あ'), "$test : encode_utf8");
397

            
added commit method
yuki-kimoto authored on 2010-05-27
398
test 'transaction';
399
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
400
$dbi->execute($CREATE_TABLE->{0});
401
$dbi->auto_commit(0);
402
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
403
$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});
404
$dbi->commit;
405
$result = $dbi->select(table => 'table1');
406
is_deeply(scalar $result->fetch_hash_all, [{key1 => 1, key2 => 2}, {key1 => 2, key2 => 3}],
407
          "$test : commit");
408

            
409
$dbi = DBIx::Custom->connect($NEW_ARGS->{0});
410
$dbi->execute($CREATE_TABLE->{0});
411
$dbi->auto_commit(0);
412
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
413
$dbi->rollback;
414

            
415
$result = $dbi->select(table => 'table1');
removed reconnect method
yuki-kimoto authored on 2010-05-28
416
ok(! $result->fetch_first, "$test: rollback");