Intro

Let us take a magnifying glass to this innocent line of Perl code:

my @user_id = 
  CompanyDB::Query('USER_ID_FROM_USERNAME', $cgi->param('username'));
[download]

This seems simple enough, but it actually shows 3 topics of developing Perl database applications which are beyond the DBI API.

Query archival

Though this code makes use of a SQL SELECT query, the query is not (and should not be) anywhere in the main body of the application code. There are 3 CPAN modules which cover this issue: <cpan>Ima::DBI</cpan>, <cpan>DBIx::Librarian</cpan>, and <cpan>SQL::Catalog</cpan>

Database Handle Management

Executing a query against a database requires a database handle (it may require a statement handle if you $dbh->prepare, instead of $dbh->do, but in all cases the database handle must be created or already exist for this code to work. This issue is trickier than it seems. In fact if you value the holy grail of view-independent applications, then somehow your means of database handles must work both when using web applications and when using command-line apps. CPAN modules related to this task are <cpan>Ima::DBI</cpan>, <cpan>DBIx::Password</cpan>, and <cpan>Apache::DBI</cpan>.

One could also say that <cpan>AppConfig</cpan> is somewhat related because it might contain all of an application's configuration data. Which makes me want to create an alternative to <cpan>DBIx::Password</cpan> which accesses password information from an <cpan>appconfig</cpan> file instead of using a Perl module with config information in it. After all, why would I want some of my application configuration information in a Perl module somewhere and the rest in my AppConfig file? This violates the principle of centralization.

SELECT query results massaging

As you can see, the line of code always returns an array of results to the user, thus simplifying the application code. The module that works most similar to this on CPAN is <cpan>DBIx::DWIW</cpan>. However, there are a host of others: <cpan>DBIx::Broker</cpan>, <cpan>DBIx::Easy</cpan>, and <cpan>DBIx::AbstractLite</cpan>. Two modules which supposedly make SELECT query massaging easier are <cpan>EZDBI</cpan> and <cpan>DBIx::Abstract</cpan> but for me their principal problem is that they do not support pure SQL, but require that the SELECT part of the SQL be removed. E.g.

use EZDBI;
for my $row (Select('* from users')) {

}

use DBIx::Abstract;
$dbh->select('* from users');
[download]

That looks like sexy code until you realize that it flies in the face of separating SQL from Perl: no longer can database experts work on complicated, tested, and well-tuned SQL and provide it to the Perl application developer via a label. Instead he has to create "truncated" SQL because of the way these two modules work.

Only non-mutating SQL executed via Perl/DBI

It is not obvious from this line of code, but the database application that I copied this line of code from, actually has a company policy that only SELECT statements are run by Perl/DBI and that any sort of UPDATE or DELETE is run via stored procedures only.

Why might a company do this? Well, I can think of a few reasons:

Asynchronous execution. Perhaps they want to get back to the main app quickly
Easy off-line testing. It is very amenable to testing to have a bunch of stored procedures to run and test without bringing up a huge Perl application.

As you can see, i am grabbing for straws in that reason list. The reason for this is that there are clearly cases where dynamic SQL is better. For instance what if you have a "MyApplication" section where the person goes thru a bunch of pulldowns and checkboxes and changes a huge ream of config info? Do you really want to write every permutation of update SQL for this? I'm assuming you are not dressed in leather and have whip in your hand as you answer this question.. Hheheh.

OUTRO

In this installation of the Wide-Wide World of DBI, we have taken one line of code and managed to understand what it implies about how you handle para-DBI issues in database application development. Inquiring minds want to know how you handle such issues.

</body> </html>

Comment on 1 line of code, 3 para-DBI database application issues... how do you handle 'em? Select or Download Code

Replies are listed 'Best First'.
Re (tilly) 1: 1 line of code, 3 para-DBI database application issues... how do you handle 'em? by tilly (Archbishop) on Feb 16, 2002 at 23:02 UTC
I handle this by recognizing that the issues I talked about at Re (tilly) 6: Code Critique truly are generic, and I suit my solution to the situation as best as I can. There is no truly "right" answer. Only trade-offs. Some tradeoffs tend to be fairly obvious in most situations. Others are not so clear. Either way unless you understand what you are trading off, getting it right will be a question of luck. So what are you trading off here?	[reply]
Re: 1 line of code, 3 para-DBI database application issues... how do you handle 'em? by jplindstrom (Monsignor) on Feb 17, 2002 at 14:47 UTC
only SELECT statements are run by Perl/DBI and that any sort of UPDATE or DELETE is run via stored procedures only. Why might a company do this? Well, I can think of a few reasons: Because they want the code modifying the database IN the database. Using that approach, they can build an interface that is pretty much SQL-flavour agnostic when it comes to capabilities (e.g. triggers). It's about architechture, not about 1) performance and 2) ease of development. /J	[reply]