In the last few chapters, we've seen how DBIx::Class can provide a powerful interface to your data. However, sometimes DBIx::Class is not the right tool for the job. Often situations may arise in which your application won't be able to access database tables directly and instead you'll need to access data through predefined stored procedures. In this case, DBIx::Class would be useless as you aren't able to read and modify objects with the usual SELECT, INSERT, UPDATE, and DELETE command set—everything must be done by calling a procedure and reading back the result.
In other cases, your data won't be in a database at all. Instead, you might choose to store and retrieve information from files in a directory.
In this chapter, we'll cover three common cases—mixing a procedural interface with a relational DBIx::Class interface, writing a database interface without DBIx::Class, and building a custom Model that doesn't use a database at all. The number of extensions for DBIC (DBIx::Class), and even its own functionality is still growing. However, it is always a good idea to check CPAN before writing your own model.
In some cases, you'll have no use of 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, which 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) 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 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 as follows:
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. Firstly, create the Test Controller by running the following command:
$ perl script/addressbook_create.pl controller Test
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:
$ 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 such test actions 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. If you need to protect sensitive information, you can check $c->debug to see if you are in debug mode, and only provide the information in that case.
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, like we did in Chapter 4, Expanding the Application. 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