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