DBI vs DBIx::Recordset vs princepawn vs chromatic

The following is an open letter to chromatic on an issue about which many perlmonks are not completely clear on. I recently published a perl.com paper on an extension of DBI known as DBIx::Recordset.

About a week after I published this, I noticed an email from the onperl@lists.oreilly.com mailing list in which a short piece of a criticism of my article by chromatic appeared. In my eyes, the criticism is weak, but in the email, the acting editor of Perl.com write supportively of it. The aggravations for me in this are:

chromatic did not cc with his initial criticism

The acting editor released a criticism of my paper without insuring that the criticism was valid

Neither party made any effort to collaborate with me in analysing possible shortcomings in my article.

Now as I write this, I am starting to think: "Tim Bunce and Alligator Descartes could say the same thing about me. I didn't tell them I was writing an article concerning DBI. And next thing you know, there it is in from 100,00 eyes for all to see." There is one difference. I did try to reach Tim and Alligator but caught pure hell trying to subscribe to the DBI mailing lists. I felt like I was interviewing for a position with the FBI or something.

So anyway, I am now writing, as open letter what I planned to write to chromatic in email and CC to the acting editor of perl.com. I hope to hear what everyone thinks about whether DBI is really a sensible tool for application development, not scripts.

And let's not forget we have several options besides Recordset for application database use from Perl:

BingoX::Carbon

Alzabo

DBIx::Abstract

Before any full-fledged attack on my article occurs in public, I would like to see such attack and have a chance to respond.
I attach my response to the small segement of criticism that was publicly aired without my prior notification at onperl@lists.oreilly.com
Since I authored this article (1 year ago), there have been several extensions of DBI and DBIx::Recordset is one. It would be very useful to compare and contrast these offerings...
I would have appreciated a CC of the initial criticisms you sent to Chris Coleman. I don't really like how much went on regarding my article without my knowledge.

In the short blurb recently released to onperl@lists.oreilly.com, an extract of Chromatic's criticism of my recent perl.com article is offered:

     .... For example, the author is either unaware of hash slices 
     or chooses not to use them in the first code example.  While 
     constructing SQL statements with many fields and placeholders 
     is tedious, and the Insert() function of DBIx::Recordset is 
     shorter, one could reduce 15 or more lines of repetitive code 
     [in the DBI example] with hash slices ....  It would be possible 
     to improve the final example by using placeholders,
     as well ....
[download]

Let's take the criticisms one-by-one. The first criticism is of the following code:

$sql='insert into uregisternew
        (country, firstname, lastname, userid, password, address1, cit
+y,
        state, province, zippostal, email, phone, favorites, remaddr,
        gender, income, dob, occupation, age)
        values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';


 my @data=($formdata{country}, $formdata{firstname}, $formdata{lastnam
+e},
        $formdata{email}, $formdata{password}, $formdata{address},
        $formdata{city},  $formdata{state},  $formdata{province},
        $formdata{zippostal}, $formdata{email}, $formdata{phone},
        $formdata{favorites}, $formdata{remaddr}, $formdata{gender},
        $formdata{income}, $formdata{date}, $formdata{occupation},
 $formdata{age});


        $sth2 = $dbh->prepare($sql);
        $sth2->execute(@data);
        $sth2->finish();
[download]

Chromatic is saying that by predefining the order the formdata, we can eliminate the monstrous "my @data = (...)" code completely and simply change

        $sth2->execute(@data);
 to
        $sth2->execute(@formdata{@formdata_column_ordering});
[download]

where @formdata_column_ordering is an array indicating the order in which to choose fields from %formdata so that they match the columns indicated. And my response to Chromatic is to say that he is 100% correct he could do this. In fact, he could simplify generation of the insert sql by a couple of simple joins:

sub make_placeholders { 
   sprintf "(%s)", join ',',  '?' x @_;
}

sub make_columns {
   sprintf "(%s)", join ',',  @_;
}
[download]

After this the "$sql = ..." code becomes much smaller too:

$sql = sprintf "insert into %s values %s", 
     make_columns(@formdata_column_ordering),
     make_placeholders(@formdata_column_ordering) ;
[download]

And so we see that Chromatic is 100% correct, you can make use of the Perl language to come up with more succinct ways to use DBI. Now let's take this statement from my paper to compare the DBI and DBIx::Recordset APIs for application-level useability:

    The key way to determine whether a particular module/library is
    matched to the level of a task is to count the number of lines of
    ``prep code'' you must write before you can do what you want.
[download]

As we can see, to make up for the fact that DBI does not directly support commission of hashes to database, I have had to do the following:

 1 create 2 subroutines 
 2 use these subroutines to generate sql 
 3 connect to the database
 4 obtain database and statement handles 
 5 commit the record to database.
[download]

This is in contrast the DBIx::Recordset example:

    DBIx::Recordset->Insert({ %connection_hash, %formdata });
[download]

Now of course, the other valid criticism is that you can write even more subroutines to allow yourself to handle steps 1-5 in one step, but by the time you have done so, you have basically re-written DBIx::Recordset! Again referring to the intro:

      In most cases the gap between DBI's API and Perl applications
      has been bridged by indiscriminately mixing generic
      application-level functionality with the specifics of the
      current application.
[download]

What I mean here is suppose that had I not created subroutines to deal with placeholder and column-name generation, but instead done something like this:

$sql= sprintf 'insert into uregisternew
        (%s) values (%s)', 
      join ',', @formdata_ordering,
      join ',', '?' x @formdata_ordering;
[download]

Then I would have been mixing the generic application-level functionality of SQL generation with the specifics of the data about to be inserted. In the intro I continue:

      Another maladaptive way that the DBI API has been extended for
      application-level databasing is by developing a collection of
      generic application-level tools but not publishing them.
[download]

This would be the case if I developed large bodies of code with my make_placeholder() and make_column_name() routines but did not make them available for widespread use. Finally I state:

       The final way to misuse DBI in an application is to use it
       directly.
[download]

And this is exactly what the paper showed as an example. Regarding your accusation:

    .... For example, the author is either unaware of hash slices 
     or chooses not to use them in the first code example.
[download]

Neither option you present is the case. This code example was lifted directly from a now-defunct dot-com that I consulted at. My goal was to show the type of kludgery that results when people who don't have the time, education, or interest to develop viable high-level APIs for their Perl programs make use of what is available and what is available (in this case DBI and it's manpages and book) provides the wrong level of abstraction for the task at hand.

The second criticism is of the final DBI example. Chromatic says:

     It would be possible to improve the final example by using
     placeholders, as well ....
[download]

How would the final example be improved by placeholders? Speed of execution? Reduction of code lines? Since the blurb did not clarify this, I assume you mean that execution speed would improve. I won't argue that the final DBI example could be made faster with fewer lines of code. But I would argue that the final DBI could be made as functional as the DBIx::Recordset example in as many lines of code. The intent of the final example was to contrast DBIx::Recordset use with DBI use in a high-level database task and show what automatic features of Recordset had to be manually coded when directly using DBI.

Back to Meditations