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; #### Table::Tablename->columns( Primary => 'primary_key_column_name' ); #### Table::User->has_many( 'pages' , 'Table::Page' => 'user_id' ); #### # 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; #### 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; #### =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; !; }