1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
|
use strict;
use warnings;
use Test::More;
use lib qw(t/lib);
use dbixcsl_common_tests;
use dbixcsl_test_dir qw/$tdir/;
eval { require DBD::SQLite };
my $class = $@ ? 'SQLite2' : 'SQLite';
my $tester = dbixcsl_common_tests->new(
vendor => 'SQLite',
auto_inc_pk => 'INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT',
dsn => "dbi:$class:dbname=$tdir/sqlite_test.db",
user => '',
password => '',
connect_info_opts => {
on_connect_do => [ 'PRAGMA foreign_keys = ON', 'PRAGMA synchronous = OFF', ]
},
loader_options => { preserve_case => 1 },
default_is_deferrable => 0,
default_on_clause => 'NO ACTION',
data_types => {
# SQLite ignores data types aside from INTEGER pks.
# We just test that they roundtrip sanely.
#
# Numeric types
'smallint' => { data_type => 'smallint' },
'int' => { data_type => 'int' },
'integer' => { data_type => 'integer' },
# test that type name is lowercased
'INTEGER' => { data_type => 'integer' },
'bigint' => { data_type => 'bigint' },
'float' => { data_type => 'float' },
'double precision' =>
{ data_type => 'double precision' },
'real' => { data_type => 'real' },
'float(2)' => { data_type => 'float', size => 2 },
'float(7)' => { data_type => 'float', size => 7 },
'decimal' => { data_type => 'decimal' },
'dec' => { data_type => 'dec' },
'numeric' => { data_type => 'numeric' },
'decimal(3)' => { data_type => 'decimal', size => 3 },
'numeric(3)' => { data_type => 'numeric', size => 3 },
'decimal(3,3)' => { data_type => 'decimal', size => [3,3] },
'dec(3,3)' => { data_type => 'dec', size => [3,3] },
'numeric(3,3)' => { data_type => 'numeric', size => [3,3] },
# Date and Time Types
'date' => { data_type => 'date' },
'timestamp DEFAULT CURRENT_TIMESTAMP'
=> { data_type => 'timestamp', default_value => \'current_timestamp' },
'time' => { data_type => 'time' },
# String Types
'char' => { data_type => 'char' },
'char(11)' => { data_type => 'char', size => 11 },
'varchar(20)' => { data_type => 'varchar', size => 20 },
},
extra => {
create => [
# 'sqlite_' is reserved, so we use 'extra_'
q{
CREATE TABLE "extra_loader_test1" (
"id" NOT NULL PRIMARY KEY,
"value" TEXT UNIQUE NOT NULL
)
},
q{
CREATE TABLE extra_loader_test2 (
event_id INTEGER PRIMARY KEY
)
},
q{
CREATE TABLE extra_loader_test3 (
person_id INTEGER PRIMARY KEY
)
},
# Wordy, newline-heavy SQL
q{
CREATE TABLE extra_loader_test4 (
event_id INTEGER NOT NULL
CONSTRAINT fk_event_id
REFERENCES extra_loader_test2(event_id),
person_id INTEGER NOT NULL
CONSTRAINT fk_person_id
REFERENCES extra_loader_test3 (person_id),
PRIMARY KEY (event_id, person_id)
)
},
# make sure views are picked up
q{
CREATE VIEW extra_loader_test5 AS SELECT * FROM extra_loader_test4
},
# Compound primary keys can't be autoinc in the DBIC sense
q{
CREATE TABLE extra_loader_test6 (
id1 INTEGER,
id2 INTEGER,
value INTEGER,
PRIMARY KEY (id1, id2)
)
},
q{
CREATE TABLE extra_loader_test7 (
id1 INTEGER,
id2 TEXT,
value DECIMAL,
PRIMARY KEY (id1, id2)
)
},
q{
create table extra_loader_test8 (
id integer primary key
)
},
q{
create table extra_loader_test9 (
id integer primary key,
eight_id int,
foreign key (eight_id) references extra_loader_test8(id)
on delete restrict on update set null deferrable
)
},
# test inline constraint
q{
create table extra_loader_test10 (
id integer primary key,
eight_id int references extra_loader_test8(id) on delete restrict on update set null deferrable
)
},
],
pre_drop_ddl => [ 'DROP VIEW extra_loader_test5' ],
drop => [ qw/extra_loader_test1 extra_loader_test2 extra_loader_test3
extra_loader_test4 extra_loader_test6 extra_loader_test7
extra_loader_test8 extra_loader_test9 extra_loader_test10 / ],
count => 20,
run => sub {
my ($schema, $monikers, $classes) = @_;
ok ((my $rs = $schema->resultset($monikers->{extra_loader_test1})),
'resultset for quoted table');
ok ((my $source = $rs->result_source), 'source');
is_deeply [ $source->columns ], [ qw/id value/ ],
'retrieved quoted column names from quoted table';
ok ((exists $source->column_info('value')->{is_nullable}),
'is_nullable exists');
is $source->column_info('value')->{is_nullable}, 0,
'is_nullable is set correctly';
ok (($source = $schema->source($monikers->{extra_loader_test4})),
'verbose table');
is_deeply [ $source->primary_columns ], [ qw/event_id person_id/ ],
'composite primary key';
is ($source->relationships, 2,
'2 foreign key constraints found');
# test that columns for views are picked up
is $schema->resultset($monikers->{extra_loader_test5})->result_source->column_info('person_id')->{data_type}, 'integer',
'columns for views are introspected';
# test that views are marked as such
isa_ok $schema->resultset($monikers->{extra_loader_test5})->result_source, 'DBIx::Class::ResultSource::View',
'view result source';
isnt $schema->resultset($monikers->{extra_loader_test6})->result_source->column_info('id1')->{is_auto_increment}, 1,
q{two integer PKs don't get marked autoinc};
isnt $schema->resultset($monikers->{extra_loader_test7})->result_source->column_info('id1')->{is_auto_increment}, 1,
q{composite integer PK with non-integer PK doesn't get marked autoinc};
# test on delete/update fk clause introspection
ok ((my $rel_info = $schema->source('ExtraLoaderTest9')->relationship_info('eight')),
'got rel info');
is $rel_info->{attrs}{on_delete}, 'RESTRICT',
'ON DELETE clause introspected correctly';
is $rel_info->{attrs}{on_update}, 'SET NULL',
'ON UPDATE clause introspected correctly';
is $rel_info->{attrs}{is_deferrable}, 1,
'DEFERRABLE clause introspected correctly';
ok (($rel_info = $schema->source('ExtraLoaderTest10')->relationship_info('eight')),
'got rel info');
is $rel_info->{attrs}{on_delete}, 'RESTRICT',
'ON DELETE clause introspected correctly for inline FK';
is $rel_info->{attrs}{on_update}, 'SET NULL',
'ON UPDATE clause introspected correctly for inline FK';
is $rel_info->{attrs}{is_deferrable}, 1,
'DEFERRABLE clause introspected correctly for inline FK';
},
},
);
$tester->run_tests();
END {
unlink "$tdir/sqlite_test.db" unless $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP};
}
|
