Class::DBI
by Tony BowdenNovember 27, 2002
Several articles on Perl.com, including the recent
Phrasebook
Design Pattern, have discussed the problems faced when writing
Perl code that interacts with a database. Terrence Brannon's
DBIx::Recordset
article attempted to show how code dealing with databases can be made
simpler, and more maintainable. In this article, I will try to show how
Class::DBI can make this easier still.
Class::DBI prizes laziness and simplicity. Its goal is to make simple
database interactions trivial, while leaving the hard ones possible.
For many simple applications, it replaces the need for writing SQL
entirely. On the other hand, it doesn't force you to build complex data
structures to specify a complex query; if you really need the power or
expressiveness of raw SQL, then it gets out of your way and lets you drop back
to that.
The easiest way to see Class::DBI in action is to build a simple
application with it. In this article, I'll build a tool for performing
analysis on my telephone bill.
|
Related Reading Programming the Perl DBI |
Data::BT::PhoneBill (available from CPAN), provides a simple interface to a phone bill downloaded from the BT Web site. So, armed with this module, and a few recent phonebills, let's store these details in a database, and see how to extract useful information from them.
Class::DBI works on the basis that each table in your database has a
corresponding class. Although each class could set up its own connection
information, it's a better idea to encapsulate that connection in one
class, and have all the others inherit from that. So, we set up our
database, and create the base class for our application:
package My::PhoneBill::DBI;
use base 'Class::DBI';
__PACKAGE__->set_db('Main', 'dbi:mysql:phonebill', 'u/n', 'p/w');
1;We simply inherit from Class::DBI and use the 'set_db' method to set up
the connection information for our database. That's all we need in this
class for now, so next we set up our table for storing the phone call
information:
CREATE TABLE call (
callid MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
number VARCHAR(20) NOT NULL,
destination VARCHAR(255) NOT NULL,
calldate DATE NOT NULL,
calltime TIME NOT NULL,
type VARCHAR(50) NOT NULL,
duration SMALLINT UNSIGNED NOT NULL,
cost FLOAT(8,1)
);For this, we set up a corresponding class:
package My::PhoneBill::Call;
use base 'My::PhoneBill::DBI';
__PACKAGE__->table('call');
__PACKAGE__->columns(All =>
qw/callid number destination calldate calltime type duration cost/);
1;We inherit our connection information from our base class, and then specify what table we're dealing with, and what its columns are. Now we have enough to populate the table.
So, we create a simple, "populate_phone_bill" script:
#!/usr/bin/perl
use Data::BT::PhoneBill;
use My::PhoneBill::Call;
my $file = shift or die "Need a phone bill file";
my $bill = Data::BT::PhoneBill->new($file) or die "Can't parse bill";
while (my $call = $bill->next_call) {
My::PhoneBill::Call->create({
number => $call->number,
calldate => $call->date,
calltime => $call->time,
destination => $call->destination,
duration => $call->duration,
type => $call->type,
cost => $call->cost,
});
}The create() call runs the SQL to INSERT the row for each call. As we're
using Class::DBI, and have defined our primary key column to be
AUTO_INCREMENT, we don't need to specify a value for that column. On databases
that support sequences, we could also inform Class::DBI what sequence
should be used to provide the primary key.
Now that we have a table populated with calls, we can begin to run queries against it. Let's write a simple script that reports on all the calls to a specified number:
#!/usr/bin/perl
use My::PhoneBill::Call;
my $number = shift or die "Usage: $0 <number>";
my @calls = My::PhoneBill::Call->search(number => $number);
my $total_cost = 0;
foreach my $call (@calls) {
$total_cost += $call->cost;
printf "%s %s - %d secs, %.1f pence\n",
$call->calldate, $call->calltime, $call->duration, $call->cost;
}
printf "Total: %d calls, %d pence\n", scalar @calls, $total_cost;Here we can see that Class::DBI provides a 'search' method for us
to use. We supply a hash of column/value pairs, and we get back all the
records that matched. Each result is an instance of the Call class,
and each has an accessor method corresponding to each column. (It's also
a mutator method, so we can update that record, but we're only reporting
at this stage).
So, if we want to see how often we're calling the Speaking Clock, then we run
> perl calls_to 123
2002-09-17 11:06:00 - 5 secs, 8.5 pence
2002-10-19 21:20:00 - 8 secs, 8.5 pence
Total: 2 calls, 17 penceSimilarly, if we want to see all the calls on a given date, then we could
have a 'calls_on' script:
#!/usr/bin/perl
use My::PhoneBill::Call;
my $date = shift or die "Usage: $0 <date>";
my @calls = My::PhoneBill::Call->search(calldate => $date);
my $total_cost = 0;
foreach my $call (@calls) {
$total_cost += $call->cost;
printf "%s) %s - %d secs, %.1f pence\n",
$call->calltime, $call->number, $call->duration, $call->cost;
}
printf "Total: %d calls, %d pence\n", scalar @calls, $total_cost;Running it gives:
> perl calls_on 2002-10-19
...
18:36:00) 028 9024 4222 - 41 secs, 4.2 pence
21:20:00) 123 - 8 secs, 8.5 pence
...
Total: 7 calls, 92 penceAs promised, we've written a database application without writing any
SQL. OK, so we haven't really done anything very complicated yet, but
even for this simplistic use Class::DBI makes our life much easier.
Building a Phone Book
I used to have a good memory for phone numbers. But Nokia, Ericsson, et
al, have conspired against me. By giving my cell phone a built-in address
book, they ensured that the part of my brain responsible for remembering
10 or 11 digit numbers would gradually atrophy. Now, when I look at the
output of 'calls_on', I have no idea who "028 9024 4222" represents. So,
let's build an address book that can store this information, and then
change our reports to use it.
The first thing we should do is arrange our information a little better.
We'll take the number and destination columns, and move them to a
"recipient" table, to which we'll add a name column. "Destination" doesn't
make as much sense when associated with the number, rather than the call,
so we'll rename it to "location".
CREATE TABLE recipient (
recipid MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
number VARCHAR(20) NOT NULL,
location VARCHAR(255) NOT NULL,
name VARCHAR(255),
KEY (number)
);And then we create the relevant class for this table:
package My::PhoneBill::Recipient;
use base 'My::PhoneBill::DBI';
__PACKAGE__->table('recipient');
__PACKAGE__->columns(All => qw/recipid number location name/);
1;We also need to modify the Call table:
CREATE TABLE call (
callid MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
* recipient MEDIUMINT UNSIGNED NOT NULL,
calldate DATE NOT NULL,
calltime TIME NOT NULL,
type VARCHAR(50) NOT NULL,
duration SMALLINT UNSIGNED NOT NULL,
cost FLOAT(8,1),
* KEY (recipient)
);and its associated class:
package My::PhoneBill::Call;
use base 'My::PhoneBill::DBI';
__PACKAGE__->table('call');
__PACKAGE__->columns(All =>
* qw/callid recipient calldate calltime type duration cost/);
1;Then we can modify our script that populates the database:
#!/usr/bin/perl
use Data::BT::PhoneBill;
use My::PhoneBill::Call;
*use My::PhoneBill::Recipient;
my $file = shift or die "Need a phone bill file";
my $bill = Data::BT::PhoneBill->new($file) or die "Can't parse bill";
*while (my $call = $bill->next_call) {
* my $recipient = My::PhoneBill::Recipient->find_or_create({
* number => $call->number,
* location => $call->destination,
* });
* My::PhoneBill::Call->create({
* recipient => $recipient->id,
calldate => $call->date,
calltime => $call->time,
duration => $call->duration,
type => $call->type,
cost => $call->cost,
});
}This time we need to create the Recipient first, so we can link to it
from the Call. But we don't want to create a new Recipient for each call -
if we've ever rang this person before, there'll already be an entry
in the recipient table: so we use find_or_create to give us back the
existing entry if it's there, or else create a new one.
With the table repopulated we can return to our reporting scripts.
Our calls_on script now fails as we can can't ask a call for its
'number'. So, we change it to:
#!/usr/bin/perl
use My::PhoneBill::Call;
my $date = shift or die "Usage: $0 <date>";
my @calls = My::PhoneBill::Call->search(calldate => $date);
my $total_cost = 0;
foreach my $call (@calls) {
$total_cost += $call->cost;
printf "%s) %s - %d secs, %.1f pence\n",
* $call->calltime, $call->recipient, $call->duration, $call->cost;
}
printf "Total: %d calls, %d pence\n", scalar @calls, $total_cost;However, running it doesn't really give us what we want:
> perl calls_on 2002-10-19
...
18:36:00) 67 - 41 secs, 4.2 pence
21:20:00) 47 - 8 secs, 8.5 pence
...
Total: 7 calls, 92 pencePages: 1, 2 |
