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