Alzabo
view release on metacpan or search on metacpan
t/03-runtime.t view on Meta::CPAN
my $new_emp;
eval_ok( sub { $new_emp = $emp_t->insert( values => { name => 'asfalksf',
dep_id => $borg_id,
smell => undef,
cash => 20.2,
} ) },
"Inserting a NULL into a non-nullable column that has a default should not produce an exception" );
eval_ok( sub { $new_emp->delete },
"Delete a just-created employee" );
}
eval { $emp_t->insert( values => { name => 'YetAnotherTest',
dep_id => undef,
cash => 1.1,
} ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::Params',
"Exception thrown from attempt to insert a NULL into dep_id for an employee" );
eval { $emp{bill}->update( dep_id => undef ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::Params',
"Exception thrown from attempt to update dep_id to NULL for an employee" );
{
my $updated = $emp{bill}->update( cash => undef, smell => 'hello!' );
ok( $updated, 'update() did change values' );
ok( ! defined $emp{bill}->select('cash'),
"Bill has no cash" );
}
{
my $updated = $emp{bill}->update( cash => undef, smell => 'hello!' );
ok( ! $updated, 'update() did not change values' );
}
ok( $emp{bill}->select('smell') eq 'hello!',
"smell for bill should be 'hello!'" );
eval { $emp{bill}->update( name => undef ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::NotNullable',
"Exception thrown from attempt to update a non-nullable column to NULL" );
eval_ok( sub { $dep{borg}->update( manager_id => $emp{bill}->select('employee_id') ) },
"Set manager_id column for borg department" );
eval_ok( sub { $emp{2} = $emp_t->insert( values =>
{ name => 'unit 2',
smell => 'good',
dep_id => $dep{lying}->select('department_id') } ) },
"Create employee 'unit 2'" );
my $emp2_id = $emp{2}->select('employee_id');
delete $emp{2};
my $cursor;
my $x = 0;
eval_ok( sub { $cursor =
$emp_t->rows_where
( where => [ $emp_t->column('employee_id'), '=', $emp2_id ] );
while ( my $row = $cursor->next )
{
$x++;
$emp{2} = $row;
}
},
"Retrieve 'unit 2' employee via rows_where method and cursor" );
is( $x, 1,
"Check count of rows found where employee_id == $emp2_id" );
is( $cursor->count, 1,
"Make sure cursor's count() is accurate" );
is( $emp{2}->select('name'), 'unit 2',
"Check that row found has name of 'unit 2'" );
{
my $row;
eval_ok( sub { $row =
$emp_t->one_row
( where =>
[ $emp_t->column('employee_id'), '=', $emp2_id ] ) },
"Retrieve 'unit 2' employee via one_row method" );
is( $row->select('name'), 'unit 2',
"Check that the single row returned has the name 'unit 2'" );
}
{
my $row;
eval_ok( sub { $row =
$emp_t->one_row
( where =>
[ $emp_t->column('employee_id'), '=', $emp2_id ],
quote_identifiers => 1,
) },
"Retrieve 'unit 2' employee via one_row method with quote_identifiers" );
is( $row->select('name'), 'unit 2',
"Check that the single row returned has the name 'unit 2'" );
}
my %proj;
$proj{extend} = $proj_t->insert( values => { name => 'Extend',
department_id => $dep{borg}->select('department_id') } );
$proj{embrace} = $proj_t->insert( values => { name => 'Embrace',
department_id => $dep{borg}->select('department_id') } );
$emp_proj_t->insert( values => { employee_id => $emp{bill}->select('employee_id'),
project_id => $proj{extend}->select('project_id') } );
$emp_proj_t->insert( values => { employee_id => $emp{bill}->select('employee_id'),
project_id => $proj{embrace}->select('project_id') } );
my $fk = $emp_t->foreign_keys_by_table($emp_proj_t);
my @emp_proj;
my @cursor_counts;
eval_ok( sub { $cursor = $emp{bill}->rows_by_foreign_key( foreign_key => $fk );
while ( my $row = $cursor->next )
{
push @emp_proj, $row;
push @cursor_counts, $cursor->count;
} },
"Fetch rows via ->rows_by_foreign_key method (expect cursor)" );
is( scalar @emp_proj, 2,
"Check that only two rows were returned" );
is( $emp_proj[0]->select('employee_id'), $emp{bill}->select('employee_id'),
"Check that employee_id in employee_project is same as bill's" );
is( $emp_proj[0]->select('project_id'), $proj{extend}->select('project_id'),
"Check that project_id in employee_project is same as extend project" );
foreach (1..2)
{
is( $cursor_counts[$_ - 1], $_,
"cursor->count should be 1..2" );
}
my $emp_proj = $emp_proj[0];
$fk = $emp_proj_t->foreign_keys_by_table($emp_t);
my $emp;
eval_ok( sub { $emp = $emp_proj->rows_by_foreign_key( foreign_key => $fk ) },
"Fetch rows via ->rows_by_foreign_key method (expect row)" );
is( $emp->select('employee_id'), $emp_proj->select('employee_id'),
"The returned row should have bill's employee_id" );
$x = 0;
my @rows;
eval_ok( sub { $cursor = $emp_t->all_rows;
$x++ while $cursor->next
},
"Fetch all rows from employee table" );
is( $x, 2,
"Only 2 rows should be found" );
$cursor->reset;
my $count = $cursor->all_rows;
is( $x, 2,
"Only 2 rows should be found after cursor reset" );
{
my $cursor;
eval_ok( sub { $cursor =
$s->join( join => [ $emp_t, $emp_proj_t, $proj_t ],
where =>
[ $emp_t->column('employee_id'), '=',
$emp{bill}->select('employee_id') ],
order_by => $proj_t->column('project_id'),
quote_identifiers => 1,
) },
"Join employee, employee_project, and project tables where employee_id = bill's employee id with quote_identifiers" );
my @rows = $cursor->next;
is( scalar @rows, 3,
"3 rows per cursor ->next call" );
is( $rows[0]->table->name, 'employee',
"First row is from employee table" );
is( $rows[1]->table->name, 'employee_project',
"Second row is from employee_project table" );
is( $rows[2]->table->name, 'project',
"Third row is from project table" );
my $first_proj_id = $rows[2]->select('project_id');
@rows = $cursor->next;
my $second_proj_id = $rows[2]->select('project_id');
ok( $first_proj_id < $second_proj_id,
"Order by clause should cause project rows to come back" .
" in ascending order of project id" );
}
{
my $cursor;
eval_ok( sub { $cursor =
$s->join( join => [ $emp_t, $emp_proj_t, $proj_t ],
where =>
[ [ $proj_t->column('project_id'), '=',
$proj{extend}->select('project_id') ],
'or',
[ $proj_t->column('project_id'), '=',
$proj{embrace}->select('project_id') ],
],
order_by => $proj_t->column('project_id') ) },
"Join employee, employee_project, and project tables with OR in where clause" );
1 while $cursor->next;
is( $cursor->count, 2,
"join with OR in where clause should return two sets of rows" );
}
# Alias code
{
my $e_alias;
eval_ok( sub { $e_alias = $emp_t->alias },
"Create an alias object for the employee table" );
my $p_alias;
eval_ok( sub { $p_alias = $proj_t->alias },
"Create an alias object for the project table" );
eval_ok( sub { $cursor =
$s->join( join => [ $e_alias, $emp_proj_t, $p_alias ],
where => [ $e_alias->column('employee_id'), '=', 1 ],
order_by => $p_alias->column('project_id'),
) },
"Join employee, employee_project, and project tables where" .
" employee_id = 1 using aliases" );
my @rows = $cursor->next;
is( scalar @rows, 3,
"3 rows per cursor ->next call" );
is( $rows[0]->table->name, 'employee',
"First row is from employee table" );
is( $rows[1]->table->name, 'employee_project',
"Second row is from employee_project table" );
is( $rows[2]->table->name, 'project',
"Third row is from project table" );
}
# Alias code & multiple joins to the same table
{
my $p_alias = $proj_t->alias;
eval_ok( sub { $cursor = $s->join( select => [ $p_alias, $proj_t ],
join => [ $p_alias, $emp_proj_t, $proj_t ],
where => [ [ $p_alias->column('project_id'), '=', 1 ],
[ $proj_t->column('project_id'), '=', 1 ] ],
) },
"Join employee_project and project table (twice) using aliases" );
my @rows = $cursor->next;
is( scalar @rows, 2,
"2 rows per cursor ->next call" );
is( $rows[0]->table->name, 'project',
"First row is from project table" );
is( $rows[1]->table->name, 'project',
"Second row is from project table" );
is( $rows[0]->table, $rows[1]->table,
"The two rows should share the same table object (the alias should be gone at this point)" );
}
{
my @rows;
eval_ok( sub { @rows = $s->one_row( tables => [ $emp_t, $emp_proj_t, $proj_t ],
where => [ $emp_t->column('employee_id'), '=', 1 ],
order_by => $proj_t->column('project_id') ) },
"Join employee, employee_project, and project tables where employee_id = 1 using one_row method" );
is( $rows[0]->table->name, 'employee',
"First row is from employee table" );
is( $rows[1]->table->name, 'employee_project',
"Second row is from employee_project table" );
is( $rows[2]->table->name, 'project',
"Third row is from project table" );
}
$cursor = $s->join( join => [ $emp_t, $emp_proj_t, $proj_t ],
where => [ $emp_t->column('employee_id'), '=', 1 ],
order_by => [ $proj_t->column('project_id'), 'desc' ] );
@rows = $cursor->next;
my $first_proj_id = $rows[2]->select('project_id');
@rows = $cursor->next;
my $second_proj_id = $rows[2]->select('project_id');
ok( $first_proj_id > $second_proj_id,
"Order by clause should cause project rows to come back in descending order of project id" );
$cursor = $s->join( join => [ $emp_t, $emp_proj_t, $proj_t ],
where => [ $emp_t->column('employee_id'), '=', 1 ],
order_by => [ $proj_t->column('project_id'), 'desc' ] );
@rows = $cursor->next;
$first_proj_id = $rows[2]->select('project_id');
@rows = $cursor->next;
$second_proj_id = $rows[2]->select('project_id');
ok( $first_proj_id > $second_proj_id,
"Order by clause (alternate form) should cause project rows to come back in descending order of project id" );
eval_ok( sub { $cursor = $s->join( select => [ $emp_t, $emp_proj_t, $proj_t ],
join => [ [ $emp_t, $emp_proj_t ],
[ $emp_proj_t, $proj_t ] ],
where => [ $emp_t->column('employee_id'), '=', 1 ] ) },
"Join with join as arrayref of arrayrefs" );
@rows = $cursor->next;
is( scalar @rows, 3,
"3 rows per cursor ->next call" );
is( $rows[0]->table->name, 'employee',
"First row is from employee table" );
is( $rows[1]->table->name, 'employee_project',
"Second row is from employee_project table" );
is( $rows[2]->table->name, 'project',
"Third row is from project table" );
{
my $cursor;
eval_ok( sub { $cursor = $s->join( join => [ [ $emp_t, $emp_proj_t ],
[ $emp_proj_t, $proj_t ] ],
where => [ $emp_t->column('employee_id'), '=', 1 ] ) },
"Same join with no select parameter" );
my @rows = $cursor->next;
@rows = sort { $a->table->name cmp $b->table->name } @rows;
is( scalar @rows, 3,
"3 rows per cursor ->next call" );
is( ( grep { $_->table->name eq 'employee' } @rows ), 1,
"First row is from employee table" );
is( ( grep { $_->table->name eq 'employee_project' } @rows ), 1,
"Second row is from employee_project table" );
is( ( grep { $_->table->name eq 'project' } @rows ), 1,
"Third row is from project table" );
}
eval { $s->join( select => [ $emp_t, $emp_proj_t, $proj_t ],
join => [ [ $emp_t, $emp_proj_t ],
[ $emp_proj_t, $proj_t ],
[ $s->tables( 'outer_1', 'outer_2' ) ] ],
where => [ $emp_t->column('employee_id'), '=', 1 ] ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::Logic',
"Exception thrown from join with table map that does not connect" );
eval_ok( sub { @rows = $s->join( join => $emp_t,
where => [ $emp_t->column('employee_id'), '=', 1 ] )->all_rows },
"Join with a single table" );
is( @rows, 1,
"Only one row should be returned" );
is( $rows[0]->select('employee_id'), 1,
"Returned employee should be employee number one" );
{
$s->table('outer_2')->insert( values => { outer_2_name => 'will match something',
outer_2_pk => 1 },
);
$s->table('outer_2')->insert( values => { outer_2_name => 'will match nothing',
outer_2_pk => 99 },
);
$s->table('outer_1')->insert( values => { outer_1_name => 'test1 (has matching join row)',
outer_2_pk => 1 },
);
$s->table('outer_1')->insert( values => { outer_1_name => 'test2 (has no matching join row)',
outer_2_pk => undef },
);
{
my $cursor;
eval_ok( sub { $cursor =
$s->join
( select => [ $s->tables( 'outer_1', 'outer_2' ) ],
join =>
[ left_outer_join =>
$s->tables( 'outer_1', 'outer_2' ) ]
) },
"Do a left outer join" );
my @sets = $cursor->all_rows;
is( scalar @sets, 2,
"Left outer join should return 2 sets of rows" );
# re-order so that the set with 2 valid rows is always first
unless ( defined $sets[0]->[1] )
{
my $set = shift @sets;
push @sets, $set;
}
is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)',
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets[0]->[1]->select('outer_2_name'), 'will match something',
"The second row in the first set should have the name 'will match something'" );
is( $sets[1]->[0]->select('outer_1_name'), 'test2 (has no matching join row)',
"The first row in the second set should have the name 'test12 (has no matching join row)'" );
ok( ! defined $sets[1]->[1],
"The second row in the second set should not be defined" );
}
{
my $cursor;
eval_ok( sub { $cursor =
$s->join
( select => [ $s->tables( 'outer_1', 'outer_2' ) ],
join =>
[ [ left_outer_join =>
$s->tables( 'outer_1', 'outer_2' ),
[ $s->table('outer_2')->column( 'outer_2_pk' ),
'!=', 1 ],
] ],
order_by =>
$s->table('outer_1')->column('outer_1_name')
) },
"Do a left outer join" );
my @sets = $cursor->all_rows;
is( scalar @sets, 2,
"Left outer join should return 2 sets of rows" );
is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)',
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets[0]->[1], undef,
"The second row in the first set should be undef" );
is( $sets[1]->[0]->select('outer_1_name'), 'test2 (has no matching join row)',
"The first row in the second set should have the name 'test1 (has matching join row)'" );
is( $sets[1]->[1], undef,
"The second row in the second set should be undef" );
}
{
my $fk = $s->table('outer_1')->foreign_keys_by_table( $s->table('outer_2') );
my $cursor;
eval_ok( sub { $cursor =
$s->join
( select => [ $s->tables( 'outer_1', 'outer_2' ) ],
join =>
[ [ left_outer_join =>
$s->tables( 'outer_1', 'outer_2' ),
$fk,
[ $s->table('outer_2')->column( 'outer_2_pk' ),
'!=', 1 ],
] ],
order_by =>
$s->table('outer_1')->column('outer_1_name')
) },
"Do a left outer join" );
my @sets = $cursor->all_rows;
is( scalar @sets, 2,
"Left outer join should return 2 sets of rows" );
is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)',
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets[0]->[1], undef,
"The second row in the first set should be undef" );
is( $sets[1]->[0]->select('outer_1_name'), 'test2 (has no matching join row)',
"The first row in the second set should have the name 'test1 (has matching join row)'" );
is( $sets[1]->[1], undef,
"The second row in the second set should be undef" );
}
{
my $cursor;
eval_ok( sub { $cursor =
$s->join
( select => [ $s->tables( 'outer_1', 'outer_2' ) ],
join =>
[ [ right_outer_join =>
$s->tables( 'outer_1', 'outer_2' ) ] ]
) },
"Attempt a right outer join" );
my @sets = $cursor->all_rows;
is( scalar @sets, 2,
"Right outer join should return 2 sets of rows" );
# re-order so that the set with 2 valid rows is always first
unless ( defined $sets[0]->[0] )
{
my $set = shift @sets;
push @sets, $set;
}
is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)',
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets[0]->[1]->select('outer_2_name'), 'will match something',
"The second row in the first set should have the name 'will match something'" );
ok( ! defined $sets[1]->[0],
"The first row in the second set should not be defined" );
is( $sets[1]->[1]->select('outer_2_name'), 'will match nothing',
"The second row in the second set should have the name 'test12 (has no matching join row)'" );
}
{
my $cursor;
# do the same join, but with specified foreign key
my $fk = $s->table('outer_1')->foreign_keys_by_table( $s->table('outer_2') );
eval_ok( sub { $cursor =
$s->join
( select => [ $s->tables( 'outer_1', 'outer_2' ) ],
join =>
[ [ right_outer_join =>
$s->tables( 'outer_1', 'outer_2' ), $fk ] ]
) },
"Attempt a right outer join, with explicit foreign key" );
my @sets = $cursor->all_rows;
is( scalar @sets, 2,
"Right outer join should return 2 sets of rows" );
# re-order so that the set with 2 valid rows is always first
unless ( defined $sets[0]->[0] )
{
my $set = shift @sets;
push @sets, $set;
}
is( $sets[0]->[0]->select('outer_1_name'), 'test1 (has matching join row)',
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets[0]->[1]->select('outer_2_name'), 'will match something',
"The second row in the first set should have the name 'will match something'" );
ok( ! defined $sets[1]->[0],
"The first row in the second set should not be defined" );
is( $sets[1]->[1]->select('outer_2_name'), 'will match nothing',
"The second row in the second set should have the name 'test12 (has no matching join row)'" );
}
}
my $id = $emp{bill}->select('employee_id');
$emp{bill}->delete;
eval { $emp{bill}->select('name'); };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::NoSuchRow',
"Exception thrown from attempt to select from deleted row object" );
{
my $row =
$emp_proj_t->row_by_pk
( pk =>
{ employee_id => $id,
project_id => $proj{extend}->select('project_id') } );
is( $row, undef,
"make sure row was deleted by cascading delte" );
}
is( $dep{borg}->select('manager_id'), 1,
"The manager_id for the borg department will be 1 because the object does not the database was changed" );
$dep{borg}->refresh;
my $dep_id = $dep{borg}->select('department_id');
$emp_t->insert( values => { name => 'bob', smell => 'awful', dep_id => $dep_id } );
$emp_t->insert( values => { name => 'rachel', smell => 'horrid', dep_id => $dep_id } );
$emp_t->insert( values => { name => 'al', smell => 'bad', dep_id => $dep_id } );
{
my @emps;
eval_ok ( sub { @emps = $emp_t->all_rows( order_by =>
[ $emp_t->column('name') ] )->all_rows },
t/03-runtime.t view on Meta::CPAN
order_by => [ COUNT( $proj_t->column('name') ), 'DESC' ] );
is( @rows, 2,
"Only two projects should be returned from schema->function ordered by COUNT(*)" );
is( $rows[0][0], 'Extend',
"First project should be Extend" );
is( $rows[1][0], 'Embrace',
"Second project should be Embrace" );
is( $rows[0][1], 3,
"First project should have 3 employee" );
is( $rows[1][1], 1,
"Second project should have 1 employees" );
}
{
my @rows = $s->function( select => [ $proj_t->column('name'),
COUNT( $proj_t->column('name') ) ],
join => [ $emp_proj_t, $proj_t ],
group_by => $proj_t->column('name'),
order_by => [ COUNT( $proj_t->column('name') ), 'DESC' ],
having => [ COUNT( $proj_t->column('name') ), '>', 2 ],
);
is( @rows, 1,
"Only one project should be returned from schema->function ordered by COUNT(*) HAVING COUNT(*) > 2" );
is( $rows[0][0], 'Extend',
"First project should be Extend" );
is( $rows[0][1], 3,
"First project should have 3 employee" );
}
{
my @rows;
eval_ok( sub { @rows = $s->function( select => 1,
join => [ $emp_proj_t, $proj_t ],
) },
"Call schema->function with scalar select" );
is( @rows, 4,
"Should return four rows" );
}
{
my $st;
eval_ok( sub { $st = $s->select( select => 1,
join => [ $emp_proj_t, $proj_t ],
) },
"Call schema->select with scalar select" );
my @rows = $st->all_rows;
is( @rows, 4,
"Should return four rows" );
}
my $p1 = $proj_t->insert( values => { name => 'P1',
department_id => $dep_id,
} );
my $p2 = $proj_t->insert( values => { name => 'P2',
department_id => $dep_id,
} );
eval_ok( sub { $cursor = $s->join( distinct => $dep_t,
join => [ $dep_t, $proj_t ],
where => [ $proj_t->column('project_id'), 'in',
map { $_->select('project_id') } $p1, $p2 ],
) },
"Do a join with distinct parameter set" );
@rows = $cursor->all_rows;
is( scalar @rows, 1,
"Setting distinct should cause only a single row to be returned" );
is( $rows[0]->select('department_id'), $dep_id,
"Returned row's department_id should be $dep_id" );
{
eval_ok( sub { $cursor =
$s->join( distinct => $emp_proj_t,
join => [ $emp_t, $emp_proj_t ],
where => [ $emp_t->column('employee_id'), 'in', 9001 ],
) },
"Do a join with distinct parameter set to a table with a multi-col PK" );
@rows = $cursor->all_rows;
is( scalar @rows, 1,
"Setting distinct should cause only a single row to be returned" );
is( $rows[0]->select('employee_id'), 9001,
"Returned row's employee_id should be 9001" );
}
{
eval_ok( sub { $cursor =
$s->join
( distinct => [ $emp_t, $emp_proj_t ],
join => [ $emp_t, $emp_proj_t ],
where =>
[ $emp_t->column('employee_id'), 'in', 9000, 9001 ],
) },
"Do a join with distinct parameter set to a table with a multi-col PK" );
@rows = $cursor->all_rows;
is( scalar @rows, 3,
"Setting distinct should cause only three rows to be returned" );
ok( ( grep { $_->[0]->select('employee_id') == 9000 } @rows ),
"Returned rows should include employee_id 9000" );
ok( ( grep { $_->[0]->select('employee_id') == 9001 } @rows ),
"Returned rows should include employee_id 9001" );
}
{
$proj_t->insert( values => { name => 'P99',
department_id => $dep{lying}->select('department_id'),
} );
eval_ok( sub { $cursor = $s->join( distinct => $dep_t,
join => [ $dep_t, $proj_t ],
order_by => $proj_t->column('name'),
) },
"Do a join with distinct and order_by not in select" );
@rows = $cursor->all_rows;
if ( $rdbms eq 'pg' )
{
is( scalar @rows, 5, "distinct should cause only five rows to be returned" );
}
else
{
is( scalar @rows, 2, "distinct should cause only two rows to be returned" );
}
is( $rows[0]->select('department_id'), $dep{borg}->select('department_id'),
'first row is borg department' );
is( $rows[-1]->select('department_id'), $dep{lying}->select('department_id'),
'last row is lying department' );
# Prevents a warning later about destroying a DBI handle with
# active statement handles.
undef $cursor;
}
# insert rows used to test order by with multiple columns
my $start_id = 999_990;
foreach ( [ qw( OB1 bad ) ],
[ qw( OB1 worse ) ],
[ qw( OB2 bad ) ],
[ qw( OB2 worse ) ],
[ qw( OB3 awful ) ],
[ qw( OB3 bad ) ],
)
{
$emp_t->insert( values => { employee_id => $start_id++,
name => $_->[0],
smell => $_->[1],
dep_id => $dep_id } );
}
@rows = $emp_t->rows_where( where => [ $emp_t->column('employee_id'), 'BETWEEN',
999_990, 999_996 ],
order_by => [ $emp_t->columns( 'name', 'smell' ) ] )->all_rows;
is( $rows[0]->select('name'), 'OB1',
"First row name should be OB1" );
is( $rows[0]->select('smell'), 'bad',
"First row smell should be bad" );
is( $rows[1]->select('name'), 'OB1',
"Second row name should be OB1" );
is( $rows[1]->select('smell'), 'worse',
"Second row smell should be bad" );
is( $rows[2]->select('name'), 'OB2',
"Third row name should be OB2" );
is( $rows[2]->select('smell'), 'bad',
"Third row smell should be bad" );
is( $rows[3]->select('name'), 'OB2',
"Fourth row name should be OB2" );
is( $rows[3]->select('smell'), 'worse',
"Fourth row smell should be worse" );
is( $rows[4]->select('name'), 'OB3',
"Fifth row name should be OB3" );
is( $rows[4]->select('smell'), 'awful',
"Fifth row smell should be awful" );
is( $rows[5]->select('name'), 'OB3',
"Sixth row name should be OB3" );
is( $rows[5]->select('smell'), 'bad',
"Sixth row smell should be bad" );
@rows = $emp_t->rows_where( where => [ $emp_t->column('employee_id'), 'BETWEEN',
999_990, 999_996 ],
order_by => [ $emp_t->column('name'), 'desc', $emp_t->column('smell'), 'asc' ] )->all_rows;
is( $rows[0]->select('name'), 'OB3',
"First row name should be OB3" );
is( $rows[0]->select('smell'), 'awful',
"First row smell should be awful" );
is( $rows[1]->select('name'), 'OB3',
"Second row name should be OB3" );
is( $rows[1]->select('smell'), 'bad',
"Second row smell should be bad" );
is( $rows[2]->select('name'), 'OB2',
"Third row name should be OB2" );
is( $rows[2]->select('smell'), 'bad',
"Third row smell should be bad" );
is( $rows[3]->select('name'), 'OB2',
"Fourth row name should be OB2" );
is( $rows[3]->select('smell'), 'worse',
"Fourth row smell should be worse" );
is( $rows[4]->select('name'), 'OB1',
"Fifth row name should be OB1" );
is( $rows[4]->select('smell'), 'bad',
"Fifth row smell should be bad" );
is( $rows[5]->select('name'), 'OB1',
"Sixth row name should be OB1" );
is( $rows[5]->select('smell'), 'worse',
"Sixth row smell should be worse" );
if ( $rdbms eq 'mysql' )
{
my $emp;
eval_ok( sub { $emp = $emp_t->insert( values => { name => UNIX_TIMESTAMP(),
dep_id => $dep_id } ) },
"Insert using SQL function UNIX_TIMESTAMP()" );
like( $emp->select('name'), qr/\d+/,
"Name should be all digits (unix timestamp)" );
eval_ok( sub { $emp->update( name => LOWER('FOO') ) },
"Do update using SQL function LOWER()" );
is( $emp->select('name'), 'foo',
"Name should be 'foo'" );
eval_ok( sub { $emp->update( name => REPEAT('Foo', 3) ) },
"Do update using SQL function REPEAT()" );
is( $emp->select('name'), 'FooFooFoo',
"Name should be 'FooFooFoo'" );
eval_ok( sub { $emp->update( name => UPPER( REPEAT('Foo', 3) ) ) },
"Do update using nested SQL functions UPPER(REPEAT())" );
is( $emp->select('name'), 'FOOFOOFOO',
"Name should be 'FOOFOOFOO'" );
$emp_t->insert( values => { name => 'Timestamp',
dep_id => $dep_id,
tstamp => time - 100_000 } );
my $cursor;
eval_ok( sub { $cursor =
$emp_t->rows_where( where =>
[ [ $emp_t->column('tstamp'), '!=', undef ],
[ $emp_t->column('tstamp'), '<', UNIX_TIMESTAMP() ] ] ) },
"Do select with where condition that uses SQL function UNIX_TIMESTAMP()" );
my @rows = $cursor->all_rows;
is( scalar @rows, 1,
"Only one row should have a timestamp value that is not null and that is less than the current time" );
is( $rows[0]->select('name'), 'Timestamp',
"That row should be named Timestamp" );
# Fulltext support tests
my $snuffle_id = $emp_t->insert( values => { name => 'snuffleupagus',
smell => 'invisible',
dep_id => $dep_id } )->select('employee_id');
@rows = $emp_t->rows_where( where => [ MATCH( $emp_t->column('name') ), AGAINST('abathraspus') ] )->all_rows;
is( @rows, 0,
"Make sure that fulltext search doesn't give a false positive" );
@rows = $emp_t->rows_where( where => [ MATCH( $emp_t->column('name') ), AGAINST('snuffleupagus') ] )->all_rows;
is( @rows, 1,
"Make sure that fulltext search for snuffleupagus returns 1 row" );
is( $rows[0]->select('employee_id'), $snuffle_id,
"Make sure that the returned row is snuffleupagus" );
my $rows = $emp_t->function( select => [ $emp_t->column('employee_id'), MATCH( $emp_t->column('name') ), AGAINST('snuffleupagus') ],
where => [ MATCH( $emp_t->column('name') ), AGAINST('snuffleupagus') ] );
my ($id, $score) = @$rows;
is( $id, $snuffle_id,
"Returned row should still be snuffleupagus" );
like( $score, qr/\d+(?:\.\d+)?/,
"Returned score should be some sort of number (integer or floating point)" );
ok( $score > 0,
"The score should be greater than 0 because the match was successful" );
eval_ok( sub { @rows = $emp_t->all_rows( order_by => [ IF( 'employee_id < 100',
$emp_t->column('employee_id'),
$emp_t->column('smell') ),
$emp_t->column('employee_id'),
],
)->all_rows },
"Order by IF() function" );
is( @rows, 16,
"Seventeen rows should have been returned" );
is( $rows[0]->select('employee_id'), 3,
"First row should be id 3" );
is( $rows[-1]->select('employee_id'), 999993,
"Last row should be id 999993" );
eval_ok( sub { @rows = $emp_t->all_rows( order_by => RAND() )->all_rows },
"order by RAND()" );
is ( @rows, 16,
"This should return 16 rows" );
}
elsif ( $rdbms eq 'pg' )
{
my $emp;
eval_ok( sub { $emp = $emp_t->insert( values => { name => NOW(),
dep_id => $dep_id } ) },
"Do insert using SQL function NOW()" );
like( $emp->select('name'), qr/\d+/,
"Name should be all digits (Postgres timestamp)" );
eval_ok( sub { $emp->update( name => LOWER('FOO') ) },
"Do update using SQL function LOWER()" );
is( $emp->select('name'), 'foo',
"Name should be 'foo'" );
eval_ok( sub { $emp->update( name => REPEAT('Foo', 3) ) },
"Do update using SQL function REPEAT()" );
is( $emp->select('name'), 'FooFooFoo',
"Name should be 'FooFooFoo'" );
eval_ok( sub { $emp->update( name => UPPER( REPEAT('Foo', 3) ) ) },
"Do update using nested SQL functions UPPER(REPEAT())" );
is( $emp->select('name'), 'FOOFOOFOO',
"Name should be 'FOOFOOFOO'" );
$emp_t->insert( values => { name => 'Timestamp',
dep_id => $dep_id,
tstamp => time - 100_000 } );
my $cursor;
eval_ok( sub { $cursor =
$emp_t->rows_where( where =>
[ [ $emp_t->column('tstamp'), '!=', undef ],
[ $emp_t->column('tstamp'), '<', NOW() ] ] ) },
"Do select with where condition that uses SQL function NOW()" );
my @rows = $cursor->all_rows;
is( scalar @rows, 1,
"Only one row should have a timestamp value that is not null and that is less than the current time" );
is( $rows[0]->select('name'), 'Timestamp',
"That row should be named Timestamp" );
}
# Potential rows
my $p_emp;
eval_ok( sub { $p_emp = $emp_t->potential_row },
"Create potential row object");
is( $p_emp->is_live, 0,
"potential_row should ! ->is_live" );
is( $p_emp->select('smell'), 'grotesque',
"Potential Employee should have default smell, 'grotesque'" );
{
my $updated = $p_emp->update( cash => undef, smell => 'hello!' );
ok( $updated, 'update() did change values' );
ok( ! defined $p_emp->select('cash'),
"Potential Employee cash column is not defined" );
}
{
my $updated = $p_emp->update( cash => undef, smell => 'hello!' );
ok( ! $updated, 'update() did not change values' );
}
is( $p_emp->select('smell'), 'hello!',
"smell for employee should be 'hello!' after update" );
$p_emp->update( name => 'Ilya' );
is( $p_emp->select('name'), 'Ilya',
"New employee got a name" );
$p_emp->update( dep_id => $dep_id );
is( $p_emp->select('dep_id'), $dep_id,
"New employee got a department" );
eval { $p_emp->update( wrong => 'column' ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::Params',
"Exception thrown from attempt to update a column which doesn't exist" );
eval { $p_emp->update( name => undef ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::NotNullable',
"Exception thrown from attempt to update a non-NULLable column in a potential row to null" );
eval_ok( sub { $p_emp->make_live( values => { smell => 'cottony' } ) },
"Make potential row live");
is( $p_emp->select('name'), 'Ilya',
"Formerly potential employee row object should have same name as before" );
is( $p_emp->select('smell'), 'cottony',
"Formerly potential employee row object should have new smell of 'cottony'" );
eval_ok ( sub { $p_emp->delete },
"Delete new employee" );
eval_ok( sub { $p_emp = $emp_t->potential_row( values => { cash => 100 } ) },
"Create potential row object and set some fields ");
is( $p_emp->select('cash'), 100,
"Employee cash should be 100" );
eval { $emp_t->rows_where( where => [ $eid_c, '=', 9000,
$eid_c, '=', 9002 ] ) };
$e = $@;
isa_ok( $e, 'Alzabo::Exception::Params',
"Exception from where clause as single arrayref with <>3 elements" );
{
# test that DriverStatement objects going out of scope leave
# $@ alone!
eval
{
my $cursor = $emp_t->all_rows;
die "ok\n";
};
is( $@, "ok\n",
"\$\@ should be 'ok'" );
}
{
my $row;
eval_ok( sub { $row =
$emp_t->one_row
( where => [ $emp_t->column('name'), '=', 'nonexistent' ] ) },
"Call ->one_row with a query guaranteed to fail" );
ok( ! defined $row,
"Make sure that the query really returned nothing" );
}
{
is( scalar $proj_t->prefetch,
( scalar $proj_t->columns -
$proj_t->primary_key_size -
scalar ( grep { $_->is_blob } $proj_t->columns ) ),
"Check that schema->prefetch_all_but_blobs is on by default" );
}
{
$proj_t->set_prefetch();
$s->prefetch_all;
is( scalar $proj_t->prefetch,
( scalar $proj_t->columns -
scalar $proj_t->primary_key_size ),
"Check that schema->prefetch_all works" );
}
{
$proj_t->set_prefetch();
$s->prefetch_all_but_blobs;
is( scalar $proj_t->prefetch,
( scalar $proj_t->columns -
$proj_t->primary_key_size -
scalar ( grep { $_->is_blob } $proj_t->columns ) ),
"Check that schema->prefetch_all_but_blobs works" );
}
{
$s->prefetch_none;
is( scalar $proj_t->prefetch, 0,
"Check that schema->prefetch_none works" );
}
{
$s->prefetch_all;
my $cursor;
eval_ok( sub { $cursor =
$s->join( join => [ $emp_t, $emp_proj_t, $proj_t ],
where => [ $emp_t->column('employee_id'), '=', 9001 ] ) },
"Join with join as arrayref of arrayrefs" );
my @rows = $cursor->next;
is( scalar @rows, 3,
"3 rows per cursor ->next call" );
is( ( grep { defined } @rows ), 3,
"Make sure all rows are defined" );
is( $rows[0]->select('employee_id'), 9001,
"First rows should have employee_id == 9001" );
is( $rows[0]->select('name'), 'bob9001',
"First rows should have employee with name eq 'bob9001'" );
is( $rows[2]->select('name'), 'Extend',
"First rows should have project with name eq 'Extend'");
}
{
my $foo = $emp_t->column('employee_id')->alias( as => 'foo' );
my $st = $emp_t->select( select => $foo );
my %h = $st->next_as_hash;
is( exists $h{foo}, 1,
"next_as_hash should return a hash with a 'foo' key" );
}
$s->disconnect;
}
( run in 0.614 second using v1.01-cache-2.11-cpan-5837b0d9d2c )