byterock

Well some Driver::DBI code today in the Moose-Pen.

Carrying on from my last Post where I had created the new sub 'raw_query' and had it testing nicely I started on my merry way to program the Driver::DBI module. Well I did not get very far along.

I started out with creating a get_sql sub that the 'raw_query' would call, but then quickly realized that that would be a little redundant as any time I needed just the SQL I could just call the 'raw_query' and save a sub. So I came up with this.

sub raw_query {

   my $self = shift;

   my ($type) = @_;

   my $sql;

   

   if ( $type eq Database::Accessor::Constants::CREATE ) {

        $sql = $self->_insert();

   }

    elsif ( $type eq Database::Accessor::Constants::UPDATE ) {

        $sql = $self->_update();

    }

    elsif ( $type eq Database::Accessor::Constants::DELETE ) {

        $sql = $self->_delete();

    }

    else {

        $sql = $self->_select_clause();

    }


   return $sql;

}

That stated to look fine until I started writing the '_insert' sub and before I got too far along I saw that it is impossible to do an insert into a table without some data. My SQL returned from the above if I called it from the test class from 10_crud_basic.t would at best be

   INSERT INTO user (username,address) VALUES(?,?);

as if do have I fields handy in the 'Elements' array that is loaded in via the Accessor.pm but I had nothing to insert. The SQL is of course valid SQL but I have to make the assumption that the user wants to do a full insert on all the Elements described in the Test::DB::User class and that the ?,? are in place for two parameters that have yet to be defined. That is not what I want. I need to be much more flexible so I can let the tell the Driver::DBI what fields are to be used in the CRUD operations.

So what I need to add in is same sort of $container I use on the execute sub so I tell the Driver::DBI how to set up the query. So I added that into the above

--my ($type) = @_;

++my ($type,$container) = @_;

…

--   $sql = $self->_insert();

++   $sql = $self->_insert($container);

and that of course led me right back to the Accessor.pm.

The question now is what is the '$container' going to be? I know from my older Data::Accessor system I would pass in a hash-ref and the code would then take that and attempt to align it with the 'elements' that are present on the DA and create the SQL from there.

Now it would be very nice if I could make this new system work with a Class and even sometimes an Array-ref so I will add that in as well. I know for a fact some flavours of SQL and MongoDB for sure lets you do muti-line queries and an Array-Ref container would facilitate this.

Looking at each of the four CRUD functions in detail on sees that 'CREATE' is the only one that should be able to process a single hash-ref or class or and array-ref of the same depending of course on the underlying DB. RETRIVE should be able to take an array-ref of 'Element' names to limit what fields are returned. 'UPDATE' would accept just a single hash-ref or class and finally 'DELETE' doesn't event need a container So I will have to build that into Accessor.pm.

Now the next problem. To make the code nice and tight I have do some sanity checking on the $container that is passed in the four CRUD methods and the raw_query method. To do this I really should just have 1 function that is called from all five subs.

Oh well even more code to add into Accessor.pm for tomorrow post. I think I will be going back and forth quite a lot over the next little while.