Sorting Perl with Class::DBI

Question

You have the following table called Pets:

name     age     pet
------------------------
Carol     25     null
Stean     23     cat
Mel       24     dog
Rich      24     rabbit

In a MySQL database on the server mydbserver with the user of 'user' with a password of 'password'.

Do the following:

1) Create a Class::DBI connection to this database with the above credentials ( DBI.pm ).

2) Create a Class for the table Pets ( Pet.pm )

3) Create a program that prints all the names of people in the Pets table and what kind (if any ) of pet he/she has sorted by name then age.

Here is the code I wrote.....

#!/usr/bin/perl      
package Pet::DBI;
use DBI;
use strict;
use base 'Class::DBI';
Pet::DBI->set_db('Main','dbi:mysql:dname', 'user', 'password') 

or die $DBI::errstr "\n";
1;

package Pet::Pets;
use base 'Pet::DBI';
use strict;
use warning;
Pet::Pets->table('Pets');
Pet::Pets->columns(All => qw/name age pet/);
1;

use Pet::Pets;
my @pets = Pet::Pets->retrieve_all; 
for (sort {$a->name cmp $b->name} || {$a->age <=> $b->age}  @Pets) {
print "Name:".$_->name ' => '."Age". $_->age"\n";
}

1;

Answer 1

It's basically correct, but there's a number of small problems.

It's not necessary to load DBI, Class::DBI will take care of that for you.

You should be using connection instead of set_db("Main", ...). set_db comes from Ima::DBI and it's not polite (or necessary) to peek under the hood like that.

Although this isn't directly documented in Class::DBI (it should be), its inherited from Ima::DBI, there's no need to check for DBI errors. RaiseError is on and if the connection fails it will throw an error.

You have a typo, use warning; instead of use warnings;.

Unless you have stitched three files together for the post, if the code is all in one file the 1; statements do nothing. use Pet::Pets will not work because there is no Pet/Pets.pm file. You don't have to use a class which is already in the same file.

In general, avoid using $_ if you don't have to, too many things can quietly use or change it. Instead, give the for loop a proper variable like for my $person.

sort only takes one block, but you're basically correct. It should be sort { ($a->name cmp $b->name) || ($a->age <=> $b->age) } @Pets

To avoid reading the whole, potentially very large, table into memory, the sorting should really be done in the database with an ORDER BY name ASC, age ASC and then retrieved a row at a time using an iterator. Unfortunately, retrieve_all does not support any options. You can use retrieve_from_sql to add arbitrary SQL to the end of the basic SELECT. my $all_people = Pet::Pets->retrieve_from_sql("ORDER BY name ASC, age ASC"); Then your data will already be sorted and can be read a row at a time. while( my $person = $all_people->next ) { ... }

You're missing a . in "Age". $_->age"\n".

Null values in a database come back as undef. You'll want to check if $_->pet is defined and if not use some other string like "no pet" or just a blank "".

You're printing the person's age, the question asks for their pet.

Otherwise, it should work.

But really, tell whomever gave you this homework to stop telling people to use Class::DBI. They can email me if they like, schwern@pobox.com.