Thursday, December 22, 2011

DBIx::Class

I've recently been looking for a Perl ORM. The 2 current contenders are:

  1. DBIx::Class (DBIC)
  2. Rose::DB::Object (RDO)

Initially, I preferred RDO because it looked easier to learn because it was better documented and its scope was smaller. More importantly, it was built for speed which is a high priority for me right now. The main problem was that it does not work with MS SQL. So that was the end of that.

DBIx::Class was my second choice because it is built for flexibility rather than speed. But with Rose::DB::Object not available for me, this became the default choice. Still it's said to be faster than the older DBI::Class, and there are the documentation has tips for improving speed. And I'm not complaining about having the flexibility. Initially, it did take a little searching around documentation to get it to work, but once I got it working it didn't seem too difficult at all.

Here's some code for a quick start. Begin with the schema class.

package MySchema;
use base 'DBIx::Class::Schema';
__PACKAGE__->load_namespaces;

Define the classes which correspond to tables in the database (assuming they already exist). These classes will be automatically detected when you create the schema object. There is a module that allows you to auto-load these without having to specify them, but the performance is slower.

package MySchema::Result::MyTable;
use base 'DBIx::Class::Core';
__PACKAGE__->table('tablename');
__PACKAGE__->add_columns(qw/ col1 col2 col3 /);
__PACKAGE__->set_primary_key(qw/ col1 col2 /);

Then it's ready to use.

package main;
my $schema = MySchema->connect($dsn, $dbuser, $dbpass, \%attr);

# query the table: WHERE col1 = 1
my $resultset = $schema->resultset('MyTable')->search({ col1 => 1 });

# a cool feature of the result sets is that you can chain queries.

# iterate the rows of the result set
while (my $row = $resultset->next) {
    # use the row as an object with accessors for each column
    print $row->col1, "\n";
}

So far the results set class was autogenerated. But you can subclass your own and create your own custom searches. And a cool thing is that you can then chain these filters.

package AM::Tariff::DB::ResultSet::CallTypes;
use base 'DBIx::Class::ResultSet';

sub filter1 {
    my ($self) = @_;
    return $self->search({ col1 => 5 });
}
sub filter2 {
    my ($self) = @_;
    return $self->search({ col2 => 3 });
}

...

my $rs = $schema->resultset('MyTable')
                ->filter1
                ->filter2;
Pretty cool, huh?