Baby Gets Going

Finanlly a DBI codeing day here in the Moose-Pen

So after yesterday's little review I finally got to do some coding on Driver::DBI and the first thing I got working was my '00_load.t' test case. All I needed to do with the present sate of the code is add in;

my $in_hash = { 
++                           da_compose_only=>1,
                view => { name  => 'name' }};
 
to that test case and the error I was getting from DBI;

BD::ExampleP::db prepare failed: Syntax error in select statement ("1") at 
 
went away. Now the error was caused by this sub in Driver::DBI

sub _select {
    my $self = shift;
    return 1;
}
 
which is just a stub in for now until I get more code written. Really all this test was suppose to prove was that DBI, and Database::Accessor where loaded and the Accessor could find and load in the Database::Accesor::Driver::DBI code. Now before I started on the next tests case I noticed from yesterday this call

 $self->da_warn("SQL=$sql")
      if $self->da_warning();
 
and what I decided to do was save my DAD writers a little more typing by moving that back into the Database::Accessor::Roles::Driver class with this change;

--      requires 'execute';

 ++     sub da_warn {
 ++          my $self       = shift;
 ++          my ($package, $filename, $line, $sub) = caller;
 ++         my ($message) =  @_;
 ++          warn("$package->$sub(), line:$line, message=$message");
           
 ++      }
 
and as I was in Accessor.pm anyway I changed da_warning into an integer so one can throttle the waring level;

       has [
        qw(da_compose_only
           da_no_effect
 --          da_warning
          )
 ...
++     has da_warning => (
++        is  => 'rw',
++        isa => 'Int',
++        default     => 0,
++          traits => ['ENV'],
        );
 

and now back into Driver::DBI and getting '10_crud_basic.t' to work;

As we saw in this post I am going to use the rather limited DBD::DBM in these basic tests because I know it should be present on any system that has DBI installed; so my first test is the rather simple,

my $user = Test::DB::User->new();
my $container =  {username=>'user_new',
                  address =>'address_new'};

eval{
   $user->create($utils->connect(),
                 $container);
};

if ($@) {
    fail("Create function error=$@");
}
else {
    pass("Create function");
}
 
in my DBI::Driver code I first want to create the end SQL something like this

INSERT INTO user (username,address) (?,?);
 
then bind those two ?s to the corresponding values coming in on the $container; easy enough; Not to bore you with the rather numerous iterations of code I needed before I got something working this is what I came up with today

sub _insert {
    my $self             = shift;
    my ($container)      = @_;
    my @fields           = ();
    my @values           = ();
    my @fields_to_insert = $self->elements();
    my $insert_clause    = join(" ",Database::Accessor::Driver::DBI::SQL::INSERT,Database::Accessor::Driver::DBI::SQL::INTO, $self->view()->name());
    
    $self->da_warn("_insert","Insert clause='$insert_clause'")
      if $self->da_warning()>=5;
    foreach my $key ( keys( %{$container} ) ) {
        my $field = $self->get_element_by_name(sub {$_->name eq $key});
        push(@fields,$field->name);
        my $param =  Database::Accessor::Param->new({value=> $container->{$key}});
        push(@values,$param->value());
    }
    my $fields_clause = Database::Accessor::Driver::DBI::SQL::OPEN_PARENS
                        .join(",",@fields)
                        .Database::Accessor::Driver::DBI::SQL::CLOSE_PARENS;
   
    $self->da_warn("_insert"," Fields clause='$fields_clause'")
      if $self->da_warning()>=5;
    my $values_clause = Database::Accessor::Driver::DBI::SQL::VALUES
                        .Database::Accessor::Driver::DBI::SQL::OPEN_PARENS
                        .join(",",
                              map(Database::Accessor::Driver::DBI::SQL::PARAM,@fields)
                             )
                        .Database::Accessor::Driver::DBI::SQL::CLOSE_PARENS;
    $self->da_warn("_insert"," Values clause='$values_clause'")
      if $self->da_warning()>=5;
    return join(" ",$insert_clause,$fields_clause,$values_clause);
}
 
I first created two arrays to hold the filelds I will be inserting and what values I will be inserting. Next I created my $insert clause and then a warning at level 5 for that, and then I iterate over each of the keys in the container and as should have one field for each key in the container (part of my promise rules) I look up that field with

  my $field = $self->get_element_by_name(sub {$_->name eq $key});
now that is something new I added that in to Accessor.pm here

  has elements => (
            isa => 'ArrayRefofElements',
            is  => 'ro',
            traits  => ['Array'],
            handles => { element_count => 'count',
 ++                      get_element_by_name  => 'first',
                       },
            default => sub { [] },
        );
 
It is one of the Native Traits 'first' to use it I must, supply a sub and it will find the first matching element for me. Next I set up the param and add its value into the @values. Once I am done iterating I create my '$fields_clause' and '$values_clause' with a combination of constants, joins and a map and at the same time I added in a level five waring for each. At the end I simply return a join of the three clause which give me

INSERT INTO user (address,username) VALUES(?,?) 
 
back into the 'sub execute'

That is is for today as I see there are a few little re-factoring I can do to the above to improve things early rather than later.

IMG_5552a.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