1#!/usr/bin/perl -w 2 3use strict; 4 5use Test::More tests => 1 + (6 * 38) + 9; 6 7BEGIN 8{ 9 require 't/test-lib.pl'; 10 use_ok('Rose::DB::Object::Loader'); 11} 12 13our %Have; 14 15our @Tables = qw(vendors products prices colors products_colors); 16our $Include_Tables = join('|', @Tables, 'no_pk_test2?'); 17 18our %Reserved_Words; 19 20# 21# Tests 22# 23 24FOO: 25{ 26 package MyCM; 27 28 @MyCM::ISA = qw(Rose::DB::Object::ConventionManager); 29 30 sub auto_foreign_key_name 31 { 32 $JCS::Called_Custom_CM{$_[0]->parent->class}++; 33 shift->SUPER::auto_foreign_key_name(@_); 34 } 35} 36 37my $i = 1; 38 39foreach my $db_type (qw(mysql pg pg_with_schema informix sqlite oracle)) 40{ 41 SKIP: 42 { 43 unless($Have{$db_type}) 44 { 45 skip("$db_type tests", 38 + scalar @{$Reserved_Words{$db_type} ||= []}); 46 } 47 } 48 49 next unless($Have{$db_type}); 50 51 $i++; 52 53 Rose::DB->default_type($db_type); 54 Rose::DB::Object::Metadata->unregister_all_classes; 55 56 my $class_prefix = ucfirst($db_type eq 'pg_with_schema' ? 'pgws' : $db_type); 57 58 #$Rose::DB::Object::Metadata::Debug = 1; 59 60 %JCS::Called_Custom_CM = (); 61 62 my $pre_init_hook = 0; 63 64 my $db = Rose::DB->new; 65 my $loader = 66 Rose::DB::Object::Loader->new( 67 db => $db, 68 class_prefix => $class_prefix, 69 ($db_type eq 'mysql' ? (require_primary_key => 0) : ()), 70 pre_init_hook => sub { $pre_init_hook++ }); 71 72 my %extra_loader_args; 73 74 if($db_type eq 'sqlite') 75 { 76 $loader->warn_on_missing_primary_key(0); 77 $loader->warn_on_missing_pk(1); 78 } 79 elsif($db_type eq 'pg') 80 { 81 $loader->include_predicated_unique_indexes(1); 82 } 83 elsif($db_type eq 'mysql') 84 { 85 $loader->warn_on_missing_pk(0); 86 $loader->warn_on_missing_primary_key(1); 87 $extra_loader_args{'warn_on_missing_pk'} = undef; 88 $extra_loader_args{'warn_on_missing_primary_key'} = undef; 89 } 90 91 $loader->convention_manager($i % 2 ? 'MyCM' : MyCM->new); 92 93 my @classes; 94 95 my $i = 0; 96 97 # Test aliased parameter conflicts 98 foreach my $a (0, 1, undef) 99 { 100 foreach my $b (0, 1, undef) 101 { 102 if(($a || 0) != ($b || 0)) 103 { 104 $i++; 105 106 eval 107 { 108 $loader->make_classes(warn_on_missing_pk => $a, 109 warn_on_missing_primary_key => $b); 110 }; 111 112 ok($@, "warn_on_missing_pk conflict $i - $db_type"); 113 } 114 } 115 } 116 117 CATCH_WARNINGS: 118 { 119 my $warnings; 120 local $SIG{'__WARN__'} = sub { $warnings .= "@_\n" }; 121 @classes = $loader->make_classes(include_tables => $Include_Tables . 122 ($db_type eq 'mysql' ? '|read' : ''), 123 %extra_loader_args); 124 125 #foreach my $class (@classes) 126 #{ 127 # next unless($class->isa('Rose::DB::Object')); 128 # print $class->meta->perl_class_definition, "\n"; 129 #} 130 131 if($db_type eq 'sqlite') 132 { 133 ok($warnings =~ /\QWarning: table 'no_pk_test' has no primary key defined. Skipping./, 134 "warn_on_missing_primary_key - $db_type"); 135 } 136 else 137 { 138 is($warnings, undef, "warn_on_missing_primary_key - $db_type"); 139 } 140 } 141 142 ok(scalar keys %JCS::Called_Custom_CM >= 3, "custom convention manager - $db_type"); 143 ok($pre_init_hook > 0, "pre_init_hook - $db_type"); 144 145 if($db_type eq 'informix') 146 { 147 foreach my $class (@classes) 148 { 149 next unless($class->isa('Rose::DB::Object')); 150 $class->meta->allow_inline_column_values(1); 151 152 if($class->meta->column('release_day')) 153 { 154 is($class->meta->column('release_day')->type, 'datetime year to month', 155 "datetime year to month - $db_type"); 156 } 157 } 158 } 159 else 160 { 161 ok(1, "skip datetime year to month - $db_type"); 162 } 163 164 if(defined Rose::DB->new->schema) 165 { 166 ok(!scalar(grep { /NoPk2/i } @classes), "pk classes only - $db_type"); 167 } 168 else 169 { 170 if($db_type eq 'mysql') 171 { 172 ok(1, "pk classes - $db_type"); 173 } 174 else 175 { 176 ok(!scalar(grep { /NoPk\b/i } @classes), "pk classes only - $db_type"); 177 } 178 } 179 180 my $product_class = $class_prefix . '::Product'; 181 my $price_class = $class_prefix . '::Price'; 182 my $map_manager_class = $class_prefix . '::ProductsColor::Manager'; 183 184 ## 185 ## Run tests 186 ## 187 188 if($db_type =~ /^(?:mysql|pg|sqlite)$/) 189 { 190 my $serial = 191 ($db_type ne 'mysql' || $db->dbh->{'Driver'}{'Version'} >= 4.002) ? 192 'serial' : 'integer'; 193 194 is($product_class->meta->column('id')->type, $serial, "serial column - $db_type"); 195 } 196 else 197 { 198 SKIP: { skip("serial coercion test for $db_type", 1) } 199 } 200 201 if($db_type eq 'pg') 202 { 203 my $uk = $product_class->meta->unique_key_by_name('products_uk_test'); 204 ok($uk && $uk->has_predicate, "include unique index with predicate - $db_type"); 205 } 206 elsif($db_type eq 'pg_with_schema') 207 { 208 my $uk = $product_class->meta->unique_key_by_name('products_uk_test'); 209 ok(!$uk, "skip unique index with predicate - $db_type"); 210 } 211 else 212 { 213 SKIP: { skip("unique index with predicate for $db_type", 1) } 214 } 215 216 if($db_type eq 'pg') 217 { 218 is($product_class->meta->column('release_date')->type, 'timestamp', 219 "timestamp - $db_type"); 220 221 is($product_class->meta->column('release_date_tz')->type, 'timestamp with time zone', 222 "timestamp with time zone - $db_type"); 223 } 224 else 225 { 226 SKIP: { skip("timestamp with time zone tests for $db_type", 2) } 227 } 228 229 if($db_type eq 'mysql' && $db->dbh->{'Driver'}{'Version'} >= 4.002) 230 { 231 is($price_class->meta->column('id')->type, 'bigserial', "bigserial column - $db_type"); 232 } 233 else 234 { 235 SKIP: { skip("bigserial test for $db_type", 1) } 236 } 237 238 if($db_type eq 'Pg' || $db_type eq 'mysql') 239 { 240 is($price_class->meta->column('price')->precision, 10, "decimal precision - $db_type"); 241 is($price_class->meta->column('price')->scale, 2, "decimal scale - $db_type"); 242 } 243 else 244 { 245 SKIP: { skip("decimal precision and scale - $db_type yet", 2) } 246 } 247 248 if($db_type eq 'informix' || $db_type eq 'oracle') 249 { 250 SKIP: { skip("count distinct multi-pk doesn't work in \u$db_type yet", 1) } 251 } 252 else 253 { 254 my $count = $map_manager_class->get_objects_count(require_objects => [ 'color' ]); 255 is($count, 0, "count distinct multi-pk - $db_type"); 256 } 257 258 my $p = $product_class->new(name => "Sled $i"); 259 260 if($p->can('release_day')) 261 { 262 $p->release_day('2001-02'); 263 die "datetime year to month not truncated" unless($p->release_day->day == 1); 264 $p->release_day('2001-02-05'); 265 die "datetime year to month not truncated" unless($p->release_day->day == 1); 266 } 267 268 # Check reserved methods 269 foreach my $word (@{$Reserved_Words{$db_type} ||= []}) 270 { 271 ok($p->$word(int(rand(10)) + 1), "reserved word: $word - $db_type"); 272 } 273 274 is($p->db->class, 'Rose::DB', "db 1 - $db_type"); 275 276 if($db_type =~ /^pg/) 277 { 278 ok($p->can('tee_time') && $p->can('tee_time5'), "time methods - $db_type"); 279 is($p->meta->column('tee_time5')->scale, 5, "time precision check 1 - $db_type"); 280 is($p->meta->column('tee_time')->scale || 0, 0, "time precision check 2 - $db_type"); 281 my $t = $p->tee_time5->as_string; 282 $t =~ s/0+$//; 283 is($p->tee_time5->as_string, '12:34:56.12345', "time default 1 - $db_type"); 284 $t = $p->meta->column('tee_time5')->default; 285 $t =~ s/0+$//; 286 is($t, '12:34:56.12345', "time default 2 - $db_type"); 287 is($price_class->meta->column('mprice')->length, undef, "money 1 - $db_type"); 288 } 289 elsif($db_type eq 'informix') 290 { 291 ok(!$p->can('tee_time') && !$p->can('tee_time5'), "time methods - $db_type"); 292 ok(!$p->meta->column('tee_time5'), "time precision check 1 - $db_type"); 293 ok(!$p->meta->column('tee_time'), "time precision check 2 - $db_type"); 294 is($p->meta->column('bint1')->type, 'bigint', "bigint 1 - $db_type"); 295 ok($p->bint1 =~ /^\+?9223372036854775800$/, "bigint 2 - $db_type"); 296 SKIP: { skip("money tests - $db_type", 1) } 297 } 298 else 299 { 300 ok(!$p->can('tee_time') && !$p->can('tee_time5'), "time methods - $db_type"); 301 ok(!$p->meta->column('tee_time5'), "time precision check 1 - $db_type"); 302 ok(!$p->meta->column('tee_time'), "time precision check 2 - $db_type"); 303 ok(1, "time default 1 - $db_type"); 304 ok(1, "time default 2 - $db_type"); 305 SKIP: { skip("money tests - $db_type", 1) } 306 } 307 308 OBJECT_CLASS: 309 { 310 no strict 'refs'; 311 ok(${"${product_class}::ISA"}[0] =~ /^${class_prefix}::DB::Object::AutoBase\d+$/, "base class 1 - $db_type"); 312 } 313 314 $p->vendor(name => "Acme $i"); 315 316 $p->prices({ price => 1.25, region => 'US' }, 317 { price => 4.25, region => 'UK' }); 318 319 $p->colors({ name => 'red' }, 320 { name => 'green' }); 321 322 $p->save; 323 324 $p = $product_class->new(id => $p->id)->load; 325 is($p->vendor->name, "Acme $i", "vendor 1 - $db_type"); 326 327 328 my @prices = sort { $a->price <=> $b->price } $p->prices; 329 330 is(scalar @prices, 2, "prices 1 - $db_type"); 331 is($prices[0]->price, 1.25, "prices 2 - $db_type"); 332 is($prices[1]->price, 4.25, "prices 3 - $db_type"); 333 334 my @colors = sort { $a->name cmp $b->name } $p->colors; 335 336 is(scalar @colors, 2, "colors 1 - $db_type"); 337 is($colors[0]->name, 'green', "colors 2 - $db_type"); 338 is($colors[1]->name, 'red', "colors 3 - $db_type"); 339 340 my $mgr_class = $class_prefix . '::Product::Manager'; 341 342 #local $Rose::DB::Object::Manager::Debug = 1; 343 #$DB::single = 1; 344 345 my $prods = $mgr_class->get_products(query => [ id => $p->id ]); 346 347 is(ref $prods, 'ARRAY', "get_products 1 - $db_type"); 348 is(@$prods, 1, "get_products 2 - $db_type"); 349 is($prods->[0]->id, $p->id, "get_products 3 - $db_type"); 350 351 #$DB::single = 1; 352 #local $Rose::DB::Object::Debug = 1; 353 354 # Reserved tablee name tests 355 if($db_type eq 'mysql') 356 { 357 my $o = Mysql::Read->new(read => 'Foo')->save; 358 $o = Mysql::Read->new(id => $o->id)->load; 359 is($o->read, 'Foo', "reserved table name 1 - $db_type"); 360 my $os = Mysql::Read::Manager->get_read; 361 ok(@$os == 1 && $os->[0]->read eq 'Foo', "reserved table name 2 - $db_type"); 362 363 ok(Mysql::NoPkTest->isa('Rose::DB::Object'), "require_primary_key 1 - $db_type") 364 } 365 else 366 { 367 SKIP: 368 { 369 skip("reserved table name and no pk tests", 3); 370 } 371 } 372} 373 374 375BEGIN 376{ 377 our %Have; 378 379 our %Reserved_Words = 380 ( 381 'pg' => [ qw(role cast user) ], 382 'pg_with_schema' => [ qw(role cast user) ], 383 'mysql' => [ qw(read for case) ], 384 ); 385 386 # 387 # PostgreSQL 388 # 389 390 my $dbh; 391 392 eval 393 { 394 $dbh = Rose::DB->new('pg_admin')->retain_dbh() 395 or die Rose::DB->error; 396 }; 397 398 if(!$@ && $dbh) 399 { 400 $Have{'pg'} = 1; 401 $Have{'pg_with_schema'} = 1; 402 403 # Drop existing tables and create schema, ignoring errors 404 { 405 local $dbh->{'RaiseError'} = 0; 406 local $dbh->{'PrintError'} = 0; 407 408 $dbh->do('DROP TABLE no_pk_test CASCADE'); 409 $dbh->do('DROP TABLE no_pk_test2 CASCADE'); 410 $dbh->do('DROP TABLE products_colors CASCADE'); 411 $dbh->do('DROP TABLE colors CASCADE'); 412 $dbh->do('DROP TABLE prices CASCADE'); 413 $dbh->do('DROP TABLE products CASCADE'); 414 $dbh->do('DROP TABLE vendors CASCADE'); 415 416 $dbh->do('DROP TABLE Rose_db_object_private.no_pk_test CASCADE'); 417 $dbh->do('DROP TABLE Rose_db_object_private.no_pk_test2 CASCADE'); 418 $dbh->do('DROP TABLE Rose_db_object_private.products_colors CASCADE'); 419 $dbh->do('DROP TABLE Rose_db_object_private.colors CASCADE'); 420 $dbh->do('DROP TABLE Rose_db_object_private.prices CASCADE'); 421 $dbh->do('DROP TABLE Rose_db_object_private.products CASCADE'); 422 $dbh->do('DROP TABLE Rose_db_object_private.vendors CASCADE'); 423 424 $dbh->do('DROP SCHEMA Rose_db_object_private CASCADE'); 425 $dbh->do('CREATE SCHEMA Rose_db_object_private'); 426 } 427 428 $dbh->do(<<"EOF"); 429CREATE TABLE no_pk_test 430( 431 id SERIAL NOT NULL, 432 name VARCHAR(255) NOT NULL, 433 434 UNIQUE(name) 435) 436EOF 437 438 $dbh->do(<<"EOF"); 439CREATE TABLE no_pk_test2 440( 441 id SERIAL NOT NULL PRIMARY KEY, 442 name VARCHAR(255) NOT NULL, 443 444 UNIQUE(name) 445) 446EOF 447 448 $dbh->do(<<"EOF"); 449CREATE TABLE vendors 450( 451 id SERIAL NOT NULL PRIMARY KEY, 452 name VARCHAR(255) NOT NULL, 453 454 UNIQUE(name) 455) 456EOF 457 458 $dbh->do(<<"EOF"); 459CREATE TABLE products 460( 461 id SERIAL NOT NULL PRIMARY KEY, 462 name VARCHAR(255) NOT NULL, 463 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 464 465 @{[ join(', ', map { qq("$_" INT) } @{$Reserved_Words{'pg'}}) . ',' ]} 466 467 vendor_id INT REFERENCES vendors (id), 468 469 status VARCHAR(128) NOT NULL DEFAULT 'inactive' 470 CHECK(status IN ('inactive', 'active', 'defunct')), 471 472 tee_time TIME, 473 tee_time5 TIME(5) DEFAULT '12:34:56.12345', 474 475 date_created TIMESTAMP NOT NULL DEFAULT NOW(), 476 release_date TIMESTAMP, 477 release_date_tz TIMESTAMP WITH TIME ZONE, 478 479 UNIQUE(name) 480) 481EOF 482 483 $dbh->do(<<"EOF"); 484CREATE UNIQUE INDEX products_uk_test ON products (date_created) WHERE status = 'inactive'; 485EOF 486 487 $dbh->do(<<"EOF"); 488CREATE UNIQUE INDEX products_uk1 ON products (LOWER(name)) 489EOF 490 491 $dbh->do(<<"EOF"); 492CREATE TABLE prices 493( 494 id SERIAL NOT NULL PRIMARY KEY, 495 product_id INT NOT NULL REFERENCES products (id), 496 region CHAR(2) NOT NULL DEFAULT 'US', 497 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 498 mprice MONEY, 499 500 UNIQUE(product_id, region) 501) 502EOF 503 504 $dbh->do(<<"EOF"); 505CREATE TABLE colors 506( 507 id SERIAL NOT NULL PRIMARY KEY, 508 name VARCHAR(255) NOT NULL, 509 510 UNIQUE(name) 511) 512EOF 513 514 $dbh->do(<<"EOF"); 515CREATE TABLE products_colors 516( 517 product_id INT NOT NULL REFERENCES products (id), 518 color_id INT NOT NULL REFERENCES colors (id), 519 520 PRIMARY KEY(product_id, color_id) 521) 522EOF 523 524 $dbh->do(<<"EOF"); 525CREATE TABLE Rose_db_object_private.no_pk_test 526( 527 id SERIAL NOT NULL PRIMARY KEY, 528 name VARCHAR(255) NOT NULL, 529 530 UNIQUE(name) 531) 532EOF 533 534 $dbh->do(<<"EOF"); 535CREATE TABLE Rose_db_object_private.no_pk_test2 536( 537 id SERIAL NOT NULL, 538 name VARCHAR(255) NOT NULL, 539 540 UNIQUE(name) 541) 542EOF 543 544 $dbh->do(<<"EOF"); 545CREATE TABLE Rose_db_object_private.vendors 546( 547 id SERIAL NOT NULL PRIMARY KEY, 548 name VARCHAR(255) NOT NULL, 549 550 UNIQUE(name) 551) 552EOF 553 554 $dbh->do(<<"EOF"); 555CREATE TABLE Rose_db_object_private.products 556( 557 id SERIAL NOT NULL PRIMARY KEY, 558 name VARCHAR(255) NOT NULL, 559 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 560 561 @{[ join(', ', map { qq("$_" INT) } @{$Reserved_Words{'pg'}}) . ',' ]} 562 563 vendor_id INT REFERENCES vendors (id), 564 565 status VARCHAR(128) NOT NULL DEFAULT 'inactive' 566 CHECK(status IN ('inactive', 'active', 'defunct')), 567 568 tee_time TIME, 569 tee_time5 TIME(5) DEFAULT '12:34:56.12345', 570 571 date_created TIMESTAMP NOT NULL DEFAULT NOW(), 572 release_date TIMESTAMP, 573 release_date_tz TIMESTAMP WITH TIME ZONE, 574 575 UNIQUE(name) 576) 577EOF 578 579 $dbh->do(<<"EOF"); 580CREATE UNIQUE INDEX products_uk_test ON Rose_db_object_private.products (date_created) WHERE status = 'inactive'; 581EOF 582 583 $dbh->do(<<"EOF"); 584CREATE TABLE Rose_db_object_private.prices 585( 586 id SERIAL NOT NULL PRIMARY KEY, 587 product_id INT NOT NULL REFERENCES products (id), 588 region CHAR(2) NOT NULL DEFAULT 'US', 589 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 590 mprice MONEY, 591 592 UNIQUE(product_id, region) 593) 594EOF 595 596 $dbh->do(<<"EOF"); 597CREATE TABLE Rose_db_object_private.colors 598( 599 id SERIAL NOT NULL PRIMARY KEY, 600 name VARCHAR(255) NOT NULL, 601 602 UNIQUE(name) 603) 604EOF 605 606 $dbh->do(<<"EOF"); 607CREATE TABLE Rose_db_object_private.products_colors 608( 609 product_id INT NOT NULL REFERENCES products (id), 610 color_id INT NOT NULL REFERENCES colors (id), 611 612 PRIMARY KEY(product_id, color_id) 613) 614EOF 615 616 $dbh->disconnect; 617 } 618 619 # 620 # MySQL 621 # 622 623 eval 624 { 625 my $db = Rose::DB->new('mysql_admin'); 626 $dbh = $db->retain_dbh or die Rose::DB->error; 627 628 die "MySQL version too old" unless($db->database_version >= 4_000_000); 629 630 # Drop existing tables, ignoring errors 631 { 632 local $dbh->{'RaiseError'} = 0; 633 local $dbh->{'PrintError'} = 0; 634 635 $dbh->do('DROP TABLE no_pk_test CASCADE'); 636 $dbh->do('DROP TABLE products_colors CASCADE'); 637 $dbh->do('DROP TABLE colors CASCADE'); 638 $dbh->do('DROP TABLE prices CASCADE'); 639 $dbh->do('DROP TABLE products CASCADE'); 640 $dbh->do('DROP TABLE vendors CASCADE'); 641 $dbh->do('DROP TABLE `read` CASCADE'); 642 } 643 644 # Foreign key stuff requires InnoDB support 645 $dbh->do(<<"EOF"); 646CREATE TABLE vendors 647( 648 id INT AUTO_INCREMENT PRIMARY KEY, 649 name VARCHAR(255) NOT NULL, 650 651 UNIQUE(name) 652) 653ENGINE=InnoDB 654EOF 655 656 # MySQL will silently ignore the "ENGINE=InnoDB" part and create 657 # a MyISAM table instead. MySQL is evil! Now we have to manually 658 # check to make sure an InnoDB table was really created. 659 my $db_name = $db->database; 660 my $sth = $dbh->prepare("SHOW TABLE STATUS FROM `$db_name` LIKE ?"); 661 $sth->execute('vendors'); 662 my $info = $sth->fetchrow_hashref; 663 664 no warnings 'uninitialized'; 665 unless(lc $info->{'Type'} eq 'innodb' || lc $info->{'Engine'} eq 'innodb') 666 { 667 die "Missing InnoDB support"; 668 } 669 }; 670 671 if(!$@ && $dbh) 672 { 673 $Have{'mysql'} = 1; 674 675 $dbh->do(<<"EOF"); 676CREATE TABLE no_pk_test 677( 678 id INT NOT NULL, 679 name VARCHAR(255) NOT NULL, 680 681 UNIQUE(name) 682) 683EOF 684 685 $dbh->do(<<"EOF"); 686CREATE TABLE products 687( 688 id INT AUTO_INCREMENT PRIMARY KEY, 689 name VARCHAR(255) NOT NULL, 690 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 691 692 @{[ join(', ', map { "`$_` INT" } @{$Reserved_Words{'mysql'}}) . ',' ]} 693 694 vendor_id INT, 695 696 status VARCHAR(128) NOT NULL DEFAULT 'inactive' 697 CHECK(status IN ('inactive', 'active', 'defunct')), 698 699 date_created TIMESTAMP, 700 release_date TIMESTAMP, 701 702 UNIQUE(name), 703 INDEX(vendor_id), 704 705 FOREIGN KEY (vendor_id) REFERENCES vendors (id) ON DELETE NO ACTION ON UPDATE SET NULL 706) 707ENGINE=InnoDB 708EOF 709 710 $dbh->do(<<"EOF"); 711CREATE TABLE prices 712( 713 id BIGINT AUTO_INCREMENT PRIMARY KEY, 714 product_id INT NOT NULL, 715 region CHAR(2) NOT NULL DEFAULT 'US', 716 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 717 718 UNIQUE(product_id, region), 719 INDEX(product_id), 720 721 FOREIGN KEY (product_id) REFERENCES products (id) ON UPDATE NO ACTION 722) 723ENGINE=InnoDB 724EOF 725 726 $dbh->do(<<"EOF"); 727CREATE TABLE colors 728( 729 id INT AUTO_INCREMENT PRIMARY KEY, 730 name VARCHAR(255) NOT NULL, 731 732 UNIQUE(name) 733) 734ENGINE=InnoDB 735EOF 736 737 $dbh->do(<<"EOF"); 738CREATE TABLE products_colors 739( 740 product_id INT NOT NULL, 741 color_id INT NOT NULL, 742 743 PRIMARY KEY(product_id, color_id), 744 745 INDEX(color_id), 746 INDEX(product_id), 747 748 FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE NO ACTION, 749 FOREIGN KEY (color_id) REFERENCES colors (id) ON UPDATE NO ACTION 750) 751ENGINE=InnoDB 752EOF 753 754 $dbh->do(<<"EOF"); 755CREATE TABLE `read` 756( 757 id INT AUTO_INCREMENT PRIMARY KEY, 758 `read` VARCHAR(255) NOT NULL 759) 760ENGINE=InnoDB 761EOF 762 763 $dbh->disconnect; 764 } 765 766 # 767 # Informix 768 # 769 770 eval 771 { 772 $dbh = Rose::DB->new('informix_admin')->retain_dbh() 773 or die Rose::DB->error; 774 }; 775 776 if(!$@ && $dbh) 777 { 778 $Have{'informix'} = 1; 779 780 # Drop existing tables, ignoring errors 781 { 782 local $dbh->{'RaiseError'} = 0; 783 local $dbh->{'PrintError'} = 0; 784 785 $dbh->do('DROP TABLE no_pk_test CASCADE'); 786 $dbh->do('DROP TABLE products_colors CASCADE'); 787 $dbh->do('DROP TABLE colors CASCADE'); 788 $dbh->do('DROP TABLE prices CASCADE'); 789 $dbh->do('DROP TABLE products CASCADE'); 790 $dbh->do('DROP TABLE vendors CASCADE'); 791 } 792 793 $dbh->do(<<"EOF"); 794CREATE TABLE no_pk_test 795( 796 id INT NOT NULL, 797 name VARCHAR(255) NOT NULL, 798 799 UNIQUE(name) 800) 801EOF 802 803 $dbh->do(<<"EOF"); 804CREATE TABLE vendors 805( 806 id SERIAL NOT NULL PRIMARY KEY, 807 name VARCHAR(255) NOT NULL, 808 809 UNIQUE(name) 810) 811EOF 812 813 $dbh->do(<<"EOF"); 814CREATE TABLE products 815( 816 id SERIAL NOT NULL PRIMARY KEY, 817 name VARCHAR(255) NOT NULL, 818 price DECIMAL(10,2) DEFAULT 0.00 NOT NULL, 819 820 vendor_id INT REFERENCES vendors (id), 821 822 status VARCHAR(128) DEFAULT 'inactive' NOT NULL 823 CHECK(status IN ('inactive', 'active', 'defunct')), 824 825 rint1 INT, 826 bint1 INT8 DEFAULT 9223372036854775800, 827 828 date_created DATETIME YEAR TO SECOND, 829 release_date DATETIME YEAR TO SECOND, 830 release_day DATETIME YEAR TO MONTH, 831 832 UNIQUE(name) 833) 834EOF 835 836 $dbh->do(<<"EOF"); 837CREATE TABLE prices 838( 839 id SERIAL NOT NULL PRIMARY KEY, 840 product_id INT NOT NULL REFERENCES products (id), 841 region CHAR(2) DEFAULT 'US' NOT NULL, 842 price DECIMAL(10,2) DEFAULT 0.00 NOT NULL, 843 844 UNIQUE(product_id, region) 845) 846EOF 847 848 $dbh->do(<<"EOF"); 849CREATE TABLE colors 850( 851 id SERIAL NOT NULL PRIMARY KEY, 852 name VARCHAR(255) NOT NULL, 853 854 UNIQUE(name) 855) 856EOF 857 858 $dbh->do(<<"EOF"); 859CREATE TABLE products_colors 860( 861 product_id INT NOT NULL REFERENCES products (id), 862 color_id INT NOT NULL REFERENCES colors (id), 863 864 PRIMARY KEY(product_id, color_id) 865) 866EOF 867 868 $dbh->disconnect; 869 } 870 871 # 872 # SQLite 873 # 874 875 eval 876 { 877 $dbh = Rose::DB->new('sqlite_admin')->retain_dbh() 878 or die Rose::DB->error; 879 }; 880 881 if(!$@ && $dbh) 882 { 883 $Have{'sqlite'} = 1; 884 885 # Drop existing tables, ignoring errors 886 { 887 local $dbh->{'RaiseError'} = 0; 888 local $dbh->{'PrintError'} = 0; 889 890 $dbh->do('DROP TABLE no_pk_test'); 891 $dbh->do('DROP TABLE products_colors'); 892 $dbh->do('DROP TABLE colors'); 893 $dbh->do('DROP TABLE prices'); 894 $dbh->do('DROP TABLE products'); 895 $dbh->do('DROP TABLE vendors'); 896 } 897 898 $dbh->do(<<"EOF"); 899CREATE TABLE 'no_pk_test' 900( 901 id INT NOT NULL, 902 name VARCHAR(255) NOT NULL, 903 904 UNIQUE(name) 905) 906EOF 907 908 $dbh->do(<<"EOF"); 909CREATE TABLE "vendors" 910( 911 "id" INTEGER PRIMARY KEY AUTOINCREMENT, 912 name VARCHAR(255) NOT NULL, 913 914 UNIQUE("name") 915) 916EOF 917 918 $dbh->do(<<"EOF"); 919CREATE TABLE products 920( 921 id INTEGER PRIMARY KEY AUTOINCREMENT, 922 name VARCHAR(255) NOT NULL, 923 price DECIMAL(10,2) DEFAULT 0.00 NOT NULL, 924 925 vendor_id INT REFERENCES vendors (id), 926 927 status VARCHAR(128) DEFAULT 'inactive' NOT NULL 928 CHECK(status IN ('inactive', 'active', 'defunct')), 929 930 date_created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 931 release_date DATETIME, 932 933 UNIQUE('name') 934) 935EOF 936 937 $dbh->do(<<"EOF"); 938CREATE TABLE prices 939( 940 id INTEGER PRIMARY KEY AUTOINCREMENT, 941 product_id INT NOT NULL REFERENCES products (id), 942 region CHAR(2) DEFAULT 'US' NOT NULL, 943 price DECIMAL(10,2) DEFAULT 0.00 NOT NULL, 944 945 UNIQUE(product_id, region) 946) 947EOF 948 949 $dbh->do(<<"EOF"); 950CREATE TABLE colors 951( 952 id INTEGER PRIMARY KEY AUTOINCREMENT, 953 name VARCHAR(255) NOT NULL, 954 955 UNIQUE(name) 956) 957EOF 958 959 $dbh->do(<<"EOF"); 960CREATE TABLE products_colors 961( 962 product_id INT NOT NULL REFERENCES products (id), 963 color_id INT NOT NULL REFERENCES colors (id), 964 965 PRIMARY KEY(product_id, color_id) 966) 967EOF 968 969 $dbh->disconnect; 970 } 971 972 # 973 # Oracle 974 # 975 976 eval 977 { 978 $dbh = Rose::DB->new('oracle_admin')->retain_dbh() 979 or die Rose::DB->error; 980 }; 981 982 if(!$@ && $dbh) 983 { 984 $Have{'oracle'} = 1; 985 986 # Drop existing tables, ignoring errors 987 { 988 local $dbh->{'RaiseError'} = 0; 989 local $dbh->{'PrintError'} = 0; 990 991 $dbh->do('DROP TABLE no_pk_test'); 992 $dbh->do('DROP TABLE products_colors'); 993 $dbh->do('DROP TABLE colors'); 994 $dbh->do('DROP TABLE prices'); 995 $dbh->do('DROP TABLE products'); 996 $dbh->do('DROP TABLE vendors'); 997 $dbh->do('DROP SEQUENCE vendors_id_seq'); 998 $dbh->do('DROP SEQUENCE products_id_seq'); 999 $dbh->do('DROP SEQUENCE prices_id_seq'); 1000 $dbh->do('DROP SEQUENCE colors_id_seq'); 1001 } 1002 1003 $dbh->do(<<"EOF"); 1004CREATE TABLE no_pk_test 1005( 1006 id INT NOT NULL, 1007 name VARCHAR(255) NOT NULL, 1008 1009 CONSTRAINT no_pk_test_name UNIQUE (name) 1010) 1011EOF 1012 1013 $dbh->do(<<"EOF"); 1014CREATE TABLE vendors 1015( 1016 id INT NOT NULL PRIMARY KEY, 1017 name VARCHAR(255) NOT NULL, 1018 1019 CONSTRAINT vendors_name UNIQUE (name) 1020) 1021EOF 1022 1023 $dbh->do('CREATE SEQUENCE vendors_id_seq'); 1024 $dbh->do(<<"EOF"); 1025CREATE OR REPLACE TRIGGER vendors_insert BEFORE INSERT ON vendors 1026FOR EACH ROW 1027BEGIN 1028 SELECT NVL(:new.id, vendors_id_seq.nextval) 1029 INTO :new.id FROM dual; 1030END; 1031EOF 1032 1033 $dbh->do(<<"EOF"); 1034CREATE TABLE products 1035( 1036 id INT NOT NULL PRIMARY KEY, 1037 name VARCHAR(255) NOT NULL, 1038 price DECIMAL(10,2) DEFAULT 0.00 NOT NULL, 1039 1040 vendor_id INT, 1041 1042 status VARCHAR(128) DEFAULT 'inactive' NOT NULL 1043 CHECK(status IN ('inactive', 'active', 'defunct')), 1044 1045 rint1 INT, 1046 bint1 NUMBER(20) DEFAULT 9223372036854775800, 1047 1048 date_created TIMESTAMP, 1049 1050 CONSTRAINT products_name UNIQUE (name), 1051 CONSTRAINT products_vendor_id_fk FOREIGN KEY (vendor_id) REFERENCES vendors (id) 1052) 1053EOF 1054 1055 $dbh->do('CREATE SEQUENCE products_id_seq'); 1056 $dbh->do(<<"EOF"); 1057CREATE OR REPLACE TRIGGER products_insert BEFORE INSERT ON products 1058FOR EACH ROW 1059BEGIN 1060 SELECT NVL(:new.id, products_id_seq.nextval) 1061 INTO :new.id FROM dual; 1062END; 1063EOF 1064 1065 $dbh->do(<<"EOF"); 1066CREATE TABLE prices 1067( 1068 id INT NOT NULL PRIMARY KEY, 1069 product_id INT NOT NULL, 1070 region CHAR(2) DEFAULT 'US' NOT NULL, 1071 price NUMBER(10,2) DEFAULT 0.00 NOT NULL, 1072 1073 CONSTRAINT prices_uk UNIQUE (product_id, region), 1074 CONSTRAINT prices_product_id_fk FOREIGN KEY (product_id) REFERENCES products (id) 1075) 1076EOF 1077 1078 $dbh->do('CREATE SEQUENCE prices_id_seq'); 1079 $dbh->do(<<"EOF"); 1080CREATE OR REPLACE TRIGGER prices_insert BEFORE INSERT ON prices 1081FOR EACH ROW 1082BEGIN 1083 SELECT NVL(:new.id, prices_id_seq.nextval) 1084 INTO :new.id FROM dual; 1085END; 1086EOF 1087 1088 $dbh->do(<<"EOF"); 1089CREATE TABLE colors 1090( 1091 id INT NOT NULL PRIMARY KEY, 1092 name VARCHAR(255) NOT NULL, 1093 1094 CONSTRAINT colors_name UNIQUE (name) 1095) 1096EOF 1097 1098 $dbh->do('CREATE SEQUENCE colors_id_seq'); 1099 $dbh->do(<<"EOF"); 1100CREATE OR REPLACE TRIGGER colors_insert BEFORE INSERT ON colors 1101FOR EACH ROW 1102BEGIN 1103 SELECT NVL(:new.id, colors_id_seq.nextval) 1104 INTO :new.id FROM dual; 1105END; 1106EOF 1107 1108 $dbh->do(<<"EOF"); 1109CREATE TABLE products_colors 1110( 1111 product_id INT NOT NULL, 1112 color_id INT NOT NULL, 1113 1114 CONSTRAINT products_colors_pk PRIMARY KEY (product_id, color_id), 1115 CONSTRAINT products_colors_product_id_fk FOREIGN KEY (product_id) REFERENCES products (id), 1116 CONSTRAINT products_colors_color_id_fk FOREIGN KEY (color_id) REFERENCES colors (id) 1117) 1118EOF 1119 1120 $dbh->disconnect; 1121 } 1122} 1123 1124END 1125{ 1126 # Delete test table 1127 1128 if($Have{'pg'}) 1129 { 1130 # PostgreSQL 1131 my $dbh = Rose::DB->new('pg_admin')->retain_dbh() 1132 or die Rose::DB->error; 1133 1134 $dbh->do('DROP TABLE no_pk_test CASCADE'); 1135 $dbh->do('DROP TABLE no_pk_test2 CASCADE'); 1136 $dbh->do('DROP TABLE products_colors CASCADE'); 1137 $dbh->do('DROP TABLE colors CASCADE'); 1138 $dbh->do('DROP TABLE prices CASCADE'); 1139 $dbh->do('DROP TABLE products CASCADE'); 1140 $dbh->do('DROP TABLE vendors CASCADE'); 1141 1142 $dbh->do('DROP TABLE Rose_db_object_private.no_pk_test CASCADE'); 1143 $dbh->do('DROP TABLE Rose_db_object_private.no_pk_test2 CASCADE'); 1144 $dbh->do('DROP TABLE Rose_db_object_private.products_colors CASCADE'); 1145 $dbh->do('DROP TABLE Rose_db_object_private.colors CASCADE'); 1146 $dbh->do('DROP TABLE Rose_db_object_private.prices CASCADE'); 1147 $dbh->do('DROP TABLE Rose_db_object_private.products CASCADE'); 1148 $dbh->do('DROP TABLE Rose_db_object_private.vendors CASCADE'); 1149 1150 $dbh->do('DROP SCHEMA Rose_db_object_private CASCADE'); 1151 1152 $dbh->disconnect; 1153 } 1154 1155 if($Have{'mysql'}) 1156 { 1157 # MySQL 1158 my $dbh = Rose::DB->new('mysql_admin')->retain_dbh() 1159 or die Rose::DB->error; 1160 1161 $dbh->do('DROP TABLE no_pk_test CASCADE'); 1162 $dbh->do('DROP TABLE products_colors CASCADE'); 1163 $dbh->do('DROP TABLE colors CASCADE'); 1164 $dbh->do('DROP TABLE prices CASCADE'); 1165 $dbh->do('DROP TABLE products CASCADE'); 1166 $dbh->do('DROP TABLE vendors CASCADE'); 1167 $dbh->do('DROP TABLE `read` CASCADE'); 1168 1169 $dbh->disconnect; 1170 } 1171 1172 if($Have{'informix'}) 1173 { 1174 # Informix 1175 my $dbh = Rose::DB->new('informix_admin')->retain_dbh() 1176 or die Rose::DB->error; 1177 1178 $dbh->do('DROP TABLE no_pk_test CASCADE'); 1179 $dbh->do('DROP TABLE products_colors CASCADE'); 1180 $dbh->do('DROP TABLE colors CASCADE'); 1181 $dbh->do('DROP TABLE prices CASCADE'); 1182 $dbh->do('DROP TABLE products CASCADE'); 1183 $dbh->do('DROP TABLE vendors CASCADE'); 1184 1185 $dbh->disconnect; 1186 } 1187 1188 if($Have{'sqlite'}) 1189 { 1190 # Informix 1191 my $dbh = Rose::DB->new('sqlite_admin')->retain_dbh() 1192 or die Rose::DB->error; 1193 1194 $dbh->do('DROP TABLE no_pk_test'); 1195 $dbh->do('DROP TABLE products_colors'); 1196 $dbh->do('DROP TABLE colors'); 1197 $dbh->do('DROP TABLE prices'); 1198 $dbh->do('DROP TABLE products'); 1199 $dbh->do('DROP TABLE vendors'); 1200 1201 $dbh->disconnect; 1202 } 1203 1204 if($Have{'oracle'}) 1205 { 1206 # Informix 1207 my $dbh = Rose::DB->new('oracle_admin')->retain_dbh() 1208 or die Rose::DB->error; 1209 1210 $dbh->do('DROP TABLE no_pk_test'); 1211 $dbh->do('DROP TABLE products_colors'); 1212 $dbh->do('DROP TABLE colors'); 1213 $dbh->do('DROP TABLE prices'); 1214 $dbh->do('DROP TABLE products'); 1215 $dbh->do('DROP TABLE vendors'); 1216 $dbh->do('DROP SEQUENCE vendors_id_seq'); 1217 $dbh->do('DROP SEQUENCE products_id_seq'); 1218 $dbh->do('DROP SEQUENCE prices_id_seq'); 1219 $dbh->do('DROP SEQUENCE colors_id_seq'); 1220 1221 $dbh->disconnect; 1222 } 1223} 1224