How do I refactor Perl code that uses Template Toolkit with DBI to take advantage of FastCGI?

Question

Background

Below is a typical piece of Perl code (sample.pl for the sake of discussion) that grabs submitted form data using CGI, passes the form data to DBI which will then retrieve the required rows from MySQL and then hands the results over to Template Toolkit to render into a HTML document for display.

Code listing for sample.pl :

#!/usr/bin/perl
use strict;
use CGI;
use DBI:
use Template;

#Grab submitted form data
my $cgi = CGI->new();
my $idFromSomewhere= $cgi->param('id');

my $driver   = "mysql";
my $server   = "localhost:3306";
my $database = "test";
my $url      = "DBI:$driver:$database:$server";
my $user     = "apache";
my $password = "";

#Connect to database
my $db_handle = DBI->connect( $url, $user, $password ) 
    or die $DBI::errstr;

#SQL query to execute
my $sql = "SELECT * FROM tests WHERE id=?";

#Prepare SQL query
my $statement = $db_handle->prepare($sql)
        or die "Couldn't prepare query '$sql': $DBI::errstr\n";

#Execute SQL Query
$statement->execute($idFromSomewhere)
    or die "Couldn't execute query '$sql': $DBI::errstr\n";

#Get query results as hash
my $results = $statement->fetchall_hashref('id');

$db_handle->disconnect();
my $tt = Template->new();

#HTML output template
my $input = 'template.html';
my $vars = {
    tests => $results,
};

#Process template and output as HTML
$tt->process($input, $vars)
    or die $tt->error();

For better performance and scalability, web hosts providing shared servers, such as Dreamhost, strongly recommend that all production Perl script support FastCGI. The FastCGI documentation is pretty clear on how to modify existing Perl code to support FastCGI. The simple code below is often given as an example:

use FCGI;
while (FCGI::accept >= 0)
{    
   #Run existing code.
}

What's not so clear is where and what to put in the while loop.

Sub Questions

A. Should the code in sample.pl be simply wrapped around the existing code like so:

while (FCGI::accept >= 0)
{    
    #Grab submitted form data
    my $cgi = CGI->new();
    ...
    ...
    #Process template and output as HTML
    $tt->process($input, $vars)
    or die $tt->error();
}

B. Or is there more to it? For instance, should the code that handles the cgi, database and template be refactored into their own subs?

C. Should DBI->connect() and $db_handle->disconnect() be called inside or outside the FCGI while loop and what are the performance implications?

D. Should $tt->process() be called inside or outside the FCGI while loop?

Answer 1

Subquestion C: (persistent DB connections)

Take a look at DBI->connect_cached(). I believe you can use it inside your CGI::Fast loop, and DBI.pm will remember/cache your connection. So, on the 2nd, 3rd, etc. calls to connect_cached() with the same parameters, you will get back an existing connection. It will create a new connection if the old one is no longer available.

What's really nice about this approach is that the only change you have to make to your existing application (other than adding the CGI::Fast loop) is to replace connect() with connect_cached(). And connect_cached() will work with plain vanilla CGI too.

See also Do I have to put DB connection/initialization outside of the FCGI loop to take advantage of FastCGI in Perl? and http://www.mail-archive.com/cgiapp@lists.erlbaum.net/msg04351.html

Answer 2

If you want to use FCGI, then only do the bare minimum in that loop to start the task. Everything else should live in modules, and all you need to do is pass the input along.

use FCGI;
while (FCGI::accept >= 0)
    {    
    MyApplication->activate( @args );
    }

The rest of the stuff is in MyApplication somewhere. Anything interesting shouldn't be in the FastCGI script. You don't what to tightly couple all the application stuff with the thing that's activating it.

You might want to see my chapter on modulinos in Mastering Perl to see how you can turn your scripts into re-usable modules. That sort of thing makes stuff like this really easy.

For the persistent database connections, you have a little bit more work to do. You can start a connection outside the loop, but periodicially you need to ping it and perhaps re-establish it. See what Apache::DBI for this.

Answer 3

If you're familiar with CGI.pm, there is no point in using FCGI.pm, use CGI::Fast.

Your example converted to use CGI::Fast would be:

#!/usr/bin/perl
use strict;
use CGI::Fast;
use DBI;
use Template;

my $driver   = "mysql";
my $server   = "localhost:3306";
my $database = "test";
my $url      = "DBI:$driver:$database:$server";
my $user     = "apache";
my $password = "";

#Connect to database
my $db_handle = DBI->connect( $url, $user, $password ) or die $DBI::errstr;

while ( my $cgi = CGI::Fast->new() ) {

    #Grab submitted form data
    my $idFromSomewhere = $cgi->param( 'id' );

    #SQL query to execute
    my $sql = "SELECT * FROM tests WHERE id=?";

    #Prepare SQL query
    my $statement = $db_handle->prepare( $sql )
        or die "Couldn't prepare query '$sql': $DBI::errstr\n";

    #Execute SQL Query
    $statement->execute( $idFromSomewhere )
        or die "Couldn't execute query '$sql': $DBI::errstr\n";

    #Get query results as hash
    my $results = $statement->fetchall_hashref( 'id' );

    my $tt = Template->new();

    #HTML output template
    my $input = 'template.html';
    my $vars = { tests => $results, };

    #Process template and output as HTML
    $tt->process( $input, $vars )
        or die $tt->error();
}

As for your sub questions:

• A: Do not use FCGI unless you are 100% sure that you know what you're doing. You definitely want CGI::Fast :)
• B: I would refactor it for readability
• C: if you use DBI->connect before accepting connection you get permanent database connection which is great from performance standpoint
• D: definitely inside.

Just as a side information - if you want to develop websites in Perl, at least take a look at Catalyst (http://www.catalystframework.org/)