Catalyst and DBIx::Class: Part 1

[For instructions on the installation of Catalyst, please see the Catalyst Manual page on installation.]

This is an attempt at introducing DBIx::Class and using it in Catalyst, so you can be familiar with it enough to start using it.

Understanding ORM

First, you'll need to understand the purpose of Object-relational mapping. It is what DBIx::Class is. To quote the wikipedia.org page:

Object-Relational mapping (aka O/RM, ORM, and O/R mapping) is a programming technique for converting data between incompatible type systems in databases and Object-oriented programming languages. This creates, in effect, a "virtual object database" which can be used from within the programming language.

So, what you end up with is classes that have the ability to look and feel like database tables, yet all read/write operations happen behind the scenes. This enables you to work with the database without having to make database specific calls. This also allows you to code where all you need to worry about in the code is just what to call, not what database is under the hood. DBIx::Class makes this easy and is pretty simple to wrap your brain around.

The basic idea is that you have one controller master class, and then one or more different classes that usually represent each table in the database. The controller master class is where you will specify the database connection information (and other optional settings) and load the table classes.

This may seem complex, yet it makes life much easier and allow your code to be much more flexibly to change. It also fulfills the need of having the Controller layer not be aware of the Model layer details in the Model-View-Controller design pattern (which is most common in serious web applications).

Getting to know DBIx::Class

Please take a moment to go over the examples in the DBIx::Class CPAN page.

Then go over the manuals (they're pretty brief and include code to explain everything): Intro, Example, Joining, Cookbook with interest on prefetch and joins, and Troubleshooting.

These documents do a much better job at explaining how to use DBIx::Class and all it's specifics than I could do. Be sure to check out the documentation map also.

Using DBIx::Class in Catalyst

Please table a moment to go over the Catalyst manual tutorial, the database access with DBIx::Class section. This will get you familiar with the basics.

If you already have an existing database schema and it's in a database, you can have Catalyst create them for you. It's advised that you just do this once, so that you can save some typing initially (if you already have a database with schema in it... like in this example). First make sure you're in your Catalyst root directory, made with this:

$ catalyst.pl MyTestApp

Then you can use the Catalyst create script to create your DBIx::Class classes for you:

$ cd MyTestApp
$ ./script/mytestapp_create.pl model DB DBIC::Schema DB::Schema create=static dbi:Pg:dbname=ticketingsystem chris
 exists "/home/chris/MyTestApp/script/../lib/MyTestApp/Model"
 exists "/home/chris/MyTestApp/script/../t"
Dumping manual schema for DB::Schema to directory /home/chris/MyTestApp/script/../lib ...
Schema dump completed.
created "/home/chris/MyTestApp/script/../lib/MyTestApp/Model/DB.pm"
created "/home/chris/MyTestApp/script/../t/model_DB.t"
$ 

Ok, now let's take a look at what it created:

$ head -13 lib/MyTestApp/Model/DB.pm
package MyTestApp::Model::DB;

use strict;
use base 'Catalyst::Model::DBIC::Schema';

__PACKAGE__->config(
    schema_class => 'DB::Schema',
    connect_info => [
        'dbi:Pg:dbname=ticketingsystem',
        'chris',
        
    ],
);
$ 

Ok, so from looking at the Model class for this database, it seems pretty easy to figure out. It creates a class that is based off Catalyst::Model::DBIC::Schema and sets up some configuration values (what the master DBIx::Class is and the database connection information used by DBI). Ok, now lets look at the DB::Schema file:

$ cat lib/DB/Schema.pm 
package DB::Schema;

# Created by DBIx::Class::Schema::Loader v0.03009 @ 2007-09-04 19:26:55

use strict;
use warnings;

use base 'DBIx::Class::Schema';

__PACKAGE__->load_classes;

1;

$ 

Hrm, even simplier right? '__PACKAGE__->load_classes' subroutine loads up all the configured schema classes in the lib/DB/Schema/ directory. Let's look at one:

$ cat lib/DB/Schema/Status.pm 
package DB::Schema::Status;

# Created by DBIx::Class::Schema::Loader v0.03009 @ 2007-09-04 19:26:55

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("PK::Auto", "Core");
__PACKAGE__->table("status");
__PACKAGE__->add_columns(
  "id",
  {
    data_type => "integer",
    default_value => "nextval('status_id_seq'::regclass)",
    is_nullable => 0,
    size => 4,
  },
  "name",
  {
    data_type => "character varying",
    default_value => undef,
    is_nullable => 0,
    size => 40,
  },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->has_many(
  "users_ticket_status_logs",
  "DB::Schema::UsersTicketStatusLog",
  { "foreign.status_id" => "self.id" },
);
__PACKAGE__->has_many(
  "staff_ticket_status_logs",
  "DB::Schema::StaffTicketStatusLog",
  { "foreign.status_id" => "self.id" },
);
__PACKAGE__->has_many(
  "tickets",
  "DB::Schema::Ticket",
  { "foreign.status_id" => "self.id" },
);

1;

This was obviously generated from PostgreSQL, as you can note the default_value for "id". Anyways, without focusing too much on that, the important parts are all the function calls ('has_many', 'set_primary_key', 'add_columns', 'table', and 'load_components'). Here is a breakdown on what they all are for:

• load_components: Loads the components you need, which should almost always be the same (PK::Auto and Core) unless you need custom components detailed in the DBIx::Class component manual. [technically I guess you don't need PK::Auto as it's in Core now]


• table: obviously sets the name of the table


• add_columns: defines the columns of the table


• set_primary_key: sets the primary key of the table


• has_many: one of a few relationship definition functions for the table, see more about them in detail here.

It's pretty simple once you read the documentation links I gave. The naming of everything is pretty intuitive, so makes wrapping your brain around what's going on pretty easy.

Summary

So you should now have an idea of what DBIx::Class' purpose is and some details about it's use. You should also know where to look for documentation on each part discussed here and have a good general knowledge of DBIx::Class' implementation of ORM.

You should have a simple understanding of DBIx::Class and Catalyst. I'll continue in another article on more details and dive more into using DBIx::Class in Catalyst Controllers and using multiple databases.

Hopefully this was useful to you. I'd like to keep going, yet it's more appropriate to break this up into parts. If I made any mistakes or feel I should have written something in a better way, let me know and I'll be sure to update!

Enjoy playing with Catalyst and DBIx::Class!