Never Ending API Moose

Even more API fun day here in the Moose-Pen

When I left off yesterday I had everything working nicely but I was left with a little problem;

What should I do about the case of hash-keys returned from the DB?

Now in DBI I have three choices

  • NAME
    • What ever comes out of the driver, could be upper, or lower or mixed depending on the query, driver and DB.
  • NAME_lc and
    • all keys in lower case
  • NAME_uc
    • all keys in upper case

while in most other NON SQL dbs I really do not have any choice. Though it seems in Mongo they always want you to use lower case for field names though you can used any case you want, while RethinkDB, at least when I used it some time ago, it only allowd lower case field names.

The real rub on the SQL side of things is that some DBs such as Oracle will let you preserve case when creating a field name while others will not. As well some DBD drivers do not even implement the convention above. What to do?

The best I can do is a comprimise,


  1. The native results from the DB

  2. Always upper and

  3. Allways lower.

So back to Database::Accessor I go;

First I need some tests and back to good old '20_dad_load.t' and add in a repeat from the other day;


my %read_write = (da_compose_only=>1,
da_no_effect=>1,
da_raise_error_off=>1,
da_warning=>1,
da_suppress_view_name=>1,
da_result_set=>1,
++ da_key_case=>1,
);
...
ok( $da->da_key_case() eq 'Lower', "Key Case is Lower" );
ok( $da->is_Lower() == 1, "is_Lower is true" );
ok( $da->is_Native() == 0, "is_Native is false" );
ok( $da->is_Upper() == 0, "is_Upper is false" );
...
my $da_new = Database::Accessor->new( { delete_requires_condition=>0,
update_requires_condition=>0,
da_result_set=>'HashRef',
++ da_key_case=>'Upper',
view => { name => 'person' },
elements=>[{ name => 'street', view => 'person', }] } );
...
else {
my $dad = $da_new->result()->error;
ok($dad->is_HashRef ==1,"DAD is_HashRef is true");
++ ok($dad->is_Upper ==1,"DAD is_is_Upper is true");
}

Now to add in my new attribure, I went with another enumeration and the name da_key_case,

has da_key_case => (
traits => ["Enumeration"],
is => "rw",
enum => [qw/ Native Lower Upper /],
handles => 1,
default => 'Lower'
);

and this time I did not forget to pass that value to the DAD in the '_execute' sub;

...
da_suppress_view_name=> $self->da_suppress_view_name,
da_result_set => $self->da_result_set,
++ da_key_case => $self->da_key_case,
identity_index => $self->_identity_index
}

...


and tankfully they all pass so back to Driver::DBI I go.

Right now no need to write up a new test as I what I have now tests the defualt case (NAME_lc) so all I need to do is make the code changes;

First I will start with a few new constants that I will tack into 'Database::Accessor::Driver::DBI::SQL';


use constant SET =>'SET';
use constant NAME =>'NAME';
use constant NAME_LC =>'NAME_lc';
use constant NAME_UC =>'NAME_uc';

use constant DA_KEY_CASE => {
Native => Database::Accessor::Driver::DBI::SQL::NAME,
Lower => Database::Accessor::Driver::DBI::SQL::NAME_LC,
Upper => Database::Accessor::Driver::DBI::SQL::NAME_UC
};


and I think this very simple change is all I need to do in Driver::DBI;

if ( $action eq Database::Accessor::Constants::RETRIEVE ) {
$sth->execute();
my $results;
if (!$self->is_ArrayRef()) {
-- while (my $hash_ref = $sth->fetchrow_hashref('NAME_lc')) {
++ while (my $hash_ref = $sth->fetchrow_hashref(
++ Database::Accessor::Driver::DBI::SQL::DA_KEY_CASE->{$self->da_key_case})) {
push(@{$results},$hash_ref);
};
}
else {
$results = $sth->fetchall_arrayref();
}
$result->set($results);

and when I run it I get;

ok 2 - HashRef returned with correct data

Now for the next test;

$da->da_key_case('Upper');
ok($da->is_Upper ==1,"return Key case is Upper");
$da->retrieve($dbh);
cmp_deeply( $da->result()->set->[0], $user_db->new_person_data->[1]"HashRef returned All Keys Upper case");

Teally just a simple change that 'da_key_case' attribure, then a check on it, do a retreive and then check the results. I did add in an new row to the 'new_person_data' array ref with all upper-case keys and a corrected first name and on my first run I get;

ok 1 - return set is a HashRef
ok 2 - HashRef returned with correct data
ok 3 - return Key case is Upper
ok 4 - HashRef returned All Keys Upper case

so that is all good now for the final test;

In this case I really do not know what the DB is going to though back at me I can only guess and it may vary by DB but for how I defined things in Oracle all the fields should be Upper case excpet the ones with an alias so keeping that in mind I added in one more row to ' new_person_data' and these tests;


...
$da->da_key_case('Native');
ok($da->is_Native ==1,"return Key case is Native");
$da->retrieve($dbh);
cmp_deeply( $da->result()->set->[0], $user_db->new_person_data->[2],
"HashRef returned All Keys Native case");

and when I ran it I got

not ok 6 - HashRef returned All Keys Native case
# Failed test 'HashRef returned All Keys Native case'
# at 20_result_sets.t line 47.
# Comparing hash keys of $data
# Missing: 'address_id', 'country', 'region'
# Extra: 'ADDRESS_ID', 'COUNTRY', 'REGION'

so time zone worked but none of the other fields with an alias and the only differance on the SQL;

time_zone.description "time zone"

are those quotes around the alias. I think I can fix that quickly as well with this little patch to the '_fields_sql' sub;

...
if ( $field->alias() ) {
my $alias = $field->alias();
$alias = '"' . $alias . '"'
-- if ( index( $alias, " " ) != -1 );
++ if ( index( $alias, " " ) != -1 or $self->is_Native);

$sql .= join( " ", "", $alias );
};


and on my run I get

...
ok 5 - return Key case is Native
ok 6 - HashRef returned All Keys Native case

so that was an easy one to end the day on.


HPIM1623a.jpg

Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations