DBIx-Custom / t / sqlite.t /
Newer Older
218 lines | 7.31kb
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;
cleanup test
Yuki Kimoto authored on 2011-08-10
7
use lib "$FindBin::Bin/common";
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

            
test cleanup
Yuki Kimoto authored on 2011-08-10
22
use DBIx::Custom;
test cleanup
Yuki Kimoto authored on 2011-08-10
23
{
24
    package DBIx::Custom;
25
    has dsn => sub { 'dbi:SQLite:dbname=:memory:' }
26
}
test cleanup
Yuki Kimoto authored on 2011-08-10
27

            
cleanup test
Yuki Kimoto authored on 2011-08-06
28
# Constant
cleanup test
Yuki Kimoto authored on 2011-08-10
29
my $create_table1 = 'create table table1 (key1 varchar, key2 varchar);';
30
my $create_table_reserved = 'create table "table" ("select" varchar, "update" varchar)';
test cleanup
Yuki Kimoto authored on 2011-08-10
31
my $q = '"';
32
my $p = '"';
cleanup test
Yuki Kimoto authored on 2011-08-06
33

            
cleanup test
Yuki Kimoto authored on 2011-08-06
34
# Variables
35
my $dbi;
36
my $result;
37
my $row;
38
my $rows;
cleanup test
Yuki Kimoto authored on 2011-08-10
39
my $binary;
cleanup test
Yuki Kimoto authored on 2011-08-06
40

            
41
# Prepare table
test cleanup
Yuki Kimoto authored on 2011-08-10
42
$dbi = DBIx::Custom->connect;
cleanup test
Yuki Kimoto authored on 2011-08-06
43

            
test cleanup
Yuki Kimoto authored on 2011-08-10
44
### SQLite only test
45
test 'prefix';
46
$dbi = DBIx::Custom->connect;
47
eval { $dbi->execute('drop table table1') };
48
$dbi->execute('create table table1 (key1 varchar, key2 varchar, primary key(key1));');
49
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
50
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 4}, prefix => 'or replace');
51
$result = $dbi->execute('select * from table1;');
52
$rows   = $result->all;
53
is_deeply($rows, [{key1 => 1, key2 => 4}], "basic");
test cleanup
Yuki Kimoto authored on 2011-08-10
54

            
test cleanup
Yuki Kimoto authored on 2011-08-10
55
$dbi = DBIx::Custom->connect;
56
eval { $dbi->execute('drop table table1') };
57
$dbi->execute('create table table1 (key1 varchar, key2 varchar, primary key(key1));');
58
$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2});
59
$dbi->update(table => 'table1', param => {key2 => 4},
60
  where => {key1 => 1}, prefix => 'or replace');
61
$result = $dbi->execute('select * from table1;');
62
$rows   = $result->all;
63
is_deeply($rows, [{key1 => 1, key2 => 4}], "basic");
test cleanup
Yuki Kimoto authored on 2011-08-10
64

            
65

            
test cleanup
Yuki Kimoto authored on 2011-08-10
66
test 'quote';
67
$dbi = DBIx::Custom->connect;
68
$dbi->quote('"');
69
eval { $dbi->execute("drop table ${q}table$p") };
70
$dbi->execute($create_table_reserved);
71
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
72
$dbi->insert(table => 'table', param => {select => 1});
73
$dbi->delete(table => 'table', where => {select => 1});
74
$result = $dbi->execute("select * from ${q}table$p");
75
$rows   = $result->all;
76
is_deeply($rows, [], "reserved word");
77

            
test cleanup
Yuki Kimoto authored on 2011-08-10
78
test 'finish statement handle';
79
$dbi = DBIx::Custom->connect;
80
$dbi->execute($create_table1);
81
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
82
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
83

            
84
$result = $dbi->select(table => 'table1');
85
$row = $result->fetch_first;
86
is_deeply($row, [1, 2], "row");
87
$row = $result->fetch;
88
ok(!$row, "finished");
89

            
90
$result = $dbi->select(table => 'table1');
91
$row = $result->fetch_hash_first;
92
is_deeply($row, {key1 => 1, key2 => 2}, "row");
93
$row = $result->fetch_hash;
94
ok(!$row, "finished");
95

            
96
$dbi->execute('create table table2 (key1, key2);');
97
$result = $dbi->select(table => 'table2');
98
$row = $result->fetch_hash_first;
99
ok(!$row, "no row fetch");
100

            
101
$dbi = DBIx::Custom->connect;
102
eval { $dbi->execute('drop table table1') };
103
$dbi->execute($create_table1);
104
$dbi->insert({key1 => 1, key2 => 2}, table => 'table1');
105
$dbi->insert({key1 => 3, key2 => 4}, table => 'table1');
106
$dbi->insert({key1 => 5, key2 => 6}, table => 'table1');
107
$dbi->insert({key1 => 7, key2 => 8}, table => 'table1');
108
$dbi->insert({key1 => 9, key2 => 10}, table => 'table1');
109
$result = $dbi->select(table => 'table1');
110
$rows = $result->fetch_multi(2);
111
is_deeply($rows, [[1, 2],
112
                  [3, 4]], "fetch_multi first");
113
$rows = $result->fetch_multi(2);
114
is_deeply($rows, [[5, 6],
115
                  [7, 8]], "fetch_multi secound");
