1#!/usr/bin/perl -w 2 3use strict; 4 5use Test::More tests => 1627; 6 7BEGIN 8{ 9 require 't/test-lib.pl'; 10 use_ok('Rose::DB::Object::Loader'); 11 use_ok('Rose::DB::Object::Helpers'); 12} 13 14use Data::Dumper; 15$Data::Dumper::Sortkeys = 1; 16 17our %Have; 18 19# 20# Tests 21# 22 23use Rose::DB::Object::Constants qw(STATE_SAVING); 24 25#$Rose::DB::Object::Manager::Debug = 1; 26 27if(defined $ENV{'RDBO_NESTED_JOINS'} && Rose::DB::Object::Manager->can('default_nested_joins')) 28{ 29 Rose::DB::Object::Manager->default_nested_joins($ENV{'RDBO_NESTED_JOINS'}); 30} 31 32my $Include = 33 '^(?:' . join('|', qw(colors descriptions authors nicknames 34 description_author_map product_color_map 35 prices products vendors regions)) . ')$'; 36$Include = qr($Include); 37 38foreach my $db_type (qw(sqlite mysql pg pg_with_schema informix)) 39{ 40 SKIP: 41 { 42 skip("$db_type tests", 325) unless($Have{$db_type}); 43 } 44 45 next unless($Have{$db_type}); 46 47 Rose::DB->default_type($db_type); 48 49 Rose::DB::Object::Metadata->unregister_all_classes; 50 51 # Test of the subselect limit code 52 #Rose::DB::Object::Manager->default_limit_with_subselect(1) if($db_type =~ /^pg/); 53 54 my $db = Rose::DB->new; 55 56 my $class_prefix = 57 ucfirst($db_type eq 'pg_with_schema' ? 'pgws' : $db_type); 58 59 my $loader = 60 Rose::DB::Object::Loader->new( 61 db => $db, 62 class_prefix => $class_prefix); 63 64 my @classes = $loader->make_classes(include_tables => $Include); 65 66 my $product_class = $class_prefix . '::Product'; 67 my $manager_class = $product_class . '::Manager'; 68 69 Rose::DB::Object::Helpers->import(-target_class => $product_class, qw(as_tree new_from_tree init_with_tree)); 70 71 my $p1 = 72 $product_class->new( 73 id => 1, 74 name => 'Kite', 75 vendor => { id => 1, name => 'V1', region => { id => 'DE', name => 'Germany' } }, 76 prices => 77 [ 78 { price => 1.25, region => { id => 'US', name => 'America' } }, 79 { price => 4.25, region => { id => 'DE', name => 'Germany' } }, 80 ], 81 colors => 82 [ 83 { 84 name => 'red', 85 description => 86 { 87 text => 'desc 1', 88 authors => 89 [ 90 { 91 name => 'john', 92 nicknames => [ { nick => 'jack' }, { nick => 'sir' } ], 93 }, 94 { 95 name => 'sue', 96 nicknames => [ { nick => 'sioux' } ], 97 }, 98 ], 99 }, 100 }, 101 { 102 name => 'blue', 103 description => 104 { 105 text => 'desc 2', 106 authors => 107 [ 108 { name => 'john' }, 109 { 110 name => 'jane', 111 nicknames => [ { nick => 'blub' } ], 112 }, 113 ], 114 } 115 } 116 ]); 117 118 $p1->save; 119 120 my $p2 = 121 $product_class->new( 122 id => 2, 123 name => 'Sled', 124 vendor => { id => 2, name => 'V2', region_id => 'US', vendor_id => 1 }, 125 prices => [ { price => 9.25 } ], 126 colors => 127 [ 128 { name => 'red' }, 129 { 130 name => 'green', 131 description => 132 { 133 text => 'desc 3', 134 authors => [ { name => 'tim' } ], 135 } 136 } 137 ]); 138 139 $p2->save; 140 141 my $p3 = 142 $product_class->new( 143 id => 3, 144 name => 'Barn', 145 vendor => { id => 3, name => 'V3', region => { id => 'UK', name => 'England' }, vendor_id => 2 }, 146 prices => [ { price => 100 } ], 147 colors => 148 [ 149 { name => 'green' }, 150 { 151 name => 'pink', 152 description => 153 { 154 text => 'desc 4', 155 authors => [ { name => 'joe', nicknames => [ { nick => 'joey' } ] } ], 156 } 157 } 158 ]); 159 160 $p3->save; 161 162 #local $Rose::DB::Object::Manager::Debug = 1; 163 164 my $products = 165 $manager_class->get_products( 166 db => $db, 167 require_objects => [ 'vendor.vendor', 'vendor.region' ]); 168 169 is(scalar @$products, 2, "require vendors 1 - $db_type"); 170 171 is($products->[0]{'vendor'}{'id'}, 2, "p2 - require vendors 1 - $db_type"); 172 is($products->[0]{'vendor'}{'vendor'}{'id'}, 1, "p2 - require vendors 2 - $db_type"); 173 is($products->[0]{'vendor'}{'region'}{'name'}, 'America', "p2 - require vendors 3 - $db_type"); 174 175 is($products->[1]{'vendor'}{'id'}, 3, "p3 - require vendors 1 - $db_type"); 176 is($products->[1]{'vendor'}{'vendor'}{'id'}, 2, "p3 - require vendors 2 - $db_type"); 177 is($products->[1]{'vendor'}{'region'}{'name'}, 'England', "p3 - require vendors 3 - $db_type"); 178 179 # No-op join override tests 180 181 my $last_sql; 182 my $i = 1; 183 184 foreach my $pair ([ [], [ 'vendor.vendor', 'vendor.region' ] ], 185 [ [], [ 'vendor!.vendor', 'vendor.region' ] ], 186 [ [], [ 'vendor.vendor!', 'vendor.region' ] ], 187 [ [], [ 'vendor.vendor!', 'vendor!.region' ] ], 188 [ [], [ 'vendor.vendor!', 'vendor.region!' ] ], 189 [ [], [ 'vendor!.vendor', 'vendor.region!' ] ], 190 [ [], [ 'vendor.vendor!', 'vendor!.region' ] ], 191 [ [], [ 'vendor!.vendor!', 'vendor!.region!' ] ]) 192 { 193 my $sql = 194 $manager_class->get_objects_sql( 195 db => $db, 196 debug => 1, 197 (@{$pair->[0]} ? (with_objects => $pair->[0]) : ()), 198 (@{$pair->[1]} ? (require_objects => $pair->[1]) : ())); 199 200 $sql =~ s/\s+/ /g; 201 202 if($last_sql) 203 { 204 is($sql, $last_sql, "join override no-op $i - $db_type"); 205 } 206 else 207 { 208 ok($sql, "join override $i - $db_type"); 209 } 210 211 $last_sql = $sql; 212 $i++; 213 } 214 215 $i = 1; 216 217 # Override tests 218 219 my $sql = 220 $manager_class->get_objects_sql( 221 db => $db, 222 with_objects => [ 'vendor.region!' ]); 223 224 cmp_sql($sql, <<"EOF", "join override $i - $db_type"); 225SELECT 226 t1.vendor_id, 227 t1.name, 228 t1.id, 229 t2.region_id, 230 t2.vendor_id, 231 t2.name, 232 t2.id, 233 t3.name, 234 t3.id 235FROM 236 products t1 237 LEFT OUTER JOIN (vendors t2 JOIN regions t3 ON (t2.region_id = t3.id)) ON (t1.vendor_id = t2.id) 238EOF 239 240 $i++; 241 242 $sql = 243 $manager_class->get_objects_sql( 244 db => $db, 245 with_objects => [ 'vendor.region' ]); 246 247 cmp_sql($sql, <<"EOF", "join override $i - $db_type"); 248SELECT 249 t1.vendor_id, 250 t1.name, 251 t1.id, 252 t2.region_id, 253 t2.vendor_id, 254 t2.name, 255 t2.id, 256 t3.name, 257 t3.id 258FROM 259 products t1 260 LEFT OUTER JOIN vendors t2 ON (t1.vendor_id = t2.id) 261 LEFT OUTER JOIN regions t3 ON (t2.region_id = t3.id) 262EOF 263 264 $i++; 265 266 $sql = 267 $manager_class->get_objects_sql( 268 db => $db, 269 multi_many_ok => 1, 270 with_objects => [ 'colors.description.authors.nicknames' ]); 271 272 cmp_sql("$sql\n", <<"EOF", "join override $i - $db_type"); 273SELECT 274 t1.vendor_id, 275 t1.name, 276 t1.id, 277 t3.description_id, 278 t3.name, 279 t3.id, 280 t4.text, 281 t4.id, 282 t6.name, 283 t6.id, 284 t7.author_id, 285 t7.id, 286 t7.nick 287FROM 288 products t1 289 LEFT OUTER JOIN product_color_map t2 ON (t2.product_id = t1.id) 290 LEFT OUTER JOIN colors t3 ON (t2.color_id = t3.id) 291 LEFT OUTER JOIN descriptions t4 ON (t3.description_id = t4.id) 292 LEFT OUTER JOIN description_author_map t5 ON (t5.description_id = t4.id) 293 LEFT OUTER JOIN authors t6 ON (t5.author_id = t6.id) 294 LEFT OUTER JOIN nicknames t7 ON (t6.id = t7.author_id) 295 296ORDER BY t1.id 297EOF 298 #print STDERR "$sql\n"; 299 300 $i++; 301 302 $sql = 303 $manager_class->get_objects_sql( 304 db => $db, 305 multi_many_ok => 1, 306 with_objects => [ 'colors.description!.authors.nicknames!' ]); 307 308 cmp_sql("$sql\n", <<"EOF", "join override $i - $db_type"); 309SELECT 310 t1.vendor_id, 311 t1.name, 312 t1.id, 313 t3.description_id, 314 t3.name, 315 t3.id, 316 t4.text, 317 t4.id, 318 t6.name, 319 t6.id, 320 t7.author_id, 321 t7.id, 322 t7.nick 323FROM 324 products t1 325 LEFT OUTER JOIN product_color_map t2 ON (t2.product_id = t1.id) 326 LEFT OUTER JOIN (colors t3 JOIN descriptions t4 ON (t3.description_id = t4.id)) ON (t2.color_id = t3.id) 327 LEFT OUTER JOIN description_author_map t5 ON (t5.description_id = t4.id) 328 LEFT OUTER JOIN (authors t6 JOIN nicknames t7 ON (t6.id = t7.author_id)) ON (t5.author_id = t6.id) 329 330ORDER BY t1.id 331EOF 332 #print STDERR "$sql\n"; 333 334 $i++; 335 336 $sql = 337 $manager_class->get_objects_sql( 338 db => $db, 339 multi_many_ok => 1, 340 require_objects => [ 'colors.description.authors.nicknames' ]); 341 342 if($db->likes_implicit_joins) 343 { 344 cmp_sql("$sql\n", <<"EOF", "join override $i - $db_type"); 345SELECT 346 t1.vendor_id, 347 t1.name, 348 t1.id, 349 t3.description_id, 350 t3.name, 351 t3.id, 352 t4.text, 353 t4.id, 354 t6.name, 355 t6.id, 356 t7.author_id, 357 t7.nick, 358 t7.id 359FROM 360 products t1, 361 product_color_map t2, 362 colors t3, 363 descriptions t4, 364 description_author_map t5, 365 authors t6, 366 nicknames t7 367WHERE 368 t2.product_id = t1.id AND 369 t2.color_id = t3.id AND 370 t3.description_id = t4.id AND 371 t5.description_id = t4.id AND 372 t5.author_id = t6.id AND 373 t6.id = t7.author_id 374ORDER BY t1.id 375EOF 376 } 377 else 378 { 379 380 cmp_sql("$sql\n", <<"EOF", "join override $i - $db_type"); 381SELECT 382 t1.vendor_id, 383 t1.name, 384 t1.id, 385 t3.description_id, 386 t3.name, 387 t3.id, 388 t4.text, 389 t4.id, 390 t6.name, 391 t6.id, 392 t7.author_id, 393 t7.id, 394 t7.nick 395FROM 396 products t1 397 JOIN (product_color_map t2 JOIN (colors t3 JOIN (descriptions t4 JOIN (description_author_map t5 JOIN (authors t6 JOIN nicknames t7 ON (t6.id = t7.author_id)) ON (t5.author_id = t6.id)) ON (t5.description_id = t4.id)) ON (t3.description_id = t4.id)) ON (t2.color_id = t3.id)) ON (t2.product_id = t1.id) 398ORDER BY t1.id 399EOF 400 } 401 #print STDERR "$sql\n"; 402 403 $i++; 404 405 $sql = 406 $manager_class->get_objects_sql( 407 db => $db, 408 multi_many_ok => 1, 409 require_objects => [ 'colors.description?.authors.nicknames?' ]); 410 411 cmp_sql("$sql\n", <<"EOF", "join override $i - $db_type"); 412SELECT 413 t1.vendor_id, 414 t1.name, 415 t1.id, 416 t3.description_id, 417 t3.name, 418 t3.id, 419 t4.text, 420 t4.id, 421 t6.name, 422 t6.id, 423 t7.author_id, 424 t7.id, 425 t7.nick 426FROM 427 products t1 428 JOIN (product_color_map t2 JOIN colors t3 ON (t2.color_id = t3.id)) ON (t2.product_id = t1.id) 429 LEFT OUTER JOIN (descriptions t4 JOIN (description_author_map t5 JOIN authors t6 ON (t5.author_id = t6.id)) ON (t5.description_id = t4.id)) ON (t3.description_id = t4.id) 430 LEFT OUTER JOIN nicknames t7 ON (t6.id = t7.author_id) 431ORDER BY t1.id 432EOF 433 434 #print STDERR "$sql\n"; 435 436 # Conflict tests 437 438 $i = 1; 439 440 foreach my $pair ([ [], [ 'vendor.vendor', 'vendor?.region' ] ], 441 [ [], [ 'vendor?.vendor', 'vendor.region' ] ], 442 [ [], [ 'vendor?.vendor!', 'vendor!.region' ] ], 443 [ [ 'vendor?.vendor' ], [ 'vendor.region' ] ], 444 [ [ 'vendor.vendor' ], [ 'vendor!.region' ] ]) 445 { 446 eval 447 { 448 $manager_class->get_objects_sql( 449 db => $db, 450 debug => 1, 451 (@{$pair->[0]} ? (with_objects => $pair->[0]) : ()), 452 (@{$pair->[1]} ? (require_objects => $pair->[1]) : ())); 453 }; 454 455 ok($@, "join override conflict $i - $db_type"); 456 457 $i++; 458 } 459 460 is(scalar @$products, 2, "require vendors 1 - $db_type"); 461 462 is($products->[0]{'vendor'}{'id'}, 2, "p2 - require vendors 1 - $db_type"); 463 is($products->[0]{'vendor'}{'vendor'}{'id'}, 1, "p2 - require vendors 2 - $db_type"); 464 is($products->[0]{'vendor'}{'region'}{'name'}, 'America', "p2 - require vendors 3 - $db_type"); 465 466 is($products->[1]{'vendor'}{'id'}, 3, "p3 - require vendors 1 - $db_type"); 467 is($products->[1]{'vendor'}{'vendor'}{'id'}, 2, "p3 - require vendors 2 - $db_type"); 468 is($products->[1]{'vendor'}{'region'}{'name'}, 'England', "p3 - require vendors 3 - $db_type"); 469 470 $products = 471 $manager_class->get_products( 472 db => $db, 473 require_objects => [ 'vendor.vendor', 'vendor.region' ], 474 limit => 10, 475 offset => 1); 476 477 is(scalar @$products, 1, "offset require vendors 1 - $db_type"); 478 479 is($products->[0]{'vendor'}{'id'}, 3, "p3 - offset require vendors 1 - $db_type"); 480 is($products->[0]{'vendor'}{'vendor'}{'id'}, 2, "p3 - offset require vendors 2 - $db_type"); 481 is($products->[0]{'vendor'}{'region'}{'name'}, 'England', "p3 - offset require vendors 3 - $db_type"); 482 483 my $iterator = 484 $manager_class->get_products_iterator( 485 db => $db, 486 require_objects => [ 'vendor.vendor', 'vendor.region' ]); 487 488 my $p = $iterator->next; 489 is($p->{'vendor'}{'id'}, 2, "p2 - require vendors iterator 1 - $db_type"); 490 is($p->{'vendor'}{'vendor'}{'id'}, 1, "p2 - require vendors iterator 2 - $db_type"); 491 is($p->{'vendor'}{'region'}{'name'}, 'America', "p2 - require vendors iterator 3 - $db_type"); 492 493 $p = $iterator->next; 494 is($p->{'vendor'}{'id'}, 3, "p3 - require vendors iterator 1 - $db_type"); 495 is($p->{'vendor'}{'vendor'}{'id'}, 2, "p3 - require vendors iterator 2 - $db_type"); 496 is($p->{'vendor'}{'region'}{'name'}, 'England', "p3 - require vendors iterator 3 - $db_type"); 497 498 ok(!$iterator->next, "require vendors iterator 1 - $db_type"); 499 is($iterator->total, 2, "require vendors iterator 2 - $db_type"); 500 501 $iterator = 502 $manager_class->get_products_iterator( 503 db => $db, 504 require_objects => [ 'vendor.vendor', 'vendor.region' ], 505 limit => 10, 506 offset => 1); 507 508 $p = $iterator->next; 509 is($p->{'vendor'}{'id'}, 3, "p3 - offset require vendors iterator 1 - $db_type"); 510 is($p->{'vendor'}{'vendor'}{'id'}, 2, "p3 - offset require vendors iterator 2 - $db_type"); 511 is($p->{'vendor'}{'region'}{'name'}, 'England', "p3 - offset require vendors iterator 3 - $db_type"); 512 513 ok(!$iterator->next, "offset require vendors iterator 1 - $db_type"); 514 is($iterator->total, 1, "offset require vendors iterator 2 - $db_type"); 515 516 #local $Rose::DB::Object::Manager::Debug = 1; 517 518 $products = 519 $manager_class->get_products( 520 db => $db, 521 with_objects => [ 'colors.description.authors.nicknames' ], 522 multi_many_ok => 1, 523 limit => 2, 524 sort_by => [ 'colors.name DESC', 'authors.name' ]); 525 526 is($products->[0]{'colors'}[0]{'name'}, 'red', "p1 - with colors 1 - $db_type"); 527 is($products->[0]{'colors'}[1]{'name'}, 'blue', "p1 - with colors 2 - $db_type"); 528 is(scalar @{$products->[0]{'colors'}}, 2, "p1 - with colors 3 - $db_type"); 529 530 is($products->[0]{'colors'}[0]{'description'}{'text'}, 'desc 1', "p1 - with colors description 1 - $db_type"); 531 is($products->[0]{'colors'}[1]{'description'}{'text'}, 'desc 2', "p1 - with colors description 2 - $db_type"); 532 533 if(has_broken_order_by($db_type)) 534 { 535 $products->[0]{'colors'}[0]{'description'}{'authors'} = 536 [ sort { $a->{'name'} cmp $b->{'name'} } @{$products->[0]{'colors'}[0]{'description'}{'authors'}} ]; 537 } 538 539 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p1 - with colors description authors 1 - $db_type"); 540 is($products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p1 - with colors description authors 2 - $db_type"); 541 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}}, 2, "p1 - with colors description authors 3 - $db_type"); 542 543 if(has_broken_order_by($db_type)) 544 { 545 $products->[0]{'colors'}[1]{'description'}{'authors'} = 546 [ sort { $a->{'name'} cmp $b->{'name'} } @{$products->[0]{'colors'}[1]{'description'}{'authors'}} ]; 547 } 548 549 is($products->[0]{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'jane', "p1 - with colors description authors 4 - $db_type"); 550 is($products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'name'}, 'john', "p1 - with colors description authors 5 - $db_type"); 551 is(scalar @{$products->[0]{'colors'}[1]{'description'}{'authors'}}, 2, "p1 - with colors description authors 6 - $db_type"); 552 553 $products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 554 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 555 556 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'jack', "p1 - with colors description authors nicknames 1 - $db_type"); 557 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'sir', "p1 - with colors description authors nicknames 2 - $db_type"); 558 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p1 - with colors description authors nicknames 3 - $db_type"); 559 is($products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p1 - with colors description authors nicknames 4 - $db_type"); 560 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p1 - with colors description authors nicknames 5 - $db_type"); 561 562 $products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} = 563 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}} ]; 564 565 is($products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'jack', "p1 - with colors description authors nicknames 6 - $db_type"); 566 is($products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}[1]{'nick'}, 'sir', "p1 - with colors description authors nicknames 7 - $db_type"); 567 is(scalar @{$products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}}, 2, "p1 - with colors description authors nicknames 8 - $db_type"); 568 is($products->[0]{'colors'}[1]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'blub', "p1 - with colors description authors nicknames 9 - $db_type"); 569 is(scalar @{$products->[0]{'colors'}[1]{'description'}{'authors'}[0]{'nicknames'}}, 1, "p1 - with colors description authors nicknames 10 - $db_type"); 570 571 is($products->[1]{'colors'}[0]{'name'}, 'red', "p2 - with colors 1 - $db_type"); 572 is($products->[1]{'colors'}[1]{'name'}, 'green', "p2 - with colors 2 - $db_type"); 573 is(scalar @{$products->[1]{'colors'}}, 2, "p2 - with colors 3 - $db_type"); 574 575 is($products->[1]{'colors'}[0]{'description'}{'text'}, 'desc 1', "p2 - with colors description 1 - $db_type"); 576 is($products->[1]{'colors'}[1]{'description'}{'text'}, 'desc 3', "p2 - with colors description 2 - $db_type"); 577 578 is($products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p2 - with colors description authors 1 - $db_type"); 579 is($products->[1]{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p2 - with colors description authors 2 - $db_type"); 580 is(scalar @{$products->[1]{'colors'}[0]{'description'}{'authors'}}, 2, "p2 - with colors description authors 3 - $db_type"); 581 582 is($products->[1]{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'tim', "p2 - with colors description authors 4 - $db_type"); 583 is(scalar @{$products->[1]{'colors'}[1]{'description'}{'authors'}}, 1, "p2 - with colors description authors 6 - $db_type"); 584 585 $products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 586 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 587 588 is($products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'jack', "p2 - with colors description authors nicknames 1 - $db_type"); 589 is($products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'sir', "p2 - with colors description authors nicknames 2 - $db_type"); 590 is(scalar @{$products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p2 - with colors description authors nicknames 3 - $db_type"); 591 is($products->[1]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p2 - with colors description authors nicknames 4 - $db_type"); 592 is(scalar @{$products->[1]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p2 - with colors description authors nicknames 5 - $db_type"); 593 594 is(scalar @{$products->[1]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} || []}, 0, "p2 - with colors description authors nicknames 6 - $db_type"); 595 596 $products = 597 $manager_class->get_products( 598 db => $db, 599 with_objects => [ 'colors.description.authors.nicknames' ], 600 multi_many_ok => 1, 601 limit => 1, 602 offset => 1, 603 sort_by => [ 'colors.name DESC', 'authors.name' ]); 604 605 is($products->[0]{'colors'}[0]{'name'}, 'red', "p2 - offset with colors 1 - $db_type"); 606 is($products->[0]{'colors'}[1]{'name'}, 'green', "p2 - offset with colors 2 - $db_type"); 607 is(scalar @{$products->[0]{'colors'}}, 2, "p2 - offset with colors 3 - $db_type"); 608 609 is($products->[0]{'colors'}[0]{'description'}{'text'}, 'desc 1', "p2 - offset with colors description 1 - $db_type"); 610 is($products->[0]{'colors'}[1]{'description'}{'text'}, 'desc 3', "p2 - offset with colors description 2 - $db_type"); 611 612 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p2 - offset with colors description authors 1 - $db_type"); 613 is($products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p2 - offset with colors description authors 2 - $db_type"); 614 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}}, 2, "p2 - offset with colors description authors 3 - $db_type"); 615 616 is($products->[0]{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'tim', "p2 - offset with colors description authors 4 - $db_type"); 617 is(scalar @{$products->[0]{'colors'}[1]{'description'}{'authors'}}, 1, "p2 - offset with colors description authors 6 - $db_type"); 618 619 $products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 620 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 621 622 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'jack', "p2 - offset with colors description authors nicknames 1 - $db_type"); 623 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'sir', "p2 - offset with colors description authors nicknames 2 - $db_type"); 624 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p2 - offset with colors description authors nicknames 3 - $db_type"); 625 is($products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p2 - offset with colors description authors nicknames 4 - $db_type"); 626 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p2 - offset with colors description authors nicknames 5 - $db_type"); 627 628 is(scalar @{$products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} || []}, 0, "p2 - offset with colors description authors nicknames 6 - $db_type"); 629 630 $products = 631 $manager_class->get_products( 632 db => $db, 633 with_objects => [ 'colors.description.authors.nicknames' ], 634 multi_many_ok => 1, 635 limit => 1, 636 offset => 1, 637 sort_by => [ 'colors.name DESC', 'authors.name' ]); 638 639 Rose::DB::Object::Helpers::strip($products->[0], leave => [ 'related_objects' ]); 640 Rose::DB::Object::Helpers::strip($products->[0], leave => 'foreign_keys'); 641 Rose::DB::Object::Helpers::strip($products->[0], leave => [ 'relationships' ]); 642 Rose::DB::Object::Helpers::strip($products->[0]); 643 644 $iterator = 645 $manager_class->get_products_iterator( 646 db => $db, 647 with_objects => [ 'colors.description.authors.nicknames' ], 648 multi_many_ok => 1, 649 limit => 2, 650 sort_by => [ 'colors.name DESC', 'authors.name' ]); 651 652 $p = $iterator->next; 653 is($p->{'colors'}[0]{'name'}, 'red', "p1 - iterator with colors 1 - $db_type"); 654 is($p->{'colors'}[1]{'name'}, 'blue', "p1 - iterator with colors 2 - $db_type"); 655 is(scalar @{$p->{'colors'}}, 2, "p1 - iterator with colors 3 - $db_type"); 656 657 is($p->{'colors'}[0]{'description'}{'text'}, 'desc 1', "p1 - iterator with colors description 1 - $db_type"); 658 is($p->{'colors'}[1]{'description'}{'text'}, 'desc 2', "p1 - iterator with colors description 2 - $db_type"); 659 660 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p1 - iterator with colors description authors 1 - $db_type"); 661 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p1 - iterator with colors description authors 2 - $db_type"); 662 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}}, 2, "p1 - iterator with colors description authors 3 - $db_type"); 663 664 if(has_broken_order_by($db_type)) 665 { 666 $p->{'colors'}[1]{'description'}{'authors'} = 667 [ sort { $a->{'name'} cmp $b->{'name'} } @{$p->{'colors'}[1]{'description'}{'authors'}} ]; 668 } 669 670 is($p->{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'jane', "p1 - iterator with colors description authors 4 - $db_type"); 671 is($p->{'colors'}[1]{'description'}{'authors'}[1]{'name'}, 'john', "p1 - iterator with colors description authors 5 - $db_type"); 672 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}}, 2, "p1 - iterator with colors description authors 6 - $db_type"); 673 674 $p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 675 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 676 677 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'jack', "p1 - iterator with colors description authors nicknames 1 - $db_type"); 678 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'sir', "p1 - iterator with colors description authors nicknames 2 - $db_type"); 679 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p1 - iterator with colors description authors nicknames 3 - $db_type"); 680 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p1 - iterator with colors description authors nicknames 4 - $db_type"); 681 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p1 - iterator with colors description authors nicknames 5 - $db_type"); 682 683 $p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} = 684 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}} ]; 685 686 is($p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'jack', "p1 - iterator with colors description authors nicknames 6 - $db_type"); 687 is($p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}[1]{'nick'}, 'sir', "p1 - iterator with colors description authors nicknames 7 - $db_type"); 688 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}}, 2, "p1 - iterator with colors description authors nicknames 8 - $db_type"); 689 is($p->{'colors'}[1]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'blub', "p1 - iterator with colors description authors nicknames 9 - $db_type"); 690 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}[0]{'nicknames'}}, 1, "p1 - iterator with colors description authors nicknames 10 - $db_type"); 691 692 $p = $iterator->next; 693 is($p->{'colors'}[0]{'name'}, 'red', "p2 - iterator with colors 1 - $db_type"); 694 is($p->{'colors'}[1]{'name'}, 'green', "p2 - iterator with colors 2 - $db_type"); 695 is(scalar @{$p->{'colors'}}, 2, "p2 - iterator with colors 3 - $db_type"); 696 697 is($p->{'colors'}[0]{'description'}{'text'}, 'desc 1', "p2 - iterator with colors description 1 - $db_type"); 698 is($p->{'colors'}[1]{'description'}{'text'}, 'desc 3', "p2 - iterator with colors description 2 - $db_type"); 699 700 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p2 - iterator with colors description authors 1 - $db_type"); 701 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p2 - iterator with colors description authors 2 - $db_type"); 702 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}}, 2, "p2 - iterator with colors description authors 3 - $db_type"); 703 704 is($p->{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'tim', "p2 - iterator with colors description authors 4 - $db_type"); 705 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}}, 1, "p2 - iterator with colors description authors 6 - $db_type"); 706 707 $p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 708 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 709 710 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'jack', "p2 - iterator with colors description authors nicknames 1 - $db_type"); 711 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'sir', "p2 - iterator with colors description authors nicknames 2 - $db_type"); 712 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p2 - iterator with colors description authors nicknames 3 - $db_type"); 713 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p2 - iterator with colors description authors nicknames 4 - $db_type"); 714 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p2 - iterator with colors description authors nicknames 5 - $db_type"); 715 716 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} || []}, 0, "p2 - iterator with colors description authors nicknames 6 - $db_type"); 717 718 ok(!$iterator->next, "iterator with colors description authors nicknames 1 - $db_type"); 719 is($iterator->total, 2, "iterator with colors description authors nicknames 2 - $db_type"); 720 721 $iterator = 722 $manager_class->get_products_iterator( 723 db => $db, 724 with_objects => [ 'colors.description.authors.nicknames' ], 725 multi_many_ok => 1, 726 limit => 1, 727 offset => 1, 728 sort_by => [ 'colors.name DESC', 'authors.name' ]); 729 730 $p = $iterator->next; 731 is($p->{'colors'}[0]{'name'}, 'red', "p2 - offset iterator with colors 1 - $db_type"); 732 is($p->{'colors'}[1]{'name'}, 'green', "p2 - offset iterator with colors 2 - $db_type"); 733 is(scalar @{$p->{'colors'}}, 2, "p2 - offset iterator with colors 3 - $db_type"); 734 735 is($p->{'colors'}[0]{'description'}{'text'}, 'desc 1', "p2 - offset iterator with colors description 1 - $db_type"); 736 is($p->{'colors'}[1]{'description'}{'text'}, 'desc 3', "p2 - offset iterator with colors description 2 - $db_type"); 737 738 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p2 - offset iterator with colors description authors 1 - $db_type"); 739 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p2 - offset iterator with colors description authors 2 - $db_type"); 740 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}}, 2, "p2 - offset iterator with colors description authors 3 - $db_type"); 741 742 is($p->{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'tim', "p2 - offset iterator with colors description authors 4 - $db_type"); 743 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}}, 1, "p2 - offset iterator with colors description authors 6 - $db_type"); 744 745 $p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 746 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 747 748 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'jack', "p2 - offset iterator with colors description authors nicknames 1 - $db_type"); 749 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'sir', "p2 - offset iterator with colors description authors nicknames 2 - $db_type"); 750 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p2 - offset iterator with colors description authors nicknames 3 - $db_type"); 751 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p2 - offset iterator with colors description authors nicknames 4 - $db_type"); 752 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p2 - offset iterator with colors description authors nicknames 5 - $db_type"); 753 754 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} || []}, 0, "p2 - offset iterator with colors description authors nicknames 6 - $db_type"); 755 756 ok(!$iterator->next, "offset iterator with colors description authors nicknames 1 - $db_type"); 757 is($iterator->total, 1, "offset iterator with colors description authors nicknames 2 - $db_type"); 758 759 #local $Rose::DB::Object::Manager::Debug = 1; 760 761 $products = 762 $manager_class->get_products( 763 db => $db, 764 require_objects => [ 'vendor.region', 'prices.region' ], 765 with_objects => [ 'colors.description.authors.nicknames' ], 766 multi_many_ok => 1, 767 limit => 2, 768 sort_by => [ 'colors.name DESC', 'authors.name' ]); 769 770 #exit; 771 772 is($products->[0]{'vendor'}{'name'}, 'V1', "p1 - vendor 1 - $db_type"); 773 is($products->[0]{'vendor'}{'region'}{'name'}, 'Germany', "p1 - vendor 2 - $db_type"); 774 775 is($products->[1]{'vendor'}{'name'}, 'V2', "p2 - vendor 1 - $db_type"); 776 is($products->[1]{'vendor'}{'region'}{'name'}, 'America', "p2 - vendor 2 - $db_type"); 777 778 is(scalar @{$products->[0]{'prices'}}, 2, "p1 - prices 1 - $db_type"); 779 is(scalar @{$products->[1]{'prices'}}, 1, "p2 - prices 2 - $db_type"); 780 781 $products->[0]{'prices'} = [ sort { $a->{'price'} <=> $b->{'price'} } @{$products->[0]{'prices'}} ]; 782 $products->[1]{'prices'} = [ sort { $a->{'price'} <=> $b->{'price'} } @{$products->[1]{'prices'}} ]; 783 784 is($products->[0]{'prices'}[0]{'price'}, 1.25, "p1 - prices 2 - $db_type"); 785 is($products->[0]{'prices'}[0]{'region'}{'name'}, 'America', "p1 - prices 3 - $db_type"); 786 is($products->[0]{'prices'}[1]{'price'}, 4.25, "p1 - prices 4 - $db_type"); 787 is($products->[0]{'prices'}[1]{'region'}{'name'}, 'Germany', "p1 - prices 5 - $db_type"); 788 789 is($products->[1]{'prices'}[0]{'price'}, 9.25, "p2 - prices 2 - $db_type"); 790 is($products->[1]{'prices'}[0]{'region'}{'name'}, 'America', "p2 - prices 3 - $db_type"); 791 792 if(has_broken_order_by($db_type)) 793 { 794 $products->[0]{'colors'} = 795 [ sort { $b->{'name'} cmp $a->{'name'} } @{$products->[0]{'colors'}} ]; 796 797 $products->[0]{'colors'}[0]{'description'}{'authors'} = 798 [ sort { $a->{'name'} cmp $b->{'name'} } @{$products->[0]{'colors'}[0]{'description'}{'authors'}} ]; 799 800 $products->[0]{'colors'}[1]{'description'}{'authors'} = 801 [ sort { $a->{'name'} cmp $b->{'name'} } @{$products->[0]{'colors'}[1]{'description'}{'authors'}} ]; 802 } 803 804 is($products->[0]{'colors'}[0]{'name'}, 'red', "p1 - with colors vendors 1 - $db_type"); 805 is($products->[0]{'colors'}[1]{'name'}, 'blue', "p1 - with colors vendors 2 - $db_type"); 806 is(scalar @{$products->[0]{'colors'}}, 2, "p1 - with colors vendors 3 - $db_type"); 807 808 is($products->[0]{'colors'}[0]{'description'}{'text'}, 'desc 1', "p1 - with colors vendors description 1 - $db_type"); 809 is($products->[0]{'colors'}[1]{'description'}{'text'}, 'desc 2', "p1 - with colors vendors description 2 - $db_type"); 810 811 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p1 - with colors vendors description authors 1 - $db_type"); 812 is($products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p1 - with colors vendors description authors 2 - $db_type"); 813 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}}, 2, "p1 - with colors vendors description authors 3 - $db_type"); 814 815 is($products->[0]{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'jane', "p1 - with colors vendors description authors 4 - $db_type"); 816 is($products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'name'}, 'john', "p1 - with colors vendors description authors 5 - $db_type"); 817 is(scalar @{$products->[0]{'colors'}[1]{'description'}{'authors'}}, 2, "p1 - with colors vendors description authors 6 - $db_type"); 818 819 $products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 820 [ sort { $b->{'nick'} cmp $a->{'nick'} } @{$products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 821 822 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'sir', "p1 - with colors vendors description authors nicknames 1 - $db_type"); 823 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'jack', "p1 - with colors vendors description authors nicknames 2 - $db_type"); 824 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p1 - with colors vendors description authors nicknames 3 - $db_type"); 825 is($products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p1 - with colors vendors description authors nicknames 4 - $db_type"); 826 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p1 - with colors vendors description authors nicknames 5 - $db_type"); 827 828 $products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} = 829 [ sort { $b->{'nick'} cmp $a->{'nick'} } @{$products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}} ]; 830 831 is($products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sir', "p1 - with colors vendors description authors nicknames 6 - $db_type"); 832 is($products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}[1]{'nick'}, 'jack', "p1 - with colors vendors description authors nicknames 7 - $db_type"); 833 is(scalar @{$products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}}, 2, "p1 - with colors vendors description authors nicknames 8 - $db_type"); 834 is($products->[0]{'colors'}[1]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'blub', "p1 - with colors vendors description authors nicknames 9 - $db_type"); 835 is(scalar @{$products->[0]{'colors'}[1]{'description'}{'authors'}[0]{'nicknames'}}, 1, "p1 - with colors vendors description authors nicknames 10 - $db_type"); 836 837 is($products->[1]{'colors'}[0]{'name'}, 'red', "p2 - with colors vendors 1 - $db_type"); 838 is($products->[1]{'colors'}[1]{'name'}, 'green', "p2 - with colors vendors 2 - $db_type"); 839 is(scalar @{$products->[1]{'colors'}}, 2, "p2 - with colors vendors 3 - $db_type"); 840 841 is($products->[1]{'colors'}[0]{'description'}{'text'}, 'desc 1', "p2 - with colors vendors description 1 - $db_type"); 842 is($products->[1]{'colors'}[1]{'description'}{'text'}, 'desc 3', "p2 - with colors vendors description 2 - $db_type"); 843 844 is($products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p2 - with colors vendors description authors 1 - $db_type"); 845 is($products->[1]{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p2 - with colors vendors description authors 2 - $db_type"); 846 is(scalar @{$products->[1]{'colors'}[0]{'description'}{'authors'}}, 2, "p2 - with colors vendors description authors 3 - $db_type"); 847 848 is($products->[1]{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'tim', "p2 - with colors vendors description authors 4 - $db_type"); 849 is(scalar @{$products->[1]{'colors'}[1]{'description'}{'authors'}}, 1, "p2 - with colors vendors description authors 6 - $db_type"); 850 851 $products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 852 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 853 854 is($products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'jack', "p2 - with colors vendors description authors nicknames 1 - $db_type"); 855 is($products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'sir', "p2 - with colors vendors description authors nicknames 2 - $db_type"); 856 is(scalar @{$products->[1]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p2 - with colors vendors description authors nicknames 3 - $db_type"); 857 is($products->[1]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p2 - with colors vendors description authors nicknames 4 - $db_type"); 858 is(scalar @{$products->[1]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p2 - with colors vendors description authors nicknames 5 - $db_type"); 859 860 is(scalar @{$products->[1]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} || []}, 0, "p2 - with colors vendors description authors nicknames 6 - $db_type"); 861 862 $products = 863 $manager_class->get_products( 864 db => $db, 865 require_objects => [ 'vendor.region', 'prices.region' ], 866 with_objects => [ 'colors.description.authors.nicknames' ], 867 multi_many_ok => 1, 868 limit => 1, 869 offset => 1, 870 sort_by => [ 'colors.name DESC', 'authors.name' ]); 871 872 is($products->[0]{'vendor'}{'name'}, 'V2', "p2 - offset vendor 1 - $db_type"); 873 is($products->[0]{'vendor'}{'region'}{'name'}, 'America', "p2 - offset vendor 2 - $db_type"); 874 875 is(scalar @{$products->[0]{'prices'}}, 1, "p1 - offset prices 1 - $db_type"); 876 877 $products->[0]{'prices'} = [ sort { $a->{'price'} <=> $b->{'price'} } @{$products->[0]{'prices'}} ]; 878 879 is($products->[0]{'prices'}[0]{'price'}, 9.25, "p2 - offset prices 2 - $db_type"); 880 is($products->[0]{'prices'}[0]{'region'}{'name'}, 'America', "p2 - offset prices 3 - $db_type"); 881 882 is($products->[0]{'colors'}[0]{'name'}, 'red', "p2 - offset with colors vendors 1 - $db_type"); 883 is($products->[0]{'colors'}[1]{'name'}, 'green', "p2 - offset with colors vendors 2 - $db_type"); 884 is(scalar @{$products->[0]{'colors'}}, 2, "p2 - offset with colors vendors 3 - $db_type"); 885 886 is($products->[0]{'colors'}[0]{'description'}{'text'}, 'desc 1', "p2 - offset with colors vendors description 1 - $db_type"); 887 is($products->[0]{'colors'}[1]{'description'}{'text'}, 'desc 3', "p2 - offset with colors vendors description 2 - $db_type"); 888 889 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p2 - offset with colors vendors description authors 1 - $db_type"); 890 is($products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p2 - offset with colors vendors description authors 2 - $db_type"); 891 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}}, 2, "p2 - offset with colors vendors description authors 3 - $db_type"); 892 893 is($products->[0]{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'tim', "p2 - offset with colors vendors description authors 4 - $db_type"); 894 is(scalar @{$products->[0]{'colors'}[1]{'description'}{'authors'}}, 1, "p2 - offset with colors vendors description authors 6 - $db_type"); 895 896 $products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 897 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 898 899 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'jack', "p2 - offset with colors vendors description authors nicknames 1 - $db_type"); 900 is($products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'sir', "p2 - offset with colors vendors description authors nicknames 2 - $db_type"); 901 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p2 - offset with colors vendors description authors nicknames 3 - $db_type"); 902 is($products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p2 - offset with colors vendors description authors nicknames 4 - $db_type"); 903 is(scalar @{$products->[0]{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p2 - offset with colors vendors description authors nicknames 5 - $db_type"); 904 905 is(scalar @{$products->[0]{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} || []}, 0, "p2 - offset with colors vendors description authors nicknames 6 - $db_type"); 906 907 $iterator = 908 $manager_class->get_products_iterator( 909 db => $db, 910 require_objects => [ 'vendor.region', 'prices.region' ], 911 with_objects => [ 'colors.description.authors.nicknames' ], 912 multi_many_ok => 1, 913 limit => 2, 914 sort_by => [ 'colors.name DESC', 'authors.name' ]); 915 916 $p = $iterator->next; 917 is($p->{'vendor'}{'name'}, 'V1', "p1 - iterator vendor 1 - $db_type"); 918 is($p->{'vendor'}{'region'}{'name'}, 'Germany', "p1 - iterator vendor 2 - $db_type"); 919 920 is(scalar @{$p->{'prices'}}, 2, "p1 - iterator prices 1 - $db_type"); 921 922 $p->{'prices'} = [ sort { $a->{'price'} <=> $b->{'price'} } @{$p->{'prices'}} ]; 923 924 is($p->{'prices'}[0]{'price'}, 1.25, "p1 - iterator prices 2 - $db_type"); 925 is($p->{'prices'}[0]{'region'}{'name'}, 'America', "p1 - iterator prices 3 - $db_type"); 926 is($p->{'prices'}[1]{'price'}, 4.25, "p1 - iterator prices 4 - $db_type"); 927 is($p->{'prices'}[1]{'region'}{'name'}, 'Germany', "p1 - iterator prices 5 - $db_type"); 928 929 is($p->{'colors'}[0]{'name'}, 'red', "p1 - iterator with colors vendors 1 - $db_type"); 930 is($p->{'colors'}[1]{'name'}, 'blue', "p1 - iterator with colors vendors 2 - $db_type"); 931 is(scalar @{$p->{'colors'}}, 2, "p1 - iterator with colors vendors 3 - $db_type"); 932 933 is($p->{'colors'}[0]{'description'}{'text'}, 'desc 1', "p1 - iterator with colors vendors description 1 - $db_type"); 934 is($p->{'colors'}[1]{'description'}{'text'}, 'desc 2', "p1 - iterator with colors vendors description 2 - $db_type"); 935 936 if(has_broken_order_by($db_type)) 937 { 938 $p->{'colors'}[0]{'description'}{'authors'} = 939 [ sort { $a->{'name'} cmp $b->{'name'} } @{$p->{'colors'}[0]{'description'}{'authors'}} ]; 940 941 $p->{'colors'}[1]{'description'}{'authors'} = 942 [ sort { $a->{'name'} cmp $b->{'name'} } @{$p->{'colors'}[1]{'description'}{'authors'}} ]; 943 } 944 945 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p1 - iterator with colors vendors description authors 1 - $db_type"); 946 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p1 - iterator with colors vendors description authors 2 - $db_type"); 947 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}}, 2, "p1 - iterator with colors vendors description authors 3 - $db_type"); 948 949 is($p->{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'jane', "p1 - iterator with colors vendors description authors 4 - $db_type"); 950 is($p->{'colors'}[1]{'description'}{'authors'}[1]{'name'}, 'john', "p1 - iterator with colors vendors description authors 5 - $db_type"); 951 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}}, 2, "p1 - iterator with colors vendors description authors 6 - $db_type"); 952 953 $p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 954 [ sort { $b->{'nick'} cmp $a->{'nick'} } @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 955 956 $p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} = 957 [ sort { $b->{'nick'} cmp $a->{'nick'} } @{$p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}} ]; 958 959 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'sir', "p1 - iterator with colors vendors description authors nicknames 1 - $db_type"); 960 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'jack', "p1 - iterator with colors vendors description authors nicknames 2 - $db_type"); 961 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p1 - iterator with colors vendors description authors nicknames 3 - $db_type"); 962 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p1 - iterator with colors vendors description authors nicknames 4 - $db_type"); 963 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p1 - iterator with colors vendors description authors nicknames 5 - $db_type"); 964 965 is($p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sir', "p1 - iterator with colors vendors description authors nicknames 6 - $db_type"); 966 is($p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}[1]{'nick'}, 'jack', "p1 - iterator with colors vendors description authors nicknames 7 - $db_type"); 967 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'}}, 2, "p1 - iterator with colors vendors description authors nicknames 8 - $db_type"); 968 is($p->{'colors'}[1]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'blub', "p1 - iterator with colors vendors description authors nicknames 9 - $db_type"); 969 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}[0]{'nicknames'}}, 1, "p1 - iterator with colors vendors description authors nicknames 10 - $db_type"); 970 971 $p = $iterator->next; 972 is($p->{'vendor'}{'name'}, 'V2', "p2 - iterator vendor 1 - $db_type"); 973 is($p->{'vendor'}{'region'}{'name'}, 'America', "p2 - iterator vendor 2 - $db_type"); 974 975 $p->{'prices'} = [ sort { $a->{'price'} <=> $b->{'price'} } @{$p->{'prices'}} ]; 976 977 is(scalar @{$p->{'prices'}}, 1, "p2 - iterator prices 2 - $db_type"); 978 is($p->{'prices'}[0]{'price'}, 9.25, "p2 - iterator prices 2 - $db_type"); 979 is($p->{'prices'}[0]{'region'}{'name'}, 'America', "p2 - iterator prices 3 - $db_type"); 980 981 is($p->{'colors'}[0]{'name'}, 'red', "p2 - iterator with colors vendors 1 - $db_type"); 982 is($p->{'colors'}[1]{'name'}, 'green', "p2 - iterator with colors vendors 2 - $db_type"); 983 is(scalar @{$p->{'colors'}}, 2, "p2 - iterator with colors vendors 3 - $db_type"); 984 985 is($p->{'colors'}[0]{'description'}{'text'}, 'desc 1', "p2 - iterator with colors vendors description 1 - $db_type"); 986 is($p->{'colors'}[1]{'description'}{'text'}, 'desc 3', "p2 - iterator with colors vendors description 2 - $db_type"); 987 988 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p2 - iterator with colors vendors description authors 1 - $db_type"); 989 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p2 - iterator with colors vendors description authors 2 - $db_type"); 990 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}}, 2, "p2 - iterator with colors vendors description authors 3 - $db_type"); 991 992 is($p->{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'tim', "p2 - iterator with colors vendors description authors 4 - $db_type"); 993 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}}, 1, "p2 - iterator with colors vendors description authors 6 - $db_type"); 994 995 $p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 996 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 997 998 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'jack', "p2 - iterator with colors vendors description authors nicknames 1 - $db_type"); 999 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'sir', "p2 - iterator with colors vendors description authors nicknames 2 - $db_type"); 1000 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p2 - iterator with colors vendors description authors nicknames 3 - $db_type"); 1001 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p2 - iterator with colors vendors description authors nicknames 4 - $db_type"); 1002 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p2 - iterator with colors vendors description authors nicknames 5 - $db_type"); 1003 1004 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} || []}, 0, "p2 - iterator with colors vendors description authors nicknames 6 - $db_type"); 1005 1006 $iterator = 1007 $manager_class->get_products_iterator( 1008 db => $db, 1009 require_objects => [ 'vendor.region', 'prices.region' ], 1010 with_objects => [ 'colors.description.authors.nicknames' ], 1011 multi_many_ok => 1, 1012 limit => 1, 1013 offset => 1, 1014 sort_by => [ 'colors.name DESC', 'authors.name' ]); 1015 1016 $p = $iterator->next; 1017 is($p->{'vendor'}{'name'}, 'V2', "p2 - offset iterator vendor 1 - $db_type"); 1018 is($p->{'vendor'}{'region'}{'name'}, 'America', "p2 - offset iterator vendor 2 - $db_type"); 1019 1020 $p->{'prices'} = [ sort { $a->{'price'} <=> $b->{'price'} } @{$p->{'prices'}} ]; 1021 1022 is(scalar @{$p->{'prices'}}, 1, "p2 - offset iterator prices 2 - $db_type"); 1023 is($p->{'prices'}[0]{'price'}, 9.25, "p2 - offset iterator prices 2 - $db_type"); 1024 is($p->{'prices'}[0]{'region'}{'name'}, 'America', "p2 - offset iterator prices 3 - $db_type"); 1025 1026 is($p->{'colors'}[0]{'name'}, 'red', "p2 - offset iterator with colors vendors 1 - $db_type"); 1027 is($p->{'colors'}[1]{'name'}, 'green', "p2 - offset iterator with colors vendors 2 - $db_type"); 1028 is(scalar @{$p->{'colors'}}, 2, "p2 - offset iterator with colors vendors 3 - $db_type"); 1029 1030 is($p->{'colors'}[0]{'description'}{'text'}, 'desc 1', "p2 - offset iterator with colors vendors description 1 - $db_type"); 1031 is($p->{'colors'}[1]{'description'}{'text'}, 'desc 3', "p2 - offset iterator with colors vendors description 2 - $db_type"); 1032 1033 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p2 - offset iterator with colors vendors description authors 1 - $db_type"); 1034 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p2 - offset iterator with colors vendors description authors 2 - $db_type"); 1035 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}}, 2, "p2 - offset iterator with colors vendors description authors 3 - $db_type"); 1036 1037 is($p->{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'tim', "p2 - offset iterator with colors vendors description authors 4 - $db_type"); 1038 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}}, 1, "p2 - offset iterator with colors vendors description authors 6 - $db_type"); 1039 1040 $p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 1041 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 1042 1043 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'jack', "p2 - offset iterator with colors vendors description authors nicknames 1 - $db_type"); 1044 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'sir', "p2 - offset iterator with colors vendors description authors nicknames 2 - $db_type"); 1045 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p2 - offset iterator with colors vendors description authors nicknames 3 - $db_type"); 1046 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p2 - offset iterator with colors vendors description authors nicknames 4 - $db_type"); 1047 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p2 - offset iterator with colors vendors description authors nicknames 5 - $db_type"); 1048 1049 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} || []}, 0, "p2 - offset iterator with colors vendors description authors nicknames 6 - $db_type"); 1050 1051 ok(!$iterator->next, "offset iterator with colors vendors description authors nicknames 1 - $db_type"); 1052 is($iterator->total, 1, "offset iterator with colors vendors description authors nicknames 2 - $db_type"); 1053 1054 #local $Rose::DB::Object::Manager::Debug = 1; 1055 1056 $iterator = 1057 $manager_class->get_products_iterator( 1058 db => $db, 1059 require_objects => [ 'vendor.region', 'prices.region' ], 1060 with_objects => [ 'colors.description.authors.nicknames' ], 1061 multi_many_ok => 1, 1062 query => [ 'vendor.region.name' => 'America' ], 1063 sort_by => [ 'colors.name DESC', 'authors.name' ]); 1064 1065 $p = $iterator->next; 1066 is($p->{'vendor'}{'name'}, 'V2', "p2 - query iterator vendor 1 - $db_type"); 1067 is($p->{'vendor'}{'region'}{'name'}, 'America', "p2 - query iterator vendor 2 - $db_type"); 1068 1069 $p->{'prices'} = [ sort { $a->{'price'} <=> $b->{'price'} } @{$p->{'prices'}} ]; 1070 1071 is(scalar @{$p->{'prices'}}, 1, "p2 - query iterator prices 2 - $db_type"); 1072 is($p->{'prices'}[0]{'price'}, 9.25, "p2 - query iterator prices 2 - $db_type"); 1073 is($p->{'prices'}[0]{'region'}{'name'}, 'America', "p2 - query iterator prices 3 - $db_type"); 1074 1075 is($p->{'colors'}[0]{'name'}, 'red', "p2 - query iterator with colors vendors 1 - $db_type"); 1076 is($p->{'colors'}[1]{'name'}, 'green', "p2 - query iterator with colors vendors 2 - $db_type"); 1077 is(scalar @{$p->{'colors'}}, 2, "p2 - query iterator with colors vendors 3 - $db_type"); 1078 1079 is($p->{'colors'}[0]{'description'}{'text'}, 'desc 1', "p2 - query iterator with colors vendors description 1 - $db_type"); 1080 is($p->{'colors'}[1]{'description'}{'text'}, 'desc 3', "p2 - query iterator with colors vendors description 2 - $db_type"); 1081 1082 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'name'}, 'john', "p2 - query iterator with colors vendors description authors 1 - $db_type"); 1083 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'name'}, 'sue', "p2 - query iterator with colors vendors description authors 2 - $db_type"); 1084 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}}, 2, "p2 - query iterator with colors vendors description authors 3 - $db_type"); 1085 1086 is($p->{'colors'}[1]{'description'}{'authors'}[0]{'name'}, 'tim', "p2 - query iterator with colors vendors description authors 4 - $db_type"); 1087 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}}, 1, "p2 - query iterator with colors vendors description authors 6 - $db_type"); 1088 1089 $p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'} = 1090 [ sort { $a->{'nick'} cmp $b->{'nick'} } @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}} ]; 1091 1092 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[0]{'nick'}, 'jack', "p2 - query iterator with colors vendors description authors nicknames 1 - $db_type"); 1093 is($p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}[1]{'nick'}, 'sir', "p2 - query iterator with colors vendors description authors nicknames 2 - $db_type"); 1094 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[0]{'nicknames'}}, 2, "p2 - query iterator with colors vendors description authors nicknames 3 - $db_type"); 1095 is($p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}[0]{'nick'}, 'sioux', "p2 - query iterator with colors vendors description authors nicknames 4 - $db_type"); 1096 is(scalar @{$p->{'colors'}[0]{'description'}{'authors'}[1]{'nicknames'}}, 1, "p2 - query iterator with colors vendors description authors nicknames 5 - $db_type"); 1097 1098 is(scalar @{$p->{'colors'}[1]{'description'}{'authors'}[1]{'nicknames'} || []}, 0, "p2 - query iterator with colors vendors description authors nicknames 6 - $db_type"); 1099 1100 ok(!$iterator->next, "query iterator with colors vendors description authors nicknames 1 - $db_type"); 1101 is($iterator->total, 1, "query iterator with colors vendors description authors nicknames 2 - $db_type"); 1102 1103 # End test of the subselect limit code 1104 #Rose::DB::Object::Manager->default_limit_with_subselect(0); 1105} 1106 1107BEGIN 1108{ 1109 our %Have; 1110 1111 # 1112 # PostgreSQL 1113 # 1114 1115 my $dbh; 1116 1117 eval 1118 { 1119 $dbh = Rose::DB->new('pg_admin')->retain_dbh() 1120 or die Rose::DB->error; 1121 1122 #die "This test chokes DBD::Pg version 2.1.x and 2.2.0" if($DBD::Pg::VERSION =~ /^2\.(?:1\.|2\.0)/); 1123 }; 1124 1125 if(!$@ && $dbh) 1126 { 1127 $Have{'pg'} = 1; 1128 $Have{'pg_with_schema'} = 1; 1129 1130 # Drop existing tables and create schema, ignoring errors 1131 { 1132 local $dbh->{'RaiseError'} = 0; 1133 local $dbh->{'PrintError'} = 0; 1134 $dbh->do('DROP TABLE product_color_map CASCADE'); 1135 $dbh->do('DROP TABLE colors CASCADE'); 1136 $dbh->do('DROP TABLE description_author_map CASCADE'); 1137 $dbh->do('DROP TABLE nicknames CASCADE'); 1138 $dbh->do('DROP TABLE authors CASCADE'); 1139 $dbh->do('DROP TABLE descriptions CASCADE'); 1140 $dbh->do('DROP TABLE prices CASCADE'); 1141 $dbh->do('DROP TABLE products CASCADE'); 1142 $dbh->do('DROP TABLE vendors CASCADE'); 1143 $dbh->do('DROP TABLE regions CASCADE'); 1144 1145 $dbh->do('DROP TABLE Rose_db_object_private.product_color_map CASCADE'); 1146 $dbh->do('DROP TABLE Rose_db_object_private.colors CASCADE'); 1147 $dbh->do('DROP TABLE Rose_db_object_private.description_author_map CASCADE'); 1148 $dbh->do('DROP TABLE Rose_db_object_private.nicknames CASCADE'); 1149 $dbh->do('DROP TABLE Rose_db_object_private.authors CASCADE'); 1150 $dbh->do('DROP TABLE Rose_db_object_private.descriptions CASCADE'); 1151 $dbh->do('DROP TABLE Rose_db_object_private.prices CASCADE'); 1152 $dbh->do('DROP TABLE Rose_db_object_private.products CASCADE'); 1153 $dbh->do('DROP TABLE Rose_db_object_private.vendors CASCADE'); 1154 $dbh->do('DROP TABLE Rose_db_object_private.regions CASCADE'); 1155 1156 $dbh->do('CREATE SCHEMA Rose_db_object_private'); 1157 } 1158 1159 $dbh->do(<<"EOF"); 1160CREATE TABLE regions 1161( 1162 id CHAR(2) NOT NULL PRIMARY KEY, 1163 name VARCHAR(32) NOT NULL, 1164 1165 UNIQUE(name) 1166) 1167EOF 1168 1169 $dbh->do(<<"EOF"); 1170CREATE TABLE vendors 1171( 1172 id SERIAL NOT NULL PRIMARY KEY, 1173 name VARCHAR(255) NOT NULL, 1174 1175 vendor_id INT REFERENCES vendors (id), 1176 region_id CHAR(2) REFERENCES regions (id), 1177 1178 UNIQUE(name) 1179) 1180EOF 1181 1182 $dbh->do(<<"EOF"); 1183CREATE TABLE products 1184( 1185 id SERIAL NOT NULL PRIMARY KEY, 1186 name VARCHAR(255) NOT NULL, 1187 1188 vendor_id INT REFERENCES vendors (id), 1189 1190 UNIQUE(name) 1191) 1192EOF 1193 1194 $dbh->do(<<"EOF"); 1195CREATE TABLE prices 1196( 1197 id SERIAL NOT NULL PRIMARY KEY, 1198 product_id INT NOT NULL REFERENCES products (id), 1199 region_id CHAR(2) NOT NULL REFERENCES regions (id) DEFAULT 'US', 1200 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 1201 1202 UNIQUE(product_id, region_id) 1203) 1204EOF 1205 1206 $dbh->do(<<"EOF"); 1207CREATE TABLE descriptions 1208( 1209 id SERIAL NOT NULL PRIMARY KEY, 1210 text VARCHAR(255) NOT NULL, 1211 1212 UNIQUE(text) 1213) 1214EOF 1215 1216 $dbh->do(<<"EOF"); 1217CREATE TABLE authors 1218( 1219 id SERIAL NOT NULL PRIMARY KEY, 1220 name VARCHAR(255) NOT NULL, 1221 1222 UNIQUE(name) 1223) 1224EOF 1225 1226 $dbh->do(<<"EOF"); 1227CREATE TABLE nicknames 1228( 1229 id SERIAL NOT NULL PRIMARY KEY, 1230 nick VARCHAR(255) NOT NULL, 1231 author_id INT REFERENCES authors (id), 1232 1233 UNIQUE(nick, author_id) 1234) 1235EOF 1236 1237 $dbh->do(<<"EOF"); 1238CREATE TABLE description_author_map 1239( 1240 description_id INT NOT NULL REFERENCES descriptions (id), 1241 author_id INT NOT NULL REFERENCES authors (id), 1242 1243 PRIMARY KEY(description_id, author_id) 1244) 1245EOF 1246 1247 $dbh->do(<<"EOF"); 1248CREATE TABLE colors 1249( 1250 id SERIAL NOT NULL PRIMARY KEY, 1251 name VARCHAR(255) NOT NULL, 1252 description_id INT REFERENCES descriptions (id), 1253 1254 UNIQUE(name) 1255) 1256EOF 1257 1258 $dbh->do(<<"EOF"); 1259CREATE TABLE product_color_map 1260( 1261 product_id INT NOT NULL REFERENCES products (id), 1262 color_id INT NOT NULL REFERENCES colors (id), 1263 1264 PRIMARY KEY(product_id, color_id) 1265) 1266EOF 1267 1268 $dbh->do(<<"EOF"); 1269CREATE TABLE Rose_db_object_private.regions 1270( 1271 id CHAR(2) NOT NULL PRIMARY KEY, 1272 name VARCHAR(32) NOT NULL, 1273 1274 UNIQUE(name) 1275) 1276EOF 1277 1278 $dbh->do(<<"EOF"); 1279CREATE TABLE Rose_db_object_private.vendors 1280( 1281 id SERIAL NOT NULL PRIMARY KEY, 1282 name VARCHAR(255) NOT NULL, 1283 1284 vendor_id INT REFERENCES Rose_db_object_private.vendors (id), 1285 region_id CHAR(2) REFERENCES Rose_db_object_private.regions (id), 1286 1287 UNIQUE(name) 1288) 1289EOF 1290 1291 $dbh->do(<<"EOF"); 1292CREATE TABLE Rose_db_object_private.products 1293( 1294 id SERIAL NOT NULL PRIMARY KEY, 1295 name VARCHAR(255) NOT NULL, 1296 1297 vendor_id INT REFERENCES Rose_db_object_private.vendors (id), 1298 1299 UNIQUE(name) 1300) 1301EOF 1302 1303 $dbh->do(<<"EOF"); 1304CREATE TABLE Rose_db_object_private.prices 1305( 1306 id SERIAL NOT NULL PRIMARY KEY, 1307 product_id INT NOT NULL REFERENCES Rose_db_object_private.products (id), 1308 region_id CHAR(2) NOT NULL REFERENCES Rose_db_object_private.regions (id) DEFAULT 'US', 1309 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 1310 1311 UNIQUE(product_id, region_id) 1312) 1313EOF 1314 1315 $dbh->do(<<"EOF"); 1316CREATE TABLE Rose_db_object_private.descriptions 1317( 1318 id SERIAL NOT NULL PRIMARY KEY, 1319 text VARCHAR(255) NOT NULL, 1320 1321 UNIQUE(text) 1322) 1323EOF 1324 1325 $dbh->do(<<"EOF"); 1326CREATE TABLE Rose_db_object_private.authors 1327( 1328 id SERIAL NOT NULL PRIMARY KEY, 1329 name VARCHAR(255) NOT NULL, 1330 1331 UNIQUE(name) 1332) 1333EOF 1334 1335 $dbh->do(<<"EOF"); 1336CREATE TABLE Rose_db_object_private.nicknames 1337( 1338 id SERIAL NOT NULL PRIMARY KEY, 1339 nick VARCHAR(255) NOT NULL, 1340 author_id INT REFERENCES Rose_db_object_private.authors (id), 1341 1342 UNIQUE(nick, author_id) 1343) 1344EOF 1345 1346 $dbh->do(<<"EOF"); 1347CREATE TABLE Rose_db_object_private.description_author_map 1348( 1349 description_id INT NOT NULL REFERENCES Rose_db_object_private.descriptions (id), 1350 author_id INT NOT NULL REFERENCES Rose_db_object_private.authors (id), 1351 1352 PRIMARY KEY(description_id, author_id) 1353) 1354EOF 1355 1356 $dbh->do(<<"EOF"); 1357CREATE TABLE Rose_db_object_private.colors 1358( 1359 id SERIAL NOT NULL PRIMARY KEY, 1360 name VARCHAR(255) NOT NULL, 1361 description_id INT REFERENCES Rose_db_object_private.descriptions (id), 1362 1363 UNIQUE(name) 1364) 1365EOF 1366 1367 $dbh->do(<<"EOF"); 1368CREATE TABLE Rose_db_object_private.product_color_map 1369( 1370 product_id INT NOT NULL REFERENCES Rose_db_object_private.products (id), 1371 color_id INT NOT NULL REFERENCES Rose_db_object_private.colors (id), 1372 1373 PRIMARY KEY(product_id, color_id) 1374) 1375EOF 1376 1377 $dbh->disconnect; 1378 } 1379 1380 # 1381 # MySQL 1382 # 1383 1384 eval 1385 { 1386 my $db = Rose::DB->new('mysql_admin'); 1387 $dbh = $db->retain_dbh or die Rose::DB->error; 1388 1389 die "MySQL version too old" unless($db->database_version >= 4_000_000); 1390 1391 # Drop existing tables, ignoring errors 1392 { 1393 local $dbh->{'RaiseError'} = 0; 1394 local $dbh->{'PrintError'} = 0; 1395 $dbh->do('DROP TABLE product_color_map CASCADE'); 1396 $dbh->do('DROP TABLE colors CASCADE'); 1397 $dbh->do('DROP TABLE descriptions CASCADE'); 1398 $dbh->do('DROP TABLE authors CASCADE'); 1399 $dbh->do('DROP TABLE nicknames CASCADE'); 1400 $dbh->do('DROP TABLE description_author_map CASCADE'); 1401 $dbh->do('DROP TABLE prices CASCADE'); 1402 $dbh->do('DROP TABLE products CASCADE'); 1403 $dbh->do('DROP TABLE vendors CASCADE'); 1404 $dbh->do('DROP TABLE regions CASCADE'); 1405 } 1406 1407 $dbh->do(<<"EOF"); 1408CREATE TABLE regions 1409( 1410 id CHAR(2) NOT NULL PRIMARY KEY, 1411 name VARCHAR(32) NOT NULL, 1412 1413 UNIQUE(name) 1414) 1415ENGINE=InnoDB 1416EOF 1417 1418 # MySQL will silently ignore the "ENGINE=InnoDB" part and create 1419 # a MyISAM table instead. MySQL is evil! Now we have to manually 1420 # check to make sure an InnoDB table was really created. 1421 my $db_name = $db->database; 1422 my $sth = $dbh->prepare("SHOW TABLE STATUS FROM `$db_name` LIKE ?"); 1423 $sth->execute('regions'); 1424 my $info = $sth->fetchrow_hashref; 1425 1426 no warnings 'uninitialized'; 1427 unless(lc $info->{'Type'} eq 'innodb' || lc $info->{'Engine'} eq 'innodb') 1428 { 1429 die "Missing InnoDB support"; 1430 } 1431 }; 1432 1433 if(!$@ && $dbh) 1434 { 1435 $Have{'mysql'} = 1; 1436 1437 $dbh->do(<<"EOF"); 1438CREATE TABLE vendors 1439( 1440 id INT AUTO_INCREMENT PRIMARY KEY, 1441 name VARCHAR(255) NOT NULL, 1442 1443 vendor_id INT, 1444 region_id CHAR(2), 1445 1446 INDEX(vendor_id), 1447 INDEX(region_id), 1448 1449 FOREIGN KEY (vendor_id) REFERENCES vendors (id), 1450 FOREIGN KEY (region_id) REFERENCES regions (id), 1451 1452 UNIQUE(name) 1453) 1454ENGINE=InnoDB 1455EOF 1456 1457 $dbh->do(<<"EOF"); 1458CREATE TABLE products 1459( 1460 id INT AUTO_INCREMENT PRIMARY KEY, 1461 name VARCHAR(255) NOT NULL, 1462 1463 vendor_id INT, 1464 1465 INDEX(vendor_id), 1466 1467 FOREIGN KEY (vendor_id) REFERENCES vendors (id), 1468 1469 UNIQUE(name) 1470) 1471ENGINE=InnoDB 1472EOF 1473 1474 $dbh->do(<<"EOF"); 1475CREATE TABLE prices 1476( 1477 id INT AUTO_INCREMENT PRIMARY KEY, 1478 product_id INT, 1479 region_id CHAR(2) NOT NULL DEFAULT 'US', 1480 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 1481 1482 INDEX(product_id), 1483 INDEX(region_id), 1484 1485 FOREIGN KEY (product_id) REFERENCES products (id), 1486 FOREIGN KEY (region_id) REFERENCES regions (id), 1487 1488 UNIQUE(product_id, region_id) 1489) 1490ENGINE=InnoDB 1491EOF 1492 1493 $dbh->do(<<"EOF"); 1494CREATE TABLE descriptions 1495( 1496 id INT AUTO_INCREMENT PRIMARY KEY, 1497 text VARCHAR(255) NOT NULL, 1498 1499 UNIQUE(text) 1500) 1501ENGINE=InnoDB 1502EOF 1503 1504 $dbh->do(<<"EOF"); 1505CREATE TABLE authors 1506( 1507 id INT AUTO_INCREMENT PRIMARY KEY, 1508 name VARCHAR(255) NOT NULL, 1509 1510 UNIQUE(name) 1511) 1512ENGINE=InnoDB 1513EOF 1514 1515 $dbh->do(<<"EOF"); 1516CREATE TABLE nicknames 1517( 1518 id INT AUTO_INCREMENT PRIMARY KEY, 1519 nick VARCHAR(255) NOT NULL, 1520 author_id INT, 1521 1522 INDEX(author_id), 1523 1524 FOREIGN KEY (author_id) REFERENCES authors (id), 1525 1526 UNIQUE(nick, author_id) 1527) 1528ENGINE=InnoDB 1529EOF 1530 1531 $dbh->do(<<"EOF"); 1532CREATE TABLE description_author_map 1533( 1534 description_id INT NOT NULL, 1535 author_id INT NOT NULL, 1536 1537 INDEX(description_id), 1538 INDEX(author_id), 1539 1540 FOREIGN KEY (description_id) REFERENCES descriptions (id), 1541 FOREIGN KEY (author_id) REFERENCES authors (id), 1542 1543 PRIMARY KEY(description_id, author_id) 1544) 1545ENGINE=InnoDB 1546EOF 1547 1548 $dbh->do(<<"EOF"); 1549CREATE TABLE colors 1550( 1551 id INT AUTO_INCREMENT PRIMARY KEY, 1552 name VARCHAR(255) NOT NULL, 1553 description_id INT, 1554 1555 INDEX(description_id), 1556 1557 FOREIGN KEY (description_id) REFERENCES descriptions (id), 1558 1559 UNIQUE(name) 1560) 1561ENGINE=InnoDB 1562EOF 1563 1564 $dbh->do(<<"EOF"); 1565CREATE TABLE product_color_map 1566( 1567 product_id INT NOT NULL, 1568 color_id INT NOT NULL, 1569 1570 INDEX(product_id), 1571 INDEX(color_id), 1572 1573 FOREIGN KEY (product_id) REFERENCES products (id), 1574 FOREIGN KEY (color_id) REFERENCES colors (id), 1575 1576 PRIMARY KEY(product_id, color_id) 1577) 1578ENGINE=InnoDB 1579EOF 1580 1581 $dbh->disconnect; 1582 } 1583 1584 # 1585 # Informix 1586 # 1587 1588 eval 1589 { 1590 $dbh = Rose::DB->new('informix_admin')->retain_dbh() 1591 or die Rose::DB->error; 1592 }; 1593 1594 if(!$@ && $dbh) 1595 { 1596 $Have{'informix'} = 1; 1597 1598 # Drop existing tables and create schema, ignoring errors 1599 { 1600 local $dbh->{'RaiseError'} = 0; 1601 local $dbh->{'PrintError'} = 0; 1602 $dbh->do('DROP TABLE product_color_map CASCADE'); 1603 $dbh->do('DROP TABLE colors CASCADE'); 1604 $dbh->do('DROP TABLE description_author_map CASCADE'); 1605 $dbh->do('DROP TABLE nicknames CASCADE'); 1606 $dbh->do('DROP TABLE authors CASCADE'); 1607 $dbh->do('DROP TABLE descriptions CASCADE'); 1608 $dbh->do('DROP TABLE prices CASCADE'); 1609 $dbh->do('DROP TABLE products CASCADE'); 1610 $dbh->do('DROP TABLE vendors CASCADE'); 1611 $dbh->do('DROP TABLE regions CASCADE'); 1612 } 1613 1614 $dbh->do(<<"EOF"); 1615CREATE TABLE regions 1616( 1617 id CHAR(2) NOT NULL PRIMARY KEY, 1618 name VARCHAR(32) NOT NULL, 1619 1620 UNIQUE(name) 1621) 1622EOF 1623 1624 $dbh->do(<<"EOF"); 1625CREATE TABLE vendors 1626( 1627 id SERIAL NOT NULL PRIMARY KEY, 1628 name VARCHAR(255) NOT NULL, 1629 1630 vendor_id INT REFERENCES vendors (id), 1631 region_id CHAR(2) REFERENCES regions (id), 1632 1633 UNIQUE(name) 1634) 1635EOF 1636 1637 $dbh->do(<<"EOF"); 1638CREATE TABLE products 1639( 1640 id SERIAL NOT NULL PRIMARY KEY, 1641 name VARCHAR(255) NOT NULL, 1642 1643 vendor_id INT REFERENCES vendors (id), 1644 1645 UNIQUE(name) 1646) 1647EOF 1648 1649 $dbh->do(<<"EOF"); 1650CREATE TABLE prices 1651( 1652 id SERIAL NOT NULL PRIMARY KEY, 1653 product_id INT NOT NULL REFERENCES products (id), 1654 region_id CHAR(2) DEFAULT 'US' NOT NULL REFERENCES regions (id), 1655 price DECIMAL(10,2) DEFAULT 0.00 NOT NULL, 1656 1657 UNIQUE(product_id, region_id) 1658) 1659EOF 1660 1661 $dbh->do(<<"EOF"); 1662CREATE TABLE descriptions 1663( 1664 id SERIAL NOT NULL PRIMARY KEY, 1665 text VARCHAR(255) NOT NULL, 1666 1667 UNIQUE(text) 1668) 1669EOF 1670 1671 $dbh->do(<<"EOF"); 1672CREATE TABLE authors 1673( 1674 id SERIAL NOT NULL PRIMARY KEY, 1675 name VARCHAR(255) NOT NULL, 1676 1677 UNIQUE(name) 1678) 1679EOF 1680 1681 $dbh->do(<<"EOF"); 1682CREATE TABLE nicknames 1683( 1684 id SERIAL NOT NULL PRIMARY KEY, 1685 nick VARCHAR(255) NOT NULL, 1686 author_id INT REFERENCES authors (id), 1687 1688 UNIQUE(nick, author_id) 1689) 1690EOF 1691 1692 $dbh->do(<<"EOF"); 1693CREATE TABLE description_author_map 1694( 1695 description_id INT NOT NULL REFERENCES descriptions (id), 1696 author_id INT NOT NULL REFERENCES authors (id), 1697 1698 PRIMARY KEY(description_id, author_id) 1699) 1700EOF 1701 1702 $dbh->do(<<"EOF"); 1703CREATE TABLE colors 1704( 1705 id SERIAL NOT NULL PRIMARY KEY, 1706 name VARCHAR(255) NOT NULL, 1707 description_id INT REFERENCES descriptions (id), 1708 1709 UNIQUE(name) 1710) 1711EOF 1712 1713 $dbh->do(<<"EOF"); 1714CREATE TABLE product_color_map 1715( 1716 product_id INT NOT NULL REFERENCES products (id), 1717 color_id INT NOT NULL REFERENCES colors (id), 1718 1719 PRIMARY KEY(product_id, color_id) 1720) 1721EOF 1722 1723 $dbh->disconnect; 1724 } 1725 1726 # 1727 # SQLite 1728 # 1729 1730 eval 1731 { 1732 $dbh = Rose::DB->new('sqlite_admin')->retain_dbh() 1733 or die Rose::DB->error; 1734 }; 1735 1736 if(!$@ && $dbh) 1737 { 1738 $Have{'sqlite'} = 1; 1739 1740 # Drop existing tables and create schema, ignoring errors 1741 { 1742 local $dbh->{'RaiseError'} = 0; 1743 local $dbh->{'PrintError'} = 0; 1744 1745 $dbh->do('DROP TABLE colors'); 1746 $dbh->do('DROP TABLE descriptions'); 1747 $dbh->do('DROP TABLE authors'); 1748 $dbh->do('DROP TABLE nicknames'); 1749 $dbh->do('DROP TABLE description_author_map'); 1750 $dbh->do('DROP TABLE product_color_map'); 1751 $dbh->do('DROP TABLE prices'); 1752 $dbh->do('DROP TABLE products'); 1753 $dbh->do('DROP TABLE vendors'); 1754 $dbh->do('DROP TABLE regions'); 1755 } 1756 1757 $dbh->do(<<"EOF"); 1758CREATE TABLE regions 1759( 1760 id CHAR(2) NOT NULL PRIMARY KEY, 1761 name VARCHAR(32) NOT NULL, 1762 1763 UNIQUE(name) 1764) 1765EOF 1766 1767 $dbh->do(<<"EOF"); 1768CREATE TABLE vendors 1769( 1770 id INTEGER PRIMARY KEY AUTOINCREMENT, 1771 name VARCHAR(255) NOT NULL, 1772 1773 vendor_id INT REFERENCES vendors (id), 1774 region_id CHAR(2) REFERENCES regions (id), 1775 1776 UNIQUE(name) 1777) 1778EOF 1779 1780 $dbh->do(<<"EOF"); 1781CREATE TABLE products 1782( 1783 id INTEGER PRIMARY KEY AUTOINCREMENT, 1784 name VARCHAR(255) NOT NULL, 1785 1786 vendor_id INT REFERENCES vendors (id), 1787 1788 UNIQUE(name) 1789) 1790EOF 1791 1792 $dbh->do(<<"EOF"); 1793CREATE TABLE prices 1794( 1795 id INTEGER PRIMARY KEY AUTOINCREMENT, 1796 product_id INT NOT NULL REFERENCES products (id), 1797 region_id CHAR(2) NOT NULL REFERENCES regions (id) DEFAULT 'US', 1798 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 1799 1800 UNIQUE(product_id, region_id) 1801) 1802EOF 1803 1804 $dbh->do(<<"EOF"); 1805CREATE TABLE descriptions 1806( 1807 id INTEGER PRIMARY KEY AUTOINCREMENT, 1808 text VARCHAR(255) NOT NULL, 1809 1810 UNIQUE(text) 1811) 1812EOF 1813 1814 $dbh->do(<<"EOF"); 1815CREATE TABLE authors 1816( 1817 id INTEGER PRIMARY KEY AUTOINCREMENT, 1818 name VARCHAR(255) NOT NULL, 1819 1820 UNIQUE(name) 1821) 1822EOF 1823 1824 $dbh->do(<<"EOF"); 1825CREATE TABLE nicknames 1826( 1827 id INTEGER PRIMARY KEY AUTOINCREMENT, 1828 nick VARCHAR(255) NOT NULL, 1829 author_id INT REFERENCES authors (id), 1830 1831 UNIQUE(nick, author_id) 1832) 1833EOF 1834 1835 $dbh->do(<<"EOF"); 1836CREATE TABLE description_author_map 1837( 1838 description_id INT NOT NULL REFERENCES descriptions (id), 1839 author_id INT NOT NULL REFERENCES authors (id), 1840 1841 PRIMARY KEY(description_id, author_id) 1842) 1843EOF 1844 1845 $dbh->do(<<"EOF"); 1846CREATE TABLE colors 1847( 1848 id INTEGER PRIMARY KEY AUTOINCREMENT, 1849 name VARCHAR(255) NOT NULL, 1850 description_id INT REFERENCES descriptions (id), 1851 1852 UNIQUE(name) 1853) 1854EOF 1855 1856 $dbh->do(<<"EOF"); 1857CREATE TABLE product_color_map 1858( 1859 product_id INT NOT NULL REFERENCES products (id), 1860 color_id INT NOT NULL REFERENCES colors (id), 1861 1862 PRIMARY KEY(product_id, color_id) 1863) 1864EOF 1865 1866 $dbh->disconnect; 1867 } 1868} 1869 1870END 1871{ 1872 if($Have{'pg'}) 1873 { 1874 my $dbh = Rose::DB->new('pg_admin')->retain_dbh() 1875 or die Rose::DB->error; 1876 1877 $dbh->do('DROP TABLE product_color_map CASCADE'); 1878 $dbh->do('DROP TABLE colors CASCADE'); 1879 $dbh->do('DROP TABLE description_author_map CASCADE'); 1880 $dbh->do('DROP TABLE nicknames CASCADE'); 1881 $dbh->do('DROP TABLE authors CASCADE'); 1882 $dbh->do('DROP TABLE descriptions CASCADE'); 1883 $dbh->do('DROP TABLE prices CASCADE'); 1884 $dbh->do('DROP TABLE products CASCADE'); 1885 $dbh->do('DROP TABLE vendors CASCADE'); 1886 $dbh->do('DROP TABLE regions CASCADE'); 1887 1888 $dbh->do('DROP TABLE Rose_db_object_private.product_color_map CASCADE'); 1889 $dbh->do('DROP TABLE Rose_db_object_private.colors CASCADE'); 1890 $dbh->do('DROP TABLE Rose_db_object_private.description_author_map CASCADE'); 1891 $dbh->do('DROP TABLE Rose_db_object_private.nicknames CASCADE'); 1892 $dbh->do('DROP TABLE Rose_db_object_private.authors CASCADE'); 1893 $dbh->do('DROP TABLE Rose_db_object_private.descriptions CASCADE'); 1894 $dbh->do('DROP TABLE Rose_db_object_private.prices CASCADE'); 1895 $dbh->do('DROP TABLE Rose_db_object_private.products CASCADE'); 1896 $dbh->do('DROP TABLE Rose_db_object_private.vendors CASCADE'); 1897 $dbh->do('DROP TABLE Rose_db_object_private.regions CASCADE'); 1898 1899 $dbh->do('DROP SCHEMA Rose_db_object_private CASCADE'); 1900 1901 $dbh->disconnect; 1902 } 1903 1904 if($Have{'mysql'}) 1905 { 1906 my $dbh = Rose::DB->new('mysql_admin')->retain_dbh() 1907 or die Rose::DB->error; 1908 1909 $dbh->do('DROP TABLE product_color_map CASCADE'); 1910 $dbh->do('DROP TABLE colors CASCADE'); 1911 $dbh->do('DROP TABLE description_author_map CASCADE'); 1912 $dbh->do('DROP TABLE nicknames CASCADE'); 1913 $dbh->do('DROP TABLE authors CASCADE'); 1914 $dbh->do('DROP TABLE descriptions CASCADE'); 1915 $dbh->do('DROP TABLE prices CASCADE'); 1916 $dbh->do('DROP TABLE products CASCADE'); 1917 $dbh->do('DROP TABLE vendors CASCADE'); 1918 $dbh->do('DROP TABLE regions CASCADE'); 1919 1920 $dbh->disconnect; 1921 } 1922 1923 if($Have{'informix'}) 1924 { 1925 my $dbh = Rose::DB->new('informix_admin')->retain_dbh() 1926 or die Rose::DB->error; 1927 1928 $dbh->do('DROP TABLE product_color_map CASCADE'); 1929 $dbh->do('DROP TABLE colors CASCADE'); 1930 $dbh->do('DROP TABLE description_author_map CASCADE'); 1931 $dbh->do('DROP TABLE nicknames CASCADE'); 1932 $dbh->do('DROP TABLE authors CASCADE'); 1933 $dbh->do('DROP TABLE descriptions CASCADE'); 1934 $dbh->do('DROP TABLE prices CASCADE'); 1935 $dbh->do('DROP TABLE products CASCADE'); 1936 $dbh->do('DROP TABLE vendors CASCADE'); 1937 $dbh->do('DROP TABLE regions CASCADE'); 1938 1939 $dbh->disconnect; 1940 } 1941 1942 if($Have{'sqlite'}) 1943 { 1944 my $dbh = Rose::DB->new('sqlite_admin')->retain_dbh() 1945 or die Rose::DB->error; 1946 1947 $dbh->do('DROP TABLE colors'); 1948 $dbh->do('DROP TABLE descriptions'); 1949 $dbh->do('DROP TABLE authors'); 1950 $dbh->do('DROP TABLE nicknames'); 1951 $dbh->do('DROP TABLE description_author_map'); 1952 $dbh->do('DROP TABLE product_color_map'); 1953 $dbh->do('DROP TABLE prices'); 1954 $dbh->do('DROP TABLE products'); 1955 $dbh->do('DROP TABLE vendors'); 1956 $dbh->do('DROP TABLE regions'); 1957 1958 $dbh->disconnect; 1959 } 1960} 1961 1962sub has_broken_order_by 1963{ 1964 my($db_type) = shift; 1965 1966 no warnings 'uninitialized'; 1967 (my $version = $DBD::SQLite::VERSION) =~ s/_//g; 1968 1969 if($db_type eq 'sqlite' && $version < 1.11) 1970 { 1971 return 1; 1972 } 1973 1974 return 0; 1975} 1976 1977sub cmp_sql 1978{ 1979 my($a, $b, $msg) = @_; 1980 1981 for($a, $b) 1982 { 1983 s/\s+/ /g; 1984 s/^\s+//; 1985 s/\s+$//; 1986 s/^SELECT.*?FROM/SELECT * FROM/; 1987 s/\brose_db_object_private\.//g; 1988 } 1989 1990 @_ = ($a, $b, $msg); 1991 1992 goto &is; 1993} 1994