1use strict; 2use warnings; 3use Test::More; 4use Test::Exception; 5use Try::Tiny; 6use File::Path 'rmtree'; 7use DBIx::Class::Optional::Dependencies; 8use DBIx::Class::Schema::Loader 'make_schema_at'; 9use Scope::Guard (); 10 11use lib qw(t/lib); 12 13use dbixcsl_common_tests; 14use dbixcsl_test_dir '$tdir'; 15 16use constant EXTRA_DUMP_DIR => "$tdir/sqlanywhere_extra_dump"; 17 18# The default max_cursor_count and max_statement_count settings of 50 are too 19# low to run this test. 20# 21# Setting them to zero is preferred. 22 23my %dsns; 24for (qw(SQLANYWHERE SQLANYWHERE_ODBC)) { 25 next unless $ENV{"DBICTEST_${_}_DSN"}; 26 27 my $dep_group = lc "rdbms_$_"; 28 if (!DBIx::Class::Optional::Dependencies->req_ok_for($dep_group)) { 29 diag 'You need to install ' . DBIx::Class::Optional::Dependencies->req_missing_for($dep_group) 30 . " to test with $_"; 31 next; 32 } 33 34 $dsns{$_}{dsn} = $ENV{"DBICTEST_${_}_DSN"}; 35 $dsns{$_}{user} = $ENV{"DBICTEST_${_}_USER"}; 36 $dsns{$_}{password} = $ENV{"DBICTEST_${_}_PASS"}; 37}; 38 39plan skip_all => 'You need to set the DBICTEST_SQLANYWHERE_DSN, _USER and _PASS and/or the DBICTEST_SQLANYWHERE_ODBC_DSN, _USER and _PASS environment variables' 40 unless %dsns; 41 42my ($schema, $schemas_created); # for cleanup in END for extra tests 43 44my $tester = dbixcsl_common_tests->new( 45 vendor => 'SQLAnywhere', 46 auto_inc_pk => 'INTEGER IDENTITY NOT NULL PRIMARY KEY', 47 connect_info => [ map { $dsns{$_} } sort keys %dsns ], 48 loader_options => { preserve_case => 1 }, 49 default_is_deferrable => 1, 50 default_on_clause => 'RESTRICT', 51 data_types => { 52 # http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.11.0.1/dbreference_en11/rf-datatypes.html 53 # 54 # Numeric types 55 'bit' => { data_type => 'bit' }, 56 'tinyint' => { data_type => 'tinyint' }, 57 'smallint' => { data_type => 'smallint' }, 58 'int' => { data_type => 'integer' }, 59 'integer' => { data_type => 'integer' }, 60 'bigint' => { data_type => 'bigint' }, 61 'float' => { data_type => 'real' }, 62 'real' => { data_type => 'real' }, 63 'double' => { data_type => 'double precision' }, 64 'double precision' => 65 { data_type => 'double precision' }, 66 67 'float(2)' => { data_type => 'real' }, 68 'float(24)' => { data_type => 'real' }, 69 'float(25)' => { data_type => 'double precision' }, 70 'float(53)' => { data_type => 'double precision' }, 71 72 # This test only works with the default precision and scale options. 73 # 74 # They are preserved even for the default values, because the defaults 75 # can be changed. 76 'decimal' => { data_type => 'decimal', size => [30,6] }, 77 'dec' => { data_type => 'decimal', size => [30,6] }, 78 'numeric' => { data_type => 'numeric', size => [30,6] }, 79 80 'decimal(3)' => { data_type => 'decimal', size => [3,0] }, 81 'dec(3)' => { data_type => 'decimal', size => [3,0] }, 82 'numeric(3)' => { data_type => 'numeric', size => [3,0] }, 83 84 'decimal(3,3)' => { data_type => 'decimal', size => [3,3] }, 85 'dec(3,3)' => { data_type => 'decimal', size => [3,3] }, 86 'numeric(3,3)' => { data_type => 'numeric', size => [3,3] }, 87 88 'decimal(18,18)' => { data_type => 'decimal', size => [18,18] }, 89 'dec(18,18)' => { data_type => 'decimal', size => [18,18] }, 90 'numeric(18,18)' => { data_type => 'numeric', size => [18,18] }, 91 92 # money types 93 'money' => { data_type => 'money' }, 94 'smallmoney' => { data_type => 'smallmoney' }, 95 96 # bit arrays 97 'long varbit' => { data_type => 'long varbit' }, 98 'long bit varying' 99 => { data_type => 'long varbit' }, 100 'varbit' => { data_type => 'varbit', size => 1 }, 101 'varbit(20)' => { data_type => 'varbit', size => 20 }, 102 'bit varying' => { data_type => 'varbit', size => 1 }, 103 'bit varying(20)' 104 => { data_type => 'varbit', size => 20 }, 105 106 # Date and Time Types 107 'date' => { data_type => 'date' }, 108 'datetime' => { data_type => 'datetime' }, 109 'smalldatetime' 110 => { data_type => 'smalldatetime' }, 111 'timestamp' => { data_type => 'timestamp' }, 112 # rewrite 'current timestamp' as 'current_timestamp' 113 'timestamp default current timestamp' 114 => { data_type => 'timestamp', default_value => \'current_timestamp', 115 original => { default_value => \'current timestamp' } }, 116 'time' => { data_type => 'time' }, 117 118 # String Types 119 'char' => { data_type => 'char', size => 1 }, 120 'char(11)' => { data_type => 'char', size => 11 }, 121 'nchar' => { data_type => 'nchar', size => 1 }, 122 'nchar(11)' => { data_type => 'nchar', size => 11 }, 123 'varchar' => { data_type => 'varchar', size => 1 }, 124 'varchar(20)' => { data_type => 'varchar', size => 20 }, 125 'char varying(20)' 126 => { data_type => 'varchar', size => 20 }, 127 'character varying(20)' 128 => { data_type => 'varchar', size => 20 }, 129 'nvarchar(20)' => { data_type => 'nvarchar', size => 20 }, 130 'xml' => { data_type => 'xml' }, 131 'uniqueidentifierstr' 132 => { data_type => 'uniqueidentifierstr' }, 133 134 # Binary types 135 'binary' => { data_type => 'binary', size => 1 }, 136 'binary(20)' => { data_type => 'binary', size => 20 }, 137 'varbinary' => { data_type => 'varbinary', size => 1 }, 138 'varbinary(20)'=> { data_type => 'varbinary', size => 20 }, 139 'uniqueidentifier' 140 => { data_type => 'uniqueidentifier' }, 141 142 # Blob types 143 'long binary' => { data_type => 'long binary' }, 144 'image' => { data_type => 'image' }, 145 'long varchar' => { data_type => 'long varchar' }, 146 'text' => { data_type => 'text' }, 147 'long nvarchar'=> { data_type => 'long nvarchar' }, 148 'ntext' => { data_type => 'ntext' }, 149 }, 150 extra => { 151 create => [ 152 # 4 through 8 are used for the multi-schema tests 153 q{ 154 create table sqlanywhere_loader_test9 ( 155 id int identity not null primary key 156 ) 157 }, 158 q{ 159 create table sqlanywhere_loader_test10 ( 160 id int identity not null primary key, 161 nine_id int, 162 foreign key (nine_id) references sqlanywhere_loader_test9(id) 163 on delete cascade on update set null 164 ) 165 }, 166 ], 167 drop => [ qw/sqlanywhere_loader_test9 sqlanywhere_loader_test10/ ], 168 count => 4 + 30 * 2, 169 run => sub { 170 SKIP: { 171 $schema = $_[0]; 172 my $self = $_[3]; 173 174 # test on delete/update fk clause introspection 175 ok ((my $rel_info = $schema->source('SqlanywhereLoaderTest10')->relationship_info('nine')), 176 'got rel info'); 177 178 is $rel_info->{attrs}{on_delete}, 'CASCADE', 179 'ON DELETE clause introspected correctly'; 180 181 is $rel_info->{attrs}{on_update}, 'SET NULL', 182 'ON UPDATE clause introspected correctly'; 183 184 is $rel_info->{attrs}{is_deferrable}, 1, 185 'is_deferrable defaults to 1'; 186 187 my $connect_info = [@$self{qw/dsn user password/}]; 188 189 my $dbh = $schema->storage->dbh; 190 191 try { 192 $dbh->do("CREATE USER dbicsl_test1 identified by 'dbicsl'"); 193 } 194 catch { 195 $schemas_created = 0; 196 skip "no CREATE USER privileges", 30 * 2; 197 }; 198 199 $dbh->do(<<"EOF"); 200 CREATE TABLE dbicsl_test1.sqlanywhere_loader_test4 ( 201 id INT IDENTITY NOT NULL PRIMARY KEY, 202 value VARCHAR(100) 203 ) 204EOF 205 $dbh->do(<<"EOF"); 206 CREATE TABLE dbicsl_test1.sqlanywhere_loader_test5 ( 207 id INT IDENTITY NOT NULL PRIMARY KEY, 208 value VARCHAR(100), 209 four_id INTEGER NOT NULL, 210 CONSTRAINT loader_test5_uniq UNIQUE (four_id), 211 FOREIGN KEY (four_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id) 212 ) 213EOF 214 $dbh->do("CREATE USER dbicsl_test2 identified by 'dbicsl'"); 215 $dbh->do(<<"EOF"); 216 CREATE TABLE dbicsl_test2.sqlanywhere_loader_test5 ( 217 pk INT IDENTITY NOT NULL PRIMARY KEY, 218 value VARCHAR(100), 219 four_id INTEGER NOT NULL, 220 CONSTRAINT loader_test5_uniq UNIQUE (four_id), 221 FOREIGN KEY (four_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id) 222 ) 223EOF 224 $dbh->do(<<"EOF"); 225 CREATE TABLE dbicsl_test2.sqlanywhere_loader_test6 ( 226 id INT IDENTITY NOT NULL PRIMARY KEY, 227 value VARCHAR(100), 228 sqlanywhere_loader_test4_id INTEGER, 229 FOREIGN KEY (sqlanywhere_loader_test4_id) REFERENCES dbicsl_test1.sqlanywhere_loader_test4 (id) 230 ) 231EOF 232 $dbh->do(<<"EOF"); 233 CREATE TABLE dbicsl_test2.sqlanywhere_loader_test7 ( 234 id INT IDENTITY NOT NULL PRIMARY KEY, 235 value VARCHAR(100), 236 six_id INTEGER NOT NULL UNIQUE, 237 FOREIGN KEY (six_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test6 (id) 238 ) 239EOF 240 $dbh->do(<<"EOF"); 241 CREATE TABLE dbicsl_test1.sqlanywhere_loader_test8 ( 242 id INT IDENTITY NOT NULL PRIMARY KEY, 243 value VARCHAR(100), 244 sqlanywhere_loader_test7_id INTEGER, 245 FOREIGN KEY (sqlanywhere_loader_test7_id) REFERENCES dbicsl_test2.sqlanywhere_loader_test7 (id) 246 ) 247EOF 248 249 $schemas_created = 1; 250 251 my $guard = Scope::Guard->new(\&extra_cleanup); 252 253 foreach my $db_schema (['dbicsl_test1', 'dbicsl_test2'], '%') { 254 lives_and { 255 rmtree EXTRA_DUMP_DIR; 256 257 my @warns; 258 local $SIG{__WARN__} = sub { 259 push @warns, $_[0] unless $_[0] =~ /\bcollides\b/; 260 }; 261 262 make_schema_at( 263 'SQLAnywhereMultiSchema', 264 { 265 naming => 'current', 266 db_schema => $db_schema, 267 dump_directory => EXTRA_DUMP_DIR, 268 quiet => 1, 269 }, 270 $connect_info, 271 ); 272 273 diag join "\n", @warns if @warns; 274 275 is @warns, 0; 276 } 'dumped schema for dbicsl_test1 and dbicsl_test2 schemas with no warnings'; 277 278 my ($test_schema, $rsrc, $rs, $row, %uniqs, $rel_info); 279 280 lives_and { 281 ok $test_schema = SQLAnywhereMultiSchema->connect(@$connect_info); 282 } 'connected test schema'; 283 284 lives_and { 285 ok $rsrc = $test_schema->source('SqlanywhereLoaderTest4'); 286 } 'got source for table in schema one'; 287 288 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1, 289 'column in schema one'; 290 291 is try { $rsrc->column_info('value')->{data_type} }, 'varchar', 292 'column in schema one'; 293 294 is try { $rsrc->column_info('value')->{size} }, 100, 295 'column in schema one'; 296 297 lives_and { 298 ok $rs = $test_schema->resultset('SqlanywhereLoaderTest4'); 299 } 'got resultset for table in schema one'; 300 301 lives_and { 302 ok $row = $rs->create({ value => 'foo' }); 303 } 'executed SQL on table in schema one'; 304 305 $rel_info = try { $rsrc->relationship_info('dbicsl_test1_sqlanywhere_loader_test5') }; 306 307 is_deeply $rel_info->{cond}, { 308 'foreign.four_id' => 'self.id' 309 }, 'relationship in schema one'; 310 311 is $rel_info->{attrs}{accessor}, 'single', 312 'relationship in schema one'; 313 314 is $rel_info->{attrs}{join_type}, 'LEFT', 315 'relationship in schema one'; 316 317 lives_and { 318 ok $rsrc = $test_schema->source('DbicslTest1SqlanywhereLoaderTest5'); 319 } 'got source for table in schema one'; 320 321 %uniqs = try { $rsrc->unique_constraints }; 322 323 is keys %uniqs, 2, 324 'got unique and primary constraint in schema one'; 325 326 delete $uniqs{primary}; 327 328 is_deeply ((values %uniqs)[0], ['four_id'], 329 'correct unique constraint in schema one'); 330 331 lives_and { 332 ok $rsrc = $test_schema->source('SqlanywhereLoaderTest6'); 333 } 'got source for table in schema two'; 334 335 is try { $rsrc->column_info('id')->{is_auto_increment} }, 1, 336 'column in schema two introspected correctly'; 337 338 is try { $rsrc->column_info('value')->{data_type} }, 'varchar', 339 'column in schema two introspected correctly'; 340 341 is try { $rsrc->column_info('value')->{size} }, 100, 342 'column in schema two introspected correctly'; 343 344 lives_and { 345 ok $rs = $test_schema->resultset('SqlanywhereLoaderTest6'); 346 } 'got resultset for table in schema two'; 347 348 lives_and { 349 ok $row = $rs->create({ value => 'foo' }); 350 } 'executed SQL on table in schema two'; 351 352 $rel_info = try { $rsrc->relationship_info('sqlanywhere_loader_test7') }; 353 354 is_deeply $rel_info->{cond}, { 355 'foreign.six_id' => 'self.id' 356 }, 'relationship in schema two'; 357 358 is $rel_info->{attrs}{accessor}, 'single', 359 'relationship in schema two'; 360 361 is $rel_info->{attrs}{join_type}, 'LEFT', 362 'relationship in schema two'; 363 364 lives_and { 365 ok $rsrc = $test_schema->source('SqlanywhereLoaderTest7'); 366 } 'got source for table in schema two'; 367 368 %uniqs = try { $rsrc->unique_constraints }; 369 370 is keys %uniqs, 2, 371 'got unique and primary constraint in schema two'; 372 373 delete $uniqs{primary}; 374 375 is_deeply ((values %uniqs)[0], ['six_id'], 376 'correct unique constraint in schema two'); 377 378 lives_and { 379 ok $test_schema->source('SqlanywhereLoaderTest6') 380 ->has_relationship('sqlanywhere_loader_test4'); 381 } 'cross-schema relationship in multi-db_schema'; 382 383 lives_and { 384 ok $test_schema->source('SqlanywhereLoaderTest4') 385 ->has_relationship('sqlanywhere_loader_test6s'); 386 } 'cross-schema relationship in multi-db_schema'; 387 388 lives_and { 389 ok $test_schema->source('SqlanywhereLoaderTest8') 390 ->has_relationship('sqlanywhere_loader_test7'); 391 } 'cross-schema relationship in multi-db_schema'; 392 393 lives_and { 394 ok $test_schema->source('SqlanywhereLoaderTest7') 395 ->has_relationship('sqlanywhere_loader_test8s'); 396 } 'cross-schema relationship in multi-db_schema'; 397 } 398 } 399 }, 400 }, 401); 402 403$tester->run_tests(); 404 405sub extra_cleanup { 406 if (not $ENV{SCHEMA_LOADER_TESTS_NOCLEANUP}) { 407 if ($schemas_created && (my $dbh = try { $schema->storage->dbh })) { 408 foreach my $table ('dbicsl_test1.sqlanywhere_loader_test8', 409 'dbicsl_test2.sqlanywhere_loader_test7', 410 'dbicsl_test2.sqlanywhere_loader_test6', 411 'dbicsl_test2.sqlanywhere_loader_test5', 412 'dbicsl_test1.sqlanywhere_loader_test5', 413 'dbicsl_test1.sqlanywhere_loader_test4') { 414 try { 415 $dbh->do("DROP TABLE $table"); 416 } 417 catch { 418 diag "Error dropping table: $_"; 419 }; 420 } 421 422 foreach my $db_schema (qw/dbicsl_test1 dbicsl_test2/) { 423 try { 424 $dbh->do("DROP USER $db_schema"); 425 } 426 catch { 427 diag "Error dropping test user $db_schema: $_"; 428 }; 429 } 430 } 431 rmtree EXTRA_DUMP_DIR; 432 } 433} 434# vim:et sts=4 sw=4 tw=0: 435