Categories
Uncategorized

Class::DBI transactions

This is something I did at work just before Christmas, which is kind of useful.

Some years ago, I came up with the original method do_transaction() for Class::DBI. However, it’s kind of clunky to use, as the example shows.

Music::DBI->do_transaction( sub {
  my $artist = Music::Artist->insert({ name => 'Pink Floyd' });
  my $cd = $artist->add_to_cds({
    title => 'Dark Side Of The Moon',
    year  => 1974,
  });
});

The thing that bugs me in particular is the mandatory use of sub {}. I’d rather it be like ruby’s blocks, more of a language feature. Thankfully, Perl allows you to do this with prototypes.

So I modified my base class for Class::DBI to look like this:

use Exporter 'import';
our @EXPORT = qw( do_transaction );
sub do_transaction (&) {
  my $class = __PACKAGE__;
  # Rest of function remains the same.
}

This lets you call do_transaction in a much simpler fashion:

use Music::DBI;
do_transaction {
  my $artist = Music::Artist->insert({ name => 'Pink Floyd' });
  my $cd = $artist->add_to_cds({
    title => 'Dark Side Of The Moon',
    year  => 1974,
  });
}

Now I have committed one of the cardinal sins of Perl OO here—I’m exporting from an OO module. In this case though, I feel it’s justified. Firstly, you need to call the function as a function and not a method for the prototype to have any effect. Secondly, it greatly reduces the amount of typing that I have to do. Overall, it’s a much nicer solution.

Of course, Ruby on Rails makes this much simpler, because it’s notion of blocks is so deeply embedded into the language. See ActiveRecord::Transactions::ClassMethods for details.

Categories
Uncategorized

REST on RAILS

Matt Biddulph has just had an article published on xml.com, REST on rails. It’s a really great way of working, and I encourage closer scrutiny. And it shows off Rails at its best. I really look forward to playing with Rails more. I would like to implement PUT for that rest_resource method as well…

It also happens to be very similar to a system I wrote at work a couple of years ago using Perl and Class::DBI. The system we have at work is a bit more complicated and has more edge cases in the generated XML, particularly with regards to nesting. Looking at that article makes me wonder if we couldn’t simplify the design our protocol… But it’s already in use by so many clients, I think that would be difficult at this point.

Categories
Uncategorized

Class::DBI, PostgreSQL and Transactions

Once again, I managed to see the dreaded ”<IDLE> in transaction” message from PostgreSQL today. This means that I have a connection to the database open, which is currently inside a transaction. This is bad because locks are held open in a transaction. So, what to do about it? Firstly, examine the documentation.

  1. <a href="http://search.cpan.org/dbdpg/DBD-Pg-1.43/Pg.pm#Transactions”>DBD::Pg on transactions.
  2. <a href="http://search.cpan.org/timb/DBI-1.48/DBI.pm#Transactions”>DBI on transactions.
  3. <a href="http://search.cpan.org/tmtm/Class-DBI-v3.0.4/lib/Class/DBI.pm#TRANSACTIONS”>Class::DBI on transactions (which I’m amused to see that I contributed when I first came across this problem several years ago)

Now, the application I’m working on at present has no particular need for transactions for the most part. So it would definitely be easier to turn on <a href="http://search.cpan.org/timb/DBI-1.48/DBI.pm#AutoCommit”>AutoCommit and only drop into a transaction when I absolutely have to.

What do I know so far?

  • AutoCommit defaults to on.
  • But Class::DBI disables it by default for PostgreSQL.
  • My application is running as part of an HTML::Mason web page.
  • When I make any call that goes near the database on a Class::DBI object, DBD::Pg executes an implicit BEGIN before the SQL that it has been sent executes. Only the first time though. This is because DBD::Pg is a database where a transaction must be explicitly started. DBI says the driver will automatically begin an explicit transaction when AutoCommit is turned off.
  • In a nutshell, I need to get AutoCommit turned on until I need it.

There, wasn’t that simple?