Ovid

Did you know you can write a view in DBIx::Class? The DBIx::Class::ResultSource::View module makes this very easy and it's helped me solve a very thorny problem in Veure: how do I efficiently make sure that email sent from Alpha Centauri to Epsilon Eridani doesn't show up instantly in your inbox?

Here's the problem: in the game Veure, you can send email to other players (only one at a time), they can reply, there can be email "threads", and so on. If you notice something interesting at the cloning vats in The House of Comoros space station in the Alpha Centauri system and you dash off a quick email to your friend about it, you may not know or care where your friend is. If they're in the same star system, they get the email instantly (a concession to gameplay mechanics), but if they're at the Epsilon Eridani Jump Gate, that will take a while because the email is traveling via wormhole.

Epsilon Eridani is about 10.5 light years from Sol, but it's 12.64 light years from Alpha Centauri A. As luck would have it, there's a direct wormhole between the two, making your email faster (there is no direct wormhole between Sol and Epsilon Eridani, so you actually have to travel almost 14 light years to get there). Information sent via wormhole takes 30 seconds per light year to travel (that's over three times faster than the Corvette, currently the fastest ship in the game). As I use PostgreSQL for the Veure database, I can take advantage of PostgreSQL's excellent time handling. So my basic rules look like this:

• You can always see email you've sent
• You can always see email originating in the system you're currently in
• You cannot see email originating in a different star system unless it was sent 30 seconds x "wormhole route distance" ago.

Now when you want to fetch an entire email thread, that's where things start to get hairy because the SQL looks like this (this lets us select the entire thread, regardless of which email id is used):

Do you want to try to convert that to a dbic query? I didn't think so. I started on it but it wasn't clear to me that it was an improvement over the raw SQL.

Fortunately, dbic's views take arbitrary SQL and returns a standard resultset, though the individual results are read-only (because they might not have a one-to-one correspondence with to a given table). That's often fine in a Web app because you often present a list of results, a user chooses one and acts on that. The "choosing one" happens on a separate request where you can edit a standard result instead of the view result.

For the above SQL, I now have it wrapped up in the following:

package Veure::Schema::Result::View::EmailThread;

use Moose;
use MooseX::MarkAsMethods autoclean => 1;
extends 'Veure::Schema::Result::Email';

__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
__PACKAGE__->table("email_thread");    # XXX virtual view name. Doesn't exist

# is_virtual allows us to use bind parameters
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition($scary_sql_here);
__PACKAGE__->meta->make_immutable;

1;

And, of course, the ResultSet class:

package Veure::Schema::ResultSet::View::EmailThread;

use strict;
use warnings;
use parent 'DBIx::Class::ResultSet';

sub get_thread {
    my ( $self, $email, $character ) = @_;
    my $id = $character->character_id;
    return $self->search( {}, { bind => [ $email->id, $id, $id ] } );
}

1;

I have a lot more work to do on this, but even though the above is a bit clumsy, it lets me solve a hard problem with very little code. Sure, I could have used a regular email result set and iterated through all of them, checking to see if you were the recipient and whether you were in a different star system and where enough time had elapsed to let you see the email.

Or I can let my database do it for me. Not only is the code faster, it's cleaner, too.