116
$rows = $result->fetch_multi(2);
117
is_deeply($rows, [[9, 10]], "fetch_multi third");
118
$rows = $result->fetch_multi(2);
119
ok(!$rows);
120

            
121
$result = $dbi->select(table => 'table1');
122
eval {$result->fetch_multi};
123
like($@, qr/Row count must be specified/, "Not specified row count");
124

            
125
$result = $dbi->select(table => 'table1');
126
$rows = $result->fetch_hash_multi(2);
127
is_deeply($rows, [{key1 => 1, key2 => 2},
128
                  {key1 => 3, key2 => 4}], "fetch_multi first");
129
$rows = $result->fetch_hash_multi(2);
130
is_deeply($rows, [{key1 => 5, key2 => 6},
131
                  {key1 => 7, key2 => 8}], "fetch_multi secound");
132
$rows = $result->fetch_hash_multi(2);
133
is_deeply($rows, [{key1 => 9, key2 => 10}], "fetch_multi third");
134
$rows = $result->fetch_hash_multi(2);
135
ok(!$rows);
136

            
137
$result = $dbi->select(table => 'table1');
138
eval {$result->fetch_hash_multi};
139
like($@, qr/Row count must be specified/, "Not specified row count");
140

            
test cleanup
Yuki Kimoto authored on 2011-08-10
141

            
cleanup test
Yuki Kimoto authored on 2011-08-10
142
test 'type option'; # DEPRECATED!
143
$dbi = DBIx::Custom->connect(
144
    data_source => 'dbi:SQLite:dbname=:memory:',
145
    dbi_option => {
146
        $DBD::SQLite::VERSION > 1.26 ? (sqlite_unicode => 1) : (unicode => 1)
147
    }
148
);
149
$binary = pack("I3", 1, 2, 3);
150
eval { $dbi->execute('drop table table1') };
151
$dbi->execute('create table table1(key1, key2)');
152
$dbi->insert(table => 'table1', param => {key1 => $binary, key2 => 'あ'}, type => [key1 => DBI::SQL_BLOB]);
153
$result = $dbi->select(table => 'table1');
154
$row   = $result->one;
155
is_deeply($row, {key1 => $binary, key2 => 'あ'}, "basic");
156
$result = $dbi->execute('select length(key1) as key1_length from table1');
157
$row = $result->one;
158
is($row->{key1_length}, length $binary);
159

            
160
test 'type_rule from';
161
$dbi = DBIx::Custom->connect;
162
$dbi->type_rule(
163
    from1 => {
164
        date => sub { uc $_[0] }
165
    }
166
);
167
$dbi->execute("create table table1 (key1 Date, key2 datetime)");
168
$dbi->insert({key1 => 'a'}, table => 'table1');
169
$result = $dbi->select(table => 'table1');
170
is($result->fetch_first->[0], 'A');
171

            
172
$result = $dbi->select(table => 'table1');
173
is($result->one->{key1}, 'A');
174

            
test cleanup
Yuki Kimoto authored on 2011-08-10
175
# DEPRECATED! test
176
test 'filter __ expression';
177
$dbi = DBIx::Custom->connect;
cleanup test
Yuki Kimoto authored on 2011-08-10
178
eval { $dbi->execute('drop table table2') };
179
eval { $dbi->execute('drop table table3') };
180
$dbi->execute('create table table2 (id, name, table3_id)');
181
$dbi->execute('create table table3 (id, name)');
182
$dbi->apply_filter('table3',
test cleanup
Yuki Kimoto authored on 2011-08-10
183
  name => {in => sub { uc $_[0] } }
184
);
185

            
cleanup test
Yuki Kimoto authored on 2011-08-10
186
$dbi->insert(table => 'table2', param => {id => 1, name => 'a', table3_id => 2});
187
$dbi->insert(table => 'table3', param => {id => 2, name => 'b'});
test cleanup
Yuki Kimoto authored on 2011-08-10
188

            
189
$result = $dbi->select(
cleanup test
Yuki Kimoto authored on 2011-08-10
190
    table => ['table2', 'table3'], relation => {'table2.table3_id' => 'table3.id'},
191
    column => ['table3.name as table3__name']
test cleanup
Yuki Kimoto authored on 2011-08-10
192
);
193
is($result->fetch_first->[0], 'B');
194

            
195
$result = $dbi->select(
cleanup test
Yuki Kimoto authored on 2011-08-10
196
    table => 'table2', relation => {'table2.table3_id' => 'table3.id'},
197
    column => ['table3.name as table3__name']
test cleanup
Yuki Kimoto authored on 2011-08-10
198
);
199
is($result->fetch_first->[0], 'B');
200

            
201
$result = $dbi->select(
cleanup test
Yuki Kimoto authored on 2011-08-10
202
    table => 'table2', relation => {'table2.table3_id' => 'table3.id'},
203
    column => ['table3.name as "table3.name"']
test cleanup
Yuki Kimoto authored on 2011-08-10
204
);
205
is($result->fetch_first->[0], 'B');
test cleanup
Yuki Kimoto authored on 2011-08-10
206

            
207
test 'reserved_word_quote';
208
$dbi = DBIx::Custom->connect;
209
eval { $dbi->execute("drop table ${q}table$p") };
210
$dbi->reserved_word_quote('"');
211
$dbi->execute($create_table_reserved);
212
$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});
213
$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});
214
$dbi->insert(table => 'table', param => {select => 1});
215
$dbi->update(table => 'table', where => {'table.select' => 1}, param => {update => 2});
216
$result = $dbi->execute("select * from ${q}table$p");
217
$rows   = $result->all;
218
is_deeply($rows, [{select => 2, update => 6}], "reserved word");