1#!/usr/bin/perl -w 2 3use strict; 4 5use File::Spec; 6 7use lib '.', File::Spec->catdir( File::Spec->curdir, 't', 'lib' ); 8 9use Alzabo::Test::Utils; 10 11use Test::More; 12 13 14my @rdbms_names = Alzabo::Test::Utils->rdbms_names; 15 16unless (@rdbms_names) 17{ 18 plan skip_all => 'no test config provided'; 19 exit; 20} 21 22my $tests_per_run = 340; 23my $test_count = $tests_per_run * @rdbms_names; 24 25my %SINGLE_RDBMS_TESTS = ( mysql => 23, 26 pg => 11, 27 ); 28 29foreach my $rdbms ( keys %SINGLE_RDBMS_TESTS ) 30{ 31 next unless grep { $_ eq $rdbms } @rdbms_names; 32 33 $test_count += $SINGLE_RDBMS_TESTS{$rdbms}; 34} 35 36plan tests => $test_count; 37 38 39Alzabo::Test::Utils->remove_all_schemas; 40 41 42foreach my $rdbms (@rdbms_names) 43{ 44 if ( $rdbms eq 'mysql' ) 45 { 46 # prevent subroutine redefinition warnings 47 local $^W = 0; 48 eval 'use Alzabo::SQLMaker::MySQL qw(:all)'; 49 } 50 elsif ( $rdbms eq 'pg' ) 51 { 52 local $^W = 0; 53 eval 'use Alzabo::SQLMaker::PostgreSQL qw(:all)'; 54 } 55 56 Alzabo::Test::Utils->make_schema($rdbms); 57 58 run_tests($rdbms); 59 60 Alzabo::Test::Utils->remove_schema($rdbms); 61} 62 63sub run_tests 64{ 65 my $rdbms = shift; 66 67 my $config = Alzabo::Test::Utils->test_config_for($rdbms); 68 69 my $s = Alzabo::Runtime::Schema->load_from_file( name => $config->{schema_name} ); 70 71 # tests setting basic parameters and connecting to RDBMS 72 { 73 eval_ok( sub { $s->set_user('foo') }, 74 "Set user for schema to foo" ); 75 76 eval_ok( sub { $s->set_password('foo') }, 77 "Set password for schema to foo" ); 78 79 eval_ok( sub { $s->set_host('foo') }, 80 "Set host for schema to foo" ); 81 82 eval_ok( sub { $s->set_port(1234) }, 83 "Set port for schema to 1234" ); 84 85 $s->$_(undef) foreach qw( set_user set_password set_host set_port ); 86 87 $s->connect( Alzabo::Test::Utils->connect_params_for($rdbms) ); 88 89 $s->set_referential_integrity(1); 90 } 91 92 { 93 my $dbh = $s->driver->handle; 94 isa_ok( $dbh, ref $s->driver->{dbh}, 95 "Object returned by \$s->driver->handle method" ); 96 97 eval_ok( sub { $s->driver->handle($dbh) }, 98 "Set \$s->driver->handle" ); 99 } 100 101 my $emp_t = $s->table('employee'); 102 my $dep_t = $s->table('department'); 103 my $proj_t = $s->table('project'); 104 my $emp_proj_t = $s->table('employee_project'); 105 106 my %dep; 107 eval_ok( sub { $dep{borg} = $dep_t->insert( values => { name => 'borging' } ) }, 108 "Insert borging row into department table" ); 109 110 is( $dep{borg}->select('name'), 'borging', 111 "The borg department name should be 'borging'" ); 112 113 { 114 my @all = $dep{borg}->select; 115 is( @all, 3, 116 "select with no columns should return all the values" ); 117 is( $all[1], 'borging', 118 "The second value should be the department name" ); 119 120 my %all = $dep{borg}->select_hash; 121 is( keys %all, 3, 122 "select_hash with no columns should return two keys" ); 123 ok( exists $all{department_id}, 124 "The returned hash should have a department_id key" ); 125 ok( exists $all{name}, 126 "The returned hash should have a department_id key" ); 127 is( $all{name}, 'borging', 128 "The value of the name key be the department name" ); 129 } 130 131 132 $dep{lying} = $dep_t->insert( values => { name => 'lying to the public' } ); 133 134 my $borg_id = $dep{borg}->select('department_id'); 135 delete $dep{borg}; 136 137 eval_ok( sub { $dep{borg} = $dep_t->row_by_pk( pk => $borg_id ) }, 138 "Retrieve borg department row via row_by_pk method" ); 139 140 isa_ok( $dep{borg}, 'Alzabo::Runtime::Row', 141 "Borg department" ); 142 143 is( $dep{borg}->select('name'), 'borging', 144 "Department's name should be 'borging'" ); 145 146 eval { $dep_t->insert( values => { name => 'will break', 147 manager_id => 1 } ); }; 148 149 my $e = $@; 150 isa_ok( $e, 'Alzabo::Exception::ReferentialIntegrity', 151 "Exception thrown from attempt to insert a non-existent manager_id into department" ); 152 153 my %emp; 154 eval_ok( sub { $emp{bill} = $emp_t->insert( values => { name => 'Big Bill', 155 dep_id => $borg_id, 156 smell => 'robotic', 157 cash => 20.2, 158 } ) }, 159 "Insert Big Bill into employee table" ); 160 161 my %data = $emp{bill}->select_hash( 'name', 'smell' ); 162 is( $data{name}, 'Big Bill', 163 "select_hash - check name key" ); 164 is( $data{smell}, 'robotic', 165 "select_hash - check smell key" ); 166 167 is( $emp{bill}->is_live, 1, 168 "->is_live should be true for real row" ); 169 170 eval { $emp_t->insert( values => { name => undef, 171 dep_id => $borg_id, 172 smell => 'robotic', 173 cash => 20.2, 174 } ); }; 175 176 $e = $@; 177 isa_ok( $e, 'Alzabo::Exception::NotNullable', 178 "Exception thrown from inserting a non-nullable column as NULL" ); 179 180 is( $e->table_name, 'employee', 181 "NotNullable exceptions contain table name" ); 182 183 is( $e->schema_name, $config->{schema_name}, 184 "NotNullable exceptions contain schema name" ); 185 186 { 187 my $new_emp; 188 eval_ok( sub { $new_emp = $emp_t->insert( values => { name => 'asfalksf', 189 dep_id => $borg_id, 190 smell => undef, 191 cash => 20.2, 192 } ) }, 193 "Inserting a NULL into a non-nullable column that has a default should not produce an exception" ); 194 195 eval_ok( sub { $new_emp->delete }, 196 "Delete a just-created employee" ); 197 } 198 199 eval { $emp_t->insert( values => { name => 'YetAnotherTest', 200 dep_id => undef, 201 cash => 1.1, 202 } ) }; 203 204 $e = $@; 205 isa_ok( $e, 'Alzabo::Exception::Params', 206 "Exception thrown from attempt to insert a NULL into dep_id for an employee" ); 207 208 eval { $emp{bill}->update( dep_id => undef ) }; 209 $e = $@; 210 isa_ok( $e, 'Alzabo::Exception::Params', 211 "Exception thrown from attempt to update dep_id to NULL for an employee" ); 212 213 { 214 my $updated = $emp{bill}->update( cash => undef, smell => 'hello!' ); 215 216 ok( $updated, 'update() did change values' ); 217 ok( ! defined $emp{bill}->select('cash'), 218 "Bill has no cash" ); 219 } 220 221 { 222 my $updated = $emp{bill}->update( cash => undef, smell => 'hello!' ); 223 224 ok( ! $updated, 'update() did not change values' ); 225 } 226 227 ok( $emp{bill}->select('smell') eq 'hello!', 228 "smell for bill should be 'hello!'" ); 229 230 eval { $emp{bill}->update( name => undef ) }; 231 $e = $@; 232 isa_ok( $e, 'Alzabo::Exception::NotNullable', 233 "Exception thrown from attempt to update a non-nullable column to NULL" ); 234 235 eval_ok( sub { $dep{borg}->update( manager_id => $emp{bill}->select('employee_id') ) }, 236 "Set manager_id column for borg department" ); 237 238 eval_ok( sub { $emp{2} = $emp_t->insert( values => 239 { name => 'unit 2', 240 smell => 'good', 241 dep_id => $dep{lying}->select('department_id') } ) }, 242 "Create employee 'unit 2'" ); 243 244 my $emp2_id = $emp{2}->select('employee_id'); 245 delete $emp{2}; 246 247 my $cursor; 248 my $x = 0; 249 eval_ok( sub { $cursor = 250 $emp_t->rows_where 251 ( where => [ $emp_t->column('employee_id'), '=', $emp2_id ] ); 252 253 while ( my $row = $cursor->next ) 254 { 255 $x++; 256 $emp{2} = $row; 257 } 258 }, 259 "Retrieve 'unit 2' employee via rows_where method and cursor" ); 260 261 is( $x, 1, 262 "Check count of rows found where employee_id == $emp2_id" ); 263 is( $cursor->count, 1, 264 "Make sure cursor's count() is accurate" ); 265 266 is( $emp{2}->select('name'), 'unit 2', 267 "Check that row found has name of 'unit 2'" ); 268 269 { 270 my $row; 271 eval_ok( sub { $row = 272 $emp_t->one_row 273 ( where => 274 [ $emp_t->column('employee_id'), '=', $emp2_id ] ) }, 275 "Retrieve 'unit 2' employee via one_row method" ); 276 277 is( $row->select('name'), 'unit 2', 278 "Check that the single row returned has the name 'unit 2'" ); 279 } 280 281 { 282 my $row; 283 eval_ok( sub { $row = 284 $emp_t->one_row 285 ( where => 286 [ $emp_t->column('employee_id'), '=', $emp2_id ], 287 quote_identifiers => 1, 288 ) }, 289 "Retrieve 'unit 2' employee via one_row method with quote_identifiers" ); 290 291 is( $row->select('name'), 'unit 2', 292 "Check that the single row returned has the name 'unit 2'" ); 293 } 294 295 my %proj; 296 $proj{extend} = $proj_t->insert( values => { name => 'Extend', 297 department_id => $dep{borg}->select('department_id') } ); 298 $proj{embrace} = $proj_t->insert( values => { name => 'Embrace', 299 department_id => $dep{borg}->select('department_id') } ); 300 301 $emp_proj_t->insert( values => { employee_id => $emp{bill}->select('employee_id'), 302 project_id => $proj{extend}->select('project_id') } ); 303 304 $emp_proj_t->insert( values => { employee_id => $emp{bill}->select('employee_id'), 305 project_id => $proj{embrace}->select('project_id') } ); 306 307 my $fk = $emp_t->foreign_keys_by_table($emp_proj_t); 308 my @emp_proj; 309 my @cursor_counts; 310 eval_ok( sub { $cursor = $emp{bill}->rows_by_foreign_key( foreign_key => $fk ); 311 while ( my $row = $cursor->next ) 312 { 313 push @emp_proj, $row; 314 push @cursor_counts, $cursor->count; 315 } }, 316 "Fetch rows via ->rows_by_foreign_key method (expect cursor)" ); 317 318 is( scalar @emp_proj, 2, 319 "Check that only two rows were returned" ); 320 is( $emp_proj[0]->select('employee_id'), $emp{bill}->select('employee_id'), 321 "Check that employee_id in employee_project is same as bill's" ); 322 is( $emp_proj[0]->select('project_id'), $proj{extend}->select('project_id'), 323 "Check that project_id in employee_project is same as extend project" ); 324 325 foreach (1..2) 326 { 327 is( $cursor_counts[$_ - 1], $_, 328 "cursor->count should be 1..2" ); 329 } 330 331 my $emp_proj = $emp_proj[0]; 332 $fk = $emp_proj_t->foreign_keys_by_table($emp_t); 333 334 my $emp; 335 eval_ok( sub { $emp = $emp_proj->rows_by_foreign_key( foreign_key => $fk ) }, 336 "Fetch rows via ->rows_by_foreign_key method (expect row)" ); 337 is( $emp->select('employee_id'), $emp_proj->select('employee_id'), 338 "The returned row should have bill's employee_id" ); 339 340 $x = 0; 341 my @rows; 342 343 eval_ok( sub { $cursor = $emp_t->all_rows; 344 $x++ while $cursor->next 345 }, 346 "Fetch all rows from employee table" ); 347 is( $x, 2, 348 "Only 2 rows should be found" ); 349 350 $cursor->reset; 351 my $count = $cursor->all_rows; 352 353 is( $x, 2, 354 "Only 2 rows should be found after cursor reset" ); 355 356 { 357 my $cursor; 358 eval_ok( sub { $cursor = 359 $s->join( join => [ $emp_t, $emp_proj_t, $proj_t ], 360 where => 361 [ $emp_t->column('employee_id'), '=', 362 $emp{bill}->select('employee_id') ], 363 order_by => $proj_t->column('project_id'), 364 quote_identifiers => 1, 365 ) }, 366 "Join employee, employee_project, and project tables where employee_id = bill's employee id with quote_identifiers" ); 367 368 my @rows = $cursor->next; 369 370 is( scalar @rows, 3, 371 "3 rows per cursor ->next call" ); 372 is( $rows[0]->table->name, 'employee', 373 "First row is from employee table" ); 374 is( $rows[1]->table->name, 'employee_project', 375 "Second row is from employee_project table" ); 376 is( $rows[2]->table->name, 'project', 377 "Third row is from project table" ); 378 379 my $first_proj_id = $rows[2]->select('project_id'); 380 @rows = $cursor->next; 381 my $second_proj_id = $rows[2]->select('project_id'); 382 383 ok( $first_proj_id < $second_proj_id, 384 "Order by clause should cause project rows to come back" . 385 " in ascending order of project id" ); 386 } 387 388 { 389 my $cursor; 390 eval_ok( sub { $cursor = 391 $s->join( join => [ $emp_t, $emp_proj_t, $proj_t ], 392 where => 393 [ [ $proj_t->column('project_id'), '=', 394 $proj{extend}->select('project_id') ], 395 'or', 396 [ $proj_t->column('project_id'), '=', 397 $proj{embrace}->select('project_id') ], 398 ], 399 order_by => $proj_t->column('project_id') ) }, 400 "Join employee, employee_project, and project tables with OR in where clause" ); 401 402 1 while $cursor->next; 403 404 is( $cursor->count, 2, 405 "join with OR in where clause should return two sets of rows" ); 406 } 407 408 # Alias code 409 { 410 my $e_alias; 411 eval_ok( sub { $e_alias = $emp_t->alias }, 412 "Create an alias object for the employee table" ); 413 414 my $p_alias; 415 eval_ok( sub { $p_alias = $proj_t->alias }, 416 "Create an alias object for the project table" ); 417 418 eval_ok( sub { $cursor = 419 $s->join( join => [ $e_alias, $emp_proj_t, $p_alias ], 420 where => [ $e_alias->column('employee_id'), '=', 1 ], 421 order_by => $p_alias->column('project_id'), 422 ) }, 423 "Join employee, employee_project, and project tables where" . 424 " employee_id = 1 using aliases" ); 425 426 my @rows = $cursor->next; 427 428 is( scalar @rows, 3, 429 "3 rows per cursor ->next call" ); 430 is( $rows[0]->table->name, 'employee', 431 "First row is from employee table" ); 432 is( $rows[1]->table->name, 'employee_project', 433 "Second row is from employee_project table" ); 434 is( $rows[2]->table->name, 'project', 435 "Third row is from project table" ); 436 } 437 438 # Alias code & multiple joins to the same table 439 { 440 my $p_alias = $proj_t->alias; 441 442 eval_ok( sub { $cursor = $s->join( select => [ $p_alias, $proj_t ], 443 join => [ $p_alias, $emp_proj_t, $proj_t ], 444 where => [ [ $p_alias->column('project_id'), '=', 1 ], 445 [ $proj_t->column('project_id'), '=', 1 ] ], 446 ) }, 447 "Join employee_project and project table (twice) using aliases" ); 448 449 my @rows = $cursor->next; 450 451 is( scalar @rows, 2, 452 "2 rows per cursor ->next call" ); 453 is( $rows[0]->table->name, 'project', 454 "First row is from project table" ); 455 is( $rows[1]->table->name, 'project', 456 "Second row is from project table" ); 457 is( $rows[0]->table, $rows[1]->table, 458 "The two rows should share the same table object (the alias should be gone at this point)" ); 459 } 460 461 { 462 my @rows; 463 eval_ok( sub { @rows = $s->one_row( tables => [ $emp_t, $emp_proj_t, $proj_t ], 464 where => [ $emp_t->column('employee_id'), '=', 1 ], 465 order_by => $proj_t->column('project_id') ) }, 466 "Join employee, employee_project, and project tables where employee_id = 1 using one_row method" ); 467 468 is( $rows[0]->table->name, 'employee', 469 "First row is from employee table" ); 470 is( $rows[1]->table->name, 'employee_project', 471 "Second row is from employee_project table" ); 472 is( $rows[2]->table->name, 'project', 473 "Third row is from project table" ); 474 } 475 476 $cursor = $s->join( join => [ $emp_t, $emp_proj_t, $proj_t ], 477 where => [ $emp_t->column('employee_id'), '=', 1 ], 478 order_by => [ $proj_t->column('project_id'), 'desc' ] ); 479 @rows = $cursor->next; 480 my $first_proj_id = $rows[2]->select('project_id'); 481 @rows = $cursor->next; 482 my $second_proj_id = $rows[2]->select('project_id'); 483 484 ok( $first_proj_id > $second_proj_id, 485 "Order by clause should cause project rows to come back in descending order of project id" ); 486 487 $cursor = $s->join( join => [ $emp_t, $emp_proj_t, $proj_t ], 488 where => [ $emp_t->column('employee_id'), '=', 1 ], 489 order_by => [ $proj_t->column('project_id'), 'desc' ] ); 490 491 @rows = $cursor->next; 492 $first_proj_id = $rows[2]->select('project_id'); 493 @rows = $cursor->next; 494 $second_proj_id = $rows[2]->select('project_id'); 495 496 ok( $first_proj_id > $second_proj_id, 497 "Order by clause (alternate form) should cause project rows to come back in descending order of project id" ); 498 499 eval_ok( sub { $cursor = $s->join( select => [ $emp_t, $emp_proj_t, $proj_t ], 500 join => [ [ $emp_t, $emp_proj_t ], 501 [ $emp_proj_t, $proj_t ] ], 502 where => [ $emp_t->column('employee_id'), '=', 1 ] ) }, 503 "Join with join as arrayref of arrayrefs" ); 504 505 @rows = $cursor->next; 506 507 is( scalar @rows, 3, 508 "3 rows per cursor ->next call" ); 509 is( $rows[0]->table->name, 'employee', 510 "First row is from employee table" ); 511 is( $rows[1]->table->name, 'employee_project', 512 "Second row is from employee_project table" ); 513 is( $rows[2]->table->name, 'project', 514 "Third row is from project table" ); 515 516 { 517 my $cursor; 518 eval_ok( sub { $cursor = $s->join( join => [ [ $emp_t, $emp_proj_t ], 519 [ $emp_proj_t, $proj_t ] ], 520 where => [ $emp_t->column('employee_id'), '=', 1 ] ) }, 521 "Same join with no select parameter" ); 522 523 my @rows = $cursor->next; 524 525 @rows = sort { $a->table->name cmp $b->table->name } @rows; 526 527 is( scalar @rows, 3, 528 "3 rows per cursor ->next call" ); 529 is( ( grep { $_->table->name eq 'employee' } @rows ), 1, 530 "First row is from employee table" ); 531 is( ( grep { $_->table->name eq 'employee_project' } @rows ), 1, 532 "Second row is from employee_project table" ); 533 is( ( grep { $_->table->name eq 'project' } @rows ), 1, 534 "Third row is from project table" ); 535 } 536 537 eval { $s->join( select => [ $emp_t, $emp_proj_t, $proj_t ], 538 join => [ [ $emp_t, $emp_proj_t ], 539 [ $emp_proj_t, $proj_t ], 540 [ $s->tables( 'outer_1', 'outer_2' ) ] ], 541 where => [ $emp_t->column('employee_id'), '=', 1 ] ) }; 542 543 $e = $@; 544 isa_ok( $e, 'Alzabo::Exception::Logic', 545 "Exception thrown from join with table map that does not connect" ); 546 547 eval_ok( sub { @rows = $s->join( join => $emp_t, 548 where => [ $emp_t->column('employee_id'), '=', 1 ] )->all_rows }, 549 "Join with a single table" ); 550 is( @rows, 1, 551 "Only one row should be returned" ); 552 is( $rows[0]->select('employee_id'), 1, 553 "Returned employee should be employee number one" ); 554 555 { 556 557 $s->table('outer_2')->insert( values => { outer_2_name => 'will match something', 558 outer_2_pk => 1 }, 559 ); 560 561 $s->table('outer_2')->insert( values => { outer_2_name => 'will match nothing', 562 outer_2_pk => 99 }, 563 ); 564 565 566 $s->table('outer_1')->insert( values => { outer_1_name => 'test1 (has matching join row)', 567 outer_2_pk => 1 }, 568 ); 569 570 $s->table('outer_1')->insert( values => { outer_1_name => 'test2 (has no matching join row)', 571 outer_2_pk => undef }, 572 ); 573 574 { 575 my $cursor; 576 eval_ok( sub { $cursor = 577 $s->join 578 ( select => [ $s->tables( 'outer_1', 'outer_2' ) ], 579 join => 580 [ left_outer_join => 581 $s->tables( 'outer_1', 'outer_2' ) ] 582 ) }, 583 "Do a left outer join" ); 584 585 my @sets = $cursor->all_rows; 586 587 is( scalar @sets, 2, 588 "Left outer join should return 2 sets of rows" ); 589 590 # re-order so that the set with 2 valid rows is always first 591 unless ( defined $sets[0]->[1] ) 592 { 593 my $set = shift @sets; 594 push @sets, $set; 595 } 596 597 is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)', 598 "The first row in the first set should have the name 'test1 (has matching join row)'" ); 599 600 is( $sets[0]->[1]->select('outer_2_name'), 'will match something', 601 "The second row in the first set should have the name 'will match something'" ); 602 603 is( $sets[1]->[0]->select('outer_1_name'), 'test2 (has no matching join row)', 604 "The first row in the second set should have the name 'test12 (has no matching join row)'" ); 605 606 ok( ! defined $sets[1]->[1], 607 "The second row in the second set should not be defined" ); 608 } 609 610 { 611 my $cursor; 612 eval_ok( sub { $cursor = 613 $s->join 614 ( select => [ $s->tables( 'outer_1', 'outer_2' ) ], 615 join => 616 [ [ left_outer_join => 617 $s->tables( 'outer_1', 'outer_2' ), 618 [ $s->table('outer_2')->column( 'outer_2_pk' ), 619 '!=', 1 ], 620 ] ], 621 order_by => 622 $s->table('outer_1')->column('outer_1_name') 623 ) }, 624 "Do a left outer join" ); 625 626 my @sets = $cursor->all_rows; 627 628 is( scalar @sets, 2, 629 "Left outer join should return 2 sets of rows" ); 630 631 is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)', 632 "The first row in the first set should have the name 'test1 (has matching join row)'" ); 633 634 is( $sets[0]->[1], undef, 635 "The second row in the first set should be undef" ); 636 637 is( $sets[1]->[0]->select('outer_1_name'), 'test2 (has no matching join row)', 638 "The first row in the second set should have the name 'test1 (has matching join row)'" ); 639 640 is( $sets[1]->[1], undef, 641 "The second row in the second set should be undef" ); 642 } 643 644 { 645 my $fk = $s->table('outer_1')->foreign_keys_by_table( $s->table('outer_2') ); 646 my $cursor; 647 eval_ok( sub { $cursor = 648 $s->join 649 ( select => [ $s->tables( 'outer_1', 'outer_2' ) ], 650 join => 651 [ [ left_outer_join => 652 $s->tables( 'outer_1', 'outer_2' ), 653 $fk, 654 [ $s->table('outer_2')->column( 'outer_2_pk' ), 655 '!=', 1 ], 656 ] ], 657 order_by => 658 $s->table('outer_1')->column('outer_1_name') 659 ) }, 660 "Do a left outer join" ); 661 662 my @sets = $cursor->all_rows; 663 664 is( scalar @sets, 2, 665 "Left outer join should return 2 sets of rows" ); 666 667 is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)', 668 "The first row in the first set should have the name 'test1 (has matching join row)'" ); 669 670 is( $sets[0]->[1], undef, 671 "The second row in the first set should be undef" ); 672 673 is( $sets[1]->[0]->select('outer_1_name'), 'test2 (has no matching join row)', 674 "The first row in the second set should have the name 'test1 (has matching join row)'" ); 675 676 is( $sets[1]->[1], undef, 677 "The second row in the second set should be undef" ); 678 } 679 680 { 681 my $cursor; 682 eval_ok( sub { $cursor = 683 $s->join 684 ( select => [ $s->tables( 'outer_1', 'outer_2' ) ], 685 join => 686 [ [ right_outer_join => 687 $s->tables( 'outer_1', 'outer_2' ) ] ] 688 ) }, 689 "Attempt a right outer join" ); 690 691 my @sets = $cursor->all_rows; 692 693 is( scalar @sets, 2, 694 "Right outer join should return 2 sets of rows" ); 695 696 # re-order so that the set with 2 valid rows is always first 697 unless ( defined $sets[0]->[0] ) 698 { 699 my $set = shift @sets; 700 push @sets, $set; 701 } 702 703 is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)', 704 "The first row in the first set should have the name 'test1 (has matching join row)'" ); 705 706 is( $sets[0]->[1]->select('outer_2_name'), 'will match something', 707 "The second row in the first set should have the name 'will match something'" ); 708 709 ok( ! defined $sets[1]->[0], 710 "The first row in the second set should not be defined" ); 711 712 is( $sets[1]->[1]->select('outer_2_name'), 'will match nothing', 713 "The second row in the second set should have the name 'test12 (has no matching join row)'" ); 714 } 715 716 717 { 718 my $cursor; 719 # do the same join, but with specified foreign key 720 my $fk = $s->table('outer_1')->foreign_keys_by_table( $s->table('outer_2') ); 721 eval_ok( sub { $cursor = 722 $s->join 723 ( select => [ $s->tables( 'outer_1', 'outer_2' ) ], 724 join => 725 [ [ right_outer_join => 726 $s->tables( 'outer_1', 'outer_2' ), $fk ] ] 727 ) }, 728 "Attempt a right outer join, with explicit foreign key" ); 729 730 my @sets = $cursor->all_rows; 731 732 is( scalar @sets, 2, 733 "Right outer join should return 2 sets of rows" ); 734 735 # re-order so that the set with 2 valid rows is always first 736 unless ( defined $sets[0]->[0] ) 737 { 738 my $set = shift @sets; 739 push @sets, $set; 740 } 741 742 is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)', 743 "The first row in the first set should have the name 'test1 (has matching join row)'" ); 744 745 is( $sets[0]->[1]->select('outer_2_name'), 'will match something', 746 "The second row in the first set should have the name 'will match something'" ); 747 748 ok( ! defined $sets[1]->[0], 749 "The first row in the second set should not be defined" ); 750 751 is( $sets[1]->[1]->select('outer_2_name'), 'will match nothing', 752 "The second row in the second set should have the name 'test12 (has no matching join row)'" ); 753 } 754 } 755 756 my $id = $emp{bill}->select('employee_id'); 757 758 $emp{bill}->delete; 759 760 eval { $emp{bill}->select('name'); }; 761 $e = $@; 762 763 isa_ok( $e, 'Alzabo::Exception::NoSuchRow', 764 "Exception thrown from attempt to select from deleted row object" ); 765 766 { 767 my $row = 768 $emp_proj_t->row_by_pk 769 ( pk => 770 { employee_id => $id, 771 project_id => $proj{extend}->select('project_id') } ); 772 773 is( $row, undef, 774 "make sure row was deleted by cascading delte" ); 775 } 776 777 is( $dep{borg}->select('manager_id'), 1, 778 "The manager_id for the borg department will be 1 because the object does not the database was changed" ); 779 $dep{borg}->refresh; 780 781 my $dep_id = $dep{borg}->select('department_id'); 782 783 $emp_t->insert( values => { name => 'bob', smell => 'awful', dep_id => $dep_id } ); 784 $emp_t->insert( values => { name => 'rachel', smell => 'horrid', dep_id => $dep_id } ); 785 $emp_t->insert( values => { name => 'al', smell => 'bad', dep_id => $dep_id } ); 786 787 { 788 my @emps; 789 eval_ok ( sub { @emps = $emp_t->all_rows( order_by => 790 [ $emp_t->column('name') ] )->all_rows }, 791 "Select all employee rows with arrayref to order_by" ); 792 793 is( scalar @emps, 4, 794 "There should be 4 rows in the employee table" ); 795 is( $emps[0]->select('name'), 'al', 796 "First row name should be al" ); 797 is( $emps[1]->select('name'), 'bob', 798 "Second row name should be bob" ); 799 is( $emps[2]->select('name'), 'rachel', 800 "Third row name should be rachel" ); 801 is( $emps[3]->select('name'), 'unit 2', 802 "Fourth row name should be 'unit 2'" ); 803 } 804 805 { 806 my @emps; 807 eval_ok ( sub { @emps = $emp_t->all_rows( order_by => 808 [ $emp_t->column('name') ], 809 quote_identifiers => 1, 810 )->all_rows }, 811 "Select all employee rows with arrayref to order_by with quote_identifiers" ); 812 813 is( scalar @emps, 4, 814 "There should be 4 rows in the employee table" ); 815 is( $emps[0]->select('name'), 'al', 816 "First row name should be al" ); 817 is( $emps[1]->select('name'), 'bob', 818 "Second row name should be bob" ); 819 is( $emps[2]->select('name'), 'rachel', 820 "Third row name should be rachel" ); 821 is( $emps[3]->select('name'), 'unit 2', 822 "Fourth row name should be 'unit 2'" ); 823 } 824 825 { 826 my @emps; 827 eval_ok( sub { @emps = $emp_t->all_rows( order_by => $emp_t->column('name') )->all_rows }, 828 "Select all employee rows with column obj to order_by" ); 829 830 is( scalar @emps, 4, 831 "There should be 4 rows in the employee table" ); 832 is( $emps[0]->select('name'), 'al', 833 "First row name should be al" ); 834 is( $emps[1]->select('name'), 'bob', 835 "Second row name should be bob" ); 836 is( $emps[2]->select('name'), 'rachel', 837 "Third row name should be rachel" ); 838 is( $emps[3]->select('name'), 'unit 2', 839 "Fourth row name should be 'unit 2'" ); 840 } 841 842 { 843 my @emps; 844 eval_ok( sub { @emps = $emp_t->all_rows( order_by => [ $emp_t->column('name') ] )->all_rows }, 845 "Select all employee rows with arrayref to order_by" ); 846 847 is( scalar @emps, 4, 848 "There should be 4 rows in the employee table" ); 849 is( $emps[0]->select('name'), 'al', 850 "First row name should be al" ); 851 is( $emps[1]->select('name'), 'bob', 852 "Second row name should be bob" ); 853 is( $emps[2]->select('name'), 'rachel', 854 "Third row name should be rachel" ); 855 is( $emps[3]->select('name'), 'unit 2', 856 "Fourth row name should be 'unit 2'" ); 857 } 858 859 { 860 my @emps; 861 eval_ok( sub { @emps = $emp_t->all_rows( order_by => 862 [ $emp_t->column('smell') ] )->all_rows }, 863 "Select all employee rows with arrayref to order_by (by smell)" ); 864 865 is( scalar @emps, 4, 866 "There should be 4 rows in the employee table" ); 867 is( $emps[0]->select('name'), 'bob', 868 "First row name should be bob" ); 869 is( $emps[1]->select('name'), 'al', 870 "Second row name should be al" ); 871 is( $emps[2]->select('name'), 'unit 2', 872 "Third row name should be 'unit 2'" ); 873 is( $emps[3]->select('name'), 'rachel', 874 "Fourth row name should be rachel" ); 875 } 876 877 { 878 my @emps; 879 eval_ok( sub { @emps = $emp_t->all_rows( order_by => 880 [ $emp_t->column('smell'), 'desc' ] )->all_rows }, 881 "Select all employee rows order by smell (descending)" ); 882 883 is( $emps[0]->select('name'), 'rachel', 884 "First row name should be rachel" ); 885 is( $emps[1]->select('name'), 'unit 2', 886 "Second row name should be 'unit 2'" ); 887 is( $emps[2]->select('name'), 'al', 888 "Third row name should be al" ); 889 is( $emps[3]->select('name'), 'bob', 890 "Fourth row name should be bob" ); 891 } 892 893 eval_ok( sub { $count = $emp_t->row_count }, 894 "Call row_count for employee table" ); 895 896 is( $count, 4, 897 "The count should be 4" ); 898 899 eval_ok( sub { $count = $emp_t->function( select => COUNT( $emp_t->column('employee_id') ) ) }, 900 "Get row count via ->function method" ); 901 902 is( $count, 4, 903 "There should still be just 4 rows" ); 904 905 { 906 my $one; 907 eval_ok( sub { $one = $emp_t->function( select => 1 ) }, 908 "Get '1' via ->function method" ); 909 910 is( $one, 1, 911 "Getting '1' via ->function should return 1" ); 912 } 913 914 { 915 my $statement; 916 eval_ok( sub { $statement = $emp_t->select( select => COUNT( $emp_t->column('employee_id') ) ) }, 917 "Get row count via even spiffier new ->select method" ); 918 919 isa_ok( $statement, 'Alzabo::DriverStatement', 920 "Return value from Table->select method" ); 921 922 $count = $statement->next; 923 is( $count, 4, 924 "There should still be just 4 rows" ); 925 } 926 927 { 928 my $st; 929 eval_ok( sub { $st = $emp_t->select( select => 1 ) }, 930 "Get '1' via ->select method" ); 931 932 is( $st->next, 1, 933 "Getting '1' via ->select should return 1" ); 934 } 935 936 { 937 my @emps; 938 eval_ok( sub { @emps = $emp_t->all_rows( order_by => 939 [ $emp_t->column('smell'), 'desc' ], 940 limit => 2 )->all_rows }, 941 "Get all employee rows with ORDER BY and LIMIT" ); 942 943 is( scalar @emps, 2, 944 "This should only return 2 rows" ); 945 946 is( $emps[0]->select('name'), 'rachel', 947 "First row should be rachel" ); 948 is( $emps[1]->select('name'), 'unit 2', 949 "Second row is 'unit 2'" ); 950 } 951 952 { 953 my @emps; 954 eval_ok( sub { @emps = $emp_t->all_rows( order_by => 955 [ $emp_t->column('smell'), 'desc' ], 956 limit => [2, 2] )->all_rows }, 957 "Get all employee rows with ORDER BY and LIMIT (with offset)" ); 958 959 is( scalar @emps, 2, 960 "This should only return 2 rows" ); 961 962 is( $emps[0]->select('name'), 'al', 963 "First row should be al" ); 964 is( $emps[1]->select('name'), 'bob', 965 "Second row is bob" ); 966 } 967 968 $emp_t->set_prefetch( $emp_t->columns( qw( name smell ) ) ); 969 my @p = $emp_t->prefetch; 970 971 is( scalar @p, 2, 972 "Prefetch method should return 2 column names" ); 973 is( scalar ( grep { $_ eq 'name' } @p ), 1, 974 "One column should be 'name'" ); 975 is( scalar ( grep { $_ eq 'smell' } @p ), 1, 976 "And the other should be 'smell'" ); 977 978 is( $emp_t->row_count, 4, 979 "employee table should have 4 rows" ); 980 981 { 982 my @emps = $emp_t->all_rows( order_by => 983 [ $emp_t->column('smell'), 'desc' ], 984 limit => [2, 2] )->all_rows; 985 986 my $smell = $emps[0]->select('smell'); 987 is( $emp_t->row_count( where => [ $emp_t->column('smell'), '=', $smell ] ), 1, 988 "Call row_count method with where parameter." ); 989 990 $emps[0]->delete; 991 eval { $emps[0]->update( smell => 'kaboom' ); }; 992 $e = $@; 993 isa_ok( $e, 'Alzabo::Exception::NoSuchRow', 994 "Exception thrown from attempt to update a deleted row" ); 995 996 my $row_id = $emps[1]->id_as_string; 997 my $row; 998 eval_ok( sub { $row = $emp_t->row_by_id( row_id => $row_id ) }, 999 "Fetch a row via the ->row_by_id method" ); 1000 is( $row->id_as_string, $emps[1]->id_as_string, 1001 "Row retrieved via the ->row_by_id method should be the same as the row whose id was used" ); 1002 } 1003 1004 $emp_t->insert( values => { employee_id => 9000, 1005 name => 'bob9000', 1006 smell => 'a', 1007 dep_id => $dep_id } ); 1008 $emp_t->insert( values => { employee_id => 9001, 1009 name => 'bob9001', 1010 smell => 'b', 1011 dep_id => $dep_id } ); 1012 $emp_t->insert( values => { employee_id => 9002, 1013 name => 'bob9002', 1014 smell => 'c', 1015 dep_id => $dep_id } ); 1016 1017 my $eid_c = $emp_t->column('employee_id'); 1018 1019 { 1020 my @emps = $emp_t->rows_where( where => [ [ $eid_c, '=', 9000 ], 1021 'or', 1022 [ $eid_c, '=', 9002 ] ] )->all_rows; 1023 1024 @emps = sort { $a->select('employee_id') <=> $b->select('employee_id') } @emps; 1025 1026 is( @emps, 2, 1027 "Do a query with 'or' and count the rows" ); 1028 is( $emps[0]->select('employee_id'), 9000, 1029 "First row returned should be employee id 9000" ); 1030 1031 is( $emps[1]->select('employee_id'), 9002, 1032 "Second row returned should be employee id 9002" ); 1033 } 1034 1035 { 1036 my @emps = $emp_t->rows_where( where => [ [ $emp_t->column('smell'), '!=', 'c' ], 1037 'and', 1038 ( 1039 '(', 1040 [ $eid_c, '=', 9000 ], 1041 'or', 1042 [ $eid_c, '=', 9002 ], 1043 ')', 1044 ), 1045 ] )->all_rows; 1046 is( @emps, 1, 1047 "Do another complex query with 'or' and subgroups" ); 1048 is( $emps[0]->select('employee_id'), 9000, 1049 "The row returned should be employee id 9000" ); 1050 } 1051 1052 { 1053 my @emps = $emp_t->rows_where( where => [ ( 1054 '(', 1055 [ $eid_c, '=', 9000 ], 1056 'and', 1057 [ $eid_c, '=', 9000 ], 1058 ')', 1059 ), 1060 'or', 1061 ( 1062 '(', 1063 [ $eid_c, '=', 9000 ], 1064 'and', 1065 [ $eid_c, '=', 9000 ], 1066 ')', 1067 ), 1068 ] )->all_rows; 1069 1070 is( @emps, 1, 1071 "Do another complex query with 'or', 'and' and subgroups" ); 1072 is( $emps[0]->select('employee_id'), 9000, 1073 "The row returned should be employee id 9000" ); 1074 } 1075 1076 { 1077 my @emps = $emp_t->rows_where( where => [ $eid_c, 'between', 9000, 9002 ] )->all_rows; 1078 @emps = sort { $a->select('employee_id') <=> $b->select('employee_id') } @emps; 1079 1080 is( @emps, 3, 1081 "Select using between should return 3 rows" ); 1082 is( $emps[0]->select('employee_id'), 9000, 1083 "First row returned should be employee id 9000" ); 1084 is( $emps[1]->select('employee_id'), 9001, 1085 "Second row returned should be employee id 9001" ); 1086 is( $emps[2]->select('employee_id'), 9002, 1087 "Third row returned should be employee id 9002" ); 1088 } 1089 1090 { 1091 my @emps; 1092 eval_ok( sub { @emps = $emp_t->rows_where( where => [ '(', '(', 1093 [ $eid_c, '=', 9000 ], 1094 ')', ')' 1095 ] )->all_rows }, 1096 "Nested subgroups should be allowed" ); 1097 1098 is( @emps, 1, 1099 "Query with nested subgroups should return 1 row" ); 1100 is( $emps[0]->select('employee_id'), 9000, 1101 "The row returned should be employee id 9000" ); 1102 } 1103 1104 $emp_t->insert( values => { name => 'Smelly', 1105 smell => 'a', 1106 dep_id => $dep_id, 1107 } ); 1108 1109 { 1110 my @emps = eval { $emp_t->rows_where( where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ] )->all_rows }; 1111 1112 is( @emps, 4, 1113 "There should be only 4 employees where the length of the smell column is 1" ); 1114 } 1115 1116 { 1117 my @emps; 1118 eval_ok( sub { @emps = $emp_t->rows_where( where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ], 1119 limit => 2 )->all_rows }, 1120 "Select all employee rows with WHERE and LIMIT" ); 1121 1122 is( scalar @emps, 2, 1123 "Limit should cause only two employee rows to be returned" ); 1124 } 1125 1126 { 1127 my @emps; 1128 eval_ok( sub { @emps = $emp_t->rows_where( where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ], 1129 order_by => $emp_t->column('smell'), 1130 limit => 2 )->all_rows }, 1131 "Select all employee rows with WHERE, ORDER BY, and LIMIT" ); 1132 1133 is( scalar @emps, 2, 1134 "Limit should cause only two employee rows to be returned (again)" ); 1135 } 1136 1137 { 1138 my @emps; 1139 eval_ok( sub { @emps = $emp_t->rows_where( where => [ '(', 1140 [ $emp_t->column('employee_id'), '=', 9000 ], 1141 ')', 1142 ], 1143 order_by => $emp_t->column('employee_id') )->all_rows }, 1144 "Query with subgroup followed by order by" ); 1145 1146 is( @emps, 1, 1147 "Query with subgroup followed by order by should return 1 row" ); 1148 is( $emps[0]->select('employee_id'), 9000, 1149 "The row returned should be employee id 9000" ); 1150 } 1151 1152 my @smells = $emp_t->function( select => [ $emp_t->column('smell'), COUNT( $emp_t->column('smell') ) ], 1153 group_by => $emp_t->column('smell') ); 1154 # map smell to count 1155 my %smells = map { $_->[0] => $_->[1] } @smells; 1156 is( @smells, 6, 1157 "Query with group by should return 6 values" ); 1158 is( $smells{a}, 2, 1159 "Check count of smell = 'a'" ); 1160 is( $smells{b}, 1, 1161 "Check count of smell = 'b'" ); 1162 is( $smells{c}, 1, 1163 "Check count of smell = 'c'" ); 1164 is( $smells{awful}, 1, 1165 "Check count of smell = 'awful'" ); 1166 is( $smells{good}, 1, 1167 "Check count of smell = 'good'" ); 1168 is( $smells{horrid}, 1, 1169 "Check count of smell = 'horrid'" ); 1170 1171 { 1172 my $statement = $emp_t->select( select => [ $emp_t->column('smell'), COUNT( $emp_t->column('smell') ) ], 1173 group_by => $emp_t->column('smell') ); 1174 1175 my @smells = $statement->all_rows; 1176 1177 # map smell to count 1178 %smells = map { $_->[0] => $_->[1] } @smells; 1179 is( @smells, 6, 1180 "Query with group by should return 6 values - via ->select" ); 1181 is( $smells{a}, 2, 1182 "Check count of smell = 'a' - via ->select" ); 1183 is( $smells{b}, 1, 1184 "Check count of smell = 'b' - via ->select" ); 1185 is( $smells{c}, 1, 1186 "Check count of smell = 'c' - via ->select" ); 1187 is( $smells{awful}, 1, 1188 "Check count of smell = 'awful' - via ->select" ); 1189 is( $smells{good}, 1, 1190 "Check count of smell = 'good' - via ->select" ); 1191 is( $smells{horrid}, 1, 1192 "Check count of smell = 'horrid' - via ->select" ); 1193 } 1194 1195 @rows = $emp_t->function( select => $emp_t->column('smell'), 1196 where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ], 1197 order_by => $emp_t->column('smell') ); 1198 is( @rows, 4, 1199 "There should only be four rows which have a single character smell" ); 1200 is( $rows[0], 'a', 1201 "First smell should be 'a'" ); 1202 is( $rows[1], 'a', 1203 "Second smell should be 'a'" ); 1204 is( $rows[2], 'b', 1205 "Third smell should be 'b'" ); 1206 is( $rows[3], 'c', 1207 "Fourth smell should be 'c'" ); 1208 1209 { 1210 my $statement = $emp_t->select( select => $emp_t->column('smell'), 1211 where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ], 1212 order_by => $emp_t->column('smell') ); 1213 my @rows = $statement->all_rows; 1214 1215 is( @rows, 4, 1216 "There should only be four rows which have a single character smell - via ->select" ); 1217 is( $rows[0], 'a', 1218 "First smell should be 'a' - via ->select" ); 1219 is( $rows[1], 'a', 1220 "Second smell should be 'a' - via ->select" ); 1221 is( $rows[2], 'b', 1222 "Third smell should be 'b' - via ->select" ); 1223 is( $rows[3], 'c', 1224 "Fourth smell should be 'c' - via ->select" ); 1225 } 1226 1227 @rows = $emp_t->function( select => $emp_t->column('smell'), 1228 where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ], 1229 order_by => $emp_t->column('smell'), 1230 limit => 2, 1231 ); 1232 is( @rows, 2, 1233 "There should only be two rows which have a single character smell - with limit" ); 1234 is( $rows[0], 'a', 1235 "First smell should be 'a' - with limit" ); 1236 is( $rows[1], 'a', 1237 "Second smell should be 'a' - with limit" ); 1238 1239 { 1240 my $statement = $emp_t->select( select => $emp_t->column('smell'), 1241 where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ], 1242 order_by => $emp_t->column('smell'), 1243 limit => 2, 1244 ); 1245 my @rows = $statement->all_rows; 1246 1247 is( @rows, 2, 1248 "There should only be two rows which have a single character smell - with limit via ->select" ); 1249 is( $rows[0], 'a', 1250 "First smell should be 'a' - with limit via ->select" ); 1251 is( $rows[1], 'a', 1252 "Second smell should be 'a' - with limit via ->select" ); 1253 } 1254 1255 my $extend_id = $proj{extend}->select('project_id'); 1256 my $embrace_id = $proj{embrace}->select('project_id'); 1257 foreach ( [ 9000, $extend_id ], [ 9000, $embrace_id ], 1258 [ 9001, $extend_id ], [ 9002, $extend_id ] ) 1259 { 1260 $emp_proj_t->insert( values => { employee_id => $_->[0], 1261 project_id => $_->[1] } ); 1262 } 1263 1264 # find staffed projects 1265 @rows = $s->function( select => [ $proj_t->column('name'), 1266 COUNT( $proj_t->column('name') ) ], 1267 join => [ $emp_proj_t, $proj_t ], 1268 group_by => $proj_t->column('name') ); 1269 is( @rows, 2, 1270 "Only two projects should be returned from schema->function" ); 1271 is( $rows[0][0], 'Embrace', 1272 "First project should be Embrace" ); 1273 is( $rows[1][0], 'Extend', 1274 "Second project should be Extend" ); 1275 is( $rows[0][1], 1, 1276 "First project should have 1 employee" ); 1277 is( $rows[1][1], 3, 1278 "Second project should have 3 employees" ); 1279 1280 { 1281 my $statement = $s->select( select => [ $proj_t->column('name'), 1282 COUNT( $proj_t->column('name') ) ], 1283 join => [ $emp_proj_t, $proj_t ], 1284 group_by => $proj_t->column('name') ); 1285 my @rows = $statement->all_rows; 1286 1287 is( @rows, 2, 1288 "Only two projects should be returned from schema->select" ); 1289 is( $rows[0][0], 'Embrace', 1290 "First project should be Embrace - via ->select" ); 1291 is( $rows[1][0], 'Extend', 1292 "Second project should be Extend - via ->select" ); 1293 is( $rows[0][1], 1, 1294 "First project should have 1 employee - via ->select" ); 1295 is( $rows[1][1], 3, 1296 "Second project should have 3 employees - via ->select" ); 1297 } 1298 1299 @rows = $s->function( select => [ $proj_t->column('name'), 1300 COUNT( $proj_t->column('name') ) ], 1301 join => [ $emp_proj_t, $proj_t ], 1302 group_by => $proj_t->column('name'), 1303 limit => [1, 1], 1304 ); 1305 is( @rows, 1, 1306 "Only one projects should be returned from schema->function - with limit" ); 1307 is( $rows[0][0], 'Extend', 1308 "First project should be Extend - with limit" ); 1309 is( $rows[0][1], 3, 1310 "First project should have 3 employees - with limit" ); 1311 1312 { 1313 my $statement = $s->select( select => [ $proj_t->column('name'), 1314 COUNT( $proj_t->column('name') ) ], 1315 join => [ $emp_proj_t, $proj_t ], 1316 group_by => $proj_t->column('name'), 1317 limit => [1, 1], 1318 ); 1319 1320 my @rows = $statement->all_rows; 1321 1322 is( @rows, 1, 1323 "Only one projects should be returned from schema->select - with limit via ->select" ); 1324 is( $rows[0][0], 'Extend', 1325 "First project should be Extend - with limit via ->select" ); 1326 is( $rows[0][1], 3, 1327 "First project should have 3 employees - with limit via ->select" ); 1328 } 1329 1330 { 1331 my @rows = $s->function( select => [ $proj_t->column('name'), 1332 COUNT( $proj_t->column('name') ) ], 1333 join => [ $emp_proj_t, $proj_t ], 1334 group_by => $proj_t->column('name'), 1335 order_by => [ COUNT( $proj_t->column('name') ), 'DESC' ] ); 1336 1337 is( @rows, 2, 1338 "Only two projects should be returned from schema->function ordered by COUNT(*)" ); 1339 is( $rows[0][0], 'Extend', 1340 "First project should be Extend" ); 1341 is( $rows[1][0], 'Embrace', 1342 "Second project should be Embrace" ); 1343 is( $rows[0][1], 3, 1344 "First project should have 3 employee" ); 1345 is( $rows[1][1], 1, 1346 "Second project should have 1 employees" ); 1347 } 1348 1349 { 1350 my @rows = $s->function( select => [ $proj_t->column('name'), 1351 COUNT( $proj_t->column('name') ) ], 1352 join => [ $emp_proj_t, $proj_t ], 1353 group_by => $proj_t->column('name'), 1354 order_by => [ COUNT( $proj_t->column('name') ), 'DESC' ], 1355 having => [ COUNT( $proj_t->column('name') ), '>', 2 ], 1356 ); 1357 1358 is( @rows, 1, 1359 "Only one project should be returned from schema->function ordered by COUNT(*) HAVING COUNT(*) > 2" ); 1360 is( $rows[0][0], 'Extend', 1361 "First project should be Extend" ); 1362 is( $rows[0][1], 3, 1363 "First project should have 3 employee" ); 1364 } 1365 1366 { 1367 my @rows; 1368 eval_ok( sub { @rows = $s->function( select => 1, 1369 join => [ $emp_proj_t, $proj_t ], 1370 ) }, 1371 "Call schema->function with scalar select" ); 1372 1373 is( @rows, 4, 1374 "Should return four rows" ); 1375 } 1376 1377 { 1378 my $st; 1379 eval_ok( sub { $st = $s->select( select => 1, 1380 join => [ $emp_proj_t, $proj_t ], 1381 ) }, 1382 "Call schema->select with scalar select" ); 1383 1384 my @rows = $st->all_rows; 1385 is( @rows, 4, 1386 "Should return four rows" ); 1387 } 1388 1389 my $p1 = $proj_t->insert( values => { name => 'P1', 1390 department_id => $dep_id, 1391 } ); 1392 my $p2 = $proj_t->insert( values => { name => 'P2', 1393 department_id => $dep_id, 1394 } ); 1395 eval_ok( sub { $cursor = $s->join( distinct => $dep_t, 1396 join => [ $dep_t, $proj_t ], 1397 where => [ $proj_t->column('project_id'), 'in', 1398 map { $_->select('project_id') } $p1, $p2 ], 1399 ) }, 1400 "Do a join with distinct parameter set" ); 1401 1402 @rows = $cursor->all_rows; 1403 1404 is( scalar @rows, 1, 1405 "Setting distinct should cause only a single row to be returned" ); 1406 1407 is( $rows[0]->select('department_id'), $dep_id, 1408 "Returned row's department_id should be $dep_id" ); 1409 1410 { 1411 eval_ok( sub { $cursor = 1412 $s->join( distinct => $emp_proj_t, 1413 join => [ $emp_t, $emp_proj_t ], 1414 where => [ $emp_t->column('employee_id'), 'in', 9001 ], 1415 ) }, 1416 "Do a join with distinct parameter set to a table with a multi-col PK" ); 1417 1418 @rows = $cursor->all_rows; 1419 1420 is( scalar @rows, 1, 1421 "Setting distinct should cause only a single row to be returned" ); 1422 1423 is( $rows[0]->select('employee_id'), 9001, 1424 "Returned row's employee_id should be 9001" ); 1425 } 1426 1427 { 1428 eval_ok( sub { $cursor = 1429 $s->join 1430 ( distinct => [ $emp_t, $emp_proj_t ], 1431 join => [ $emp_t, $emp_proj_t ], 1432 where => 1433 [ $emp_t->column('employee_id'), 'in', 9000, 9001 ], 1434 ) }, 1435 "Do a join with distinct parameter set to a table with a multi-col PK" ); 1436 1437 @rows = $cursor->all_rows; 1438 1439 is( scalar @rows, 3, 1440 "Setting distinct should cause only three rows to be returned" ); 1441 1442 ok( ( grep { $_->[0]->select('employee_id') == 9000 } @rows ), 1443 "Returned rows should include employee_id 9000" ); 1444 1445 ok( ( grep { $_->[0]->select('employee_id') == 9001 } @rows ), 1446 "Returned rows should include employee_id 9001" ); 1447 } 1448 1449 { 1450 $proj_t->insert( values => { name => 'P99', 1451 department_id => $dep{lying}->select('department_id'), 1452 } ); 1453 1454 eval_ok( sub { $cursor = $s->join( distinct => $dep_t, 1455 join => [ $dep_t, $proj_t ], 1456 order_by => $proj_t->column('name'), 1457 ) }, 1458 "Do a join with distinct and order_by not in select" ); 1459 1460 @rows = $cursor->all_rows; 1461 1462 if ( $rdbms eq 'pg' ) 1463 { 1464 is( scalar @rows, 5, "distinct should cause only five rows to be returned" ); 1465 } 1466 else 1467 { 1468 is( scalar @rows, 2, "distinct should cause only two rows to be returned" ); 1469 } 1470 1471 is( $rows[0]->select('department_id'), $dep{borg}->select('department_id'), 1472 'first row is borg department' ); 1473 1474 is( $rows[-1]->select('department_id'), $dep{lying}->select('department_id'), 1475 'last row is lying department' ); 1476 1477 # Prevents a warning later about destroying a DBI handle with 1478 # active statement handles. 1479 undef $cursor; 1480 } 1481 1482 # insert rows used to test order by with multiple columns 1483 my $start_id = 999_990; 1484 foreach ( [ qw( OB1 bad ) ], 1485 [ qw( OB1 worse ) ], 1486 [ qw( OB2 bad ) ], 1487 [ qw( OB2 worse ) ], 1488 [ qw( OB3 awful ) ], 1489 [ qw( OB3 bad ) ], 1490 ) 1491 { 1492 $emp_t->insert( values => { employee_id => $start_id++, 1493 name => $_->[0], 1494 smell => $_->[1], 1495 dep_id => $dep_id } ); 1496 } 1497 1498 @rows = $emp_t->rows_where( where => [ $emp_t->column('employee_id'), 'BETWEEN', 1499 999_990, 999_996 ], 1500 order_by => [ $emp_t->columns( 'name', 'smell' ) ] )->all_rows; 1501 is( $rows[0]->select('name'), 'OB1', 1502 "First row name should be OB1" ); 1503 is( $rows[0]->select('smell'), 'bad', 1504 "First row smell should be bad" ); 1505 is( $rows[1]->select('name'), 'OB1', 1506 "Second row name should be OB1" ); 1507 is( $rows[1]->select('smell'), 'worse', 1508 "Second row smell should be bad" ); 1509 is( $rows[2]->select('name'), 'OB2', 1510 "Third row name should be OB2" ); 1511 is( $rows[2]->select('smell'), 'bad', 1512 "Third row smell should be bad" ); 1513 is( $rows[3]->select('name'), 'OB2', 1514 "Fourth row name should be OB2" ); 1515 is( $rows[3]->select('smell'), 'worse', 1516 "Fourth row smell should be worse" ); 1517 is( $rows[4]->select('name'), 'OB3', 1518 "Fifth row name should be OB3" ); 1519 is( $rows[4]->select('smell'), 'awful', 1520 "Fifth row smell should be awful" ); 1521 is( $rows[5]->select('name'), 'OB3', 1522 "Sixth row name should be OB3" ); 1523 is( $rows[5]->select('smell'), 'bad', 1524 "Sixth row smell should be bad" ); 1525 1526 @rows = $emp_t->rows_where( where => [ $emp_t->column('employee_id'), 'BETWEEN', 1527 999_990, 999_996 ], 1528 order_by => [ $emp_t->column('name'), 'desc', $emp_t->column('smell'), 'asc' ] )->all_rows; 1529 is( $rows[0]->select('name'), 'OB3', 1530 "First row name should be OB3" ); 1531 is( $rows[0]->select('smell'), 'awful', 1532 "First row smell should be awful" ); 1533 is( $rows[1]->select('name'), 'OB3', 1534 "Second row name should be OB3" ); 1535 is( $rows[1]->select('smell'), 'bad', 1536 "Second row smell should be bad" ); 1537 is( $rows[2]->select('name'), 'OB2', 1538 "Third row name should be OB2" ); 1539 is( $rows[2]->select('smell'), 'bad', 1540 "Third row smell should be bad" ); 1541 is( $rows[3]->select('name'), 'OB2', 1542 "Fourth row name should be OB2" ); 1543 is( $rows[3]->select('smell'), 'worse', 1544 "Fourth row smell should be worse" ); 1545 is( $rows[4]->select('name'), 'OB1', 1546 "Fifth row name should be OB1" ); 1547 is( $rows[4]->select('smell'), 'bad', 1548 "Fifth row smell should be bad" ); 1549 is( $rows[5]->select('name'), 'OB1', 1550 "Sixth row name should be OB1" ); 1551 is( $rows[5]->select('smell'), 'worse', 1552 "Sixth row smell should be worse" ); 1553 1554 if ( $rdbms eq 'mysql' ) 1555 { 1556 my $emp; 1557 eval_ok( sub { $emp = $emp_t->insert( values => { name => UNIX_TIMESTAMP(), 1558 dep_id => $dep_id } ) }, 1559 "Insert using SQL function UNIX_TIMESTAMP()" ); 1560 1561 like( $emp->select('name'), qr/\d+/, 1562 "Name should be all digits (unix timestamp)" ); 1563 1564 eval_ok( sub { $emp->update( name => LOWER('FOO') ) }, 1565 "Do update using SQL function LOWER()" ); 1566 1567 is( $emp->select('name'), 'foo', 1568 "Name should be 'foo'" ); 1569 1570 eval_ok( sub { $emp->update( name => REPEAT('Foo', 3) ) }, 1571 "Do update using SQL function REPEAT()" ); 1572 1573 is( $emp->select('name'), 'FooFooFoo', 1574 "Name should be 'FooFooFoo'" ); 1575 1576 eval_ok( sub { $emp->update( name => UPPER( REPEAT('Foo', 3) ) ) }, 1577 "Do update using nested SQL functions UPPER(REPEAT())" ); 1578 1579 is( $emp->select('name'), 'FOOFOOFOO', 1580 "Name should be 'FOOFOOFOO'" ); 1581 1582 $emp_t->insert( values => { name => 'Timestamp', 1583 dep_id => $dep_id, 1584 tstamp => time - 100_000 } ); 1585 1586 my $cursor; 1587 eval_ok( sub { $cursor = 1588 $emp_t->rows_where( where => 1589 [ [ $emp_t->column('tstamp'), '!=', undef ], 1590 [ $emp_t->column('tstamp'), '<', UNIX_TIMESTAMP() ] ] ) }, 1591 "Do select with where condition that uses SQL function UNIX_TIMESTAMP()" ); 1592 1593 my @rows = $cursor->all_rows; 1594 is( scalar @rows, 1, 1595 "Only one row should have a timestamp value that is not null and that is less than the current time" ); 1596 is( $rows[0]->select('name'), 'Timestamp', 1597 "That row should be named Timestamp" ); 1598 1599 # Fulltext support tests 1600 my $snuffle_id = $emp_t->insert( values => { name => 'snuffleupagus', 1601 smell => 'invisible', 1602 dep_id => $dep_id } )->select('employee_id'); 1603 1604 @rows = $emp_t->rows_where( where => [ MATCH( $emp_t->column('name') ), AGAINST('abathraspus') ] )->all_rows; 1605 is( @rows, 0, 1606 "Make sure that fulltext search doesn't give a false positive" ); 1607 1608 @rows = $emp_t->rows_where( where => [ MATCH( $emp_t->column('name') ), AGAINST('snuffleupagus') ] )->all_rows; 1609 is( @rows, 1, 1610 "Make sure that fulltext search for snuffleupagus returns 1 row" ); 1611 is( $rows[0]->select('employee_id'), $snuffle_id, 1612 "Make sure that the returned row is snuffleupagus" ); 1613 1614 my $rows = $emp_t->function( select => [ $emp_t->column('employee_id'), MATCH( $emp_t->column('name') ), AGAINST('snuffleupagus') ], 1615 where => [ MATCH( $emp_t->column('name') ), AGAINST('snuffleupagus') ] ); 1616 my ($id, $score) = @$rows; 1617 is( $id, $snuffle_id, 1618 "Returned row should still be snuffleupagus" ); 1619 like( $score, qr/\d+(?:\.\d+)?/, 1620 "Returned score should be some sort of number (integer or floating point)" ); 1621 ok( $score > 0, 1622 "The score should be greater than 0 because the match was successful" ); 1623 1624 eval_ok( sub { @rows = $emp_t->all_rows( order_by => [ IF( 'employee_id < 100', 1625 $emp_t->column('employee_id'), 1626 $emp_t->column('smell') ), 1627 $emp_t->column('employee_id'), 1628 ], 1629 )->all_rows }, 1630 "Order by IF() function" ); 1631 is( @rows, 16, 1632 "Seventeen rows should have been returned" ); 1633 is( $rows[0]->select('employee_id'), 3, 1634 "First row should be id 3" ); 1635 is( $rows[-1]->select('employee_id'), 999993, 1636 "Last row should be id 999993" ); 1637 1638 eval_ok( sub { @rows = $emp_t->all_rows( order_by => RAND() )->all_rows }, 1639 "order by RAND()" ); 1640 is ( @rows, 16, 1641 "This should return 16 rows" ); 1642 } 1643 elsif ( $rdbms eq 'pg' ) 1644 { 1645 my $emp; 1646 eval_ok( sub { $emp = $emp_t->insert( values => { name => NOW(), 1647 dep_id => $dep_id } ) }, 1648 "Do insert using SQL function NOW()" ); 1649 1650 like( $emp->select('name'), qr/\d+/, 1651 "Name should be all digits (Postgres timestamp)" ); 1652 1653 eval_ok( sub { $emp->update( name => LOWER('FOO') ) }, 1654 "Do update using SQL function LOWER()" ); 1655 1656 is( $emp->select('name'), 'foo', 1657 "Name should be 'foo'" ); 1658 1659 eval_ok( sub { $emp->update( name => REPEAT('Foo', 3) ) }, 1660 "Do update using SQL function REPEAT()" ); 1661 1662 is( $emp->select('name'), 'FooFooFoo', 1663 "Name should be 'FooFooFoo'" ); 1664 1665 eval_ok( sub { $emp->update( name => UPPER( REPEAT('Foo', 3) ) ) }, 1666 "Do update using nested SQL functions UPPER(REPEAT())" ); 1667 1668 is( $emp->select('name'), 'FOOFOOFOO', 1669 "Name should be 'FOOFOOFOO'" ); 1670 1671 $emp_t->insert( values => { name => 'Timestamp', 1672 dep_id => $dep_id, 1673 tstamp => time - 100_000 } ); 1674 1675 my $cursor; 1676 eval_ok( sub { $cursor = 1677 $emp_t->rows_where( where => 1678 [ [ $emp_t->column('tstamp'), '!=', undef ], 1679 [ $emp_t->column('tstamp'), '<', NOW() ] ] ) }, 1680 "Do select with where condition that uses SQL function NOW()" ); 1681 1682 my @rows = $cursor->all_rows; 1683 is( scalar @rows, 1, 1684 "Only one row should have a timestamp value that is not null and that is less than the current time" ); 1685 is( $rows[0]->select('name'), 'Timestamp', 1686 "That row should be named Timestamp" ); 1687 } 1688 1689 # Potential rows 1690 my $p_emp; 1691 eval_ok( sub { $p_emp = $emp_t->potential_row }, 1692 "Create potential row object"); 1693 1694 is( $p_emp->is_live, 0, 1695 "potential_row should ! ->is_live" ); 1696 1697 is( $p_emp->select('smell'), 'grotesque', 1698 "Potential Employee should have default smell, 'grotesque'" ); 1699 1700 { 1701 my $updated = $p_emp->update( cash => undef, smell => 'hello!' ); 1702 1703 ok( $updated, 'update() did change values' ); 1704 ok( ! defined $p_emp->select('cash'), 1705 "Potential Employee cash column is not defined" ); 1706 } 1707 1708 { 1709 my $updated = $p_emp->update( cash => undef, smell => 'hello!' ); 1710 1711 ok( ! $updated, 'update() did not change values' ); 1712 } 1713 1714 is( $p_emp->select('smell'), 'hello!', 1715 "smell for employee should be 'hello!' after update" ); 1716 1717 $p_emp->update( name => 'Ilya' ); 1718 is( $p_emp->select('name'), 'Ilya', 1719 "New employee got a name" ); 1720 1721 $p_emp->update( dep_id => $dep_id ); 1722 is( $p_emp->select('dep_id'), $dep_id, 1723 "New employee got a department" ); 1724 1725 eval { $p_emp->update( wrong => 'column' ) }; 1726 $e = $@; 1727 isa_ok( $e, 'Alzabo::Exception::Params', 1728 "Exception thrown from attempt to update a column which doesn't exist" ); 1729 1730 eval { $p_emp->update( name => undef ) }; 1731 $e = $@; 1732 isa_ok( $e, 'Alzabo::Exception::NotNullable', 1733 "Exception thrown from attempt to update a non-NULLable column in a potential row to null" ); 1734 1735 eval_ok( sub { $p_emp->make_live( values => { smell => 'cottony' } ) }, 1736 "Make potential row live"); 1737 1738 is( $p_emp->select('name'), 'Ilya', 1739 "Formerly potential employee row object should have same name as before" ); 1740 1741 is( $p_emp->select('smell'), 'cottony', 1742 "Formerly potential employee row object should have new smell of 'cottony'" ); 1743 1744 eval_ok ( sub { $p_emp->delete }, 1745 "Delete new employee" ); 1746 1747 eval_ok( sub { $p_emp = $emp_t->potential_row( values => { cash => 100 } ) }, 1748 "Create potential row object and set some fields "); 1749 1750 is( $p_emp->select('cash'), 100, 1751 "Employee cash should be 100" ); 1752 1753 eval { $emp_t->rows_where( where => [ $eid_c, '=', 9000, 1754 $eid_c, '=', 9002 ] ) }; 1755 $e = $@; 1756 isa_ok( $e, 'Alzabo::Exception::Params', 1757 "Exception from where clause as single arrayref with <>3 elements" ); 1758 1759 { 1760 # test that DriverStatement objects going out of scope leave 1761 # $@ alone! 1762 eval 1763 { 1764 my $cursor = $emp_t->all_rows; 1765 1766 die "ok\n"; 1767 }; 1768 1769 is( $@, "ok\n", 1770 "\$\@ should be 'ok'" ); 1771 } 1772 1773 { 1774 my $row; 1775 eval_ok( sub { $row = 1776 $emp_t->one_row 1777 ( where => [ $emp_t->column('name'), '=', 'nonexistent' ] ) }, 1778 "Call ->one_row with a query guaranteed to fail" ); 1779 1780 ok( ! defined $row, 1781 "Make sure that the query really returned nothing" ); 1782 } 1783 1784 { 1785 is( scalar $proj_t->prefetch, 1786 ( scalar $proj_t->columns - 1787 $proj_t->primary_key_size - 1788 scalar ( grep { $_->is_blob } $proj_t->columns ) ), 1789 "Check that schema->prefetch_all_but_blobs is on by default" ); 1790 } 1791 1792 { 1793 $proj_t->set_prefetch(); 1794 $s->prefetch_all; 1795 1796 is( scalar $proj_t->prefetch, 1797 ( scalar $proj_t->columns - 1798 scalar $proj_t->primary_key_size ), 1799 "Check that schema->prefetch_all works" ); 1800 } 1801 1802 { 1803 $proj_t->set_prefetch(); 1804 $s->prefetch_all_but_blobs; 1805 1806 is( scalar $proj_t->prefetch, 1807 ( scalar $proj_t->columns - 1808 $proj_t->primary_key_size - 1809 scalar ( grep { $_->is_blob } $proj_t->columns ) ), 1810 "Check that schema->prefetch_all_but_blobs works" ); 1811 } 1812 1813 { 1814 $s->prefetch_none; 1815 1816 is( scalar $proj_t->prefetch, 0, 1817 "Check that schema->prefetch_none works" ); 1818 } 1819 1820 { 1821 $s->prefetch_all; 1822 1823 my $cursor; 1824 1825 eval_ok( sub { $cursor = 1826 $s->join( join => [ $emp_t, $emp_proj_t, $proj_t ], 1827 where => [ $emp_t->column('employee_id'), '=', 9001 ] ) }, 1828 "Join with join as arrayref of arrayrefs" ); 1829 1830 my @rows = $cursor->next; 1831 1832 is( scalar @rows, 3, 1833 "3 rows per cursor ->next call" ); 1834 is( ( grep { defined } @rows ), 3, 1835 "Make sure all rows are defined" ); 1836 is( $rows[0]->select('employee_id'), 9001, 1837 "First rows should have employee_id == 9001" ); 1838 is( $rows[0]->select('name'), 'bob9001', 1839 "First rows should have employee with name eq 'bob9001'" ); 1840 is( $rows[2]->select('name'), 'Extend', 1841 "First rows should have project with name eq 'Extend'"); 1842 } 1843 1844 { 1845 my $foo = $emp_t->column('employee_id')->alias( as => 'foo' ); 1846 1847 my $st = $emp_t->select( select => $foo ); 1848 1849 my %h = $st->next_as_hash; 1850 is( exists $h{foo}, 1, 1851 "next_as_hash should return a hash with a 'foo' key" ); 1852 } 1853 1854 $s->disconnect; 1855} 1856