Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

trs80's scratchpad

by trs80 (Priest)
on Jun 01, 2004 at 22:22 UTC ( [id://358708]=scratchpad: print w/replies, xml ) Need Help??

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 gather 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. 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 )
The first thing you need to be familiar with is databases. All applications that would utilize the Class::DBI module use SQL bases 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.

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.

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.

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 Programming in General
OO programming is (in my understanding) designed to allow us to deal with aspects of programming as devices, aka Objects. These devices have handles (methods) on them and each handle produces a specific result and these handles can be grouped and they can be borrowed (inherited) by other Objects. OO programming promotes code reuse, or at least it did for me.

Having a firm grasp on OO programming is a Good Idea(tm) for almost anyone in modern programming and as we move toward Perl 6 it will become increasing important.
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 Class::DBI appear in one file that you create. We will start by addressing that one file.

For this example we will create My::ClassDBI ( if you don't know how you need to read more on (OO) Perl modules ).

Our My::ClassDBI module is going to contain some code to make our lives easier when working with Class::DBI, generally you would have to create a series of packages that contain information on each table, this can be tedious in some applications, however there are times when you want to skip the automation in which case you simply follow the suggestions provided in the Class::DBI documentation. More on that below. Here is an example of how to create your subclass of Class::DBI and include automatic package creation. ( these examples are based on MySQL, but should be adaptable to other SQL engines )
package My::ClassDBI; use strict; use warnings; use base 'Class::DBI'; my $dsn = 'dbi:mysql:database'; my $user = 'user'; my $password = 'password'; My::ClassDBI->set_db('Main', $dsn, $user, $password); # auto load tables my $base = __PACKAGE__; # "My::ClassDBI"; # create connection to database my $dbh = DBI->connect($dsn,$user,$password) or die $DBI::errstr; # not the Right Way(tm) but works for now my $get_tables = $dbh->prepare(qq!SHOW TABLES!); $get_tables->execute; my @tables; while ( my $table = $get_tables->fetchrow ) { my @columns; my $ucftable = ucfirst($table); my $get_column = $dbh->prepare(qq!DESC $table!); $get_column->execute(); while ( my @cols = $get_column->fetchrow() ) { # force the primary key to be first # this insures Class::DBI correctly relates table # without having to manually define the Primary Key $cols[3] =~ /pri/i ? unshift @columns , $cols[0] : push @columns , $cols[0] } eval qq!package Table::$ucftable; use base '$base'; Table::$ucftable->table('$table'); Table::$ucftable->columns(All => qw/! . join(" ",@columns) . "/);"; } # this bit of code is explained below Table::User->has_many( 'pages' , 'Table::Page' => 'user_id' ); 1;
That module will create all the table packages you need automatically and properly place the primary key in the first position on the list of columns. The package name for each of the tables becomes:

You can name that 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 collision 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 an 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, date_of_birth varchar(255) default NULL, occupation varchar(255) default NULL, is_active tinyint(4) NOT NULL default '0', last_visited_on datetime default NULL, created_on datetime default NULL, modified_on timestamp(14) NOT NULL, email varchar(75) default NULL, city varchar(75) default NULL, state varchar(75) default NULL, country varchar(255) default NULL, postal_code varchar(75) default NULL, phone_number varchar(75) default NULL, organization varchar(255) default NULL, address varchar(200) default NULL, PRIMARY KEY (user_id) ) TYPE=MyISAM; CREATE TABLE page ( page_id int(11) unsigned NOT NULL auto_increment, name varchar(255) default NULL, created_on datetime NOT NULL default '0000-00-00 00:00:00', modified_on timestamp(14) NOT NULL, filename varchar(255) NOT NULL default '', PRIMARY KEY (page_id) ) TYPE=MyISAM;
Here is the test application code:
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 $! , "\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(''); $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("DELETE FROM user"); $dbh->do("DELETE FROM page"); print "\nFinished\n"; 1;
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.

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 > =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; !; }
Log In?

What's my password?
Create A New User
Domain Nodelet?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others sharing their wisdom with the Monastery: (4)
As of 2024-07-17 17:16 GMT
Find Nodes?
    Voting Booth?

    No recent polls found

    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.