Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Algebraic data retrieval with DBIx::Array

by metaperl (Curate)
on Sep 01, 2011 at 17:36 UTC ( #923676=perlmeditation: print w/replies, xml ) Need Help??

DBIx::Array is a new DBI wrapper. This evaluation will be written from the standpoint of someone who is a long-time DBIx::Simple user. If you'd rather see code than read opinions, then read away

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:

# 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; }
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.

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:

# current API my $count =$dbx->delete( "DELETE from table WHERE id > 22" ); # ideal API my $count =$dbx->count( "DELETE from table WHERE id > 22" );
The delete-DELETE in the current API is redundant. And they all call update() anyway so there is no need for 3 separate names.

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:
  1. FIELD
  3. ROW
  4. 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
  2. ARRAY
  3. HASH
and some nestings of the above.

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
  1. What a database can return from a query... call it $RESULT
  2. What data structure we want to hold it in ... call it $STRUCT
So the methods would look something like
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:
rdbms resultresult descriptionperl result holderquery to create such a resultmy API callcurrent API call
FIELDa single column and single rowscalarSELECT title FROM film WHERE film_id = 3sqlfieldsqlscalar
COLUMNa list of a single fieldlistSELECT title FROM film WHERE film_id BETWEEN 2 and 22sqlcolumnsqlarray
ROWa single rowlist or hashSELECT * FROM film WHERE film_id =3 sqlrow or sqlrowhashsqlarray or sqlarrayhash
ROWSa list of rowsLoA or LoHSELECT * FROM film WHERE film_id BETWEEN 2 and 22sqlarrayarray or sqlarrayhashsqlarrayarray 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 like
# 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;
So thank God that Michael Davis got around to doing this before me (grin)

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
    Sigh. I had overlooked this post before but I do feel that it needs a reply to set a few things straight for other people who might stumble upon this thread.

    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?

    The thing you complained about wasn't a has-a relationship, but a method that creates and returns a DBIx::Simple object, called in a chain. As you've previously demonstrated, this causes premature destruction. (One of the possible work-arounds is "don't do that, then", i.e. don't chain.) Even then, the way you used DBIx::Simple doesn't cause a circular reference. DBIx::Simple specifically avoids creating circular references; the "fix" that you suggested, however, would have created circular references. That's one reason that your suggested change was rejected.

    A has-a works perfectly with DBIx::Simple and Moose. It does mean that the D::S object will remain in use as long as your Moose object is, but that's what has-a's are for, of course... :)

    package Argh; use Moose; use DBIx::Simple; has 'db' => ( is => 'rw', isa => 'DBIx::Simple', lazy => 1, default => sub { DBIx::Simple->connect("dbi:SQLite::memory:") }, ); sub meh { my ($self) = @_; my $db = $self->db; say $db->query("SELECT 2 - 1")->list; say $self->db->query("SELECT 1 + 1")->list; } 1;

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://923676]
Front-paged by Arunbear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (8)
As of 2020-09-29 10:18 GMT
Find Nodes?
    Voting Booth?
    If at first I donít succeed, I Ö

    Results (146 votes). Check out past polls.