DBIx::Class

by ByWater Staff on Oct 10, 2012

I wanted to take a moment and explain a patch that I submitted recently to assist my work with the calendar. The patch adds the ability for Koha to utilize DBIX::Class. I am going to gear this post mostly toward Koha developers so that they don’t have to go through a lot of the trouble that I went through to get my code working.

Basic

First things first we need to know how to load up a class object. It is relatively similar to initializing a DBI handle.

my $schema = C4::Context->schema;

After you have you new schema object you need to do some work with it. For simple queries you can just perform lookups based on the id:

my $resultset = $schema->resultset("ClassName")->find($id);

There are a few things I should explain from above. First, the ClassName is always the singular form of your database table. If you need to verify you can look in /Koha/Schema/ResultSet/ClassName.pm. Second, the search will return a ResultSet object. You can find ways to manipulate ResultSet data here.

Advanced

For more advanced querying you can use the search syntax.

my $resultset = $schema->resultset("ClassName")->search({

column_one => $var1,
column_two => $var2,

});

This will run the query:

Select * from ClassName where column_one = ? and column_two = ?

where the ?’s will be replaced by your variables.

Now lets say you have a more complex searching need still. Perhaps you need to use OR’s or a combination of OR’s and AND’s. Simple enough:

my $resultset = $schema->resultset("ClassName")->search({

-and => [

-or => [

column_one => $var1,
column_two => $var2,

],

-between => [

timestamp1 => $time,
timestamp2 => $now,

],

column_three => $var3,

]

});

As you can see, queries can be as complex as you’d like. All options that are available in your RDBMS are available to you in DBIx::Class.

Join or Prefetch

I’m sure you are very familiar with joins from your RDBMS but if you are anything like me prefetch was very new. The join syntax and the prefetch syntax are basically the same and require the same setups to complete.

Before you can perform a join you need to define what to join on in the Class file for your table. Note that all the class files have been generated by DBIx::Class::Schema:Loader and are included in my patch. If you’d like to add a joining operation look at the bottom of the file after the MD5 hash and add:

__PACKAGE__->has_many( somethings => 'Koha::Schama::Result::ClassName', 'something_id');

This will add a one to many relationship that that you can use in your queries. See the example below for a join operation.

Select syntax with join looks something like this:

my $resultset = $schema->resultset("ClassName")->search({

column_one => $var1,
column_two => $var2,

},

{join => "somethings"},

);

Please not that you can define any kind of join available in your RDBMS via the Class file. You can also nest joins and other crazy joining operations. For more examples refer to the cookbook.

Prefetching is very interesting in that it does the exact same thing as a join EXCEPT when you want to use that data again it doesn’t poll the database for fresh info. It instead uses the data from the first query which could have the potential to speed up page loads significantly. To use prefetch simply replace “join” with “prefetch”.

InflateColumn

There are several things that make DBIx::Class a powerful tool in yourarsenal but data inflation is one of my favorites. Say for example that we have a DateTime column in our database and we want to use it as a Perl DateTime object in our code. Instead of using DateTime::Formatter::MySQL or something of the like we can just use InflateColumn::DateTime. This way what DBIx::Class returns to us is not a DateTime String but a DateTime object ready to be used.

You can also use InflateColumn for other types of objects or you could write your own data inflators.

NewOpportunitieswith DBIx::Class

DBIx::Class opens up a whole new world of possibilities for Koha to use for its day-to-day operations. Some of these include:

• DBIx::Class::DeploymentHandler – Version Control
• DBIx::Class::InflateColumn – Object Inflation
• DBIx::Class – handling data from multiple databases
• more that I haven’t explored yet

If you have any questions regarding DBIx::Class and how to use it in Koha feel free to find me on the IRC as libsysguy, shoot me an email, or leave a comment below.

References

• http://search.cpan.org/~frew/DBIx-Class-0.08201/lib/DBIx/Class/Manual/Cookbook.pod
• http://search.cpan.org/~mstrout/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod
• http://search.cpan.org/~mstrout/DBIx-Class-0.08202/lib/DBIx/Class/InflateColumn.pm
• http://search.cpan.org/~frew/DBIx-Class-DeploymentHandler-0.002202/lib/DBIx/Class/DeploymentHandler.pm

[Originally posted by Elliott Davis / Translated to Croatian by Anja Skrba]