Extending a DBIx::Class Model
A common occurrence is a situation in which your application has free reign over most of the database, but needs to use a few stored procedure calls to get at certain pieces of data. In that case, you’ll want to create a normal DBIC schema and then add methods for accessing the unusual data.
As an example, let’s look back to the AddressBook application and imagine that for some reason we couldn’t use DBIx::Class to access the user table, and instead need to write the raw SQL to return an array containing everyone’s username. In AddressBook::Model::AddressDB, we just need to write a subroutine to do our work as follows:
package AddressBook::Model::AddressDB;
// other code in the package
sub get_users {
my $self = shift;
my $storage = $self->storage;
return $storage->dbh_do(
sub {
my $self = shift;
my $dbh = shift;
my $sth = $dbh->prepare('SELECT username FROM user');
$sth->execute();
my @rows = @{$sth->fetchall_arrayref()};
return map { $_->[0] } @rows;
});
}
Here’s how the code works. On the first line, we get our DBIC::Schema object and then obtain the schema’s storage object. The storage object is what DBIC uses to execute its generated SQL on the database, and is usually an instance of DBIx:: Class::Storage::DBI. This class contains a method called dbh_do which will execute a piece of code, passed to dbh_do as a coderef (or “anonymous subroutine”), and provide the code with a standard DBI database handle (usually called $dbh). dbh_do will make sure that the database handle is valid before it calls your code, so you don’t need to worry about things like the database connection timing out. DBIC will reconnect if necessary and then call your code. dbh_do will also handle exceptions raised within your code in a standard way, so that errors can be caught normally.
The rest of the code deals with actually executing our query. When the database handle is ready, it’s passed as the second argument to our coderef (the first is the storage object itself, in case you happen to need that). Once we have the database handle, the rest of the code is exactly the same as if we were using plain DBI instead of DBIx::Class. We first prepare our query (which need not be a SELECT; it could be EXEC or anything else), execute it and, finally, process the result. The map statement converts the returned data to the form we expect it in, a list of names (instead of a list of rows each containing a single name). Note that the return statement in the coderef returns to dbh_do, not to the caller of get_users. This means that you can execute dbh_do as many times as required and then further process the results before returning from the get_users subroutine.
Once you’ve written this subroutine, you can easily call it from elsewhere in your application:
my @users = $c->model('AddressDB')->get_users;
$c->response->body('All Users' join ', ', @users);
Custom Methods Without Raw SQL
As the above example doesn’t use any features of the database that DBIC doesn’t explicitly expose in its resultset interface, let us see how we can implement the get_users function without using dbh_do. Although the preconditions of the example indicated that we couldn’t use DBIC, it’s good to compare the two approaches so you can decide which way to do things in your application. Here’s another way to implement the above example:
sub get_users { # version 2
my $self = shift;
my $users = $self->resultset('User');
my @result;
while(my $user = $users->next){
push @result, $user->username;
}
return @result;
}
This looks like the usual DBIC manipulation that we’re used to. (Usually we call $c->model(‘AddressDB::User’) to get the “User” resultset, but under the hood this is the same as $c->model(‘AddressDB’)->resultset(‘User’). In this example, $self is the same as $c->model(‘AddressDB’).)
The above code is cleaner and more portable (across database systems) than the dbh_do method, so it’s best to prefer resultsets over dbh_do unless there’s absolutely no other way to achieve the functionality you desire.
Calling Database Functions
Another common problem is the need to call database functions on tables that you’re accessing with DBIC. Fortunately, DBIC provides syntax for this case, so we won’t need to write any SQL manually and run it with dbh_do. All that’s required is a second argument to search. For example, if we want to get the count of all users in the user table, we could write (in a controller) the following:
$users = $c->model('AddressDB::User');
$users->search({}, { select => [ { COUNT => 'id' } ],
as => [ 'count' ],});
$count = $users->first->get_column('count');
This is the same as executing SELECT COUNT(id) FROM user, fetching the first row and then setting $count to the first column of that row.
Note that we didn’t specify a WHERE clause, but if we wanted to, we could replace the first {} with the WHERE expression, and then get the count of matching rows. Here’s a function that we can place in the User ResultSetClass to get easy access to the user count:
sub count_users_where {
my $self = shift;
my $condition = shift;
$self->search($condition,
{ select => [ { COUNT => 'id' } ],
as => [ 'count' ], });
my $first = $users->first;
return $first->get_column('count') if $first;
return 0; # if there is no "first" row, return 0
}
Now, we can write something like the following:
$jons = $c->model('AddressDB::User')->
count_users_where([ username => {-like => '%jon%'}]);
to get the number of jons in the database, without having to fetch every record and count them.
If you only need to work with a single column, you can also use the DBIx::Class:: ResultSetColumn interface.
Creating a Database Model from Scratch
In some cases, you’ll have no use for any of DBIC‘s functionality. DBIC might not work with your database, or perhaps you’re migrating a legacy application that has well-tested database queries that you don’t want to rewrite. In this sort of situation, you can write the entire database model manually.
In the next example, we’ll use Catalyst::Model::DBI to set up the basic DBI layer and the write methods (like we did above) to access the data in the model. As we have the AddressBook application working, we’ll add a DBI model and write some queries against the AddressBook database.
First, we need to create the model. We’ll call it AddressDBI:
$ perl script/addressbook_create.pl model AddressDBI DBI DBI:SQLite:
database
When you open the generated AddressBook::Model::AddressDBI file, you should see something like this:
package AddressBook::Model::AddressDBI;
use strict;
use base 'Catalyst::Model::DBI';
__PACKAGE__->config(
dsn => 'DBI:SQLite:database',
user => '',
password => '',
options => {},
);
1; # magic true value required
Once you have this file, you can just start adding methods. The database handle will be available via $self->dbh, and the rest is up to you. Let’s add a count_users function:
sub count_users {
my $self = shift;
my $dbh = $self->dbh;
my $rows = $dbh->
selectall_arrayref('SELECT COUNT(id) FROM user');
return $rows->[0]->[0]; # first row, then the first column
}
Let’s also add a test Controller so that we can see if this method works. First, create the Test controller by running the following command line:
$ perl script/addressbook_create.pl controller Test
And then add a quick test action as follows:
sub count_users : Local {
my ($self, $c) = @_;
my $count = $c->model('AddressDBI')->count_users();
$c->response->body("There are $count users.");
}
You can quickly see the output of this action by running the following command line:
$ perl script/addressbook_test.pl /test/count_users
There are 2 users.
The myapp_test.pl script will work for any action, but it works best for test actions like this because the output is plain-text and will fit on the screen. When you’re testing actual actions in your application, it’s usually easier to read the page when you view it in the browser.
That’s all there is to it—just add methods to AddressDBI until you have everything you need.
The only other thing you might want to do is to add the database configuration to your config file. It works almost the same way for DBI as it does for DBIC::Schema:
---
name: AddressBook
Model::AddressDBI:
dsn: "DBI:SQLite:database"
username: ~
password: ~
options:
option1: something
# and so on
# the rest of your config file goes here
Implementing a Filesystem Model
In this final example, we’ll build an entire model from scratch without even the help of a model base class like Catalyst::Model::DBI. Before you do this for your own application, you should check the CPAN to see if anyone’s done anything similar already. There are currently about fifty ready-to-use model base classes that abstract data sources like LDAP servers, RSS readers, shopping carts, search engines, Subversion, email folders, web services and even YouTube. Expanding upon one of these classes will usually be easier than writing everything yourself.
For this example, we’ll create a very simple blog application. To post the blog, you just write some text and put it in a file whose name is the title you want on the post. We’ll write a filesystem model from scratch to provide the application with the blog posts.
Let’s start by creating the app’s skeleton:
$ catalyst.pl Blog
After that, we’ll create our Filesystem model:
$ cd Blog
$ perl script/blog_create.pl model Filesystem
We’ll also use plain TT for the View:
$ perl script/blog_create.pl view TT TT
