As you start using the address book, you might want to import and export data in a standard format. You could add a web interface for this functionality, but sometimes it's easier to just write a quick shell script to parse some outside data and enter it into our database. Thanks to the flexibility of Catalyst, scripts running outside of the main application can use the same Model (and even other components, if you can dream up a use for them) as the application and manipulate the database directly.

Using the DBIx::Class schema externally is pretty straightforward. All we need to do is use the Schema, connect to it, and add the data.

Let's write a script in the script directory that will accept lines of CSV-formatted data (for example, firstname, lastname, Home, 123 Fake Street, 123-456-7890, [email protected]) and add them to the database. We start our script by creating it in script/import_csv.pl and writing a bit of documentation:

#!/usr/bin/env perl
use strict;
use warnings;
=head1 NAME
import_csv.pl - imports CSV files into the address book
=head1 FORMAT
The data should be in CSV format with the following fields:
firstname, lastname, address location, address, phone, email
=cut

Next, we'll load the Text::CSV_XS module to parse the CSV data for us as follows:

use Text::CSV_XS;

Here's the tricky part:

use FindBin qw($Bin);
use Path::Class;
use lib dir($Bin, '..', 'lib')->stringify;

We want our application to work on any platform and be able to find the configuration file so that we can find the database automatically. We start by using the FindBin module to find the filename of our script (placed in the $Bin variable). As we put the script in script/import_csv.pl and our Catalyst application is rooted one level above, we use the Path::Class module to generate the filename of the lib directory that's one level above the location of our script. We use Path::Class instead of just writing $Bin/../lib, so that the script will run under operating systems other than UNIX. Once we have a filename, we pass it to the lib pragma to tell Perl to look for modules (our Schema) in that path.

Now we can safely load the schema and YAML to parse the config file:

use AddressBook::Schema::AddressDB;
use Config::JFDI;
use strict;

We are using Config::JFDI to load values from the configuration file. Config::JFDI replaces __HOME__ with the relevant path just like Catalyst::ConfigLoader. For more details on this module, you can read http://search.cpan.org/~rkrimen/Config-JFDI-0.064/lib/Config/JFDI.pm.

my $filename = file($Bin, '..', 'addressbook.conf'),
my $config = Config::JFDI->new(path => $filename);
my $dsn = $config->get->{'Model::AddressDB'}->{connect_info};

Our database's connection information in now in $dsn, and we can simply connect to the schema:

my $schema =
AddressBook::Schema::AddressDB->connect($dsn)
or die "Failed to connect to database at $dsn";

From there, everything works as it did inside our application, with a few caveats. Instead of using $c->model('AddressDB::TableName'), we write $schema->resultset("TableName").

while(my $line = <>){
eval {
my $csv = Text::CSV_XS->new();
$csv->parse($line) or die "Invalid data";
my ($first, $last, $location, $address, $phone, $email)
= $csv->fields();
my $person = $schema->resultset('People')->
find_or_create({ firstname => $first, lastname => $last,
});
$schema->resultset('Addresses')->
create({ person => $person, location => $location, postal => $address, phone => $phone, email => $email,
});
print "Added @{[$person->name]}'s $location address.
";
};
if($@){
warn "Problem adding address: $@";
}
}

The main loop is pretty simple. We first use Text::CSV_XS to parse the current line (STDIN or files specified on the command line) into the fields—first name, last name, location, postal address, phone number, and e-mail address. We then use the first name and last name to locate an entry in the people table (or create it if it doesn't exist). Then we use that person object to create an appropriate row in the address table, using the rest of the data. If that works, we print out a message saying that we added the user and then move on to the next line in the data file.

We also wrap the entire procedure in an eval{} block so that we can print an error message and move on to the next piece of data if there's an error.

There's one more thing we need to do before we can run this. The find_or_create method will only work if you guarantee to DBIx::Class that each firstname and lastname combination is unique (as we are using this constraint to add new addresses to the same person). We do this by creating a UNIQUE constraint in the database and then adding the following line to AddressBook::Schema::Result::People:

__PACKAGE__->add_unique_constraint(name =>
[qw/firstname lastname/]);

This tells DBIx::Class that the columns firstname and lastname, taken together, are unique (like a primary key). We name this constraint name so that we can distinguish between it and other constraints.

After you add that line to your schema, you should be able to run the application:

$ perl script/import_csv.pl

Test,Person,Home,123 Home St.,123-456-7890,[email protected]

Test,Person,Work,123 Work ST.,890-123-4567,[email protected]

^D
$

Now when we visit the website, we'll see a Test Person with a Work and Home address! As you can see, sometimes it's very convenient to access your application without a web interface. Some infrequent operations, like reporting or bulk imports, are easier to perform from the command line.

We'll conclude with a script to generate some random CSV data:

#!/usr/bin/perl
=head1 NAME
random_addresses.pl generate random addresses
=head1 USAGE
Run like C<perl script/random_addresses.pl | perl script/import_csv. pl>
=cut
my @names = qw(Foo Bar Baz Test Jonathan Rockway Person Another A);
my @locations = qw(Home Work Mobile Fax Test);
my @streets = qw(Green Oak Elm 60th Fake State Halsted);
for(1..200){
my $first = $names[rand @names];
my $last = $names[rand @names];
my $where = $locations[rand @locations];
my $number = int rand 9900 + 100; # 3-digit street number my $street = $streets[rand @streets];
my $address = "$number $street St.";
my $phone = join '-', (int rand 800 + 100, int rand 899 + 100, int rand 8999 + 1000);
my $email = "$first.$last@$where.example.com";
print "$first,$last,$where,$address,$phone,$email
";
}

Then we can run:

$ perl script/random_addresses.pl | perl script/import_csv.pl

When the last command finishes, you'll have 200 new addresses in your database—just in time for the next section on searching!

Thanks to the import script, our application now has all the power of the UNIX command line. Anything that can generate CSV data can now communicate with the application. (You can also try opening up the generated random addresses in your spreadsheet program; it should work fine.)