We've expanded our Perl news coverage and improved our search! Search for all things Perl across O'Reilly!

---

How to Avoid Writing Code

Using Template Toolkit and Class::DBI

by Kake Pugh
July 15, 2003

One of the most boring programming tasks in the world has to be pulling data out of a database and displaying it on a web site. Yet it's also one of the most ubiquitous. Perl programmers being lazy, there are tools to help make boring programming tasks less painful, and two of these tools, Class::DBI and the Template Toolkit, create a whole which is far more drudgery-destroying than its parts.

Both these tools can do more complicated stuff than that described in this article, but my aim is to motivate people who may not have tried them out to give them a go and see how much work they can save you for even simple tasks.

I've assumed that you know the basics of designing a database--why you have several tables and JOIN them rather than putting everything in the same table. I've also assumed that you're not allergic to reading documentation, so I'm going to spend more space on saying why I use particular features of the modules rather than explaining exactly how they work.

Synergy

The reason that Class::DBI and the Template Toolkit work so well together is simple. Template Toolkit templates can call methods on objects passed to them--so there's no need to explicitly pull every column out of the database before you process the template--and Class::DBI saves you the bother of writing methods to retrieve database columns. You're essentially going straight from the database to HTML with only a very small amount of Perl in the middle.

Suppose you're writing a web application to store details of books and their authors, and reviews of the books by users of the site. You'd like to have a page that displays all the books in your database and, for each book, offers links to all the reviews already written. With suitably set-up classes you can write a couple of lines of Perl:

  #!/usr/bin/perl -w
  use strict;

  use Bookworms::Book;
  use Bookworms::Template;

  my @books = Bookworms::Book->retrieve_all;
  @books = sort { $a->title cmp $b->title } @books;
  print Bookworms::Template->output( template => "book_list.tt",
                                     vars     => { books => \@books } );

hand your designer a simple template to pretty up:

  [% page_title = "List all books" %]
  [% INCLUDE header.tt %]

    <ul>
      [% FOREACH book = books %]
        <li>[% book.title %] ([% book.author.name %])
            [% FOREACH review = book.reviews %]
              (<a href="review.cgi?review=[% review.uid %]">Read review 
			  by [% review.reviewer.name %]</a>)
            [% END %]
        </li>
      [% END %]
    </ul>

  [% INCLUDE footer.tt %]

and your task is done. You don't have to explicitly select the reviews; you don't have to then cross-reference to another table to find out the reviewer's name; you don't have to mess with HERE-documents or fill your program with print statements. You hardly have to do anything.

Except of course, write the Bookworm::* classes in the first place, but that's easy.

Simple, Small Classes

For convenience, we write a class containing all the SQL needed to set up our database schema. This is very useful for running tests as well as for deploying a new install of the application.

  package Bookworm::Setup;
  use strict;
  use DBI;

  # Hash for table creation SQL - keys are the names of the tables,
  # values are SQL statements to create the corresponding tables.
  my %sql = (
      author => qq {
          CREATE TABLE author (
              uid   int(10) unsigned NOT NULL auto_increment,
              name  varchar(200),
              PRIMARY KEY (uid)
          )
      },
      book => qq{
          CREATE TABLE book (
              uid           int(10) unsigned NOT NULL auto_increment,
              title         varchar(200),
              first_name    varchar(200),
              author        int(10) unsigned, # references author.uid
              PRIMARY KEY (uid)
          )
      },
      review => qq{
          CREATE TABLE review (
              uid       int(10) unsigned NOT NULL auto_increment,
              book      int(10) unsigned, # references book.uid
              reviewer  int(10) unsigned, # references reviewer.uid
              PRIMARY KEY (uid)
          )
      },
      reviewer => qq{
          CREATE TABLE review (
              uid   int(10) unsigned NOT NULL auto_increment,
              name  varchar(200),
              PRIMARY KEY (uid)
          )
      }
  );

