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 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('email@domain.com'); $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

Comment on Class::DBI Intro
Select or Download Code
Re: Class::DBI Intro
by dbwiz (Curate) on Jul 30, 2003 at 12:16 UTC

    A few comments.

    • In your connection to DBI, you are testing "$!". Be aware that this won't work as you expect. See the relevant info from DBI docs.
      If the connect fails (see below), it returns "undef" and sets both "$DBI::err" and "$DBI::errstr". (It does not set "$!", etc.) You should generally test the return status of "connect" and "print $DBI::errstr" if it has failed.
    • The delete method is rather inefficient. When called, Class::DBI will issue one DELETE statement for each row in your table. Instead of producing
      DELETE FROM page WHERE user_id = 1 DELETE FROM user WHERE user_id = 1
      It does
      DELETE FROM page WHERE page_id = '1' DELETE FROM page WHERE page_id = '2' DELETE FROM page WHERE page_id = '3' DELETE FROM page WHERE page_id = '4' DELETE FROM page WHERE page_id = '5' DELETE FROM user WHERE user_id = '1'
      which can be quite long for large data sets.
      The sad thing is that, even if I use a database that enforces referential integrity, Class::DBI will still override the database features and issue the same DELETE statements. In your example, if you replace the table definition to use the InnoDB table type, adding
      FOREIGN KEY (user_id) references user (user_id) ON DELETE CASCADE, KEY user_id (user_id) # and replace "MyISAM" with "InnoDB" for both tables

      You can then get the same behavior with

      %dbh->do("DELETE FROM user WHERE user_id=1")

      and the database engine will take care of cascade deletiing the appropriate records in "page".

    • You said nothing about how DBI::Class handles queries with one or more JOIN, which are quite common in a production environment. This article would be much better if you explained how it works. Without it, it seems no more than an interesting toy.

      I think you are looking at Class::DBI from the wrong angle - Class::DBI is not a tool to force existing tables (and their relations) into classes/objects, but to provide some simplicistic way of storing objects in tables.

      Class::DBI isa Ima::DBI and thus has support for adding custom SQL constructors if you need to create an object resp. find an object through a complex query which you don't want/can't reproduce via Perl.

      perl -MHTTP::Daemon -MHTTP::Response -MLWP::Simple -e ' ; # The $d = new HTTP::Daemon and fork and getprint $d->url and exit;#spider ($c = $d->accept())->get_request(); $c->send_response( new #in the HTTP::Response(200,$_,$_,qq(Just another Perl hacker\n))); ' # web
        I think you are looking at Class::DBI from the wrong angle - Class::DBI is not a tool to force existing tables (and their relations) into classes/objects, but to provide some simplicistic way of storing objects in tables.

        My thoughts were the exact opposite -- Class::DBI is an abstraction layer that let's you interact with your tables as objects. Throughout the documentation, when the author refers to 'objects' he generally means 'rows'.

        Not that you can't do that. I'm only saying that Class::DBI is not going to necessarily make it easer to represent class inheritance in a relational system.

        Matt

      I corrected the connection test as you suggested, don't know how that slipped by.

      Your second point is a good one, but eliminates the need for Class::DBI in that context. Class::DBI is not an end all be all for relationship integrity management. Class::DBI can however be used to manage relationships in situations where
      1. The management of relationships has to be done at an application level vs. the database (somewhat database engine agnostic design)
      2. The the database doesn't support more robust features, for which there are numerous examples and converting table format isn't an available solution because *insert subjective reason here*

      "You said nothing about how DBI::Class handles queries with one or more JOIN"

      Yes Class::DBI has many features and transparent SQL is one of them, but they are not within the context of an introduction. I plan to release a mid range tutorial after I complete my present project using Class::DBI. I am open for suggestions and or tidbits of what should be included in that node.

      The delete method is rather inefficient. When called, Class::DBI will issue one DELETE statement for each row in your table.

      Yes. This is a deliberate design decision. The class in question may have explicit behaviours attached to the act of deleting (for example, you can set up triggers at the application level, rather than at the database.) The only way to cause these to be fired is to delete the objects one at a time.

      It's fairly trivial to set up your own method to delete from the database in one pass if you're comfortable doing so. Replacing all your SQL is not really Class::DBI's goal. Its job is to remove all the boring trivial repetitive SQL from your application, freeing you up to spend the time writing the more advanced stuff yourself that can't be easily abstracted away.

      Tony

Re: Class::DBI Intro
by mojotoad (Monsignor) on Jul 30, 2003 at 19:42 UTC
    Nice tutorial. ++

    A number of things I'd be curious to see added:

    • Autocreation of table classes via Class::DBI::mysql and ilk. (Class::DBI::Loader seems to be aimed at mod_perl? If not, how do these differ?)
    • Some exposition on handling joins via the has_many() call with mapping syntax, i.e. from the docs:

      Music::CD->has_many(styles => [ 'Music::StyleRef' => 'style' ]);

      is identical to

      Music::CD->has_many(_style_refs => 'Music::StyleRef'); sub styles { my $self = shift; return map $_->style, $self->_style_refs; }

      (but note that your join table still requires a primary key)

    • Real bridging of object heirarchies into relational databases using toys such as Class::DBI::SAK, which integrate lots of approaches such as that taken by Class::Tangram (or at least, so I hear)

    Good stuff,
    Matt

      (but note that your join table still requires a primary key)

      Actually, it doesn't always. As long as the relationship is unique, the cross-reference itself can be treated as a multi-column primary key, even if it isn't defined as such in the database.

      Strange things can happen with this if you step off the (admittedly very narrow) beaten path, but for general cross-referencing it seems to work just fine.

      Tony

        Actually, it doesn't always. As long as the relationship is unique, the cross-reference itself can be treated as a multi-column primary key, even if it isn't defined as such in the database.

        Can you give an example? I'm still learning about Class::DBI, but thus far I have not found a way to avoid it claiming the first column as a primary key (in the absence of such a declaration in the DB table itself).

        Matt

Re: Class::DBI Intro
by chunlou (Curate) on Jul 30, 2003 at 22:02 UTC

      Kake's perl.com article is good, and covers quite a few other topics as well.

      There was also a perl.com article solely on Class::DBI itself late last year: http://www.perl.com/pub/a/2002/11/27/classdbi.html...

      (although it's slightly out of date by now)

      Tony

Class::DBI related links
by parasew (Beadle) on Sep 26, 2003 at 10:46 UTC