byterock

It still expand Test day here in the Moose-Pen.

Carrying on with my extended 'xt' tests or as I like to call them practical test. I wanted to make sure that I can change the conditions on my Database::Accessor and and check that all the DB is correct in the 'person' table.

To accomplish this I added in the following;

$test_data = $user_db->people_data;

$da->reset_conditions();

$da->add_sort({name=>'id'});

$da->retrieve($dbh);

cmp_deeply( $da->result()->set, $test_data,

            "All Persons result correct");

the first line above should get all the expected data to be found in the DB, the next should reset the current conditions back to empty, next I added in a sort so that both lists are in the same order or my compare will not work. Finally I do the 'retrieve' and then a cmp_deeply on the expected and returned results.

On my first run of the above I did get this error;

not ok 8 - Return results correct

#   Failed test 'Return results correct'

#   at 10_crud_basic.t line 84.

# Compared $data->[0][3]

#    got : 'masterb '

# expect : 'masterb'

in this case it is an error in my expected data as the DB schema has that field 'CHAR(8)' which according to DB it will return a space padded string for that type of field. Now DBI does have 'ChopBlanks' option which I might be able to use here and it does work as when I added this in;

$da->add_sort({name=>'id'});

$dbh->{ChopBlanks} = 1;

$da->retrieve($dbh);

and my test passed so that is one way to lick that problem. However I think I am better of just corrected my expected data rather that the DBI trick so out that goes and in goes a few spaces in my expected data array.

My next test was to see if my $da bleed into each or share some sort of common code under the hood someplace as I have encountered this before when using base classes in perl. Fortunately we are dealing with Moose so I am very confident that this test should pass;

my $persons = Xtest::DA::People->new();

my $persons_da = $persons->da();

$persons_da->add_sort({name=>'user_id'});

$test_data = $user_db->persons_data;

$persons_da->retrieve($dbh);

cmp_deeply( $persons_da->result()->set, $test_data,

            "People results correct");

in the above test I create a new $persons_da and I see that it dose not have a problem handling the above as I get a pass on this one as well;

Next I want to do a quick 'update' tests so I added in a little more default data into the 'Xtest::DB::Users' class and added this in;

$da->add_condition({

                left => {

                    name => 'user_id',

                },

                right     => { value => $new_person->{user_id }},

                operator  => '=',

            });


$da->update($dbh,$user_db->update_person_data);

$da->retrieve($dbh);

cmp_deeply( $da->result()->set->[0], $user_db->updated_person_data,

            "Update person results correct");

and I got this result;

not ok 10 - Update person results correct
# Failed test 'Update person results correct'
# at 10_crud_basic.t line 112.
# Compared $data->[1]
# got : 'James'
# expect : 'Diego'

so the update did not take and looking at 'result' for the update I see I have an SQL error in there;

UPDATE people SET first_name = ? 

     LEFT JOIN people_address ON people.id = people_address.people_id 

…

somehow the join is now part of the update and is should not be. I swear I had a test for this as well and looking at one such test;

 update => {

            container => $container,

            sql =>

"UPDATE people SET first_name = ?, last_name = ? 

  LEFT JOIN address ON people.id = address.user_id RIGHT JOIN phone ON people.phone_id = ? WHERE people.first_name = ?",

            params => [ 'Bill', 'Bloggings', '1234567890', 'test1' ]

        },

I don't see anyway that the SQL is correct for the above; I must have been smoking something very odd when I created these tests. Thank goodness for practical testing.

Well looking at the Oracle SQL manual again it seems you cannot do an SQL query like the above and it seems it is only valid on a few DBs so I think I will just drop the Link/Join from that part of the API. Looking at the rather obtuse SQL that is being used it does creep out of the scope of Database::Accessors and even ORMs and is really just from specific SQL programming.

The change for that one was in the 'execute' sub of Driver::DBI and the patch is;

 if ( $action eq Database::Accessor::Constants::CREATE ) {


        $sql = $self->_insert($container);

    }

    elsif ( $action eq Database::Accessor::Constants::UPDATE ) {

        $sql = $self->_update($container);

–      $sql .= $self->_join_clause();

        $sql .= $self->_where_clause();

    }

    elsif ( $action eq Database::Accessor::Constants::DELETE ) {

        $sql = $self->_delete();

--      $sql .= $self->_join_clause();

        $sql .= $self->_where_clause();

    }

I also took the liberty of fixing the DELETE action as well since the join syntax there is just as obtuse as in the update.

After that fix I get

...

ok 10 - Update person results correct

and now add in the delete;

$da->delete($dbh);

$da->retrieve($dbh);

cmp_deeply( $da->result()->set, [],

            "Delete person results correct");

and a quick run;

...

ok 9 - People results correct

ok 10 - Update person results correct

ok 11 - Delete person results correct

Things are looking good for tomorrow.

TTFN.