Perl Beginners - Addressbook Tutorial Step 6 - Writing utility and SQL classes, and using Class::MethodMaker
In Step 5, we created the start() method for the unrestricted Perl handler. This method is called by the cgi and will display a blank search form. It needs to look in the database to fill the pull-downs for area codes and states. The start method looks to our utility class, Record.pm, for the methods to provide the data to fill the <select> lists in the template file.
The SQL class
Before we get into the utility class, let's write the SQL class, SQL.pm:
package PEACE::AddressBook::SQL;
# /usr/lib/perl5/5.6.0/PEACE/AddressBook/SQL.pm - holds SQL commands for addressbook
use strict;
our $VERSION = do { my @r = (q$Revision: 1.1.1.1 $ =~ /\d+/g); sprintf "%d."."%02d" x $#r, @r };
# must be all on one line, for MakeMaker
sub select_distinct_area_codes {
return <<QEND;
select distinct area_code as AREA_CODE
from number
order by area_code
QEND
}
sub select_distinct_states {
return <<QEND;
select distinct state as STATE
from address
order by state
QEND
}
1;
__END__
This doesn't seem too complicated. SQL.pm is simply a container to hold all our SQL queries. Doing this centralizes the SQL to ease future conversion to another database. Additionally, centralizing the SQL means we'll never have to hunt through our code to find a particular query. Notice we're using aliases in the query. I use them to demonstrate one of the most important differences between PostgreSQL and MySQL: aliases do not always seem as they appear to be! Look at this example of a SELECT statement in PostgreSQL:
addressbook=> select person_id as PERSON_ID, last_name as LAST_NAME,
addressbook-> first_name as FIRST_NAME, middle_name as MIDDLE_NAME
addressbook-> from person;
person_id | last_name | first_name | middle_name
-----------+-----------+------------+-------------
1 | Osbourne | Ozzy |
(1 row)
addressbook=>
Notice that, even though the aliases were capitalized, the resulting column headers are lower case. The same query in MySQL yields:
mysql> select person_id as PERSON_ID, last_name as LAST_NAME,
-> first_name as FIRST_NAME, middle_name as MIDDLE_NAME
-> from person;
+-----------+-----------+------------+-------------+
| PERSON_ID | LAST_NAME | FIRST_NAME | MIDDLE_NAME |
+-----------+-----------+------------+-------------+
| 1 | Osbourne | Ozzy | NULL |
+-----------+-----------+------------+-------------+
1 row in set (0.00 sec)
mysql>
The column headers are capitalized, just as they were declared in the aliases. What does this mean? If we were to use the fetchrow_hashref() method to retrieve the data, and we wanted to print the data directly from our Perl script, we would have to be very careful how we dereferenced the hash:
my $sth = $dbh->prepare(
select person_id as PERSON_ID, last_name as LAST_NAME,
first_name as FIRST_NAME, middle_name as MIDDLE_NAME
from person
);
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
print "Person ID: ", $ref->{PERSON_ID}, "\n",
"Last Name: ", $ref->{LAST_NAME}, "\n",
"First Name: ", $ref->{FIRST_NAME}, "\n",
"Middle Name: ", $ref->{MIDDLE_NAME}, "\n";
}
This will work for MySQL, but not for PostgreSQL. To get it to work with PostgreSQL, we'd have to dereference the hash using the lower case field names:
print "Person ID: ", $ref->{person_id}, "\n",
"Last Name: ", $ref->{last_name}, "\n",
"First Name: ", $ref->{first_name}, "\n",
"Middle Name: ", $ref->{middle_name}, "\n";
However, HTML::Template helps us overcome this problem. No matter which case is used, the values are returned as we would expect. We can reference the values in the template file using either upper or lower case:
<TMPL_LOOP NAME=PEOPLE>
<p>
Person ID: <TMPL_VAR ESCAPE=HTML NAME=PERSON_ID><br>
Last Name: <TMPL_VAR ESCAPE=HTML NAME=last_name><br>
First Name: <TMPL_VAR ESCAPE=HTML NAME=fIrSt_NaMe><br>
Middle Name: <TMPL_VAR ESCAPE=HTML NAME=MIDDLE_name>
</p>
</TMPL_LOOP>
These template tags will work with either PostgreSQL or MySQL.
Each method in SQL.pm uses a here document to return the text of each particular query. Using here documents is beneficial for two reasons: when developing, we're able to cut and paste our queries directly into a database command line to test the syntax, and we're free to add punctuation to the queries without having to worry about escaping out certain characters.
Using Class::MethodMaker
In the utility class, we'll be using a module called Class::MethodMaker. To demonstrate how Class::MethodMaker works, consider the following code:
sub new {
my $invocant = shift;
my $self = bless({}, ref $invocant || $invocant);
$self->init();
return $self;
}
sub init {
my $self = shift;
$self->tmpldir("/some/local/directory/");
}
for my $field ( qw{tmpldir} ) {
my $slot = __PACKAGE__ . "::$field";
no strict "refs";
*$field = sub {
my $self = shift;
$self->{$slot} = shift if @_;
return $self->{$slot};
};
}
Although it may seem complicated, all this code does is provide a class method called tmpldir() that's available to $self after calling the new() constructor. All the tmpldir() method does is return the scalar value, /some/local/directory/. Seems like a lot of work just to provide a scalar to the class, doesn't it? What's more, every time you want to add another class method you'd have to add the corresponding $self->something() line to init(), and then you'd have add something to the qw{} list. Additionally, all the class methods are automatically created, whether you need to use them or not. Too much work and too much overhead.
What Class::MethodMaker does is automagically create the internal class methods we'll need. Also, we can provide a way to create them only when they're needed. Here's how I would rewrite the above code using Class::MethodMaker:
use Class::MethodMaker
new_with_init => 'new',
get_set => [qw/tmpldir/];
sub init {
my $self = shift;
$self->tmpldir("/some/local/directory/");
}
Class::MethodMaker creates the new() and tmpldir() methods for us, and new() will call init(), so we just need to provide the implementation of init().
There are lots of other cool methods provided by Class::MethodMaker. I highly suggest reading more about this class. You can do that by typing perldoc Class::MethodMaker.
The utility class
So, let's create our utility class. Remember, the utility class provides methods to the handler for working with the data. Its methods are called by the handler, but not by the cgi. The only thing we'll need the constructor to do is to provide a database handle to the rest of the class. Here's a file, Record.pm to get us started:
package PEACE::AddressBook::Record;
# /usr/lib/perl5/5.6.0/PEACE/AddressBook/Record.pm - utility class to provide methods to handler
require 5.6.0;
use strict;
use base qw(PEACE::AddressBook);
use PEACE::AddressBook::SQL;
use Carp;
#my $debug = 0;
my $debug = 1;
use Data::Dumper;
our $VERSION = do { my @r = (q$Revision: 1.1.1.1 $ =~ /\d+/g); sprintf "%d."."%02d" x $#r, @r };
# must be all on one line, for MakeMaker
use Class::MethodMaker
get_set => [qw/dbh/],
new_with_init => "new";
sub init {
my ($self, $dbh) = @_;
croak "not enough args to init()" unless defined $dbh;
$self->dbh($dbh);
return $self;
}
1;
__END__
After declaring which other modules we'll need and setting the VERSION variable. we tell Class::MethodMaker that we want to create an internal method called dbh. This is the reference to the database handle that's passed in from the constructor in our handler:
my $record = PEACE::AddressBook::Record->new($self->_db); # constructor in Handler.pm
Of course, we want the database handle to be defined, so we'll call croak if it isn't. Our handler called two methods in the utility class, select_distinct_area_codes() and select_distinct_states(). So, let's add these methods to the class. You can add them anywhere after the use declarations and before the __END__ token.
sub select_distinct_area_codes {
my ($self) = @_;
my $sth = $self->dbh->prepare(PEACE::AddressBook::SQL->select_distinct_area_codes());
$sth->execute();
my @area_codes;
while (my $ref = $sth->fetchrow_hashref()) { push(@area_codes, $ref); }
$sth->finish();
return \@area_codes;
}
sub select_distinct_states {
my ($self) = @_;
my $sth = $self->dbh->prepare(PEACE::AddressBook::SQL->select_distinct_states());
$sth->execute();
my @states;
while (my $ref = $sth->fetchrow_hashref()) { push(@states, $ref); }
$sth->finish();
return \@states;
}
Let's analyze the important parts of these methods.
my $sth = $self->dbh->prepare(PEACE::AddressBook::SQL->select_distinct_area_codes());
$sth->execute();
my @states;
Here we prepare and execute a statement handle, $sth. Notice we're calling one of the query methods we created in SQL.pm. We'll store the query results in an array called @states.
while (my $ref = $sth->fetchrow_hashref()) { push(@area_codes, $ref); }
We'll use the fetchrow_hashref() method to retrieve each row of data. This method is considered the slowest of the DBI methods for retrieving data, but we need the data to be stored in a hash to use it in the template file (that's one of the drawbacks of using HTML::Template).
$sth->finish();
return \@area_codes;
We don't need to call the finish() method, because whenever a statement handle goes out of scope, finish() is called automatically by DBI. I like to call it anyway (call me crazy). Finally, since the template file will be looking for a reference to an array that contains the data hash references, we return a reference to the area_codes array we built when retrieving the data using the fetchrow_hashref() method.
The select_distinct_states() method is similar to the select_distinct_area_codes() method, so I'll skip its explanation.
After adding these methods to our utility class, we're finally able to call the cgi. Point your browser to /cgi-bin/handler.cgi, and you should see a form that looks similar to this:
Note that I pulled down the area_code <select> box to demonstrate that it's filled with one area code (I added one sample record). Add a test record or two and reload the cgi. The pull-downs should be filled with the area code and state values you added in your test records.
Coming next - A summary of what we've done so far.
Copyright © 2001 by Peace Computer Systems