#!/usr/bin/perl -w

use strict;

use File::Spec;

use lib '.', File::Spec->catdir( File::Spec->curdir, 't', 'lib' );

use Alzabo::Test::Utils;

use Test::More;


my @rdbms_names = Alzabo::Test::Utils->rdbms_names;

unless (@rdbms_names)
{
    plan skip_all => 'no test config provided';
    exit;
}

my $tests_per_run = 340;
my $test_count = $tests_per_run * @rdbms_names;

my %SINGLE_RDBMS_TESTS = ( mysql => 23,
			   pg => 11,
			 );

foreach my $rdbms ( keys %SINGLE_RDBMS_TESTS )
{
    next unless grep { $_ eq $rdbms } @rdbms_names;

    $test_count += $SINGLE_RDBMS_TESTS{$rdbms};
}

plan tests => $test_count;


Alzabo::Test::Utils->remove_all_schemas;


foreach my $rdbms (@rdbms_names)
{
    if ( $rdbms eq 'mysql' )
    {
        # prevent subroutine redefinition warnings
        local $^W = 0;
	eval 'use Alzabo::SQLMaker::MySQL qw(:all)';
    }
    elsif ( $rdbms eq 'pg' )
    {
        local $^W = 0;
	eval 'use Alzabo::SQLMaker::PostgreSQL qw(:all)';
    }

    Alzabo::Test::Utils->make_schema($rdbms);

    run_tests($rdbms);

    Alzabo::Test::Utils->remove_schema($rdbms);
}

