Class::DBI Intro

[trs80]

Intro

When working with databases there are several solutions on CPAN that deal with Object Oriented access to database tables that either avoid completely or minimize the use of SQL. Unfortunately most of these modules have a rather large learning curve, not necessarily the module itself, but all the precursors to it. In that I am referencing the need to understand databases, OO Perl and a good degree of OO Programming in general, and how Perl modules (packages/classes) work. This node attempts to address these topics enough to allow for a brave soul to consider Class::DBI to make their lives easier (possibly) when working with databases.

What You Need

• DBI
• Class::DBI and its related modules
• A relational database, MySQL is used for these examples, but could be modified for others
• The DBD for your database ( DBD::msql )

Databases

If you are not using a database in your application then Class::DBI is of no use. All applications that would utilize the Class::DBI module use SQL based databases. The intent of Class::DBI is to hide the SQL behind an OO interface, but it is important to understand this so you know what is going on behind the scenes and what benefit Class::DBI provides. Class::DBI also allows for creation of custom SQL statements/methods so knowing SQL and learning Class::DBI can keep you from making "hackish" additions to your application.

Databases consist of one or more tables that house the actual data records you work with. In order to use Class::DBI you must have an existing database and tables, Class::DBI does not create either of these for you. Please refer to your database server's documentation on how to create the appropriate databases and tables.

This is the first part of the learning curve I mention above. You have to be knowledgeable enough about database systems to already be interacting with them at the SQL level or some from of GUI interface so you can understand what information you want to access.

Most databases are what they call Relational databases, which means the information between tables is related to one another and you execute queries (SQL statements) against multiple tables to create your result set. Before you begin using any of the OO modules to aid in this make sure you have a firm grasp on how these relationships work, I am only going to present a small summary for the examples below.

I will also point out that in some cases there is no relationship between the tables even with proper table design based on the nature of the data.

diotalevi pointed out that I needed to bring more attention to the origin and rules related to relational databases so to that end I offer these links:
Codd's 12 Rules
The rise of the relational database

OO Perl

While many people that write Perl programs use OO modules I would venture to guess that most people start out in Perl doing procedural programming and many never have a need to create their own OO modules. This isn't a bad thing, but it is a hurdle you have to cross in order to use Class::DBI effectively. Class::DBI uses OO Perl extensively and requires you to at least grasp the basics in order to create your initial module (subclass). There are many excellent resources for OO Perl and I recommend that you understand what a 'class' , 'subclass' , 'method' and 'inheritance' is before you continue.

OO in Brief

OO programming is designed to look at a program from the standpoint of many "objects" that interact with each other. These "objects" have "properties" and "methods". The "properties" of these objects might be something like color, or name. The "methods" of these objects tell the object to do something (or to return something to the caller) like setName (to set the name) or getColor (to get the color) or perhaps print (to tell the object to print itself).

Inheritance is used when you want an object that is almost exactly like some existing object, but you just want to override some things, and/or add more methods/properties to the object. Whatever you don't override keeps the behavior of the "parent" object and further whatever functions/procedures could operate on the "parent" object can also operate on your "child". The act of creating a child is also called creating a "subclass".

Class::DBI Basics

The first thing you have to realize when working with Class::DBI is that you are going to subclass it and never use it directly. You will inherit all the methods from it into your 'class' so you will only have 'use Class::DBI' appear in one file that you create, a Class::DBI subclass. We will start by addressing that one file.

