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
|
use strict;
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",
user => '',
password => '',
connect_info_opts => {
on_connect_do => [ 'PRAGMA foreign_keys = ON', 'PRAGMA synchronous = OFF', ]
},
loader_options => { preserve_case => 1 },
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)
)
},
],
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/ ],
count => 11,
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';
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};
},
},
);
$tester->run_tests();
END {
unlink "$tdir/sqlite_test";
}
|
