On writing clean database code

In earlier articles we looked at the way we use “economic exchanges” to simplify complex code into a series of small understandable steps. We use similar ideas to build up business logic in our DBIx::Class model layer: combining multiple small predefined queries together to create more complex conditions for generating SQL queries against the database.

Read on below if you take the challenge to read “0011001010” language of our database experts who love to share knowledge with you again about clean code.

What is DBIx::Class?

DBIx::Class (often known more simply as DBIC) is the “Object/Relational Mapping” system we use in Tau Station. DBIC is not a really a full ORM in the traditional sense, but can be better described as:

  • a stateful query generator
  • a stateful result parser
  • a convenient way to organise database business logic

We also make heavy use of DBIx::Class::Helpers (thanks Frew!) and you will see them used frequently in the following code samples. If you use DBIC without DBIx::Class::Helpers then I suggest you check them out.

The Bad and the Ugly

We often need a list of visible player characters which we might also restrict to those on a specific station, or perhaps just within a specific station area. We could use a single large query but since game requirements change we would have to maintain this long-term and continually risk introducing new unexpected bugs. Such a DBIC query might look something like this:

sub visible ($self) {
    my $end = $self->context->now;
    my $start = $end - period(
        days => config('time', 'player_active_duration')
    );

    $_ = $schema->format_datetime($_) foreach $start, $end;

    return $self->available->search(
        {
            $self->me('arrival')       => { '<=', $end },
            $self->me('last_activity') => { '>=', $start },
            [
                # citizen
                {
                    $self->me('affiliation_id') =>
                      { -ident => 'station.affiliation_id' }
                },

                # station affiliation does not require visa
                { -not_bool => 'affiliation.requires_visa' },

                # valid visa on station
                {
                    'station.affiliation_id' =>
                      { -ident => 'visas.affiliation_id' },
                    'visas.expires' => { '>=', $now },
                }
            ],
            'hotel_room_occupied.hotel_room_id' => undef,
            $self->me('current_ship_id')        => undef,
        },
        {
            join => [
              	'character_user',
                'hotel_room_occupied',
                'visas',
                {
                    station_area => {
                        station => 'affiliation'
                    }
                }
            ],
        }
    );
}

This is much more complex than we’d like so…

DBIC to the rescue

Fortunately DBIC supports the concept of “chaining” which allows us to create a number of small, understandable and maintainable queries that can be chained together to create more complex queries. Its documentation has this example:

$schema->resultset('Book')
    ->good
    ->cheap
    ->recent

Each of ‘good’, ‘cheap’ and ‘recent’ are implemented as individual methods known as ‘predefined queries’ and we can test them individually and re-use them elsewhere. Reduced code complexity and reusability all in one: success!

The Good

Our complex query can be handled in the same way and we construct it from small predefined queries…

A player’s character might be travelling from one station to another. We want¬†only characters who have arrived at their destination:

sub arrived ($self) {
    my $now = $schema->format_datetime(
        $self->context->now
    );

    return $self->search(
        { $self->me('arrival') => { '<=', $now } },
    );
}

… and who have been active in the game recently:

sub active ($self) {
    my $start = $schema->format_datetime(
        $self->context->now
        - config(qw/time player_active_duration/)
    );

    return $self->search(
        {
            $self->me('last_activity') => { '>=', $start }
        }, 
    );      
}

… and they must have a valid visa for their current location (this one is a little more complex):

sub with_valid_visa ($self) {
    my $now = $schema->format_datetime(
        $self->context->now
    );

    return $self->search(
        [
            # citizen
            {
                $self->me('affiliation_id') =>
                  { -ident => 'station.affiliation_id' }
            },

            # station affiliation does not require visa
            { -not_bool => 'affiliation.requires_visa' },

            # valid visa on station
            {
                'station.affiliation_id' =>
                  { -ident => 'visas.affiliation_id' },
                'visas.expires' => { '>=', $now },

            }
        ],
        {
            join => [
                'visas',
                {
                    station_area => {
                        station => 'affiliation'
                    }
                }
            ],
        },
    );
}

… also remembering that characters who are in a hotel room cannot be seen:

sub outside_hotel_room ($self) {
    return $self->search(
        {
            'hotel_room_occupied.hotel_room_id' => undef,
        },
        {
            join => 'hotel_room_occupied',
        }
    );
}

… and we cannot see anyone onboard their own ship:

sub not_onboard_ship ($self) {
    return $self->search(
        {
            $self->me('current_ship_id') => undef
        }
    );
}

Since we need all of those queries to be chained together to create the
‘visible’ query we create this new method:

sub visible ($self) {
    return
      $self->arrived->active->with_valid_visa
        ->outside_hotel_room->not_onboard_ship;
}

… which would result in an SQL query along the lines of:

SELECT me.* FROM character me
  LEFT JOIN visa visas ON visas.character_id = me.character_id
  JOIN station_area station_area
    ON station_area.station_area_id = me.station_area_id
  JOIN station station ON station.station_id = station_area.station_id
  JOIN affiliation affiliation
    ON affiliation.affiliation_id = station.affiliation_id
  LEFT JOIN hotel_room hotel_room_occupied
    ON hotel_room_occupied.occupied_id = me.character_id
WHERE ( NOT affiliation.requires_visa
        OR me.affiliation_id = station.affiliation_id
        OR ( station.affiliation_id = visas.affiliation_id
             AND visas.expires >= ?
           )
      )
      AND me.last_activity >= ?
      AND me.arrival <= ?
      AND me.available
      AND current_ship_id IS NULL
      AND hotel_room_occupied.hotel_room_id IS NULL

Now we can narrow down our predefined ‘visible’ search to those characters in the same station area as the current character:

my $visible_characters = $schema->resultset('Character')
  ->visible->search(
    {
        $self->me('station_area_id')
          => $character->station_area_id
    }
);

Using small predefined queries that we can chain together allows us to develop faster and write testable and maintainable code. We also find bugs more quickly and can fix them more easily without risk of breakage.

The above code is, of course, subject to change.