跳ね込むの日記 - hanekomu's Perl blog : Title: Sending DBI trace output to FirePHP with HTTP::Engine

Hi! Please consider following me on twitter: @hanekomu.

2009年06月11日

Sending DBI trace output to FirePHP with HTTP::Engine

As promised last time, here is a complete example of how to send DBI trace output to FirePHP in an HTTP::Engine demo web application.

First we create a very small example SQLite database. Let's model a company that has two departments with two employees each. Create a file called create.sql and enter the following SQL commands:

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department_id INTEGER
);

INSERT INTO departments (id, name) VALUES (1, 'publishing');
INSERT INTO departments (id, name) VALUES (2, 'accounting');

INSERT INTO employees (id, name, department_id)
    VALUES (1, 'Yamamura Masayoshi', 1);

INSERT INTO employees (id, name, department_id)
    VALUES (2, 'Ueda Akira', 1);

INSERT INTO employees (id, name, department_id)
    VALUES (3, 'Matsumoto Daichi', 2);

INSERT INTO employees (id, name, department_id)
    VALUES (4, 'Yasuda Ryota', 2);

Now we create the database itself:

sqlite3 kaisha.db <create.sql

The web application is pretty simple as well. We create an HTTP::Engine object and run it; the handler connects to the database, runs a simple query, creates an HTML table from the results and sends it in a response back to the engine.

#!/usr/bin/env perl

use strict;
use warnings;
use DBI;
use HTTP::Engine;
use HTTP::Engine::FirePHP;

my $engine = HTTP::Engine->new(
    interface => {
        module => 'ServerSimple',
        args   => {
            host => 'localhost',
            port => '1984',
        },
        request_handler => \&handler,
    },
)->run;

sub handler {
    my $req = shift;
    my $dbh = DBI->connect('dbi:SQLite:dbname=kaisha.db', '', '');
    my $res = HTTP::Engine::Response->new;

    $dbh->trace(2, $res->get_fire_php_fh);

    my $r = $dbh->selectall_arrayref('
        SELECT E.name, D.name
        FROM employees E, departments D
        WHERE D.id = E.department_id
    ');

    $dbh->disconnect;

    my $body = "<table>\n" . 
        (join "\n" =>
             map { sprintf "<tr><td>%s</td><td>%s</td></tr>\n", @$_ } @$r
        )
        . "</table>\n";
    $res->status(200);
    $res->body($body);
    $res;
}

The relevant command to send DBI trace output to FirePHP is:

$dbh->trace(2, $res->get_fire_php_fh);

In version 0.02, HTTP::Engine::FirePHP gives HTTP::Engine::Response the method get_fire_php_fh, which will return a filehandle that has been opened to a PerlIO::via::ToFirePHP layer, so you don't even need to do that manually anymore.

With this one line, DBI knows that it should trace all calls and send the trace output to the filehandle. The trace output will automatically be put into the HTTP response headers so FirePHP can display them. Here is what the Firebug console looks like when we make a request to the server: