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;