Class::DBI

by Tony Bowden
November 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.

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 pence

Similarly, 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 pence

As 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 pence

Pages: 1, 2

Next Page