.
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);
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.
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.
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
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
Let's continue by creating a template for displaying the most recent blog posts, called root/recent.tt:
<html> <head><title>Recent blog posts</title></head> <body> <h1>Blog</h1> [% FOREACH post = posts %] <h2>[% post.title | html %]</h2> <i>Written on [% post.created | html %]</i> [% post.body %] [% END %] </body> </html>
Finally,
let's replace the default index action with one that gets the posts
from the model and then displays them inside the recent.tt template. In
Blog::Controller:: Root, we'll replace default with the following code:
sub default : Private { my ( $self, $c ) = @_; $c->stash->{template} = 'recent.tt'; #$c->stash->{posts} = [$c->model('Filesystem') # ->get_recent_posts()]; }
Note
that we've commented out the line where we get the posts, since we
haven't implemented the get_recent_posts method yet. You should be able
to start the application now and see the beginnings of a blog when you
visit.
All that's left to do is implement
the model. This model will take a two-tiered approach. The actual
Catalyst model will find all "posts" and will create a
Blog::Model::Filesystem::Post object for each. These objects will do
most of the work—reading the file, etc. Let's start by creating the
Post class by writing lib/Blog/Model/Filesystem/Post.pm:
# Post.pm package Blog::Model::Filesystem::Post; use strict; use warnings; use Carp; use File::Basename; use File::Slurp qw(read_file); use File::CreationTime qw(creation_time); use base 'Class::Accessor'; __PACKAGE__->mk_ro_accessors(qw(filename)); sub new { my $class = shift; my $filename = shift; croak "Must specify a filename" unless $filename; my $self = {}; $self->{filename} = $filename; bless $self, $class; return $self; } sub title { my $self = shift; my $filename = $self->filename; my $title = basename($filename); $title =~ s/[.](w+)$//; # strip off .extensions return $title; } sub body { my $self = shift; return read_file($self->filename); } sub created { my $self = shift; return creation_time($self->filename); } sub modified { my $self = shift; return (stat $self->filename)[9]; # 9 is mtime } 1;
This is
a pretty standard Perl class. The new method takes a filename and
creates an instance of this class based on the filename. The rest of
the methods access the file and return the desired information. Because
of the way we've designed the class, it will be extremely simple to add
more information to each blog post in the future. We'll just create
another method, and the information will be easily available to the
controller and the template.
Now we need to create the actual
Catalyst model that will find blog posts and return instances of the
above Post object. To start with, we'll just need to add the
get_recent_posts method in the following manner:
package Blog::Model::Filesystem; use strict; use warnings; use base 'Catalyst::Model'; use Carp; use File::Spec; use Blog::Model::Filesystem::Post; __PACKAGE__->mk_accessors('base'); sub get_recent_posts { my $self = shift; my $base = $self->base; my @articles; opendir my $dir, $base or croak "Problem opening $base: $!"; while(my $file = readdir $dir){ next if $file =~ /^[.]/; # skip hidden files my $filename = File::Spec->catfile($base, $file); next if -d $filename; push @articles, Blog::Model::Filesystem::Post->new($filename); } closedir $dir; @articles = reverse sort {$a->created <=> $b->created} @articles; return @articles if @articles < 5; return @articles[0..4]; # top 5 otherwise }
The mk_accessors
line is especially important — this will allow you to specify a "base"
attribute in the config file, which will then be available in the rest
of the methods as $self->base. Here's the config file, blog.yml:
--- name: Blog Model::Filesystem: base: /tmp/test
Now
all you need to do is remove the comment from the line in Root.pm and
then add some HTML files to /tmp/test. When you start your server, you
should see the 5 most recent posts displayed!
(If you're
interested in taking this idea further, check out the Angerwhale
blogging system, available from the CPAN. It uses a similar
filesystem-based model, but one that has many more features.)
With
the core functionality in place, we can now dig a bit deeper into the
model and add some more features. The first one is a sort of "user
interface" improvement. Instead of making the user type out the
Model::Filesystem part in the config file, it would be nice to just
specify "base" and have that take the effect in the same way. We can
achieve this by reading the value of $c->config->{base} into
$c->config->{Model::Filesystem}->{base} just before Catalyst
creates an instance of the class. This is done by overriding the
COMPONENT method in the model. The COMPONENT method is called to setup
things like configuration right before the new method is called (things
like database connections are set up). We can override this in our
model, tweak the config, and then call the version of COMPONENT in
Catalyst::Model to finish everything up:
sub COMPONENT { my ($class, $app, $args) = @_; $args->{base} = $app->config->{base}; return $class->NEXT::COMPONENT($app, $args); }
We call $c $app at this
point, because $c usually means that there is a request occurring,
whereas $app is just the static data that "is" your application. The
current version of Catalyst lets you treat these objects the same way,
but Catalyst 5.8 will distinguish between them. This will allow
Catalyst applications to embed each other, among other things.
With this code in place, we can change the config file to:
--- base: /tmp/test name: Blog
The
last feature we'll add is validation of "base" in the new method. This
will check to see if the base directory exists, and if it does not,
issue an error message and prevent the application from starting:
sub new { my $class = shift; my $self = $class->NEXT::new(@_); # get the real self my $base = $self->base; croak "base $base does not exist" if !-d $base; return $self; }
If
you're inheriting from a base class, you can control whether or not
your code runs before that of your base class by choosing whether to
run your code before or after the NEXT::new() call. NEXT::new() is
where the superclasses get a chance to set themselves up, then control
is passed back to you. You should return the result of NEXT::new() from
the new method.
Generally,
your model's configuration won't change as requests run. When Catalyst
is started, your model is initialized and it doesn't see the rest of
your application again. This means that you can't save the $app you got
from COMPONENT and use it to, say, access $c->request or
$c->response in the future. It's generally a good idea to avoid
touching the request from inside a model (that's what the Controller is
for) anyway, but if you absolutely need to, you can get the latest $c
by implementing an ACCEPT_CONTEXT method in your model. It's called by
Catalyst every time you call $c->model() and is passed $c and any
arguments are passed to $c->model(). In general, it will look
something like this:
__PACKAGE__->mk_accessors(qw|context|); # at the top sub ACCEPT_CONTEXT { my ($self, $c, @args) = @_; $self->context($c); return $self; }
We return $self from ACCEPT_CONTEXT here,
but in theory you can return anything. The value is passed directly
back to the caller of $c->model(). DBIC::Schema takes advantage of
this feature to return individual resultsets instead of the entire
schema depending on how $c->model() is invoked.
After you've
added an ACCEPT_CONTEXT method like the above, you can call
$self->context() anywhere in your model to get the current request
context.
When you're writing your own
data model for use with Catalyst, you might want to consider making it
work without Catalyst first, and then later adding some glue to make it
easy to use from within Catalyst. The advantage of this approach is
that you can test your model without having to have a Catalyst app to
use it and that you can use your data model class in non-Catalyst
applications. DBIx::Class takes this approach, the DBIx::Class::Schema
works fine without Catalyst. The DBIC model you create for your
application is just a bit of glue to make using the DBIx::Class::
Schema from Catalyst convenient.
Let's take a look at how we
would build the Filesystem model in this manner. First, we'll move the
Blog::Model::Filesystem::Post class to the Blog::Backend::
Filesystem::Post namespace. Then, we'll write our post access code in
Blog:: Backend::Filesystem instead of Blog::Model::Filesystem. The code
is exactly the same, except we'll write our own new method:
package Blog::Backend::Filesystem; use strict; use warnings; use Carp; use Blog::Backend::Filesystem::Post; sub new { my ($class, $args) = @_; # args is { base => 'path' } croak 'need a base that exists' if !-d $args->{base}; return bless $args, $class; } # then the same as Blog::Model::Filesystem above, substituting # Blog::Backend::Filesystem::Post for # Blog::Model::Filesystem::Post.
Now
you have a class that you can use to access blog posts from outside of
Catalyst. Just instantiate it like my $post_model =
Blog::Backend::Filesystem- >new({ base => '/var/blog' }) and then
use $post_model like you did $c- >model('Filesystem') above.
The
final step is to create the glue to bind the backend class to a
Catalyst model. Fortunately, Catalyst::Model::Adaptor, a module on
CPAN, will do that for us automatically by running the following
command line:
$ perl script/blog_create.pl model Filesystem Adaptor Blog::Backend:: Filesystem
That command will create a Model called
Blog::Model::Filesystem which simply returns a
Blog::Backend::Filesystem object when you call
$c->model('Filesystem'). It works by creating a subclass of
Catalyst::Model:: Adaptor, which will create an instance of your
backend class at startup and return it when needed.
One disadvantage is that the configuration format changes slightly:
--- Model::Filesystem: args: base: /var/blog
If you want to avoid the unsightly args key, you can override prepare_arguments in the Model like this:
package Blog::Model::Filesytem; # generated code here sub prepare_arguments { my ($self, $app) = @_; return { base => $app->{base} }; }
Now the adapted Filesystem Model will work exactly like the one we made earlier, but with very little Catalyst-specific code.
If
you are writing a Model that needs a new backend class to be created
every time you call $c->model or once per request (instead of once
per application), you can use the Catalyst::Model::Factory and
Catalyst::Model::Factory::PerRequest modules included with
Catalyst::Model::Adaptor. They are all used in the same way (as above,
substituting Factory or Factory::PerRequest for Adaptor), but integrate
your backend class with Catalyst in slightly different ways. For most
cases, these Models will be all you need.
Models
are just the tip of the iceberg — Views and Controllers work the same way
(and implement the same methods) as Models. You can easily create
custom Views and Controllers and inherit from them in your application
to improve the reusability of your application's code.
In
this article, we looked at alternate ways to access the data model. We
first added the ability to execute raw SQL to a standard DBIC model,
and contrasted the code with normal DBIC resultset access. Then, we
completely eliminated DBIC and wrote a database Model that used DBI to
run raw SQL on the database. Next, we created a Filesystem Model from
scratch, and learned how to use COMPONENT and ACCEPT_CONTEXT to
integrate the Model with the Catalyst application. We also saw how
using Catalyst::Model::Adaptor made writing a maintainable Model easier.
Catalyst
Building Powerful and Robust Websites with Drupal 6
Building Websites with Joomla! 1.5
WordPress Complete
Xen Virtualization
EJB 3 Developer Guide
Oracle Modernization Solutions
SOA Cookbook