1#!/usr/bin/perl -w 2 3use strict; 4 5use Test::More tests => 51; 6 7BEGIN 8{ 9 require 't/test-lib.pl'; 10 use_ok('Rose::DB::Object'); 11 use_ok('Rose::DB::Object::Manager'); 12 use_ok('Rose::DB::Object::MakeMethods::Generic'); 13} 14 15our($HAVE_PG, $HAVE_MYSQL, $HAVE_INFORMIX, $HAVE_SQLITE); 16 17# 18# PostgreSQL 19# 20 21SKIP: foreach my $db_type (qw(pg)) #pg_with_schema 22{ 23 skip("PostgreSQL tests", 12) unless($HAVE_PG); 24 25 Rose::DB->default_type($db_type); 26 27 my $o = MyPgObject->new(id => 1, 28 name => 'John', 29 fkone => 1, 30 fk2 => 2, 31 fk3 => 3); 32 33 ok($o->save, "object save() 1 - $db_type"); 34 35 my $fo = MyPgOtherObject->new(id => 1, 36 name => 'Foo', 37 k1 => 1, 38 ktwo => 2, 39 k3 => 3); 40 41 ok($fo->save, "object save() 2 - $db_type"); 42 43 $fo = MyPgOtherObject->new(id => 2, 44 name => 'Bar', 45 k1 => 1, 46 ktwo => 2, 47 k3 => 3); 48 49 ok($fo->save, "object save() 3 - $db_type"); 50 51 $fo = MyPgOtherObject->new(id => 3, 52 name => 'bar 2', 53 k1 => 1, 54 ktwo => 2, 55 k3 => 3); 56 57 ok($fo->save, "object save() 4 - $db_type"); 58 59 $fo = MyPgOtherObject->new(id => 4, 60 name => 'Baz', 61 k1 => 2, 62 ktwo => 3, 63 k3 => 4); 64 65 ok($fo->save, "object save() 5 - $db_type"); 66 67 my $objs = $o->other_objs; 68 69 ok($objs && ref $objs eq 'ARRAY' && @$objs == 3, "get objects 1 - $db_type"); 70 71 is($objs->[0]->id, 2, "get objects 2 - $db_type"); 72 is($objs->[1]->id, 3, "get objects 3 - $db_type"); 73 is($objs->[2]->id, 1, "get objects 4 - $db_type"); 74 75 $o->fkone(2); 76 $o->fk2(3); 77 $o->fk3(4); 78 $o->other_objs(undef); 79 80 $objs = $o->other_objs; 81 82 ok($objs && ref $objs eq 'ARRAY' && @$objs == 1, "get objects 5 - $db_type"); 83 84 is($objs->[0]->id, 4, "get objects 6 - $db_type"); 85 86 $o->fkone(7); 87 $o->fk2(8); 88 $o->fk3(9); 89 $o->other_objs(undef); 90 91 $objs = $o->other_objs; 92 93 ok($objs && ref $objs eq 'ARRAY' && @$objs == 0, "get objects 7 - $db_type"); 94} 95 96# 97# MySQL 98# 99 100SKIP: foreach my $db_type ('mysql') 101{ 102 skip("MySQL tests", 12) unless($HAVE_MYSQL); 103 104 Rose::DB->default_type($db_type); 105 106 my $o = MyMySQLObject->new(id => 1, 107 name => 'John', 108 fkone => 1, 109 fk2 => 2, 110 fk3 => 3); 111 112 ok($o->save, "object save() 1 - $db_type"); 113 114 my $fo = MyMySQLOtherObject->new(id => 1, 115 name => 'Foo', 116 k1 => 1, 117 ktwo => 2, 118 k3 => 3); 119 120 ok($fo->save, "object save() 2 - $db_type"); 121 122 $fo = MyMySQLOtherObject->new(id => 2, 123 name => 'Bar', 124 k1 => 1, 125 ktwo => 2, 126 k3 => 3); 127 128 ok($fo->save, "object save() 3 - $db_type"); 129 130 $fo = MyMySQLOtherObject->new(id => 3, 131 name => 'bar 2', 132 k1 => 1, 133 ktwo => 2, 134 k3 => 3); 135 136 ok($fo->save, "object save() 4 - $db_type"); 137 138 $fo = MyMySQLOtherObject->new(id => 4, 139 name => 'Baz', 140 k1 => 2, 141 ktwo => 3, 142 k3 => 4); 143 144 ok($fo->save, "object save() 5 - $db_type"); 145 146 my $objs = $o->other_objs; 147 148 ok($objs && ref $objs eq 'ARRAY' && @$objs == 3, "get objects 1 - $db_type"); 149 150 is($objs->[0]->id, 2, "get objects 2 - $db_type"); 151 is($objs->[1]->id, 3, "get objects 3 - $db_type"); 152 is($objs->[2]->id, 1, "get objects 4 - $db_type"); 153 154 $o->fkone(2); 155 $o->fk2(3); 156 $o->fk3(4); 157 $o->other_objs(undef); 158 159 $objs = $o->other_objs; 160 161 ok($objs && ref $objs eq 'ARRAY' && @$objs == 1, "get objects 5 - $db_type"); 162 163 is($objs->[0]->id, 4, "get objects 6 - $db_type"); 164 165 $o->fkone(7); 166 $o->fk2(8); 167 $o->fk3(9); 168 $o->other_objs(undef); 169 170 $objs = $o->other_objs; 171 172 ok($objs && ref $objs eq 'ARRAY' && @$objs == 0, "get objects 7 - $db_type"); 173} 174 175# 176# Informix 177# 178 179SKIP: foreach my $db_type (qw(informix)) 180{ 181 skip("Informix tests", 12) unless($HAVE_INFORMIX); 182 183 Rose::DB->default_type($db_type); 184 185 my $o = MyInformixObject->new(id => 1, 186 name => 'John', 187 fkone => 1, 188 fk2 => 2, 189 fk3 => 3); 190 191 ok($o->save, "object save() 1 - $db_type"); 192 193 my $fo = MyInformixOtherObject->new(id => 1, 194 name => 'Foo', 195 k1 => 1, 196 ktwo => 2, 197 k3 => 3); 198 199 ok($fo->save, "object save() 2 - $db_type"); 200 201 $fo = MyInformixOtherObject->new(id => 2, 202 name => 'Bar', 203 k1 => 1, 204 ktwo => 2, 205 k3 => 3); 206 207 ok($fo->save, "object save() 3 - $db_type"); 208 209 $fo = MyInformixOtherObject->new(id => 3, 210 name => 'bar 2', 211 k1 => 1, 212 ktwo => 2, 213 k3 => 3); 214 215 ok($fo->save, "object save() 4 - $db_type"); 216 217 $fo = MyInformixOtherObject->new(id => 4, 218 name => 'Baz', 219 k1 => 2, 220 ktwo => 3, 221 k3 => 4); 222 223 ok($fo->save, "object save() 5 - $db_type"); 224 225 my $objs = $o->other_objs; 226 227 ok($objs && ref $objs eq 'ARRAY' && @$objs == 3, "get objects 1 - $db_type"); 228 229 is($objs->[0]->id, 2, "get objects 2 - $db_type"); 230 is($objs->[1]->id, 3, "get objects 3 - $db_type"); 231 is($objs->[2]->id, 1, "get objects 4 - $db_type"); 232 233 $o->fkone(2); 234 $o->fk2(3); 235 $o->fk3(4); 236 $o->other_objs(undef); 237 238 $objs = $o->other_objs; 239 240 ok($objs && ref $objs eq 'ARRAY' && @$objs == 1, "get objects 5 - $db_type"); 241 242 is($objs->[0]->id, 4, "get objects 6 - $db_type"); 243 244 $o->fkone(7); 245 $o->fk2(8); 246 $o->fk3(9); 247 $o->other_objs(undef); 248 249 $objs = $o->other_objs; 250 251 ok($objs && ref $objs eq 'ARRAY' && @$objs == 0, "get objects 7 - $db_type"); 252} 253 254# 255# SQLite 256# 257 258SKIP: foreach my $db_type (qw(sqlite)) 259{ 260 skip("SQLite tests", 12) unless($HAVE_SQLITE); 261 262 Rose::DB->default_type($db_type); 263 264 my $o = MySQLiteObject->new(id => 1, 265 name => 'John', 266 fkone => 1, 267 fk2 => 2, 268 fk3 => 3); 269 270 ok($o->save, "object save() 1 - $db_type"); 271 272 my $fo = MySQLiteOtherObject->new(id => 1, 273 name => 'Foo', 274 k1 => 1, 275 ktwo => 2, 276 k3 => 3); 277 278 ok($fo->save, "object save() 2 - $db_type"); 279 280 $fo = MySQLiteOtherObject->new(id => 2, 281 name => 'Bar', 282 k1 => 1, 283 ktwo => 2, 284 k3 => 3); 285 286 ok($fo->save, "object save() 3 - $db_type"); 287 288 $fo = MySQLiteOtherObject->new(id => 3, 289 name => 'bar 2', 290 k1 => 1, 291 ktwo => 2, 292 k3 => 3); 293 294 ok($fo->save, "object save() 4 - $db_type"); 295 296 $fo = MySQLiteOtherObject->new(id => 4, 297 name => 'Baz', 298 k1 => 2, 299 ktwo => 3, 300 k3 => 4); 301 302 ok($fo->save, "object save() 5 - $db_type"); 303 304 my $objs = $o->other_objs; 305 306 ok($objs && ref $objs eq 'ARRAY' && @$objs == 3, "get objects 1 - $db_type"); 307 308 is($objs->[0]->id, 2, "get objects 2 - $db_type"); 309 is($objs->[1]->id, 3, "get objects 3 - $db_type"); 310 is($objs->[2]->id, 1, "get objects 4 - $db_type"); 311 312 $o->fkone(2); 313 $o->fk2(3); 314 $o->fk3(4); 315 $o->other_objs(undef); 316 317 $objs = $o->other_objs; 318 319 ok($objs && ref $objs eq 'ARRAY' && @$objs == 1, "get objects 5 - $db_type"); 320 321 is($objs->[0]->id, 4, "get objects 6 - $db_type"); 322 323 $o->fkone(7); 324 $o->fk2(8); 325 $o->fk3(9); 326 $o->other_objs(undef); 327 328 $objs = $o->other_objs; 329 330 ok($objs && ref $objs eq 'ARRAY' && @$objs == 0, "get objects 7 - $db_type"); 331} 332 333BEGIN 334{ 335 # 336 # PostgreSQL 337 # 338 339 my $dbh; 340 341 eval 342 { 343 $dbh = Rose::DB->new('pg_admin')->retain_dbh() 344 or die Rose::DB->error; 345 }; 346 347 if(!$@ && $dbh) 348 { 349 our $HAVE_PG = 1; 350 351 # Drop existing table and create schema, ignoring errors 352 { 353 local $dbh->{'RaiseError'} = 0; 354 local $dbh->{'PrintError'} = 0; 355 $dbh->do('DROP TABLE rose_db_object_test'); 356 $dbh->do('DROP TABLE rose_db_object_other'); 357 } 358 359 $dbh->do(<<"EOF"); 360CREATE TABLE rose_db_object_other 361( 362 id INT NOT NULL PRIMARY KEY, 363 k1 INT NOT NULL, 364 k2 INT NOT NULL, 365 k3 INT NOT NULL, 366 name VARCHAR(32) 367) 368EOF 369 370 # Create test foreign subclass 371 372 package MyPgOtherObject; 373 374 our @ISA = qw(Rose::DB::Object); 375 376 sub init_db { Rose::DB->new('pg') } 377 378 MyPgOtherObject->meta->table('rose_db_object_other'); 379 380 MyPgOtherObject->meta->columns 381 ( 382 id => { primary_key => 1 }, 383 name => { type => 'varchar'}, 384 k1 => { type => 'int' }, 385 k2 => { type => 'int' }, 386 k3 => { type => 'int' }, 387 ); 388 389 MyPgOtherObject->meta->alias_column(k2 => 'ktwo'); 390 391 MyPgOtherObject->meta->initialize; 392 393 $dbh->do(<<"EOF"); 394CREATE TABLE rose_db_object_test 395( 396 id INT NOT NULL PRIMARY KEY, 397 name VARCHAR(32) NOT NULL, 398 fk1 INT, 399 fk2 INT, 400 fk3 INT 401) 402EOF 403 404 $dbh->disconnect; 405 406 # Create test subclass 407 408 package MyPgObject; 409 410 our @ISA = qw(Rose::DB::Object); 411 412 sub init_db { Rose::DB->new('pg') } 413 414 MyPgObject->meta->table('rose_db_object_test'); 415 416 MyPgObject->meta->columns 417 ( 418 'name', 419 id => { primary_key => 1 }, 420 fk1 => { type => 'int' }, 421 fk2 => { type => 'int' }, 422 fk3 => { type => 'int' }, 423 ); 424 425 MyPgObject->meta->alias_column(fk1 => 'fkone'); 426 MyPgObject->meta->initialize; 427 428 Rose::DB::Object::MakeMethods::Generic->import 429 ( 430 objects_by_key => 431 [ 432 other_objs => 433 { 434 class => 'MyPgOtherObject', 435 key_columns => 436 { 437 fk1 => 'k1', 438 fk2 => 'k2', 439 fk3 => 'k3', 440 }, 441 manager_args => { sort_by => 'LOWER(name)' }, 442 query_args => [ name => { ne => 'foo' } ], 443 }, 444 ] 445 ); 446 } 447 448 # 449 # MySQL 450 # 451 452 eval 453 { 454 $dbh = Rose::DB->new('mysql_admin')->retain_dbh() 455 or die Rose::DB->error; 456 }; 457 458 if(!$@ && $dbh) 459 { 460 our $HAVE_MYSQL = 1; 461 462 # Drop existing table and create schema, ignoring errors 463 { 464 local $dbh->{'RaiseError'} = 0; 465 local $dbh->{'PrintError'} = 0; 466 $dbh->do('DROP TABLE rose_db_object_test'); 467 $dbh->do('DROP TABLE rose_db_object_other'); 468 } 469 470 $dbh->do(<<"EOF"); 471CREATE TABLE rose_db_object_other 472( 473 id INT NOT NULL PRIMARY KEY, 474 k1 INT NOT NULL, 475 k2 INT NOT NULL, 476 k3 INT NOT NULL, 477 name VARCHAR(32) 478) 479EOF 480 481 # Create test foreign subclass 482 483 package MyMySQLOtherObject; 484 485 our @ISA = qw(Rose::DB::Object); 486 487 sub init_db { Rose::DB->new('mysql') } 488 489 MyMySQLOtherObject->meta->table('rose_db_object_other'); 490 491 MyMySQLOtherObject->meta->columns 492 ( 493 id => { primary_key => 1 }, 494 name => { type => 'varchar'}, 495 k1 => { type => 'int' }, 496 k2 => { type => 'int' }, 497 k3 => { type => 'int' }, 498 ); 499 500 MyMySQLOtherObject->meta->alias_column(k2 => 'ktwo'); 501 502 MyMySQLOtherObject->meta->initialize; 503 504 $dbh->do(<<"EOF"); 505CREATE TABLE rose_db_object_test 506( 507 id INT NOT NULL PRIMARY KEY, 508 name VARCHAR(32) NOT NULL, 509 fk1 INT, 510 fk2 INT, 511 fk3 INT 512) 513EOF 514 515 $dbh->disconnect; 516 517 # Create test subclass 518 519 package MyMySQLObject; 520 521 our @ISA = qw(Rose::DB::Object); 522 523 sub init_db { Rose::DB->new('mysql') } 524 525 MyMySQLObject->meta->table('rose_db_object_test'); 526 527 MyMySQLObject->meta->columns 528 ( 529 'name', 530 id => { primary_key => 1 }, 531 fk1 => { type => 'int' }, 532 fk2 => { type => 'int' }, 533 fk3 => { type => 'int' }, 534 ); 535 536 MyMySQLObject->meta->alias_column(fk1 => 'fkone'); 537 MyMySQLObject->meta->initialize; 538 539 Rose::DB::Object::MakeMethods::Generic->import 540 ( 541 objects_by_key => 542 [ 543 other_objs => 544 { 545 class => 'MyMySQLOtherObject', 546 key_columns => 547 { 548 fk1 => 'k1', 549 fk2 => 'k2', 550 fk3 => 'k3', 551 }, 552 manager_args => { sort_by => 'LOWER(name)' }, 553 }, 554 ] 555 ); 556 } 557 558 # 559 # Informix 560 # 561 562 eval 563 { 564 $dbh = Rose::DB->new('informix_admin')->retain_dbh() 565 or die Rose::DB->error; 566 }; 567 568 if(!$@ && $dbh) 569 { 570 our $HAVE_INFORMIX = 1; 571 572 # Drop existing table and create schema, ignoring errors 573 { 574 local $dbh->{'RaiseError'} = 0; 575 local $dbh->{'PrintError'} = 0; 576 $dbh->do('DROP TABLE rose_db_object_test'); 577 $dbh->do('DROP TABLE rose_db_object_other'); 578 } 579 580 $dbh->do(<<"EOF"); 581CREATE TABLE rose_db_object_other 582( 583 id INT NOT NULL PRIMARY KEY, 584 k1 INT NOT NULL, 585 k2 INT NOT NULL, 586 k3 INT NOT NULL, 587 name VARCHAR(32) 588) 589EOF 590 591 # Create test foreign subclass 592 593 package MyInformixOtherObject; 594 595 our @ISA = qw(Rose::DB::Object); 596 597 sub init_db { Rose::DB->new('informix') } 598 599 MyInformixOtherObject->meta->table('rose_db_object_other'); 600 601 MyInformixOtherObject->meta->columns 602 ( 603 id => { primary_key => 1 }, 604 name => { type => 'varchar'}, 605 k1 => { type => 'int' }, 606 k2 => { type => 'int' }, 607 k3 => { type => 'int' }, 608 ); 609 610 MyInformixOtherObject->meta->alias_column(k2 => 'ktwo'); 611 612 MyInformixOtherObject->meta->initialize; 613 614 $dbh->do(<<"EOF"); 615CREATE TABLE rose_db_object_test 616( 617 id INT NOT NULL PRIMARY KEY, 618 name VARCHAR(32) NOT NULL, 619 fk1 INT, 620 fk2 INT, 621 fk3 INT 622) 623EOF 624 625 $dbh->disconnect; 626 627 # Create test subclass 628 629 package MyInformixObject; 630 631 our @ISA = qw(Rose::DB::Object); 632 633 sub init_db { Rose::DB->new('informix') } 634 635 MyInformixObject->meta->table('rose_db_object_test'); 636 637 MyInformixObject->meta->columns 638 ( 639 'name', 640 id => { primary_key => 1 }, 641 fk1 => { type => 'int' }, 642 fk2 => { type => 'int' }, 643 fk3 => { type => 'int' }, 644 ); 645 646 MyInformixObject->meta->alias_column(fk1 => 'fkone'); 647 MyInformixObject->meta->initialize; 648 649 Rose::DB::Object::MakeMethods::Generic->import 650 ( 651 objects_by_key => 652 [ 653 other_objs => 654 { 655 class => 'MyInformixOtherObject', 656 key_columns => 657 { 658 fk1 => 'k1', 659 fk2 => 'k2', 660 fk3 => 'k3', 661 }, 662 manager_args => { sort_by => 'LOWER(name)' }, 663 }, 664 ] 665 ); 666 } 667 668 # 669 # SQLite 670 # 671 672 eval 673 { 674 $dbh = Rose::DB->new('sqlite_admin')->retain_dbh() 675 or die Rose::DB->error; 676 }; 677 678 if(!$@ && $dbh) 679 { 680 our $HAVE_SQLITE = 1; 681 682 # Drop existing table and create schema, ignoring errors 683 { 684 local $dbh->{'RaiseError'} = 0; 685 local $dbh->{'PrintError'} = 0; 686 $dbh->do('DROP TABLE rose_db_object_test'); 687 $dbh->do('DROP TABLE rose_db_object_other'); 688 } 689 690 $dbh->do(<<"EOF"); 691CREATE TABLE rose_db_object_other 692( 693 id INT NOT NULL PRIMARY KEY, 694 k1 INT NOT NULL, 695 k2 INT NOT NULL, 696 k3 INT NOT NULL, 697 name VARCHAR(32) 698) 699EOF 700 701 # Create test foreign subclass 702 703 package MySQLiteOtherObject; 704 705 our @ISA = qw(Rose::DB::Object); 706 707 sub init_db { Rose::DB->new('sqlite') } 708 709 MySQLiteOtherObject->meta->table('rose_db_object_other'); 710 711 MySQLiteOtherObject->meta->columns 712 ( 713 id => { primary_key => 1 }, 714 name => { type => 'varchar'}, 715 k1 => { type => 'int' }, 716 k2 => { type => 'int' }, 717 k3 => { type => 'int' }, 718 ); 719 720 MySQLiteOtherObject->meta->alias_column(k2 => 'ktwo'); 721 722 MySQLiteOtherObject->meta->initialize; 723 724 $dbh->do(<<"EOF"); 725CREATE TABLE rose_db_object_test 726( 727 id INT NOT NULL PRIMARY KEY, 728 name VARCHAR(32) NOT NULL, 729 fk1 INT, 730 fk2 INT, 731 fk3 INT 732) 733EOF 734 735 $dbh->disconnect; 736 737 # Create test subclass 738 739 package MySQLiteObject; 740 741 our @ISA = qw(Rose::DB::Object); 742 743 sub init_db { Rose::DB->new('sqlite') } 744 745 MySQLiteObject->meta->table('rose_db_object_test'); 746 747 MySQLiteObject->meta->columns 748 ( 749 'name', 750 id => { primary_key => 1 }, 751 fk1 => { type => 'int' }, 752 fk2 => { type => 'int' }, 753 fk3 => { type => 'int' }, 754 ); 755 756 MySQLiteObject->meta->alias_column(fk1 => 'fkone'); 757 MySQLiteObject->meta->initialize; 758 759 Rose::DB::Object::MakeMethods::Generic->import 760 ( 761 objects_by_key => 762 [ 763 other_objs => 764 { 765 class => 'MySQLiteOtherObject', 766 key_columns => 767 { 768 fk1 => 'k1', 769 fk2 => 'k2', 770 fk3 => 'k3', 771 }, 772 manager_args => { sort_by => 'LOWER(name)' }, 773 }, 774 ] 775 ); 776 } 777} 778 779END 780{ 781 # Delete test tables 782 783 if($HAVE_PG) 784 { 785 # PostgreSQL 786 my $dbh = Rose::DB->new('pg_admin')->retain_dbh() 787 or die Rose::DB->error; 788 789 $dbh->do('DROP TABLE rose_db_object_test'); 790 $dbh->do('DROP TABLE rose_db_object_other'); 791 792 $dbh->disconnect; 793 } 794 795 if($HAVE_MYSQL) 796 { 797 # MySQL 798 my $dbh = Rose::DB->new('mysql_admin')->retain_dbh() 799 or die Rose::DB->error; 800 801 $dbh->do('DROP TABLE rose_db_object_test'); 802 $dbh->do('DROP TABLE rose_db_object_other'); 803 804 $dbh->disconnect; 805 } 806 807 if($HAVE_INFORMIX) 808 { 809 # Informix 810 my $dbh = Rose::DB->new('informix_admin')->retain_dbh() 811 or die Rose::DB->error; 812 813 $dbh->do('DROP TABLE rose_db_object_test'); 814 $dbh->do('DROP TABLE rose_db_object_other'); 815 816 $dbh->disconnect; 817 } 818 819 if($HAVE_SQLITE) 820 { 821 # Informix 822 my $dbh = Rose::DB->new('sqlite_admin')->retain_dbh() 823 or die Rose::DB->error; 824 825 $dbh->do('DROP TABLE rose_db_object_test'); 826 $dbh->do('DROP TABLE rose_db_object_other'); 827 828 $dbh->disconnect; 829 } 830} 831 832