For this example we will create a package called: My::ClassDBI ( if you don't know how you need to read more on (OO) Perl modules ). A package is sometimes referred to as a 'class' or module, I am sure the purists will comment on that :)
Here is the code for My::ClassDBI, skip down to the database section to learn more about the tables.

package My::ClassDBI;

use strict;
use warnings;
use base 'Class::DBI';

# Here we create our 'main' connection
# to the database

my $dsn      = 'dbi:mysql:database';
my $user     = 'user';
my $password = 'password';

My::ClassDBI->set_db('Main', $dsn, $user, $password);

# there is a one to one relationship
# between a package and a table in the database
# we will create 2 packages, one for the 'user'
# table and one for the 'page' table
# We upper case the table names our based on
# accepted naming conventions for packages
# but you could use any case.

package Table::User;

use base 'My::ClassDBI';

# tell Class::DBI which table with 'class'
# is working with
Table::User->table('user');

# important side note -
# the All creation method only works correctly if the FIRST field
# in the table is a primary, use the Primary assignment method
# outlined in the docs if this is the case

Table::User->columns(All =>
              qw/user_id user_name first_name
                 last_name password email city
                 state postal_code phone_number
                 address created_on modified_on/ );

1;

package Table::Page;

use base 'My::ClassDBI';
Table::Page->table('page');

Table::Page->columns(All =>
              qw/page_id user_id name filename
                 created_on modified_on/ );

1;

# Now we assign a relationship
# These become one of the most compelling reasons to invest
# in learning and using a module like Class::DBI
# Please read the Class::DBI docs for more information
# on how relationships are managed

# this statement tells Class::DBI that the 'user' table
# has records that relate to it in the 'page' table
# and that the *key* that relates them is the 'user_id'
# field.  The *key* has to be an index (or key depending
# on SQL engine) in order for this relation to work.

Table::User->has_many( 'pages' , 'Table::Page' => 'user_id' );

1;

You can name the table classes anything you like since the package only exists in memory ( you don't need to have a corresponding file on the file system, however you want to avoid possibly colliding with other modules you use.) I am a lazy typer so I like to use a name that is as short and descriptive as possible. 'Table' identifies what it is, but you might want to append the database name in front of it to better clarify. I will use 'Table' for this discussion.

The primary key is important because Class::DBI assumes that the first column passed in the 'columns' method 'All' attribute is the primary key, which it will use to assist in relationship identification. If for some reason your database doesn't allow for Primary Key identification you can do this:

Table::Tablename->columns( Primary => 'primary_key_column_name' );

The above syntax brings us to our next task, which is to specify how these tables relate. We will use our inherited methods from Class::DBI to do this.

Table::User->has_many( 'pages' , 'Table::Page' => 'user_id' );

This snippet of code does several things:

• has_many indicates to Class::DBI that the proceeding 'User' table has a many (to one) relationship with the Page table
• creates a method called 'pages' that gives us access to all the pages related to a user object
• identifies 'user_id' as the link between the tables

If you have not properly configured your Primary keys you will run into a snag when attempting to use the 'pages' method, so make sure these are correctly assigned, either automatically or manually.

The Application

So you have created our class file, now lets create a small test script to confirm operations. First create a database called classdbitest and put these tables in it:

# sql for testing

CREATE TABLE user (
  user_id int(11) unsigned NOT NULL auto_increment,
  user_name varchar(255) default NULL,
  first_name varchar(255) default NULL,
  last_name varchar(255) default NULL,
  password varchar(255) default NULL,
  email varchar(75) default NULL,
  city varchar(75) default NULL,
  state varchar(75) default NULL,
  postal_code varchar(75) default NULL,
  phone_number varchar(75) default NULL,
  address varchar(200) default NULL,
  created_on datetime default NULL,
  modified_on timestamp(14) NOT NULL,
  PRIMARY KEY  (user_id)
) TYPE=MyISAM;

CREATE TABLE page (
  page_id int(11) unsigned NOT NULL auto_increment,
  user_id int(11) unsigned NOT NULL,
  name varchar(255) default NULL,
  filename varchar(255) NOT NULL default '',
  created_on datetime NOT NULL default '0000-00-00 00:00:00',
  modified_on timestamp(14) NOT NULL,
  PRIMARY KEY  (page_id)
) TYPE=MyISAM;

Here is the test script, it will do all of the following:

1. Connect in the traditional DBI manner so we can see the "raw" results
2. Create a new database connection simply by calling on Table->User rather then having to create a connection inside the script. That is all handled by our My::Class::DBI behind the curtain by Class::DBI (yes there are several layers here)
3. Add a user via the 'create' method with Class::DBI
4. Retrieve that users information via the traditional DBI call
5. Retrieve the users information via the 'retrieve' method with Class::DBI
6. Set attributes for the user via the Table::User methods (generated by Class::Accessor, another part of the behind the curtain activity) email and phone.
7. "Push" the change to the database table via the 'update' method
8. Traditional DBI query to show that the values are in fact updated
9. Add some pages related to the user_id of our user
10. Traditional DBI query to show that the records were created
11. Delete the user object (and record) via the 'delete' method
12. Traditional DBI query to show that the user was deleted
13. Traditional DBI query to show that the pages related to that user were "magically" deleted from the page table based on our relationship established via My::ClassDBI
14. Reset our auto_increment via traditional DBI call so we can safely rerun the script and have our user created user_id 1

NOTE: The traditional DBI connection and queries are used to help illustrate how Class::DBI is used and would NOT be part of a real application based on Class::DBI

use My::ClassDBI;
use DBI;

use strict;

# Since this is our test code we will create a
# traditional connection to the database so we
# can display the actual database contents along
# with our abstracted interaction

my $dbh = DBI->connect('dbi:mysql:test') or die $DBI::errstr , "\n";

# the create method is misleading since it is
# a mthod that adds records not tables like the
# SQL CREATE command.

# Lets add a user to the user table, we will
# only add a small amount of initial information

Table::User->create( {
  user_name => 'trs80',
  first_name => 'Fist',
  last_name => 'Last',
  }
);

# Verify it added with traditional SQL
sub traditional_select {
    print "\n>> Traditional SQL results\n";
    my $cursor = $dbh->prepare("SELECT user_name,
                                   first_name,
                   last_name,
                   email,
                   phone_number,
                   address
                    FROM user");
    $cursor->execute;
    my $count = 1;
    while ( my @columns = $cursor->fetchrow() ) {
        print "    Row " . $count++ . ":" . join("\t",@columns) . "\n"
+;
    }
    print "\n";
}

traditional_select();

# now we will make an object that relates to a single
# record.  We know that our entry is id 1 since it is
# the only record in the database so we do the following

our $user = Table::User->retrieve(1);

# $user contains our object, but since we haven't called
# any methods on the objects it has not made any calls to
# the database.

print ">> Class::DBI Results (single column)\n";
print "    Users First Name: " , $user->first_name , "\n\n";

# Now $user contains all the information related to
# the record in the database.  You can verify this with
# Data::Dumper if you want.

# Lets add some more data to our users record
# We do this by passing our value to the method
# names that correspond to our table columns,
# these were auto created by Class::DBI

$user->email('[e-mail]');
$user->phone_number('999-511-1212');
$user->update();

print ">> Class::DBI Results (single column)\n";
print "    Users Phone Number: " , $user->phone_number , "\n";

# Verify with traditional_sql
traditional_select();

# Notice we used update here, what happens if we don't use
# update?

$user->address('123 First Street');

print ">> Class::DBI Results (single column)\n";
print "    Users Address:      " , $user->address , "\n";

# Verify with traditional_sql
traditional_select();


# This is a bit confusing here since the update has not
# made it to the database, the value exists in the
# object, but until you do 'update' no change is
# passed to the database.  This is important because
# it allows for a pseduo rollback even in databases that
# don't support transactions.

# If we take the object out of scope and recreate it, we
# see that address has lost its value.

undef $user;
$user = Table::User->retrieve(1);

print ">> Class::DBI Results (single column)\n";
print "    Users Address:      " , $user->address , "\n\n";

# Class::DBI is nice enough to print a warning about
# destroying the user object without updating, but the
# app will continue to run.

# Now lets get our page data (there shouldn't be any)
# we are going to put it in a sub since we use it
# several times.

sub print_page_names {
    # it is a good idea to make sure you are
    # working with a valid Class before attempting
    # to use relationship methods, Class::DBI will
    # die if you attempt to use a method on a deleted
    # record.
    if ( $user->isa( 'Table::User' ) ) {
        my @pages = $user->pages;
        print ">> Attempting to print page names\n";
        foreach (@pages) {
            print "    " , $_->name , "\n";
        }
    print "\n";
    } else {
        warn "\nUser object no longer valid\n";
    }
}

print_page_names();

# so that didn't do anything, which is good, this
# shows we can access empty tables and suffer no
# errors.

# Lets add some data to the page table

Table::Page->create( {
    user_id   => 1,
    name      => "Page number 1",
    filename  => "page.html",
} );

# Now lets rerun our pages call above
# It should now print Our Page

print_page_names();

# that worked good so lets add 5 new pages

foreach (2..5) {
    Table::Page->create( {
        user_id   => 1,
        name      => "Page number $_",
        filename  => "page$_.html",
    } );
}

# run our pages code again and show
# all 5 pages

print_page_names();

# Next we want to delete the user, and this should
# have a side effect of removing all the pages.

$user->delete(1);

# Now if we try to print out page names
# we get no output.

print_page_names();

# lets clear up the auto_increment for the next
# run
$dbh->do("ALTER TABLE user AUTO_INCREMENT = 0");
$dbh->do("ALTER TABLE page AUTO_INCREMENT = 0");

print "\nFinished\n";

1;

Conclusion

Class::DBI can be a powerful way of interacting with your databases, but it really requires a large scale project to reap the benefits and be worth the performance hit you take vs. doing traditional SQL queries. When using Class::DBI you are adding dynamic accessors for much of the data and the overhead of the method calls for all your operations. The benefit is less code, referral integrity (cascading delete), rollbacks in non transaction databases, reduction in lines of code, and increased portability since Class::DBI handles the abstraction across multiple database sources.

If you are working on a large scale database centric application or you want to learn more about OO Perl Class::DBI is a good place to start.

Extra

There is a module on CPAN called Class::DBI::Loader, but I was unable to get it working correctly based on the documentation and it did not provide for any identification of table primary keys.

The initial subclass offers only the automated dynamic generation of table classes, if for some reason you would like to create static classes or would like to see what is being created you can use this code instead.

Once you have created that Class file you will need to use it inside of your application instead of the My::ClassDBI module. You can create a minimalistic My::ClassDBI as outlined in the Class::DBI docs using this technique.

=pod

=head1 Purpose

This script will generate the sub classed files required
for working with Class::DBI.

By default the Class is called Table, you can modify it
by passing in a command line argument.

Redirect STDOUT (at the command line is how I do it)
to a file to save the output, like so:

perl create_table_classes.pl > Table.pm

=cut

use DBI;
use strict;

my $base     = "My::ClassDBI";

my $dsn      = 'dbi:mysql:database';
my $user     = 'user';
my $password = 'password';

# create connection to database
my $dbh = DBI->connect($dsn,$user,$password) or die $DBI::errstr;

my $get_tables = $dbh->prepare(qq!SHOW TABLES!);

$get_tables->execute;
my @tables;
while ( my $table = $get_tables->fetchrow ) {

    my @columns;
    my $get_column = $dbh->prepare(qq!DESC $_!);
    $get_column->execute();
    while ( my @cols = $get_column->fetchrow() ) {
        $cols[3] =~ /pri/i ? unshift @columns , $cols[0]
                           : push @columns , $cols[0]
    }

my $col_list = "\t" . join("\n\t",@columns);

print qq!package Table::$table;
use base '$base';
Table::$table->table('$_');
Table::$table->columns(All => qw/
$col_list
/);

1;


!;

}

UPDATE corrected die statement on $dbh creation as suggested by dbwiz