This class has a single method that sets up a database conforming to the schema above. Here's the rendered POD for it; the implementation is pretty simple. The "force_clear" option is very useful for testing.

    setup_db( dbname      => 'bookworms',
              dbuser      => 'username',
              dbpass      => 'password',
              force_clear => 0            # optional, defaults to 0
            );

  Sets up the tables. Unless "force_clear" is supplied and set to a
  true value, any existing tables with the same names as we want to
  create will be left alone, whether or not they have the right
  columns etc. If "force_clear" is true, then any tables that are "in
  the way" will be removed. _Note that this option will nuke all your
  existing data._

  The database user "dbuser" must be able to create and drop tables in
  the database "dbname".

  Croaks on error, returns true if all OK.

Now, another class to wrap around the Template Toolkit; we want to grab global variables like the name of the site, and so on, from a config class. (There are plenty of config modules on CPAN; you're bound to find one you like. I quite like Config::Tiny; other people swear by AppConfig--and since the latter is a prerequisite of the Template Toolkit, you'll have it installed already.) Bookworms::Config is just a little wrapper class around Config::Tiny, so if I change to a different config method later I don't have to rewrite lots of code.

  package Bookworms::Template;
  use strict;
  use Bookworms::Config;
  use CGI;
  use Template;

  # We have one method, which returns everything you need to send to
  # STDOUT, including the Content-Type: header.

  sub output {
      my ($class, %args) = @_;

      my $config = Bookworms::Config->new;
      my $template_path = $config->get_var( "template_path" );
      my $tt = Template->new( { INCLUDE_PATH => $template_path } );

      my $tt_vars = $args{vars} || {};
      $tt_vars->{site_name} = $config->get_var( "site_name" );

      my $header = CGI::header;

      my $output;
      $tt->process( $args{template}, $tt_vars, \$output)
          or croak $tt->error;
      return $header . $output;
  }

Now we can start writing the classes to manage our database tables. Here's the class to handle book objects:

  package Bookworms::Book;
  use base 'Bookworms::DBI';
  use strict;

  __PACKAGE__->set_up_table( "book" );
  __PACKAGE__->has_a( author => "Bookworms::Author" );
  __PACKAGE__->has_many( "reviews",
                         "Bookworms::Review" => "book" );

  1;

Yes, that's all you need. This simple class, by its ultimate inheritance from Class::DBI, has auto-created constructors and accessors for every aspect of a book as defined in our database schema. And moreover, because we've told it (using has_a) that the author column in the book table is actually a foreign key for the primary key of the table modeled by Bookworms::Author, when we use the ->author accessor we actually get a Bookworms::Author object, which we can then call methods on:

  my $hobbit = Bookworms::Book->search( title => "The Hobbit" );
  print "The Hobbit was written by " . $hobbit->author->name;

There are a couple of supporting classes that we need to write, but they're not complicated either.

First a base class, as with all Class::DBI applications, to set the database details:

  package Bookworms::DBI;
  use base "Class::DBI::mysql";

  __PACKAGE__->set_db( "Main", "dbi:mysql:bookworms", 
    "username", "password" );

  1;

Our base class inherits from Class::DBI::mysql instead of plain Class::DBI, so we can save ourselves the trouble of directly specifying the table columns for each of our database tables--the database-specific base classes will auto-create a set_up_table method to handle all this for you.

At the time of writing, base classes for MySQL, PostgreSQL, Oracle, and SQLite are available on CPAN. There's also Class::DBI::BaseDSN, which allows you to specify the database type at runtime.

We'll also want a class for each of the author, review, and reviewer tables, but these are even simpler than the Book class. For example, the author class could be as trivial as:

  package Bookworms::Author;
  use base 'Bookworms::DBI';
  use strict;

  __PACKAGE__->set_up_table( "author" );

  1;

If we wanted to be able to access all the books by a given author, we could add the single line

  __PACKAGE__->has_many( "books",
                         "Bookworms::Book" => "author" );

and an accessor to return an array of Bookworms::Book objects would be automatically created, to be used like so:

  my $author = Bookworms::Author->search( name => "J K Rowling" );
  my @books = $author->books;

Or indeed:

  <h1>[% author.name %]</h1>

  <ul>
    [% FOREACH book = author.books %]
      <li>[% book.title %]</li>
    [% END %]
  </ul>

Simple, small, almost trivial classes, taking a minute or two each to write.

Pages: 1, 2

Next Page