1#!/usr/bin/perl -w 2 3use strict; 4 5use Test::More tests => 32; 6 7BEGIN 8{ 9 require 't/test-lib.pl'; 10 use_ok('Rose::DB::Object'); 11 use_ok('Rose::DB::Object::Manager'); 12} 13 14our(%HAVE, $DID_SETUP); 15 16# 17# Tests 18# 19 20#$Rose::DB::Object::Manager::Debug = 1; 21 22foreach my $db_type (qw(mysql pg pg_with_schema informix sqlite)) 23{ 24 SKIP: 25 { 26 skip("$db_type tests", 6) unless($HAVE{$db_type}); 27 } 28 29 next unless($HAVE{$db_type}); 30 31 Rose::DB->default_type($db_type); 32 33 unless($DID_SETUP++) 34 { 35 # Load classes 36 use FindBin qw($Bin); 37 use lib "$Bin/lib"; 38 require My::DB::Gene::Main; 39 require My::DB::Unigene::Main; 40 } 41 42 # Run tests 43 44 is(join(', ', map { $_->name } My::DB::Gene2Unigene->meta->foreign_keys), 45 'Rose_db_object_g_main, Rose_db_object_ug_main', "foreign_keys 1 - $db_type"); 46 47 is(join(', ', map { $_->name . ' ' . $_->type} My::DB::Gene::Main->meta->relationships), 48 'unigenes many to many', "relationships 1 - $db_type"); 49 50 is(join(', ', map { $_->name . ' ' . $_->type} My::DB::Unigene::Main->meta->relationships), 51 'genes many to many', "relationships 2 - $db_type"); 52 53 is(scalar @Rose::DB::Object::Metadata::Deferred_Relationships || 0, 0, 54 "deferred relationships - $db_type"); 55 56 # XXX: switch entirely to per-db SQL? 57 #My::DB::Gene::Main->meta->init_with_db(Rose::DB->new); 58 #My::DB::Unigene::Main->meta->init_with_db(Rose::DB->new); 59 60 my $g = My::DB::Gene::Main->new; 61 eval { $g->unigenes }; 62 ok(!$@, "unigenes - $db_type"); 63 64 $g = My::DB::Unigene::Main->new; 65 eval { $g->genes }; 66 ok(!$@, "genes - $db_type"); 67} 68 69BEGIN 70{ 71 our %HAVE; 72 73 # 74 # PostgreSQL 75 # 76 77 my $dbh; 78 79 eval 80 { 81 $dbh = Rose::DB->new('pg_admin')->retain_dbh() 82 or die Rose::DB->error; 83 }; 84 85 if(!$@ && $dbh) 86 { 87 $HAVE{'pg'} = 1; 88 $HAVE{'pg_with_schema'} = 1; 89 90 # Drop existing tables and create schema, ignoring errors 91 { 92 local $dbh->{'RaiseError'} = 0; 93 local $dbh->{'PrintError'} = 0; 94 $dbh->do('DROP TABLE Rose_db_object_g_ug CASCADE'); 95 $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE'); 96 $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE'); 97 $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_ug CASCADE'); 98 $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_ug_main CASCADE'); 99 $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_main CASCADE'); 100 $dbh->do('CREATE SCHEMA Rose_db_object_private'); 101 } 102 103 $dbh->do(<<"EOF"); 104CREATE TABLE Rose_db_object_ug_main 105( 106 ug_id VARCHAR PRIMARY KEY NOT NULL, 107 species VARCHAR, 108 symbol VARCHAR, 109 description VARCHAR, 110 cytoband VARCHAR, 111 scount INTEGER, 112 homol VARCHAR, 113 rest_expr VARCHAR, 114 mgi VARCHAR 115) 116EOF 117 118 $dbh->do(<<"EOF"); 119CREATE TABLE Rose_db_object_g_main 120( 121 tax_id INTEGER, 122 gene_id INTEGER PRIMARY KEY, 123 symbol VARCHAR, 124 locustag VARCHAR, 125 chromosome VARCHAR, 126 map_location VARCHAR, 127 description VARCHAR, 128 gene_type VARCHAR, 129 symbol_from_nomenclature_auth VARCHAR, 130 full_name_from_nomenclature_auth VARCHAR, 131 nomenclature_status VARCHAR, 132 discontinued BOOLEAN DEFAULT FALSE, 133 new_gene_id INTEGER DEFAULT NULL 134) 135EOF 136 137 $dbh->do(<<"EOF"); 138CREATE TABLE Rose_db_object_g_ug 139( 140 g_ug_id SERIAL PRIMARY KEY, 141 142 gene_id INTEGER REFERENCES Rose_db_object_g_main (gene_id) 143 ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, 144 145 ug_id VARCHAR REFERENCES Rose_db_object_ug_main (ug_id) 146 ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE 147) 148EOF 149 150 $dbh->do(<<"EOF"); 151CREATE TABLE Rose_db_object_private.Rose_db_object_ug_main 152( 153 ug_id VARCHAR PRIMARY KEY NOT NULL, 154 species VARCHAR, 155 symbol VARCHAR, 156 description VARCHAR, 157 cytoband VARCHAR, 158 scount INTEGER, 159 homol VARCHAR, 160 rest_expr VARCHAR, 161 mgi VARCHAR 162) 163EOF 164 165 $dbh->do(<<"EOF"); 166CREATE TABLE Rose_db_object_private.Rose_db_object_g_main 167( 168 tax_id INTEGER, 169 gene_id INTEGER PRIMARY KEY, 170 symbol VARCHAR, 171 locustag VARCHAR, 172 chromosome VARCHAR, 173 map_location VARCHAR, 174 description VARCHAR, 175 gene_type VARCHAR, 176 symbol_from_nomenclature_auth VARCHAR, 177 full_name_from_nomenclature_auth VARCHAR, 178 nomenclature_status VARCHAR, 179 discontinued BOOLEAN DEFAULT FALSE, 180 new_gene_id INTEGER DEFAULT NULL 181) 182EOF 183 184 $dbh->do(<<"EOF"); 185CREATE TABLE Rose_db_object_private.Rose_db_object_g_ug 186( 187 g_ug_id SERIAL PRIMARY KEY, 188 189 gene_id INTEGER REFERENCES Rose_db_object_private.Rose_db_object_g_main (gene_id) 190 ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE, 191 192 ug_id VARCHAR REFERENCES Rose_db_object_private.Rose_db_object_ug_main (ug_id) 193 ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE 194) 195EOF 196 197 $dbh->disconnect; 198 } 199 200 # 201 # MySQL 202 # 203 204 eval 205 { 206 my $db = Rose::DB->new('mysql_admin'); 207 $dbh = $db->retain_dbh or die Rose::DB->error; 208 209 die "MySQL version too old" unless($db->database_version >= 4_000_000 && 210 mysql_supports_innodb()); 211 212 # Drop existing tables, ignoring errors 213 { 214 local $dbh->{'RaiseError'} = 0; 215 local $dbh->{'PrintError'} = 0; 216 $dbh->do('DROP TABLE Rose_db_object_g_ug CASCADE'); 217 $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE'); 218 $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE'); 219 } 220 221 $dbh->do(<<"EOF"); 222CREATE TABLE Rose_db_object_ug_main 223( 224 ug_id VARCHAR(255) NOT NULL PRIMARY KEY, 225 species VARCHAR(255), 226 symbol VARCHAR(255), 227 description VARCHAR(255), 228 cytoband VARCHAR(255), 229 scount INT, 230 homol VARCHAR(255), 231 rest_expr VARCHAR(255), 232 mgi VARCHAR(255) 233) 234ENGINE=InnoDB 235EOF 236 }; 237 238 if(!$@ && $dbh) 239 { 240 $HAVE{'mysql'} = 1; 241 242 $dbh->do(<<"EOF"); 243CREATE TABLE Rose_db_object_g_main 244( 245 gene_id INT NOT NULL PRIMARY KEY, 246 tax_id INT, 247 symbol VARCHAR(255), 248 locustag VARCHAR(255), 249 chromosome VARCHAR(255), 250 map_location VARCHAR(255), 251 description VARCHAR(255), 252 gene_type VARCHAR(255), 253 symbol_from_nomenclature_auth VARCHAR(255), 254 full_name_from_nomenclature_auth VARCHAR(255), 255 nomenclature_status VARCHAR(255), 256 discontinued INT DEFAULT 0, 257 new_gene_id INT DEFAULT NULL 258) 259ENGINE=InnoDB 260EOF 261 262 $dbh->do(<<"EOF"); 263CREATE TABLE Rose_db_object_g_ug 264( 265 g_ug_id INT PRIMARY KEY, 266 gene_id INT NOT NULL, 267 ug_id VARCHAR(255) NOT NULL, 268 269 INDEX(gene_id), 270 INDEX(ug_id), 271 272 FOREIGN KEY (gene_id) REFERENCES Rose_db_object_g_main (gene_id), 273 FOREIGN KEY (ug_id) REFERENCES Rose_db_object_ug_main (ug_id) 274) 275ENGINE=InnoDB 276EOF 277 278 $dbh->disconnect; 279 } 280 281 # 282 # Informix 283 # 284 285 eval 286 { 287 $dbh = Rose::DB->new('informix_admin')->retain_dbh() 288 or die Rose::DB->error; 289 }; 290 291 if(!$@ && $dbh) 292 { 293 $HAVE{'informix'} = 1; 294 295 # Drop existing tables, ignoring errors 296 { 297 local $dbh->{'RaiseError'} = 0; 298 local $dbh->{'PrintError'} = 0; 299 $dbh->do('DROP TABLE Rose_db_object_g_ug'); 300 $dbh->do('DROP TABLE Rose_db_object_ug_main'); 301 $dbh->do('DROP TABLE Rose_db_object_g_main'); 302 } 303 304 $dbh->do(<<"EOF"); 305CREATE TABLE Rose_db_object_ug_main 306( 307 ug_id VARCHAR(255) NOT NULL PRIMARY KEY, 308 species VARCHAR(255), 309 symbol VARCHAR(255), 310 description VARCHAR(255), 311 cytoband VARCHAR(255), 312 scount INT, 313 homol VARCHAR(255), 314 rest_expr VARCHAR(255), 315 mgi VARCHAR(255) 316) 317EOF 318 319 $dbh->do(<<"EOF"); 320CREATE TABLE Rose_db_object_g_main 321( 322 tax_id INT, 323 gene_id INT PRIMARY KEY, 324 symbol VARCHAR(255), 325 locustag VARCHAR(255), 326 chromosome VARCHAR(255), 327 map_location VARCHAR(255), 328 description VARCHAR(255), 329 gene_type VARCHAR(255), 330 symbol_from_nomenclature_auth VARCHAR(255), 331 full_name_from_nomenclature_auth VARCHAR(255), 332 nomenclature_status VARCHAR(255), 333 discontinued INT DEFAULT 0, 334 new_gene_id INT DEFAULT NULL 335) 336EOF 337 338 $dbh->do(<<"EOF"); 339CREATE TABLE Rose_db_object_g_ug 340( 341 g_ug_id SERIAL PRIMARY KEY, 342 gene_id INT REFERENCES Rose_db_object_g_main (gene_id), 343 ug_id VARCHAR(255) REFERENCES Rose_db_object_ug_main (ug_id) 344) 345EOF 346 347 $dbh->disconnect; 348 } 349 350 # 351 # SQLite 352 # 353 354 eval 355 { 356 $dbh = Rose::DB->new('sqlite_admin')->retain_dbh() 357 or die Rose::DB->error; 358 }; 359 360 if(!$@ && $dbh) 361 { 362 $HAVE{'sqlite'} = 1; 363 364 # Drop existing tables, ignoring errors 365 { 366 local $dbh->{'RaiseError'} = 0; 367 local $dbh->{'PrintError'} = 0; 368 $dbh->do('DROP TABLE Rose_db_object_g_ug'); 369 $dbh->do('DROP TABLE Rose_db_object_ug_main'); 370 $dbh->do('DROP TABLE Rose_db_object_g_main'); 371 } 372 373 $dbh->do(<<"EOF"); 374CREATE TABLE Rose_db_object_ug_main 375( 376 ug_id VARCHAR(255) NOT NULL PRIMARY KEY, 377 species VARCHAR(255), 378 symbol VARCHAR(255), 379 description VARCHAR(255), 380 cytoband VARCHAR(255), 381 scount INT, 382 homol VARCHAR(255), 383 rest_expr VARCHAR(255), 384 mgi VARCHAR(255) 385) 386EOF 387 388 $dbh->do(<<"EOF"); 389CREATE TABLE Rose_db_object_g_main 390( 391 tax_id INT, 392 gene_id INT PRIMARY KEY, 393 symbol VARCHAR(255), 394 locustag VARCHAR(255), 395 chromosome VARCHAR(255), 396 map_location VARCHAR(255), 397 description VARCHAR(255), 398 gene_type VARCHAR(255), 399 symbol_from_nomenclature_auth VARCHAR(255), 400 full_name_from_nomenclature_auth VARCHAR(255), 401 nomenclature_status VARCHAR(255), 402 discontinued INT DEFAULT 0, 403 new_gene_id INT DEFAULT NULL 404) 405EOF 406 407 $dbh->do(<<"EOF"); 408CREATE TABLE Rose_db_object_g_ug 409( 410 g_ug_id INTEGER PRIMARY KEY AUTOINCREMENT, 411 gene_id INTEGER REFERENCES Rose_db_object_g_main (gene_id), 412 ug_id VARCHAR(255) REFERENCES Rose_db_object_ug_main (ug_id) 413) 414EOF 415 416 $dbh->disconnect; 417 } 418} 419 420END 421{ 422 # Delete test table 423 424 if($HAVE{'pg'}) 425 { 426 # PostgreSQL 427 my $dbh = Rose::DB->new('pg_admin')->retain_dbh() 428 or die Rose::DB->error; 429 430 $dbh->do('DROP TABLE Rose_db_object_g_ug CASCADE'); 431 $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE'); 432 $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE'); 433 $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_ug CASCADE'); 434 $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_ug_main CASCADE'); 435 $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_main CASCADE'); 436 437 $dbh->disconnect; 438 } 439 440 if($HAVE{'mysql'}) 441 { 442 # MySQL 443 my $dbh = Rose::DB->new('mysql_admin')->retain_dbh() 444 or die Rose::DB->error; 445 446 $dbh->do('DROP TABLE Rose_db_object_g_ug CASCADE'); 447 $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE'); 448 $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE'); 449 450 $dbh->disconnect; 451 } 452 453 if($HAVE{'informix'}) 454 { 455 # Informix 456 my $dbh = Rose::DB->new('informix_admin')->retain_dbh() 457 or die Rose::DB->error; 458 459 $dbh->do('DROP TABLE Rose_db_object_g_ug CASCADE'); 460 $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE'); 461 $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE'); 462 463 $dbh->disconnect; 464 } 465 466 if($HAVE{'sqlite'}) 467 { 468 # Informix 469 my $dbh = Rose::DB->new('sqlite_admin')->retain_dbh() 470 or die Rose::DB->error; 471 472 $dbh->do('DROP TABLE Rose_db_object_g_ug'); 473 $dbh->do('DROP TABLE Rose_db_object_ug_main'); 474 $dbh->do('DROP TABLE Rose_db_object_g_main'); 475 476 $dbh->disconnect; 477 } 478} 479