byterock

Its a little harder day here in the Moose-Pen.

Carrying on with adding to the '32_params.t' case I though today I would tackle one of the harder parts of prams and that is passing an array of params or in my case containers into a query statement.

So in my test I could have this;

$container = [{first_name=>'Bill',last_name =>'Bloggings'},

              {first_name=>'Jane',last_name =>'Doe'},

              {first_name=>'John',last_name =>'Doe'},

              {first_name=>'Joe',last_name =>'Blow'},

              ];

and what I wold expect to happen on a create would be that all a new recoded would be added for each. In DBI has the 'execute_array' which works with 'bind_param_array' to effect an SQL action on such a set of tuples. DBI works on the above by either the default implimetaion of simple iteration over each other DBDs such ad DBD::Oracle have built in API for such actions.

As MongoDB (and other Key-pair Dbs) has a similar function 'updateMany' adding support for this is well with-in the scope of my project.

Next the test;

$expected  = [['Bill','Bloggings'],

              ['Jane','Doe'],

              ['John','Doe'],

              ['Joe','Blow'],

              ];

$da->create( $utils->connect(),$container);

ok($da->result()->query() eq "INSERT INTO people ( people.first_name, people.last_name ) VALUES( ?, ? )","Array create SQL correct");

for (my $index = 0; $index < $da->result()->param_count; $index++){

    cmp_deeply(

           $da->result()->params->[$index],

           $expected->[$index],

           "Array create tuple $index are correct"

          );

}

a simple check of the generated SQL and then iteration over my params to make sure they are all correct;

On my first run I get;

Not a HASH reference at GitHub\database-accessor-driver-dbi\lib/Database/Accessor/Driver/DBI.pm line 474.

and looking at the offending line I see

 foreach my $key ( sort(keys( %{$container} )) ) {

so I will have to add in a if case before that to handle the situation for an array bind on a create like this;

if (ref($container) eq "ARRAY"){
      $self->is_exe_array(1);
      my $fields = $container->[0];
      
      foreach my $key (sort(keys( %{$fields} )) ) {
        my $field = $self->get_element_by_name( $key);
        next
         if(!$field);
        push(@fields,$field);

        push(@field_sql, $self->_element_sql($field));

        

      }

      foreach my $tuple (@{$container}){

         my @params = ();

         foreach my $field (@fields){

           my $param =  Database::Accessor::Param->new({value=> $tuple->{$field->name()}});

           

           push(@params,$param);

         }

         $self->add_param(\@params);

       }

       my $params = $self->params();

      

    }

    else {

      foreach my $key ( sort(keys( %{$container} )) ) {

In the above you will notice I added a new attribute 'is_exe_array' which is just;

 has is_exe_array => (

            is  => 'rw',

            isa => 'Bool',

            default     => 0,

     );

which for now is local to Driver ::DBI. I will use this later on as I have two new paths for the code 'bind_param_array' and then 'execute_array'.

Next I want to know which fields I am binding wich I get off the first row of the container, and then using that I set up the fields array, the same way I would for a normal bind. Next I will need to compile the 'tuples' with another simple iteration and then just fall back into the regular code stream.

The second part happens in the 'exexcute' sub where I use that 'is_exe_array' flag here

...

   if ($self->is_exe_array()){

      my $params = $self->params();

      foreach my $tuple (@{$params}){

         my @tuple = map({$_->value} @{$tuple} );

         $result->add_param(\@tuple);

      }

    }

    else {

      $result->params([map({$_->value} @{$self->params} )]);

    }

...

to set up the results params to be a set tuples and then a little further along I use the flag again;

...

       foreach my $index (1..$self->param_count()){

          if ($self->is_exe_array()){

            $sth->bind_param_array($index,$result->params()->[$index-1]);

          }

          else {

...

in the bind. Finally I use the flag one last time here;

... 

else {

           my $rows_effected;

           if ($self->is_exe_array()){

             my @tuple_status;

             $sth->execute_array( { ArrayTupleStatus => \@tuple_status } );

             $rows_effected = scalar(@tuple_status);

             $result->set(\@tuple_status);

           }

           else {

             $rows_effected = $sth->execute();

           }

           $result->effected($rows_effected);

        }

when I do the execute_array and then return via the 'set' attribute on the 'result' class the status of the various tuples;

And If at this point I run my tests I get;

ok 4 - delete params in correct order

ok 5 - Array create tuple 0 are correct

ok 6 - Array create tuple 1 are correct

ok 7 - Array create tuple 2 are correct

ok 8 - Array create tuple 3 are correct

all great and good but I have the 'da_compose_only' flag set so I never actually get to the execute part of my code. So really I am only testing half of what I should.

Oh well something for tomorrow's post.