sub run_tests
{
    my $rdbms = shift;

    my $config = Alzabo::Test::Utils->test_config_for($rdbms);

    my $s = Alzabo::Runtime::Schema->load_from_file( name => $config->{schema_name} );

    # tests setting basic parameters and connecting to RDBMS
    {
        eval_ok( sub { $s->set_user('foo') },
                 "Set user for schema to foo" );

        eval_ok( sub { $s->set_password('foo') },
                 "Set password for schema to foo" );

        eval_ok( sub { $s->set_host('foo') },
                 "Set host for schema to foo" );

        eval_ok( sub { $s->set_port(1234) },
                 "Set port for schema to 1234" );

        $s->$_(undef) foreach qw( set_user set_password set_host set_port );

        $s->connect( Alzabo::Test::Utils->connect_params_for($rdbms) );

        $s->set_referential_integrity(1);
    }

    {
        my $dbh = $s->driver->handle;
        isa_ok( $dbh, ref $s->driver->{dbh},
                "Object returned by \$s->driver->handle method" );

        eval_ok( sub { $s->driver->handle($dbh) },
                 "Set \$s->driver->handle" );
    }

    my $emp_t = $s->table('employee');
    my $dep_t = $s->table('department');
    my $proj_t = $s->table('project');
    my $emp_proj_t = $s->table('employee_project');

    my %dep;
    eval_ok( sub { $dep{borg} = $dep_t->insert( values => { name => 'borging' } ) },
	     "Insert borging row into department table" );

    is( $dep{borg}->select('name'), 'borging',
	"The borg department name should be 'borging'" );

    {
	my @all = $dep{borg}->select;
	is( @all, 3,
	    "select with no columns should return all the values" );
	is( $all[1], 'borging',
	    "The second value should be the department name" );

	my %all = $dep{borg}->select_hash;
	is( keys %all, 3,
	    "select_hash with no columns should return two keys" );
	ok( exists $all{department_id},
	    "The returned hash should have a department_id key" );
	ok( exists $all{name},
	    "The returned hash should have a department_id key" );
	is( $all{name}, 'borging',
	    "The value of the name key be the department name" );
    }


    $dep{lying} = $dep_t->insert( values => { name => 'lying to the public' } );

    my $borg_id = $dep{borg}->select('department_id');
    delete $dep{borg};

    eval_ok( sub { $dep{borg} = $dep_t->row_by_pk( pk => $borg_id ) },
	     "Retrieve borg department row via row_by_pk method" );

    isa_ok( $dep{borg}, 'Alzabo::Runtime::Row',
	    "Borg department" );

    is( $dep{borg}->select('name'), 'borging',
	"Department's name should be 'borging'" );

    eval { $dep_t->insert( values => { name => 'will break',
				       manager_id => 1 } ); };

    my $e = $@;
    isa_ok( $e, 'Alzabo::Exception::ReferentialIntegrity',
	    "Exception thrown from attempt to insert a non-existent manager_id into department" );

    my %emp;
    eval_ok( sub { $emp{bill} = $emp_t->insert( values => { name => 'Big Bill',
							    dep_id => $borg_id,
							    smell => 'robotic',
							    cash => 20.2,
							  } ) },
	     "Insert Big Bill into employee table" );

    my %data = $emp{bill}->select_hash( 'name', 'smell' );
    is( $data{name}, 'Big Bill',
	"select_hash - check name key" );
    is( $data{smell}, 'robotic',
	"select_hash - check smell key" );

    is( $emp{bill}->is_live, 1,
        "->is_live should be true for real row" );

    eval { $emp_t->insert( values => { name => undef,
				       dep_id => $borg_id,
				       smell => 'robotic',
				       cash => 20.2,
				     } ); };

    $e = $@;
    isa_ok( $e, 'Alzabo::Exception::NotNullable',
	    "Exception thrown from inserting a non-nullable column as NULL" );

    is( $e->table_name, 'employee',
        "NotNullable exceptions contain table name" );

    is( $e->schema_name, $config->{schema_name},
        "NotNullable exceptions contain schema name" );

    {
	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 },
		  "Select all employee rows with arrayref to order_by" );

	is( scalar @emps, 4,
	    "There should be 4 rows in the employee table" );
	is( $emps[0]->select('name'), 'al',
	    "First row name should be al" );
	is( $emps[1]->select('name'), 'bob',
	    "Second row name should be bob" );
	is( $emps[2]->select('name'), 'rachel',
	    "Third row name should be rachel" );
	is( $emps[3]->select('name'), 'unit 2',
	    "Fourth row name should be 'unit 2'" );
    }

    {
	my @emps;
	eval_ok ( sub { @emps = $emp_t->all_rows( order_by =>
						  [ $emp_t->column('name') ],
                                                  quote_identifiers => 1,
                                                )->all_rows },
		  "Select all employee rows with arrayref to order_by with quote_identifiers" );

	is( scalar @emps, 4,
	    "There should be 4 rows in the employee table" );
	is( $emps[0]->select('name'), 'al',
	    "First row name should be al" );
	is( $emps[1]->select('name'), 'bob',
	    "Second row name should be bob" );
	is( $emps[2]->select('name'), 'rachel',
	    "Third row name should be rachel" );
	is( $emps[3]->select('name'), 'unit 2',
	    "Fourth row name should be 'unit 2'" );
    }

    {
	my @emps;
	eval_ok( sub { @emps = $emp_t->all_rows( order_by => $emp_t->column('name') )->all_rows },
		 "Select all employee rows with column obj to order_by" );

	is( scalar @emps, 4,
	    "There should be 4 rows in the employee table" );
	is( $emps[0]->select('name'), 'al',
	    "First row name should be al" );
	is( $emps[1]->select('name'), 'bob',
	    "Second row name should be bob" );
	is( $emps[2]->select('name'), 'rachel',
	    "Third row name should be rachel" );
	is( $emps[3]->select('name'), 'unit 2',
	    "Fourth row name should be 'unit 2'" );
    }

    {
	my @emps;
	eval_ok( sub { @emps = $emp_t->all_rows( order_by => [ $emp_t->column('name') ] )->all_rows },
		 "Select all employee rows with arrayref to order_by" );

	is( scalar @emps, 4,
	    "There should be 4 rows in the employee table" );
	is( $emps[0]->select('name'), 'al',
	    "First row name should be al" );
	is( $emps[1]->select('name'), 'bob',
	    "Second row name should be bob" );
	is( $emps[2]->select('name'), 'rachel',
	    "Third row name should be rachel" );
	is( $emps[3]->select('name'), 'unit 2',
	    "Fourth row name should be 'unit 2'" );
    }

    {
	my @emps;
	eval_ok( sub { @emps = $emp_t->all_rows( order_by =>
						 [ $emp_t->column('smell') ] )->all_rows },
		 "Select all employee rows with arrayref to order_by (by smell)" );

	is( scalar @emps, 4,
	    "There should be 4 rows in the employee table" );
	is( $emps[0]->select('name'), 'bob',
	    "First row name should be bob" );
	is( $emps[1]->select('name'), 'al',
	    "Second row name should be al" );
	is( $emps[2]->select('name'), 'unit 2',
	    "Third row name should be 'unit 2'" );
	is( $emps[3]->select('name'), 'rachel',
	    "Fourth row name should be rachel" );
    }

    {
	my @emps;
	eval_ok( sub { @emps = $emp_t->all_rows( order_by =>
                                                 [ $emp_t->column('smell'), 'desc' ] )->all_rows },
		 "Select all employee rows order by smell (descending)" );

	is( $emps[0]->select('name'), 'rachel',
	    "First row name should be rachel" );
	is( $emps[1]->select('name'), 'unit 2',
	    "Second row name should be 'unit 2'" );
	is( $emps[2]->select('name'), 'al',
	    "Third row name should be al" );
	is( $emps[3]->select('name'), 'bob',
	    "Fourth row name should be bob" );
    }

    eval_ok( sub { $count = $emp_t->row_count },
	     "Call row_count for employee table" );

    is( $count, 4,
	"The count should be 4" );

    eval_ok( sub { $count = $emp_t->function( select => COUNT( $emp_t->column('employee_id') ) ) },
	     "Get row count via ->function method" );

    is( $count, 4,
	"There should still be just 4 rows" );

    {
	my $one;
	eval_ok( sub { $one = $emp_t->function( select => 1 ) },
		 "Get '1' via ->function method" );

	is( $one, 1,
	    "Getting '1' via ->function should return 1" );
    }

    {
	my $statement;
	eval_ok( sub { $statement = $emp_t->select( select => COUNT( $emp_t->column('employee_id') ) ) },
		 "Get row count via even spiffier new ->select method" );

	isa_ok( $statement, 'Alzabo::DriverStatement',
		"Return value from Table->select method" );

	$count = $statement->next;
	is( $count, 4,
	    "There should still be just 4 rows" );
    }

    {
	my $st;
	eval_ok( sub { $st = $emp_t->select( select => 1 ) },
		 "Get '1' via ->select method" );

	is( $st->next, 1,
	    "Getting '1' via ->select should return 1" );
    }

    {
	my @emps;
	eval_ok( sub { @emps = $emp_t->all_rows( order_by =>
                                                 [ $emp_t->column('smell'), 'desc' ],
						 limit => 2 )->all_rows },
		 "Get all employee rows with ORDER BY and LIMIT" );

	is( scalar @emps, 2,
	    "This should only return 2 rows" );

	is( $emps[0]->select('name'), 'rachel',
	    "First row should be rachel" );
	is( $emps[1]->select('name'), 'unit 2',
	    "Second row is 'unit 2'" );
    }

    {
	my @emps;
	eval_ok( sub { @emps = $emp_t->all_rows( order_by =>
                                                 [ $emp_t->column('smell'), 'desc' ],
						 limit => [2, 2] )->all_rows },
		 "Get all employee rows with ORDER BY and LIMIT (with offset)" );

	is( scalar @emps, 2,
	    "This should only return 2 rows" );

	is( $emps[0]->select('name'), 'al',
	    "First row should be al" );
	is( $emps[1]->select('name'), 'bob',
	    "Second row is bob" );
    }

    $emp_t->set_prefetch( $emp_t->columns( qw( name smell ) ) );
    my @p = $emp_t->prefetch;

    is( scalar @p, 2,
        "Prefetch method should return 2 column names" );
    is( scalar ( grep { $_ eq 'name' } @p ), 1,
        "One column should be 'name'" );
    is( scalar ( grep { $_ eq 'smell' } @p ), 1,
        "And the other should be 'smell'" );

    is( $emp_t->row_count, 4,
	"employee table should have 4 rows" );

    {
	my @emps = $emp_t->all_rows( order_by =>
                                     [ $emp_t->column('smell'), 'desc' ],
				     limit => [2, 2] )->all_rows;

	my $smell = $emps[0]->select('smell');
	is( $emp_t->row_count( where => [ $emp_t->column('smell'), '=', $smell ] ), 1,
	    "Call row_count method with where parameter." );

	$emps[0]->delete;
	eval { $emps[0]->update( smell => 'kaboom' ); };
	$e = $@;
	isa_ok( $e, 'Alzabo::Exception::NoSuchRow',
		"Exception thrown from attempt to update a deleted row" );

	my $row_id = $emps[1]->id_as_string;
	my $row;
	eval_ok( sub { $row = $emp_t->row_by_id( row_id => $row_id ) },
		 "Fetch a row via the ->row_by_id method" );
	is( $row->id_as_string, $emps[1]->id_as_string,
	    "Row retrieved via the ->row_by_id method should be the same as the row whose id was used" );
    }

    $emp_t->insert( values => { employee_id => 9000,
				name => 'bob9000',
				smell => 'a',
				dep_id => $dep_id } );
    $emp_t->insert( values => { employee_id => 9001,
				name => 'bob9001',
				smell => 'b',
				dep_id => $dep_id } );
    $emp_t->insert( values => { employee_id => 9002,
				name => 'bob9002',
				smell => 'c',
				dep_id => $dep_id } );

    my $eid_c = $emp_t->column('employee_id');

    {
	my @emps = $emp_t->rows_where( where => [ [ $eid_c, '=', 9000 ],
						  'or',
						  [ $eid_c, '=', 9002 ] ] )->all_rows;

	@emps = sort { $a->select('employee_id') <=> $b->select('employee_id') } @emps;

	is( @emps, 2,
	    "Do a query with 'or' and count the rows" );
	is( $emps[0]->select('employee_id'), 9000,
	    "First row returned should be employee id 9000" );

	is( $emps[1]->select('employee_id'), 9002,
	    "Second row returned should be employee id 9002" );
    }

    {
	my @emps = $emp_t->rows_where( where => [ [ $emp_t->column('smell'), '!=', 'c' ],
						  'and',
						  (
						   '(',
						   [ $eid_c, '=', 9000 ],
						   'or',
						   [ $eid_c, '=', 9002 ],
						   ')',
						  ),
						] )->all_rows;
	is( @emps, 1,
	    "Do another complex query with 'or' and subgroups" );
	is( $emps[0]->select('employee_id'), 9000,
	    "The row returned should be employee id 9000" );
    }

    {
	my @emps = $emp_t->rows_where( where => [ (
						   '(',
						   [ $eid_c, '=', 9000 ],
						   'and',
						   [ $eid_c, '=', 9000 ],
						   ')',
						  ),
						  'or',
						  (
						   '(',
						   [ $eid_c, '=', 9000 ],
						   'and',
						   [ $eid_c, '=', 9000 ],
						   ')',
						  ),
						] )->all_rows;

	is( @emps, 1,
	    "Do another complex query with 'or', 'and' and subgroups" );
	is( $emps[0]->select('employee_id'), 9000,
	    "The row returned should be employee id 9000" );
    }

    {
	my @emps = $emp_t->rows_where( where => [ $eid_c, 'between', 9000, 9002 ] )->all_rows;
	@emps = sort { $a->select('employee_id') <=> $b->select('employee_id') } @emps;

	is( @emps, 3,
	    "Select using between should return 3 rows" );
	is( $emps[0]->select('employee_id'), 9000,
	    "First row returned should be employee id 9000" );
	is( $emps[1]->select('employee_id'), 9001,
	    "Second row returned should be employee id 9001" );
	is( $emps[2]->select('employee_id'), 9002,
	    "Third row returned should be employee id 9002" );
    }

    {
	my @emps;
	eval_ok( sub { @emps = $emp_t->rows_where( where => [ '(', '(',
							      [ $eid_c, '=', 9000 ],
							      ')', ')'
							    ] )->all_rows },
		 "Nested subgroups should be allowed" );

	is( @emps, 1,
	    "Query with nested subgroups should return 1 row" );
	is( $emps[0]->select('employee_id'), 9000,
	    "The row returned should be employee id 9000" );
    }

    $emp_t->insert( values => { name => 'Smelly',
				smell => 'a',
				dep_id => $dep_id,
			      } );

    {
	my @emps = eval { $emp_t->rows_where( where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ] )->all_rows };

	is( @emps, 4,
	    "There should be only 4 employees where the length of the smell column is 1" );
    }

    {
	my @emps;
	eval_ok( sub { @emps = $emp_t->rows_where( where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ],
						   limit => 2 )->all_rows },
		 "Select all employee rows with WHERE and LIMIT" );

	is( scalar @emps, 2,
	    "Limit should cause only two employee rows to be returned" );
    }

    {
	my @emps;
	eval_ok( sub { @emps = $emp_t->rows_where( where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ],
						   order_by => $emp_t->column('smell'),
						   limit => 2 )->all_rows },
		 "Select all employee rows with WHERE, ORDER BY, and LIMIT" );

	is( scalar @emps, 2,
	    "Limit should cause only two employee rows to be returned (again)" );
    }

    {
	my @emps;
	eval_ok( sub { @emps = $emp_t->rows_where( where => [ '(',
							      [ $emp_t->column('employee_id'), '=', 9000 ],
							      ')',
							    ],
						   order_by => $emp_t->column('employee_id') )->all_rows },
		 "Query with subgroup followed by order by" );

	is( @emps, 1,
	    "Query with subgroup followed by order by should return 1 row" );
	is( $emps[0]->select('employee_id'), 9000,
	    "The row returned should be employee id 9000" );
    }

    my @smells = $emp_t->function( select => [ $emp_t->column('smell'), COUNT( $emp_t->column('smell') ) ],
				   group_by => $emp_t->column('smell') );
    # map smell to count
    my %smells = map { $_->[0] => $_->[1] } @smells;
    is( @smells, 6,
	"Query with group by should return 6 values" );
    is( $smells{a}, 2,
	"Check count of smell = 'a'" );
    is( $smells{b}, 1,
	"Check count of smell = 'b'" );
    is( $smells{c}, 1,
	"Check count of smell = 'c'" );
    is( $smells{awful}, 1,
	"Check count of smell = 'awful'" );
    is( $smells{good}, 1,
	"Check count of smell = 'good'" );
    is( $smells{horrid}, 1,
	"Check count of smell = 'horrid'" );

    {
	my $statement = $emp_t->select( select => [ $emp_t->column('smell'), COUNT( $emp_t->column('smell') ) ],
					group_by => $emp_t->column('smell') );

	my @smells = $statement->all_rows;

	# map smell to count
	%smells = map { $_->[0] => $_->[1] } @smells;
	is( @smells, 6,
	    "Query with group by should return 6 values - via ->select" );
	is( $smells{a}, 2,
	    "Check count of smell = 'a' - via ->select" );
	is( $smells{b}, 1,
	    "Check count of smell = 'b' - via ->select" );
	is( $smells{c}, 1,
	    "Check count of smell = 'c' - via ->select" );
	is( $smells{awful}, 1,
	    "Check count of smell = 'awful' - via ->select" );
	is( $smells{good}, 1,
	    "Check count of smell = 'good' - via ->select" );
	is( $smells{horrid}, 1,
	    "Check count of smell = 'horrid' - via ->select" );
    }

    @rows = $emp_t->function( select => $emp_t->column('smell'),
			      where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ],
			      order_by => $emp_t->column('smell') );
    is( @rows, 4,
	"There should only be four rows which have a single character smell" );
    is( $rows[0], 'a',
	"First smell should be 'a'" );
    is( $rows[1], 'a',
	"Second smell should be 'a'" );
    is( $rows[2], 'b',
	"Third smell should be 'b'" );
    is( $rows[3], 'c',
	"Fourth smell should be 'c'" );

    {
	my $statement = $emp_t->select( select => $emp_t->column('smell'),
					where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ],
					order_by => $emp_t->column('smell') );
	my @rows = $statement->all_rows;

	is( @rows, 4,
	    "There should only be four rows which have a single character smell - via ->select" );
	is( $rows[0], 'a',
	    "First smell should be 'a' - via ->select" );
	is( $rows[1], 'a',
	    "Second smell should be 'a' - via ->select" );
	is( $rows[2], 'b',
	    "Third smell should be 'b' - via ->select" );
	is( $rows[3], 'c',
	    "Fourth smell should be 'c' - via ->select" );
    }

    @rows = $emp_t->function( select => $emp_t->column('smell'),
			      where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ],
			      order_by => $emp_t->column('smell'),
			      limit => 2,
			    );
    is( @rows, 2,
	"There should only be two rows which have a single character smell - with limit" );
    is( $rows[0], 'a',
	"First smell should be 'a' - with limit" );
    is( $rows[1], 'a',
	"Second smell should be 'a' - with limit" );

    {
	my $statement = $emp_t->select( select => $emp_t->column('smell'),
					where => [ LENGTH( $emp_t->column('smell') ), '=', 1 ],
					order_by => $emp_t->column('smell'),
					limit => 2,
				      );
	my @rows = $statement->all_rows;

	is( @rows, 2,
	    "There should only be two rows which have a single character smell -  with limit via ->select" );
	is( $rows[0], 'a',
	    "First smell should be 'a' - with limit via ->select" );
	is( $rows[1], 'a',
	    "Second smell should be 'a' - with limit via ->select" );
    }

    my $extend_id = $proj{extend}->select('project_id');
    my $embrace_id = $proj{embrace}->select('project_id');
    foreach ( [ 9000, $extend_id ], [ 9000, $embrace_id ],
              [ 9001, $extend_id ], [ 9002, $extend_id ] )
    {
	$emp_proj_t->insert( values => { employee_id => $_->[0],
					 project_id => $_->[1] } );
    }

    # find staffed projects
    @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') );
    is( @rows, 2,
	"Only two projects should be returned from schema->function" );
    is( $rows[0][0], 'Embrace',
	"First project should be Embrace" );
    is( $rows[1][0], 'Extend',
	"Second project should be Extend" );
    is( $rows[0][1], 1,
	"First project should have 1 employee" );
    is( $rows[1][1], 3,
	"Second project should have 3 employees" );

    {
	my $statement = $s->select( select => [ $proj_t->column('name'),
						COUNT( $proj_t->column('name') ) ],
				    join   => [ $emp_proj_t, $proj_t ],
				    group_by => $proj_t->column('name') );
	my @rows = $statement->all_rows;

	is( @rows, 2,
	    "Only two projects should be returned from schema->select" );
	is( $rows[0][0], 'Embrace',
	    "First project should be Embrace - via ->select" );
	is( $rows[1][0], 'Extend',
	    "Second project should be Extend - via ->select" );
	is( $rows[0][1], 1,
	    "First project should have 1 employee - via ->select" );
	is( $rows[1][1], 3,
	    "Second project should have 3 employees - via ->select" );
    }

    @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'),
			  limit => [1, 1],
			);
    is( @rows, 1,
	"Only one projects should be returned from schema->function - with limit" );
    is( $rows[0][0], 'Extend',
	"First project should be Extend - with limit" );
    is( $rows[0][1], 3,
	"First project should have 3 employees - with limit" );

    {
	my $statement = $s->select( select => [ $proj_t->column('name'),
						COUNT( $proj_t->column('name') ) ],
				    join   => [ $emp_proj_t, $proj_t ],
				    group_by => $proj_t->column('name'),
				    limit => [1, 1],
				  );

	my @rows = $statement->all_rows;

	is( @rows, 1,
	    "Only one projects should be returned from schema->select - with limit via ->select" );
	is( $rows[0][0], 'Extend',
	    "First project should be Extend - with limit via ->select" );
	is( $rows[0][1], 3,
	    "First project should have 3 employees - with limit via ->select" );
    }

    {
	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' ] );

	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;
}