More API Moose
It still fix API day here in the Moose-pen
Yesterday I left off with a new attribute for may Database::Accessor and DAD, 'da_result_set' and I was just going to play with this in my Driver::DBI but before I go and do this I better change one test a little;
first I had this test near the end of my '20_dad_load.t' test case;
...
else {
my $dad = $da_new->result()->error;
ok($dad->is_ArrayRef ==1,"DAD is_ArrayRef is true")
}
It of course passes as I am just testing for the default value I should be really test to see if the value is effected by a change. To address this I adding in the following changes
my $da_new = Database::Accessor->new( { delete_requires_condition=>0,
update_requires_condition=>0,
++ da_result_set=>'HashRef',
view => { name => 'person' },
elements=>[{ name => 'street', view => 'person', }] } );
…
else {
my $dad = $da_new->result()->error;
-- ok($dad->is_ArrayRef ==1,"DAD is_ArrayRef is true")
++ ok($dad->is_HashRef ==1,"DAD is_HashRef is true")
}
and gave my test a go;
...
ok 62 - retrieve Query ran
not ok 63 - DAD is_HashRef is true
ok 64 - update Query ran
...
opps a fail there. As usual I forgot to pass that new attribute down to the underlying DAD and this little patch to the '_execute' sub should fix it;
da_suppress_view_name=> $self->da_suppress_view_name,
++ da_result_set => $self->da_result_set,
identity_index => $self->_identity_index
}
);
and it does
...
ok 62 - retrieve Query ran
ok 63 - DAD is_HashRef is true
ok 64 - update Query ran
...
so now onto Driver::DBI
Well the first thing we need is a test case and I have named this one '20_result_sets.t'. The first part of the test looks like this;
use Xtest::DB::Users;
use Xtest::DA::Person;
use Test::Deep qw(cmp_deeply);
my $user_db = Xtest::DB::Users->new();
my $dbh = $user_db->connect();
my $new_person = $user_db->new_person_data->[0];
my $person= Xtest::DA::Person->new();
my $da = $person->da();
$da->da_result_set('HashRef');
ok($da->is_HashRef ==1,"return set is a HashRef");
in this test case I do not have to do a '$user_db->create_db();' as that should have been run in the previous test case so all I do here is get the $da from a '$person' then set the 'da_result_set' attribute to 'HashRef' a quick test to see if that value took. When I run the test case I get
ok 1 - return set is a HashRef
which is a good start;
Next I add the following;
$da->add_condition({
left => {
name => 'user_id',
},
right => { value => $new_person->{user_id }},
operator => '=',
});
$da->retrieve($dbh);
cmp_deeply( $da->result()->set->[0], $new_person,"Hashref returned with correct data");
First a condition so I pick the correct person from the DB, then a retrieve to get the data from the DB and finally a check to see if the returned data matches up with the expected data. This will of course fail as I have not yet added this functionality to the Driver::DBI. Better go do that now.
For my first crack at it I tried
if ( $action eq Database::Accessor::Constants::RETRIEVE ) {
$sth->execute();
my $results;
if (!$self->is_ArrayRef()) {
while (my $hash_ref = $sth->fetchrow_hashref) {
push(@{$results},$hash_ref);
};
}
else {
$results = $sth->fetchall_arrayref();
}
$result->set($results);
}
You might notice I only have two logic paths here either it isn't an array-ref or is. Now the logic behind this is simple. If I am going to deal with 'HashRef', 'Class' or 'JSON' return types I will always need to use the DBI::STH::fetchrow_hashref as all three of these are really just variations on key value pairs.
So far I am just going to see what comets out with the above and on my first run I get;
not ok 2 - Hashref returned with correct data
# Failed test 'Hashref returned with correct data'
# at 20_result_sets.t line 33.
# Compared reftype($data)
# got : undef
# expect : 'HASH'
I got no data back from my $da and after a few mins of poking about I found the problem. Seems in my '10_crud_basic.t' test case the last thing I do is delete that $new_person row from the DB. Well I corrected that, won't bore you with the details on that.
On my first successful run I got this result;
not ok 2 - Hashref returned with correct data
# Failed test 'Hashref returned with correct data'
# at 20_result_sets.t line 33.
# Comparing hash keys of $data
# Missing: 'address_id', 'city', 'country', 'country_id', 'first_name', 'id', 'last_name', 'postal_code',
'region', 'region_id', 'street', 'time_zone', 'time_zone_id', 'user_id'
# Extra: 'ASSRESS_ID', 'CITY', 'COUNTRY', 'COUNTRY_ID', 'FIRST_NAME', 'ID',
'LAST_NAME', 'POSTAL_CODE', 'REGION', 'REGION_ID', 'STREET', 'TIME_ZONE_ID',
'USER_ID', 'time zone'
Looking at was returned in the set;
'set' => [
{
'ID' => '5',
'REGION' => 'NA',
'time zone' => 'CST',
'LAST_NAME' => 'Marceia',
'USER_ID' => 'marceiaj',
'CITY' => 'Ciudad de Mexico',
'COUNTRY_ID' => '3',
'FIRST_NAME' => 'Diego',
'COUNTRY' => 'Mexico',
'STREET' => 'Plaza de la Constitucion 2',
'POSTAL_CODE' => '06000',
'REGION_ID' => '21',
'TIME_ZONE_ID' => '2',
'ADDRESS_ID' => '6'
}
],
I see all the data are correct by the keys are upper case, except for 'time zone' which I handle it “” in the SQL so I guess ORACLE preserves the case. I think there is some DBI majic I can use here to get around this;
if (!$self->is_ArrayRef()) {
-- while (my $hash_ref = $sth->fetchrow_hashref()) {
++ while (my $hash_ref = $sth->fetchrow_hashref('NAME_lc')) {
push(@{$results},$hash_ref);
};
}
and sure enough my next run I get
not ok 2 - HashRef returned with correct data
# Failed test 'HashRef returned with correct data'
# at 20_result_sets.t line 33.
# Comparing hash keys of $data
# Missing: 'time_zone'
# Extra: 'time zone'
which is fixed by simply changing my default data a little and then I get this error
not ok 2 - HashRef returned with correct data
# Failed test 'HashRef returned with correct data'
# at 20_result_sets.t line 33.
# Compared $data->{"first_name"}
# got : 'Diego'
# expect : 'James'
again left over from '10_crud_basic.t' so I added in a quick patch for that as well
my $new_person = $user_db->new_person_data->[0];
++$new_person->{first_name} = 'Diego';
my $person= Xtest::DA::Person->new();
and now I get
ok 1 - return set is a HashRef
ok 2 - HashRef returned with correct data
Which is nice but now I have some things to think about? Should the lower/upper case for hash keys be something my Database::Accessor users can set to a default and what about mixed case, camel case or snake case?
Something for tomorrow I guess?
Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations
Leave a comment