1#!/usr/bin/perl -w 2 3use strict; 4 5use Test::More tests => 199; 6 7BEGIN 8{ 9 require 't/test-lib.pl'; 10 use_ok('Rose::DB::Object'); 11} 12 13our($PG_HAS_CHKPASS, $HAVE_PG, $HAVE_MYSQL, $HAVE_INFORMIX, $HAVE_SQLITE); 14 15# 16# PostgreSQL 17# 18 19SKIP: foreach my $db_type ('pg') 20{ 21 skip("PostgreSQL tests", 60) unless($HAVE_PG); 22 23 Rose::DB->default_type($db_type); 24 25 my $o = MyPgObject->new(name => 'John'); 26 27 ok(ref $o && $o->isa('MyPgObject'), "new() 1 - $db_type"); 28 29 $o->flag2('true'); 30 $o->date_created('now'); 31 $o->last_modified($o->date_created); 32 $o->save_col(7); 33 $o->other2_obj(7); 34 35 ok($o->save, "save() 1 - $db_type"); 36 ok($o->load, "load() 1 - $db_type"); 37 38 is($o->other2_obj->name, 'def', "single column foreign key 1 - $db_type"); 39 40 my $old_fks = $o->fks; 41 42 $o->other2_obj(undef); 43 44 $o->fks(0); 45 eval { $o->other2_obj }; 46 47 ok($@, "fatal referential_integrity - $db_type"); 48 ok(!defined $o->other2_obj_soft, "ok referential_integrity - $db_type"); 49 50 $o->fks($old_fks); 51 52 my $o2 = MyPgObject->new(id => $o->id); 53 54 ok(ref $o2 && $o2->isa('MyPgObject'), "new() 2 - $db_type"); 55 56 is($o2->bits->to_Bin, '00101', "bits() (bitfield default value) - $db_type"); 57 58 ok($o2->load, "load() 2 - $db_type"); 59 ok(!$o2->not_found, "not_found() 1 - $db_type"); 60 61 is($o2->name, $o->name, "load() verify 1 - $db_type"); 62 is($o2->date_created, $o->date_created, "load() verify 2 - $db_type"); 63 is($o2->last_modified, $o->last_modified, "load() verify 3 - $db_type"); 64 is($o2->status, 'active', "load() verify 4 (default value) - $db_type"); 65 is($o2->flag, 1, "load() verify 5 (default boolean value) - $db_type"); 66 is($o2->flag2, 1, "load() verify 6 (boolean value) - $db_type"); 67 is($o2->save_col, 7, "load() verify 7 (aliased column) - $db_type"); 68 is($o2->start->ymd, '1980-12-24', "load() verify 8 (date value) - $db_type"); 69 70 is($o2->bits->to_Bin, '00101', "load() verify 9 (bitfield value) - $db_type"); 71 72 $o2->name('John 2'); 73 $o2->start('5/24/2001'); 74 75 $o2->other2_obj({ id => 3, name => 'foo' }); 76 77 sleep(1); # keep the last modified dates from being the same 78 79 $o2->last_modified('now'); 80 ok($o2->save, "save() 2 - $db_type"); 81 ok($o2->load, "load() 3 - $db_type"); 82 83 is($o2->other2_obj->name, 'foo', "single column foreign key 2 - $db_type"); 84 is($o2->date_created, $o->date_created, "save() verify 1 - $db_type"); 85 ok($o2->last_modified ne $o->last_modified, "save() verify 2 - $db_type"); 86 is($o2->start->ymd, '2001-05-24', "save() verify 3 (date value) - $db_type"); 87 88 my $o3 = MyPgObject->new(); 89 90 my $db = $o3->db or die $o3->error; 91 92 ok(ref $db && $db->isa('Rose::DB'), "db() - $db_type"); 93 94 is($db->dbh, $o3->dbh, "dbh() - $db_type"); 95 96 my $o4 = MyPgObject->new(id => 999); 97 ok(!$o4->load(speculative => 1), "load() nonexistent - $db_type"); 98 ok($o4->not_found, "not_found() 2 - $db_type"); 99 100 $o->other2_obj(MyPgOtherObject2->new(name => 'bar')); 101 $o->save; 102 103 $o = MyPgObject->new(id => $o->id)->load; 104 is($o->other2_obj->name, 'bar', "single column foreign key 3 - $db_type"); 105 106 ok($o->load, "load() 4 - $db_type"); 107 108 SKIP: 109 { 110 if($PG_HAS_CHKPASS) 111 { 112 $o->{'password_encrypted'} = ':8R1Kf2nOS0bRE'; 113 114 ok($o->password_is('xyzzy'), "chkpass() 1 - $db_type"); 115 is($o->password, 'xyzzy', "chkpass() 2 - $db_type"); 116 117 $o->password('foobar'); 118 119 ok($o->password_is('foobar'), "chkpass() 3 - $db_type"); 120 is($o->password, 'foobar', "chkpass() 4 - $db_type"); 121 122 ok($o->save, "save() 3 - $db_type"); 123 } 124 else 125 { 126 skip("chkpass tests", 5); 127 } 128 } 129 130 my $o5 = MyPgObject->new(id => $o->id); 131 132 ok($o5->load, "load() 5 - $db_type"); 133 134 SKIP: 135 { 136 if($PG_HAS_CHKPASS) 137 { 138 ok($o5->password_is('foobar'), "chkpass() 5 - $db_type"); 139 is($o5->password, 'foobar', "chkpass() 6 - $db_type"); 140 } 141 else 142 { 143 skip("chkpass tests", 2); 144 } 145 } 146 147 $o5->nums([ 4, 5, 6 ]); 148 ok($o5->save, "save() 4 - $db_type"); 149 ok($o->load, "load() 6 - $db_type"); 150 151 is($o5->nums->[0], 4, "load() verify 10 (array value) - $db_type"); 152 is($o5->nums->[1], 5, "load() verify 11 (array value) - $db_type"); 153 is($o5->nums->[2], 6, "load() verify 12 (array value) - $db_type"); 154 155 my @a = $o5->nums; 156 157 is($a[0], 4, "load() verify 13 (array value) - $db_type"); 158 is($a[1], 5, "load() verify 14 (array value) - $db_type"); 159 is($a[2], 6, "load() verify 15 (array value) - $db_type"); 160 is(@a, 3, "load() verify 16 (array value) - $db_type"); 161 162 my $oo1 = MyPgOtherObject->new(k1 => 1, k2 => 2, k3 => 3, name => 'one'); 163 ok($oo1->save, 'other object save() 1'); 164 165 my $oo2 = MyPgOtherObject->new(k1 => 11, k2 => 12, k3 => 13, name => 'two'); 166 ok($oo2->save, 'other object save() 2'); 167 168 is($o->other_obj, undef, 'other_obj() 1'); 169 170 $o->fkone(1); 171 $o->fk2(2); 172 $o->fk3(3); 173 174 my $obj = $o->other_obj or warn "# ", $o->error, "\n"; 175 176 is(ref $obj, 'MyPgOtherObject', 'other_obj() 2'); 177 is($obj->name, 'one', 'other_obj() 3'); 178 is($obj->db, $o->db, 'share_db (default true)'); 179 180 $o->other_obj(undef); 181 $o->fkone(11); 182 $o->fk2(12); 183 $o->fk3(13); 184 185 $obj = $o->other_obj or warn "# ", $o->error, "\n"; 186 187 is(ref $obj, 'MyPgOtherObject', 'other_obj() 4'); 188 is($obj->name, 'two', 'other_obj() 5'); 189 190 $o->fk2(undef); 191 is($o->other_obj, undef, "key_column_triggers - $db_type"); 192 193 ok($o->delete, "delete() - $db_type"); 194 195 eval { $o->meta->alias_column(nonesuch => 'foo') }; 196 ok($@, 'alias_column() nonesuch'); 197} 198 199# 200# MySQL 201# 202 203SKIP: foreach my $db_type ('mysql') 204{ 205 skip("MySQL tests", 34) unless($HAVE_MYSQL); 206 207 Rose::DB->default_type($db_type); 208 209 my $o = MyMySQLObject->new(name => 'John'); 210 211 ok(ref $o && $o->isa('MyMySQLObject'), "new() 1 - $db_type"); 212 213 $o->flag2('true'); 214 $o->date_created('now'); 215 $o->last_modified($o->date_created); 216 $o->save_col(22); 217 $o->other2_obj(7); 218 219 ok($o->save, "save() 1 - $db_type"); 220 ok($o->load, "load() 1 - $db_type"); 221 222 is($o->other2_obj->name, 'def', "single column foreign key 1 - $db_type"); 223 224 my $old_fks = $o->fks; 225 $o->other2_obj(undef); 226 $o->fks(0); 227 eval { $o->other2_obj }; 228 229 ok($@, "fatal referential_integrity - $db_type"); 230 ok(!defined $o->other2_obj_soft, "ok referential_integrity - $db_type"); 231 232 $o->fks($old_fks); 233 234 my $o2 = MyMySQLObject->new(id => $o->id); 235 236 ok(ref $o2 && $o2->isa('MyMySQLObject'), "new() 2 - $db_type"); 237 238 is($o2->bits->to_Bin, '00101', "bits() (bitfield default value) - $db_type"); 239 240 ok($o2->load(with => [ 'other_obj' ]), "load() 2 - $db_type"); 241 ok(!$o2->not_found, "not_found() 1 - $db_type"); 242 243 is($o2->name, $o->name, "load() verify 1 - $db_type"); 244 is($o2->date_created, $o->date_created, "load() verify 2 - $db_type"); 245 is($o2->last_modified, $o->last_modified, "load() verify 3 - $db_type"); 246 is($o2->status, 'active', "load() verify 4 (default value) - $db_type"); 247 is($o2->flag, 1, "load() verify 5 (default boolean value) - $db_type"); 248 is($o2->flag2, 1, "load() verify 6 (boolean value) - $db_type"); 249 is($o2->save_col, 22, "load() verify 7 (aliased column) - $db_type"); 250 is($o2->start->ymd, '1980-12-24', "load() verify 8 (date value) - $db_type"); 251 252 is($o2->bits->to_Bin, '00101', "load() verify 9 (bitfield value) - $db_type"); 253 254 $o2->name('John 2'); 255 $o2->start('5/24/2001'); 256 257 $o2->other2_obj({ id => 3, name => 'foo' }); 258 259 sleep(1); # keep the last modified dates from being the same 260 261 $o2->last_modified('now'); 262 263 ok($o2->save, "save() 2 - $db_type"); 264 ok($o2->load, "load() 3 - $db_type"); 265 266 is($o2->other2_obj->name, 'foo', "single column foreign key 2 - $db_type"); 267 is($o2->date_created, $o->date_created, "save() verify 1 - $db_type"); 268 ok($o2->last_modified ne $o->last_modified, "save() verify 2 - $db_type"); 269 is($o2->start->ymd, '2001-05-24', "save() verify 3 (date value) - $db_type"); 270 is($o2->bits->to_Bin, '00101', "load() verify 10 (bitfield value) - $db_type"); 271 272 my $o3 = MyMySQLObject->new(); 273 274 my $db = $o3->db or die $o3->error; 275 276 ok(ref $db && $db->isa('Rose::DB'), "db() - $db_type"); 277 278 is($db->dbh, $o3->dbh, "dbh() - $db_type"); 279 280 my $o4 = MyMySQLObject->new(id => 999); 281 ok(!$o4->load(speculative => 1), "load() nonexistent - $db_type"); 282 ok($o4->not_found, "not_found() 2 - $db_type"); 283 284 $o->other2_obj(MyMySQLOtherObject2->new(name => 'bar')); 285 $o->save; 286 287 $o = MyMySQLObject->new(id => $o->id)->load; 288 is($o->other2_obj->name, 'bar', "single column foreign key 3 - $db_type"); 289 290 ok($o->delete, "delete() - $db_type"); 291 292 eval { $o->meta->alias_column(nonesuch => 'foo') }; 293 ok($@, 'alias_column() nonesuch'); 294} 295 296# 297# Informix 298# 299 300SKIP: foreach my $db_type ('informix') 301{ 302 skip("Informix tests", 51) unless($HAVE_INFORMIX); 303 304 Rose::DB->default_type($db_type); 305 306 my $o = MyInformixObject->new(name => 'John', id => 1); 307 308 ok(ref $o && $o->isa('MyInformixObject'), "new() 1 - $db_type"); 309 310 $o->flag2('true'); 311 $o->date_created('now'); 312 $o->last_modified($o->date_created); 313 $o->save_col(7); 314 $o->other2_obj(7); 315 316 ok($o->save, "save() 1 - $db_type"); 317 ok($o->load, "load() 1 - $db_type"); 318 319 is($o->other2_obj->name, 'def', "single column foreign key 1 - $db_type"); 320 321 my $old_fks = $o->fks; 322 $o->other2_obj(undef); 323 $o->fks(0); 324 eval { $o->other2_obj }; 325 326 ok($@, "fatal referential_integrity - $db_type"); 327 ok(!defined $o->other2_obj_soft, "ok referential_integrity - $db_type"); 328 329 $o->fks($old_fks); 330 331 my $o2 = MyInformixObject->new(id => $o->id); 332 333 ok(ref $o2 && $o2->isa('MyInformixObject'), "new() 2 - $db_type"); 334 335 is($o2->bits->to_Bin, '00101', "bits() (bitfield default value) - $db_type"); 336 337 ok($o2->load, "load() 2 - $db_type"); 338 ok(!$o2->not_found, "not_found() 1 - $db_type"); 339 340 is($o2->name, $o->name, "load() verify 1 - $db_type"); 341 is($o2->date_created, $o->date_created, "load() verify 2 - $db_type"); 342 is($o2->last_modified, $o->last_modified, "load() verify 3 - $db_type"); 343 is($o2->status, 'active', "load() verify 4 (default value) - $db_type"); 344 is($o2->flag, 1, "load() verify 5 (default boolean value) - $db_type"); 345 is($o2->flag2, 1, "load() verify 6 (boolean value) - $db_type"); 346 is($o2->save_col, 7, "load() verify 7 (aliased column) - $db_type"); 347 is($o2->start->ymd, '1980-12-24', "load() verify 8 (date value) - $db_type"); 348 349 is($o2->bits->to_Bin, '00101', "load() verify 9 (bitfield value) - $db_type"); 350 351 $o2->name('John 2'); 352 $o2->start('5/24/2001'); 353 354 $o2->other2_obj({ id => 3, name => 'foo' }); 355 356 sleep(1); # keep the last modified dates from being the same 357 358 $o2->last_modified('now'); 359 ok($o2->save, "save() 2 - $db_type"); 360 ok($o2->load, "load() 3 - $db_type"); 361 362 is($o2->other2_obj->name, 'foo', "single column foreign key 2 - $db_type"); 363 is($o2->date_created, $o->date_created, "save() verify 1 - $db_type"); 364 ok($o2->last_modified ne $o->last_modified, "save() verify 2 - $db_type"); 365 is($o2->start->ymd, '2001-05-24', "save() verify 3 (date value) - $db_type"); 366 367 my $o3 = MyInformixObject->new(); 368 369 my $db = $o3->db or die $o3->error; 370 371 ok(ref $db && $db->isa('Rose::DB'), "db() - $db_type"); 372 373 is($db->dbh, $o3->dbh, "dbh() - $db_type"); 374 375 my $o4 = MyInformixObject->new(id => 999); 376 ok(!$o4->load(speculative => 1), "load() nonexistent - $db_type"); 377 ok($o4->not_found, "not_found() 2 - $db_type"); 378 379 $o->other2_obj(MyInformixOtherObject2->new(name => 'bar')); 380 $o->save; 381 382 $o = MyInformixObject->new(id => $o->id)->load; 383 is($o->other2_obj->name, 'bar', "single column foreign key 3 - $db_type"); 384 385 ok($o->load, "load() 4 - $db_type"); 386 387 my $o5 = MyInformixObject->new(id => $o->id); 388 389 ok($o5->load, "load() 5 - $db_type"); 390 391 $o5->nums([ 4, 5, 6 ]); 392 ok($o5->save, "save() 4 - $db_type"); 393 ok($o->load, "load() 6 - $db_type"); 394 395 is($o5->nums->[0], 4, "load() verify 10 (array value) - $db_type"); 396 is($o5->nums->[1], 5, "load() verify 11 (array value) - $db_type"); 397 is($o5->nums->[2], 6, "load() verify 12 (array value) - $db_type"); 398 399 my @a = $o5->nums; 400 401 is($a[0], 4, "load() verify 13 (array value) - $db_type"); 402 is($a[1], 5, "load() verify 14 (array value) - $db_type"); 403 is($a[2], 6, "load() verify 15 (array value) - $db_type"); 404 is(@a, 3, "load() verify 16 (array value) - $db_type"); 405 406 my $oo1 = MyInformixOtherObject->new(k1 => 1, k2 => 2, k3 => 3, name => 'one'); 407 ok($oo1->save, 'other object save() 1'); 408 409 my $oo2 = MyInformixOtherObject->new(k1 => 11, k2 => 12, k3 => 13, name => 'two'); 410 ok($oo2->save, 'other object save() 2'); 411 412 is($o->other_obj, undef, 'other_obj() 1'); 413 414 $o->fkone(1); 415 $o->fk2(2); 416 $o->fk3(3); 417 418 my $obj = $o->other_obj or warn "# ", $o->error, "\n"; 419 420 is(ref $obj, 'MyInformixOtherObject', 'other_obj() 2'); 421 is($obj->name, 'one', 'other_obj() 3'); 422 423 $o->other_obj(undef); 424 $o->fkone(11); 425 $o->fk2(12); 426 $o->fk3(13); 427 428 $obj = $o->other_obj or warn "# ", $o->error, "\n"; 429 430 is(ref $obj, 'MyInformixOtherObject', 'other_obj() 4'); 431 is($obj->name, 'two', 'other_obj() 5'); 432 433 ok($o->delete, "delete() - $db_type"); 434 435 eval { $o->meta->alias_column(nonesuch => 'foo') }; 436 ok($@, 'alias_column() nonesuch'); 437} 438 439# 440# SQLite 441# 442 443SKIP: foreach my $db_type ('sqlite') 444{ 445 skip("SQLite tests", 53) unless($HAVE_SQLITE); 446 447 Rose::DB->default_type($db_type); 448 449 is(MySQLiteObject->meta->foreign_key('other_obj')->key_column('fk1'), 'k1', "key_column 1 - $db_type"); 450 is(MySQLiteObject->meta->foreign_key('other_obj')->key_column('fk2'), 'k2', "key_column 2 - $db_type"); 451 452 my $o = MySQLiteObject->new(name => 'John', id => 1); 453 454 ok(ref $o && $o->isa('MySQLiteObject'), "new() 1 - $db_type"); 455 456 $o->flag2('true'); 457 $o->date_created('now'); 458 $o->last_modified($o->date_created); 459 $o->save_col(7); 460 $o->other2_obj(7); 461 462 ok($o->save, "save() 1 - $db_type"); 463 ok($o->load, "load() 1 - $db_type"); 464 465 is($o->other2_obj->name, 'def', "single column foreign key 1 - $db_type"); 466 467 my $old_fks = $o->fks; 468 $o->other2_obj(undef); 469 $o->fks(0); 470 eval { $o->other2_obj }; 471 472 ok($@, "fatal referential_integrity - $db_type"); 473 ok(!defined $o->other2_obj_soft, "ok referential_integrity - $db_type"); 474 475 $o->fks($old_fks); 476 477 my $o2 = MySQLiteObject->new(id => $o->id); 478 479 ok(ref $o2 && $o2->isa('MySQLiteObject'), "new() 2 - $db_type"); 480 481 is($o2->bits->to_Bin, '00101', "bits() (bitfield default value) - $db_type"); 482 483 ok($o2->load, "load() 2 - $db_type"); 484 ok(!$o2->not_found, "not_found() 1 - $db_type"); 485 486 is($o2->name, $o->name, "load() verify 1 - $db_type"); 487 is($o2->date_created, $o->date_created, "load() verify 2 - $db_type"); 488 is($o2->last_modified, $o->last_modified, "load() verify 3 - $db_type"); 489 is($o2->status, 'active', "load() verify 4 (default value) - $db_type"); 490 is($o2->flag, 1, "load() verify 5 (default boolean value) - $db_type"); 491 is($o2->flag2, 1, "load() verify 6 (boolean value) - $db_type"); 492 is($o2->save_col, 7, "load() verify 7 (aliased column) - $db_type"); 493 is($o2->start->ymd, '1980-12-24', "load() verify 8 (date value) - $db_type"); 494 495 is($o2->bits->to_Bin, '00101', "load() verify 9 (bitfield value) - $db_type"); 496 497 $o2->name('John 2'); 498 $o2->start('5/24/2001'); 499 500 $o2->other2_obj({ id => 3, name => 'foo' }); 501 502 sleep(1); # keep the last modified dates from being the same 503 504 $o2->last_modified('now'); 505 ok($o2->save, "save() 2 - $db_type"); 506 ok($o2->load, "load() 3 - $db_type"); 507 508 is($o2->other2_obj->name, 'foo', "single column foreign key 2 - $db_type"); 509 is($o2->date_created, $o->date_created, "save() verify 1 - $db_type"); 510 ok($o2->last_modified ne $o->last_modified, "save() verify 2 - $db_type"); 511 is($o2->start->ymd, '2001-05-24', "save() verify 3 (date value) - $db_type"); 512 513 my $o3 = MySQLiteObject->new(); 514 515 my $db = $o3->db or die $o3->error; 516 517 ok(ref $db && $db->isa('Rose::DB'), "db() - $db_type"); 518 519 is($db->dbh, $o3->dbh, "dbh() - $db_type"); 520 521 my $o4 = MySQLiteObject->new(id => 999); 522 ok(!$o4->load(speculative => 1), "load() nonexistent - $db_type"); 523 ok($o4->not_found, "not_found() 2 - $db_type"); 524 525 $o->other2_obj(MySQLiteOtherObject2->new(name => 'bar')); 526 $o->save; 527 528 $o = MySQLiteObject->new(id => $o->id)->load; 529 is($o->other2_obj->name, 'bar', "single column foreign key 3 - $db_type"); 530 531 ok($o->load, "load() 4 - $db_type"); 532 533 my $o5 = MySQLiteObject->new(id => $o->id); 534 535 ok($o5->load, "load() 5 - $db_type"); 536 537 $o5->nums([ 4, 5, 6 ]); 538 ok($o5->save, "save() 4 - $db_type"); 539 ok($o->load, "load() 6 - $db_type"); 540 541 is($o5->nums->[0], 4, "load() verify 10 (array value) - $db_type"); 542 is($o5->nums->[1], 5, "load() verify 11 (array value) - $db_type"); 543 is($o5->nums->[2], 6, "load() verify 12 (array value) - $db_type"); 544 545 my @a = $o5->nums; 546 547 is($a[0], 4, "load() verify 13 (array value) - $db_type"); 548 is($a[1], 5, "load() verify 14 (array value) - $db_type"); 549 is($a[2], 6, "load() verify 15 (array value) - $db_type"); 550 is(@a, 3, "load() verify 16 (array value) - $db_type"); 551 552 my $oo1 = MySQLiteOtherObject->new(k1 => 1, k2 => 2, k3 => 3, name => 'one'); 553 ok($oo1->save, 'other object save() 1'); 554 555 my $oo2 = MySQLiteOtherObject->new(k1 => 11, k2 => 12, k3 => 13, name => 'two'); 556 ok($oo2->save, 'other object save() 2'); 557 558 is($o->other_obj, undef, 'other_obj() 1'); 559 560 $o->fkone(1); 561 $o->fk2(2); 562 $o->fk3(3); 563 564 my $obj = $o->other_obj or warn "# ", $o->error, "\n"; 565 566 is(ref $obj, 'MySQLiteOtherObject', 'other_obj() 2'); 567 is($obj->name, 'one', 'other_obj() 3'); 568 569 $o->other_obj(undef); 570 $o->fkone(11); 571 $o->fk2(12); 572 $o->fk3(13); 573 574 $obj = $o->other_obj or warn "# ", $o->error, "\n"; 575 576 is(ref $obj, 'MySQLiteOtherObject', 'other_obj() 4'); 577 is($obj->name, 'two', 'other_obj() 5'); 578 579 ok($o->delete, "delete() - $db_type"); 580 581 eval { $o->meta->alias_column(nonesuch => 'foo') }; 582 ok($@, 'alias_column() nonesuch'); 583} 584 585BEGIN 586{ 587 # 588 # PostgreSQL 589 # 590 591 my $dbh; 592 593 eval 594 { 595 $dbh = Rose::DB->new('pg_admin')->retain_dbh() 596 or die Rose::DB->error; 597 }; 598 599 if(!$@ && $dbh) 600 { 601 our $HAVE_PG = 1; 602 603 # Drop existing table and create schema, ignoring errors 604 { 605 local $dbh->{'RaiseError'} = 0; 606 local $dbh->{'PrintError'} = 0; 607 $dbh->do('DROP TABLE rose_db_object_test CASCADE'); 608 $dbh->do('DROP TABLE rose_db_object_other'); 609 $dbh->do('DROP TABLE rose_db_object_other2'); 610 $dbh->do('DROP TABLE rose_db_object_chkpass_test'); 611 } 612 613 our $PG_HAS_CHKPASS = pg_has_chkpass(); 614 615 $dbh->do(<<"EOF"); 616CREATE TABLE rose_db_object_other 617( 618 k1 INT NOT NULL, 619 k2 INT NOT NULL, 620 k3 INT NOT NULL, 621 name VARCHAR(32), 622 623 UNIQUE(k1, k2, k3) 624) 625EOF 626 627 $dbh->do(<<"EOF"); 628CREATE TABLE rose_db_object_other2 629( 630 id SERIAL PRIMARY KEY, 631 name VARCHAR(32) DEFAULT 'def' 632) 633EOF 634 635 # Create test foreign subclasses 636 637 package MyPgOtherObject; 638 639 our @ISA = qw(Rose::DB::Object); 640 641 sub init_db { Rose::DB->new('pg') } 642 643 MyPgOtherObject->meta->table('rose_db_object_other'); 644 645 MyPgOtherObject->meta->columns 646 ( 647 name => { type => 'varchar'}, 648 k1 => { type => 'int' }, 649 k2 => { type => 'int' }, 650 k3 => { type => 'int' }, 651 ); 652 653 MyPgOtherObject->meta->primary_key_columns([ qw(k1 k2 k3) ]); 654 655 MyPgOtherObject->meta->initialize; 656 657 package MyPgOtherObject2; 658 659 our @ISA = qw(Rose::DB::Object); 660 661 sub init_db { Rose::DB->new('mysql') } 662 663 MyPgOtherObject2->meta->table('rose_db_object_other2'); 664 665 MyPgOtherObject2->meta->columns 666 ( 667 id => { primary_key => 1 }, 668 name => { type => 'varchar', default => 'def' }, 669 ); 670 671 MyPgOtherObject2->meta->initialize; 672 673 $dbh->do(<<"EOF"); 674CREATE TABLE rose_db_object_test 675( 676 id SERIAL PRIMARY KEY, 677 @{[ $PG_HAS_CHKPASS ? 'password CHKPASS,' : '' ]} 678 name VARCHAR(32) NOT NULL, 679 flag BOOLEAN NOT NULL, 680 flag2 BOOLEAN, 681 status VARCHAR(32) DEFAULT 'active', 682 bits BIT(5) NOT NULL DEFAULT B'00101', 683 start DATE, 684 save INT, 685 nums INT[], 686 fk1 INT, 687 fk2 INT, 688 fk3 INT, 689 fks INT REFERENCES rose_db_object_other2 (id), 690 last_modified TIMESTAMP, 691 date_created TIMESTAMP, 692 693 FOREIGN KEY (fk1, fk2, fk3) REFERENCES rose_db_object_other (k1, k2, k3) 694) 695EOF 696 697 $dbh->disconnect; 698 699 # Create test subclass 700 701 package MyPgObject; 702 703 our @ISA = qw(Rose::DB::Object); 704 705 sub init_db { Rose::DB->new('pg') } 706 707 MyPgObject->meta->table('rose_db_object_test'); 708 709 MyPgObject->meta->columns 710 ( 711 'name', 712 id => { primary_key => 1, type => 'serial' }, 713 ($PG_HAS_CHKPASS ? (password => { type => 'chkpass' }) : ()), 714 flag => { type => 'boolean', default => 1 }, 715 flag2 => { type => 'boolean' }, 716 status => { default => 'active' }, 717 start => { type => 'date', default => '12/24/1980' }, 718 save => { type => 'scalar' }, 719 nums => { type => 'array' }, 720 bits => { type => 'bitfield', bits => 5, default => 101 }, 721 fk1 => { type => 'int' }, 722 fk2 => { type => 'int' }, 723 fk3 => { type => 'int' }, 724 fks => { type => 'int' }, 725 last_modified => { type => 'timestamp' }, 726 date_created => { type => 'timestamp' }, 727 ); 728 729 MyPgObject->meta->foreign_keys 730 ( 731 other_obj => 732 { 733 class => 'MyPgOtherObject', 734 key_columns => 735 { 736 fk1 => 'k1', 737 fk2 => 'k2', 738 fk3 => 'k3', 739 }, 740 with_column_triggers => 1, 741 }, 742 743 other2_obj => 744 { 745 class => 'MyPgOtherObject2', 746 key_columns => 747 { 748 fks => 'id', 749 }, 750 with_column_triggers => 1, 751 }, 752 753 other2_obj_soft => 754 { 755 class => 'MyPgOtherObject2', 756 key_columns => 757 { 758 fks => 'id', 759 }, 760 referential_integrity => 0, 761 with_column_triggers => 1, 762 }, 763 ); 764 765 MyPgObject->meta->alias_column(fk1 => 'fkone'); 766 767 eval { MyPgObject->meta->initialize }; 768 Test::More::ok($@, 'meta->initialize() reserved method'); 769 770 MyPgObject->meta->alias_column(save => 'save_col'); 771 MyPgObject->meta->initialize(preserve_existing => 1); 772 } 773 774 # 775 # MySQL 776 # 777 778 my $db_version; 779 780 eval 781 { 782 my $db = Rose::DB->new('mysql_admin'); 783 $dbh = $db->retain_dbh() or die Rose::DB->error; 784 $db_version = $db->database_version; 785 }; 786 787 if(!$@ && $dbh) 788 { 789 our $HAVE_MYSQL = 1; 790 791 # Drop existing table and create schema, ignoring errors 792 { 793 local $dbh->{'RaiseError'} = 0; 794 local $dbh->{'PrintError'} = 0; 795 $dbh->do('DROP TABLE rose_db_object_test CASCADE'); 796 $dbh->do('DROP TABLE rose_db_object_other'); 797 $dbh->do('DROP TABLE rose_db_object_other2'); 798 } 799 800 $dbh->do(<<"EOF"); 801CREATE TABLE rose_db_object_other 802( 803 k1 INT NOT NULL, 804 k2 INT NOT NULL, 805 k3 INT NOT NULL, 806 name VARCHAR(32), 807 808 KEY(k1, k2, k3) 809) 810EOF 811 812 $dbh->do(<<"EOF"); 813CREATE TABLE rose_db_object_other2 814( 815 id INT AUTO_INCREMENT PRIMARY KEY, 816 name VARCHAR(32) DEFAULT 'def' 817) 818EOF 819 820 # Create test foreign subclasses 821 822 package MyMySQLOtherObject; 823 824 our @ISA = qw(Rose::DB::Object); 825 826 sub init_db { Rose::DB->new('mysql') } 827 828 MyMySQLOtherObject->meta->table('rose_db_object_other'); 829 830 MyMySQLOtherObject->meta->columns 831 ( 832 name => { type => 'varchar'}, 833 k1 => { type => 'int' }, 834 k2 => { type => 'int' }, 835 k3 => { type => 'int' }, 836 ); 837 838 MyMySQLOtherObject->meta->primary_key_columns([ qw(k1 k2 k3) ]); 839 840 MyMySQLOtherObject->meta->initialize; 841 842 package MyMySQLOtherObject2; 843 844 our @ISA = qw(Rose::DB::Object); 845 846 sub init_db { Rose::DB->new('mysql') } 847 848 MyMySQLOtherObject2->meta->table('rose_db_object_other2'); 849 850 MyMySQLOtherObject2->meta->columns 851 ( 852 id => { type => 'serial', primary_key => 1 }, 853 name => { type => 'varchar', default => 'def' }, 854 ); 855 856 MyMySQLOtherObject2->meta->initialize; 857 858 # MySQL 5.0.3 or later has a completely stupid "native" BIT type 859 my $bit_col = 860 ($db_version >= 5_000_003) ? 861 q(bits BIT(5) NOT NULL DEFAULT B'00101') : 862 q(bits BIT(5) NOT NULL DEFAULT '00101'); 863 864 $dbh->do(<<"EOF"); 865CREATE TABLE rose_db_object_test 866( 867 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 868 name VARCHAR(32) NOT NULL, 869 flag TINYINT(1) NOT NULL, 870 flag2 TINYINT(1), 871 status VARCHAR(32) DEFAULT 'active', 872 $bit_col, 873 start DATE, 874 save INT, 875 fk1 INT, 876 fk2 INT, 877 fk3 INT, 878 fks INT REFERENCES rose_db_object_other2 (id), 879 last_modified TIMESTAMP, 880 date_created DATETIME 881) 882EOF 883 884 $dbh->disconnect; 885 886 # Create test subclass 887 888 package MyMySQLObject; 889 890 our @ISA = qw(Rose::DB::Object); 891 892 sub init_db { Rose::DB->new('mysql') } 893 894 MyMySQLObject->meta->allow_inline_column_values(1); 895 896 MyMySQLObject->meta->table('rose_db_object_test'); 897 898 MyMySQLObject->meta->columns 899 ( 900 'name', 901 id => { primary_key => 1 }, 902 flag => { type => 'boolean', default => 1 }, 903 flag2 => { type => 'boolean' }, 904 status => { default => 'active' }, 905 start => { type => 'date', default => '12/24/1980' }, 906 save => { type => 'scalar' }, 907 bits => { type => 'bitfield', bits => 5, default => 101 }, 908 fk1 => { type => 'int' }, 909 fk2 => { type => 'int' }, 910 fk3 => { type => 'int' }, 911 fks => { type => 'int' }, 912 last_modified => { type => 'timestamp' }, 913 date_created => { type => 'datetime' }, 914 ); 915 916 MyMySQLObject->meta->foreign_keys 917 ( 918 other_obj => 919 { 920 class => 'MyMySQLOtherObject', 921 key_columns => 922 { 923 fk1 => 'k1', 924 fk2 => 'k2', 925 fk3 => 'k3', 926 } 927 }, 928 929 other2_obj => 930 { 931 class => 'MyMySQLOtherObject2', 932 key_columns => 933 { 934 fks => 'id', 935 }, 936 }, 937 938 other2_obj_soft => 939 { 940 class => 'MyMySQLOtherObject2', 941 key_columns => 942 { 943 fks => 'id', 944 }, 945 soft => 1, 946 }, 947 ); 948 949 eval { MyMySQLObject->meta->initialize }; 950 Test::More::ok($@, 'meta->initialize() reserved method'); 951 952 MyMySQLObject->meta->alias_column(save => 'save_col'); 953 MyMySQLObject->meta->initialize(preserve_existing => 1); 954 } 955 956 # 957 # Informix 958 # 959 960 eval 961 { 962 $dbh = Rose::DB->new('informix_admin')->retain_dbh() 963 or die Rose::DB->error; 964 }; 965 966 if(!$@ && $dbh) 967 { 968 our $HAVE_INFORMIX = 1; 969 970 # Drop existing table and create schema, ignoring errors 971 { 972 local $dbh->{'RaiseError'} = 0; 973 local $dbh->{'PrintError'} = 0; 974 $dbh->do('DROP TABLE rose_db_object_test CASCADE'); 975 $dbh->do('DROP TABLE rose_db_object_other'); 976 $dbh->do('DROP TABLE rose_db_object_other2'); 977 } 978 979 $dbh->do(<<"EOF"); 980CREATE TABLE rose_db_object_other 981( 982 k1 INT NOT NULL, 983 k2 INT NOT NULL, 984 k3 INT NOT NULL, 985 name VARCHAR(32), 986 987 UNIQUE(k1, k2, k3) 988) 989EOF 990 991 $dbh->do(<<"EOF"); 992CREATE TABLE rose_db_object_other2 993( 994 id SERIAL PRIMARY KEY, 995 name VARCHAR(32) DEFAULT 'def' 996) 997EOF 998 999 # Create test foreign subclasses 1000 1001 package MyInformixOtherObject; 1002 1003 our @ISA = qw(Rose::DB::Object); 1004 1005 sub init_db { Rose::DB->new('informix') } 1006 1007 MyInformixOtherObject->meta->table('rose_db_object_other'); 1008 1009 MyInformixOtherObject->meta->columns 1010 ( 1011 name => { type => 'varchar'}, 1012 k1 => { type => 'int' }, 1013 k2 => { type => 'int' }, 1014 k3 => { type => 'int' }, 1015 ); 1016 1017 MyInformixOtherObject->meta->primary_key_columns(qw(k1 k2 k3)); 1018 1019 MyInformixOtherObject->meta->initialize; 1020 1021 package MyInformixOtherObject2; 1022 1023 our @ISA = qw(Rose::DB::Object); 1024 1025 sub init_db { Rose::DB->new('informix') } 1026 1027 MyInformixOtherObject2->meta->table('rose_db_object_other2'); 1028 1029 MyInformixOtherObject2->meta->columns 1030 ( 1031 id => { type => 'serial', primary_key => 1 }, 1032 name => { type => 'varchar', default => 'def' }, 1033 ); 1034 1035 MyInformixOtherObject2->meta->initialize; 1036 1037 $dbh->do(<<"EOF"); 1038CREATE TABLE rose_db_object_test 1039( 1040 id INT NOT NULL PRIMARY KEY, 1041 name VARCHAR(32) NOT NULL, 1042 flag BOOLEAN NOT NULL, 1043 flag2 BOOLEAN, 1044 status VARCHAR(32) DEFAULT 'active', 1045 bits VARCHAR(5) DEFAULT '00101' NOT NULL, 1046 start DATE, 1047 save INT, 1048 nums VARCHAR(255), 1049 fk1 INT, 1050 fk2 INT, 1051 fk3 INT, 1052 fks INT REFERENCES rose_db_object_other2 (id), 1053 last_modified DATETIME YEAR TO FRACTION(5), 1054 date_created DATETIME YEAR TO FRACTION(5), 1055 1056 FOREIGN KEY (fk1, fk2, fk3) REFERENCES rose_db_object_other (k1, k2, k3) 1057) 1058EOF 1059 1060 $dbh->disconnect; 1061 1062 # Create test subclass 1063 1064 package MyInformixObject; 1065 1066 our @ISA = qw(Rose::DB::Object); 1067 1068 sub init_db { Rose::DB->new('informix') } 1069 1070 MyInformixObject->meta->table('rose_db_object_test'); 1071 1072 MyInformixObject->meta->columns 1073 ( 1074 'name', 1075 id => { primary_key => 1 }, 1076 flag => { type => 'boolean', default => 1 }, 1077 flag2 => { type => 'boolean' }, 1078 status => { default => 'active' }, 1079 start => { type => 'date', default => '12/24/1980' }, 1080 save => { type => 'scalar' }, 1081 nums => { type => 'array' }, 1082 bits => { type => 'bitfield', bits => 5, default => 101 }, 1083 fk1 => { type => 'int' }, 1084 fk2 => { type => 'int' }, 1085 fk3 => { type => 'int' }, 1086 fks => { type => 'int' }, 1087 last_modified => { type => 'timestamp' }, 1088 date_created => { type => 'timestamp' }, 1089 ); 1090 1091 MyInformixObject->meta->foreign_keys 1092 ( 1093 other_obj => 1094 { 1095 class => 'MyInformixOtherObject', 1096 key_columns => 1097 { 1098 fk1 => 'k1', 1099 fk2 => 'k2', 1100 fk3 => 'k3', 1101 } 1102 }, 1103 1104 other2_obj => 1105 { 1106 class => 'MyInformixOtherObject2', 1107 key_columns => 1108 { 1109 fks => 'id', 1110 }, 1111 }, 1112 1113 other2_obj_soft => 1114 { 1115 class => 'MyInformixOtherObject2', 1116 key_columns => 1117 { 1118 fks => 'id', 1119 }, 1120 referential_integrity => 0, 1121 }, 1122 ); 1123 1124 MyInformixObject->meta->alias_column(fk1 => 'fkone'); 1125 1126 eval { MyInformixObject->meta->initialize }; 1127 Test::More::ok($@, 'meta->initialize() reserved method'); 1128 1129 MyInformixObject->meta->alias_column(save => 'save_col'); 1130 MyInformixObject->meta->initialize(preserve_existing => 1); 1131 } 1132 1133 # 1134 # SQLite 1135 # 1136 1137 eval 1138 { 1139 $dbh = Rose::DB->new('sqlite_admin')->retain_dbh() 1140 or die Rose::DB->error; 1141 }; 1142 1143 if(!$@ && $dbh) 1144 { 1145 our $HAVE_SQLITE = 1; 1146 1147 # Drop existing table and create schema, ignoring errors 1148 { 1149 local $dbh->{'RaiseError'} = 0; 1150 local $dbh->{'PrintError'} = 0; 1151 $dbh->do('DROP TABLE rose_db_object_test'); 1152 $dbh->do('DROP TABLE rose_db_object_other'); 1153 $dbh->do('DROP TABLE rose_db_object_other2'); 1154 } 1155 1156 $dbh->do(<<"EOF"); 1157CREATE TABLE rose_db_object_other 1158( 1159 k1 INT NOT NULL, 1160 k2 INT NOT NULL, 1161 k3 INT NOT NULL, 1162 name VARCHAR(32), 1163 1164 UNIQUE(k1, k2, k3) 1165) 1166EOF 1167 1168 $dbh->do(<<"EOF"); 1169CREATE TABLE rose_db_object_other2 1170( 1171 id INTEGER PRIMARY KEY AUTOINCREMENT, 1172 name VARCHAR(32) DEFAULT 'def' 1173) 1174EOF 1175 1176 # Create test foreign subclasses 1177 1178 package MySQLiteOtherObject; 1179 1180 our @ISA = qw(Rose::DB::Object); 1181 1182 sub init_db { Rose::DB->new('sqlite') } 1183 1184 MySQLiteOtherObject->meta->table('rose_db_object_other'); 1185 1186 MySQLiteOtherObject->meta->columns 1187 ( 1188 name => { type => 'varchar'}, 1189 k1 => { type => 'int' }, 1190 k2 => { type => 'int' }, 1191 k3 => { type => 'int' }, 1192 ); 1193 1194 MySQLiteOtherObject->meta->primary_key_columns(qw(k1 k2 k3)); 1195 1196 MySQLiteOtherObject->meta->initialize; 1197 1198 package MySQLiteOtherObject2; 1199 1200 our @ISA = qw(Rose::DB::Object); 1201 1202 sub init_db { Rose::DB->new('sqlite') } 1203 1204 MySQLiteOtherObject2->meta->table('rose_db_object_other2'); 1205 1206 MySQLiteOtherObject2->meta->columns 1207 ( 1208 id => { type => 'serial', primary_key => 1 }, 1209 name => { type => 'varchar', default => 'def' }, 1210 ); 1211 1212 MySQLiteOtherObject2->meta->initialize; 1213 1214 $dbh->do(<<"EOF"); 1215CREATE TABLE rose_db_object_test 1216( 1217 id INTEGER PRIMARY KEY AUTOINCREMENT, 1218 name VARCHAR(32) NOT NULL, 1219 flag BOOLEAN NOT NULL, 1220 flag2 BOOLEAN, 1221 status VARCHAR(32) DEFAULT 'active', 1222 bits VARCHAR(5) DEFAULT '00101' NOT NULL, 1223 start DATE, 1224 save INT, 1225 nums VARCHAR(255), 1226 fk1 INT, 1227 fk2 INT, 1228 fk3 INT, 1229 fks INT REFERENCES rose_db_object_other2 (id), 1230 last_modified TIMESTAMP, 1231 date_created TIMESTAMP, 1232 1233 FOREIGN KEY (fk1, fk2, fk3) REFERENCES rose_db_object_other (k1, k2, k3) 1234) 1235EOF 1236 1237 $dbh->disconnect; 1238 1239 # Create test subclass 1240 1241 package MySQLiteObject; 1242 1243 our @ISA = qw(Rose::DB::Object); 1244 1245 sub init_db { Rose::DB->new('sqlite') } 1246 1247 MySQLiteObject->meta->table('rose_db_object_test'); 1248 1249 MySQLiteObject->meta->columns 1250 ( 1251 'name', 1252 id => { primary_key => 1 }, 1253 flag => { type => 'boolean', default => 1 }, 1254 flag2 => { type => 'boolean' }, 1255 status => { default => 'active' }, 1256 start => { type => 'date', default => '12/24/1980' }, 1257 save => { type => 'scalar' }, 1258 nums => { type => 'array' }, 1259 bits => { type => 'bitfield', bits => 5, default => 101 }, 1260 fk1 => { type => 'int' }, 1261 fk2 => { type => 'int' }, 1262 fk3 => { type => 'int' }, 1263 fks => { type => 'int' }, 1264 last_modified => { type => 'timestamp' }, 1265 date_created => { type => 'timestamp' }, 1266 ); 1267 1268 MySQLiteObject->meta->foreign_keys 1269 ( 1270 other_obj => 1271 { 1272 class => 'MySQLiteOtherObject', 1273 key_columns => 1274 { 1275 fk1 => 'k1', 1276 fk2 => 'k2', 1277 fk3 => 'k3', 1278 } 1279 }, 1280 1281 other2_obj => 1282 { 1283 class => 'MySQLiteOtherObject2', 1284 key_columns => 1285 { 1286 fks => 'id', 1287 }, 1288 }, 1289 1290 other2_obj_soft => 1291 { 1292 class => 'MySQLiteOtherObject2', 1293 key_columns => 1294 { 1295 fks => 'id', 1296 }, 1297 soft => 1, 1298 }, 1299 ); 1300 1301 MySQLiteObject->meta->alias_column(fk1 => 'fkone'); 1302 1303 eval { MySQLiteObject->meta->initialize }; 1304 Test::More::ok($@, 'meta->initialize() reserved method'); 1305 1306 MySQLiteObject->meta->alias_column(save => 'save_col'); 1307 MySQLiteObject->meta->initialize(preserve_existing => 1); 1308 } 1309} 1310 1311END 1312{ 1313 # Delete test table 1314 1315 if($HAVE_PG) 1316 { 1317 # PostgreSQL 1318 my $dbh = Rose::DB->new('pg_admin')->retain_dbh() 1319 or die Rose::DB->error; 1320 1321 $dbh->do('DROP TABLE rose_db_object_test CASCADE'); 1322 $dbh->do('DROP TABLE rose_db_object_other'); 1323 $dbh->do('DROP TABLE rose_db_object_other2'); 1324 1325 $dbh->disconnect; 1326 } 1327 1328 if($HAVE_MYSQL) 1329 { 1330 # MySQL 1331 my $dbh = Rose::DB->new('mysql_admin')->retain_dbh() 1332 or die Rose::DB->error; 1333 1334 $dbh->do('DROP TABLE rose_db_object_test CASCADE'); 1335 $dbh->do('DROP TABLE rose_db_object_other'); 1336 $dbh->do('DROP TABLE rose_db_object_other2'); 1337 1338 $dbh->disconnect; 1339 } 1340 1341 if($HAVE_INFORMIX) 1342 { 1343 # Informix 1344 my $dbh = Rose::DB->new('informix_admin')->retain_dbh() 1345 or die Rose::DB->error; 1346 1347 $dbh->do('DROP TABLE rose_db_object_test CASCADE'); 1348 $dbh->do('DROP TABLE rose_db_object_other'); 1349 $dbh->do('DROP TABLE rose_db_object_other2'); 1350 1351 $dbh->disconnect; 1352 } 1353 1354 if($HAVE_SQLITE) 1355 { 1356 # SQLite 1357 my $dbh = Rose::DB->new('sqlite_admin')->retain_dbh() 1358 or die Rose::DB->error; 1359 1360 $dbh->do('DROP TABLE rose_db_object_test'); 1361 $dbh->do('DROP TABLE rose_db_object_other'); 1362 $dbh->do('DROP TABLE rose_db_object_other2'); 1363 1364 $dbh->disconnect; 1365 } 1366} 1367