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

}
[download]

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" );
[download]

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
2. COLUMN
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

1. SCALAR
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

$object->sql$RESULT$STRUCT
[download]

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 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 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;
[download]

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;
[download]