is a new DBI wrapper. This evaluation will be
written from the standpoint of someone who is a long-time
user. If you'd rather see code than read
opinions, then
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.
and that is because DBIx::Simple is more complex in terms of how it is
built -
tooks pains to build extras classes for results of
queries and he created extra methods to delegate to SQL::Abstract, and
. I was forced to build my own convenience interfaces to
::Abstract (I chose
) and ::Interp. But
they fit right into the
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:
- 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
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
- What a database can return from a query... call it $RESULT
- What data structure we want to hold it in ... call it $STRUCT
So the methods would look something like
$object->sql$RESULT$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:
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;
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;