First things first, let's just say this module is a clean bare layer over DBI whereas DBIx::Simple has much more object-oriented layering over DBI. Attempting to make a DBIx::Simple instance a component of a Moose/Moo class failed whereas you can see it works just fine in DBIx::Array.
Now, DBIx::Simple leads to prettier code, compare:
and that is because DBIx::Simple is more complex in terms of how it is built - Juerd tooks pains to build extras classes for results of queries and he created extra methods to delegate to SQL::Abstract, and SQL::Interp. I was forced to build my own convenience interfaces to ::Abstract (I chose SQL::Abstract::More) and ::Interp. But they fit right into the DBIx::Array API without a hitch because all of the SQL builders tend to produce two things - placeheld SQL and a list of bindvariables.# DBIx::Array sub single_row_list { my ($my) = @_; my %where = ( film_id => 5 ); my ( $title, $desc ) = $my->da->sqlarray( $my->abstract->select( film => [qw(title description)], \%wher +e ) ); warn "($title, $desc)"; } # DBIx::Simple sub single_row_list { my ($my) = @_; my %where = ( film_id => 5 ); my ( $title, $desc ) = $simple->select( film => [qw(title description)], \%where ) )->l +ist; }
But what good is cleaner code if you end up with circular references when you try to use DBIx::Simple in a simple has-a relationship?
method naming
methods to count rows affected
One drawback to the DBIx::Array API is that the class has three methods - insert, update, and delete that all simply take SQL and return the count of rows affected. There should have been one method named count instead. Compare:
The delete-DELETE in the current API is redundant. And they all call update() anyway so there is no need for 3 separate names.# current API my $count =$dbx->delete( "DELETE from table WHERE id > 22" ); # ideal API my $count =$dbx->count( "DELETE from table WHERE id > 22" );
sensible methods to retrieve common things
Every common thing that you could want in bulk (as opposed to fetching row-by-row), you can get at easily in DBIx::Array, but he probably should've tossed in some convenience methods to make things even more ultra-explicit. Let's be surgically technical about what a database is, what it returns, and how you can get that in Perl:What a database returns
A relational database is composed of TABLES which have ROWS which are composed of FIELDS. Retrieving data from such a SQL query returns exactly one of the following:- FIELD
- COLUMN
- ROW
- ROWS
what data structures do we have in Perl to hold these results??
Now, the things in Perl that we have to hold data are- SCALAR
- ARRAY
- HASH
What is the logical API for a DBI wrapper???
Well, from the above two sections it should now be obvious that a sane, clean DBI wrapper, would have methods based on- What a database can return from a query... call it $RESULT
- What data structure we want to hold it in ... call it $STRUCT
And that is exactly what DBIx::Array gives us. A complete mapping from possible results to possible perl types to hold them. Now there are some places where the API is a bit more implicit and explicit and that is where I added a few methods in my suggested changes to the API. Bur with no further ado, here is the table:$object->sql$RESULT$STRUCT
rdbms result | result description | perl result holder | query to create such a result | my API call | current API call |
---|---|---|---|---|---|
FIELD | a single column and single row | scalar | SELECT title FROM film WHERE film_id = 3 | sqlfield | sqlscalar |
COLUMN | a list of a single field | list | SELECT title FROM film WHERE film_id BETWEEN 2 and 22 | sqlcolumn | sqlarray |
ROW | a single row | list or hash | SELECT * FROM film WHERE film_id =3 | sqlrow or sqlrowhash | sqlarray or sqlarrayhash |
ROWS | a list of rows | LoA or LoH | SELECT * FROM film WHERE film_id BETWEEN 2 and 22 | sqlarrayarray or sqlarrayhash | sqlarrayarray or sqlarrayhash |
Is the word "sql" needed
Because DBIx::Array is objectoriented, all of its methods reside in its own namespace. Is prefixing each method with "sql" a good thing. If I had been genius enough to develop this interface, and ultra-anal enough to never think your brain was wired to think like Perl, I might've just rigidly named all the methods $RESULT_$STRUCT, leading to things likeSo thank God that Michael Davis got around to doing this before me (grin)# FIELD FROM DB to PERL SCALAR my $scalar = $da->field_scalar; # COLUMN FROM DB TO PERL ARRAY my @column = $da->column_array # ROW FROM DB TO PERL ARRAY my @row = $da->row_array; # ROW FROM DB TO PERL HASH my %row = $da->row_hash; # ROWS FROM DB to PERL ARRAY OF ARRAYS my @rows = $da->rows_arrayofarrays; # ROWS FROM DB to PERL ARRAY OF HASHREFS my @rows = $da->rows_arraysofhash;
segue-way to DBIx::Class
One thing that was never clear to me was how to get my data out a DBIx::Class resultset. I think if I had created a table such as the one in this post, someone could've helped me see the mapping between DBMS results, DBIx::Class resultsets, and Perl data structures. Although most of the time, when I asked about data structures I was rebuked with comments along the line that DBIx::Class was an OBJECT relational mapper.Well, I'm glad a Perl-relational mapper has finally hit CPAN.
runnable code
When I talk about DBIx::Array, I'm not speaking from armchair scanning of docs. I have used it against the sakila database and you can too. Simply download and install DBIx::Cookbook and then you can run all the examples below:use strict; use warnings; use Moose; use Data::Dumper; use SQL::Interp qw(:all); use lib 'c:/Users/thequietcenter/prg/dbix-cookbook/lib'; *DBIx::Array::do = \&DBIx::Array::update; *DBIx::Array::sqlfield = \&DBIx::Array::sqlscalar; *DBIx::Array::sqlcolumn = \&DBIx::Array::sqlarray; *DBIx::Array::sqlrow = \&DBIx::Array::sqlarray; sub DBIx::Array::interp { my ( $da, @param ) = @_; $da->do( sql_interp(@param) ); } sub DBIx::Array::sqlrowhash { my ( $da, @arg ) = @_; my @data = $da->sqlarrayhash(@arg); $data[0]; } has 'abstract' => ( is => 'rw', default => sub { use SQL::Abstract::More; SQL::Abstract::More->new; } ); has 'da' => ( is => 'rw', default => sub { use DBIx::Cookbook::DBH; my $dbh = DBIx::Cookbook::DBH->new; use DBIx::Array; my $da = DBIx::Array->new; $da->connect( $dbh->for_dbi ); $da; } ); sub interp { my ( $self, @arg ) = @_; sql_interp(@arg); } sub dump { my ( $my, @arg ) = @_; warn Dumper(@arg); } sub main { my ($func) = @_; my $o = __PACKAGE__->new; warn "$o"; use DBI; DBI->trace(1); $o->$func; } main::main(@ARGV) unless caller; sub add_lang { my ($my) = @_; for ( 1 .. 10 ) { $my->da->do( $my->abstract->insert( language => { name => "language$_" +} ) ); warn $_; } } sub trim_lang { my ($my) = @_; for ( 1 .. 10 ) { my %where = ( language_id => { '>', 13 } ); $my->da->do( $my->abstract->delete( language => \%where ) ); warn $_; } } sub single_row_scalar { my ($my) = @_; my %where = ( language_id => 5 ); my $val = $my->da->sqlscalar( $my->abstract->select( language => 'name', \%where ) ); warn $val; } sub single_row_list { my ($my) = @_; my %where = ( film_id => 5 ); my ( $title, $desc ) = $my->da->sqlarray( $my->abstract->select( film => [qw(title description)], \%wher +e ) ); warn "($title, $desc)"; } # single column sub single_column { my ($my) = @_; my @country = $my->da->sqlcolumn( $my->abstract->select( country => 'country' +) ); warn Dumper( \@country ); } sub single_row { my ($my) = @_; my @data = $my->da->sqlrow('SELECT * FROM city WHERE city_id = 4') +; warn Dumper( \@data ); } sub single_row_hashref { my ($my) = @_; # my @data = $my->da->sqlarrayhash('SELECT * FROM city WHERE city_ +id = 4'); # warn Dumper( \@data ); my $data = $my->da->sqlrowhash('SELECT * FROM city WHERE city_id = + 4'); warn Dumper($data); } sub fetch_all_aref { my ($my) = @_; my %where = ( address_id => { '>', 600 } ); my @data = $my->da->sqlarrayarray( $my->abstract->select( address => [qw(address district)], \%wh +ere ) ); warn Dumper( \@data ); } sub fetch_all_href { my ($my) = @_; my %where = ( address_id => { '>', 600 } ); my @data = $my->da->sqlarrayhash( $my->abstract->select( address => [qw(address district)], \%wh +ere ) ); warn Dumper( \@data ); } sub interp_examples { my ($my) = @_; my %data = ( title => 'perl programming wars' . rand(23423), description => 'epic drama of perl scripting', language_id => 1 ); my ( $sql, @bind ) = sql_interp( 'INSERT INTO film', \%data ); warn Dumper( $sql, \@bind ); $my->interp( 'INSERT INTO film', \%data ); $my->interp( 'UPDATE staff SET', { first_name => 'Bob' }, 'WHERE', { last_name => 'Stephens' } ); $my->interp( 'DELETE FROM language WHERE language_id >', \7 ); my $district = 'Okayama'; my @in = qw(547 376); $my->da->do( $my->interp( "SELECT * FROM address WHERE district =", \$district, "AND city_id IN", \@in ) ); $my->da->do( $my->interp( "SELECT * FROM address WHERE", { district => $district, city_id => \@in } ) ); } sub sql_hash { my ($my) = @_; my $sql = "SELECT city_id, city FROM city LIMIT 5"; # my @rows = $da->_sqlarrayarray( sql => $sql, param => [], name = +> 0 ); # die Dumper( \@rows ); my $hash = $my->da->sqlhash($sql); $my->dump($hash); } sub sql_array_array { my ($my) = @_; my $sql = "SELECT city_id, city FROM city LIMIT 5"; my $data = $my->da->sqlarrayarray($sql); $my->dump($data); } sub sql_array_array_name { my ($my) = @_; my $sql = "SELECT city_id, city FROM city LIMIT 5"; my $data = $my->da->sqlarrayarrayname($sql); $my->dump($data); } sub sql_cursor { my ( $da, $abstract ) = @_; warn "DA:$da:"; my $sql = "SELECT city_id, city FROM city LIMIT 5"; my $sth = $da->sqlcursor($sql); die Dumper( $sql, $sth ); my $hash = $da->sqlhash($sql); warn Dumper($hash); } 1;
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: Algebraic data retrieval with DBIx::Array
by Juerd (Abbot) on Sep 14, 2011 at 00:48 UTC |